11g的RMAN duplicate 可以通过Active database duplicate和Backup-based duplicate两种方法实现。这里的测试使用的是Active database duplicate，因为Active database duplicate 功能强大，不需要先把目标数据库进行rman备份，只要目标数据库处于归档模式下即可直接通过网络对数据库进行copy，且copy完成后自动open数据库。这对于大数据特别是T级别的数据库来说优点非常明显，复制前不需要进行备份，减少了备份和传送备份的时间，同时节省备份空间。
1、环境说明
| 目标数据库（被复制的数据库） | 辅助数据库（复制后的数据库） | |
| IP | 172.17.22.16 | 172.17.22.17 |
| SID | orcl | orcl |
| 数据库版本 | Oracle 11g 11.2.0.1.0 | 仅安装了数据库软件Oracle11g 11.2.0.1.0 |
2、确保目标数据库处于归档模式
SYS@orcl>archive log listDatabase log mode No Archive ModeAutomatic archival DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 5Current log sequence 7SYS@orcl>shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SYS@orcl>startup mountORACLE instance started.Total System Global Area 3290345472 bytesFixed Size 2217832 bytesVariable Size 1795164312 bytesDatabase Buffers 1476395008 bytesRedo Buffers 16568320 bytesDatabase mounted.SYS@orcl>alter database archivelog;Database altered.SYS@orcl>alter database open;Database altered.SYS@orcl>archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 5Next log sequence to archive 7Current log sequence 7SYS@orcl>
3、在辅助数据库服务器上创建相应的目录
查询目标数据库的相关目录
SYS@orcl>col name for a30SYS@orcl>col value for a200SYS@orcl>select name,value from v$parameter where name in ('audit_file_dest','background_dump_dest','control_files','core_dump_dest','user_dump_dest') ORDER BY name ASC;NAME VALUE------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------audit_file_dest /usr/oracle/app/admin/orcl/adumpbackground_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/tracecontrol_files /usr/oracle/app/oradata/orcl/control01.ctl, /usr/oracle/app/flash_recovery_area/orcl/control02.ctlcore_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/cdumpuser_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/traceSYS@orcl> 在辅助数据库服务器创建相同的目录
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/admin/orcl/adump[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/oradata/orcl[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/flash_recovery_area/orcl[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace
4、创建口令文件
方法一： 直接从目标数据库复制密钥文件到辅助数据库相同目录
[oracle@oracledb dbs]$ pwd/usr/oracle/app/product/11.2.0/dbhome_1/dbs[oracle@oracledb dbs]$ lshc_DBUA0.dat hc_orcl.dat init.ora lkORCL orapworcl snapcf_orcl.f spfileorcl.ora[oracle@oracledb dbs]$ scp orapworcl oracle@172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbsThe authenticity of host '172.17.22.17 (172.17.22.17)' can't be established.RSA key fingerprint is 72:28:f5:f9:9c:f8:49:23:48:6d:9d:d4:0e:0c:89:71.Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '172.17.22.17' (RSA) to the list of known hosts.oracle@172.17.22.17's password: orapworcl 100% 1536 1.5KB/s 00:00 [oracle@oracledb dbs]$
方法二： orapwd生成
orapwd FILE=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl password=wangshengzhuang entries=30;
5、创建Pfile文件
在源数据库根据spfile生产pfile
SYS@orcl>create pfile from spfile;File created.
6、复制口令文件、pfile文件到远端目标数据库
复制口令文件和pfile文件
[oracle@oracledb dbs]$ pwd/usr/oracle/app/product/11.2.0/dbhome_1/dbs[oracle@oracledb dbs]$ scp initorcl.ora 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbsoracle@172.17.22.17's password: ***************initorcl.ora 100% 970 1.0KB/s 00:00 [oracle@oracledb dbs]$ scp orapworcl 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbsoracle@172.17.22.17's password: ***************orapworcl 100% 1536 1.5KB/s 00:00 [oracle@oracledb dbs]$
7、启动辅助数据库到nomount状态
使用复制的pfile启动，或者由pfile生成spfile后，由spfile启动
create spfile from pfile
[oracle@oracledb ~]$ sqlplus sys as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 22 16:07:27 2015Copyright (c) 1982, 2009, Oracle. All rights reserved.Enter password: Connected to an idle instance.SQL> startup nomountORACLE instance started.Total System Global Area 3290345472 bytesFixed Size 2217832 bytesVariable Size 1795164312 bytesDatabase Buffers 1476395008 bytesRedo Buffers 16568320 bytesSQL>
8、辅助数据库配置监听并启动
listener.ora
# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME =/usr/oracle/app/product/11.2.0/dbhome_1) (SID_NAME = orcl) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) ) )ADR_BASE_LISTENER = /usr/oracle/app~
启动监听
[oracle@oracledb admin]$ lsnrctl startLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-DEC-2015 16:11:39Copyright (c) 1991, 2009, Oracle. All rights reserved.Starting /usr/oracle/app/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionSystem parameter file is /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.oraLog messages written to /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date 22-DEC-2015 16:11:41Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.oraListener Log File /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))Services Summary...Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully[oracle@oracledb admin]$
9、目标数据库配置网络服务名
tnsnames.ora
oradu = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
测试
[oracle@oracledb admin]$ tnsping oraduTNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-DEC-2015 16:15:06Copyright (c) 1997, 2009, Oracle. All rights reserved.Used parameter files:/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))OK (0 msec)[oracle@oracledb admin]$
10、使用RMAN duplicate命令复制
连接目标数据库和辅助数据库
[oracle@oracledb admin]$ rman target sys/wangshengzhuang@orcl auxiliary sys/wangshengzhuang@oraduRecovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 23 10:50:34 2015Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1426832466)connected to auxiliary database: ORCL (not mounted)
开始复制
RMAN> duplicate target database to orcl from active database nofilenamecheck;Starting Duplicate Db at 23-DEC-15using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=1 device type=DISKcontents of Memory Script:{ sql clone "alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount backup as copy current controlfile auxiliary format '/usr/oracle/app/oradata/orcl/control01.ctl'; restore clone controlfile to '/usr/oracle/app/flash_recovery_area/orcl/control02.ctl' from '/usr/oracle/app/oradata/orcl/control01.ctl'; alter clone database mount;}executing Memory Scriptsql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfilesql statement: alter system set db_unique_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfileOracle instance shut downOracle instance startedTotal System Global Area 3290345472 bytesFixed Size 2217832 bytesVariable Size 1795164312 bytesDatabase Buffers 1476395008 bytesRedo Buffers 16568320 bytesStarting backup at 23-DEC-15allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=38 device type=DISKchannel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20151223T105952 RECID=2 STAMP=899204392channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 23-DEC-15Starting restore at 23-DEC-15allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=18 device type=DISKchannel ORA_AUX_DISK_1: copied control file copyFinished restore at 23-DEC-15database mountedcontents of Memory Script:{ set newname for datafile 1 to "/usr/oracle/app/oradata/orcl/system01.dbf"; set newname for datafile 2 to "/usr/oracle/app/oradata/orcl/sysaux01.dbf"; set newname for datafile 3 to "/usr/oracle/app/oradata/orcl/undotbs01.dbf"; set newname for datafile 4 to "/usr/oracle/app/oradata/orcl/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/usr/oracle/app/oradata/orcl/system01.dbf" datafile 2 auxiliary format "/usr/oracle/app/oradata/orcl/sysaux01.dbf" datafile 3 auxiliary format "/usr/oracle/app/oradata/orcl/undotbs01.dbf" datafile 4 auxiliary format "/usr/oracle/app/oradata/orcl/users01.dbf" ; sql 'alter system archive log current';}executing Memory Scriptexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 23-DEC-15using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=/usr/oracle/app/oradata/orcl/system01.dbfoutput file name=/usr/oracle/app/oradata/orcl/system01.dbf tag=TAG20151223T105958channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=/usr/oracle/app/oradata/orcl/sysaux01.dbfoutput file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf tag=TAG20151223T105958channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=/usr/oracle/app/oradata/orcl/undotbs01.dbfoutput file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf tag=TAG20151223T105958channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=/usr/oracle/app/oradata/orcl/users01.dbfoutput file name=/usr/oracle/app/oradata/orcl/users01.dbf tag=TAG20151223T105958channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 23-DEC-15sql statement: alter system archive log currentcontents of Memory Script:{ backup as copy reuse archivelog like "/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_c7n3lkgq_.arc" auxiliary format "/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_%u_.arc" ; catalog clone recovery area; switch clone datafile all;}executing Memory ScriptStarting backup at 23-DEC-15using channel ORA_DISK_1channel ORA_DISK_1: starting archived log copyinput archived log thread=1 sequence=9 RECID=3 STAMP=899204433output file name=/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arc RECID=0 STAMP=0channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01Finished backup at 23-DEC-15searching for all files in the recovery areaList of Files Unknown to the Database=====================================File Name: /usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arccataloging files...cataloging doneList of Cataloged Files=======================File Name: /usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arcdatafile 1 switched to datafile copyinput datafile copy RECID=2 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/system01.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=3 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=4 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=5 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/users01.dbfcontents of Memory Script:{ set until scn 1027195; recover clone database delete archivelog ;}executing Memory Scriptexecuting command: SET until clauseStarting recover at 23-DEC-15using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 9 is already on disk as file /usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arcarchived log file name=/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arc thread=1 sequence=9media recovery complete, elapsed time: 00:00:00Finished recover at 23-DEC-15contents of Memory Script:{ shutdown clone immediate; startup clone nomount; sql clone "alter system set db_name = ''ORCL'' 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 Scriptdatabase dismountedOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 3290345472 bytesFixed Size 2217832 bytesVariable Size 1795164312 bytesDatabase Buffers 1476395008 bytesRedo Buffers 16568320 bytessql statement: alter system set db_name = ''ORCL'' comment= ''Reset to original value by RMAN'' scope=spfilesql statement: alter system reset db_unique_name scope=spfileOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 3290345472 bytesFixed Size 2217832 bytesVariable Size 1795164312 bytesDatabase Buffers 1476395008 bytesRedo Buffers 16568320 bytessql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 SIZE 50 M , GROUP 2 SIZE 50 M , GROUP 3 SIZE 50 M DATAFILE '/usr/oracle/app/oradata/orcl/system01.dbf' CHARACTER SET AL32UTF8contents of Memory Script:{ set newname for tempfile 1 to "/usr/oracle/app/oradata/orcl/temp01.dbf"; switch clone tempfile all; catalog clone datafilecopy "/usr/oracle/app/oradata/orcl/sysaux01.dbf", "/usr/oracle/app/oradata/orcl/undotbs01.dbf", "/usr/oracle/app/oradata/orcl/users01.dbf"; switch clone datafile all;}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /usr/oracle/app/oradata/orcl/temp01.dbf in control filecataloged datafile copydatafile copy file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf RECID=1 STAMP=899204444cataloged datafile copydatafile copy file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf RECID=2 STAMP=899204444cataloged datafile copydatafile copy file name=/usr/oracle/app/oradata/orcl/users01.dbf RECID=3 STAMP=899204444datafile 2 switched to datafile copyinput datafile copy RECID=1 STAMP=899204444 file name=/usr/oracle/app/oradata/orcl/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=2 STAMP=899204444 file name=/usr/oracle/app/oradata/orcl/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=3 STAMP=899204444 file name=/usr/oracle/app/oradata/orcl/users01.dbfcontents of Memory Script:{ Alter clone database open resetlogs;}executing Memory Scriptdatabase openedFinished Duplicate Db at 23-DEC-15 11、检查结果
SQL> select select name from v$datafile;NAME--------------------------------------------------------------------------------/usr/oracle/app/oradata/orcl/system01.dbf/usr/oracle/app/oradata/orcl/sysaux01.dbf/usr/oracle/app/oradata/orcl/undotbs01.dbf/usr/oracle/app/oradata/orcl/users01.dbf
有个问题，logfile的文件名变化了，后期自己rename
select * from v$logfile order by group# asc;