Smart Oracle

2012-07-06T03:55:00.001-07:00

A Journey from Oracle E-Business Suite R12.1 to R12.2

I will try to analyze architectural overview of the latest updates, installation and upgrade options, new configuration options, and new tools for hot-cloning and automated “lights out” cloning in R-12.2

There are lots of updates coming in this release, but from a technology stack perspective, EBS 12.2 will be notable for two things:

Replacing Oracle Containers for Java (OC4J) 10g with WebLogic Server 11g
Online Patching support via 11gR2 Edition-Based Redefinition

Release 12.2 will replace Oracle Containers for Java (OC4J) 10g with WebLogic Server 11g
Release 12.2 Database tier will run RDBMS 11gR2 to support online patching

There will be number of updates coming in release R12.2, from a technology stack perspective, EBS 12.2 will be notable for below things:

Replacing Oracle Containers for Java (OC4J) 10g with WebLogic Server 11g

EBS 12.2 will use WebLogic Server from Fusion Middleware 11g in place of OC4J 10g as part of the release’s internal technology stack. Other additional new Fusion Middleware 11g components used will include WebLogic JSP and UIX 11g

Oracle E-Business Suite R12 (12.2) uses Oracle Fusion Middleware 11g R1 PS3 (11.1.1.4) including WebLogic 10.3.4 as its application server (Note: In previous version of R12 i.e. 12.0.X and 12.1.X this is 10g R3 i.e. 10.1.3.X). Oracle HTTP server in R12 (12.2) is 11.1.1.4

Online Patching support via 11gR2 Edition-Based Redefinition

EBS 12.2 will use the 11gR2 Database’s Edition-Based Redefinition features to provide support for Online Patching. Edition-Based Redefinition is really exciting new technology.

“Edition-based redefinition enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.

“To upgrade an application while it is in use, you copy the database objects that comprise the application and redefine the copied objects in isolation. Your changes do not affect users of the application—they continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users.”

It is not released yet but It will include significant changes to the Application Server architecture and It will change Patching, Cloning & the User Experience.

· Oracle E-Business Suite R12 (12.2) uses Oracle Application Server 10g R3 (10.1.2.3) for Forms & Reports (Note: In previous version of R12 i.e. 12.0.X and 12.1.X, forms are reports are of same version i.e. 10.1.2.3)

· Default Database for Oracle E-Business Suite R12 (12.2) is 11g R2 (11.2.X) . (Note: In previous version of R12 i.e. 12.0.X, default database is 11.1 and 12.1.X, default database is 11.2)

· Oracle JSP Compiler (OJSP) 10.1.3.5 (12.0.X and 12.1.X) is replaced by WebLogic JSP Compiler 11.1.1.4 in R12 version 12.2

· Oracle Apps R12.2 cloning will also support Fusion Middleware (11.1.1.4 – discussed in point 1) cloning.

· Oracle Web Applications Desktop Integrator (Web ADI) in R12.2 is now certified with Microsoft Office 32-bit and 64-bit.

JSP error Request URI:/OA_HTML/RF.jsp java.lang.NoClassDefFoundError

2009-08-10T21:20:00.000-07:00

After running the autoconfig on test instance , faced this error when logging into e-business suite:

JSP Error:
Request URI:/OA_HTML/RF.jsp
Exception:
java.lang.NoClassDefFoundError

JSP Error:
Request URI:/OA_HTML/AppsLocalLogin.jsp
Exception:
java.lang.NoClassDefFoundError

The jserv.log and OACoreGroup..stderr (where is the number of the JVM) will show errors like:-

Exception in static block of jtf.cache.CacheManager. Stack trace is:
oracle.apps.jtf.base.resources.FrameworkException: IAS Cache initialization failed. The Distributed Caching System failed to initialize on port: 12346. The list of hosts in the distributed caching system is: hostA.domain.com . The port 12346 should be free on each host running the JVMs. The default port 12346 can be overridden using
-Doracle.apps.jtf.cache.IASCacheProvidercacheProvider.port=
at oracle.apps.jtf.cache.IASCacheProvider.init(IASCacheProvider.java:220)
at oracle.apps.jtf.cache.CacheManager.activateCache(CacheManager.java:1444)
at oracle.apps.jtf.cache.CacheManager.initCache(CacheManager.java:752)

java.lang.NoClassDefFoundError
at org.apache.jserv.JServServletManager.load_init(JServServletManager.java:765)
at org.apache.jserv.JServServletManager.loadServlet(JServServletManager.java:659)

Solution: Disabling Java Caching mechanism also resolves this issue.

Just commenting out the following line in jserv.properties to disable Java Caching

### wrapper.bin.parameters=-DLONG_RUNNING_JVM=true

FRM-92050 Error with Internet Explorer-8

2009-07-20T03:27:00.001-07:00

While using oracle applications R12 with Internet Explrer 8 an Form Server related error is encountered (FRM-92050: Failed to connect to the server).To, avoid this problem Disable the XSS Filter option in IE8.

Tools--> Internet options--> Security-->Security level for this zone --> Custom level-->Disable XSS filter

Now there is no issue with Error FRM-92050

Errors: ORA-01552 ORA-00376 ORA-01110 ORA-01548 –UNDO tablespace

2009-07-15T00:31:00.000-07:00

Recently our Helpdesk people configured backup on Payroll server. while running the backup, backup applications locking the writing file.for that issue alert log showing errors like below:

Mon Jul 06 05:07:36 2009
KCF: write/open error block=0x6f6 online=1
file=2 D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF
error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.'
Automatic datafile offline due to write error on
file 2: D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF
Mon Jul 06 05:07:36 2009
Errors in file d:\oracle9\product\payroll\admin\payroll9\bdump\payroll9_smon_2700.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF'

Immediately we stopped the backup & restarted the database .
Still the Application is not working. Application show error message

"ORA-01552: cannot use system rollback segment for non-system tablespace"

then I checked for the corrupted rollback segments

SQL>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- -----------------
_SYSSMU1$ NEEDS RECOVERY UNDOTBS1
_SYSSMU2$ NEEDS RECOVERY UNDOTBS1
_SYSSMU3$ NEEDS RECOVERY UNDOTBS1
_SYSSMU4$ NEEDS RECOVERY UNDOTBS1
_SYSSMU5$ NEEDS RECOVERY UNDOTBS1
_SYSSMU6$ NEEDS RECOVERY UNDOTBS1
_SYSSMU7$ NEEDS RECOVERY UNDOTBS1
_SYSSMU8$ NEEDS RECOVERY UNDOTBS1
_SYSSMU9$ NEEDS RECOVERY UNDOTBS1
_SYSSMU10$ NEEDS RECOVERY UNDOTBS1

then I recreated another tablespace as UNDOTBS2

CREATE UNDO TABLESPACE "UNDOTBS2"
DATAFILE 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS02.DBF' SIZE 200M REUSE
AUTOEXTEND ON NEXT 51200K MAXSIZE 800M;

ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;

Now I try to drop the old UNDO tablespace but it’s giving error:

ora-01548 active rollback segment found

Now the issue is resolved but in Alert log we are getting errors like:

Wed Jul 15 11:50:52 2009
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
……..
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery

& for some transactions from applications we are getting some more errors:

ORA-00376: file 2 cannot be read at this tme ORA-01110: data file 2: 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF'

Solution:
1. create pfile from spfile;

2. Add the following line to pfile:

_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$',
'_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

_OFFLINE_ROLLBACK_SEGMENTS=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

3. comment undo management-Auto

#undo_management=AUTO

4. create spfile from pfile

5. Start the database again:

SQL> STARTUP RESTRICT

6. Drop bad rollback segments

SQL> drop rollback segment "_SYSSMU11$";
Rollback segment dropped.
......
SQL> drop rollback segment "_SYSSMU10$";
Rollback segment dropped.

7. Check again

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

No rows returned

8. DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;

9. shutdown immedaite

10. remove added lines & comments from pfile and recreate the spfile

11.startup

Now the issue is resolved.

IOR file '/var/tmp/gconfd-root/lock/ior' not opened successfully, no gconfd located

2009-07-13T00:52:00.001-07:00

While login to the Solaris as root or any other user it’s showing the errors like below:

GConf Error: Failed to contact configuration server; some possible causes are that you need to enable TCP/IP networking for ORBit, or you have stale NFS locks due to a system crash. See http://www.gnome.org/projects/gconf/ for information. (Details - 1: IOR file '/var/tmp/gconfd-root/lock/ior' not opened successfully, no gconfd located: No such file or directory 2: IOR file '/var/tmp/gconfd-root/lock/ior' not opened successfully, no gconfd located: No such file or directory)

I had checked the /var/tmp/gconfd-root directory permissions

it has 777 permissions for that directory

while i checked log under /var/log it says: 777 is bad option for gconfd-root
I had changed that permissions to 700 and all seems ok now.

now it’s working fine

ORA-20100: Error: FND_FILE FAILURE unable to create file in the directory,/usr/tmp.

2009-07-13T00:37:00.000-07:00

Concurrent requests are failed with the below error:

ORA-20100: Error: FND_FILE failure. Unable to create file, o0040178.tmp in thedirectory, /usr/tmp.

You will find more information in the request log.
ORA-06512: at "APPS.FND_FILE", line 417
ORA-06512: at "APPS.FND_FILE", line 456
ORA-06512: at line 1

Upon checking below settings there is no issue :

1. The $APPLTMP, $APPLPTMP variables and UTL_FILE_DIR database parameter are valid and pointing to directory where there is enough space and users have written permission.
2. FND_FILE.PUT_LINE is able to create new files in the temporary directory.

FND_FILE.PUT_LINE Checking method:

SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');

This should dump a file on APPLPTMP. If this test works, it would indicate that FND_FILE is ok and the problem is possibly with the Application.

You may want to leave only one entry on utl_file_dir for this test.

finally I checked the utl_file_dir, but there is no entry like /usr/tmp path in that list

SQL> connect / as sysdbaSQL> show parameter UTL_FILE_DIR

just I added /usr/tmp for the UTL_FILE_DIR & restarted the applications & database.

now the issue is resolved & concurrent requests are working fine

TNS listener failed to start with 12546 , 12560 , 00516 & Solaris Error: 13: Permission denied errors

2009-07-12T23:44:00.000-07:00

while starting the database listener, listener is failed with errors.

LSNRCTL> start VIS
Starting /data2/oracle/VIS/db/tech_st/10.2.0/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 10.2.0.3.0 - Production

System parameter file is /data2/oracle/VIS/db/tech_st/10.2.0/network/admin/VIS_aplerp6/listener.ora

Log messages written to /data2/oracle/VIS/db/tech_st/10.2.0/network/admin/vis.log

Error listening on: (ADDRESS=(PROTOCOL=ipc)(PARTIAL=yes)(QUEUESIZE=1))

No longer listening on:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aplerp6.apl.com)(PORT=1522)))

TNS-12546: TNS:permission denied

TNS-12560: TNS:protocol adapter error

TNS-00516: Permission denied

Solaris Error: 13: Permission denied

Listener failed to start. See the error message(s) above...

cause:
.oracle read, write permissions are changed
solution
cd /var/tmp
chmod 777 .oracle
now the issue is resolved & listener stared .

ORA-29282: invalid file ID. ORA-06512: at "SYS.UTL_FILE", line 1

2009-07-12T23:36:00.000-07:00

During the 12.0.6 to 12.1.1 upgrade , Main patch (7303030 -12.1.1 Maintenance Pack) is failed with the below error:

Error message in the worker logs is:-
DECLARE*ERROR at line 1:
ORA-29282: invalid file ID
ORA-06512: at "SYS.UTL_FILE",
line 1ORA-06512: at line 41
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE",
line 449ORA-29283: invalid file operation

Cause:
In this case the utl_file_dir was set to /usr/tmp for both instances.

The utl_file package does not give a suitable error message when it finds an existing file with the same name as the file it is attempting to write.

The same errors can be reported when the system does not have permissions to write to the utl_file_dir.

Solution:
Change the utl_file_dir variable in both init.ora files for each instance and bounce the RDBMS.

Initially Instance-VIS and Instance-CRP both had utl_file_dir=/usr/tmp .

They should be set to something like:-

For VIS Instance- utl_file_dir=/usr/tmp/VIS/

For CRP Instance- utl_file_dir=/usr/tmp/CRP/

ORA-12701: CREATE DATABASE character set is not known

2009-07-12T23:23:00.001-07:00

While Starting the database, I had faced the error - ORA-1092 signalled during: ALTER DATABASE OPEN...
then check in the Alert.log it shows the messages like below:
ORA-12701: CREATE DATABASE character set is not known
Thu Jul 9 19:10:14 2009
Error 12701 happened during db open, shutting down database
USER: terminating instance due to error 12701
Instance terminated by USER, pid = 28018
ORA-1092 signalled during: ALTER DATABASE OPEN...

Solution:
I had checked $ORA_NLS10 path. Instead of “$ORACLE_HOME/nls/data/9idata”,
it points to the Wrong one like below:

echo $ORA_NLS10
ORA_NLS10=/aplvol01/CRP/oracle/CRP/db/tech_st/10.2.0/ocommon/nls/admin/data

Change the env file to the correct path and start the database.

wferr: - 1602: Could not save. - 1400: Could not save to database. MODE=UPGRADE EFFDATE=

2009-07-12T23:10:00.000-07:00

While Applying the 8310984 - May 2009, Order Management R12 Cumulative Patch on R12.0.6, worker is failed with the below mentioned error:

ATTENTION: All workers either have failed or are waiting:
FAILED: file oexwford.wft on worker 1.
Worker Log file:
wferr:
- 1602: Could not save.
- 1400: Could not save to database. MODE=UPGRADE EFFDATE=
- 1404: Please first load this entity or check protect and custom level of ACTIVITY 'WFSTD/FED_BUILD_PA_AP_INVO_ACCT_GEN'.
Oracle Workflow Definition Loader 2.6.4.0.
Access level: 20, Mode: UPGRADE
ITEM_TYPE 'WFSTD' is protected, no changes were saved.
ITEM_ATTRIBUTE 'WFSTD/EVENTNAME' is protected, no changes were saved.
ITEM_ATTRIBUTE 'WFSTD/REQUESTOR_USERNAME' is protected, no changes were saved.
ITEM_ATTRIBUTE 'WFSTD/OWNER_ROLE' is protected, no changes were saved.
ITEM_ATTRIBUTE 'WFSTD/FORWARD_FROM_DISPLAY_NAME' is protected, no changes were saved.
ITEM_ATTRIBUTE 'WFSTD/N' is protected, no changes were saved.

Solution:
Change the Patch driver file related WFLOAD command from UPGRADE to FORCE.

Before Modify:
# file-version-parsed ont patch/115/import/US oexwford.wft 120.8.12000000.10

exec fnd bin WFLOAD bin &phase=daa+52 checkfile:ont:patch/115/import/US:oexwford.wft &ui_apps 0 Y UPGRADE @ONT:patch/115/import/US/oexwford.wft

After Modify:
# file-version-parsed ont patch/115/import/US oexwford.wft 120.8.12000000.10

exec fnd bin WFLOAD bin &phase=daa+52 checkfile:ont:patch/115/import/US:oexwford.wft &ui_apps 0 Y FORCE @ONT:patch/115/import/US/oexwford.wft

How to Stop Accrual Plans (Leave Balances) from Going Negative

2009-06-24T21:50:00.000-07:00

If using the Absence form, you will get a warning message if an employee takes more hours than are accrued - but it's a soft error, and you can just click OK to go through it.

To prohibit the accrual plan from going negative, there are two distinct options: A. If you are using OTL, you can alter OTL behavior B. If you are not using OTL, you will alter the absence API behavior

Option A: If you are using Oracle Time and Labor, you can setup Time Entry Rules that prohibit the accrual plan from going negative. You have to use the US OTL Application Developer Responsibility. Navigation Path: Time Entry Rules -> Define Time Entry Rules.

Option B:
If you are not using OTL, you will alter the absence API behavior using API user hooks.

a) The API that is being used: HR_PERSON_ABSENCE_API

b) If preventing the PTO balance from going negative, the user hook will have to check the boolean parameter called 'p_exceeds_pto_entit_warning'. If this is set to TRUE then raise an error message.

Implementing User Hooks:

1. Choose the API you wish to hook some extra logic to.

SELECT AHK.API_HOOK_ID,
AHK.HOOK_PACKAGE,
AHK.HOOK_PROCEDURE
FROM HR_API_HOOKS AHK,
HR_API_MODULES AHM
WHERE
AHM.MODULE_NAME LIKE 'CREATE_PERSON_ABSENCE'
AND AHM.API_MODULE_TYPE = 'BP' AND
AHK.API_HOOK_TYPE = 'AP'
AND
AHK.API_MODULE_ID=AHM.API_MODULE_ID;

it returns API_HOOK_ID, HOOK_PACKAGE, HOOK_PROCEDURE values

2. Write the PL/SQL procedure that you wish to be called by the hook.

CREATE OR REPLACE PACKAGE APL_NEG_BAL_CHECK AS
PROCEDURE APL_NEG_ACR_BAL_CHK
( P_ABSENCE_ATTENDANCE_TYPE_ID NUMBER
,P_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN);
END APL_NEG_BAL_CHECK;

CREATE OR REPLACE Package Body APL_NEG_BAL_CHECK as
PROCEDURE APL_NEG_ACR_BAL_CHK
( P_ABSENCE_ATTENDANCE_TYPE_ID NUMBER
,P_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN)
IS

CURSOR CSR_GET_ABS_CAT IS
SELECT ABSENCE_CATEGORY
FROM PER_ABSENCE_ATTENDANCE_TYPES
WHERE ABSENCE_ATTENDANCE_TYPE_ID = P_ABSENCE_ATTENDANCE_TYPE_ID;
L_ABS_CATEGORY VARCHAR2(20);

BEGIN

--Get Absence Category
OPEN CSR_GET_ABS_CAT;
FETCH CSR_GET_ABS_CAT INTO L_ABS_CATEGORY;
CLOSE CSR_GET_ABS_CAT;

IF (L_ABS_CATEGORY ='H') AND (P_EXCEEDS_PTO_ENTIT_WARNING = TRUE) THEN
fnd_message.set_name('PER','HR_LOA_EMP_NOT_ENTITLED');
fnd_message.raise_error;
END IF;
END APL_NEG_ACR_BAL_CHK;
END APL_NEG_BAL_CHECK;

3. Register or associate the procedure you have written

Pass the input values like HOOK_ID from Step 1
declare
l_api_hook_call_id number;
l_object_version_number number;
begin
hr_api_hook_call_api.create_api_hook_call
(p_validate => false,
p_effective_date => to_date('01-JUL-1999','DD-MON-YYYY'),
p_api_hook_id => 3870,
p_api_hook_call_type => 'PP',
p_sequence => 3000,
p_enabled_flag => 'Y',
p_call_package => 'APL_NEG_BAL_CHECK',
p_call_procedure => 'APL_NEG_ACR_BAL_CHK',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number);
end;

check is registration successful or not , run the below script
SELECT * FROM HR_API_HOOK_CALLS WHERE CALL_PACKAGE='NEG_BAL_CHECK'

4. Run the pre-processor program which builds the logic to execute your PL/SQL procedure from the hook.

To run the pre-processor run one of the following commands:

cd $PER_TOP/admin/sql

Log into SQLPLUS as the APPS user

SQL> @hrahkall.sql

If it successful below lines will be added to the HR_PERSON_ABSENCE_BK1 API.

if hr_api.call_cus_hooks then
NEG_BAL_CHECK.NEG_ACR_BAL_CHK
(P_EXCEEDS_PTO_ENTIT_WARNING => P_EXCEEDS_PTO_ENTIT_WARNING
);end if;

5. Now you try to apply leave, the Absence page will not allowed you for next page if Accrual balance is Zero.

APP-FND-01564: ORACLE error 1403 in changepassword

2009-06-04T03:37:00.000-07:00

When we Try to change all schemas the Password using FNDCPASS, we had encountered the “SECURITY-UNABLE TO CONNECT TO SYSTEM “ error.
$ FNDCPASS apps/apps 0 Y system/manager ALLORACLE welcome

ERROR:

Related log file(L7187704.log) shows the Error message like below:
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
module:
+---------------------------------------------------------------------------+
Current system time is 04-MAY-2009 16:04:57
+---------------------------------------------------------------------------+
SECURITY-UNABLE TO CONNECT TO SYSTEM
APP-FND-01564: ORACLE error 1403 in changepassword
Cause: changepassword failed due to ORA-01403: no data found.

The SQL statement being executed at the time of the error was: and was executed from the file &ERRFILE.
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 04-MAY-2009 16:04:57
+---------------------------------------------------------------------------+
Solution:
Recreating the grants & synonyms will solve the Issue with FNDCPASS

ODX-0251: Unable to find stored variable "SQL_115_AX"

2009-06-04T03:21:00.000-07:00

I had faced the below issue ,While running the Payables invoice data Diagnostic test in R12. For that issue we had raised a Service request with Oracle. But no further progress from Oracle Support.
Version Info:

Release = 12.0.4
Patch OD for R12 RUP3 [12.0.3] (6154018) is installed
Patch Oracle Diagnostics Tools, R12.IZU.A.DELTA.4 (6497339) is installed

Issue:
ODX-0060: SQL has not been executed as table or view does not exist (ORA-00942 returned)Error executing element "sql" with the name "SQL_115_AX"Error in the sql statement:

"SELECT event_id FROM AX_EVENTS WHERE application_id = 200 AND event_type like 'NON_CASH%' AND event_field1 = :1 AND NVL(org_id, -99)= :2 UNION SELECT event_id FROM AX_EVENTS WHERE application_id = 200 AND ( event_type like 'CASH%' OR event_type like 'FUTURE%' ) AND event_field1 IN ( SELECT check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = :3 ) AND NVL(org_id, -99) = :4" with the bind values:"'38658', '82', '38658', '82'".

SQLERROR - ODX-0251: Unable to find stored variable "SQL_115_AX".ACTION -

If the XML file for this test was delivered by Oracle Support Services, contact the support representative for assistanceXML File Information

XML Engine Version = 3.2.1

File Name = APListXml.xml (File Version = 120.0.12000000.8, File Location = ap/diag, Date = 2008/04/24)

But Finally we find out the resolution. That issue occurred with synonym of AX_EVENTS Table.

Solution:
1. Recreate the grants & synonyms through adadmin & Complie Apps Schema.

ORA-00821: Specified value of sga_target 280M is too small, needs to be at least 896M

2009-05-19T02:52:00.000-07:00

Recently I have installed Oracle10g on Windows for Trident Leave management system implementation.
The base install of Oracle10g Release 2 (10.2.0.1) went fine. As part of the install, I let the install create the starter database. after completion of the installation, I had increased the SHARED_POOL_SIZE and JAVA_POOL_SIZE are 400MB each using scope=pfile.

then I shutdown the database & restarting the database with startup command. The following error appeared
ORA-00821: Specified value of sga_target 280M is too small, needs to be at least 896M
Then I find out , that issue with SGA_TARGET size.

If we are using Automatic Shared Memory Management, ensure that the value of the SGA_TARGET initialization parameter size is at least 50 MB greater than the sum of the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters.

This is important! Most likely, you are using Automatic Shared Memory Management.
Solution is increase the value of the SGA_TARGET parameter to the value specified in the error.
Since Oracle said this could happen and that all I need to do was change the SGA_TARGET to the stated value, I did so.
SQL> alter system set sga_target=900m scope=spfile;
alter system set sga_target=900scope=spfile
*
ERROR at line 1:ORA-01034: ORACLE not available
so I 'll do a startup nomount and then change the SGA_TARGET.
SQL> startup nomount
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0
That didn't work, so I tried it again
Connected to an idle instance.
SQL> startup nomount
ORA-00821: Specified value of sga_target 280M is too small, needs to be at 896
SQL> alter system set sga_target=900m scope=spfile;
alter system set sga_target=900m scope=spfile
*
ERROR at line 1:ORA-01034: ORACLE not available

I thought I would create a pfile from the spfile, change the SGA_TARGET in the pfile, since it is editable, and then restart the database using the pfile.
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-27041: unable to open file
OSD-04002: unable to open fileO/S-Error:
(OS 2) The system cannot find the file specified.

I checked for pfile in ORACLE_HOME/database, there was no spfile, just a pfile with one line in it, pointing to the spfile in the directory ORACLE_HOME/dbs. Since the database was still down, I renamed the pfile in the database directory and copied the spfile from the dbs directory to the database directory, and tried the same command again.

SQL> create pfile from spfile;
File created.
I edited the pfile, setting the SGA_TARGET to 900M, and tried the startup command in new session.

SQL> startup pfile= xxx/xxx/xxx/inittrilms.ora
ORACLE instance started.
then I created the Spfile using pfile
Now the issue resolved

Enable Search Functionality For Move Orders Created By Other Users On Find Move Orders

2009-03-22T04:22:00.000-07:00

In the Move Orders form (INVTOMAI), when using the Find function, the Created By field cannot bechanged. For this reason, move orders from other users cannot be queried.
The security function INV_INVTOMAI_CREATOR is not enabled in the menu being used. The form INVTOMAI.fmb within the Package TOMAI_MENU and PROCEDURE CREATED_BY_CHECK checks for the function INV_INVTOMAI_CREATOR. If it is not enabled for the user, the Created By field is not updatable.
Execute the following steps to enable the INV_INVTOMAI_CREATOR function:
A. Define the INV_INVTOMAI_CREATOR function if is does not already exist:
Navigate: System Administrator > Application > Function
Enter Function Name: INV_INVTOMAI_CREATOR
Enter User Function Name: 'Move Orders Creator'
Under the Properties tab, enter Type: Subfunction
Enter Maintenance Mode support: None
Enter Context Dependence: Responsibility
Save
B. Assign the function to the Menu you are using:
Navigate System Administrator > Application > Menu
Click on the flash light icon from Tool bar.
Add a new line to the menu:Prompt: 'Move Order Creator'Function: select 'Move Orders Creator' from the LOVDescription: 'Move Orders Creator'
Save
C. Check the Functionality
Go the the responsibility that has move orders and open move order screen.
Query for menu 'INV_MOVE_ORDER'
The Created By field in the Move Order find form should now be enabled and modifiable.
When you go to created by field you will see an LOV. Select the user whose move orders have to be View or cancel.
Hit Find button
Now click on open for the records retrieved. Select from tool bar tools > Cancel if you want to cancel or close to close the move order.
Note: You may need to sign out and back in for the change to take affect.
Ref Doc: Note:280131.1

Step by Step to Install Oracle Applications R12.0.4 On Solaris 5.10(SPARC)

2009-01-03T21:24:00.000-08:00

Environment:

Operating Systems Requirement: Solaris 5.10 (SPARC)

Oracle Applications : e-business Suite R12.0.4

1. Command to check the operating system version: $ uname –r

2. Command to check the maintenance update level: $ cat /etc/release
Required Patches
a. 125100-04 or higher
b. 120473-05 or higher:

Command to retrieve the list of operating system patches already applied:

$ showrev -p sort > patchList

Command to check for a specific patch:

$ /usr/sbin/patchadd -p grep patch_number

Required Packages for R12
Solaris 10 (5.10)
SUNWarc
SUNWbtool
SUNWhea
SUNWlibm
SUNWlibms
SUNWsprot
SUNWtoo
SUNWi1of
SUNWi1cs
SUNWi15cs
SUNWxwfnt

Command to check for existing packages:$ pkginfo

Required Kernel Settings
Solaris 10 (5.10)

set rlim_fd_max=65536
set noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmni=100

Check the /etc/system file to verify the kernel settings.

Hostname Settings

Verify that the /etc/hosts file is formatted as follows:

127.0.0.1 localhost.localdomain localhost

Create Users & Groups

Groupadd dba

Useradd –d /Home/applprod –g dba applprod

Useradd –d /Home/oraprod –g dba oraprod

Start Install:Create the Stage directory and start Rapid Install.

$ cd /dump/StageR12/startCD/Disk1/rapidwiz$ ./rapidwiz

Go through the Welcome Screen & Next

Go through the Wizard Screen & Next

Go through the Oracle Configuration Manager Screen & Next

We are doing fresh ,Single node installationOn Confuguration choice screen

select Create a new configuration & Next

On Global Syatem settings Screen Provide the Port pool

Provide the database node details

Check & verify the Node Paths

Go through the licensing & select required

Select The Country Specific Functionalities

Provide the Applications Node Details

Verify the Node Information

Go through the Pre-Install Checks

Go through review & Next

Start the Installation by go through YES

Verify the Installation Stages

Verify through Post-install Checks & Next

Next Finish

Export/Import Issue with oracle 8i

2009-01-03T20:53:00.000-08:00

While Upgrading the EMS Database to 9.2.0.6, I have faced Some issues while Importing the EMS data Using the Export DMP file that was created with an Oracle 8i (8.1.6) Release Export Utility.

Error Message:
IMP-00017: following statement failed with ORACLE error 6550:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :="
" NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR"
"RAY(~,~); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STAT"
"S.SET_COLUMN_STATS(NULL,'"BOE_DTL"','"BED_EXP_BNK_CODE"', NULL ,NULL,NULL,0"
",0,7209,srec,1,2); END;"
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 1, column 155:
PLS-00103: Encountered the symbol "~" when expecting one of the following:

IMP-00017: following statement failed with ORACLE error 6550:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :="
" NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR"
"RAY(~,~); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STAT"
"S.SET_COLUMN_STATS(NULL,'"BOE_DTL"','"BED_BYR_BNK_CODE"', NULL ,NULL,NULL,0"
",0,7209,srec,1,2); END;"
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 1, column 155:
PLS-00103: Encountered the symbol "~" when expecting one of the following:

Solution:
This issue occurs with an Oracle8i, 8.1.7.2 or earlier release export utility Apply the 8.1.7.3 Patch set or Use this Action plan to resolve the Data Migration Issue:
1. Re-run the export without exporting the statistics:

> exp system/password FILE=... STATISTICS=none .... (other parameters)

And re-run the import afterwards.

or:

2. Do not import the statistics that were exported in the export dump file:

a. When importing into an Oracle8i database:
> imp system/password FILE=... ANALYZE=n .... (other parameters)
or:
> imp system/password FILE=... RECALCULATE_STATISTICS=y .... (other p's)

b. When importing into an Oracle9i/Oracle10g database:
> imp system/password FILE=... STATISTICS=none .... (other parameters)
or:
> imp system/password FILE=... STATISTICS=recalculate .... (other params)

A New Begining

2008-12-31T09:18:00.000-08:00

Probably this will be my last post in this year 2008...
The countdown begins.
The excitement rises.
A joyous New Year dawns.
Time to share the joy and excitement with loved ones and special friends...

May The Year 2009, Bring for You Happiness,Success and filled with Peace,Hope & Togetherness of your Family & Friends....
Wishing You a...*HAPPY NEW YEAR*

Oracle Applications R12 Migration from Solaris to Linux Platform

2008-12-18T07:41:00.001-08:00

Abstract

This Document is intended to describe the steps we followed to migrate the existing Oracle Applications Release 12 both Application & Database Tiers from Solaris Sparc Platform to Redhat Linux Platform. This process provides us a way to quickly and easily move an existing Oracle Applications Release 12 from Solaris Sparc to Redhat Linux Platform, allowing us to utilize different hardware.

Document History

Author : Srinivas Ramineni

Title: Assistant Manger-ERP

Date Created : 05-Dec-2008

Company : Aurobindo Pharma ltd.,Hyderabad.

Migration of Oracle Applications Release 12 from Solaris to Linux Platform

This Document provides detailed steps on how we migrated our existing Oracle Applications Release 12 both Application & Database Tiers from Solaris Sparc Platform to Redhat Linux Platform.

This Process consists of Eight discrete steps. Each Step Covered in a Separate Section in this Document.

· Section-1: Prerequisites

· Section-2: Prepare the Source System

· Section-3: Prepare a target Release 12 database instance

· Section-4: Export the source Release 12 database instance

· Section-5: Import the Release 12 database instance

· Section-6: Update the imported Release 12 database instance

· Section-7: Prepare the Target Application Tier

· Section-8: Prepare The target Instance

Section-1: Prerequisites:

1. Apply the Applications consolidated export/import utility patch

Apply patch 6924477 to the source administration server node.

2. Apply latest Applications database preparation scripts patch

Apply patch 6342289 to every application tier server node in the source system.

3. Apply Platform Migration Patches
6903505 : QRMP:NOT ABLE TO APPLY R12 PLATFORM SPECIFIC PATCH DURING PLATFORM MIGRATION

6156498 : MIGRATION: CHANGES NEEDED TO ADCLONECTX.PL SCRIPT

4. Apply Additional Patches (Conditional)
6767273 : INSTALLING R12.AD.A.DELTA.4 FAILS FOR OFF-CYCLE PRODUCTS (AD)

5. Run AutoConfig on the Source System

cd $ADMIN_SCRIPTS_HOME

adautocfg.sh apps/apps

6. Run adpreclone on the Source System

Log on to the source system as the APPLMGR user, and run the following commands on each node that contains an APPL_TOP:
cd <INST_TOP>/admin/scripts
perl adpreclone.pl appsTier

7. Maintain Snapshot Information
Run adadmin à Maintain Application Files à Maintain snapshot information àupdate current view snapshot.

8. Identify Technology Stack Updates

To find the applied patches on the Oracle Applications technology stack run this script

Note:Ensure "APPLRGF" variable is set in environment. If not, set it to the same value as "APPLTMP".

Cd $FND_TOP/patch/115/bin

Run the utility as follows:

perl $FND_TOP/patch/115/bin/TXKScript.pl

-script=$FND_TOP/patch/115/bin/txkInventory.pl

-txktop=$APPLTMP  - contextfile=$CONTEXT_FILE

-appspass=apps

-outfile=$APPLTMP/Report_Inventory.txt

-reporttype=text

Section-2: Prepare the Source System:

1. Create a working directory

$ mkdir /xxx/xxxx/expimp

2. Generate and upload the manifest of customer-specific files

1. Generate customer-specific manifest file

Note: Log in to your Source System Applications Node as the APPLMGR user and Source the APPL_TOP environment file

cd <AD_TOP>/bin

perl adgenpsf.pl

above script generate the file adgenpsf.txt under <APPL_TOP>/admin/<TWO_TASK>/out.

2. Upload Manifest File for generation to target Linux/Unix platforms

Go to http://updates.oracle.com/PlatformMigration

3. Copy the Source System to the Target System

Copy the Application Tier file system from the Source Applications System to the Target Node by executing the following steps in the order listed.

· <APPL_TOP>

· <COMMON_TOP>/clone

· <COMMON_TOP>/java

· <COMMON_TOP>/_pages

· <COMMON_TOP>/webapps

· <COMMON_TOP>/util

Ex: scp -r /UATtlsvol01/oracle/UAT/apps/apps_st/ tlsdev:/appldev/oracle/DEV/apps/

Note: Change Owner ship

4. Generate target database instance creation script aucrdb.sql

$ sqlplus system/<system password> \

@$AU_TOP/patch/115/sql/auclondb.sql 10

It creates aucrdb.sql in the current directory.

5. Record Advanced Queue settings

Copy the auque1.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node

Run as Database Owner

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @auque1.sql

It generates auque2.sql.

6. Create parameter file for tables with long columns

Copy the aulong.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node

Run as Database Owner

$ sqlplus /nolog

SQL> connect system/<system password>;

SQL> @aulong.sql

It generates aulongexp.dat.

7. Remove rebuild index parameter in spatial indexes

SQL> select * from dba_indexes where index_type='DOMAIN' and

upper(parameters) like '%REBUILD%';

To remove the rebuild index parameter, use SQL*Plus to connect to the source database as the owner of the index and run the following command:

SQL> alter index <index name> rebuild parameters <parameters>

where <parameters> is the original parameter set without the rebuild_index parameter.

Ex: ALTER INDEX MST.MST_MD_ADM_BNDS_N1 rebuild parameters ('sdo_indx_dims=2 sdo_rtr_pctfree=10 TABLESPACE=APPS_TS_TX_IDX');

Section-3: Prepare a target Release 12 database instance:

1. Create target Oracle 10g Oracle home

If you choose to use Rapid Install, you must use Rapid Install Release 12.0.0. As the owner of the Oracle RDBMS file system, start the Rapid Install wizard by typing:

$ rapidwiz -techstack

Choose the "10gR2 RDBMS" option in the techstack components window and provide the details for the new Oracle home.

Ex: /oradev/oracle/DEV

2. Create the target initialization parameter file and CBO parameter file

Copy the backup copy of InitDEV.ora file to the $ORACLE_HOME/dbs directory

Note: 1. Ensure that the undo_tablespace parameter in the initialization parameter file of the target database instance matches with the default undo tablespace set in the aucrdb.sql script.

2. Ignore the initialization parameters that pertain to the native compilation of PL/SQL code. You will be instructed to add them later, if necessary.

3. Create a working directory

$ mkdir /xxx/expimp

4. Create the target database instance

Copy the aucrdb.sql script from the source administration server node to the working directory in the target database server node.

Update the script on the target database server node with any necessary changes to the directory structures for the log file(s), data file(s), or tablespaces, reflecting the layout of the target database server node.

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> spool aucrdb.log;

SQL> startup nomount;

SQL> @aucrdb.sql

SQL> exit;

Note: When the target database instance has been created, restart the database instance.

5. Copy database preparation scripts to target Oracle home

Copy the following files from the $APPL_TOP/admin directory of the source administration server node to the working directory in the target database server node

1. addb1020.sql

2. adsy1020.sql

3. adjv1020.sql

4. admsc1020.sql

6. Set up the SYS schema

The addb1020.sqlscript sets up the SYS schema for use with the Applications.

$ sqlplus "/ as sysdba" @addb1020.sql

7. Set up the SYSTEM schema

The adsy1020.sql script sets up the SYSTEM schema for use with the Applications.

$ sqlplus system/<system password> @adsy1020.sql

8. Install Java Virtual Machine

The adjv1020.sql script installs the Java Virtual Machine (JVM) in the database

$ sqlplus system/<system password> @adjv1020.sql

Note: This script can be run only once in a given database instance, because the scripts that it calls are not rerunnable.

9. Install other required components

The admsc1020.sql script installs the following required components in the database: ORD, Spatial, XDB, OLAP, Data Mining, interMedia, and ConText.

$ sqlplus system/<system password> \

@admsc1020.sql FALSE SYSAUX TEMP

10. Disable automatic gathering of statistics

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the target database server node.

$ sqlplus "/ as sysdba"

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> exit;

Section-4: Export the source Release 12 database instance

1. Create the export parameter file

Copy $AU_TOP/patch/115/import/auexpdp.dat from the source administration server node to the working directory in the source database server node

Create a directory in the system schema that corresponds to the directory specified in the template.

$ sqlplus system/<system password>

SQL> create directory dmpdir as '/u01/expimp';

Note: Do not change the other parameters.

2. Shut down Applications server processes

Shut down all Applications server processes except the database and the Net8 listener for the database.

3. Grant privilege to source system schema

$ sqlplus ‘/as sysdba’

SQL> grant EXEMPT ACCESS POLICY to system;

4. Export the Applications database instance

$ expdp system/<system password> parfile=auexpdp.dat

Note: the export runs for several hours.

5. Export tables with long columns

$ exp parfile=aulongexp.dat

6. Export tables with XML type columns

Copy $AU_TOP/patch/115/import/auxmlexp.dat from the source administration server to the working directory in the source database server node

$ exp parfile=auxmlexp.dat

7. Revoke privilege from source system schema

SQL> revoke EXEMPT ACCESS POLICY from system;

Section-5: Import the Release 12 database instance

1. Create the import parameter files

Copy auimpdp.dat, aufullimp.dat, and auimpusr.dat from the $AU_TOP/patch/115/import directory in the source administration server node to the working directory in the target database server node.

Note: Make sure that the directory, dumpfile, and logfile parameters in auimpdp.dat and auimpusr.dat are set properly.

Create a directory in the system schema with the name set to the directory

$ sqlplus system/<system password>

SQL> create directory dmpdir as '/XXX/expimp';

2. Copy the export dump files

Copy the export dump files from the source database server node to the working directory in the target database server node.

3.Import the users into the target database

Start an import session on the target database server node using the customized import parameter file.

$ impdp system/<system password> parfile=auimpusr.dat

4. Grant Unlimited on Tablespace “APPS_TS_TX_DATA”

alter user AMS quota unlimited on APPS_TS_TX_DATA;

alter user AMS quota unlimited on APPS_TS_SEED;

alter user APPLSYS quota unlimited on APPS_TS_TX_DATA;

alter user AR quota unlimited on APPS_TS_TX_DATA;

alter user BEN quota unlimited on APPS_TS_TX_DATA;

alter user BOM quota unlimited on APPS_TS_TX_DATA;

alter user CN quota unlimited on APPS_TS_TX_DATA;

alter user CSI quota unlimited on APPS_TS_TX_DATA;

alter user FA quota unlimited on APPS_TS_TX_DATA;

alter user GMA quota unlimited on APPS_TS_TX_DATA;

alter user GMD quota unlimited on APPS_TS_TX_DATA;

alter user HR quota unlimited on APPS_TS_TX_DATA;

alter user HXC quota unlimited on APPS_TS_TX_DATA;

alter user IES quota unlimited on APPS_TS_TX_DATA;

alter user IGI quota unlimited on APPS_TS_TX_DATA;

alter user IGS quota unlimited on APPS_TS_TX_DATA;

alter user IGW quota unlimited on APPS_TS_TX_DATA;

alter user INV quota unlimited on APPS_TS_TX_DATA;

alter user JTF quota unlimited on APPS_TS_TX_DATA;

alter user JTF quota unlimited on APPS_TS_ARCHIVE;

alter user MSC quota unlimited on APPS_TS_TX_DATA;

alter user OE quota unlimited on APPS_TS_TX_DATA;

alter user OKE quota unlimited on APPS_TS_TX_DATA;

alter user OSM quota unlimited on APPS_TS_TX_DATA;

alter user PA quota unlimited on APPS_TS_TX_DATA;

alter user PO quota unlimited on APPS_TS_TX_DATA;

alter user RG quota unlimited on APPS_TS_TX_DATA;

alter user RLM quota unlimited on APPS_TS_TX_DATA;

alter user WMS quota unlimited on APPS_TS_TX_DATA;

alter user WSH quota unlimited on APPS_TS_TX_DATA;

alter user AZ quota unlimited on APPS_TS_TX_DATA;

alter user BIS quota unlimited on APPS_TS_TX_DATA;

alter user CZ quota unlimited on APPS_TS_TX_DATA;

alter user BIC quota unlimited on APPS_TS_TX_DATA;

alter user BIS quota unlimited on APPS_TS_TX_DATA;

alter user CZ quota unlimited on APPS_TS_TX_DATA;

alter user FV quota unlimited on APPS_TS_TX_DATA;

alter user WSH quota unlimited on APPS_TS_TX_DATA;

alter user ALR quota unlimited on APPS_TS_TX_DATA;

alter user APPS quota unlimited on APPS_TS_TX_DATA;

5. Import tables with long columns into the target database

Modify the aufullimp.dat file with the following:

Set userid to "sys/<sys password> as sysdba".

Set file to the dump file containing the long tables (longexp by default).

Set the log file appropriately.

Leave the ignore parameter commented out.

Import the tables using the following command:

$ imp parfile=aufullimp.dat

Note: You will get failures for the triggers as the dependent tables have not yet been imported

6. Import the Applications database instance

$ impdp system/<system password> parfile=auimpdp.dat

Typically, import runs for several hours.

7. Import triggers into the target database

Modify the aufullimp.dat file with the following:

Set userid to "sys/<sys password> as sysdba".

Set file to the dump file containing the long tables (longexp by default).

Change the log file name.

Uncomment the ignore parameter.

Add a line with the parameter "rows=n".

Start an import session on the target database server node using the customized import parameter file. Use the following command:

$ imp parfile=aufullimp.dat.

Section-6: Update the imported Release 12 database instance

1. Reset Advanced Queues

Copy the auque2.sql script from the working directory in the source database server node to the working directory in the target database server node

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @auque2.sql

2. Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql from the administration server node to the working directory in the database server node.

$ sqlplus "/ as sysdba" @adgrants.sql <APPLSYS schema name>

3. Grant create procedure privilege on CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node.

$ sqlplus apps/<APPS password> @adctxprv.sql <SYSTEM password> CTXSYS

Section-7: Prepare the Target Application Tier

1. Install JDK and InfoZIP Utilities on the Target System
If not already installed, the following utilities must be installed on the Target System

1.Install JDK 1.5 or higher into the JDK_HOME.

2. InfoZip UNZIP version 5.52

3.InfoZip ZIP version Zip 2.30

Ex: which java

Download & install jdk-1_5_0_08-linux-i586-rpm.bin

2. Copy the Source System Context File to the Target System

3. Clone the Applications Context File on the Target System
Log on to the Target System as the APPLMGR user and run the following commands:

1. Create a pairsfile (text file) with the following values:

s_systemname=DEV

s_dbhost=tlsdev

s_dbSid=DEV

s_jdktop=/usr/java/jdk1.5.0_08

s_jretop=/usr/java/jdk1.5.0_08/jre

2. Generate the Target System Context File:

cd <COMMON_TOP>/clone/bin

perl adclonectx.pl migrate java=<JDK_HOME> pairsfile=<pairsfile> contextfile=<Source Applications Context File>

ex: perl adclonectx.pl migrate java=/usr/java/jdk1.5.0_08 pairsfile=/home/appldev/pairsfile.txt contextfile=/home/appldev/UAT_tlsuat.xml

Note: The Clone Context tool will ask for all the new mount points on the Target migration node.

4. Install the Middle Tier Technology Stack

Run the Ra Wizard with the -techstack option to install the Oracle Applications Technology Stack.

Use the Target System context file created in the previous step:

cd <StageR12>/startCD/Disk1/rapidwiz

./rapidwiz -techstack

5. Run AutoConfig setup phase on the Target System

Execute the INSTE8_SETUP phase of AutoConfig with the new context file.

This will create the environment files required for the AutoPatch session:

cd <AD_TOP>/bin

./adconfig.sh run=INSTE8_SETUP contextfile=<Target System Context File>

Ex: ./adconfig.sh run=INSTE8_SETUP contextfile=/appldev/oracle/DEV/inst/apps/DEV_tlsdev/appl/admin/DEV_tlsdev.xml

6. Download and apply the customer-specific update Patch with AutoPatch

adpatch options=hotpatch,phtofile

Note: In order to successfully execute autopatch on the new platform, you must use the adpatch executable included on this patch. For Linux: 64

7. Regenerate File System Objects

Source the APPL_TOP environment file and perform the following tasks to regenerate the platform dependent files on the Target System:

1. If migrating an Application tier that starts Forms, run the following script:

cd <AD_TOP>/bin

./adgensgn.sh <Apps User>/<Apps Password>

2. Run adadmin to generate messages, forms, reports and product jar files.

8. Run AutoConfig to complete the Target System configuration

cd <AD_TOP>/bin

./adconfig.sh contextfile=<Target System Context File>

Note: On this last step, the database will be updated to reflect the new Target System profile.

Section-8: Final preparation

1. Implement and run AutoConfig

Go through Doc: 387859.1 Section:6

Notepad : 1.Shut down all processes, including the database and the listener, and restart them to load the new environment settings

2. Take the necessary Backups

2. Gather statistics for SYS schema

$ sqlplus "/ as sysdba"

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> shutdown normal;

SQL> startup;

SQL> exit;

Note: Make sure that you have at least 1.5 GB of free default temporary tablespace.

3. Re-create custom database links

$ sqlplus apps/<apps password>

SQL> select db_link from dba_db_links;

The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist, should have been updated with the new port number by AutoConfig in the previous step.

$ sqlplus apps/<apps password>

create database link APPS_TO_APPS.TRIDENT.COM connect to APPS identified by APPS using 'DEV';

create database link EDW_APPS_TO_WH.TRIDENT.COM connect to APPS identified by APPS using 'DEV';

4. Create ConText and AZ objects

The consolidated export/import utility patch that you applied to the source administration server node contains a perl script, dpost_imp.pl, that you can run to generate an AutoPatch driver file.

$ perl $AU_TOP/patch/115/bin/dpost_imp.pl <driver file> 10

Once the driver file has been generated, use AutoPatch to apply it on the target administration server node.

5. Import tables with XML type columns into the target database (Not Required)

Modify the aufullimp.dat file with the following:

Set userid to "az/<az password>"

Set file to the dump file containing the tables with XML types (xmlexp by default).

Change the log file name.

Comment out the ignore and rows parameters.

Start an import session on the target database server node using the customized import parameter file. Use the following command:

$ imp parfile=aufullimp.dat

6. Populate CTXSYS.DR$SQE table

$ sqlplus apps/<apps password>

SQL> exec icx_cat_sqe_pvt.sync_sqes_for_all_zones;

7. Compile invalid objects

$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql

8. Maintain Applications database objects

Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks:

1.Compile flexfield data in AOL tables

2. Recreate grants and synonyms for APPS schema

9. Start Applications server processes

Start all the server processes on the target Applications system. You can allow users to access the system at this time.

10. Create DQM indexes

Create DQM indexes by following these steps:

Log on to Oracle Applications with the "Trading Community Manager" responsibility

Click Control > Request > Run

Select "Single Request" option

Enter "DQM Staging Program" name

Enter the following parameters:

Number of Parallel Staging Workers: 4

Staging Command: CREATE_INDEXES

Continue Previous Execution: NO

Index Creation: SERIAL

Click "Submit"

11. Run Gather Applications statistics program

12. Update Workflow configuration settings as per the Metalink Doc: 438086.1

References

1. Export/Import Process for Oracle E-Business Suite Release 12 Using 10gR2 Note:454616.1

2. Platform Migration with Oracle Applications Release 12 Note:438086.1

Encrypting Network Traffic Using Advanced Security - R12

2008-11-28T03:27:00.000-08:00

To configure the E-Business Suite Release 12 to encrypt network traffic sent over the TNS protocol we use the Advanced Networking Option (ANO) that is part of the Advanced Security Option (ASO) of the Oracle database and included with the Release 12 E-Business Suite Technology Suite.

TNS (Transparent Networking Substrate) is an Oracle protocol running on top of a number of supported network protocols - typically TCP/IP. ANO/ASO encryption prevents sending TNS traffic "in-the-clear" over a network connection.
CERTIFICATION: This configuration is certified for Oracle Applications Release 12 using Forms listener Servlet (the default mode) on the following platforms: Linux-x86 Solaris-32 AIX4-32 Tru64 HP-UX Windows-32 .

Advanced security encryption can be configured, based on a combination of client and server configuration parameters as REJECTED, ACCEPTED, REQUESTED or REQUIRED.

Oracle has certified EBS Release 12 with the server parameter set to REQUIRED - this ensures that all EBS TNS network traffic is being encrypted. Although ANO/ASO supports a number of different encryption algorithms, the supported algorithms are version dependent.

For Release 12 certification the the server's preference is set to AES256, AES192, 3DES168.
Steps to enable the encryption in each of the different ORACLE_HOME’s in an EBS deployment.

Step 1 - Shutdown Middle Tier Server Processes and Database Listener

1. On the database server node, shut down the database listener:
$ORACLE_HOME/appsutil/scripts/ DEV_tlsdev/addlnctl.sh stop DEV

2. On each middle tier server, shut down all processes or services:
$ADMIN_SCRIPTS_HOME/adstpall.sh apps user / apps password

Step 2 - DB Tier Changes

1. Logon to the DB Tier server as the file system owner.

2. Source the DB Tier environment file located in Oracle Home directory.

3. Take a backup of the $TNS_ADMIN/sqlnet_ifile.ora file.

4. Open the $TNS_ADMIN/sqlnet_ifile.ora file with the editor of your choice and add the following lines replacing [crypto seed] with a string consisting of 10 - 70 alphanumeric characters of your choosing. The characters that form the value fo this parameter will be used when generating cryptographic keys. The more random the characters entered into this field are, the stronger the keys are.

SQLNET.ENCRYPTION_TYPES_SERVER=(AES256, AES192, 3DES168) SQLNET.ENCRYPTION_SERVER=REQUIRED SQLNET.CRYPTO_SEED=[crytpo seed]

Note: Oracle Corporation recommends that you enter as many characters as possible, up to 70, for the crypto seed to make the resulting key more random and therefore stronger.

5. After the changes have been made, restart the listener:
$ORACLE_HOME/appsutil/scripts/ DEV_tlsdev/addlnctl.sh start DEV

Step 3 - Create $TNS_ADMIN/sqlnet.ora and sqlnet_ifile.ora files on each Middle Tier.

By default, the Oracle Applications Middle Tier installations do not have either a sqlnet.ora or sqlnet_ifile.ora file so we will need create these. We keep the ANO/ASO directives in the sqlnet_ifile.ora file to isolate it from any future autoconfig updates that affect the sqlnet.ora file.

1. Logon to the Middle Tier server as the file system owner.
2. Source your middle tier environment file (APPSDEV_tlsdev.env) located in the APPL_TOP directory.
3. Navigate to the $TNS_ADMIN directory.
4. Use the editor of you choice to create the sqlnet.ora file with the following lines:

#######################################################################
sqlnet.ora file for middle tier sqlnet encryption with Advanced SSL Configuration
######################################################################
IFILE = /sqlnet_ifile.ora
5. Use the editor of you choice to create the sqlnet_ifile.ora file with the following lines:

######################################################################### sqlnet_ifile.ora for middle tier sqlnet encryption with Advanced SSL Configuration
#######################################################################
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256, AES192, 3DES168)
SQLNET.ENCRYPTION_CLIENT=REQUIREDSQLNET.CRYPTO_SEED=

somelongandrandomstringfordeploymentUpTo70characters

Note: the SQLNET.CRYPTO_SEED does not need to be the same as used on the db tier.

Step 4 - Update the Context File

Use the Oracle Applications Manager (OAM) Context Editor to change the SSL related variables on each middle tier server as shown As Below:

Set s_custom_dbc_params value as ENCRYPTION_CLIENT=REQUIRED ENCRYPTION_TYPES_CLIENT=(3DES168)

Step 5 - Run Autoconfig 1. Run autoconfig on each middle tier server:
$ADMIN_SCRIPTS_HOME/adautocfg.sh appspass=apps password

Step 6 - Restart the Middle Tier Services1. On each middle tier server, restart all processes and services: $ADMIN_SCRIPTS_HOME/adstrtall.sh apps user / apps password

Ref: Metalink Doc no: 376700.1

How to DROP an Oracle Database?

2008-11-28T03:22:00.000-08:00

You can do it at the OS level by deleting all the files of the database.
The files to be deleted can be found using:

1) select * from dba_data_files;
2) select * from v$logfile;
3) select * from v$controlfile;
4) archive log list
5) initSID.ora
6) In addition you can clean the UDUMP, BDUMP, scripts etc

Clean up the listener.ora and the tnsnames.ora.
Make sure that the oratab entry is also removed.
With dbca you can easily drop an oracle database.
However you may wish manually to delete a database. The following steps will help you to manually drop a database.

1)Set the Oracle SID of the database which you interested to drop.
On my system it is,

$export ORACLE_SID=dbase

2)Connect to SQL*Plus as SYSDBA privilege.

$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 13 02:41:24 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

3)Mount the database.

SQL> startup force mount

ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 92274728 bytes
Database Buffers 67108864 bytes
Redo Buffers 6369280 bytes
Database mounted.

4)Enable restricted session.

SQL> alter system enable RESTRICTED session;
System altered.

5)Drop the database.

SQL> drop database;
Database dropped.

Upgrade Oracle Database 9.2.0.1 to Oracle Database 9.2.0.6 (patchset 3948480)

2008-10-06T22:44:00.000-07:00

Yesterday I have Completed Platform migration on payroll database. While doing the migration, I have upgraded Oracle Database 9.2.0.1 to Oracle Database 9.2.0.6
1. Log in to the Oracle 9.2.0.1 Database server machine as the oracle user (administrator role in case of windows) on Unix.
2. Make sure that your environment contains the correct values for the ORACLE_HOME and ORACLE_SID variables.
3. Shut down any existing Oracle9i database instances with normal or immediate priority As the oracle user:
sqlplus /nolog
SQL> connect / as sysdba
SQL>shutdown immediate

4. Download the patchset & Extract the 9.2.0.6 patch set (3948480 into Stage directory.
5. Install the 9.2.0.6 patch set
cd /Stage/3948480/Disk1/
Run Setup.exe
Welcome
Next ->
Specify File Locations
Source Path: choose default
Destination Name: ORACLE_HOME (select from dropdown list, which databse you want to upgrade)
Destination Path: /oracle/products/payroll
Next ->
Summary
Install ->
End of Installation
Exit

Pre Installation Steps before Database Upgrade
1. If JServer is part of the installation, ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.
2. For RAC installations, ensure that there is at least 50 MB of free space allocated to the XDB tablespace.
3. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
1. Start the database:
2. SQL> STARTUP
3. If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
SQL> SHOW PARAMETER PFILE;
This command displays the name and location of the server parameter file or the initialization parameter file.
Note:The UF installation uses an spfile (Oracle9i Database will automatically use the ORACLE_HOME/dbs/spfilewebct.ora file if it exists), but we want to make sure that the pfile and the file match up to reduce confusion.
4. Determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
5. If the system is using a server parameter file:
a. If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 250 MB:
b. SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='250M' SCOPE=spfile;
c.
d. If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 250 MB:
e. SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='250M' SCOPE=spfile;

6. If the system uses an initialization parameter file, if necessary, change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 250 MB in the initialization parameter file (initsid.ora).
7. Shut down the database:
SQL> SHUTDOWN

Upgrading the Database

Perform the following steps on every database associated with the upgraded Oracle home:

1. Log in as the oracle user:
sqlplus /nolog
SQL> connect / as sysdba
2. Enter the following SQL*Plus commands:
SQL> startup migrate
SQL> spool patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> spool off

3. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.
4.list provides the version and status of each SERVER component in the database. If necessary, rerun the catpatch.sql script after correcting any problems.
5. Restart the database:
SQL> shutdown immediate
SQL> startup
5. Execute the following script to recompile all invalid PL/SQL packages now. (This step is optional but recommended.)
SQL> @?/rdbms/admin/utlrp.sql
6. Restart the listener & other processes

OERR Utility to Diagnosis the Problem

2008-09-23T22:25:00.000-07:00

OERR Stands for Oracle Error.It is used to diagnosis the problem

oradev@aplerp4 # cd $ORACLE_HOME/bin
oradev@aplerp4 # oerr ora 00060
00060, 00000, "deadlock detected while waiting for resource"
// *Cause: Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
// involved. Retry if necessary.

oradev@aplerp4 # oerr ora 01555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
// overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
// setting. Otherwise, use larger rollback segments

oradev@aplerp4 # oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
Srinivas Ramineni
Oracle Applications DBA

JSP Pages Hanging in R12 After Removing Cached Class Files in _pages

2008-09-23T22:21:00.000-07:00

In Release 12 after Removing the compiled class files from the JSP pages in the directory $COMMON_TOP/_pages and Bounce of Apache server,calling JSP pages results in hanging 'blank' screen.

In Release 11i when performing the same steps new class files are created automatically when the JSP page is called from a browser session and JSP was rendered fine.

In Release 12 it's observed that when calling the JSP no class file is created in the diretciry $COMMON_TOP/_pages

1. Change the value for the main_mode parameter to recompile (instead of justrun)

This can be achieved by doing the following actions
• Use the Context editor to change the value for "s_jsp_main_mode" in the .xml file used by autoconfig and change value from justrun to recompile
• Run Autoconfig to propagate the changes to the configuration files
• Verify that now the $INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml has

main_mode

recompile

• Test the scenario failing before.
• See that now a new _.class is created when the JSP page is called.

After A Clone, Blank AppsLogin / AppsLocalLogin Page in R12?

2008-09-23T22:12:00.000-07:00

while try to access the Applications we are facing the Blank Apps Login, After Completion of the clone Successfully.

The cause might be explained because if the system was not able to access the classes this may have as root cause jsp / class corruption or wrong $CLASSPATH.

To implement the solution, please execute the following steps:

A. Set the JSP compilation to Automatic using the following steps:
1. Use the vi editor to edit the $CONTEXT_NAME.xml context file and change the value for "s_jsp_main_mode" from "justrun"or “Compile” to "recompile"
Note: Backup the context file before editing it.

2. Run Autoconfig to propagate the changes to the configuration files.

3. Verify that now the
$INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml has

main_mode

recompile

4. Restart the Middle Tier services and access the applications to load JSP's which will cause them to be recompiled on the run.
5. After successfully accessing the applications, use the vi editor to edit the $CONTEXT_NAME.xml and revert the value for "s_jsp_main_mode" to "justrun" in order not to hamper the performance of your system.
6. Run AutoConfig to propagate the changes.