How to Find AD Patchset Level

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.

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';

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.

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.

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

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.

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