How to clone Oracle Apps R12 Online A Step by Step Guide

Most of the time Oracle Apps project requires a test instance which is almost similar to Production instance. R12 Production instance can’t be brought down for taking offline clone. So, for online cloning you need to use RMAN. The following is a step by step guide to make a clone instance as similar as Production. Here Production instance is referred as "SOURCE" and test

instance is named as "TARGET". 

The assumption is that you have already a R12 target instance which will be refreshed. If you don't have a target instance you need to take an offline backup of Production source instance to create a target instance for the first time. [It's possible to create first target instance online also. I'll write an article later on that.]

1) First take the backup of target  database spfile in a pfile

In target db run the following command.

SQL> create pfile='/D02/TARGET/initTARGET.ora' from spfile;

2) Stop the training instance. Both Apps tier and DB tier.

3) Prepare the Source Instance by running preclone in db and apps tier.

db

. /D01/SOURCE/db/tech_st/11.1.0/SOURCE_LONDON.env

cd /D01/SOURCE/db/tech_st/11.1.0/appsutil/scripts/SOURCE_LONDON

perl adpreclone.pl dbTier

apps

 . /a01/SOURCE/apps/apps_st/appl/APPSSOURCE_LONDONPAP002.env

cd $ADMIN_SCRIPTS_HOME

perl adpreclone.pl appsTier

4) delete or backup the TARGET instance apps directory. Ensure that you have old context file backup. Also, you may keep old apps env file.

5) start transfering apps tier from TARGET server to SOURCE server using following query. Here we are using rsync to copy to remote directory. Also, we are excluding the concurrent manager log file and particular interface file from copying to remote server.

rsync -az --exclude='xxinterface' --exclude='concmgr' /a01/SOURCE/apps oasTARGET@100.200.10.400:/A01/TARGETAPP/

6) In our case, for RMAN backup, The same /backup directory that is mounted in TARGET database server is also mounted on SOURCE db server as /backup

7) Add the following two lines at the bottom of previously created "/D02/TARGET/initTARGET.ora" file

*.log_file_name_convert=(/D01/SOURCE/db/apps_st/data/,/D02/TARGET/db/apps_st/data/)

*.db_file_name_convert=(/D01/SOURCE/db/apps_st/data/,/D02/TARGET/db/apps_st/data/)

8) Add the target listener information in source tnsnames.ora file.

Open /D02/TARGET/db/tech_st/11.1.0/network/admin/TARGET_LONDONdb001/tnsnames.ora and add the contents from source listener

-----------

9) After setting the the old TARGET db env file start the listener.

. /D02/TARGET/db/tech_st/11.1.0/TARGET_LONDONqdb001.env

lsnrctl start TARGET

lsnrctl start SOURCE

10) Start the source instance with pfile

sqlplus / as sysdba

SQL> startup nomount pfile='/D02/TARGET/initTARGET.ora';

11) delete or backup all the old datafiles

12) Recover the SOURCE Production  database in target. In our case, for RMAN backup, The same /backup directory that is mounted in TARGET database server is also mounted on SOURCE db server as /backup

$ rman target sys/****@SOURCE nocatalog auxiliary /

RMAN> DUPLICATE TARGET DATABASE TO 'TARGET';

At the last stage it may show some error. But we'll recover the db by applying archive log.

13) login to the target database using 

         

          $ sqlplus / as sysdba

          SQL> select status from v$instance; it should be in mount stage.        

          SQL> recover database using backup controlfile until cancel;

copy the archive file required by the recover command from the source system to the target system. atleast provide 5 archives files. CANCEL the recover command after the desired archive log has been applied. I am giving a sample of this command set.

------------

ORA-00289: suggestion :

/D02/TARGET/db/apps_st/data/archive/1_933_813073562.dbf

ORA-00280: change 5988716976174 for thread 1 is in sequence #933

ORA-00278: log file '/backup/archive_tmp/SOURCE1932813073562.arc' no longer

needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

/backup/archive_tmp/SOURCE1933813073562.arc

ORA-00279: change 5988717746012 generated at 05/24/2013 03:29:30 needed for

thread 1

ORA-00289: suggestion :

/D02/TARGET/db/apps_st/data/archive/1_934_813073562.dbf

ORA-00280: change 5988717746012 for thread 1 is in sequence #934

ORA-00278: log file '/backup/archive_tmp/SOURCE1933813073562.arc' no longer

needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

CANCEL

Media recovery cancelled.

------------

14) Open the database with resetlogs. 

       SQL> alter database open resetlogs;

            shutdown database

       SQL> shut immediate

15) Disable the archive log mode if required.  Goto mount stage

       SQL> startup mount

       SQL> alter database noarchivelog;         

       SQL> alter database open;

Remove all trace files & alertlog file from udump and bdump.

   SQL> shutdown immediate 

   $ cd bdump/ udump/  $ rm *

  

create spfile from current pfile

   $ sqlplus / as sysdba  

   SQL> startup   

16)Temperory files should be added with following cmds

       SQL> ALTER TABLESPACE temp1 ADD TEMPFILE '/D02/TARGET/db/apps_st/data/temp1.dbf' SIZE 1024m;

       SQL> ALTER TABLESPACE temp2 ADD TEMPFILE '/D02/TARGET/db/apps_st/data/temp2.dbf' SIZE 1024m;

17) clean the fnd_nodes info by running following script

  SQL> conn apps/appspwd

  SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN();

  SQL> commit;

  SQL> create spfile from pfile

--------

18) In the application tier run adcfgclone with old context file to configure apps tier.

cd /A01/TARGETAPP/apps/apps_st/comn/clone/bin/

perl adcfgclone.pl appsTier /A01/TARGETAPP/inst/apps/TARGET_LONDON001/appl/admin/TARGET_LONDON001.xml

--------

19) change custom env file name and custom top location inside that file.

run db autoconfig

run apps autoconfig

restart apps and db

20) Do post clone activities and password change etc.. If you are facing some issues, you can leave a comment below.

Step by step guide for creating Disaster Recovery site for Oracle Apps R12

In this article, I tried to explain Disaster Recovery [DR] site creation for R12 in elaborate manner. It's a very much practical hands on article. By, following each steps carefully any one can create a R12 Disaster Recovery site. I tried to avoid theoretical details about Disaster Recovery unless required for practical implementation. Here I have given a real DR site creation process.


[By following this document any one create a physical standby Data guard site of oracle 11g database also].


Assumption
a. We have Disaster Recovery Site server whose base level directory structures are same as to primary site.
In our practical example, the directory structure is as follows.

Primary Site Disaster Recovery Site
apps /a01/DUMMY/apps /a01/DUMMY/apps
db     /D03/DUMMY/db         /D03/DUMMY/db

If you have different directory structure, add proper db file dir conversion parameters [log_file_name_convert,db_file_name_convert] in the pfile.

b. Here I have shown how to create a "physical standby" site using oracle data guard. For creating "logical standby" few simple changes are required.




Creation of DR site

Step 1 Prepare the primary Database


 1.1. Enable force logging in primary database after enabling it in archival mode [if it's not done already]. 

SQL> ALTER DATABASE FORCE LOGGING;


1.2. Configure the database Network communication
The primary and standby databases need to be able to communicate using Oracle Net. This means that on the standby, a listener needs to be running to handle incoming requests from the primary. In addition TNS aliases must be created on both the primary and standby systems. For both aliases and listener, you should configure ifiles under the $TNS_ADMIN directory. You can use either a service (dynamic registration) or SID (static registration) model. This document uses static registration, as used in the standard AutoConfig files.

Standby Listener
This listener only runs while the server is hosting a standby database. On switchover/failover etc, the standard autoconfig listener is used. Use the same structure as the autoconfig listener, substituting different values for port,host and listener name. 

TNS Aliases
The aliases will be used by the fal_server init.ora parameters, allowing two-way communication between the primary and standby. The fal_server alias is a connect string to the primary. 

For our DUMMY Primary database in the $TNS_ADMIN directory we have following files.
DUMMY_primary_ifile.ora
listener_ifile.ora
listener.ora
sqlnet_ifile.ora
sqlnet.ora
tnsnames.ora

I made sure that sqlnet.ora, tnsnames.ora, listener.ora files are pointing to ifile "DUMMY_primary_ifile.ora". Then modify DUMMY_primary_ifile.ora as below.
--------------------
PRI_DUMMY=
        (DESCRIPTION=
            (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1529))
            (CONNECT_DATA= (SID=DUMMY)
            )
        )

STBY_DUMMY=
        (DESCRIPTION=
           (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1530))
            (CONNECT_DATA= (SID=DUMMY)
            )
        )
--------------------
 Our primary dummy db is running from port 1529 where as standby would run from 1530.


1.3. creation of secure file
$ cd /dbs
$ orapwd file=orapw password= entries= ignorecase=y
To complete the implementation of the password file, you must add the parameter remote_login_passwordfile to your init.ora file as described in the next section.

1.4. Change primary database init parameters
[If your database is running in spfile mode create a pfile, after changing the init parameters start the database with this pfile. After creation of full standby later on you may again transfer the db to run in spfile mode.] 
Change the following parameters in the pfile of primary server. [You may need to change more parameters if you want more customized configuration. For this DUMMY database, I have shown the minimal changes that needs to be done].

*.log_archive_dest_1='LOCATION=/D03/DUMMY/archive' [location of archive files]
*.log_archive_dest_2='SERVICE=STBY_DUMMY reopen=60' [log archive destination 2 is the standby site address]
*.LOG_ARCHIVE_DEST_STATE_1='enable'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.db_unique_name='PRI_DUMMY'
*.FAL_CLIENT='PRI_DUMMY'
*.FAL_SERVER='STBY_DUMMY'
*.standby_file_management='AUTO'
*.remote_login_passwordfile='EXCLUSIVE'

1.5. Add standby redo log files
alter database add standby logfile group 3 ('/D03/DUMMY/db/apps_st/data/stbtlog1a.dbf','/D03/DUMMY/db/apps_st/data/stbtlog1b.dbf') size 1024M;

1.6. Information of temporary files in primary
For future manual creation of temp files in standby database gather temp files size and other information from primary.
SQL> select file_name, bytes from dba_temp_files;


1.7. Run the application tier and database tier pre-clone scripts
As the ORACLE user, run the database pre-clone utility on the primary database server.
$ cd $RDBMS_ORACLE_HOME/appsutil/scripts/context_name/
$ perl adpreclone.pl dbTier

As the APPLMGR user, run the application tier pre-clone utility on each primary application tiers that has an APPL_TOP.
$ cd $INST_TOP/admin/scripts/
$ perl adpreclone.pl appsTier
(Optional) Shut down all application tier services to copy the APPL_TOP. If your operating system returns errors when copying open files, you may need to shut down application tier services to successfully copy the APPL_TOP and Oracle E-Business Suite technology stack software.



Step 2 Creation of Physical Standby database

2.1. Generate a standby control file in primary and copy it to standby database server

Use the following command to generate a standby control file.
SQL> alter database create standby controlfile as '/D03/DUMMY/stbyDUMMYb.dbf';
SQL> alter system switch logfile;
SQL> select thread#, sequence#-1 from v$log where status = 'CURRENT';
  THREAD# SEQUENCE#-1
---------- -----------
     1          42

You will need to recover past the time the control file is created, so switch logs and note the new log number. Copy the control file to the standby database server. Note the thread# and sequence# for later use. you will only be able to open the standby database after this log has been applied on the standby.


2.2. Do file-based configurations on the standby database server

After the database software copies are complete, log into the standby database server as the ORACLE user and execute the following commands to update the file system configurations for the new environment. 
$ cd /appsutil/clone/bin
$ perl adcfgclone.pl dbTechStack

2.3. The above step starts the database listener. It is not yet completely configured, so should be stopped. As the ORACLE user on the standby database server, enter the following command.
$ lsnrctl stop DUMMY

2.4. Configure Oracle Net in Standby Site

As the ORACLE user, copy the listener_ifile.ora and _ifile.ora from the primary server's directory to the standby server's directory. As part of the copy, rename the primary _ifile.ora to the standby's _ifile.ora matching the spelling and case in the file name in the last line of the standby server's tns_names.ora file.

In the _ifile.ora, be sure the entry for the standby service's HOST parameter holds the standby database host name and the FAL service's host name is the primary host name.

In the listener_ifile.ora file, ensure that the HOST for the standby service entry points to the standby database host.

As the ORACLE user, start the database listener for the standby.
$lsnrctl start DUMMY

The following is the entry in our "DUMMY_standby_ifile.ora" file.
-----------------------------
PRI_DUMMY=
        (DESCRIPTION=
            (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1530))
            (CONNECT_DATA= (SID=DUMMY)
            )
        )

STBY_DUMMY=
        (DESCRIPTION=
           (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1529))
            (CONNECT_DATA= (SID=DUMMY)
            )
        )
-----------------------------

2.5. Modify the database init.ora parameters on the standby server

Modify the following init parameters. Add an entry for the standby control file you created on the primary and copied to this server.
control_files = ( / , /)

*.control_files='/D03/DUMMY/stbyDUMMYb.dbf'
*.log_archive_dest_1='LOCATION=/D03/DUMMY/rchive'
*.LOG_ARCHIVE_DEST_STATE_1='enable'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.db_unique_name='STBY_DUMMY'
*.FAL_CLIENT='PRI_DUMMY'
*.FAL_SERVER='STBY_DUMMY'
*.standby_file_management='AUTO'
*.remote_login_passwordfile='EXCLUSIVE'


2.6. Mount the physical standby, start processing redo on the standby

Ensure that the password file created previously exists under $ORACLE_HOME/dbs
Mount the standby database. Connect to SQL*Plus as sysdba and issue these commands:

SQL>startup nomount pfile= /dbs/init .ora | spfile
SQL>alter database mount standby database;
Put the standby database in 'managed recover' mode
SQL>alter database recover managed standby database disconnect from session;


2.7.  Verify redo is shipping

Check to see that the database is shipping redo, by connecting to the primary database and causing a log switch.

SQL>alter system switch logfile;


Still on the primary, check for the status of the archive destinations to determine the most recently archived redo log file at each redo transport destination. The most recently archived redo log file should be the same for each destination. If it is not, a status other than VALID may identify an error encountered during the archival operation to that destination.

SQL>select * from v$archive_dest_status where status != 'INACTIVE';


This query will show the which logs have been sent /received and applied.

SQL>select sequence#, applied, to_char(first_time, 'mm/dd/yy hh24:mi:ss') first from v$archived_log order by first_time;



On the standby database server, monitor the database alert log for recovery progress.


2.8. Add your temp files to the standby database

To save time on failover, add your temp files to the standby database now. You collected the temporary file names and sizes in previous Step.
To do this, you will need to open the database in read only mode. You will not be able to open the database read only until recovery has progressed past the time the control file was created - the log sequence number was noted in previous Step.

Execute the following commands.
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
get temporary tablespace_name from primary
SQL> select distinct(tablespace_name) from dba_tablespaces
then add it to standby
SQL> alter tablespace temp1 add tempfile '/D03/DUMMY/db/apps_st/data/temp01.dbf' size 50M reuse;
shutdown immediate;
startup mount pfile='/D03/DUMMY/initDUMMYstdby.ora';
alter database recover managed standby database disconnect from session;

2.9. In this step if you want you can take primary and standby database to be run in SPFILE mode. 
create spfile from pfile='/D03/DUMMY/initDUMMYstdby8aug13.ora';


Step 3 Configuration of application tiers after standby database is enabled

Perform file-based configurations on standby application tiers

After the application tier software copies are complete, the file system configurations need to be updated to reflect the new environment. To do this on the application tiers, log onto each standby application tier system as the APPLMGR user and execute the following commands. Since your environment scripts are not yet set up, you will need to manually resolve the reference to and .
If the directory structure on standby is different than the primary then you need to run "perl adcfgclone.pl atTechStack" instead of adclonectx.
$ cd /clone/bin
$ perl adclonectx.pl /appl/admin/.xml


When the script is finished and the context file is created, execute the following commands, again resolving the reference to

$ cd APPL_TOP/ad/12.0.0/bin
$ perl adconfig.pl contextfile=$INST_TOP/appl/admin/.xml run=INSTE8
Answer the questions when prompted. This creates your environment files on the application tier. It tries to connect to the database, so some portions will fail, but the environment scripts should be created successfully. /LI>
Optionally, set up rsync for log and out files. If you wish to synchronize your concurrent manager log and out files from primary to the standby, first create directories matching matching to the APPLCSF environment variables in the appropriate place on the standby application tier server(s). For example:

$ mkdir -p /log
$ mkdir -p /out
Repeat this on the primary server, creating directories matching the standby context name, so as to be ready for a switchover operation. For UNIX systems, on the primary application tier(s), set up an rsync job in cron, to run every few minutes. This example synchronizes the log directory.
$ rsync av < APPLCSF>/log : /log --rsync-path=/usr/local/bin/rsync

This ends the Disaster Recovery site creation for Oracle Apps R12.  For other detailed topics like "Switch Over between sites" or "Complete Primary failover" see the reference below.




Reference
Business Continuity for Oracle E-Business Release 12 Using Oracle 11g Release 2 and later Physical Standby Database (Doc ID 1070033.1)