The AD patch level can be found in the report generated by the following command.
sqlplus Apps/AppsPwd @$AD_TOP/sql/adutconf.sql
The output gives you the Oracle Applications Database Configuration Report. This report contains Product Installation Status, Version Info and Patch Level. This report is useful for Application Information Gathering.
Thursday, November 20, 2008
Wednesday, July 23, 2008
How to DROP a Datafile from a Tablespace
Starting with version 10.2 and higher You can now alter tablespace drop datafile (except first datafile of a tablespace).
Eg:
Alter tablespace TBS01 drop datafile '/dev01/oradata/dev01/data_04.dbf';
Eg:
Alter tablespace TBS01 drop datafile '/dev01/oradata/dev01/data_04.dbf';
Wednesday, July 16, 2008
Enabling System Managed Undo (SMU)
As of 9iR2, Oracle Applications Release 11i supports only the use of system managed undo (SMU). SMU is more efficient than traditional rollback segments and reduces the possibilities of “snapshot too old errors.” The following steps should be used to enable system managed undo.
Step 1:
Capture the tablespace names being used for the existing rollback segments from dba_rollback_segs. Capture the datafiles and sizes being used for the rollback tablespaces from dba_data_files.
SQL> select segment_name, tablespace_name from dba_rollback_segs;
SQL> select file_name, tablespace_name, bytes from dba_data_files where tablespace_name = '';
where is the tablespace name used by a rollback segment.
Step 2:
Drop all the private and public rollback segments except for the SYSTEM rollback segment.
SQL> alter rollback segment offline;
SQL> drop rollback segment ;
Step 3:
Drop the corresponding tablespace(s) where the rollback segments reside.
SQL> alter tablespace offline;
SQL> drop tablespace ;
Step 4:
Create a new System Managed Undo tablespace per instance as follows:
create undo tablespace APPS_UNDOTS1 datafile '' size reuse extent management local ;
For the path and the size values, you should reuse the same files and sizes which were used in the tablespaces which were dropped in step 2. The sizes and file names were captured in step 1.
Step 5:
Set the following database initialization parameters:
undo_management = AUTO
undo_tablespace = APPS_UNDOTS1
Refer to the sizing table (Database Initialization Parameters Sizing section) for the appropriate setting of undo_retention.
Step 6:
Remove the database initialization parameter rollback_segments.
Step 7:
Restart the database so that the system managed undo takes effect.
Step 1:
Capture the tablespace names being used for the existing rollback segments from dba_rollback_segs. Capture the datafiles and sizes being used for the rollback tablespaces from dba_data_files.
SQL> select segment_name, tablespace_name from dba_rollback_segs;
SQL> select file_name, tablespace_name, bytes from dba_data_files where tablespace_name = '';
where is the tablespace name used by a rollback segment.
Step 2:
Drop all the private and public rollback segments except for the SYSTEM rollback segment.
SQL> alter rollback segment offline;
SQL> drop rollback segment ;
Step 3:
Drop the corresponding tablespace(s) where the rollback segments reside.
SQL> alter tablespace offline;
SQL> drop tablespace ;
Step 4:
Create a new System Managed Undo tablespace per instance as follows:
create undo tablespace APPS_UNDOTS1 datafile '' size reuse extent management local ;
For the path and the size values, you should reuse the same files and sizes which were used in the tablespaces which were dropped in step 2. The sizes and file names were captured in step 1.
Step 5:
Set the following database initialization parameters:
undo_management = AUTO
undo_tablespace = APPS_UNDOTS1
Refer to the sizing table (Database Initialization Parameters Sizing section) for the appropriate setting of undo_retention.
Step 6:
Remove the database initialization parameter rollback_segments.
Step 7:
Restart the database so that the system managed undo takes effect.
Tuesday, July 15, 2008
How to trace a specific session?
Tracing a specific session instead of the entire system allows you to get smaller and very readable tracefiles.
a. Open two sessions - one where you will control the trace and one where you can run the processes to be traced.
b. Connect to the database with dba privileges
SQL> connect / as sysdba
select v$process.SPID, v$process.USERNAME from v$process, v$session where v$session.PADDR=v$process.ADDR order by v$session.logon_time;
SPID USERNAME
--------- ---------------
863 oracle
865 oracle
867 oracle
..
5104 oracle
5159 oracle
If you run this query just before and after your logon to your other session, you should be able to find the SPID of your connection. Make sure you don't trace the wrong process.
c. Suppose our SPID is 5159.
SQL>oradebug SETOSPID 5159
Statement processed.
SQL>oradebug event 10046 trace name context forever, level 12
The second command will include the bind variables in the trace file.
d. Perform the actions you want to trace in your other session, then exit server manager. Your tracefile now only contains those statements which are relevant.
a. Open two sessions - one where you will control the trace and one where you can run the processes to be traced.
b. Connect to the database with dba privileges
SQL> connect / as sysdba
select v$process.SPID, v$process.USERNAME from v$process, v$session where v$session.PADDR=v$process.ADDR order by v$session.logon_time;
SPID USERNAME
--------- ---------------
863 oracle
865 oracle
867 oracle
..
5104 oracle
5159 oracle
If you run this query just before and after your logon to your other session, you should be able to find the SPID of your connection. Make sure you don't trace the wrong process.
c. Suppose our SPID is 5159.
SQL>oradebug SETOSPID 5159
Statement processed.
SQL>oradebug event 10046 trace name context forever, level 12
The second command will include the bind variables in the trace file.
d. Perform the actions you want to trace in your other session, then exit server manager. Your tracefile now only contains those statements which are relevant.
DBControl Configuration in 10g
Reconfigure DBControl Configuration Files Without Losing The Repository Data :
$ORACLE_HOME/bin/emca -deconfig dbcontrol db
$ORACLE_HOME/bin/emca -config dbcontrol db
Recreating DB Control with Losing the Repository Data:
$ORACLE_HOME/bin/emca -deconfig dbcontrol db -repos drop
$ORACLE_HOME/bin/emca -config dbcontrol db -repos create
To stop & start the Database Control service run:
$ORACLE_HOME/bin/emctl stop dbconsole
$ORACLE_HOME/bin/emctl start dbconsole
A complete list of available emctl commands can be obtained by just typing:
ORACLE_HOME/bin/emctl
$ORACLE_HOME/bin/emca -deconfig dbcontrol db
$ORACLE_HOME/bin/emca -config dbcontrol db
Recreating DB Control with Losing the Repository Data:
$ORACLE_HOME/bin/emca -deconfig dbcontrol db -repos drop
$ORACLE_HOME/bin/emca -config dbcontrol db -repos create
To stop & start the Database Control service run:
$ORACLE_HOME/bin/emctl stop dbconsole
$ORACLE_HOME/bin/emctl start dbconsole
A complete list of available emctl commands can be obtained by just typing:
ORACLE_HOME/bin/emctl
Monday, July 14, 2008
Merging multiple patches into one integrated patch in oracle apps?
1. Review the readme files carefully.
The patch may also require manual steps.
2. Create directories.
In the patch top area, create a source directory and a destination directory. Choose any name for these directories.
3. Unzip patches.
Copy all the patches to be merged into the source directory and unzip them.
4. Run AD Merge Patch. (admrgpch)
Run AD Merge Patch and supply the arguments for the destination directory name and the source directory name. You also need to specify the merged patch name, or accept the default.
eg: admrgpch [source_directory] [destination_directory]
admrgpch -s [source_directory] -d [destination_directory] [-merge_name [pattern]]
5. Check AD Merge Patch log files.
After AD Merge Patch runs, check the admrgpch.log file for errors. The file is located in the current working directory (where AD Merge Patch was run).
6. Run the merged patch.
Once a merged patch is created, apply it just like a single patch.
The patch may also require manual steps.
2. Create directories.
In the patch top area, create a source directory and a destination directory. Choose any name for these directories.
3. Unzip patches.
Copy all the patches to be merged into the source directory and unzip them.
4. Run AD Merge Patch. (admrgpch)
Run AD Merge Patch and supply the arguments for the destination directory name and the source directory name. You also need to specify the merged patch name, or accept the default.
eg: admrgpch [source_directory] [destination_directory]
admrgpch -s [source_directory] -d [destination_directory] [-merge_name [pattern]]
5. Check AD Merge Patch log files.
After AD Merge Patch runs, check the admrgpch.log file for errors. The file is located in the current working directory (where AD Merge Patch was run).
6. Run the merged patch.
Once a merged patch is created, apply it just like a single patch.
How to find if a Sun Solaris OS is 32-Bit or 64-Bit ?
Type "isainfo -v" at the OS prompt.
If the output shows 2 entries, then its 64bit.
$ isainfo -v
64-bit sparcv9 applications
32-bit sparc applications
Else if it shows only one entry then its 32 bit.
$ isainfo -v
32-bit sparc applications
If the output shows 2 entries, then its 64bit.
$ isainfo -v
64-bit sparcv9 applications
32-bit sparc applications
Else if it shows only one entry then its 32 bit.
$ isainfo -v
32-bit sparc applications
Tuesday, July 8, 2008
How to gather statistics in Oracle apps from backend
Use the following command to gather schema statistics:
exec fnd_stats.gather_schema_statistics('ONT') < For a specific schema >
exec fnd_stats.gather_schema_statistics('ALL') < For all schemas >
exec fnd_stats.gather_schema_statistics('ONT') < For a specific schema >
exec fnd_stats.gather_schema_statistics('ALL') < For all schemas >
How can one improve Import/ Export performance?
EXPORT:
Set the BUFFER parameter to a high value (e.g. 2M)
Set the RECORDLENGTH parameter to a high value (e.g. 64K)
Stop unnecessary applications to free-up resources for your job.
If you run multiple export sessions, ensure they write to different physical disks.
DO NOT export to an NFS mounted filesystem. It will take forever.
IMPORT:
Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
Place the file to be imported on a separate physical disk from the oracle data files
Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
Set the LOG_BUFFER to a big value and restart oracle.
Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
Use COMMIT=N in the import parameter file if you can afford it
Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements
Remember to run the indexfile previously created
Set the BUFFER parameter to a high value (e.g. 2M)
Set the RECORDLENGTH parameter to a high value (e.g. 64K)
Stop unnecessary applications to free-up resources for your job.
If you run multiple export sessions, ensure they write to different physical disks.
DO NOT export to an NFS mounted filesystem. It will take forever.
IMPORT:
Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
Place the file to be imported on a separate physical disk from the oracle data files
Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
Set the LOG_BUFFER to a big value and restart oracle.
Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
Use COMMIT=N in the import parameter file if you can afford it
Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements
Remember to run the indexfile previously created
Tips for changing delimiter in excel
To change the File delimiter on your PC to a pipe(or anything) rather than a comma bring up your default options window by clicking Start => Settings => Control Panel => Regional Settings (aka Regional Options).
Click the "Number" tab.
In the "List Separator" field, replace the current default separator with the one you want to use (e.g. a pipe symbol I ).
Click "Apply" (if it is there) and "OK" to save the change and close the window. You can now save Excel files as pipe delimited files by simply doing the following in Excel:
Open or Create a new file in Excel.
Click File => Save As on the menu bar.
In the "File Type" drop down, select "CSV (Comma Delimited) *.csv".
Click the "Number" tab.
In the "List Separator" field, replace the current default separator with the one you want to use (e.g. a pipe symbol I ).
Click "Apply" (if it is there) and "OK" to save the change and close the window. You can now save Excel files as pipe delimited files by simply doing the following in Excel:
Open or Create a new file in Excel.
Click File => Save As on the menu bar.
In the "File Type" drop down, select "CSV (Comma Delimited) *.csv".
Duplicate data files
In order to find duplicate data files in your environemnt use the following sql:
select tablespace_name,file_name from dba_data_files a where 1< (select count(*) from
dba_data_files b where substr(a.file_name,24,60)=substr (b.file_name,24,60)) ;
To fix the duplicate datafiles follow the below procedure:
1. If the database is up, shut it down.
> SHUTDOWN IMMEDIATE
2. Copy the datafile to the new name/location at operating system level.
> cp
3. Mount the database.
> STARTUP MOUNT
This command will read the control file but will not mount the datafiles.
4. Rename the file inside Oracle.
> ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';
Do this for all the datafiles that were renamed or moved at the operating system level.
5. Open the database.
> ALTER DATABASE OPEN;
6. Query v$dbfile to confirm that the changes made were correct.
> SELECT * FROM V$DBFILE;
7. Remove the datafile(s) from the old location at the operating system level.
select tablespace_name,file_name from dba_data_files a where 1< (select count(*) from
dba_data_files b where substr(a.file_name,24,60)=substr (b.file_name,24,60)) ;
To fix the duplicate datafiles follow the below procedure:
1. If the database is up, shut it down.
> SHUTDOWN IMMEDIATE
2. Copy the datafile to the new name/location at operating system level.
> cp
3. Mount the database.
> STARTUP MOUNT
This command will read the control file but will not mount the datafiles.
4. Rename the file inside Oracle.
> ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';
Do this for all the datafiles that were renamed or moved at the operating system level.
5. Open the database.
> ALTER DATABASE OPEN;
6. Query v$dbfile to confirm that the changes made were correct.
> SELECT * FROM V$DBFILE;
7. Remove the datafile(s) from the old location at the operating system level.
Compiling library files(.pll) in Oracle Apps
1. Logon as applmgr to the application server where the forms are installed.
2. Change directory to $AU_TOP/resource where all the .pll reside.
cd $AU_TOP/resource
3. Syntax for the f60gen command is as follows:
f60gen module=.pll userid=/resource/.plx
module_type=library batch=no compile_all=special
4. Examples:
cd $AU_TOP/forms/US
Example 1:
f60gen module=FNDCONC.pll userid=APPS/APPS output_file=$AU_TOP/resource/FNDCONC.
plx module_type=library batch=no compile_all=special
Example 2:
f60gen module=FAXMAD.pll userid=FA/FA output_file=$AU_TOP/resource/FAXMAD.plx
module_type=library batch=no compile_all=special
2. Change directory to $AU_TOP/resource where all the .pll reside.
cd $AU_TOP/resource
3. Syntax for the f60gen command is as follows:
f60gen module=
module_type=library batch=no compile_all=special
4. Examples:
cd $AU_TOP/forms/US
Example 1:
f60gen module=FNDCONC.pll userid=APPS/APPS output_file=$AU_TOP/resource/FNDCONC.
plx module_type=library batch=no compile_all=special
Example 2:
f60gen module=FAXMAD.pll userid=FA/FA output_file=$AU_TOP/resource/FAXMAD.plx
module_type=library batch=no compile_all=special
Find Server CPU Information
To Find Server CPU Information in Solaris:
Execute:
/usr/platform/sun4u/sbin/prtdiag
Execute:
/usr/platform/sun4u/sbin/prtdiag
Thursday, June 26, 2008
Oracle Critical Patch Update Note for Oracle E-Business Suite
April 2006
360468.1 - Oracle Critical Patch Update April 2006 Pre-Installation Note for Oracle E-Business Suite
July 2006
372931.1 - E-Business Suite Critical Patch Update Note July 2006
October 2006
391564.1 - -Business Suite Critical Patch Update October 2006 Note
January 2007
402670.1 - Oracle E-Business Suite Critical Patch Update Note January 2007
April 2007
420072.1 - Oracle E-Business Suite Critical Patch Update Note April 2007
July 2007
432882.1 - Oracle E-Business Suite Critical Patch Update Note July 2007
October 2007
455294.1 - Oracle E-Business Suite Critical Patch Update Note October 2007
January 2008
467742.1 - Oracle E-Business Suite Critical Patch Update Note (January 2008)
April 2008
557157.1 - Oracle E-Business Suite Critical Patch Update Note April 2008
July 2008
605117.1 - Oracle E-Business Suite Critical Patch Update Note July 2008
October 2008
738921.1 - Oracle E-Business Suite Releases 11i and 12 Critical Patch Update Note (October 2008)
January 2009
738923.1 - Oracle E-Business Suite Releases 11i and 12 Critical Patch Update Note (January 2009)
April 2009
738923.1 - Oracle® E-Business Suite Releases 11i and 12 Critical Patch Update Knowledge Document (April 2009)
360468.1 - Oracle Critical Patch Update April 2006 Pre-Installation Note for Oracle E-Business Suite
July 2006
372931.1 - E-Business Suite Critical Patch Update Note July 2006
October 2006
391564.1 - -Business Suite Critical Patch Update October 2006 Note
January 2007
402670.1 - Oracle E-Business Suite Critical Patch Update Note January 2007
April 2007
420072.1 - Oracle E-Business Suite Critical Patch Update Note April 2007
July 2007
432882.1 - Oracle E-Business Suite Critical Patch Update Note July 2007
October 2007
455294.1 - Oracle E-Business Suite Critical Patch Update Note October 2007
January 2008
467742.1 - Oracle E-Business Suite Critical Patch Update Note (January 2008)
April 2008
557157.1 - Oracle E-Business Suite Critical Patch Update Note April 2008
July 2008
605117.1 - Oracle E-Business Suite Critical Patch Update Note July 2008
October 2008
738921.1 - Oracle E-Business Suite Releases 11i and 12 Critical Patch Update Note (October 2008)
January 2009
738923.1 - Oracle E-Business Suite Releases 11i and 12 Critical Patch Update Note (January 2009)
April 2009
738923.1 - Oracle® E-Business Suite Releases 11i and 12 Critical Patch Update Knowledge Document (April 2009)
How to prevent Concurrent Requests executing post rapid clone
1. Edit the $COMMON_TOP/clone/bin/adcfgclone.pl
2. Go to the end of the file
3. Change the following lines:-
print "\n Starting application Services for $s_dbSid:\n";
print "Running:\n";
print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/\n");
system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");
TO:
print "\nNOT Starting application Services for $s_dbSid:\n";
#print "Running:\n";
#print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/\n");
#system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");
4. Run "perl adcfgclone.pl appsTier" as normal.
The services will not start Automatically when the clone completes allowing data to be changed safely.
2. Go to the end of the file
3. Change the following lines:-
print "\n Starting application Services for $s_dbSid:\n";
print "Running:\n";
print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/
system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");
TO:
print "\nNOT Starting application Services for $s_dbSid:\n";
#print "Running:\n";
#print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/
#system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");
4. Run "perl adcfgclone.pl appsTier" as normal.
The services will not start Automatically when the clone completes allowing data to be changed safely.
How to Enable and Disable the archive log
To Disable the Archive Log:
change the following value to false in the init.ora file
log_archive_start=false
shutdown the database:
shutdown immediate
disable the archivelog:
startup mount
alter database noarchivelog;
alter database open;
archive log list;
To Enable the Archive Log:
change the following value to true in the init.ora file
log_archive_start=true
shutdown the database:
shutdown immediate
enable the archivelog:
startup mount
alter database archivelog;
alter database open;
archive log list;
archive log all;
change the following value to false in the init.ora file
log_archive_start=false
shutdown the database:
shutdown immediate
disable the archivelog:
startup mount
alter database noarchivelog;
alter database open;
archive log list;
To Enable the Archive Log:
change the following value to true in the init.ora file
log_archive_start=true
shutdown the database:
shutdown immediate
enable the archivelog:
startup mount
alter database archivelog;
alter database open;
archive log list;
archive log all;
How to find if the Oracle Client Software is 32-Bit or 64-Bit ?
You can find whether Oracle Client Software is 32-Bit or 64-Bit by using the following :
Check for the directories :
1) $ORACLE_HOME/lib32
2) $ORACLE_HOME/lib
% ls -l $ORACLE_HOME/lib32
% ls -l $ORACLE_HOME/lib
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit client.
If there is only an ORACLE_HOME/lib directory then it is 32 bit client.
Check for the directories :
1) $ORACLE_HOME/lib32
2) $ORACLE_HOME/lib
% ls -l $ORACLE_HOME/lib32
% ls -l $ORACLE_HOME/lib
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit client.
If there is only an ORACLE_HOME/lib directory then it is 32 bit client.
11i Product Info
To get the 11i Product Info you can execute the following Sql
sqlplus apps/apps @$AD_TOP/sql/adutconf.sql
sqlplus apps/apps @$AD_TOP/sql/adutconf.sql
Thursday, March 27, 2008
Find your ATG Rollup Patch Level
Please use the following query to find the ATG rollup Patch level in your application.
connect as apps:
SELECT (bug_number),
decode((bug_number),
'3438354','ATG_PF.H',
'4017300','ATG_PF.H RUP 1',
'4125550','ATG_PF.H RUP 2',
'4334965','ATG_PF.H RUP 3',
'4676589','ATG_PF.H RUP 4',
'5473858','ATG_PF.H RUP 5',
'5903765','ATG_PF.H RUP 6',
'6241631','ATG_PF.H RUP 7')
FROM ad_bugs
WHERE bug_number IN
('3384350',
'3438354',
'4017300',
'4125550',
'4334965',
'4676589',
'5473858',
'5903765',
'6241631')
order by bug_number desc
connect as apps:
SELECT (bug_number),
decode((bug_number),
'3438354','ATG_PF.H',
'4017300','ATG_PF.H RUP 1',
'4125550','ATG_PF.H RUP 2',
'4334965','ATG_PF.H RUP 3',
'4676589','ATG_PF.H RUP 4',
'5473858','ATG_PF.H RUP 5',
'5903765','ATG_PF.H RUP 6',
'6241631','ATG_PF.H RUP 7')
FROM ad_bugs
WHERE bug_number IN
('3384350',
'3438354',
'4017300',
'4125550',
'4334965',
'4676589',
'5473858',
'5903765',
'6241631')
order by bug_number desc
Subscribe to:
Posts (Atom)
