Oracle Study之--Oracle 11gR2通过RMAN克隆数据库

àì夳堔傛蜴生んèń 2022-07-27 16:33 281阅读 0赞

**Oracle Study之--Oracle 11gR2通过RMAN克隆数据库**

### Purpose of Database Duplication ###

A duplicate database is useful for a variety of purposes, most of which involve testing. You can perform the following tasks in a duplicate database:

*  Test backup and recovery procedures
 *  Test an upgrade to a new release of Oracle Database
 *  Test the effect of applications on database performance
 *  Create a standby database
 *  Generate reports

#### Techniques for Duplicating a Database ####

RMAN supports two basic types of duplication: active database duplication and backup-based duplication. RMAN can perform backup-based duplication with or without either of the following connections:

*  Target
 *  Recovery catalog

A connection to both is required for active database duplication.

Figure 24-1 shows the decision tree for the two duplication techniques.

![Center][]

##### Active Database Duplication #####

In active database duplication, RMAN connects as `TARGET` to the source database instance and as `AUXILIARY` to the auxiliary instance. RMAN copies the live source database over the network to the auxiliary instance, thereby creating the duplicate database. No backups of the source database are required. Figure 24-2 illustrates active database duplication.

![Center 1][]

**     Oracle 11g的RMAN duplicate 可以通过Active database duplicate和Backup-based duplicate两种方法实现。本案例使用的是Active database duplicate，对于Active database duplicate来说，在克隆数据库时不用对Source备份，这对于大数据特别是T级别的数据库来说优点非常明显，复制前不需要进行备份，减少了备份和传送备份的时间，同时节省备份空间。**

**    克隆数据库一般是在不同的主机上来完成，本案例是测试环境，所以在一台主机上完成。**

**1、Source 库信息**

[oracle@rh64 ~]$sqlplus '/as sysdba'
    SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 24 16:30:48 2016
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    16:30:48 SYS@ prod>select status from v$instance;
    STATUS
    ------------
    OPEN
    
    16:31:12 SYS@ prod>select name from v$datafile;
    NAME
    ------------------------------------------------------------------------------------------------------------------------
    /u01/app/oracle/oradata/prod/system01.dbf
    /u01/app/oracle/oradata/prod/sysaux01.dbf
    /u01/app/oracle/oradata/prod/undotbs01.dbf
    /u01/app/oracle/oradata/prod/users01.dbf
    /u01/app/oracle/oradata/prod/example01.dbf
    /u01/app/oracle/oradata/prod/test1.dbf
    6 rows selected.
    
    16:37:41 SYS@ prod>select member from v$logfile;
    MEMBER
    ------------------------------------------------------------------------------------------------------------------------
    /u01/app/oracle/oradata/prod/redo03.log
    /u01/app/oracle/oradata/prod/redo02.log
    /u01/app/oracle/oradata/prod/redo01.log
    
    16:38:43 SYS@ prod>show parameter name
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_name_convert                 string
    db_name                              string      prod
    db_unique_name                       string      prod
    global_names                         boolean     FALSE
    instance_name                        string      prod
    lock_name_space                      string
    log_file_name_convert                string
    processor_group_name                 string
    service_names                        string      prod

**2、构建测试库：  
  
1）测试库目录环境**  
\[root@rh64 dsk3\]\# cd test1/  
\[root@rh64 test1\]\# ls  
oradata  logs admin  
  
**2）生成测试库pfile**  
**\[oracle@rh64 dbs\]$ cat inittest1.ora **

*.audit_file_dest='/dsk3/test1/admin/adump'
    *.audit_trail='db'
    *.compatible='11.2.0.0.0'
    *.control_files='/dsk3/test1/oradata/control01.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='test1'
    *.diagnostic_dest='/u01/app/oracle'
    *.memory_target=319430400
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.undo_tablespace='UNDOTBS1'
    *.db_file_name_convert=('/u01/app/oracle/oradata/prod/','/dsk3/test1/oradata/')        ；如果源库和目标库在同一台主机，需要配置文件存储路径转换参数
    *.log_file_name_convert=('/u01/app/oracle/oradata/prod/','/dsk3/test1/logs/')

**3）生成口令文件**  
\[oracle@rh64 dbs\]$ cp orapwprod orapwtest1  
  
**4）启动Auxiliary Instance到no mount**

16:52:01 SYS@ test1>startup nomount;
    ORACLE instance started.
    Total System Global Area  521936896 bytes
    Fixed Size                  2229944 bytes
    Variable Size             314575176 bytes
    Database Buffers          201326592 bytes
    Redo Buffers                3805184 bytes
    
    16:52:13 SYS@ test1>show parameter name
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_name_convert                 string      /u01/app/oracle/oradata/prod/,
                                                                         /dsk3/test1/oradata/
    db_name                                 string      test1
    db_unique_name                    string      test1
    global_names                         boolean     FALSE
    instance_name                        string      test1
    lock_name_space                   string
    log_file_name_convert            string        /u01/app/oracle/oradata/prod/,
                                                                      /dsk3/test1/logs/
    processor_group_name           string
    service_names                        string      test1

**三、配置网络Listener 和 tnsname**

**由于Auxiliary Instance只能启动到no mount状态，所以需要在listener里配置Auxiliary Instance为静态注册，以便RMAN进行连接。**

**1）配置监听器**  
**\[oracle@rh64 admin\]$ cat listener.ora **

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = rh64)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
    
    ADR_BASE_LISTENER = /u01/app/oracle

**\# 静态注册Auxiliary Instance：test1  
SID\_LIST\_LISTENER =**  
**  (SID\_LIST =  
    (SID\_DESC =  
      (GLOBAL\_DBNAME = test1)  
      (ORACLE\_HOME = /u01/app/oracle/product/11.2.0/db\_1)  
      (SID\_NAME = test1)  
    )  
  )**

**2）配置tnsnames  
\[oracle@rh64 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.
    
    PROD =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = rh64)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = prod)
        )
      )
    
    TEST1 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = rh64)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = test1)
        )
      )

**3）查看listener信息**

[oracle@rh64 admin]$ lsnrctl status
    LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-MAR-2016 17:44:04
    Copyright (c) 1991, 2011, Oracle.  All rights reserved.
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rh64)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
    Start Date                24-MAR-2016 17:43:17
    Uptime                    0 days 0 hr. 0 min. 46 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/rh64/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh64.cuug.cn)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    Services Summary...
    Service "prod" has 1 instance(s).
      Instance "prod", status READY, has 1 handler(s) for this service...
    Service "prodXDB" has 1 instance(s).
      Instance "prod", status READY, has 1 handler(s) for this service...
    Service "test1" has 1 instance(s).
      Instance "test1", status UNKNOWN, has 1 handler(s) for this service...           ；；Auxiliary Instance静态注册后，状态为“UNKNOWN"
    The command completed successfully

**四、配置RMAN克隆**  
**\[oracle@rh64 admin\]$ rman target sys/oracle@prod auxiliary sys/oracle@test1**  
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 24 17:07:38 2016  
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  
connected to target database: PROD (DBID=305104131)  
connected to auxiliary database: TEST1 (not mounted)  
  
**RMAN> duplicate database to test1 from active database nofilenamecheck;**

Starting Duplicate Db at 24-MAR-16
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=20 device type=DISK
    
    contents of Memory Script:
    {
       sql clone "create spfile from memory";
    }
    executing Memory Script
    sql statement: create spfile from memory
    contents of Memory Script:
    {
       shutdown clone immediate;
       startup clone nomount;
    }
    executing Memory Script
    Oracle instance shut down
    connected to auxiliary database (not started)
    Oracle instance started
    Total System Global Area     321507328 bytes
    Fixed Size                     2228024 bytes
    Variable Size                230686920 bytes
    Database Buffers              83886080 bytes
    Redo Buffers                   4706304 bytes
    contents of Memory Script:
    {
       sql clone "alter system set  db_name = 
     ''PROD'' comment=
     ''Modified by RMAN duplicate'' scope=spfile";
       sql clone "alter system set  db_unique_name = 
     ''TEST1'' comment=
     ''Modified by RMAN duplicate'' scope=spfile";
       shutdown clone immediate;
       startup clone force nomount
       backup as copy current controlfile auxiliary format  '/dsk3/test1/oradata/control01.ctl';
       alter clone database mount;
    }
    executing Memory Script
    sql statement: alter system set  db_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
    sql statement: alter system set  db_unique_name =  ''TEST1'' comment= ''Modified by RMAN duplicate'' scope=spfile
    Oracle instance shut down
    Oracle instance started
    Total System Global Area     321507328 bytes
    Fixed Size                     2228024 bytes
    Variable Size                230686920 bytes
    Database Buffers              83886080 bytes
    Redo Buffers                   4706304 bytes
    Starting backup at 24-MAR-16
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=34 device type=DISK
    channel ORA_DISK_1: starting datafile copy
    copying current control file
    output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prod.f tag=TAG20160324T175437 RECID=3 STAMP=907350882
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
    Finished backup at 24-MAR-16
    database mounted
    contents of Memory Script:
    {
       set newname for datafile  1 to 
     "/dsk3/test1/data/system01.dbf";
       set newname for datafile  2 to 
     "/dsk3/test1/data/sysaux01.dbf";
       set newname for datafile  3 to 
     "/dsk3/test1/data/undotbs01.dbf";
       set newname for datafile  4 to 
     "/dsk3/test1/data/users01.dbf";
       set newname for datafile  5 to 
     "/dsk3/test1/data/example01.dbf";
       set newname for datafile  6 to 
     "/dsk3/test1/data/test1.dbf";
       backup as copy reuse
       datafile  1 auxiliary format 
     "/dsk3/test1/data/system01.dbf"   datafile 
     2 auxiliary format 
     "/dsk3/test1/data/sysaux01.dbf"   datafile 
     3 auxiliary format 
     "/dsk3/test1/data/undotbs01.dbf"   datafile 
     4 auxiliary format 
     "/dsk3/test1/data/users01.dbf"   datafile 
     5 auxiliary format 
     "/dsk3/test1/data/example01.dbf"   datafile 
     6 auxiliary format 
     "/dsk3/test1/data/test1.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
    executing command: SET NEWNAME
    Starting backup at 24-MAR-16
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf
    output file name=/dsk3/test1/oradata/system01.dbf tag=TAG20160324T180122
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf
    output file name=/dsk3/test1/oradata/sysaux01.dbf tag=TAG20160324T180122
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00005 name=/u01/app/oracle/oradata/prod/example01.dbf
    output file name=/dsk3/test1/oradata/example01.dbf tag=TAG20160324T180122
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf
    output file name=/dsk3/test1/oradata/undotbs01.dbf tag=TAG20160324T180122
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00006 name=/u01/app/oracle/oradata/prod/test1.dbf
    output file name=/dsk3/test1/oradata/test1.dbf tag=TAG20160324T180122
    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/app/oracle/oradata/prod/users01.dbf
    output file name=/dsk3/test1/oradata/users01.dbf tag=TAG20160324T180122
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
    Finished backup at 24-MAR-16
    sql statement: alter system archive log current
    contents of Memory Script:
    {
       backup as copy reuse
       archivelog like  "/dsk4/arch_prod/arch_1_32_893265808.log" auxiliary format 
     "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf"   ;
       catalog clone archivelog  "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf";
       switch clone datafile all;
    }
    executing Memory Script
    Starting backup at 24-MAR-16
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=32 RECID=26 STAMP=907351568
    output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
    Finished backup at 24-MAR-16
    cataloged archived log
    archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf RECID=26 STAMP=907351572
    datafile 1 switched to datafile copy
    input datafile copy RECID=4 STAMP=907351573 file name=/dsk3/test1/oradata/system01.dbf
    datafile 2 switched to datafile copy
    input datafile copy RECID=5 STAMP=907351573 file name=/dsk3/test1/oradata/sysaux01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=6 STAMP=907351574 file name=/dsk3/test1/oradata/undotbs01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=7 STAMP=907351574 file name=/dsk3/test1/oradata/users01.dbf
    datafile 5 switched to datafile copy
    input datafile copy RECID=8 STAMP=907351574 file name=/dsk3/test1/oradata/example01.dbf
    datafile 6 switched to datafile copy
    input datafile copy RECID=9 STAMP=907351574 file name=/dsk3/test1/oradata/test1.dbf
    contents of Memory Script:
    {
       set until scn  1471172;
       recover
       clone database
        delete archivelog
       ;
    }
    executing Memory Script
    executing command: SET until clause
    Starting recover at 24-MAR-16
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=18 device type=DISK
    starting media recovery
    archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf
    archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf thread=1 sequence=32
    media recovery complete, elapsed time: 00:00:05
    Finished recover at 24-MAR-16
    Oracle instance started
    Total System Global Area     321507328 bytes
    Fixed Size                     2228024 bytes
    Variable Size                234881224 bytes
    Database Buffers              79691776 bytes
    Redo Buffers                   4706304 bytes
    contents of Memory Script:
    {
       sql clone "alter system set  db_name = 
     ''TEST1'' 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
    sql statement: alter system set  db_name =  ''TEST1'' 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     321507328 bytes
    Fixed Size                     2228024 bytes
    Variable Size                234881224 bytes
    Database Buffers              79691776 bytes
    Redo Buffers                   4706304 bytes
    sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST1" RESETLOGS ARCHIVELOG 
      MAXLOGFILES     16
      MAXLOGMEMBERS      3
      MAXDATAFILES      100
      MAXINSTANCES     8
      MAXLOGHISTORY      292
     LOGFILE
      GROUP   1 ( '/dsk3/test1/logs/''redo01.log' ) SIZE 50 M  REUSE,
      GROUP   2 ( '/dsk3/test1/logs/''redo02.log' ) SIZE 50 M  REUSE,
      GROUP   3 ( '/dsk3/test1/logs/''redo03.log' ) SIZE 50 M  REUSE
     DATAFILE
      '/dsk3/test1/oradata/system01.dbf'
     CHARACTER SET ZHS16GBK
    
    contents of Memory Script:
    {
       set newname for tempfile  1 to 
     "/dsk3/test1/oradata/temp01.dbf";
       switch clone tempfile all;
       catalog clone datafilecopy  "/dsk3/test1/oradata/sysaux01.dbf", 
     "/dsk3/test1/oradata/undotbs01.dbf", 
     "/dsk3/test1/oradata/users01.dbf", 
     "/dsk3/test1/oradata/example01.dbf", 
     "/dsk3/test1/oradata/test1.dbf";
       switch clone datafile all;
    }
    executing Memory Script
    executing command: SET NEWNAME
    renamed tempfile 1 to /dsk3/test1/oradata/temp01.dbf in control file
    cataloged datafile copy
    datafile copy file name=/dsk3/test1/oradata/sysaux01.dbf RECID=1 STAMP=907351727
    cataloged datafile copy
    datafile copy file name=/dsk3/test1/oradata/undotbs01.dbf RECID=2 STAMP=907351728
    cataloged datafile copy
    datafile copy file name=/dsk3/test1/oradata/users01.dbf RECID=3 STAMP=907351728
    cataloged datafile copy
    datafile copy file name=/dsk3/test1/oradata/example01.dbf RECID=4 STAMP=907351728
    cataloged datafile copy
    datafile copy file name=/dsk3/test1/oradata/test1.dbf RECID=5 STAMP=907351729
    datafile 2 switched to datafile copy
    input datafile copy RECID=1 STAMP=907351727 file name=/dsk3/test1/oradata/sysaux01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=2 STAMP=907351728 file name=/dsk3/test1/oradata/undotbs01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=3 STAMP=907351728 file name=/dsk3/test1/oradata/users01.dbf
    datafile 5 switched to datafile copy
    input datafile copy RECID=4 STAMP=907351728 file name=/dsk3/test1/oradata/example01.dbf
    datafile 6 switched to datafile copy
    input datafile copy RECID=5 STAMP=907351729 file name=/dsk3/test1/oradata/test1.dbf
    Reenabling controlfile options for auxiliary database
    Executing: alter database enable block change tracking using file '/dsk4/backup/block.trc'
    ORACLE error from auxiliary database: ORA-19751: could not create the change tracking file
    ORA-19750: change tracking file: '/dsk4/backup/block.trc'
    ORA-27038: created file already exists
    Additional information: 1
    Ignoring error, reattempt command after duplicate finishes
    contents of Memory Script:
    {
       Alter clone database open resetlogs;
    }
    executing Memory Script
    database opened
    Finished Duplicate Db at 24-MAR-16

**五、验证克隆**

Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    18:28:57 SYS@ test1>select status from v$instance;
    STATUS
    ------------
    OPEN
    
    18:29:06 SYS@ test1>select name from v$datafile;
    NAME
    ------------------------------------------------------------------------------------------------------------------------
    /dsk3/test1/oradata/system01.dbf
    /dsk3/test1/oradata/sysaux01.dbf
    /dsk3/test1/oradata/undotbs01.dbf
    /dsk3/test1/oradata/users01.dbf
    /dsk3/test1/oradata/example01.dbf
    /dsk3/test1/oradata/test1.dbf
    6 rows selected.
    
    18:29:13 SYS@ test1>select member from v$logfile;
    MEMBER
    ------------------------------------------------------------------------------------------------------------------------
    /dsk3/test1/logs/redo03.log
    /dsk3/test1/logs/redo02.log
    /dsk3/test1/logs/redo01.log
    
    18:30:19 SYS@ test1>show parameter control
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    control_files                        string      /dsk3/test1/oradata/control01.ctl  <span style="font-family: 宋体, Arial; background-color: rgb(255, 255, 255);">       </span>

**-------- 至此，数据库克隆成功 ！                                     **

[Center]: /images/20220727/5320043df2b54505b1ecd8af0185f8ed.png
[Center 1]: /images/20220727/ebae428e94a74400962c5cccf621396a.png