How to database refresh from production server to dev/clone server in Oracle Apps

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