IBM Cloning document

Document Number: IBM-ORA/AMHI/ TA /003

Copy No: 1

                               

                                                                                               

Application and Database Refresh

Oracle Application R12

                                                                 

1.1          ABOUT THE DOCUMENT

It explains

Ø  Database Refresh

Ø  Application Refresh

Ø  Post Refresh steps

1.1.1         Purpose

The purpose of this document is to describe the steps required to be performed on the target system to refresh application and database from source system. This document contains steps to refresh the TEST instance from PROD instance.

1.2       Pre Cloning/Refresh Steps on TEST Server (14.139.56.152)

1. Bring down the Listener and Apps tier services using Appltest user.

cd $INST_TOP/admin/scripts

./adstpall.sh apps/<APPS Password>

2. Login with oratest user to bring down the 'TST' Database.

     Sqlplus ‘/as sysdba’

     Select name from v$database;

     Shutdown immediate;

     Stop the TST listener using command: -  lsnrctl stop TST      

3. Take the backup of CONTEXT_FILE and TNS_ADMIN files for Database Tier using oratest user.

cp /FMS1/11G/DB/product/11.2.0/dbhome_1/appsutil/TST_test.xml   /FMS1/11G/DB/product/11.2.0/dbhome_1/appsutil/TST_test.xml_04mar

cd  $TNS_ADMIN

mkdir BKP_4Mar

cp *.ora  BKP_4Mar

4. Take the backup of  CONTEXT_FILE and TNS_ADMIN files on Application Tier using Appltest user.

   

    cp /TESTAPP/R12/inst/apps/TST_test/appl/admin/TST_test.xml /TESTAPP/R12/inst/apps/TST_test/appl/admin/TST_test.xml_4Mar

    cd  $TNS_ADMIN

    mkdir BKP_4Mar

    cp *.ora BKP_4Mar

 5. Ensure that no session exists with APPLTEST and ORATEST user on TEST server(14.139.56.152)

1.3       Backup Transfer on TEST Server (14.139.56.152) as ORATEST User

1. Copy the backup from Production (/FMSBKP/RMAN_BACKUP/PROD) to /FMSBKP/RMAN_BACKUP/PROD on TEST server.

2.  Change the ownership of the backup copied on TEST server to oratest:dba and permission to 755.

Note: It may be a case that directory location is not present on the TEST/target server. Create the similar directory structure using soft links.

/FMSBKP

lrwxrwxrwx    1 oratest  dba              18 Jan 30 21:26 RMAN_BACKUP -> /Media/RMAN_BACKUP

1.4       Database Restoration on TEST Server (14.139.56.152) as ORATEST User

Note: - Database will be restored using the Production SID PROD on TEST server.

1. Go to ORACLE_HOME/dbs  (/FMS1/11G/DB/product/11.2.0/dbhome_1/dbs) using ORATEST user.

cd  /FMS1/11G/DB/product/11.2.0/dbhome_1/dbs

2. Ensure that paths in initPROD.ora exist in /FMS1/11G/DB/product/11.2.0/dbhome_1/dbs and have relevant path and parameters as per TEST instance.

3. Set the Variables for PROD.

$ export ORACLE_SID=PROD

$ export ORACLE_HOME=/FMS1/11G/DB/product/11.2.0/dbhome_1

4. Start the PROD instance on TEST server [14.139.56.152].

$ sqlplus '/as sysdba'

SQL> startup nomount

SQL>exit

5.  SET the DBID on TEST server [14.139.56.152]

The DBID can be found from log file of the backup dailyDB_X.log.

cd /FMSBKP/ RMAN_BACKUP/PROD

vi dailyDB_2.log

Note: - DBID can also be taken from production using the query “  Select DBID from v$database;”

$ rman target /

RMAN> set DBID=3959296745

6. Restore the control files on TEST server [14.139.56.152]

RMAN> restore controlfile from ‘/FMSBKP/ RMAN_BACKUP/PROD/PROD_prod_control_198_1_66p2c1t0’;

Note – Change the name of the control file backup piece PROD_prod_control_198_1_66p2c1t0 as per the backup taken from production.

  

The Following Restoration will restore 3 control files

/TESTDB/R12/db/apps_st/data/cntrl01.dbf

/TESTDB/R12/db/apps_st/data/cntrl02.dbf

/TESTDB/R12/db/apps_st/data/cntrl03.dbf

7. Mount the database PROD on APPSTEST server using Oracle user.

$ sqlplus '/as sysdba'

SQL> Alter database mount;

SQL> exit;

8. RMAN restore database on TEST server [14.139.56.152] as ORATEST User

Incase location where the data files should be restored as the directory structure is all together different from the Production for test server.  Rename the file using below syntax in restore script.

"SET NEWNAME FOR DATAFILE '<Path in Prod>/<datafile Name>.dbf' to '<Path in Testserver>/<datafile Name>.dbf'"

Note: Compare and manually edit all the set newname for datafile, as there might have been an addition of a datafile, so pls cross check with the Production instance of all the datafiles.

9. Execute the script to restore the database.

cd /home/oratest/scripts/cloning_scripts/rman_scr

$ nohup /home/oratest/scripts/cloning_scripts/rman_scr/tst_restore.sh &

Note - Please check the paths and variables in the script before executing.

10. Check the log file of restoration and ensure that database has restored.

11. Restore the archive logs from the backup on TEST instance [14.139.56.152] as ORATEST User

rman nocatalog

connect target /

RUN                                         

{

SET ARCHIVELOG DESTINATION TO '/FMSBKP/RMAN_BACKUP/Archive';

restore archivelog sequence 255;

}

Note: Sequence numbers to be restored can be found from the log file of the backup dailyDB_2.log.

cd /FMSBKP/ RMAN_BACKUP/PROD

vi dailyDB_2.log

12. Shutdown the PROD instance on TEST server [14.139.56.152] as ORATEST User

Sqlplus ‘/as sysdba’

Shutdown immediate;

13. Rename the existing control files on TEST server [14.139.56.152] as ORATEST User.

We need to rename the existing Control files as we had already restored the control files earlier.

cd  /TESTDB/R12/db/apps_st/data

$ ls cnt*

cntrl01.dbf 

cntrl02.dbf 

cntrl03.dbf

$ mv cntrl01.dbf cntrl01.dbf.old

$ mv cntrl02.dbf cntrl02.dbf.old

$ mv cntrl03.dbf cntrl03.dbf.old

14. Re-Create the control files of TST instance on TEST server [14.139.56.152] as ORATEST User.

$ export ORACLE_SID=TST

$ sqlplus '/as sysdba'

SQL> startup nomount;

SQL>@/home/oratest/scripts/cloning_scripts/rman_scr/create_TST_ctrl.sql

Note: Number of files may vary in case of addition of files on production. Please verify the same from production with the files restored in TEST in step 9.

15. Recover TST Database on TEST Server

SQL> set logsource ‘/FMSBKP/RMAN_BACKUP/Archive’;

SQL> recover database UNTIL CANCEL using backup controlfile;

Select option AUTO and ensure that archive log extracted in step 11 has been applied.

16. Open the Database with Resetlogs on TEST server using oratest user.

SQL> alter database open resetlogs;

SQL> shutdown immediate

17. Bounce TST database on TEST server using oratest user

SQL> startup mount;


SQL> alter database noarchivelog;

SQL> alter database open;

18. Add TEMP file to TST DB on TEST server using ORATEST user.

ALTER TABLESPACE TEMP1 ADD TEMPFILE '/TESTDB/R12/db/apps_st/data/temp01.dbf'  SIZE 2000M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP2 ADD TEMPFILE '/TESTDB/R12/db/apps_st/data/temp02.dbf'  SIZE 2000M REUSE AUTOEXTEND OFF;

19. Start Listener on TEST server

$ lsnrctl start TST

1.5       Application Refresh on TEST Server (14.139.56.152)

1. Clear the FND_NODES as ORATEST user on TEST server (Connect with apps password of PROD)

$ sqlplus apps/<apps Password>

SQL> exec fnd_conc_clone.setup_clean

2. Run adconfig.sh on DB tier using ORATEST user on TEST server

cd  /FMS1/11G/DB/product/11.2.0/dbhome_1/appsutil/scripts/TST_test

./adautocfg.sh

3. Run adpreclone.pl from $INST_TOP/admin/scripts on Appstier on DBPRD1, APPLPRD1 and APPLPRD2 using applmgr user.(PROD ONLY)

   Perl adpreclone.pl AppsTier

4. Create TAR backup of application component (R12 Directory) from DBPRD1 using applmgr user.

5. Copy the TAR from DBPRD1 source system to TEST target server using scp.

6.  Remove the application component from “/TESTAPP/R12” present on TEST instance using Appltest user.

7. Extract the TAR backup copied above under application path “/TESTAPP/R12” of TEST instance.

8. Change the ownership of the application component to “appltest:dba” under path “/TESTAPP/R12”.

9. Execute adcfgclone.pl from $COMMON_TOP/clone/bin (/TESTAPP/R12/apps/apps_st/comn/clone/bin) using Appltest user on TEST server.

10. Update the application tables for NODE_NAME and DB_INSTANCE

$ Sqlplus apps/<apps password>

SQL> update fnd_concurrent_processes set NODE_NAME='TEST' where NODE_NAME='DBPRD1';

SQL> update fnd_concurrent_processes set NODE_NAME='TEST' where NODE_NAME='DBPRD2';

SQL> update fnd_concurrent_processes set DB_INSTANCE='TST' where DB_INSTANCE='PROD';

SQL> update fnd_concurrent_queues set NODE_NAME='TEST' where NODE_NAME='DBPRD1';

SQL> update fnd_concurrent_queues set NODE_NAME='TEST' where NODE_NAME='DBPRD2';

11. Set Terminating or Running to Completed/Terminated

UPDATE fnd_concurrent_requests SET phase_code = 'C', status_code = 'X' WHERE status_code ='T' OR phase_code = 'R';

UPDATE fnd_concurrent_requests SET hold_flag = 'Y' WHERE phase_code = 'P'  AND status_code in ('Q','I');

SQL> commit;

12. Change Apps Password as per APPS Password.

Connect as sysdba on TEST server using ORATEST User.

Sqlplus ‘/as sysdba’

Alter user system identified by tester;

Connect with APPLTEST user and change the APPS password.

FNDCPASS APPS/<PROD PWD> 0 Y system/<PWD> SYSTEM APPLSYS <New PWD>

13. Run Autoconfig on DB and APPS servers

Run adautocfg.sh [as ‘ORATEST’ on “TEST” server]

cd  /FMS1/11G/DB/product/11.2.0/dbhome_1/appsutil/scripts/TST_test

./adautocfg.sh

Run adautocfg.sh [as ‘APPLTEST’ on “TEST” server]

cd $INST_TOP/admin/scripts

./adautocfg.sh

14. Check the tns files generated on application and database tier with the backup files taken in step pre-cloning step 3.

Note: Incase of discrepancy restore the file from backup.

15. Start Application services on TEST Server using Appltest user.

cd $INST_TOP/admin/scripts/

./adstartal.sh apps/<apps pwd>

1.6       Post Refresh Steps on TEST Server (14.139.56.152)

1. Change the SYSADMIN User password.

2. Change the Site level profile value from 'PROD' to 'TST – Cloned on <Date>’ or use the following query to update the same.

update fnd_profile_option_Values set PROFILE_OPTION_VALUE = 'ISB - Cloned On 27OCT13'  where PROFILE_OPTION_ID = 125;

3. Configure the Workflow mailer with TEST instance testfms user ID.

4. Perform Health check by submitting ‘Active Users’ Request.

5. Configure the temporary path for XML publisher administrator. (/TESTAPP/R12/inst/apps/TST_test/appltmp/TST)

6. Change the readonly password to tester using query. (Alter user readonly identified by <Password>; )