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