Hi DBAs,
Cloning is one of the regular tasks done by Apps DBA & DBAs. I have done lot of clonning
in various ways,below is one such task.Hope you will like it. Theses steps are applicable for
Oracle EBS Release 12.1.3 and Database version is 11.2.0.4.0
Best Oracle Apps DBA Blog on Google Search Engine
Step 1: Source Environment Preparation Steps:
Run adpreclone.pl on DB and Application Tier
DB Tier:
cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
perl adpreclone.pl dbtier
Application Tier:
cd $ADMIN_SCRIPTS_HOME
perl adpreclone.pl appsTier
Step 2: Take Backup of Database and Applications:
Database:
Use the below script to take full rman backup taken can be used for cloning on source Environment.
run
{
configure device type disk parallelism 6;
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
backup full database format '/backup/rman/EBSTEST/juneDD/EBSTEST_Full%d_%s_%T_%U' database;
backup format '/backup/rman/EBSTEST/juneNN/EBSTEST_ARCH%d_%s_%T_%U' archivelog all;
backup current controlfile format '/backup/rman/EBSTEST/juneNN/control%d%t_control.ctl';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}
Note: Replace DD with the date or any other directory name as required.
Application Tier:
Compress the top directories using the below commands:
tar –cf /backup/PROD/appl/appsst<date>.tar /erpapp/apps/apps_st
tar –cf /backup/PROD/appl/techst<date>.tar /erpapp/apps/tech_st
Step 3:Copy and Extract Files to DEV Node:
Copy the appsst.tar and techst.tar to respective locations in DEV and extract using:
tar –xf appsst.tar
tar –xf techst.tar
Step 4:Preparing DEV for Refresh:
a) Shutdown Applications and Database Services
b) Take DEV backup if required
c) Take a backup of pfile
d) sqlplus ‘/as sysdba’
startup mount
alter system enable restricted session;
drop database;
e) cp initTESTEBS.ora initEBSTEST.ora
vi initEBSTEST.ora
db_name=EBSTEST
control_files='+DATA/TESTEBS/CONTROLFILE/ctr<new1>.dbf','+DATA/TESTEBS/CONTROLFILE/ctr<new2>.dbf ','+DATA/TESTEBS/CONTROLFILE/ctr<new3>.dbf '
export ORACLE_SID=EBSTEST
startup nomount
Step 5: Restore and Recover Database
Restore controlfile from backup of source Database which should be copied using scp to the
target DB server.
rman target /
restore controlfile from ‘<PATH OF CONTROLFILE BACKUP>’
alter database mount;
crosscheck backup;
delete expired backup;
crosscheck backup;
list backup;
Note:Find the latest sequence and thread number and use them in place of “SEQUENCE” and “N” in below script.
run
{
set until sequence <SEQUENCE> thread <N>;
configure device type disk parallelism 6;
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
set newname for database to '+DATA';
restore database;
switch datafile all;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}
exit;
This completes the database restore and recovery.
Step 6: Configuring DEV Database
Now the Database is in Mount State with EBSTEST name.
Change the redolog file names of Thread 1 to +RECO
a. alter database rename file ‘+RECO/EBSTEST…’ to ‘NEW PATH’
alter database backup controlfile to trace
shut immediate
b. Rename trace file to ctrl.sql
vi ctrl.sql
Change “REUSE” to “SET”
Change “EBSTEST” to “EBSDEV”
Change “noresetlogs” to “resetlogs”
Change “archivelog” to “noarchivelog”
c. cp initEBSTEST.ora initEBSDEV.ora
Change db_name=AMDEV in initEBSDEV.ora
d. export ORACLE_SID=EBSDEV
e. sqlplus ‘/as sysdba’
@ctrl.sql
alter database open resetlogs;
f. Run Autoconfig
This completes the Database Cloning
Before starting cloning, modify FND Template as below:
cd $FND_TOP/admin/driver/fndtmpl.drv
txkWfClone.sh INSTE8_SETUP to txkWfClone.sh INSTALL_SETUP
Step 7:Clone Application Tier:
cd /erpapp/apps/apps_st/comn/clone/bin
perl adcfgclone.pl appsTier
Answer the prompts and do not start services once apply is complete
Step 8:Take a backup of context file
vi $CONTEXT_FILE
Change the load balancing related parameters
Step 9:Start the EBS applications
5.11 adstrtal.sh apps/<password>
Post Cloning Steps:
a) Change logo
Upload the below logo to $OA_MEDIA/COMPLOGO_SC.jpg
b) Run the Data masking script:
c) Cancel the Running/Pending Concurrent Requests
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N';
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE phase_code IN ('R','P');
Commit;
d) Disable Workflow Notification Mailer. Set the override address from OAM. Purge pending notifications.
UPDATE WF_NOTIFICATIONS
SET MAIL_STATUS = 'SENT', STATUS = 'CLOSED'
where status in ('OPEN', 'CANCELED')
and mail_status in ('MAIL', 'INVALID');
sqlplus apps/appspwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps appspwd applsys
update wf_roles set NOTIFICATION_PREFERENCE=’DISABLED’,email_address=null;
e) Update per_all_people_f set email_address=null where email_address is not null;
f) Change the profile options:
Site Name to “DEV Instance”
Java Color Scheme to a color which is not same as PROD
e) Change the system, apps, sysadmin, xxcomp passwords
f) Add tempfile to Temp Tablespace
g) Change the URLs previously in Context File configured for Load Balancing in PROD and run autoconfig.
s_external_url http://testebs.COMPLOGO.ae:8000
s_login_page http://testebs.COMPLOGO.ae:8000/OA_HTML/AppsLogin
s_webentryhost testebs
s_webentrydomain COMPLOGO.ae
h) Update the Node information for all Concurrent Managers from Concurrent Manager Define to current node name and restart concurrent managers using adcmctl.sh
i) DEACTIVATE “Periodic Alert Scheduler”
j) Update Discoverer URL in Context File ( Application Tier - s_disco_url):
http://customsoadev.COMPLOGO.ae:7777
k) erpuat@COMPLOGO.ae shall be used as the Email ID for notifications in case if WF Notification Mailer is being enabled
Hope you followed clonning steps. Enjoy Apps DBA tasks.
I will soon be posting ERP 12.2 cloning Stay tune...
Thanks,
Rafi
www.orasols.com
Cloning is one of the regular tasks done by Apps DBA & DBAs. I have done lot of clonning
in various ways,below is one such task.Hope you will like it. Theses steps are applicable for
Oracle EBS Release 12.1.3 and Database version is 11.2.0.4.0
Best Oracle Apps DBA Blog on Google Search Engine
Step 1: Source Environment Preparation Steps:
Run adpreclone.pl on DB and Application Tier
DB Tier:
cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
perl adpreclone.pl dbtier
Application Tier:
cd $ADMIN_SCRIPTS_HOME
perl adpreclone.pl appsTier
Step 2: Take Backup of Database and Applications:
Database:
Use the below script to take full rman backup taken can be used for cloning on source Environment.
run
{
configure device type disk parallelism 6;
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
backup full database format '/backup/rman/EBSTEST/juneDD/EBSTEST_Full%d_%s_%T_%U' database;
backup format '/backup/rman/EBSTEST/juneNN/EBSTEST_ARCH%d_%s_%T_%U' archivelog all;
backup current controlfile format '/backup/rman/EBSTEST/juneNN/control%d%t_control.ctl';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}
Note: Replace DD with the date or any other directory name as required.
Application Tier:
Compress the top directories using the below commands:
tar –cf /backup/PROD/appl/appsst<date>.tar /erpapp/apps/apps_st
tar –cf /backup/PROD/appl/techst<date>.tar /erpapp/apps/tech_st
Step 3:Copy and Extract Files to DEV Node:
Copy the appsst.tar and techst.tar to respective locations in DEV and extract using:
tar –xf appsst.tar
tar –xf techst.tar
Step 4:Preparing DEV for Refresh:
a) Shutdown Applications and Database Services
b) Take DEV backup if required
c) Take a backup of pfile
d) sqlplus ‘/as sysdba’
startup mount
alter system enable restricted session;
drop database;
e) cp initTESTEBS.ora initEBSTEST.ora
vi initEBSTEST.ora
db_name=EBSTEST
control_files='+DATA/TESTEBS/CONTROLFILE/ctr<new1>.dbf','+DATA/TESTEBS/CONTROLFILE/ctr<new2>.dbf ','+DATA/TESTEBS/CONTROLFILE/ctr<new3>.dbf '
export ORACLE_SID=EBSTEST
startup nomount
Step 5: Restore and Recover Database
Restore controlfile from backup of source Database which should be copied using scp to the
target DB server.
rman target /
restore controlfile from ‘<PATH OF CONTROLFILE BACKUP>’
alter database mount;
crosscheck backup;
delete expired backup;
crosscheck backup;
list backup;
Note:Find the latest sequence and thread number and use them in place of “SEQUENCE” and “N” in below script.
run
{
set until sequence <SEQUENCE> thread <N>;
configure device type disk parallelism 6;
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
set newname for database to '+DATA';
restore database;
switch datafile all;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}
exit;
This completes the database restore and recovery.
Step 6: Configuring DEV Database
Now the Database is in Mount State with EBSTEST name.
Change the redolog file names of Thread 1 to +RECO
a. alter database rename file ‘+RECO/EBSTEST…’ to ‘NEW PATH’
alter database backup controlfile to trace
shut immediate
b. Rename trace file to ctrl.sql
vi ctrl.sql
Change “REUSE” to “SET”
Change “EBSTEST” to “EBSDEV”
Change “noresetlogs” to “resetlogs”
Change “archivelog” to “noarchivelog”
c. cp initEBSTEST.ora initEBSDEV.ora
Change db_name=AMDEV in initEBSDEV.ora
d. export ORACLE_SID=EBSDEV
e. sqlplus ‘/as sysdba’
@ctrl.sql
alter database open resetlogs;
f. Run Autoconfig
This completes the Database Cloning
Before starting cloning, modify FND Template as below:
cd $FND_TOP/admin/driver/fndtmpl.drv
txkWfClone.sh INSTE8_SETUP to txkWfClone.sh INSTALL_SETUP
Step 7:Clone Application Tier:
cd /erpapp/apps/apps_st/comn/clone/bin
perl adcfgclone.pl appsTier
Answer the prompts and do not start services once apply is complete
Step 8:Take a backup of context file
vi $CONTEXT_FILE
Change the load balancing related parameters
Step 9:Start the EBS applications
5.11 adstrtal.sh apps/<password>
Post Cloning Steps:
a) Change logo
Upload the below logo to $OA_MEDIA/COMPLOGO_SC.jpg
b) Run the Data masking script:
c) Cancel the Running/Pending Concurrent Requests
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N';
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE phase_code IN ('R','P');
Commit;
d) Disable Workflow Notification Mailer. Set the override address from OAM. Purge pending notifications.
UPDATE WF_NOTIFICATIONS
SET MAIL_STATUS = 'SENT', STATUS = 'CLOSED'
where status in ('OPEN', 'CANCELED')
and mail_status in ('MAIL', 'INVALID');
sqlplus apps/appspwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps appspwd applsys
update wf_roles set NOTIFICATION_PREFERENCE=’DISABLED’,email_address=null;
e) Update per_all_people_f set email_address=null where email_address is not null;
f) Change the profile options:
Site Name to “DEV Instance”
Java Color Scheme to a color which is not same as PROD
e) Change the system, apps, sysadmin, xxcomp passwords
f) Add tempfile to Temp Tablespace
g) Change the URLs previously in Context File configured for Load Balancing in PROD and run autoconfig.
s_external_url http://testebs.COMPLOGO.ae:8000
s_login_page http://testebs.COMPLOGO.ae:8000/OA_HTML/AppsLogin
s_webentryhost testebs
s_webentrydomain COMPLOGO.ae
h) Update the Node information for all Concurrent Managers from Concurrent Manager Define to current node name and restart concurrent managers using adcmctl.sh
i) DEACTIVATE “Periodic Alert Scheduler”
j) Update Discoverer URL in Context File ( Application Tier - s_disco_url):
http://customsoadev.COMPLOGO.ae:7777
k) erpuat@COMPLOGO.ae shall be used as the Email ID for notifications in case if WF Notification Mailer is being enabled
Hope you followed clonning steps. Enjoy Apps DBA tasks.
I will soon be posting ERP 12.2 cloning Stay tune...
Thanks,
Rafi
www.orasols.com
