Trace a Concurrent Request And Generate TKPROF

Enable Tracing For The Concurrent Manager  Program
Select the Enable Trace Checkbox

Note : Checking the Trace Check box on the Concurrent Program gives an Event 10046
Level 8 trace. So even if the trace is set for Binds and Waits on the Submission form once the
concurrent program is encountered in the trace it will reset to level 8 so no binds
will be present in the trace after that point.
Turn On Tracing

• Responsibility: System Administrator
• Navigate: Profiles > System
• Query Profile Option Concurrent: Allow Debugging
• Set profile to Yes

Run Concurrent Program With Tracing Turned On

• Logon to the Responsibility that runs the Concurrent Program
• In the Submit Request Screen click on Debug Options (B)
• Select the Checkbox for SQL Trace

If the Debug option is greyed out & not updateable set the profile Concurrent: Allow Debugging to Yes

2. Find Trace File Name

Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id

prompt
accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:'
prompt column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
--- and prog.application_id = execname.application_id
and prog.executable_application_id = execname.application_id
and prog.executable_id=execname.executable_id;


To check the timeline of the request :

SELECT request_id, TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )
request_date, TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )
requested_start_date, TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_start_date, TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_completion_date, TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )
current_date, ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 ) duration
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');

You may produce a complete report reference of the request ID using the Note:187504.1 bde_request.sql Process and Session info for one Concurrent Request(11.5)

3. TKPROF Trace File

Once you have obtained the Raw trace file you need to format the file using TKPROF.

$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no Where:
raw_trace_file.trc: Name of trace file
output_file:        tkprof out file
explain:            This option provides the explain plan for the sql
statements
sort:               This provides the sort criteria in which all sql
statements will be sorted.  This will bring the bad sql at
the top of the outputfile.
sys=no:             Disables sql statements issued by user SYS

---

NID(change the Database name using NID utility)

To change the Database name using NID utility

1)Make a whole database backup.
2)Login as a sys user..
sql>conn sys as sysdba
3)Check your database name
sql>SELECT nameFROM v$database;
NAME
———
ORCL
4)Make a proper shutdown of the database
here you can user immediate or transactional or normal
sql>SHUTDOWN IMMEDIATE
5) Invoke the newid utility
C:\mohd.dba>nid TARGET=system/manager@orcl DBNAME=newdb SETNAME=Y
DBNEWID: Release 10.2.0.1.0 – Production on Sat Jan 31 12:45:03 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database ORCL (DBID=1203749959)
Connected to server version 10.2.0
Control Files in database:
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Changing database name from ORCL to NEWDB
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL – modified
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL – modified
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL – modified
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF – wrote new name
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF – wrote new name
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF – wrote new name
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF – wrote new name
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF – wrote new name
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF – wrote new name
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL – wrote new name
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL – wrote new name
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL – wrote new name
Instance shut down
Database name changed to NEWDB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID – Completed succesfully.
6)Edit the pfile to change the db_name:
change the db_name parameter in the pfile to the new database name
db_name=newdb
7)Create a new password file using the ORAPWD utility
C:\mohd.dba>orapwd file=orapw001 password=confidential entries=10
8)Start the database using pfile:
sql>conn sys as sysdba
sql>startup pfile =G:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.0122009171812
9)Recreate the spfile from the pfile
sql> create spfile from pfile=’G:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.0122009171812′;
10) start the database using spfile:
SQL> startup force;
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 92275368 bytes
Database Buffers 192937984 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.

Undo tablespace(switch)

Change or switch undo tablespace in Oracle database

How to switch the database to a new UNDO tablespace and drop the old one

$ sqlplus / as sysdba

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> 

The current undo tablespace as suggested by the initialization parameter undo_tablespace is UNDOTBS1. Leave this sysdba as is, open another console, log in as user SCOTT and initiate a transaction.

$ sqlplus scott/tiger

update emp set sal = sal + 1000 where empno=7839;

1 row updated.

With an update on emp table we have initiated a transaction. The undo data is written to a segment in the UNDOTBS1 tablespace. Now leave this SCOTT's session intact and go back to the sysdba console without issuing any COMMIT or ROLLBACK.

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/d01/apps/oradata/oraxpo/undotbs201.dbf'
         SIZE 50M AUTOEXTEND ON NEXT 5M;

Tablespace created.

-- We created a new UNDO tablespace named UNDOTBS2

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
System altered.

-- Switch the database to the new UNDO tablespace.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

-- Try to drop the tablespace but failed. 

SQL>

With the alter system set undo_tablespace=UNDOTBS2, the database UNDO tablespace is changed and any new transaction's undo data will go to the new tablespace i.e. UNDOTBS2. But the undo data for already pending transaction (e.g. the one initiated by SCOTT before the database UNDO tablespace switch) is still in the old tablespace with a status of PENDING OFFLINE. As far as it is there you cannot drop the old tablespace.

set lines 10000
column name format a10

SELECT a.name,b.status 
FROM   v$rollname a,v$rollstat b
WHERE  a.usn = b.usn
AND    a.name IN ( 
		  SELECT segment_name
		  FROM dba_segments 
		  WHERE tablespace_name = 'UNDOTBS1'
		 );

NAME       STATUS
---------- ---------------
_SYSSMU8$  PENDING OFFLINE

The above query shows the name of the UNDO segment in the UNDOTBS1 tablespace and its status. Now lets see which users/sessions are running this pending transaction.

column username format a6

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN ( 
		  SELECT segment_name
		  FROM dba_segments 
		  WHERE tablespace_name = 'UNDOTBS1'
		 );

NAME       STATUS          USERNA        SID    SERIAL#
---------- --------------- ------ ---------- ----------
_SYSSMU8$  PENDING OFFLINE SCOTT         147          4

So this is SCOTT with SID=147 and SERIAL#=4. Since we know now the user, we can go to him/her and request to end the transaction gracefully i.e. issue a ROLLBACK or COMMIT. However, if this is not possible (say the user initiated the transaction and left for annual leave :) and trust me this happens) you may go ahead and kill the session to release the undo segments in the UNDOTBS1 tablespace.

SQL> alter system kill session '147,4';
 
System altered.
 
SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN ( 
		  SELECT segment_name
		  FROM dba_segments 
		  WHERE tablespace_name = 'UNDOTBS1'
		 );
 
no rows selected

As we can see once the session is kills we don't see anymore segments occupied in the UNDOTBS1 tablespace. Lets drop UNDOTBS1.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

If you are retaining undo data then you still won't be able to drop the tablespace because it is still in use by undo_retention. Let the UNDO_RETENTION time pass and then try to drop the tablespace. In my case it is 900 seconds i.e. 15 minutes.

-- After 15 minutes.
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Temporary Tablespace - 11g New Feature

SQL> create temporary tablespace temp2 tempfile 'test2.dbf' size 50M;

Tablespace created.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME   TABLESPACE_SIZE  ALLOCATED_SPACE   FREE_SPACE
----------------- --------------- ---------------   ----------
TEMP              20971520         4194304           19922944
TEMP2             52428800         1048576           51380224

SQL> alter tablespace temp2 shrink space keep 40M;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------- --------------- --------------- ----------
TEMP             20971520       4194304          19922944
TEMP2            42991616       1048576          41943040

SQL> alter tablespace temp2 shrink tempfile 'D:\APP\SANTONIE\PRODUCT\11.1.0\DB_1\DATABASE\TEST2.DBF' keep 30M;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME  TABLESPACE_SIZE  ALLOCATED_SPACE FREE_SPACE
---------------- --------------- --------------- ----------
TEMP             20971520        4194304          19922944
TEMP2            32497664        1040384          31457280

SQL> alter tablespace temp2 shrink space;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME  TABLESPACE_SIZE  ALLOCATED_SPACE FREE_SPACE
---------------  --------------- --------------- ----------
TEMP              20971520        4194304         19922944
TEMP2             2088960         1040384         1048576

Temp tablespace used/free in 11g/Force Online Redo Log Clear

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP1                               2122317824      2122317824 2107637760
TEMP2                               1887436800      1887436800 1869611008


select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 SizeMB,(TABLESPACE_SIZE-FREE_SPACE)/1024/1024 UsedMB,FREE_SPACE/1024/1024 FreeMB,Round((FREE_SPACE)*100/TABLESPACE_SIZE) "% Free",Round((TABLESPACE_SIZE-FREE_SPACE)*100/TABLESPACE_SIZE) "% Used" from dba_temp_free_space;



SQL> set linesize 200
SQL> select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 SizeMB,(TABLESPACE_SIZE-FREE_SPACE)/1024/1024 UsedMB,FREE_SPACE/1024/1024 FreeMB,Round((FREE_SPACE)*100/TABLESPACE_SIZE) "% Free",Round((TABLESPACE_SIZE-FREE_SPACE)*100/TABLESPACE_SIZE) "% Used" from dba_temp_free_space;

TABLESPACE_NAME                    SIZEMB     USEDMB     FREEMB     % Free     % Used
------------------------------ ---------- ---------- ---------- ---------- ----------
TEMP1                                2024         16       2008         99          1
TEMP2                                1800         17       1783         99          1

Force Online Redo Log Clear:

Now we will run an additional clause so as to force the CLEAR on our Online Redo Log. This will clear our online redo log but the corrupted log will not be recovered.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4 UNRECOVERABLE DATAFILE;
Database altered.

DataGuard

Oracle 10g - Manually Create a Physical Standby Database Using Data Guard

Submitted by hjiao on Tue, 2008-06-10 11:22

• RDBMS Server

Step-by-step instructions on how to create a Physical Standby Database on Windows and UNIX servers, and maintenance tips on the databases in a Data Guard Environment.
Oracle 10g Data Guard is a great tool to ensure high availability, data protection and disaster recovery for enterprise data. I have been working on Data Guard/Standby databases using both Grid control and SQL command line for a couple of years, and my latest experience with Data Guard was manually creating a Physical Standby Database for a Laboratory Information Management System (LIMS) half a year ago. I maintain it daily and it works well. I would like to share my experience with the other DBAs.
In this example the database version is 10.2.0.3.. The Primary database and Standby database are located on different machines at different sites. The Primary database is called PRIM and the Standby database is called STAN. I use Flash Recovery Area, and OMF.
I. Before you get started:
1. Make sure the operating system and platform architecture on the primary and standby systems are the same;
2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.
3. Test the Standby Database creation on a test environment first before working on the Production database.
II. On the Primary Database Side:
1. Enable forced logging on your primary database:
SQL> ALTER DATABASE FORCE LOGGING;
2. Create a password file if it doesn’t exist.
1) To check if a password file already exists, run the following command:
SQL> select * from v$pwfile_users;
2) If it doesn’t exist, use the following command to create one:
- On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)
- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)
3. Configure a Standby Redo log.
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;
3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;
4. Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
5. Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
1) Create pfile from spfile for the primary database:
- On Windows:
SQL>create pfile=’\database\pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).
- On UNIX:
SQL>create pfile=’/dbs/pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).
2) Edit pfilePRIM.ora to add the new primary and standby role parameters: (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
db_name=PRIM
db_unique_name=PRIM
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
LOG_ARCHIVE_DEST_1=
'LOCATION=F:\Oracle\flash_recovery_area\PRIM\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_2=
'SERVICE=STAN LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STAN'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=STAN
FAL_CLIENT=PRIM
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT='E:\oracle\product\10.2.0\oradata\STAN\DATAFILE','E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’
6. Create spfile from pfile, and restart primary database using the new spfile.
Data Guard must use SPFILE. Create the SPFILE and restart database.
- On windows:
SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).
- On UNIX:
SQL> shutdown immediate;
SQL> startup nomount pfile=’/dbs/pfilePRIM.ora’;
SQL>create spfile from pfile=’/dbs/pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).
III. On the Standby Database Site:
1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:
SQL>shutdown immediate;
On Standby Server (While the Primary database is shut down):
1) Create directory for data files, for example, on windows, E:\oracle\product\10.2.0\oradata\STAN\DATAFILE.
On UNIX, create the directory accordingly.
2) Copy the data files and temp files over.
3) Create directory (multiplexing) for online logs, for example, on Windows, E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG and F:\Oracle\flash_recovery_area\STAN\ONLINELOG.
On UNIX, create the directories accordingly.
4) Copy the online logs over.
2. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘STAN.ctl;
SQL>ALTER DATABASE OPEN;
3. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy pfilePRIM.ora from Primary server to Standby server, to database folder on Windows or dbs folder on UNIX under the Oracle home path.
2) Rename it to pfileSTAN.ora, and modify the file as follows. : (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
*.audit_file_dest='E:\oracle\product\10.2.0\admin\STAN\adump'
*.background_dump_dest='E:\oracle\product\10.2.0\admin\STAN\bdump'
*.core_dump_dest='E:\oracle\product\10.2.0\admin\STAN\cdump'
*.user_dump_dest='E:\oracle\product\10.2.0\admin\STAN\udump'
*.compatible='10.2.0.3.0'
control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\STAN\CONTROLFILE\STAN.CTL','F:\ORACLE\FLASH_RECOVERY_AREA\STAN\CONTROLFILE\STAN.CTL'
db_name='PRIM'
db_unique_name=STAN
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM,STAN)’
LOG_ARCHIVE_DEST_1=
‘LOCATION=F:\Oracle\flash_recovery_area\STAN\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STAN’
LOG_ARCHIVE_DEST_2=
‘SERVICE=PRIM LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIM’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRIM
FAL_CLIENT=STAN
remote_login_passwordfile='EXCLUSIVE'
# Specify the location of the primary DB datafiles followed by the standby location
DB_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE’,’E:\oracle\product\10.2.0\oradata\STAN\DATAFILE’
# Specify the location of the primary DB online redo log files followed by the standby location
LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’
STANDBY_FILE_MANAGEMENT=AUTO
(Note: Not all the parameter entries are listed here.)
4. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
5. Copy the standby control file ‘STAN.ctl’ from primary to standby destinations ;
6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.
On Windows copy it to \database folder, and on UNIX copy it to /dbs directory. And then rename the password file.
7. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID STAN –STARTMODE manual
8. Configure listeners for the primary and standby databases.
1) On Primary system: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start
2) On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start
9. Create Oracle Net service names.
1) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
2) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
10. On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOME and ORACLE_SID.
11. Start up nomount the standby database and generate a spfile.
- On Windows:
SQL>startup nomount pfile=’\database\pfileSTAN.ora’;
SQL>create spfile from pfile=’\database\pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
- On UNIX:
SQL>startup nomount pfile=’/dbs/pfileSTAN.ora’;
SQL>create spfile from pfile=’/dbs/pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
(Note- specify your Oracle home path to replace ‘’).
12. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;
13. Verify the standby database is performing properly:
1) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
2) On Primary, force a logfile switch:
SQL>alter system switch logfile;
3) On Standby, verify the archived redo log files were applied:
SQL>select sequence#, applied from v$archived_log order by sequence#;
14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;
15. To create multiple standby databases, repeat this procedure.
IV. Maintenance:
1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.
2. Cleanup the archive logs on Primary and Standby servers.
I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.
For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target /@STAN;
RMAN>backup archivelog all delete input;
To delete the archivelog backup files on the standby server, I run the following once a month:
RMAN>delete backupset;
3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server.
Refer to section II.2, step 2 to update/recreate password file for the Standby database.
Reference:
Oracle Data Guard Concepts and Administration 10g Release 2 (10.2), B14239-04

Upgrade 10g to 11g(me)

10g to 11g

1.install higher version softwer(11g) in 10g db machine 
2.10g pfile copy and past 11g home path(dbs)
$export ORACLE_SID=test(10g)
$export ORACLE_HOME=11g home path
$export LD_LIBARY_PATH=11g LIB path
$sqlplus / as sysdba

sql>startup upgrade

sql>@?/rdbms/admin/utlu111i.sql

check everthing(tablespace size) and go to change

3.down the db
4.open 10g db (normal) and change
5.down db


6.use the 11g home path(pfile is 10g) and startup the db

$sqlplus / as sysdba
sql>start upgrade

sql>@?/rdbms/admin/catupgrde.sql
sql>@?/rdbms/admin/utlrp.sql
sql>shu immediate
sql>startup

Dynamic/static Parameters

SQL> select name, ISSYS_MODIFIABLE from v$parameter;

Output:

NAME                                                                             ISSYS_MOD
-------------------------------------------------------------------------------- ---------
sga_max_size                                                                     FALSE
shared_pool_size                                                                IMMEDIATE
large_pool_size                                                                   IMMEDIATE
java_pool_size                                                                     IMMEDIATE
streams_pool_size                                                                IMMEDIATE
sga_target                                                                             IMMEDIATE
memory_target                                                                     IMMEDIATE
memory_max_target                                                             FALSE
control_files                                                                          FALSE

false -Static
Immediate -Dynamic

Apps Cloning

Rapid Cloning (Oracle Apps 11i and R12)

Cloning is the process of creating an identical copy of an already existing Oracle Applications system.
The new system including component versions, operating system, and platform type must be initially identical to the existing system.
Rapid Clone is the new cloning utility introduced in Release 11.5.8. Rapid Clone leverages the new installation and configuration technology utilized by Rapid Install.
See Oracle MetaLink Note 230672.1 (Cloning Oracle Applications 11i with Rapid Clone) for instructions on installing and enabling Rapid Clone, pre-requisite patches etc.
You may want to clone an Applications system for several reasons.
* Creating a test system from a recent copy of the production system in order to test patches against before applying to production.
* Periodically refreshing a test system from a production system in order to keep the test  system current.
* Moving an existing system to a different machine.
In this document, I have tried to explain the rapid cloning process with 11i as well as R12 Technology.
We can do cloning using cold as well as hot backup of database. Here I have used cold backup method for cloning.
Use the following steps for Rapid cloning
Steps    Description
Steps on Source
Step1 Run the adpreclone.pl file on DBTier and APPSTier of Source
DBTier :
Execute following command from $ORACLE_HOME/appsutil/scripts
perl adpreclone.pl dbTier
It will ask for apps password , enter the apps password
APPSTier :
Execute following command from $COMMON_TOP/admin/scripts
perl adpreclone.pl appsTier
Step2 Take Source Application Down
( from all the application nodes if you have multiple application node)
Execute adstpall.sh script from $COMMON_TOP/admin/scripts/
( For R12 $INST_TOP/apps/$CONTEXT_NAME/admin/scripts )
Step3 Take Source Database listener and database Down
Execute  following script from $ORACLE_HOME/appsutil/scripts/
( For R12 $ORACLE_HOME/appsutil/scripts )
addlnctl.sh stop <SID>
addbctl.sh stop immediate
Step 4 Copy All the TOP Folders from Source to target
(You can perform this copying process by making a tar files of below folders also.
To make tar, use command :  tar -cvf <path>/foldername.tar  <foldername> )

For 11i				For R12
Database	DB_TOP			Database	Apps_ST
	DATA_TOP				Tech_ST
Application	APPL_TOP			Application	Apps_ST
	COMMON_TOP				Tech_ST
	ORA_TOP				INST_TOP

Step5 Take Source Database and its listener Up
Execute  script from $ORACLE_HOME/appsutil/scripts/
( For R12 $ORACLE_HOME/appsutil/scripts )
addbctl.sh start
addlnctl.sh start <SID>
Step6 Take Source Application UP
( from all the application nodes if you have multiple application node)
Execute adstrtal.sh script from $COMMON_TOP/admin/scripts/
( For R12 $INST_TOP/apps/$CONTEXT_NAME/admin/scripts )
Step7 Submit the Active Users request and check its output for confirmation
Steps on Target
Steps    Description
Step1 If you have a tar files then untar it.
To untar use command :
go to the location where you want to untar and give the comand :
untar -xvf <path>/tarfilename.tar
Step2 Change the owner and file permissions of copied Folders on Target
( If Required )
Run Post Cloning Steps on DBTier
cd $HOME
vi .profile
comment any env file sourced.
Logout and login to server and execute following command
$echo $ORACLE_HOME
It should not display output.
Enter following commands,
export PERL5LIB=<RDBMS ORACLE_HOME>/Apache/perl/lib/5.00503
export PATH=<RDBMS ORACLE_HOME>/Apache/perl/bin:$PATH
cd <RDBMS ORACLE_HOME>/appsutil/clone/bin
Note down the following path
Database Node Name
RDBMS ORACLE HOME
utl_file
All DATA TOP
DISPLAY
PORT Number
Execute following command from  $<RDBMS ORACLE_HOME>/appsutil/clone/bin
perl adcfgclone.pl dbTier
Provide the values required for creation of the new Database Context file like,
Enter the APPS password [APPS]:
Do you want to use a virtual hostname for the target node (y/n) [n] ?:
Target instance is a Real Application Cluster (RAC) instance (y/n) [n]:
Target System database name [PROD]:
Target system RDBMS ORACLE_HOME directory
Target system utl_file accessible directories list
Number of DATA_TOP’s on the target system [3]:
Do you want to preserve the Display set to oraproddb1:1.0 (y/n) [y] ?:
Do you want to preserve the port values from the source system on the target system (y/n) [y] ?:
Enter the port pool number [0-99]:
Monitor the log file during process
Step3 Run Post Cloning Steps on appsTier
(If you have a multiple application tiers then perform below steps on multiple nodes starting from Concurrent node)
cd $HOME
vi .profile
comment any env file sourced.
Logout and login to server and execute following command
$echo $ORACLE_HOME
It should not display output.
Enter following commands,
export PATH=<ORA_TOP>/iAS/Apache/perl/bin:$PATH
export PERL5LIB=<ORA_TOP>/iAS/Apache/perl/lib/5.00503
cd <COMMON_TOP>/clone/bin        (for R12
Note down the following path
Database Node Name
APPL_TOP
COMMON_TOP
8.0.6 ORACLE HOME
iAS ORACLE HOME
RDBMS ORACLE HOME
utl_file
DISPLAY
PORT Number
Execute following command from $COMMON_TOP/clone/bin
perl adcfgclone.pl appsTier
Provide the values required for creation of the new APPL_TOP Context file
Enter the APPS password [APPS]:
Do you want to use a virtual hostname for the target node (y/n) [n] ?:
Target system database SID :
Target system database server node :
Target system database domain name
Does the target system have more than one application tier server node(y/n) [y] ?
Does the target system application tier utilize multiple domain names (y/n)[n] ?
Target system concurrent processing node
Target system administration node
Target system forms server node
Target system web server node
Is the target system APPL_TOP divided into multiple mount points (y/n)[n] ?:
Target system APPL_TOP mount point
Target system COMMON_TOP directory
Target system 8.0.6 ORACLE_HOME directory
Target system iAS ORACLE_HOME directory
Do you want to preserve the Display set to
Do you want to preserve the port values from the source system on the target system (y/n)[y]
Enter the port pool number [0-99]:
UTL_FILE_DIR on database tier consists of the following directories
Monitor the log file during process
Step4 Perform Post Cloning Tasks as per requirement like
Site name (%site%)
Web address (%web%)
Help Link (help%)
Current common_top
Update workflow entries
change sysadmin password
change color (java color)
change apps password
Make the necessary changes in custom scripts, user level profiles etc.
(Please check “FNDCPASS Utility” page in same blog for “How to change apps password “)
Update as follows:
select TEXT_VALUE  from WF_NOTIFICATION_ATTRIBUTES where
TEXT_VALUE like ‘%<SID>%<Port Number>%’
update WF_NOTIFICATION_ATTRIBUTES set TEXT_VALUE = NULL
where TEXT_VALUE like ‘%<SID>%<Port Number>%’
select TEXT_VALUE  from WF_ITEM_ATTRIBUTE_VALUES where
TEXT_VALUE like ‘%<SID>%<Port Number>%’
update WF_ITEM_ATTRIBUTE_VALUES  set TEXT_VALUE = NULL where
TEXT_VALUE like ‘%<SID>%<Port Number>%’
select GUID from WF_SYSTEMS
Ensure GUID is different of source and target.
select NAME from WF_SYSTEMS
Ensure NAME is different of source and target.
select ADDRESS from WF_AGENTS
Ensure ADDRESS is different of source and target.
select LOGFILE_NAME from FND_CONCURRENT_REQUESTS
update FND_CONCURRENT_REQUESTS set LOGFILE_NAME = NULL where LOGFILE_NAME like ‘%<SID>%’
select OUTFILE_NAME from FND_CONCURRENT_REQUESTS
Step5 Take  Application Down
( from all the application nodes if you have multiple application node)
Execute adstpall.sh script from $COMMON_TOP/admin/scripts/
( For R12 $INST_TOP/apps/$CONTEXT_NAME/admin/scripts )
Step6 Clean the FND Node
On SQL prompt as apps user run following commands
EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;
Step7 Execute autoconfig.sh on dbTier from $ORACLE_HOME/appsutil/scripts
Step8 Execute autoconfig.sh on appsTier $COMMON_TOP/admin/scripts
(For R12 $INST_TOP/apps/$CONTEXT_NAME/admin/scripts)
Step9 Start the Application
( from all the application nodes if you have multiple application node start from concurrent node)
Execute adstrtal.sh script from $COMMON_TOP/admin/scripts/
( For R12 $INST_TOP/apps/$CONTEXT_NAME/admin/scripts )
Step10 Submit the Active Users request and check its output for confirmation

FAQ’s

1.                 What is cloning?

Cloning is the process of creating an identical copy of an already existing Oracle Applications system.

2.         How can I clone an Oracle Applications system?

There are two cloning methods documented in the following white papers available off of Oracle MetaLink Note 135792.1

3.         What is the AD Cloning utility?

AD Cloning utility (adclone.pl) is the name of the cloning command line utility. This utility is used to preserve and apply configuration information to the cloned target system.

4.         What is Rapid Clone?

Rapid Clone is the new cloning utility introduced in Release 11.5.8. Rapid Clone leverages the new installation and configuration technology utilized by Rapid Install. See OracleMetaLink Note 230672.1 (Cloning Oracle Applications 11i with Rapid Clone) for instructions on installing and enabling Rapid Clone.

5.                 How do I determine if my system is Rapid Clone enabled?

First, verify that your system is AutoConfig enabled. Then, verify that you have applied the latest Rapid Clone patch documented in OracleMetaLink Note 230672.1 (Cloning Oracle Applications 11i with Rapid Clone). See Searching the Patch History Database in the AD Procedures Guide for instructions on searching for patches applied to your system.
There are several identifiers for when the system is AutoConfig enabled. The following are two common indicators:
Open the environment file APPSORA.env in your APPL_TOP. If the top of the file says that it is maintained by AutoConfig, then your system is probably using AutoConfig.
Check if there is an Applications Context file in the APPL_TOP/admin directory. This file will typically be named <SID>.xml or <SID>_<HOSTNAME>.xml.
Check if there is an Applications Context file in the RDBMS ORACLE_HOME under the appsutil directory. This file will typically be named <SID>.xml or <SID>_<HOSTNAME>.xml.

6.         What is AutoConfig?

AutoConfig is a configuration tool that supports automated configuration of an Oracle Applications Instance. All of the information required for configuring an Applications instance is collected into a central repository, called the Applications Context. When the AutoConfig tool runs, it uses information from the Applications Context file to generate configuration files and update database profiles. See OracleMetaLink Note 165195.1 for details on installing and migrating to AutoConfig.

7.         We are running Release 11.5.8 (or any later release), which cloning

method can we use?

In 11.5.8 AutoConfig is enabled on the middle tier out of the box. In 11.5.9 and any later release, AutoConfig is enabled by default on both the database tier and the middle tier. Update AutoConfig and Rapid Clone code to the latest code line and use Rapid Clone to clone your system. Full instructions are in Cloning Oracle Applications Release 11i with Rapid Clone document 230672.1 on OracleMetalink.

8.         Can I clone from one operating system version to another?

Yes, if the target system platform is binary compatible with the source system platform. For example, if you have an existing single-node Oracle Applications system on Solaris 2.6, you could clone it to a node running Solaris 8, but not to a node running HP-UX. Note that cloning from a higher version of a platform to a lower version is not supported, for example, from Solaris 8 to Solaris 2.6. Other examples of binary compatibility for Oracle Applications are:
AIX 4.3.3 to AIX 5.1 (32-bit)
HP-UX 11.0 to HP-UX 11i
Windows NT to Windows 2000
Within a same platform you can also clone from a 32bit source system to a 64bit target system.
Note (AIX only):   when cloning from AIX 32bit to AIX 64bit, apply patch 2896876 (64bit kernel extension for Oracle) on the target system prior to running adcfgclone.pl.

9.         Can I clone a single-node system to a multi-node system?

The Rapid Clone cloning method allows for cloning a single-node system to a multi-node system. See the Cloning Oracle Applications Release 11i with Rapid Clone white paper for details.

10.       Can I clone a multi-node system to a single-node system?

You can use Rapid Clone to merge multiple APPL_TOP and COMMON_TOP file systems into a single APPL_TOP and COMMON_TOP file system. For more details about this procedure, see “Section 3: Merging existing APPL_TOPs into a shared APPL_TOP” in document 233428.1 on OracleMetaLink.
11.       What is context file?
Context file is a central repository, which stores all application configuration information. The name is like <Instance name>_ <Server name>.xml
12.       How autoconfig will create env and configuration files?
Autoconfig will go to each and every top template directory take the templates from there and fill the values from xml file and create the required files.
13.       In how many phases autoconfig will run?
Autoconfig will run in 3 phases.
1. INIT – Instantiate the drivers and templates
2. SETUP – Fill the template with values from xml and create files
3. PROFILE – Update the profile values in database.
14.       What is the location of adconfig log file?
APPL_TOP/admin/<context_name>/log/<timestamp directory>



References :

Cloning Oracle Applications Release 11i
Cloning Oracle Applications Release 11i with Rapid Clone Oracle MetaLink Note 135792.1
Cloning Oracle Applications Release 11i with Rapid Clone (Metalink Note 230672.1)
Cloning Oracle Applications Release 12 with Rapid Clone