From 11gR2 onwards, it is possible to duplicate the Oracle Database.I am going to describe you, step by step process for database refresh using RMAN Utility. In this example, my source database name is PRD where the target database is CLONE.
Step 1: Transfer the latest database backup
from production to clone server:
Login with Database User on Production
Server:
$cd /backup1/oracle/rman_backup
$scp -p Datafiles_HOT_TST_01072019* oracle@*.*.*.*.*:/clonedb/rman_backup
$scp -p archivelogs_TST_01072019* oracle@*.*.*.*.*:/clonedb/rman_backup
$scp -p Controlfiles_HOT_TST_01072019_20043.rman oracle@*.*.*.*.*:/clonedb/rman_backup
Step 2: Login on Cloned/Development Server:
Application Tier: Shutdown the application
$cd
$ADMIN_SCRIPTS_HOME
$ls –ltr
$./adstpall.sh
apps/clnpwd
$ps -ef |grep
FNDLIB |wc –l
Database Tier: Shutdown Database & Listener
Note: Take the
backup of $CONTEXT_FILE and pfile.
$sqlplus / as
sysdba
SQL>shutdown
immediate;
SQL>startup
restrict mount;
SQL>drop database;
SQL>exit
$lsnrctl stop
CLONE
Database Refresh Activity Start here:
$sqlplus / as
sysdba
SQL>startup
nomount
SQL>exit
$ rman
auxiliary /
RMAN> run
{
ALLOCATE
AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE
AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE
AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
ALLOCATE
AUXILIARY CHANNEL aux4 DEVICE TYPE DISK;
DUPLICATE
DATABASE TO "CLONE"
SPFILE
parameter_value_convert
('PRD','CLONE')
set
db_file_name_convert='/oradata1/oracle/PRD/db/apps_st/data/','/clonedb/oradata/app/data/'
set
log_file_name_convert='/oradata1/oracle/PRD/db/apps_st/data','/clonedb/oradata/app/data/'
set
control_files='/clonedb/oradata/app/data/cntrl01.dbf','/clonedb/oradata/app/data/cntrl02.dbf','/clonedb/oradata/app/data/cntrl03.dbf'
set
db_name='CLONE'
set
log_archive_dest_1 = 'LOCATION=/clonedb/oradata/app/data/archive'
set
diagnostic_dest='/clonedb/oradata/app/oracle/product/11.2.0/admin/CLONE_erpclonedb'
BACKUP
LOCATION '/clonedb/rman_backup'
NOFILENAMECHECK;
}
$cd
$ORACLE_HOME/appsutil/install/CLONE_erpclonedb
$sqlplus / as
sysdba
SQL>@adupdlib.sql
‘so’
SQL>exit
$cd
$ORACLE_HOME/appsutil/clone/bin
$perl
./adcfgclone.pl dbconfig $CONTEXT_FILE
Now, you should see the database node name in the
FND_NODES table.
Application Side:
$sqlplus
apps/prdpwd
SQL>select
NODE_NAME, SUPPORT_DB D, SUPPORT_ADMIN A, SUPPORT_CP C,
SUPPORT_FORMS
F, SUPPORT_WEB W from FND_NODES order by 1;
Note: If
Production DB node showing here then execute below script.
Clear source node information from
FND_NODES and other configuration tables.
SQL>EXEC
FND_CONC_CLONE.SETUP_CLEAN;
SQL>COMMIT;
SQL>exit
Change APPS User Password
$FNDCPASS
apps/prdpwd 0 Y system/manager SYSTEM APPLSYS changepwd
$FNDCPASS
apps/changepwd 0 Y system/manager USER SYSADMIN changeusrpwd
Database Side: Run Autoconfig on Database Tier
$ cd
/clonedb/oradata/app/oracle/product/11.2.0/appsutil/scripts/CLONE_erpclonedb
$ ls –ltr
$
./adautocfg.sh
Application Side: Run Autoconfig on Application Tier
$cd
$ADMIN_SCRIPTS_HOME
$ ls –ltr
$
./adautocfg.sh
$sqlplus
apps/changepwd
Stop the WF Mailer on clone server
SQL>update
WF_Notifications set mail_status='SENT' where mail_status='MAIL';
Stop the Scheduled Concurrent Program on clone server scheduled by Production server.
SQL>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';
SQL>commit;
SQL>exit
Database Side: Database start in noarchivelog
$cd
$ORACLE_HOME/apex
$ sqlplus / as
sysdba
SQL>@apxchpwd.sql
(ask from you ADMIN user password like ANicsi$#789)
SQL> alter
user apex_public_user identified by oracle account unlock;
SQL>
shutdown immediate
SQL>
startup mount
SQL>
archive log list
SQL> alter
database noarchivelog;
SQL> alter
database open;
Optional: (If Required REST Service then install it)
===================================================================
$cd
$ORACLE_HOME/apex
$ sqlplus /
as sysdba
SQL>@apex_rest_config.sql
(ask from you APEX_LISTENER, APEX_REST_PUBLIC_USER user password)
SQL>alter
user APEX_LISTENER identified by oracle account unlock;
SQL>alter
user APEX_REST_PUBLIC_USER identified by oracle account unlock;
Application Side: Configuration Apex Listener & start
$cd
/cloaneappl/apex/apex_listener
$ java -jar
apex.war setup
Enter the
name of the database server [localhost]:erpclonedb.nicsi.in
Enter the
database listen port [1521]:1536
Enter 1 to
specify the database service name, or 2 to specify the database SID [1]:2
Enter the
database service name: CLONE
Enter the
database user name [APEX_PUBLIC_USER]:
Enter the
database password for APEX_PUBLIC_USER: oracle
Confirm
password:oracle
Enter 1 to
enter passwords for the RESTful Services database users
(APEX_LISTENER,APEX_REST_PUBLIC_USER), 2 to use the same password as used for
APEX_PUBLIC_USER or, 3 to skip this step [1]:3
$ nohup java
-jar apex.war > startApex_19JUL19.log &
=================================================================
Start Application after database refresh
$cd
$ADMIN_SCRIPTS_HOME
$sqlplus
apps/changepwd
SQL>@cmclean.sql
SQL>commit;
SQL>exit
$ ls –ltr
$./adstrtal.sh
apps/changepwd
$ps -ef |grep
FNDLIB
Change the
Below Profile’s Values from Front end with sysadmin user:
1.
Site Name: ERP CLONE(12-JUL-19)
2.
Java Color Scheme: Choose any
colour from LOV.
3.
Apex Http Agent: http://erpcloneappl.nicsi.in:8080
4.
Apex Http Server: http://erpcloneappl.nicsi.in:8080
5.
FND: APEX URL: http://erpcloneappl.nicsi.in:8080/apex
