基于RMAN从活动数据库异机克隆(rman duplicate from active DB)

Oracle 11g RMAN能够实现基于活动数据库进行异机克隆，从而省去需要先备份再ftp到辅助服务器的过程。这一切可以全部交给Oracle来搞定。在克隆期间，Oracle会读取Target DB的所有数据结构，无须人为的干预(不同的磁盘系统，文件路径与文件名要指定转换方式)。尽管如此，对于生产环境数据库在整个克隆期间性能会下降，其次在Target Server与Auxiliary Server中会产生较大的网络流量。如果这些可以接受的话，从活动数据库进行异机克隆是一个不错的选择。本文首先描述了克隆时不同的连接与克隆方式样本，最后演示了基于活动数据库的克隆。

1、RMAN不同克隆类型的连接方式
     关于RMAN不同克隆类型，请参考: 基于 RMAN 的同机数据库克隆基于RMAN的异机数据库克隆(rman duplicate)
     $ export ORACLE_SID=sybo3

     # 从备份集克隆，不连接到Target DB以及catalog，或者没有使用catalog
     $ rman AUXILIARY /

     # 从备份集克隆，无catalog，元数据来源于Target DB控制文件
     $ rman TARGET sys/pwd@TAR AUXILIARY sys/pwd@AUX

     # 从备份集克隆连接到catalog，但不连接到target DB，元数据库来自RMAN catalog
     $ rman CATALOG rman/pwd@catalogdb AUXILIARY sys/pwd@AUX

     # 同时连接到target DB以及catalog 元数据来源于Target DB控制文件或catalog，该方式支持从备份集克隆，从活动数据库克隆
     $ rman TARGET sys/pwd@TAR CATALOG rman/pwd@catalogdb AUXILIARY sys/pwd@AUX

2、常用的数据库克隆场景
    # Auxiliary DB使用与Target DB相同的磁盘系统，文件位置及文件名，下同。需要转换的情形请参考: RMAN 数据库克隆文件位置转换方法
     DUPLICATE TARGET DATABASE TO sybo3
       SPFILE
      NOFILENAMECHECK;

     # 将数据库克隆到Target DB一周以前的状态，注，备份集或归档日志应存在
     DUPLICATE TARGET DATABASE TO sybo3
       UNTIL TIME 'SYSDATE-7'
       SPFILE
      NOFILENAMECHECK;

     # 备份集保存在不同于Target DB保存的位置，此时需要在RMAN中单独指定其位置
     DUPLICATE DATABASE TO sybo3
       SPFILE
      BACKUP LOCATION '/u01/bak/fast_recovery_area/sybo3'
       NOFILENAMECHECK;

3、基于活动数据库克隆图示

4、演示机遇活动数据库异机克隆

--环境:    
--目标数据库: 192.168.7.25/sybo3      /u01/database/sybo3   主机名:linux3       
--辅助数据库: 192.168.7.26/sybo3      /u01/database/sybo3   主机名:linux4   目标数据库与辅助数据库使用相同的文件位置    
--说明：        
--本次演示使用为使用任何备份文件，连接到target DB。    
--对于下面描述过程中，如创建参数文件，密码文件，监听等等有不甚了解的，可参考下面链接中有关文章的描述。       
--http://blog.csdn.net/robinson_0612/article/category/827734  (体系结构相关)        
--http://blog.csdn.net/robinson_0612/article/category/828434  (网络配置相关)      

[oracle@linux3 database]$ cat /etc/issue      
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)      
Kernel \r on an \m      
      
SQL> select * from v$version where rownum<2;      
      
BANNER      
--------------------------------------------------------------------------------        
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  

--Target DB的配置,位于主机linux3
SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
sybo3            linux3.orasrv.com

--Target DB处于归档模式
SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
SYBO3     READ WRITE           ARCHIVELOG

--Target DB上用于验证克隆成功的表
SQL> select * from t;

NAME       ACTION
---------- --------------------
Robinson   Transfer DB
Jackson    Transfer DB by rman

-- Author : Robinson Cheng
-- Blog   : http://blog.csdn.net/robinson_0612

[oracle@linux3 ~]$ export ORACLE_SID=sybo3
[oracle@linux3 ~]$ rman target /
RMAN> list backupset;         --->目标数据库无任何备份集

specification does not match any backup in the repository

RMAN> list copy;

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository

RMAN> list backup of archivelog all;

specification does not match any backup in the repository


a、在Auxiliary server创建相应的dump文件夹   
[oracle@linux4 database]$ more sybo3.sh    
#!/bin/sh   
mkdir -p /u01/database   
mkdir -p /u01/database/sybo3/adump   
mkdir -p /u01/database/sybo3/controlf   
mkdir -p /u01/database/sybo3/fra   
mkdir -p /u01/database/sybo3/oradata   
mkdir -p /u01/database/sybo3/redo   
mkdir -p /u01/database/sybo3/dpdump   
mkdir -p /u01/database/sybo3/pfile   
[oracle@linux4 database]$ ./sybo3.sh   


b、配置辅助实例参数文件   
--由于auxiliary DB与target DB不存在文件位置转换，因此无需作任何修改，直接将其ftp到Auxiliary server
--如果没有pfile文件，可从target db生成。 create pfile from spfile;
[oracle@linux3 ~]$ scp $ORACLE_HOME/dbs/initsybo3.ora 192.168.7.26:$ORACLE_HOME/dbs   


c、生成辅助实例密码文件      
--如果使用orapwd命令建立密码文件应保证与target DB密码相同
--或者直接使用Target DB的密码文件
[oracle@linux3 ~]$ scp $ORACLE_HOME/dbs/orapwsybo3 192.168.7.26:$ORACLE_HOME/dbs


d、在Auxiliary Server与Target Server配置监听(方式多样，不详述)
--Auxiliary Server
[oracle@linux4 admin]$ more listener.ora
SID_LIST_LISTENER_SYBO3 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sybo3.orasrv.com)
      (ORACLE_HOME = /u01/oracle/db_1)
      (SID_NAME = sybo3)
    )
  )

LISTENER_SYBO3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.26)(PORT = 1531))
  )

ADR_BASE_LISTENER_SYBO3 = /u01/oracle
[oracle@linux4 admin]$ more tnsnames.ora 
TAR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.25)(PORT = 1531))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SYBO3.ORASRV.COM)
    )
  )

AUX =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.26)(PORT = 1531))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SYBO3.ORASRV.COM)
    )
  )

--Target Server
[oracle@linux3 ~]$ more /u01/oracle/db_1/network/admin/listener.ora 
# listener.ora Network Configuration File: /u01/oracle/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_SYBO3 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sybo3.orasrv.com)
      (ORACLE_HOME = /u01/oracle/db_1)
      (SID_NAME = sybo3)
    )
  )

LISTENER_SYBO3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linux3.orasrv.com)(PORT = 1531))
  )

ADR_BASE_LISTENER_SYBO3 = /u01/oracle

[oracle@linux3 ~]$ more /u01/oracle/db_1/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/oracle/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
# 注，在Target Server与Auxiliary Server的tnames.ora文件中，使用了相同的entry，即同时存在TAR与AUX
TAR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.25)(PORT = 1531))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SYBO3.ORASRV.COM)
    )
  )

AUX =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.26)(PORT = 1531))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SYBO3.ORASRV.COM)
    )
  )

--使用下面的命令确保能够Ping通
[oracle@linux3 admin]$ tnsping TAR  
[oracle@linux3 admin]$ tnsping AUX    
[oracle@linux4 admin]$ tnsping TAR  
[oracle@linux4 admin]$ tnsping AUX


e、实施数据库克隆
[oracle@linux4 ~]$ export ORACLE_SID=sybo3
[oracle@linux4 ~]$ sqlplus / as sysdba
SQL> startup nomount;
SQL> exit
[oracle@linux4 ~]$ rman target sys/oracle@TAR auxiliary sys/oracle@AUX

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 2 11:44:09 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SYBO3 (DBID=2347733014)
connected to auxiliary database: SYBO3 (not mounted)

RMAN> duplicate database to sybo3 from active database spfile nofilenamecheck; --->发布duplicate命令

Starting Duplicate Db at 02-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK

contents of Memory Script:
{
   backup as copy reuse                                 --->该命令块备份服务器参数文件并修改spfile参数
   targetfile  '/u01/oracle/db_1/dbs/spfilesybo3.ora' auxiliary format 
 '/u01/oracle/db_1/dbs/spfilesybo3.ora'   ;
   sql clone "alter system set spfile= ''/u01/oracle/db_1/dbs/spfilesybo3.ora''";
}
executing Memory Script

Starting backup at 02-AUG-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=145 device type=DISK
Finished backup at 02-AUG-13

sql statement: alter system set spfile= ''/u01/oracle/db_1/dbs/spfilesybo3.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =    --->该命令块主要是修改db_name，重启auxliary DB 
 ''SYBO3'' comment=
 ''duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''SYBO3'' comment= ''duplicate'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     376635392 bytes

Fixed Size                     1336652 bytes
Variable Size                285215412 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6197248 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''SYBO3'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''SYBO3'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount           --->该命令块备份控制文件并恢复控制文件到Auxiliary DB
   backup as copy current controlfile auxiliary format  '/u01/database/sybo3/controlf/control01.ctl';
   restore clone controlfile to  '/u01/database/sybo3/controlf/control02.ctl' from 
 '/u01/database/sybo3/controlf/control01.ctl';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''SYBO3'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''SYBO3'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     376635392 bytes

Fixed Size                     1336652 bytes
Variable Size                285215412 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6197248 bytes

Starting backup at 02-AUG-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy   
copying current control file
output file name=/u01/oracle/db_1/dbs/snapcf_sybo3.f tag=TAG20130802T114449 RECID=5 STAMP=822397490
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 02-AUG-13

Starting restore at 02-AUG-13
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 02-AUG-13

database mounted
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT    --->这个地方提示隐式使用DB_FILE_NAME_CONVERT参数

contents of Memory Script:
{                                      --->该命令块用于set newname以及热备数据文件，使用copy方式
   set newname for datafile  1 to 
 "/u01/database/sybo3/oradata/system01.dbf";
   set newname for datafile  2 to 
 "/u01/database/sybo3/oradata/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/database/sybo3/oradata/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/database/sybo3/oradata/users01.dbf";
   set newname for datafile  5 to 
 "/u01/database/sybo3/oradata/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/database/sybo3/oradata/system01.dbf"   datafile 
 2 auxiliary format 
 "/u01/database/sybo3/oradata/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/u01/database/sybo3/oradata/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/u01/database/sybo3/oradata/users01.dbf"   datafile 
 5 auxiliary format 
 "/u01/database/sybo3/oradata/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 02-AUG-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/database/sybo3/oradata/system01.dbf
output file name=/u01/database/sybo3/oradata/system01.dbf tag=TAG20130802T114457
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/database/sybo3/oradata/sysaux01.dbf
output file name=/u01/database/sybo3/oradata/sysaux01.dbf tag=TAG20130802T114457
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/database/sybo3/oradata/example01.dbf
output file name=/u01/database/sybo3/oradata/example01.dbf tag=TAG20130802T114457
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/database/sybo3/oradata/undotbs01.dbf
output file name=/u01/database/sybo3/oradata/undotbs01.dbf tag=TAG20130802T114457
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/database/sybo3/oradata/users01.dbf
output file name=/u01/database/sybo3/oradata/users01.dbf tag=TAG20130802T114457
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 02-AUG-13

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse           --->该命令块备份归档日志
   archivelog like  "/u01/database/sybo3/fra/SYBO3/archivelog/2013_08_02/o1_mf_1_43_8zpbwsry_.arc" auxiliary format 
 "/u01/database/sybo3/fra/SYBO3/archivelog/2013_08_02/o1_mf_1_43_%u_.arc"   ;
   catalog clone recovery area;   --->而且克隆整个recovery area
   switch clone datafile all;
}
executing Memory Script

Starting backup at 02-AUG-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=43 RECID=48 STAMP=822397594
output file name=/u01/database/sybo3/fra/SYBO3/archivelog/2013_08_02/o1_mf_1_43_1aog9ikr_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 02-AUG-13

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u01/database/sybo3/fra/SYBO3/archivelog/2013_08_02/o1_mf_1_43_1aog9ikr_.arc
cataloging files...
cataloging done

List of Cataloged Files   --->这个地方列出了已经传送到Auxiliary Server的数据文件，以及归档日志
=======================
File Name: /u01/database/sybo3/fra/SYBO3/archivelog/2013_08_02/o1_mf_1_43_1aog9ikr_.arc

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=822397596 file name=/u01/database/sybo3/oradata/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=822397596 file name=/u01/database/sybo3/oradata/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=822397596 file name=/u01/database/sybo3/oradata/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=822397596 file name=/u01/database/sybo3/oradata/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=822397596 file name=/u01/database/sybo3/oradata/example01.dbf

contents of Memory Script:
{
   set until scn  1234019;
   recover                  --->这个命令块开始recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 02-AUG-13
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 43 is already on disk as file /u01/database/sybo3/fra/SYBO3/archivelog/
    2013_08_02/o1_mf_1_43_1aog9ikr_.arc
archived log file name=/u01/database/sybo3/fra/SYBO3/archivelog/2013_08_02/o1_mf_1_43_1aog9ikr_.arc thread=1 sequence=43
media recovery complete, elapsed time: 00:00:00
Finished recover at 02-AUG-13

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;                    --->该命令块修改参数以及重建控制文件
   sql clone "alter system set  db_name = 
 ''SYBO3'' 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     376635392 bytes

Fixed Size                     1336652 bytes
Variable Size                285215412 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6197248 bytes

sql statement: alter system set  db_name =  ''SYBO3'' 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     376635392 bytes

Fixed Size                     1336652 bytes
Variable Size                285215412 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6197248 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SYBO3" 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/database/sybo3/oradata/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to              --->将数据文件，临时文件更新到控制文件
 "/u01/database/sybo3/oradata/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/database/sybo3/oradata/sysaux01.dbf", 
 "/u01/database/sybo3/oradata/undotbs01.dbf", 
 "/u01/database/sybo3/oradata/users01.dbf", 
 "/u01/database/sybo3/oradata/example01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/database/sybo3/oradata/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/database/sybo3/oradata/sysaux01.dbf RECID=1 STAMP=822397612
cataloged datafile copy
datafile copy file name=/u01/database/sybo3/oradata/undotbs01.dbf RECID=2 STAMP=822397612
cataloged datafile copy
datafile copy file name=/u01/database/sybo3/oradata/users01.dbf RECID=3 STAMP=822397612
cataloged datafile copy
datafile copy file name=/u01/database/sybo3/oradata/example01.dbf RECID=4 STAMP=822397612

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=822397612 file name=/u01/database/sybo3/oradata/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=822397612 file name=/u01/database/sybo3/oradata/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=822397612 file name=/u01/database/sybo3/oradata/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=822397612 file name=/u01/database/sybo3/oradata/example01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;   --->open 数据库
}
executing Memory Script

database opened
Finished Duplicate Db at 02-AUG-13


f、验证结果
[oracle@linux4 ~]$ sqlplus / as sysdba

SQL> select * from t;

NAME       ACTION
---------- --------------------
Robinson   Transfer DB
Jackson    Transfer DB by rman

SQL> set linesize 190
SQL> select host_name,instance_name from v$instance;

HOST_NAME                            INSTANCE_NAME
------------------------------------ ----------------
linux4.orasrv.com                    sybo3

5、后记
a、无论使用何种方式进行数据库克隆，都是使用了基于时间点的不完全恢复，都需要使用open resetlog方式打开数据库。
b、从活动数据库进行克隆时，需要保证Target DB处于归档模式。
c、从活动数据库进行克隆应当保证Target Server与Auxiliary Server 的tnsnames文件中都存在到Target DB与Auxiliary DB的连接字符串。
d、可以在Target Server与Auxiliary Server任意一端发布duplicate database to newdb from active database进行克隆。
e、从活动数据库克隆应保证Traget DB与Auxiliary DB具有相同的密码(位于密码文件)或者直接从Target Server复制密码文件到Auxiliary Server。