ITPub博客

10gR2最大保护模式DataGuard创建

原创 Linux操作系统 作者:yiyu_66 时间:2007-12-25 09:44:28 0 删除 编辑
 
一、设置主库归档

    设置主库为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
 
 
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/81912/viewspace-1277/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-11

  • 博文量
    12
  • 访问量
    52737