Oracle Cloud

Oracle Lab

  • 博客访问: 138116
  • 博文数量: 598
  • 用 户 组: 普通用户
  • 注册时间: 2014-07-28 22:27
  • 认证徽章:
个人简介

oracle lab

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类
文章存档

2017年(320)

2016年(273)

2015年(4)

2014年(1)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

分类: Oracle

原文地址:Oracle 11gR2 使用RMAN Duplicate复制数据库 作者:wuweilong

Oracle 11gR2 使用RMAN Duplicate复制数据库 

作者:吴伟龙 PrudentWoo QQ286507175

   前言:

    上周刚做完一个项目,用户要求RAC的数据库能够自动备份到另外一个单节点上,单节点能够正常拿起来就能用。当时跟他们讨论的是用ADG来做,但通过描述后,用户觉得如果要人工干预的话太麻烦,它不想做任何的人工干预,实现数据自动到这台单机上来,那只是一台备用的数据库,没事的时候可以登录上去看看历史的数据情况。

       这个时候我提出了11g的RMAN Duplicate方案,它可以在线实现异机的在线备份,且无需人工干预,通过网络将数据传输过来后能够自动open备机.

如图:
   

      RMAN Duplicate 是从Oracle 10g就已经开始有的一个功能;但是在11g的时候进行了很多增强,免去了10g版本下需要人工复制到备机上过来进行恢复的尴尬境地。通过11g的Duplicate可以创建一个完全相同但DBID不同的备机数据库。在11g的RMAN Duplicate中可以通过Active database duplicate和Backup-based duplicate两种方法实现。我们在用户现场采用的是Active Database Duplicate;因为它不用先把目标数据库进行RMAN备份,只需要目标数据库处于归档模式下即可通过网络对数据库进行恢复,且恢复完成后能够自动Open Duplicate Database。这样就正好满足了用户不用人工干预的需求。尤其是对于大数据库特别是TB级别的数据库其优点时非常明显,恢复前不需要进行单独的备份,减少了备份及拷贝备份文件的时间,同时还节省了备份空间。下面我们来进行完整的RAC to Single Instance的Active Database Duplicate操作。

一、环境介绍


 

HostName

DBName

public ip

11gR2

RAC

db01

woo1

192.168.7.51

db02

woo2

192.168.7.52

Duplicate

standby

woo

192.168.7.55

 

二、开始配置Duplicate Database,步骤如下:
2.1 构建辅助数据库目录结构,配置辅助数据库相关系统参数。

2.2 安装软件并创建数据库。

2.3 开启归档
2.3 创建pfile,并修改
2.4
创建辅助数据库实例口令文件
2.5 配置静态监听
2.6 用pfile文件,将辅助数据库启动到nomount状态
2.7 执行RMAN duplicate命令复制数据库
2.8 创建spfile文件

三、构建辅助数据库目录结构,配置辅助数据库相关参数。(简)
  1. /usr/sbin/groupadd -g 502 dba
    /usr/sbin/groupadd -g 501 oinstall
    /usr/sbin/useradd -u 502 -g oinstall -G dba oracle
    mkdir -p /DBSoft/oraInventory
    mkdir -p /DBSoft/oracle/product/11.2.4/dbhome_1
    chown -R oracle:oinstall /DBSoft
    chmod -R 775 /DBSoft
    echo "oracle"|passwd --stdin oracle
    yum install -y setarch binutils compat-libstdc++-33 compat-libcap1 compat-db compat-libstdc++ compat-gcc elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc-common glibc
    -devel libaio libaio-devel libgcc libstdc++ libstdc++-devel make ksh numactl sysstat libXp unixODBC unixODBC-devel udev
    cat >> /etc/sysctl.conf <<EOF
    kernel.shmall = 4294967296
    kernel.sem = 510 65280 510 128
    kernel.shmmni = 4096
    kernel.shmmax = 137438953472
    net.ipv4.ip_local_port_range = 9000 65500
    net.core.rmem_default = 1048576
    net.core.rmem_max = 4194304
    net.core.wmem_default = 262144
    net.core.wmem_max = 1048576
    fs.file-max = 6815744
    fs.aio-max-nr = 1048576
    vm.swappiness = 0
    vm.dirty_background_ratio = 3
    vm.dirty_ratio = 80
    vm.dirty_expire_centisecs = 500
    vm.dirty_writeback_centisecs = 100
    net.ipv4.tcp_sack = 0
    net.ipv4.tcp_timestamps = 0
    net.ipv4.conf.default.rp_filter = 0
    net.ipv4.tcp_wmem = 262144
    net.ipv4.tcp_rmem = 4194304
    EOF


    /sbin/sysctl –p
    cat >> /etc/security/limits.conf <<EOF
    oracle          soft    nproc           2047
    oracle          hard    nproc           16384
    oracle          soft    nofile          65536
    oracle          hard    nofile          65536
    oracle          soft    memlock         3145728
    oracle          hard    memlock         3145728


    #signaturelevin
    EOF
    cat >> /etc/profile <<EOF


    if [ \$USER = "oracle" ]; then
        if [ \$SHELL = "/bin/ksh" ]; then
                    ulimit -p 16384
                    ulimit -n 65536
            else
                    ulimit -u 16384 -n 65536
            fi
    fi
    EOF


    cat >> /home/oracle/.bash_profile <<EOF
    export TMP=/tmp
    export TMPDIR=\$TMP
    export EDIT=vi
    alias  vi=vim
    export ORACLE_HOSTNAME=$hostname
    export ORACLE_UNQNAME=$db_name
    export ORACLE_BASE=/DBSoft/oracle
    export ORACLE_HOME=\$ORACLE_BASE/product/11.2.4/dbhome_1
    export ORACLE_SID=$instance_name
    export ORACLE_TERM=xterm
    export PATH=/usr/sbin:\$PATH
    export PATH=\$ORACLE_HOME/bin:\$PATH
    export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
    export CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
    export NLS_LANG=AMERICAN_AMERICA AL32UTF16
    umask 022
    EOF

四、安装软件并创建数据库
  1. ./runInstaller -silent -debug -force -responseFile /home/oracle/database/response/db_install.rsp

  2. dbca -silent -responsefile /home/oracle/database/response/dbca.rsp

五、开启归档
  1. SQL> alter system set log_archive_dest_1=\'location=+FRA001/archive\' scope=spfile sid=\'woo1\';

  2. System altered.
  3. SQL> alter database archivelog;

  4. Database altered.
  5. SQL> alter database open;

  6. Database altered.

  7. SQL> archive log list;
  8. Database log mode Archive Mode
  9. Automatic archival Enabled
  10. Archive destination +FRA001/archive
  11. Oldest online log sequence 978
  12. Next log sequence to archive 979
  13. Current log sequence 979
  14. SQL> alter system switch logfile;
  15. alter system switch logfile;

  16. System altered.

  17. SQL>

  18. System altered.
  19. SQL> archive log list;
  20. Database log mode Archive Mode
  21. Automatic archival Enabled
  22. Archive destination +FRA001/archive
  23. Oldest online log sequence 980
  24. Next log sequence to archive 981
  25. Current log sequence 981

六、配置静态监听

   RAC节点监听信息如下:

  1. $ cd $ORACLE_HOME/network/admin
  2. LISTENER =
  3.   (DESCRIPTION_LIST =
  4.     (DESCRIPTION =
  5.       (ADDRESS = (PROTOCOL = TCP)(HOST = scan.woo.com)(PORT = 1521))
  6.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  7.     )
  8.   )
  9.  
  10. SID_LIST_LISTENER =
  11.   (SID_LIST =
  12.      (SID_DESC =
  13.         (GLOBAL_DBNAME = woo.com)
  14.         (ORACLE_HOME = /DBSoft/oracle/product/11.2.0/db_1)
  15.         (SID_NAME = woo)
  16.       )
  17.   )
  18. ADR_BASE_LISTENER = /DBSoft/oracle

单节点监听信息如下:

  1. LISTENER =
  2.   (DESCRIPTION_LIST =
  3.     (DESCRIPTION =
  4.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.55)(PORT = 1521))
  5.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  6.     )
  7.   )
  8.  
  9. SID_LIST_LISTENER =
  10.   (SID_LIST =
  11.      (SID_DESC =
  12.         (GLOBAL_DBNAME = woo.com)
  13.         (ORACLE_HOME = /DBSoft/oracle/product/11.2.0/db_1)
  14.         (SID_NAME = woo)
  15.       )
  16.   )
  17. ADR_BASE_LISTENER = /DBSoft/oracle

配置所有节点tnsname信息如下:

  1. WOO =
  2.   (DESCRIPTION =
  3.     (ADDRESS_LIST =
  4.       (ADDRESS = (PROTOCOL = TCP)(HOST = scan.woo.com)(PORT = 1521))
  5.     )
  6.     (CONNECT_DATA =
  7.       (SERVICE_NAME = woo)
  8.     )
  9.   )
  10.  
  11. DUPLICATE =
  12.   (DESCRIPTION =
  13.     (ADDRESS_LIST =
  14.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.55)(PORT = 1521))
  15.     )
  16.     (CONNECT_DATA =
  17.       (SERVER = DEDICATED)
  18.       (SERVICE_NAME = woo)
  19.     )
  20.   )

 六、启动数据库到nomount状态
  1. SQL> startup nomount
  2. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
  3. ORACLE instance started.

  4. Total System Global Area 954155008 bytes
  5. Fixed Size 1368632 bytes
  6. Variable Size 306187720 bytes
  7. Database Buffers 641728512 bytes
  8. Redo Buffers 4870144 bytes
  9. SQL>exit                           ---------必须退出
  10. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
  11. With the Partitioning, Oracle Label Security, OLAP, Data Mining,
  12. Oracle Database Vault and Real Application Testing options

七、 在rac端任意节点通过RMAN连接到目标实例和辅助实例,执行duplicate命令复制数据库:

  1. [oracle@db01 ~]$ rman target sys/oracle@woo auxiliary sys/oracle@standby


    Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 3 19:39:00 2015


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


    connected to target database: WOO (DBID=4218366793)
    connected to auxiliary database: WOO (not mounted)


    RMAN>
    RMAN> duplicate target database to woo from active database nofilenamecheck;


    Starting Duplicate Db at 03-JUN-15
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=19 device type=DISK


    contents of Memory Script:
    {
       sql clone "alter system set  db_name = 
     ''WOO'' comment=
     ''Modified by RMAN duplicate'' scope=spfile";
       sql clone "alter system set  db_unique_name = 
     ''WOO'' comment=
     ''Modified by RMAN duplicate'' scope=spfile";
       shutdown clone immediate;
       startup clone force nomount
       backup as copy current controlfile auxiliary format  '/DBSoft/oracle/oradata/woo/control01.ctl';
       restore clone controlfile to  '/DBSoft/oracle/fast_recovery_area/woo/control02.ctl' from 
     '/DBSoft/oracle/oradata/woo/control01.ctl';
       alter clone database mount;
    }
    executing Memory Script
    sql statement: alter system set  db_name =  ''WOO'' comment= ''Modified by RMAN duplicate'' scope=spfile


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


    Oracle instance shut down


    Oracle instance started


    Total System Global Area     954155008 bytes


    Fixed Size                     1368632 bytes
    Variable Size                306187720 bytes
    Database Buffers             641728512 bytes
    Redo Buffers                   4870144 bytes


    Starting backup at 03-JUN-15
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=43 device type=DISK
    channel ORA_DISK_1: starting datafile copy
    copying current control file
    output file name=/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/snapcf_woo.f tag=TAG20150603T194000 RECID=3 STAMP=881437202
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
    Finished backup at 03-JUN-15


    Starting restore at 03-JUN-15
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=19 device type=DISK


    channel ORA_AUX_DISK_1: copied control file copy
    Finished restore at 03-JUN-15


    database mounted


    contents of Memory Script:
    {
       sql clone 'alter database flashback off';
       set newname for datafile  1 to 
     "/DBSoft/oracle/oradata/woo/system01.dbf";
       set newname for datafile  2 to 
     "/DBSoft/oracle/oradata/woo/sysaux01.dbf";
       set newname for datafile  3 to 
     "/DBSoft/oracle/oradata/woo/undotbs01.dbf";
       set newname for datafile  4 to 
     "/DBSoft/oracle/oradata/woo/users01.dbf";
       backup as copy reuse
       datafile  1 auxiliary format 
     "/DBSoft/oracle/oradata/woo/system01.dbf"   datafile 
     2 auxiliary format 
     "/DBSoft/oracle/oradata/woo/sysaux01.dbf"   datafile 
     3 auxiliary format 
     "/DBSoft/oracle/oradata/woo/undotbs01.dbf"   datafile 
     4 auxiliary format 
     "/DBSoft/oracle/oradata/woo/users01.dbf"   ;
       sql 'alter system archive log current';
    }
    executing Memory Script


    sql statement: alter database flashback off


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME
    Starting backup at 03-JUN-15
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00001 name=/DBSoft/oracle/oradata/woo/system01.dbf
    output file name=/DBSoft/oracle/oradata/woo/system01.dbf tag=TAG20150603T194019
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:47
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00002 name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
    output file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf tag=TAG20150603T194019
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:07
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00003 name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
    output file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf tag=TAG20150603T194019
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00004 name=/DBSoft/oracle/oradata/woo/users01.dbf
    output file name=/DBSoft/oracle/oradata/woo/users01.dbf tag=TAG20150603T194019
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
    Finished backup at 03-JUN-15


    sql statement: alter system archive log current


    contents of Memory Script:
    {
       backup as copy reuse
       archivelog like  "/DBArchive/archive/1_63_878860684.dbf" auxiliary format 
     "/DBArchive2/archive/1_63_878860684.dbf"   ;
       catalog clone archivelog  "/DBArchive2/archive/1_63_878860684.dbf";
       switch clone datafile all;
    }
    executing Memory Script


    Starting backup at 03-JUN-15
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=63 RECID=38 STAMP=881437711
    output file name=/DBArchive2/archive/1_63_878860684.dbf RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
    Finished backup at 03-JUN-15


    cataloged archived log
    archived log file name=/DBArchive2/archive/1_63_878860684.dbf RECID=38 STAMP=881437696


    datafile 1 switched to datafile copy
    input datafile copy RECID=3 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/system01.dbf
    datafile 2 switched to datafile copy
    input datafile copy RECID=4 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=5 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=6 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/users01.dbf


    contents of Memory Script:
    {
       set until scn  1338740;
       recover
       clone database
        delete archivelog
       ;
    }
    executing Memory Script


    executing command: SET until clause


    Starting recover at 03-JUN-15
    using channel ORA_AUX_DISK_1


    starting media recovery


    archived log for thread 1 with sequence 63 is already on disk as file /DBArchive2/archive/1_63_878860684.dbf
    archived log file name=/DBArchive2/archive/1_63_878860684.dbf thread=1 sequence=63
    media recovery complete, elapsed time: 00:00:00
    Finished recover at 03-JUN-15
    Oracle instance started


    Total System Global Area     954155008 bytes


    Fixed Size                     1368632 bytes
    Variable Size                306187720 bytes
    Database Buffers             641728512 bytes
    Redo Buffers                   4870144 bytes


    contents of Memory Script:
    {
       sql clone "alter system set  db_name = 
     ''WOO'' 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 =  ''WOO'' 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     954155008 bytes


    Fixed Size                     1368632 bytes
    Variable Size                306187720 bytes
    Database Buffers             641728512 bytes
    Redo Buffers                   4870144 bytes
    sql statement: CREATE CONTROLFILE REUSE SET DATABASE "WOO" RESETLOGS ARCHIVELOG 
      MAXLOGFILES     16
      MAXLOGMEMBERS      3
      MAXDATAFILES      100
      MAXINSTANCES     8
      MAXLOGHISTORY      292
     LOGFILE
      GROUP   1 ( '/DBSoft/oracle/oradata/woo/redo01.log' ) SIZE 50 M  REUSE,
      GROUP   2 ( '/DBSoft/oracle/oradata/woo/redo02.log' ) SIZE 50 M  REUSE,
      GROUP   3 ( '/DBSoft/oracle/oradata/woo/redo03.log' ) SIZE 50 M  REUSE
     DATAFILE
      '/DBSoft/oracle/oradata/woo/system01.dbf'
     CHARACTER SET WE8MSWIN1252




    contents of Memory Script:
    {
       set newname for tempfile  1 to 
     "/DBSoft/oracle/oradata/woo/temp01.dbf";
       switch clone tempfile all;
       catalog clone datafilecopy  "/DBSoft/oracle/oradata/woo/sysaux01.dbf", 
     "/DBSoft/oracle/oradata/woo/undotbs01.dbf", 
     "/DBSoft/oracle/oradata/woo/users01.dbf";
       switch clone datafile all;
    }
    executing Memory Script


    executing command: SET NEWNAME


    renamed tempfile 1 to /DBSoft/oracle/oradata/woo/temp01.dbf in control file


    cataloged datafile copy
    datafile copy file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf RECID=1 STAMP=881437718
    cataloged datafile copy
    datafile copy file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf RECID=2 STAMP=881437718
    cataloged datafile copy
    datafile copy file name=/DBSoft/oracle/oradata/woo/users01.dbf RECID=3 STAMP=881437718


    datafile 2 switched to datafile copy
    input datafile copy RECID=1 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=2 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=3 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/users01.dbf


    contents of Memory Script:
    {
       Alter clone database open resetlogs;
    }
    executing Memory Script


    database opened
    Reenabling controlfile options for auxiliary database
    Executing: alter database flashback on
    Finished Duplicate Db at 03-JUN-15


    RMAN>





至此,duplicate已经完成,standby数据库已经起来了。


  1. [oracle@db02 ~]$ sqlplus / as sysdba

  2. SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 3 19:53:02 2015

  3. Copyright (c) 1982, 2013, Oracle. All rights reserved.


  4. Connected to:
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
  6. With the Partitioning, Oracle Label Security, OLAP, Data Mining,
  7. Oracle Database Vault and Real Application Testing options

  8. SQL> col host_name format a15
  9. SQL> col file_name format a40
  10. SQL> set line 150
  11. SQL> select host_name,instance_name,status from gv$instance;

  12. HOST_NAME INSTANCE_NAME STATUS
  13. --------------- ---------------- ------------
  14. db02 woo OPEN

  15. SQL> select tablespace_name,file_name from dba_data_files;

  16. TABLESPACE_NAME FILE_NAME
  17. ------------------------------ ----------------------------------------
  18. SYSTEM /DBSoft/oracle/oradata/woo/system01.dbf
  19. SYSAUX /DBSoft/oracle/oradata/woo/sysaux01.dbf
  20. UNDOTBS1 /DBSoft/oracle/oradata/woo/undotbs01.dbf
  21. USERS /DBSoft/oracle/oradata/woo/users01.dbf

注意:这里的nofilenamecheck参数需要解释下:

       如果在复制时,位置不同时,我们会用参数db_file_name_convert 对文件位置进行转换。 但是在这个复制示例中我们用的是相同的位置。 所以这里必须加上nofilenamecheck参数。 该参数通知复制操作不必在执行还原操作前确认文件名是不同的。如果没有指定nofilenamecheck参数,rman会给出如下错误:

RMAN-05001: auxiliary filename  /DBSoft/oracle/oradata/woo/users01.dbf conflicts with a file used by the target database


                  








 
                           
     


         


阅读(46) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册