DUPLICATE DATABASE(AUXILIARY)

REM                DUPLICATE DATABASE(AUXILIARY)
REM
REM OVERVIEW
REM STEP 1.Set environment variable ORACLE_SID for auxiliary instance
REM STEP 2.Create an Oracle Password File for the Auxiliary Instance
REM STEP 3.Create an Initialization Parameter File for the Auxiliary Instance
REM STEP 4.Ensure Oracle Net Connectivity to the Auxiliary Instance
REM STEP 5.Start the Auxiliary Instance 
REM STEP 6.Prepare LVM if you need
REM STEP 7.Mount or Open the Target Database
REM STEP 8.Make Sure You Have the Necessary Backups and Archived Redo Logs
REM STEP 9.Allocate Auxiliary Channels if Automatic Channels Are Not Configured
REM STEP10.Set the datafile's path and duplicate database to auxiliary database
REM STEP11.Delete parameters(log_file_name_convert,db_file_name_convert) and create spfile
REM STEP12.Create own temporary tablespace and undo tablespace
REM STEP13.Others:Migrate or Upgrade
REM
REM Let's GO!
REM

REM #####################################################################
REM ##STEP 1.Set environment variable ORACLE_SID for auxiliary instance##
REM #####################################################################

-- Set Auxiliary database's PATH
   set ORACLE_SID=dup
   set PATH=%ORACLE_HOME%\bin;%PATH%

-- If you create the Auxiliary database on Windows,you have to execute two statement below
   D:\app\Administrator\product\11.1.0\db_1\bin\oradim.exe -new -sid DUP -startmode manual -spfile 
   D:\app\Administrator\product\11.1.0\db_1\bin\oradim.exe -edit -sid DUP -startmode auto -srvcstart system

REM #####################################################################
REM ##STEP 2.Create an Oracle Password File for the Auxiliary Instance ##
REM #####################################################################

-- On LINUX/UNIX
   orapwd file=$ORACLE_HOME/dbs/orapw${ORACLE_SID} password=change_on_install entries=20

-- On Windows
   orapwd file=%ORACLE_HOME%/database/orapw${ORACLE_SID} password=change_on_install entries=20

REM #############################################################################
REM ##STEP 3.Create an Initialization Parameter File for the Auxiliary Instance##
REM #############################################################################

-- You'd better create the pfile to $ORACLE_HOME/dbs on Auxiliary Database, or be sure that there is a copy of pfile in $ORACLE_HOME/dbs
-- Because RMAN will startup the Auxiliary database with a pfile that in $ORACLE_HOME/dbs when the duplication is done
-- And then it will create controlfile of Auxiliary database
-- If there is no pfile in $ORACLE_HOME/dbs, the Auxiliary database cannot startup normally, and follow-up steps cannot be done
   create pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora

-- Add two parameters into pfile
-- log_file_name_convert Transforms target filenames, for example, from log_* to duplog_*. Note that you can specify multiple conversion pairs.
-- You can use this parameter when you don't specify the LOGFILE clause of DUPLICATE command.
-- db_file_name_convert   Transforms target filenames, for example, from /oracle/ to /dup/oracle/. Note that you can specify multiple conversion pairs.
-- You can use this parameter for those files not renamed with either SET NEWNAME and CONFIGURE AUXNAME.
-- We will discuss the KEY words(LOGFILE and SET NEWNAME, CONFIGURE AUXNAME) later
-- And if you don't specify any of KEY words we mentioned yet, You must specify the NOFILENAMECHECK option
-- RMAN will make duplicate filenames as same as target filenames, and the duplicate database must be in another host
   *.log_file_name_convert=('/rdb/oradata/ora9','/u01/oradata/ora9')
   *.db_file_name_convert=('/trsgfifs/oradata','/u01/oradata/ora9')
--                                                                      ↑                            ↑
--                                                  target directory    auxiliary directory

-- Note that you can specify multiple conversion pairs. For example
   *.db_file_name_convert=('/trsgfifs/oradata','/u01/oradata/ora9','/trsgfifs/oradata','/u01/oradata/ora9')

REM ######################################################################
REM ##STEP 4.Ensure Oracle Net Connectivity to the Auxiliary Instance   ##
REM ######################################################################

-- Create listener.ora on Auxiliary database
   SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (GLOBAL_DBNAME = DUP)
        (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)
        (SID_NAME = DUP)
      )
    )

   LISTENER =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lenovo-f5acb7d1)(PORT = 1521))
    )

-- Create tnsnames.ora on Target database
   DUP =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = lenovo-f5acb7d1)(PORT = 1521))
      )
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = dup)
        (INSTANCE_NAME = dup)
      )
    )

-- Start the listener of Auxiliary database
    lsnrctl start

REM ##########################################
REM ##STEP 5.Start the Auxiliary Instance   ##
REM ##########################################

   startup nomount pfile='$ORACLE_HOME/dbs/initdup.ora'

REM #####################################
REM ##STEP 6.Prepare LVM if you need   ##
REM #####################################

    lvcreate -n system01 -L 1024 /dev/vg01

REM ###############################################
REM ##STEP 7.Mount or Open the Target Database   ##
REM ###############################################

-- If your Target Database is on Archive Mode, ensure the Target Database is Mounted or Opened
-- If your Target Database is on Unarchive Mode, please shutdown and mount it
    shutdown immediate
    startup mount

REM ##########################################################################
REM ##STEP 8.Make Sure You Have the Necessary Backups and Archived Redo Logs##
REM ##########################################################################

-- Connect to Target Database with RMAN, make a full backup.
-- If your Target Database is on Unarchive Mode, you need not backup the archivelog
-- Attention SET LIMIT, it can break restriction of file size on some OS
   rman tareget /;
   run {
      allocate channel d1 type disk;
      set limit channel d1 kbytes 1048576;
      backup tag 'dbfull' format '/home/backup/dbfull_%d_%s_%p.bck' database include current controlfile;
      backup tag 'logfull' format '/home/backup/logfull_%d_%s_%p.bck' archivelog all delete input;
        release channel d1;}
-- Send the backupset to Auxiliary Database, and be sure that the directory must be same on both side(Target and Auxiliary)
-- Mention: you can use [ln -s] to make a link that present the path as same as the Target backup source

REM ###############################################################################
REM ##STEP 9.Allocate Auxiliary Channels if Automatic Channels Are Not Configured##
REM ###############################################################################

-- Run RMAN on the Target Server, and connect Target Database and Auxiliary Database
    rman tareget /;
    connect auxiliary sys/change_on_install@dupdb

-- There are three cases of duplicating database

-- 1. Duplicating a Database on a Remote Host with the Same Directory Structure
-- The simplest case is to duplicate the database to a different host and to use the same directory structure
-- In this case, you do not need to change the initialization parameter file or set new filenames for the duplicate datafiles
-- Ensure the following had been done
-- ☆ Specify the NOFILENAMECHECK parameter on the DUPLICATE command
-- ☆ Specify the PFILE parameter if starting the auxiliary instance with a client-side parameter file
--     The client-side parameter file must exist on the same host as the RMAN executable used to perform the duplication
   run{ 
      allocate auxiliary channel d1 type disk;
      DUPLICATE TARGET DATABASE TO dup
      PFILE=/dup/oracle/admin/dup/initDUP.ora
      NOFILENAMECHECK;}

-- 2. Duplicating a Database on a Remote Host with a Different Directory Structure 
-- ☆ Duplicating By Using Initialization Parameters
-- Your must config the *_convert parameters in Initialization Parameter File
   *.log_file_name_convert=('/rdb/oradata/ora9','/u01/oradata/ora9')
   *.db_file_name_convert=('/trsgfifs/oradata','/u01/oradata/ora9')
   run{ 
      allocate auxiliary channel d1 type disk;
      DUPLICATE TARGET DATABASE TO dup
      PFILE=/dup/oracle/admin/dup/initDUP.ora;}
      
-- ☆ Duplicating By Using Initialization Parameters and the LOGFILE Clause
-- Set the DB_FILE_NAME_CONVERT, but do not set the LOG_FILE_NAME_CONVERT parameter
-- Specify new filenames for the duplicate database logfiles, they should different from any filenames of Target Database
   run{ 
      allocate auxiliary channel d1 type disk;
      DUPLICATE TARGET DATABASE TO dup
      PFILE=/dup/oracle/admin/dup/initDUP.ora
      LOGFILE
        GROUP 1 ('/u01/oradata/ora9/REDO01.DBF') SIZE 50M,
        GROUP 2 ('/u01/oradata/ora9/REDO02.DBF') SIZE 50M,
        GROUP 3 ('/u01/oradata/ora9/REDO03.DBF') SIZE 50M;}
      
-- ☆ Duplicating By Using SET NEWNAME
-- Specify new filenames for the duplicate database logfiles, they should different from any filenames of Target Database
   run{ 
      allocate auxiliary channel d1 type disk;
      SET NEWNAME FOR DATAFILE 1 TO '/u01/oradata/ora9/system01.dbf';
      SET NEWNAME FOR DATAFILE 2 TO '/u01/oradata/ora9/tp_data01.dbf';
      SET NEWNAME FOR DATAFILE 3 TO '/u01/oradata/ora9/xxxxx_data01.dbf'; 
      DUPLICATE TARGET DATABASE TO ora9
      PFILE=/dup/oracle/admin/dup/initDUP.ora
      LOGFILE
        GROUP 1 ('/u01/oradata/ora9/REDO01.DBF') SIZE 50M,
        GROUP 2 ('/u01/oradata/ora9/REDO02.DBF') SIZE 50M,
        GROUP 3 ('/u01/oradata/ora9/REDO03.DBF') SIZE 50M;}
        
-- ☆ Duplicating By Using CONFIGURE AUXNAME

-- 3. Creating a Duplicate Database on the Local Host 
-- Follow the procedure as case two
-- Do not use the NOFILENAMECHECK option when duplicating to the same Oracle home as the primary database
-- If you do, then the DUPLICATE command may generate an error

REM ##########################################################################################
REM ##STEP11.Delete parameters(log_file_name_convert,db_file_name_convert) and create spfile##
REM ##########################################################################################

-- After Duplication, remove the parameters below from the Initialization Parameter File 
   # *.log_file_name_convert=('/rdb/oradata/ora9','/u01/oradata/ora9')
   # *.db_file_name_convert=('/trsgfifs/oradata','/u01/oradata/ora9')

-- Create spfile
   create spfile from pfile='/u01/product/9.2.0/initora9.ora';
   
REM ################################################################
REM ##STEP12.Create own temporary tablespace and undo tablespace ##
REM ################################################################

-- RMAN will create a temporary tablespace called TEMP by default, so you must make your own
    create temporary tablespace TBS_TEMP
    tempfile '/u01/oradata/temp1.dbf' size 1000M reuse autoextend off;
    alter database default temporary tablespace TBS_TEMP;
    
-- Create your own UNDO tablespace
    create undo tablespace TBS_UNDO1
    datafile '/u01/oradata/undo1.dbf' size 1000M reuse autoextend off;
    alter system set UNDO_TABLESPACE=TBS_UNDO1 scope=both;

REM ####################################
REM ##STEP13.Others:Migrate or Upgrade##
REM ####################################

-- If there is a different of version between Target Database and Auxiliary Database
-- You must do the following things(UPDATE from 9i to 10g)
   STARTUP UPDATE
   SPOOL catpatch.out
   @?/rdbms/admin/catpatch.sql
   SPOOL OFF
   SHUTDOWN IMMEDIATE 
   STARTUP
   @?/rdbms/admin/utlrp.sql   
   SHUTDOWN IMMEDIATE
   STARTUP

-- If there is a different of patch between Target Database and Auxiliary Database
-- You must do the following things(MIGRATE from 9.2.0.1 to 9.2.0.8)
   STARTUP MIGRATE
   SPOOL catpatch.out
   @?/rdbms/admin/catpatch.sql
   SPOOL OFF
   SHUTDOWN IMMEDIATE 
   STARTUP
   @?/rdbms/admin/utlrp.sql   
   SHUTDOWN IMMEDIATE
   STARTUP