10gR2最大保護模式DataGuard建立

一、設定主庫歸檔

    設定主庫為force logging
    SQL> alter database force logging;

    設定主庫為歸檔模式：
    SQL> archive log list
    SQL> shutdown immediate
    SQL> startup mount
    SQL> alter database archivelog;
    SQL> archive log list

    為主資料庫新增"備用聯機日誌檔案"，這裡要保證備日誌檔案與主庫聯機日誌檔案相同大小。

    新增備用日誌檔案是規則：
備用日誌最少應該比redo log 多一個。推薦的備重做日誌數依賴於主資料庫上的執行緒數。
        (每執行緒日誌檔案最大數目 + 1 ) * 執行緒數
    alter database add standby logfile
        group 4 ('/data/oradata/std_redo04a.log','/data/oradata/std_redo04b.log') size 50m,
        group 5 ('/data/oradata/std_redo05a.log','/data/oradata/std_redo05b.log') size 50m,
        group 6 ('/data/oradata/std_redo06a.log','/data/oradata/std_redo06b.log') size 50m,
        group 7 ('/data/oradata/std_redo07a.log','/data/oradata/std_redo08b.dbf') size 50m;


二、修改主庫引數檔案

    SQL> create pfile from spfile;

    主庫initorcl.ora
    ======================
    *.audit_file_dest='/opt/oracle/admin/orcl/adump'
    *.background_dump_dest='/opt/oracle/admin/orcl/bdump'
    *.core_dump_dest='/opt/oracle/admin/orcl/cdump'
    *.user_dump_dest='/opt/oracle/admin/orcl/udump'
    *.compatible='10.2.0.1.0'
    *.control_files='/data/oradata/control01.ctl','/data/oradata/control02.ctl','/data/oradata/control03.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_file_multiblock_read_count=16
    *.db_name='orcl'
    *.job_queue_processes=10
    *.nls_territory='AMERICA'
    *.open_cursors=300
    *.pga_aggregate_target=16777216
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sga_target=167772160
    *.undo_management='AUTO'
    *.undo_tablespace='UNDOTBS1'
    #修改的部分
    *.DB_UNIQUE_NAME=orcl
    *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,bforcl)'
    *.LOG_ARCHIVE_DEST_1='LOCATION=/data/archive/ MANDATORY REOPEN=60 MAX_FAILURE=3 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
    *.LOG_ARCHIVE_DEST_2='SERVICE=bforcl LGWR SYNC AFFIRM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bforcl'
    *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
    *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
    *.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    *.LOG_ARCHIVE_FORMAT=orcl_%t_%s_%r.dbf
    *.#以下為了角色切換設定
    *.FAL_CLIENT = orcl
    *.FAL_SERVER = bforcl
    *.STANDBY_FILE_MANAGEMENT =AUTO

    oracle@suse10~> mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak
    oracle@suse10~> sqlplus "/as sysdba"
    SQL> create spfile from pfile;

三、配置主資料庫listener及tnsnames

    主庫listener.ora
    =======================
    SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
          (SID_NAME = orcl)
          (ORACLE_HOME = /opt/oracle/10.2.0.1)
        )
    )

    LISTENER =
    (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
          )
        )
    )

    tnsnames.ora
    =============
    RCL =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
        )
    )

    BFORCL =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = bforcl)
        )
    )
   
    生成備用庫控制檔案
    alter database create standby controlfile as '/tmp/standby_ctl01.ctl';
   
四、複製檔案到備機

    oracle@suse10~> scp /data/oradata/* oracle@192.168.0.143:/data/oradata/
    oracle@suse10 dbs> scp initorcl.ora oracle@192.168.0.143:` pwd `
    oracle@suse10 admin> scp listener.ora tnsnames.ora oracle@192.168.0.143:` pwd `
    oracle@suse10 tmp> scp standby_ctl01.ctl oracle@192.168.0.143:/home/oracle
    生成備用庫密碼檔案(保證SYS密碼與主庫相同)
    orapwd file=orapwbforcl.ora password=admin entries=10
   
    多重備用庫控制檔案
    cp standby_ctl01.ctl standby_ctl02.ctl
    mkdir -p /data/{oradata,archvie}
    mkdir -p /opt/oracle/admin/bforcl/{adump,bdump,cdump,pfile,udump}

五、修改備用庫引數檔案及listener

    *.audit_file_dest='/opt/oracle/admin/bforcl/adump'
    *.background_dump_dest='/opt/oracle/admin/bforcl/bdump'
    *.core_dump_dest='/opt/oracle/admin/bforcl/cdump'
    *.user_dump_dest='/opt/oracle/admin/bforcl/udump'

    #修改的部分
    *.control_file=('/data/oradata/standby_ctl01.ctl','/data/oradata/standby_ctl02.ctl')
    *.DB_UNIQUE_NAME=bforcl
    *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,bforcl)'
    *.LOG_ARCHIVE_DEST_1='LOCATION=/data/archive/ MANDATORY REOPEN=60 MAX_FAILURE=3 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
    *.LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
    *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
    *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
    *.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    *.LOG_ARCHIVE_FORMAT=bforcl_%t_%s_%r.dbf

    *.FAL_CLIENT = bforcl
    *.FAL_SERVER = orcl
    *.STANDBY_FILE_MANAGEMENT =AUTO #此引數保證主庫上對錶空間/資料檔案的操作會自動應用到備用庫上。

    備庫listener.ora
    =======================
    SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
          (SID_NAME = bforcl)
          (ORACLE_HOME = /opt/oracle/10.2.0.1)
        )
    )

    LISTENER =
    (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
          )
        )
    )

    tnsnames.ora
    =============
    RCL =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
        )
    )

    BFORCL =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = bforcl)
        )
    )


六、測試主備機之間網路聯通性

    tnsping orcl
    tnsping bforcl
    tnsping並不能保證可以互相登陸。
    這裡要確保sqlplus 可以互相登陸對方資料庫。

七、開啟備用庫到mount狀態

    檢視備用庫是否包含備用日誌。如果不包含備用日誌檔案，不能執行在"最大保護"或"最大可用性"模式。
    SQL> select * from v$logfile;
   
    SQL> select * from v$standby_log;
    正式執行中v$standy_log中的某一個日誌檔案同步接收主庫LGWR程式傳送過來的重做條目。

八、開啟主庫，修改主庫DataGuard保護模式

    SQL> startup mount

    SQL> select name,db_unique_name,protection_mode from v$database;
   
    NAME DB_UNIQUE_NAME PROTECTION_MODE
    ----- --------------- --------------------
    ORCL orcl            MAXIMUM PERFORMANCE
   
    SQL> alter database set standby database to maximize protection;
   
    開啟主庫到OPEN狀態，監控alert日誌檔案，檢視是否配置成功。
   
    切換主庫保護模式的語法：
    ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE }


九、開啟備用庫恢復程式

    recover managed standby database diconnect from session;
    執行上面這條語句，備用庫會在主庫日誌檔案切換(備用日誌檔案切換)歸檔後，使用歸檔檔案恢復資料庫。
    recover managed standby database using current logfile disconnect from session;   
    這條語句與上面不同的是，備用日誌檔案切換，生成歸檔日誌前，先恢復資料庫。
   
    recover managed standby database finish;
    這條語句是在做切換時，儘量多的保護資料。從備用日誌檔案中恢復資料

    recover managed standby database cancel;
    取消備用庫自動恢復
   
    可以在使用上面兩條語句時，檢視v$managed_standby的不同。
    SQL> recover managed standby database disconnect;
    Media recovery complete.
    SQL> select process,status from v$managed_standby;

    PROCESS   STATUS
    --------- ------------
    ARCH      CLOSING
    ARCH      CLOSING
    MRP0      WAIT_FOR_LOG
    RFS       IDLE
    RFS       IDLE
    RFS       IDLE


    SQL> recover managed standby database cancel;
    Media recovery complete.


    SQL> recover managed standby database using current logfile disconnect;
    Media recovery complete.
    SQL> select process,status from v$managed_standby;
   
    PROCESS   STATUS
    --------- ------------
    ARCH      CLOSING
    ARCH      CLOSING
    MRP0      APPLYING_LOG
    RFS       IDLE
    RFS       IDLE
    RFS       IDLE

    在恢復過程中，可以檢視v$standby_log,此檢視中會有日誌檔案與生產機的當前日誌檔案是同步的。


十、需要注意

    最大保護模式下，至少要有一個備用庫的設定滿足LOG_ARCHIVE_DEST_2='SERVICE=bforcl LGWR SYNC AFFIRM'並且，主庫設定為MAXIMUM PROTECTION模式。

    最大保護模式下，備用庫還是會等待備用庫切換日誌後歸檔時或歸檔前才會恢復備用系統。而不是主資料庫的操作適時反映在備用庫。適時的僅僅是日誌檔案合而已。這一點要注意。

    在主備庫切換的時候可以執行recover managed standby database finish應用適時同步過來的日誌資訊，這樣可以最大限度的保證資料。





測試：

1.在主庫開啟情況下關閉備庫：

   oracle 10g R2中，執行在LGWR最大保護模式下的備庫，主庫沒有關閉的情況下不允許關閉備庫。
    也就是說，每當關閉資料庫時，必須先關閉主庫才能關閉備用庫。
    開啟資料庫情況剛好相反。備庫沒有MOUNT而直接開啟主庫是不允許的。在資料庫開啟階段會報錯
   
    SQL> startup
    ORACLE instance started.
   
    Total System Global Area 167772160 bytes
    Fixed Size                  1218316 bytes
    Variable Size              71305460 bytes
    Database Buffers           92274688 bytes
    Redo Buffers                2973696 bytes
    Database mounted.
    ORA-03113: end-of-file on communication channel
   
    alter中錯誤資訊見下："錯誤"
   開啟資料庫必須是先mount備用庫，才能開啟主資料庫。
   
    SQL> shutdown immediate
    ORA-01154: database busy. Open, close, mount, and dismount not allowed now
   
    alter日誌中資訊：
    Completed: ALTER DATABASE RECOVER managed standby database cancel
    Wed Jul 4 06:47:40 2007
    Attempt to shut down Standby Database
    Standby Database operating in NO DATA LOSS mode
    Detected primary database alive, shutdown primary first, shutdown aborted

2.網路中斷
   
    測試斷開備用機網路
    $ifdown eth0
   
    在主資料庫做操作
    SQL> create table t (id number);
    操作一直處於掛起狀態。
    檢視主資料庫alter日誌顯示如下錯誤，
    如果網路斷開，主庫LGWR程式會一直掛起一直探測是否可以傳輸資料到備用機，直到備用機網路再次連線。
   
    主資料庫alter錯誤資訊：
   
    Thu Jul 5 14:52:22 2007
    ORA-16198: LGWR received timedout error from KSR
    LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16198)
    ORA-16198: LGWR received timedout error from KSR
    LGWR: Error 16198 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'bforcl'
    LNSb started with pid=16, OS id=12830
    Error 12560 received logging on to the standby
    Thu Jul 5 14:52:52 2007
    LGWR: Error 12560 attaching to RFS for reconnect
    LNSb started with pid=16, OS id=12834
    Error 12560 received logging on to the standby
    Thu Jul 5 14:53:17 2007
    LGWR: Error 12560 attaching to RFS for reconnect
    LNSb started with pid=16, OS id=12836
    Error 12560 received logging on to the standby
    Thu Jul 5 14:53:42 2007
    LGWR: Error 12560 attaching to RFS for reconnect
    LNSb started with pid=16, OS id=12838
    Error 12560 received logging on to the standby
    ......
   
    Thu Jul 5 14:56:13 2007
    LGWR: Error 12560 attaching to RFS for reconnect
    LNSb started with pid=16, OS id=12863
    Thu Jul 5 14:56:33 2007
    LGWR: RFS network connection re-established at host 'bforcl'
    LGWR: RFS destination opened for reconnect at host 'bforcl'   
   
   
   















附：

開啟資料庫時的錯誤資訊：

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

主庫alter日誌錯誤：
alter database open
Wed Jul 4 04:07:55 2007
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=14, OS id=7898
Wed Jul 4 04:07:57 2007
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
LGWR: Primary database is in MAXIMUM PROTECTION mode
Wed Jul 4 04:07:57 2007
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LNSb started with pid=16, OS id=7902
ARC1 started with pid=15, OS id=7900
Wed Jul 4 04:08:05 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16086: standby database does not contain available standby log files
Wed Jul 4 04:08:05 2007
LGWR: Error 16086 verifying archivelog destination LOG_ARCHIVE_DEST_2
Wed Jul 4 04:08:05 2007
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Error 16086 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'bforcl'
LGWR: Continuing...
LGWR: Minimum of 1 applicable standby database required
Wed Jul 4 04:08:09 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16072: a minimum of one standby database destination is required
Wed Jul 4 04:08:09 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16072: a minimum of one standby database destination is required
LGWR: terminating instance due to error 16072
Instance terminated by LGWR, pid = 7882

備庫alter錯誤：
Primary database is in MAXIMUM PROTECTION mode
Changing standby controlfile to MAXIMUM PROTECTION mode
RFS[4]: No standby redo logfiles selected (reason:1)
Wed Jul 4 04:02:58 2007
Errors in file /opt/oracle/admin/bforcl/udump/bforcl_rfs_9362.trc:
ORA-16086: standby database does not contain available standby log files
Wed Jul 4 04:09:51 2007