Oracle 12cR1多租户 DataGuard物理备库实施Step by Step

1. 环境说明

db_name db_unique_name tnsname
主库 192.168.1.45 t12c t12c_p
备库 192.168.1.46 t12c t12c_s
  • 环境变量,主备一致
  • $ env | grep ORA
    ORACLE_SID=t12c
    ORACLE_BASE=/data/oracle
    ORACLE_HOME=/data/oracle/product/12.1.0/db_1
    
  • 主库版本和PDB信息
  • SQL> set linesize 160
    SQL> select * from v$version;
    
    BANNER                                                                               CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
    PL/SQL Release 12.1.0.2.0 - Production                                                    0
    CORE    12.1.0.2.0      Production                                                                0
    TNS for Linux: Version 12.1.0.2.0 - Production                                            0
    NLSRTL Version 12.1.0.2.0 - Production                                                    0
    
    SQL> show pdbs
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB1                           READ WRITE NO
    

    2. 主库准备

    SQL> alter system set log_archive_dest_1='location=/data/oracle/arch' scope=both;
    SQL> shutdown immediate
    SQL> alter database archivelog;
    SQL> alter database open;
    SQL> alter database force logging;
    SQL> alter pluggable database all open;
    

    配置主库的tnsname

    T12C_P =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = test1)(PORT = 1621))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SID = t12c)
        )
      )
    
    T12C_S =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = test2)(PORT = 1621))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SID = t12c)
        )
      )
    

    3. 备库配置tnsname和监听

    $ cat tnsnames.ora
    T12C_P =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = test1)(PORT = 1621))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SID = t12c)
        )
      )
    
    T12C_S =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = test2)(PORT = 1621))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SID = t12c)
        )
      )
    
  • 注意使用静态注册
  • $ cat listener.ora
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = test2)(PORT = 1621))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
        )
      )
    
    SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = t12c)
         (ORACLE_HOME = /data/oracle/product/12.1.0/db_1)
         (SID_NAME = t12c)
        )
      )
    
    ADR_BASE_LISTENER = /data/oracle
    
  • 在备库上启动监听
  • $ lsnrctl start LISTENER
    

    备库创建必要的目录,注意每个PDB的目录都需要手工建立

    $ cd $ORACLE_BASE/admin
    $ mkdir -p t12c/{adump,dpdump,pfile,scripts,xdb_wallet}
    $ mkdir -p $ORACLE_BASE/oradata/t12c/{pdbseed,pdb1}
    $ mkdir -p /data/oracle/arch
    

    主库将pwd文件复制到备库

    $ cd $ORACLE_HOME/dbs
    $ scp orapwt12c test2:`pwd`
    

    4. 主库创建pfile

    SQL> create pfile='/home/oracle/init_p.ora' from spfile;
    File created.
    

    将pfile复制到备库

    $ pwd
    /home/oracle
    $ scp init_p.ora test2:`pwd`/init_s.ora
    

    5. 修改备库pfile

    *.db_name='t12c'
    *.db_unique_name='t12c_s'
    *.log_archive_config='dg_config=(t12c_s,t12c_p)'
    *.log_archive_dest_1='location=/data/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=t12c_s'
    *.log_archive_dest_2='service=t12c_p valid_for=(online_logfiles,primary_role)  lgwr affirm sync db_unique_name=t12c_p'
    *.log_archive_dest_state_1=enable
    *.log_archive_dest_state_2=enable
    *.standby_file_management='auto'
    *.fal_server='t12c_p'
    *.LOG_FILE_NAME_CONVERT='/data/oracle/oradata/t12c/','/data/oracle/oradata/t12c/'
    

    LOG_FILE_NAME_CONVERT目录一致主要为了解决主备角色切换时,online redo clear的问题
    如果不设置这个参数,在rman duplicate最后会发生如下告警:

    ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
    ORA-00312: online log 1 thread 1: '/data/oracle/oradata/t12c/redo01.log'
    
    RMAN-05535: WARNING: All redo log files were not defined properly.
    ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
    ORA-00312: online log 2 thread 1: '/data/oracle/oradata/t12c/redo02.log'
    
    RMAN-05535: WARNING: All redo log files were not defined properly.
    ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
    ORA-00312: online log 3 thread 1: '/data/oracle/oradata/t12c/redo03.log'
    
    RMAN-05535: WARNING: All redo log files were not defined properly.
    ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
    ORA-00312: online log 4 thread 0: '/data/oracle/oradata/t12c/stdredo01.log'
    
    RMAN-05535: WARNING: All redo log files were not defined properly.
    ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
    ORA-00312: online log 5 thread 0: '/data/oracle/oradata/t12c/stdredo02.log'
    
    RMAN-05535: WARNING: All redo log files were not defined properly.
    ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
    ORA-00312: online log 6 thread 0: '/data/oracle/oradata/t12c/stdredo03.log'
    
    RMAN-05535: WARNING: All redo log files were not defined properly.
    ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
    ORA-00312: online log 7 thread 0: '/data/oracle/oradata/t12c/stdredo04.log'
    
    RMAN-05535: WARNING: All redo log files were not defined properly.
    Finished Duplicate Db at 01-MAR-17
    

    后面打开备库启动应用,并不会有错误。

    备库创建spfile

    SQL> create spfile from pfile='/home/oracle/init_s.ora';
    

    备库启动到nomount状态

    SQL> startup nomount
    ORACLE instance started.
    
    Total System Global Area 5049942016 bytes
    Fixed Size                  5294088 bytes
    Variable Size            1258293240 bytes
    Database Buffers         3774873600 bytes
    Redo Buffers               11481088 bytes
    

    6. 主库修改如下参数

    主要修改db_unique_name需要重启,如果不修改主库的这个参数,可以不用重启.

    alter system set db_unique_name='t12c_p' scope=spfile;
    alter system set log_archive_config='dg_config=(t12c_p,t12c_s)' scope=spfile;
    alter system set log_archive_dest_1='location=/data/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=t12c_p' scope=spfile;
    alter system set log_archive_dest_2='service=t12c_s valid_for=(online_logfiles,primary_role)  lgwr affirm sync db_unique_name=t12c_s' scope=spfile;
    alter system set log_archive_dest_state_1=enable scope=spfile;
    alter system set log_archive_dest_state_2=enable scope=spfile;
    alter system set standby_file_management='auto' scope=spfile;
    alter system set fal_server='t12c_s' scope=spfile;
    alter system set LOG_FILE_NAME_CONVERT='/data/oracle/oradata/t12c/','/data/oracle/oradata/t12c/' scope=spfile;
    

    重启主库

    SQL> shutdown immediate
    SQL> startup
    SQL> alter pluggable database all open;
    

    7. 主库添加standby日志

    alter  database add  standby  logfile '/data/oracle/oradata/t12c/stdredo01.log'  size 50M;
    alter  database add  standby  logfile '/data/oracle/oradata/t12c/stdredo02.log'  size 50M;
    alter  database add  standby  logfile '/data/oracle/oradata/t12c/stdredo03.log'  size 50M;
    alter  database add  standby  logfile '/data/oracle/oradata/t12c/stdredo04.log'  size 50M;
    

    8. 备库duplicate

    在备库上操作如下:

    $ rman target sys/oracle@t12c_p auxiliary sys/oracle@t12c_s
    
    Recovery Manager: Release 12.1.0.2.0 - Production on Wed Mar 1 12:48:15 2017
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: T12C (DBID=2993981790)
    connected to auxiliary database: T12C (not mounted)
    
    RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
    
    Starting Duplicate Db at 01-MAR-17
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=362 device type=DISK
    current log archived
    
    contents of Memory Script:
    {
       backup as copy reuse
       targetfile  '/data/oracle/product/12.1.0/db_1/dbs/orapwt12c' auxiliary format 
     '/data/oracle/product/12.1.0/db_1/dbs/orapwt12c'   ;
    }
    executing Memory Script
    
    Starting backup at 01-MAR-17
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=4 device type=DISK
    Finished backup at 01-MAR-17
    
    contents of Memory Script:
    {
       restore clone from service  't12c_p' standby controlfile;
    }
    executing Memory Script
    
    Starting restore at 01-MAR-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: restoring control file
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    output file name=/data/oracle/oradata/t12c/control01.ctl
    output file name=/data/oracle/oradata/t12c/control02.ctl
    Finished restore at 01-MAR-17
    
    contents of Memory Script:
    {
       sql clone 'alter database mount standby database';
    }
    executing Memory Script
    
    sql statement: alter database mount standby database
    
    contents of Memory Script:
    {
       set newname for tempfile  1 to 
     "/data/oracle/oradata/t12c/temp01.dbf";
       set newname for tempfile  2 to 
     "/data/oracle/oradata/t12c/pdbseed/pdbseed_temp012017-03-01_09-58-48-AM.dbf";
       set newname for tempfile  3 to 
     "/data/oracle/oradata/t12c/pdb1/pdbseed_temp012017-03-01_09-58-48-AM.dbf";
       switch clone tempfile all;
       set newname for datafile  1 to 
     "/data/oracle/oradata/t12c/system01.dbf";
       set newname for datafile  3 to 
     "/data/oracle/oradata/t12c/sysaux01.dbf";
       set newname for datafile  4 to 
     "/data/oracle/oradata/t12c/undotbs01.dbf";
       set newname for datafile  5 to 
     "/data/oracle/oradata/t12c/pdbseed/system01.dbf";
       set newname for datafile  6 to 
     "/data/oracle/oradata/t12c/users01.dbf";
       set newname for datafile  7 to 
     "/data/oracle/oradata/t12c/pdbseed/sysaux01.dbf";
       set newname for datafile  8 to 
     "/data/oracle/oradata/t12c/pdb1/system01.dbf";
       set newname for datafile  9 to 
     "/data/oracle/oradata/t12c/pdb1/sysaux01.dbf";
       set newname for datafile  10 to 
     "/data/oracle/oradata/t12c/pdb1/tbs_test.dbf";
       restore
       from service  't12c_p'   clone database
       ;
       sql 'alter system archive log current';
    }
    executing Memory Script
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    renamed tempfile 1 to /data/oracle/oradata/t12c/temp01.dbf in control file
    renamed tempfile 2 to /data/oracle/oradata/t12c/pdbseed/pdbseed_temp012017-03-01_09-58-48-AM.dbf in control file
    renamed tempfile 3 to /data/oracle/oradata/t12c/pdb1/pdbseed_temp012017-03-01_09-58-48-AM.dbf in control file
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    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 restore at 01-MAR-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00001 to /data/oracle/oradata/t12c/system01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/oracle/oradata/t12c/sysaux01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00004 to /data/oracle/oradata/t12c/undotbs01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00005 to /data/oracle/oradata/t12c/pdbseed/system01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00006 to /data/oracle/oradata/t12c/users01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00007 to /data/oracle/oradata/t12c/pdbseed/sysaux01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00008 to /data/oracle/oradata/t12c/pdb1/system01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00009 to /data/oracle/oradata/t12c/pdb1/sysaux01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00010 to /data/oracle/oradata/t12c/pdb1/tbs_test.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    Finished restore at 01-MAR-17
    
    sql statement: alter system archive log current
    current log archived
    
    contents of Memory Script:
    {
       restore clone force from service  't12c_p' 
               archivelog from scn  1800434;
       switch clone datafile all;
    }
    executing Memory Script
    
    Starting restore at 01-MAR-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=42
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=43
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service t12c_p
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=44
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    Finished restore at 01-MAR-17
    
    datafile 1 switched to datafile copy
    input datafile copy RECID=3 STAMP=937493754 file name=/data/oracle/oradata/t12c/system01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=4 STAMP=937493754 file name=/data/oracle/oradata/t12c/sysaux01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=5 STAMP=937493754 file name=/data/oracle/oradata/t12c/undotbs01.dbf
    datafile 5 switched to datafile copy
    input datafile copy RECID=6 STAMP=937493754 file name=/data/oracle/oradata/t12c/pdbseed/system01.dbf
    datafile 6 switched to datafile copy
    input datafile copy RECID=7 STAMP=937493754 file name=/data/oracle/oradata/t12c/users01.dbf
    datafile 7 switched to datafile copy
    input datafile copy RECID=8 STAMP=937493754 file name=/data/oracle/oradata/t12c/pdbseed/sysaux01.dbf
    datafile 8 switched to datafile copy
    input datafile copy RECID=9 STAMP=937493754 file name=/data/oracle/oradata/t12c/pdb1/system01.dbf
    datafile 9 switched to datafile copy
    input datafile copy RECID=10 STAMP=937493754 file name=/data/oracle/oradata/t12c/pdb1/sysaux01.dbf
    datafile 10 switched to datafile copy
    input datafile copy RECID=11 STAMP=937493754 file name=/data/oracle/oradata/t12c/pdb1/tbs_test.dbf
    
    contents of Memory Script:
    {
       set until scn  1800699;
       recover
       standby
       clone database
        delete archivelog
       ;
    }
    executing Memory Script
    
    executing command: SET until clause
    
    Starting recover at 01-MAR-17
    using channel ORA_AUX_DISK_1
    
    starting media recovery
    
    archived log for thread 1 with sequence 42 is already on disk as file /data/oracle/arch/1_42_937475873.dbf
    archived log for thread 1 with sequence 43 is already on disk as file /data/oracle/arch/1_43_937475873.dbf
    archived log for thread 1 with sequence 44 is already on disk as file /data/oracle/arch/1_44_937475873.dbf
    archived log file name=/data/oracle/arch/1_42_937475873.dbf thread=1 sequence=42
    archived log file name=/data/oracle/arch/1_43_937475873.dbf thread=1 sequence=43
    archived log file name=/data/oracle/arch/1_44_937475873.dbf thread=1 sequence=44
    media recovery complete, elapsed time: 00:00:01
    Finished recover at 01-MAR-17
    Finished Duplicate Db at 01-MAR-17
    

    9. 打开备库

    SQL> show pdbs
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       MOUNTED
             3 PDB1                           MOUNTED
    SQL> select status from v$instance;
    
    STATUS
    ------------------------
    MOUNTED
    
    SQL> alter database open;
    
    SQL> alter pluggable database all open;
    

    10. 备库启用应用日志

  • 实时应用
  • SQL> alter database recover managed standby database using current logfile disconnect from session;
    
    Database altered.
    

    11. 检查主库角色和保护等级

    SQL> select PROTECTION_MODE, DATABASE_ROLE from v$database;
    
    PROTECTION_MODE      DATABASE_ROLE
    -------------------- ----------------
    MAXIMUM PERFORMANCE  PRIMARY
    

    关于紫砂壶

    感悟技术人生
    此条目发表在DataGuard分类目录,贴了标签。将固定链接加入收藏夹。