Oracle Apps Cloning – Refreshing single instance Test database with RAC Production Data

Oracle Apps Cloning – Refreshing single instance Test database with RAC Production Data

As an Apps DBA, you will need to refresh your Test or Dev instance with production changes regularly.
 Sometimes, you need to create a complete clone of the Production and sometimes you might need to just refresh the data.

Here, i will post the steps which are required to refresh the Test instance with the latest production data.

Environment Information:

1. Production (PROD) – R12.1.1. with 11.1.0.7 Database, 2 node RAC Database with 2 Redo Threads and 3 Redo Log Groups
 in Each Thread.

2. Test Database (UAT) – R12.1.1 with 11.1.0.7 Database, Single Node Database.

We assume that we can not bring down our production database for backups to be used for refresh.

We will be taking hot backup for this refresh.

step 1: Take a controlfile trace on production:

bash$ sqlplus "/as sysdba"

SQL> alter database backup controlfile to trace;

Database altered.

SQL> exit
Now go to the trace directory:

bash $ cd /db01/oraspprd/db/tech_st/11.1.0/admin/PROD_oraspapp/diag/rdbms/prod/PROD/trace

bash $ ls -lrt

In the bottom you will find the trace file among recently generated files. Normally, this trace file will be having size much more than the other trace files. Thus, by size, you can identify it. Open this file and check to confirm.

Open this trace file and copy all its contents to notepad on your desktop machine.

step 2: Take the Hot backup of your database.

bash$ sqlplus "/as sysdba"

SQL> alter system archive log current;

SQL> alter database begin backup;

SQL> select count(*) from v$backup where status='ACTIVE';

SQL> exit;
step 3:

Create a backup directory e.g. backup_data and copy all the datafiles and redo logs to this backup directory.

Once the backup completes, execute the following commands:

bash$ sqlplus "/as sysdba"

SQL> alter system archive log current;

SQL> alter database end backup;

SQL> select count(*) from v$backup where status='ACTIVE';

SQL> alter system archive log current;

SQL> alter system switch logfile;

SQL> alter system switch logfile;

SQL> alter system switch logfile;

SQL> exit;
Create another backup directory e.g. backup_arch and copy all the archive logs generated today to this backup directory.

step 4: Bring your Test instance down.

- Login to your Test instance as “applmgr” user and shut down the application tier services.

Wait, check and verify that all the processes have stopped.

- Login to your Test instance as “oracle” user and shut down the listener and database.

Wait, check and verify that all the processes have stopped.

- If you have spare space, rename the directory containing all your Datafiles, Redo Log and control files and create new empty directory with the same name. If you don’t have enough space on test machine, just remove all the Datafiles, Redo Log and control files.

step 5: Copy backups to Test Server.

- Copy all the datafiles and Redo Logs backed up to backup_data directory on production server to the directory which contains DBFs and Redo Logs on the Test server. Please do not copy any controlfiles. Check and if you see they are copied, remove them.

- Copy all the archive logs backed up to backup_arch directory on production server to some directory on Test server e.g. to “/test01/arch”

step 6:

If you are also copying the Oracle Home from Production, then you also need to follow the following steps to configure the Oracle Home on Test server. However, if you are just refreshing the data and not binaries, you may skip this step and jump on to step 7.

On Test server:

bash $ PERL5LIB=`which perl`

bash $ export PERL5LIB

bash $ cd /u02/orauat/db/11.1.0/appsutil/clone/bin

bash $ perl adcfgclone.pl dbTechStack
step 7: Create control file creation sql script for Test instance.

Now you need to edit the control file trace copied to your notepad in step 1.

Here, goto #2. RESETLOGS case, and remove all  the lines before it. You will see something like this:

--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 20157

......
......
......

'/db01/oraspprd/db/apps_st/data/undo05.dbf',
'/db01/oraspprd/db/apps_st/data/sysaux11.dbf'
CHARACTER SET UTF8
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/db01/archives/arch_1_1_732382724.arc';
-- ALTER DATABASE REGISTER LOGFILE '/db01/archives/arch_1_1_732382820.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Create log files for threads other than thread one.
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 4 (
'/db01/oraspprd/db/apps_st/data/redo1a_2.dbf',
'/pdb03/oradata/redo1b_2.dbf'
) SIZE 100M REUSE,
GROUP 5 (
'/db01/oraspprd/db/apps_st/data/redo2a_2.dbf',
'/pdb03/oradata/redo2b_2.dbf'
) SIZE 100M REUSE,
GROUP 6 (
'/db01/oraspprd/db/apps_st/data/redo3a_2.dbf',
'/pdb03/oradata/redo3b_2.dbf'
) SIZE 100M REUSE;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/db01/oraspprd/db/apps_st/data/temp02.dbf'
SIZE 1600M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/db01/oraspprd/db/apps_st/data/temp01.dbf'
SIZE 1100M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/db01/oraspprd/db/apps_st/data/temp04.dbf'
SIZE 1204M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/db01/oraspprd/db/apps_st/data/temp03.dbf'
SIZE 1024M REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
Remove all the lines before the line “STARTUP NOMOUNT”. Now, using CTRL-H option, change the datafiles path from that of production to one that is to be used on Test instance e.g. change the path from “/db01/oraspprd/db/apps_st/data” to “/u02/orauat/db/apps_st/data”. After that, change the following line:

CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG

TO

CREATE CONTROLFILE SET DATABASE "UAT" RESETLOGS NOARCHIVELOG
Also, remove all the lines containing the LOGFILE THREAD 2 information.

Now, copy from the line

"STARTUP NOMOUNT"

till

CHARACTER SET UTF8
;
open a “createctl.sql” script on the Test Server and paste the above copied lines to this script and save it.

Please go to the directory where DFBs and Redo Logs have been copied (e.g. /u02/orauat/db/apps_st/data) and verify that all the files have 755 permissions and are owned by Test instance’s database owner e.g. oracle.

step 8: On Test instance, source the Database environment and go to the directory where we have created the “createctl.sql” script.

Note: Before running the script, check your init.ora file and check the parameter log_archive_format parameter. This parameter should be same as in your prdocution instance.

e.g., in my TEST instance it was : log_archive_format = %t_%s_%r.dbf

However, in production it was: log_archive_format = arch_%t_%s_%r.arc

So i modified my Test instance’s init.ora file and set log_archive_format = arch_%t_%s_%r.arc.

Now, connect as sysdba and run the script:

bash $ sqlplus "/as sysdba"

connected to idle instance

SQL> @createctl.sql
...
...
...

Control File created
Now you have to perform recovery:

SQL> set logsource "/test01/arch"             -- The directory where you have copied the archives from production:

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 215587587 generated at 01/04/2011 01:00:47 needed for thread
1
ORA-00289: suggestion : /test01/arch/arch_1_302_732382820.arc
ORA-00280: change 215587587 for thread 1 is in sequence #302

Specify log: {=suggested | filename | AUTO | CANCEL}
/test01/arch/arch_1_302_732382820.arc
ORA-00279: change 215591020 generated at 01/04/2011 01:36:06 needed for thread
1
ORA-00289: suggestion : /test01/arch/arch_1_303_732382820.arc
ORA-00280: change 215591020 for thread 1 is in sequence #303
ORA-00278: log file '/test01/arch/arch_1_302_732382820.arc' no
longer needed for this recovery
It will keep asking for the archive logs. Keep applying the logs till the last log available with you.

...
...

Specify log: {=suggested | filename | AUTO | CANCEL}
/test01/arch/arch_1_305_732382820.arc
ORA-00279: change 215591046 generated at 01/04/2011 01:36:18 needed for thread
1
ORA-00289: suggestion : /test01/arch/1_306_732382820.dbf
ORA-00280: change 215591046 for thread 1 is in sequence #306
ORA-00278: log file '/test01/arch/arch_1_305_732382820.arc' no
longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/test01/arch/arch_1_306_732382820.arc
ORA-00279: change 215591052 generated at 01/04/2011 01:36:22 needed for thread
1
ORA-00289: suggestion : /test01/arch/1_307_732382820.dbf
ORA-00280: change 215591052 for thread 1 is in sequence #307
ORA-00278: log file '/test01/arch/arch_1_306_732382820.arc' no
longer needed for this recovery
When you have applied all the available logs, type CANCEL. Log /test01/arch/arch_1_306_732382820.arc was the last available log with me, so when it asked for log # 307, i typed CANCEL.

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
If you receive “Media recovery cancelled.” or “Media recovery complete.”, it means recovery is successful.

Now, when you try to open the database, you will get the following errors:

SQL> alter database open resetlogs;

alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
There is a workaround for this problem.

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
Now, open the initTEST.ora file and set the following parameter:

_no_recovery_through_resetlogs=TRUE
Save and close the file. Now again connect as sysdba and issue following commands:

SQL> startup mount;

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------
OPEN
Once the database is opened, you need to disable the redo thread 2 and drop its redo log groups:

Execute the following steps for this:

SQL> ALTER DATABASE DISABLE THREAD 2;

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;

SQL> ALTER DATABASE DROP LOGFILE GROUP 5;

SQL> ALTER DATABASE DROP LOGFILE GROUP 6;
Now, add the temp files to database as mentioned in the last part of control file trace.

SQL> ALTER TABLESPACE TEMP1 ADD TEMPFILE '/app01/oratest/TEST/db/apps_st/data/temp02.dbf' SIZE 1600M REUSE AUTOEXTEND OFF;
SQL> ALTER TABLESPACE TEMP1 ADD TEMPFILE '/app01/oratest/TEST/db/apps_st/data/temp01.dbf' SIZE 1100M REUSE AUTOEXTEND OFF;
SQL> ALTER TABLESPACE TEMP2 ADD TEMPFILE '/app01/oratest/TEST/db/apps_st/data/temp04.dbf' SIZE 1204M REUSE AUTOEXTEND OFF;
SQL> ALTER TABLESPACE TEMP2 ADD TEMPFILE '/app01/oratest/TEST/db/apps_st/data/temp03.dbf' SIZE 1024M REUSE AUTOEXTEND OFF;
SQL> exit;
Now go to $ORACLE_HOME/appsutil/install/ directory:

bash $ cd $ORACLE_HOME/appsutil/install/

bash $ sqlplus "/as sysdba"

SQL> @adupdlib.sql so

PL/SQL procedure successfully completed.

SQL> exit
Now, go to $ORACLE_HOME/appsutil/clone/bin/ and run the following command. Please note that the Database and the Listener should be up when you run this command.

bash $ perl adcfgclone.pl dbconfig $CONTEXT_FILE
step 9: Run autoconfig on DB Tier.

step 10: Run autoconfig on Apps Tier.

step 11:

On Apps Tier, go to $AD_TOP/bin and run:

bash $ perl admkappsutil.pl
It will create appsutil.zip file in $APPL_TOP/admin/out directory.

Copy this file to your DB ORACLE_HOME and unzip it there:

bash $ cd $ORACLE_HOME

bash $ unzip -o appsutil.zip
Then run autoconfig again on DB tier.

SQL> shutdown immediate;
Open your init.ora file and remove the parameter _no_recovery_through_resetlogs=TRUE which we set in step 8.

SQL> startup
Bring up the Application tier Services.

Thats it. Cheers, you have successfully refreshed your Test database from production.

Hope this helps.