rman 在线复制

rman active duplicate

所谓active duplicate 是指该恢复是基于数据库本身文件的在线传输，
所以该操作对网络是有要求的，不过我们的同一个机房中通常的百兆宽带，我想在数据库物理文件较大时，这种迁移方式，反而比拷贝备份文件来的更直接，更快速。

我的两个测试虚机，配置好hosts

[oracle@hdpdup admin]$ cat /etc/hosts127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.80.128 hdp.oracle.com192.168.80.137 hdpdup

在hupdup 安装oracle软件（不创建instance），这个不具表。DBA都烂熟于心了。在auxiliary database(辅助库)手动建好初始化参数文件，这里注意指定db_create_file_dest(这等于默认启用了OMF)[oracle@hdpdup dbs]$ cat initorcl.ora compatible ='11.2.0.0'db_name=dupdb_create_file_dest='/u01/app/oracle/oradata/dup/datafile'db_unique_name='dup'control_files=/u01/app/oracle/oradata/dup/datafile/control01.ctl,/u01/app/oracle/oradata/dup/datafile/control02.ctl

[oracle@hdpdup admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools.

dup库添加静听，因为我们现在没有实例，动态监听不能起来，所以我们需要手动添加static listener
静态监听(注意一下静态监听的格式吧)

SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = orcl_prd)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)      (SID_NAME = orcl)    )  )LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = hdpdup)(PORT = 1521))      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    )  )ADR_BASE_LISTENER = /u01/app/oracle

如果不配置静态监听，监听的状态是blocked，这样我们的target database是无法连接上的

Service "dup" has 1 instance(s).  Instance "dup", status BLOCKED, has 1 handler(s) for this service...The command completed successfully

确认一下监听的状态的变化哦

Service "dup" has 1 instance(s).  Instance "dup", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully

每个节点这样设置的oracle network设置(因为我们要让两个节点互通嘛)

[oracle@hdp admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.ORCL =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = hdp.oracle.com)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orcl)    )  )DUP =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = hdpdup)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = dup)    )  )[oracle@hdp admin]$ 

auxiliary database 需要创建一些目录和密码文件

mkdir -p /u01/app/oracle/oradata/dup/datafile

[oracle@hdpdup dbs]$ pwd/u01/app/oracle/product/11.2.0/dbhome_1/dbs[oracle@hdpdup dbs]$ orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdup entries=10Enter password for SYS: 

此时我们在主库上登录 目标库以及auxiliary database

[oracle@hdp admin]$ rman target sys/Oracle123@orcl auxiliary sys/Oracle123@dupRecovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 16 02:39:46 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCL (DBID=1477533441)connected to auxiliary database: DUP (not mounted)RMAN> duplicate target database to dup from active database;

报错如下：

RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version 11.2.0.0.0ORA-00202: control file: '/u01/app/oracle/oradata/dup/datafile/control01.ctl'

所以我们重新修改下

[oracle@hdpdup dbs]$ cat initdup.ora compatible ='11.2.0.4'db_name=dupdb_create_file_dest='/u01/app/oracle/oradata/dup/datafile'db_unique_name='dup'control_files=/u01/app/oracle/oradata/dup/datafile/control01.ctl,/u01/app/oracle/oradata/dup/datafile/control02.ctl[oracle@hdpdup dbs]$ 

有一个报错，好像是因为我把user offline了，在拷贝的过程中居然rman主动尝试去drop了,这也算我们get了新的技能点

删干净文件，然后重新来吧！

完整的脚本如下，我们可能需要仔细研究一下如下的信息。

[oracle@hdp admin]$ rman target sys/Oracle123@orcl auxiliary sys/Oracle123@dupRecovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 16 02:54:20 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCL (DBID=1477533441)connected to auxiliary database: DUP (not mounted)RMAN> duplicate target database to dup from active database;Starting Duplicate Db at 16-AUG-17using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=20 device type=DISKallocated channel: ORA_AUX_DISK_2channel ORA_AUX_DISK_2: SID=21 device type=DISKallocated channel: ORA_AUX_DISK_3channel ORA_AUX_DISK_3: SID=22 device type=DISKallocated channel: ORA_AUX_DISK_4channel ORA_AUX_DISK_4: SID=23 device type=DISKallocated channel: ORA_AUX_DISK_5channel ORA_AUX_DISK_5: SID=24 device type=DISKallocated channel: ORA_AUX_DISK_6channel ORA_AUX_DISK_6: SID=25 device type=DISKcontents of Memory Script:{   sql clone "create spfile from memory";}executing Memory Scriptsql statement: create spfile from memorycontents of Memory Script:{   shutdown clone immediate;   startup clone nomount;}executing Memory ScriptOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area     217157632 bytesFixed Size                     2251816 bytesVariable Size                159384536 bytesDatabase Buffers              50331648 bytesRedo Buffers                   5189632 bytescontents 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 =  ''DUP'' 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/dup/datafile/control01.ctl';   restore clone controlfile to  '/u01/app/oracle/oradata/dup/datafile/control02.ctl' from  '/u01/app/oracle/oradata/dup/datafile/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 =  ''DUP'' comment= ''Modified by RMAN duplicate'' scope=spfileOracle instance shut downOracle instance startedTotal System Global Area     217157632 bytesFixed Size                     2251816 bytesVariable Size                159384536 bytesDatabase Buffers              50331648 bytesRedo Buffers                   5189632 bytesStarting backup at 16-AUG-17allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=132 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=130 device type=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: SID=191 device type=DISKallocated channel: ORA_DISK_4channel ORA_DISK_4: SID=13 device type=DISKallocated channel: ORA_DISK_5channel ORA_DISK_5: SID=73 device type=DISKallocated channel: ORA_DISK_6channel ORA_DISK_6: SID=198 device type=DISKchannel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f tag=TAG20170816T025435 RECID=4 STAMP=952138475channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 16-AUG-17Starting restore at 16-AUG-17allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=18 device type=DISKallocated channel: ORA_AUX_DISK_2channel ORA_AUX_DISK_2: SID=20 device type=DISKallocated channel: ORA_AUX_DISK_3channel ORA_AUX_DISK_3: SID=21 device type=DISKallocated channel: ORA_AUX_DISK_4channel ORA_AUX_DISK_4: SID=22 device type=DISKallocated channel: ORA_AUX_DISK_5channel ORA_AUX_DISK_5: SID=23 device type=DISKallocated channel: ORA_AUX_DISK_6channel ORA_AUX_DISK_6: SID=24 device type=DISKchannel ORA_AUX_DISK_2: skipped, AUTOBACKUP already foundchannel ORA_AUX_DISK_3: skipped, AUTOBACKUP already foundchannel ORA_AUX_DISK_4: skipped, AUTOBACKUP already foundchannel ORA_AUX_DISK_5: skipped, AUTOBACKUP already foundchannel ORA_AUX_DISK_6: skipped, AUTOBACKUP already foundchannel ORA_AUX_DISK_1: copied control file copyFinished restore at 16-AUG-17database mountedcontents of Memory Script:{   set newname for clone datafile  1 to new;   set newname for clone datafile  2 to new;   set newname for clone datafile  3 to new;   set newname for clone datafile  4 to new;   set newname for clone datafile  5 to new;   backup as copy reuse   datafile  1 auxiliary format new   datafile  2 auxiliary format new   datafile  3 auxiliary format new   datafile  4 auxiliary format new   datafile  5 auxiliary format new   ;   sql 'alter system archive log current';}executing Memory Scriptexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 16-AUG-17using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3using channel ORA_DISK_4using channel ORA_DISK_5using channel ORA_DISK_6channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_2: starting datafile copyinput datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbfchannel ORA_DISK_3: starting datafile copyinput datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbfchannel ORA_DISK_4: starting datafile copyinput datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbfchannel ORA_DISK_5: starting datafile copyinput datafile file number=00005 name=/u02/oradata/ORCL/datafile/o1_mf_test001_ds17lv9r_.dbfoutput file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_undotbs1_knsc0uns_.dbf tag=TAG20170816T025452channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:03output file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_test001_kosc0uns_.dbf tag=TAG20170816T025452channel ORA_DISK_5: datafile copy complete, elapsed time: 00:00:03output file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_system_klsc0uns_.dbf tag=TAG20170816T025452channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:25output file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_sysaux_kmsc0uns_.dbf tag=TAG20170816T025452channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:25output file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_users_kksc0uns_.dbf tag=TAG20170816T025452channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45Finished backup at 16-AUG-17sql statement: alter system archive log currentcontents of Memory Script:{   backup as copy reuse   archivelog like  "/u02/backup/ORCL/archivelog/2017_08_16/o1_mf_1_5_ds6jx9n5_.arc" auxiliary format  "/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_951984433.dbf"   ;   catalog clone archivelog  "/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_951984433.dbf";   switch clone datafile all;}executing Memory ScriptStarting backup at 16-AUG-17using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3using channel ORA_DISK_4using channel ORA_DISK_5using channel ORA_DISK_6channel ORA_DISK_1: starting archived log copyinput archived log thread=1 sequence=5 RECID=22 STAMP=952138537output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_951984433.dbf RECID=0 STAMP=0channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03Finished backup at 16-AUG-17cataloged archived logarchived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_951984433.dbf RECID=22 STAMP=953826873datafile 1 switched to datafile copyinput datafile copy RECID=4 STAMP=953826873 file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_system_klsc0uns_.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=5 STAMP=953826873 file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_sysaux_kmsc0uns_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=6 STAMP=953826873 file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_undotbs1_knsc0uns_.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=7 STAMP=953826873 file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_users_kksc0uns_.dbfdatafile 5 switched to datafile copyinput datafile copy RECID=8 STAMP=953826873 file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_test001_kosc0uns_.dbfcontents of Memory Script:{   set until scn  1728699;   recover   clone database    delete archivelog   ;}executing Memory Scriptexecuting command: SET until clauseStarting recover at 16-AUG-17using channel ORA_AUX_DISK_1using channel ORA_AUX_DISK_2using channel ORA_AUX_DISK_3using channel ORA_AUX_DISK_4using channel ORA_AUX_DISK_5using channel ORA_AUX_DISK_6starting media recoveryarchived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_951984433.dbfarchived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_951984433.dbf thread=1 sequence=5media recovery complete, elapsed time: 00:00:00Finished recover at 16-AUG-17Oracle instance startedTotal System Global Area     217157632 bytesFixed Size                     2251816 bytesVariable Size                159384536 bytesDatabase Buffers              50331648 bytesRedo Buffers                   5189632 bytescontents of Memory Script:{   sql clone "alter system set  db_name =  ''DUP'' 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 Scriptsql statement: alter system set  db_name =  ''DUP'' 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     217157632 bytesFixed Size                     2251816 bytesVariable Size                159384536 bytesDatabase Buffers              50331648 bytesRedo Buffers                   5189632 bytessql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" 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  '/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_system_klsc0uns_.dbf' CHARACTER SET WE8MSWIN1252contents of Memory Script:{   set newname for clone tempfile  1 to new;   switch clone tempfile all;   catalog clone datafilecopy  "/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_sysaux_kmsc0uns_.dbf",  "/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_undotbs1_knsc0uns_.dbf",  "/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_users_kksc0uns_.dbf",  "/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_test001_kosc0uns_.dbf";   switch clone datafile all;}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_temp_%u_.tmp in control filecataloged datafile copydatafile copy file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_sysaux_kmsc0uns_.dbf RECID=1 STAMP=953826880cataloged datafile copydatafile copy file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_undotbs1_knsc0uns_.dbf RECID=2 STAMP=953826880cataloged datafile copydatafile copy file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_users_kksc0uns_.dbf RECID=3 STAMP=953826880cataloged datafile copydatafile copy file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_test001_kosc0uns_.dbf RECID=4 STAMP=953826880datafile 2 switched to datafile copyinput datafile copy RECID=1 STAMP=953826880 file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_sysaux_kmsc0uns_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=2 STAMP=953826880 file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_undotbs1_knsc0uns_.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=3 STAMP=953826880 file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_users_kksc0uns_.dbfdatafile 5 switched to datafile copyinput datafile copy RECID=4 STAMP=953826880 file name=/u01/app/oracle/oradata/dup/datafile/DUP/datafile/o1_mf_test001_kosc0uns_.dbfcontents of Memory Script:{   Alter clone database open resetlogs;}executing Memory Scriptdatabase openedFinished Duplicate Db at 16-AUG-17RMAN> 

此时在 auxiliary database 查看,发现数据都已经过来了。

[oracle@hdpdup onlinelog]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 4 15:56:53 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> show parameter db_nameNAME                     TYPE------------------------------------ ---------------------------------VALUE------------------------------db_name                  stringDUPSQL> conn hdp/123123Connected.SQL> select tname from tab  2  /TNAME--------------------------------------------------------------------------------T1T2T3TOAD_PLAN_TABLE

此时我们再去auxiliary database os 上面看监听的信息,发现监听的状态已经改变了

Service "dup" has 2 instance(s).  Instance "dup", status UNKNOWN, has 1 handler(s) for this service...  Instance "dup", status READY, has 1 handler(s) for this service...The command completed successfully

总结：

这和我们在装11gR2 的DATAGUARD非常类似啊！

ORACLE是Linux/UNIX/windows平台的超级软件，其可配置性太强了，感觉像是手动挡的超级跑车，又像是水果刀界的瑞士军刀(具体是这个界吗？)，哈哈，技术是学无止境的，所谓高手，大师，也不过是闻道有先后，术业有专攻而已，是不是这样呢？不知道，因为我即不是高手，也不是大师。

the end