使用RMAN Active duplicate创建异地auxiliary Database
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;
参考：
