001 理论基础
active database duplication ：A duplicate database that is created over a network without restoring backups of the target database. This technique is an alternative to backup-based duplication.
意即：duplicate数据库不需要通过还原目标数据库的备份，而是直接通过网络而创建。这项技术是基于备份的duplication的替代选择。
002 实验步骤
1.创建辅助实例的Oracle口令文件
2.建立到辅助实例的Oracle Net连接
3.创建辅助实例的初始化参数文件
4.在nomount模式下启动辅助实例
5.装载或打开目标数据库
6.登录rman，执行duplicate命令
003 创建辅助实例的Oracle口令文件
第一步是在辅助实例所在的主机创建辅助实例的口令文件。创建口令文件有两种方式：
1、手动创建口令文件
对于执行duplicate ... from active database有一些额外的要求。你必须使用sys用户的ID，并且密码必须和source database的密码一致。
orapwd file=$ORACLE_HOME/dbs/orapwPROD2 password=oracle entries=30
或者将source database的口令文件拷贝过来（scp）然后改名。
004 建立到辅助实例的Oracle Net连接
在duplicate过程中会开/关auxiliary实例，因此需要对auxiliary实例配置静态监听。同时需要在source database端访问auxiliary实例，因此还需要在source database端配置tnsnames.ora。
source database端的tnsnames.ora:
PROD1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = huangrui.hhu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD1)
)
)
PROD2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = huan.hhu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD2)
)
)
在duplicate端tnsnames.ora:
PROD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =huangrui.hhu )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD1)
)
)
PROD2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = huan.hhu)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD2)
)
)
在duplicate端listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD2)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = PROD2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = huan.hhu)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
005 创建辅助实例的参数文件
将source database端的参数文件拷贝过来，因为source database端只有spfile，所以在source database端生成一个initPROD1.ora文件。
SYS@PROD1> create pfile from spfile;
File created.
然后将initPROD1.ora传输到duplicate端：
scp initPROD1 @huan.hhu:$ORACLE_HOME/dbs
在duplicate端编辑initPROD1文件，首先将其改名为initPROD2.ora：
mv initPROD1.ora initPROD2.ora
开始编辑initPROD2.ora,将文件里的所有PROD1改为PROD2:
%s@PROD1@PROD2@gi
在文件尾部加上两行：
db_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/PROD2'
log_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/PROD2'
最终的参数文件样子：
PROD2.__db_cache_size=469762048
PROD2.__java_pool_size=16777216
PROD2.__large_pool_size=33554432
PROD2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD2.__pga_aggregate_target=520093696
PROD2.__sga_target=754974720
PROD2.__shared_io_pool_size=0
PROD2.__shared_pool_size=218103808
PROD2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PROD2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/PROD2/control01.ctl','/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PROD2'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD2XDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1261436928
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
db_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/PROD2'
log_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/PROD2'
还要将参数文件中提及的几个目录建立好：
mkdir -p /u01/app/oracle/admin/PROD2/adump
mkdir -p /u01/app/oracle/oradata/PROD2
mkdir -p /u01/app/oracle/fast_recovery_area/PROD2
006 在nomount模式下启动辅助实例
当然，是在duplicate端：
SQL>startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD2.ora'；
007 装载或打开目标数据库
将source database启动到mount或open状态即可。
008 登录rman，执行duplicate命令
当然，是在source database端登录。
先登录rman：rman target sys/oracle@PROD1 auxiliary sys/oracle@PROD2
然后就是第一条也是最后一条duplicate命令：duplicate target database to PROD2 from active database;
009 下面是执行完第8步之后出现的整个duplicate流程：
RMAN> duplicate target database to PROD2 from active database;
Starting Duplicate Db at 11-JAN-16
using target database control file instead of recovery catalog #使用目标数据库控制文件而不是恢复目录
allocated channel: ORA_AUX_DISK_1 #分配通道
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script: #内存脚本内容
{
sql clone "create spfile from memory"; #创建spfile
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script: #关闭然后开启克隆实例到nomount状态
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1269366784 bytes
Fixed Size 2252864 bytes
Variable Size 822087616 bytes
Database Buffers 436207616 bytes
Redo Buffers 8818688 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD1'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''PROD2'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u01/app/oracle/oradata/PROD2/control01.ctl'; #拷贝控制文件
restore clone controlfile to '/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl' from
'/u01/app/oracle/oradata/PROD2/control01.ctl';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD1'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''PROD2'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1269366784 bytes
Fixed Size 2252864 bytes
Variable Size 822087616 bytes
Database Buffers 436207616 bytes
Redo Buffers 8818688 bytes
Starting backup at 11-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD1.f tag=TAG20160111T160715 RECID=4 STAMP=900864435
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-JAN-16
Starting restore at 11-JAN-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 11-JAN-16
database mounted
contents of Memory Script: #改变数据文件路径
{
set newname for datafile 1 to
"/u01/app/oracle/oradata/PROD2/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/PROD2/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/PROD2/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/PROD2/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/PROD2/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/PROD2/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/PROD2/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/PROD2/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/PROD2/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/PROD2/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 11-JAN-16 #复制数据文件
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
output file name=/u01/app/oracle/oradata/PROD2/system01.dbf tag=TAG20160111T160722
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
output file name=/u01/app/oracle/oradata/PROD2/sysaux01.dbf tag=TAG20160111T160722
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
output file name=/u01/app/oracle/oradata/PROD2/example01.dbf tag=TAG20160111T160722
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
output file name=/u01/app/oracle/oradata/PROD2/undotbs01.dbf tag=TAG20160111T160722
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
output file name=/u01/app/oracle/oradata/PROD2/users01.dbf tag=TAG20160111T160722
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-JAN-16
sql statement: alter system archive log current
contents of Memory Script: #将source database端的归档日志复制过来
{
backup as copy reuse
archivelog like "/u01/app/oracle/fast_recovery_area/PROD1/archivelog/2016_01_11/o1_mf_1_7_c96rsdhz_.arc" auxiliary format
"/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 11-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=7 RECID=3 STAMP=900864558
output file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_09qr46hf_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 11-JAN-16
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_09qr46hf_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_09qr46hf_.arc
List of files in Recovery Area not managed by the database
==========================================================
File Name: /u01/app/oracle/fast_recovery_area/PROD2/control02.ctl
RMAN-07526: Reason: File is not an Oracle Managed File
number of files not managed by recovery area is 1, totaling 9.28MB
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=900864562 file name=/u01/app/oracle/oradata/PROD2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=900864562 file name=/u01/app/oracle/oradata/PROD2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=900864562 file name=/u01/app/oracle/oradata/PROD2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=900864562 file name=/u01/app/oracle/oradata/PROD2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=900864562 file name=/u01/app/oracle/oradata/PROD2/example01.dbf
contents of Memory Script: #恢复数据库，不完全恢复
{
set until scn 1053451;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 11-JAN-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_09qr46hf_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_09qr46hf_.arc thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 11-JAN-16
Oracle instance started
Total System Global Area 1269366784 bytes
Fixed Size 2252864 bytes
Variable Size 822087616 bytes
Database Buffers 436207616 bytes
Redo Buffers 8818688 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD2'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD2'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1269366784 bytes
Fixed Size 2252864 bytes
Variable Size 822087616 bytes
Database Buffers 436207616 bytes
Redo Buffers 8818688 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD2" RESETLOGS ARCHIVELOG #重建控制文件
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/PROD2/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/PROD2/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/oradata/PROD2/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/PROD2/system01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/PROD2/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/PROD2/sysaux01.dbf",
"/u01/app/oracle/oradata/PROD2/undotbs01.dbf",
"/u01/app/oracle/oradata/PROD2/users01.dbf",
"/u01/app/oracle/oradata/PROD2/example01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/PROD2/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PROD2/sysaux01.dbf RECID=1 STAMP=900864572
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PROD2/undotbs01.dbf RECID=2 STAMP=900864572
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PROD2/users01.dbf RECID=3 STAMP=900864572
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PROD2/example01.dbf RECID=4 STAMP=900864572
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=900864572 file name=/u01/app/oracle/oradata/PROD2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=900864572 file name=/u01/app/oracle/oradata/PROD2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=900864572 file name=/u01/app/oracle/oradata/PROD2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=900864572 file name=/u01/app/oracle/oradata/PROD2/example01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs; #打开数据库
}
executing Memory Script
database opened
Finished Duplicate Db at 11-JAN-16