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 list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Current log sequence 7 SYS@orcl>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@orcl>startup mount ORACLE instance started. Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo Buffers 16568320 bytes Database mounted. SYS@orcl>alter database archivelog; Database altered. SYS@orcl>alter database open; Database altered. SYS@orcl>archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7 SYS@orcl>
3、在辅助数据库服务器上创建相应的目录
查询目标数据库的相关目录
SYS@orcl>col name for a30
SYS@orcl>col value for a200
SYS@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/adump
background_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/trace
control_files /usr/oracle/app/oradata/orcl/control01.ctl, /usr/oracle/app/flash_recovery_area/orcl/control02.ctl
core_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/cdump
user_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/trace
SYS@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]$ ls hc_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/dbs The 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/dbs oracle@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/dbs oracle@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 sysdba SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 22 16:07:27 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo Buffers 16568320 bytes SQL>
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 start LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-DEC-2015 16:11:39 Copyright (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 - Production System parameter file is /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml Listening 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 LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 22-DEC-2015 16:11:41 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml Listening 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 oradu TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-DEC-2015 16:15:06 Copyright (c) 1997, 2009, Oracle. All rights reserved. Used parameter files: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting 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@oradu Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 23 10:50:34 2015 Copyright (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-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK
contents 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 Script
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 1795164312 bytes
Database Buffers 1476395008 bytes
Redo Buffers 16568320 bytes
Starting backup at 23-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20151223T105952 RECID=2 STAMP=899204392
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-15
Starting restore at 23-DEC-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 23-DEC-15
database mounted
contents 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 Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 23-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/usr/oracle/app/oradata/orcl/system01.dbf
output file name=/usr/oracle/app/oradata/orcl/system01.dbf tag=TAG20151223T105958
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/usr/oracle/app/oradata/orcl/sysaux01.dbf
output file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf tag=TAG20151223T105958
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/usr/oracle/app/oradata/orcl/undotbs01.dbf
output file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf tag=TAG20151223T105958
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/usr/oracle/app/oradata/orcl/users01.dbf
output file name=/usr/oracle/app/oradata/orcl/users01.dbf tag=TAG20151223T105958
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-15
sql statement: alter system archive log current
contents 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 Script
Starting backup at 23-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=9 RECID=3 STAMP=899204433
output file name=/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-15
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arc
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/users01.dbf
contents of Memory Script:
{
set until scn 1027195;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 23-DEC-15
using channel ORA_AUX_DISK_1
starting media recovery
archived 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_.arc
archived log file name=/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-DEC-15
contents 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 Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 1795164312 bytes
Database Buffers 1476395008 bytes
Redo Buffers 16568320 bytes
sql statement: alter system set db_name = ''ORCL'' 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 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 1795164312 bytes
Database Buffers 1476395008 bytes
Redo Buffers 16568320 bytes
sql 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 AL32UTF8
contents 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 Script
executing command: SET NEWNAME
renamed tempfile 1 to /usr/oracle/app/oradata/orcl/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf RECID=1 STAMP=899204444
cataloged datafile copy
datafile copy file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf RECID=2 STAMP=899204444
cataloged datafile copy
datafile copy file name=/usr/oracle/app/oradata/orcl/users01.dbf RECID=3 STAMP=899204444
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=899204444 file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=899204444 file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=899204444 file name=/usr/oracle/app/oradata/orcl/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished 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;
