How to refresh oracle(dev/tst/trn) Database from Production using RMAN

Simple Clone from Production SID to non Prod SID

Please make sure RMAN completed without any error in Production.

This is my directory structure on Prod_SID side.

/orafra/Prod_SID

drwxr-x---. 2 uid dba 4096 Dec 29  2014 onlinelog

drwxr-x---. 2 uid dba 4096 Jul  9  2015 autobackup

drwxr-x---. 3 uid dba 4096 Feb  9 12:43 backupset

drwxr-x---. 2  uid dba 4096 Feb  9 13:54 flashback

drwxr-x---. 5 uid dba 4096 Feb 11 01:36 archivelog

/orafara/Nprod_SID

drwxr-x---. 2 uid dba 4096 Dec 29  2014 onlinelog

drwxr-x---. 2 uid dba 4096 Jul  9  2015 autobackup

drwxr-x---. 3 uid dba 4096 Feb  9 12:43 backupset

drwxr-x---. 2 uid dba 4096 Feb  9 13:54 flashback

drwxr-x---. 5 uid dba 4096 Feb 11 01:36 archivelog

eg: /orafra/P_SID/backupset/2016_02_26/*.bkp

$ scp *.bkp uid@servername.xxx.xxx:/orafra/D_SID/backup_sid/

$ ls  -l  *.bkp 

Make sure all the files are copied in the dev location.

Now you have to edit init.ora file.

In my case all the file are located /oradatra/Prod_SID/logfie, dbf file, ctl file 

the below two lines should in your nonProd_SID init.ora file.

*.db_file_name_convert = (/oradata/Prod_SID/,/oradata/Nprod_SID/)

*.log_file_name_convert = (/oradata/Prod_SID/,/oradata/NPROD_SID/)

• . Please make sure that your env file set for your Target database.

    sqlplus '/as sysdba'

SQL> startup nomount pfile='/xxxx/xxxx/admin/SID/pfile/Nprod_sid.ora

ORACLE instance started.

 /

Total System Global Area 4275781632 bytes

Fixed Size                  2260088 bytes

Variable Size             704643976 bytes

Database Buffers         3556769792 bytes

Redo Buffers               12107776 bytes

SQL> exit

$rman auxiliary /

RMAN>duplicate database to Nprod_SID backup location 'orafra/nprod_SID/backupset/2016_02_26';

.......
.........

database opened
Reenabling controlfile options for auxiliary database
Executing: alter database flashback on
Finished Duplicate Db at 26-FEB-16 


If you are working in Oracle EBS side. Please execute the following scripts.

Source your db env file.

login as apps user

 sqlplus apps/xxxxxx(production_password)

(if you using  12.2.4 application do not use  FND_CONC_CLONE.SETUP_CLEAN).

 sql# exec FND_CONC_CLONE.SETUP_CLEAN; 

commit;
  select count(*) from fnd_nodes;

now you won't see any value in fnd_nodes.
exit;

Take backup of TNS_ADMIN

execute adconfig.sh
$ORACLE_HOME/appsutil/bin/adconfig.sh

Enter the full path to the Context file: $ORACLE_HOME/appsutil/SID_HOSTname.xml
(display variable)
adconfig. completed.

Make sure tnsname.ora, listner.ora, sqlnet.ora files are correct.

Make sure all your dblinks are working. 

Assume you have apps tier server.....

Run adconfig on apps Tier

After adconfig completed, you can reset APPS  password using fndcpass and make sure you system account is unlocked.

Happy learning and share the world.