Clone Production database to Demo

1. Stop DEMO database

set ORACLE_SID=demo
set ORACLE_HOME=C:\app\oracle\product\11.2.0\dbhome_1
C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus / as sysdba
shutdown abort;

2. Purge DEMO database

del /F /Q /S C:\app\oracle\fast_recovery_area\demo\
del /F /Q /S C:\app\oracle\admin\demo\
del /F /Q /S F:\oradata\demo\
del /F /Q /S E:\fast_recovery_area\demo\
del /F /Q /S F:\fast_recovery_area\demo\

mkdir C:\app\oracle\fast_recovery_area\demo
mkdir C:\app\oracle\admin
mkdir C:\app\oracle\admin\demo
mkdir C:\app\oracle\admin\demo\adump
mkdir C:\app\oracle\admin\demo\bdump
mkdir C:\app\oracle\admin\demo\dpdump
mkdir C:\app\oracle\admin\demo\pfile
mkdir F:\oradata\demo
mkdir F:\oradata\demo\datafile
mkdir E:\fast_recovery_area\demo
mkdir E:\fast_recovery_area\demo\archivelog
mkdir F:\fast_recovery_area\demo
mkdir F:\fast_recovery_area\demo\archivelog

3. Create Password file - SYS password needs to be the same on both databases

cd /D C:\app\oracle\product\11.2.0\dbhome_1\database
ORAPWD FILE=PWDdemo.ora ENTRIES=10

4. Listener.ora
Add a static entry for demo

5. Fix E:\periodic\oracle_rman_clone.rcv
Run this against Production database to get a list of latest datafiles

SELECT 'SET NEWNAME FOR DATAFILE ' || file# || ' TO ''' || REPLACE (name, 'E:\ORADATA\PROD', 'F:\ORADATA\DEMO') || ''';' sqlline
  FROM v$datafile
 ORDER BY file#;

Refresh the datafile list in oracle_rman_clone.rcv, and we need to fix the archivelog sequence # base on Production RMAN output

#
# SVN Revision Information:
# $Revision: 2021 $
# $Author: pchiu $
# $Date: 2016-12-10 00:45:01 -0500 (Sat, 10 Dec 2016) $
#
#
#  SELECT 'SET NEWNAME FOR DATAFILE ' || file# || ' TO ''' || REPLACE (name, 'E:\ORADATA\PROD', 'F:\ORADATA\DEMO') || ''';' sqlline
#    FROM v$datafile
#ORDER BY file#;
#
run {  
SET NEWNAME FOR DATAFILE 1 TO 'F:\ORADATA\DEMO\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'F:\ORADATA\DEMO\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'F:\ORADATA\DEMO\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'F:\ORADATA\DEMO\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'F:\ORADATA\DEMO\EXAMPLE01.DBF';
SET NEWNAME FOR DATAFILE 6 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWQS94L_.DBF';
SET NEWNAME FOR DATAFILE 7 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_HCS_STAG_BHWQVY73_.DBF';
SET NEWNAME FOR DATAFILE 8 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_HCS_STAG_BHWQX19T_.DBF';
SET NEWNAME FOR DATAFILE 9 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_DATA_BHWQXTD2_.DBF';
SET NEWNAME FOR DATAFILE 10 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_APEX_DAT_BHWR0HHJ_.DBF';
SET NEWNAME FOR DATAFILE 11 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_INDE_BHWR18JT_.DBF';
SET NEWNAME FOR DATAFILE 12 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_BT_DATA_BHWR2CLM_.DBF';
SET NEWNAME FOR DATAFILE 13 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_HIST_BHWR2GNF_.DBF';
SET NEWNAME FOR DATAFILE 14 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWR37PG_.DBF';
SET NEWNAME FOR DATAFILE 15 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_LECXE_DA_BHWR5WSR_.DBF';
SET NEWNAME FOR DATAFILE 16 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_LECXE_LO_BHWR6CVX_.DBF';
SET NEWNAME FOR DATAFILE 17 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWR7GY4_.DBF';
SET NEWNAME FOR DATAFILE 18 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWR9J1Q_.DBF';
SET NEWNAME FOR DATAFILE 19 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BQOXCZNQ_.DBF';
SET NEWNAME FOR DATAFILE 20 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_C2OH4RCN_.DBF';
SET NEWNAME FOR DATAFILE 21 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CB028886_.DBF';
SET NEWNAME FOR DATAFILE 22 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CF0WL1ML_.DBF';
SET NEWNAME FOR DATAFILE 23 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CNCN5RBS_.DBF';
SET NEWNAME FOR DATAFILE 24 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CQ5ZWVP8_.DBF';
SET NEWNAME FOR DATAFILE 25 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_INDE_CT7MCBWW_.DBF';
SET NEWNAME FOR DATAFILE 26 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CT7MN0X5_.DBF';
SET NEWNAME FOR DATAFILE 27 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CXRZ4VD6_.DBF';
SET NEWNAME FOR DATAFILE 28 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_D14533D2_.DBF';
SET NEWNAME FOR DATAFILE 29 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_D4KCSQ85_.DBF';
SET NEWNAME FOR TEMPFILE 1 TO 'F:\oradata\demo\temp01.dbf';
DUPLICATE TARGET DATABASE TO 'DEMO'
FROM ACTIVE DATABASE
SPFILE 
PARAMETER_VALUE_CONVERT 'PROD','DEMO'
SET DB_CREATE_FILE_DEST 'F:\ORADATA\DEMO'
SET dispatchers '(PROTOCOL=TCP) (SERVICE=demoXDB)'
SET control_files 'F:\ORADATA\DEMO\CONTROL01.CTL'
SET LOG_ARCHIVE_DEST_1 'location=E:\fast_recovery_area\demo\archivelog'
SET LOG_ARCHIVE_DEST_10 'location=USE_DB_RECOVERY_FILE_DEST'
NOFILENAMECHECK
OPEN RESTRICTED
SKIP READONLY;
}

6. Clone database
Startup database

set ORACLE_SID=demo
set ORACLE_HOME=C:\app\oracle\product\11.2.0\dbhome_1
C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus / as sysdba
STARTUP NOMOUNT PFILE='C:\app\oracle\product\11.2.0\dbhome_1\database\initdemo.ora';

C:\app\oracle\product\11.2.0\dbhome_1\database\initdemo.ora

DB_NAME=demo

Start cloning

C:\app\oracle\product\11.2.0\dbhome_1\bin\rman target sys/xxxxx@prod auxiliary sys/xxxxx@demo CMDFILE E:\periodic\oracle_rman_clone.rcv LOG F:\oracle_backup\dmp\oracle_rman_clone_test.log

7. Post cloning

C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus / as sysdba
ALTER SYSTEM SET DB_CREATE_FILE_DEST='F:\ORADATA'

C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus accdb/xxxxx@benecaid_demo
BEGIN
  migration_pkg.migration_demo;
END;
/
@E:\periodic\demo_patch01.sql
-- @E:\periodic\demo_patch02.sql

8. Refresh all Oracle Forms, Oracle Reports and Jasper Reports from Production