|
Document Number:
IBM-ORA/AMHI/ TA /003
|
|
Copy No: 1
|
Database Refresh
Oracle
Application R12
Indian
Council of Agricultural Research
Version 1.0
Prepared by
|
REVISION
HISTORY
|
|||||
|
Document No.:
IBM-ORA/AMHI/ TA /002
|
Document Title: Database Refresh
|
||||
|
Version No.
|
Issue Date
|
Change Details
|
Changed by
|
Authorized by
|
Remarks
|
|
1.0
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
DISTRIBUTION
|
||
|
Copy No.
|
Name
|
Location
|
|
1.
|
|
|
|
2
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1.1
ABOUT THE
DOCUMENT
|
It explains
Ø Database
Refresh steps of target ERP database
1.1.1
Purpose
The purpose of the database refresh document is to describe the steps
required to be performed on the target system to refresh the 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
$
cd /home/oracle/
$
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 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
Configuration 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;
SQL>
COMMIT;
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
Note: This will ask for APPS password, provide the
prod(Source) apps schema password.
3.
Update the application tables for NODE_NAME and DB_INSTANCE
$
sqlplus apps/<apps password>
SQL> select
distinct(node_name) from fnd_concurrent_processes ;
NODE_NAME
------------------------------
DBPRD2
DBPRD1
SQL> select
node_name from fnd_nodes;
NODE_NAME
------------------------------
TEST
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> select
distinct(DB_INSTANCE) from fnd_concurrent_processes ;
DB_INSTANCE
----------------
PROD
SQL> select
name from v$database;
NAME
---------
TST
SQL>
update fnd_concurrent_processes set DB_INSTANCE='TST' where DB_INSTANCE='PROD';
SQL>
select distinct(node_name) from fnd_concurrent_queues ;
NODE_NAME
------------------------------
DBPRD1
SQL>
update fnd_concurrent_queues set NODE_NAME='TEST' where NODE_NAME='DBPRD1';
4.
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;
5.
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>
6.
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
Note:
This will ask for APPS password, provide the password changed in step 5.
Run adautocfg.sh [as ‘APPLTEST’ on “TEST” server]
cd
$INST_TOP/admin/scripts
./adautocfg.sh
Note:
This will ask for APPS password, provide the password changed in step 5.
7. 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.
8.
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 = 'TST - 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>; )
No comments:
Post a Comment