精华内容
下载资源
问答
  • Oracle 级联DG部署以及切换测试

    千次阅读 2019-06-08 11:29:41
    DG搭建很简单,级联DG也是生产中比较常见的部署方式,如果一级DG切换为主库,那么就相当于一主两备。 一、 搭建一级备库 环境:11.2.0.4 单机 主库:192.168.100.129 SID:ecms 主机名:nhjcgl-db 一级备库:192....

    **前言:**文档描述搭建一级、二级DG并且模拟多个场景切换测试。DG搭建很简单,级联DG也是生产中比较常见的部署方式,如果一级DG切换为主库,那么就相当于一主两备。

    一、 搭建一级备库
    环境:11.2.0.4 单机
    主库:192.168.100.129 SID:ecms 主机名:nhjcgl-db
    一级备库:192.168.100.199 SID:ecms 主机名:dg1
    二级备库:192.168.100.200 SID:ecms 主机名:dg2

    1.1 主库准备
    select FORCE_LOGGING,log_mode from v$database;
    alter database force logging;
    1.2 开启归档
    shu immediate
    startup mount
    alter database archivelog;
    alter database open;

    1.3 参数设置
    alter system set log_archive_config=‘dg_config=(ecms,ecmsdg1)’;
    alter system set log_archive_dest_2=‘service=ecmsdg1 async valid_for=(online_logfile,primary_role) db_unique_name=ecmsdg1’;

    1.4 监听配置
    #TNS
    ECMS =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = nhjcgl-db)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ecms)
    )
    )

    ecmsdg1 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.199)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ecms)
    )
    )

    ##Listener
    [oracle@nhjcgl-db admin]$ cat listener.ora
    # listener.ora Network Configuration File: /u01/app/oracle/11.2.4/db_home1/network/admin/listener.ora
    # Generated by Oracle configuration tools.

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = nhjcgl-db)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_NAME = ecms)
    (ORACLE_HOME =/u01/app/oracle/11.2.4/db_home1)
    (SID_NAME= ecms)
    )
    )

    ADR_BASE_LISTENER = /u01/app/oracle

    1.5 备库准备
    复制密码文件、参数文件、监听文件
    修改pfile
    [oracle@dg1 ~]$ cat pfile001
    ecms.__db_cache_size=281018368
    ecms.__java_pool_size=20971520
    ecms.__large_pool_size=4194304
    ecms.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
    ecms.__pga_aggregate_target=289406976
    ecms.__sga_target=545259520
    ecms.__shared_io_pool_size=0
    ecms.__shared_pool_size=222298112
    ecms.__streams_pool_size=8388608
    *.audit_file_dest=’/u01/app/oracle/admin/ecms/adump’
    *.audit_trail=‘db’
    *.compatible=‘11.2.0.4.0’
    *.control_files=’/u01/app/oracle/oradata/ecms/control01.ctl’
    .db_block_size=8192
    #
    .db_create_file_dest=’/u01/app/oracle/oradata/ecms/’
    *.db_domain=’’
    *.db_name=‘ecms’
    *.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’
    *.db_recovery_file_dest_size=4385144832
    *.diagnostic_dest=’/u01/app/oracle’
    *.dispatchers=’(PROTOCOL=TCP) (SERVICE=ecmsXDB)’
    *.fal_client=‘ECMSDG1’
    *.fal_server=‘ECMS’
    *.java_jit_enabled=TRUE
    *.java_pool_size=0
    *.large_pool_size=4194304
    *.log_archive_config=‘dg_config=(ecms,ecmsdg1)’
    *.log_archive_dest_2=‘service=ecms async valid_for=(online_logfile,primary_role) db_unique_name=ecms’
    *.memory_target=831520768
    *.open_cursors=3500
    *.processes=150
    *.remote_login_passwordfile=‘EXCLUSIVE’
    *.service_names=‘ecms,tt’
    *.standby_file_management=‘AUTO’
    *.undo_tablespace=‘UNDOTBS1’
    *.db_unique_name=ECMSDG1

    1.6 创建目录
    /u01/app/oracle/admin/ecms/adump
    /u01/app/oracle/oradata/ecms/
    /u01/app/oracle/fast_recovery_area
    1.7 启动
    SQL> startup nomount pfile=’/home/oracle/pfile001’;
    ORACLE instance started.
    Total System Global Area 830930944 bytes
    Fixed Size 2257800 bytes
    Variable Size 545262712 bytes
    Database Buffers 281018368 bytes
    Redo Buffers 2392064 bytes
    SQL> create spfile from pfile=’/home/oracle/pfile001’;
    SQL> shu immediate;startup nomount;

    1.8 备库连接到主库
    [oracle@dg1 dbs]$ rman target sys/xxxxx@ecms auxiliary sys/xxxxx@ecmsdg1

    Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 6 10:15:18 2019

    Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    connected to target database: ECMS (DBID=4086521191)
    connected to auxiliary database: ECMS (not mounted)

    RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;

    1.9 添加standby log
    alter database add standby logfile ‘/u01/app/oracle/oradata/ecms/standby01.log’ size 50m;

    二、 搭建二级备库

    2.1 级联备库(与创建一级备库方法步骤一致)
    级联主库修改dg1
    alter system set log_archive_config=‘dg_config=(ecms,ecmsdg1,ecmsdg2)’;
    alter system set log_archive_dest_3=‘service=ecmsdg2 async valid_for=(standby_logfile,standby_role) db_unique_name=ecmsdg2’;

    2.2 级联备库修改dg2,复制监听文件,密码文件,参数文件
    Pfile修改
    [oracle@dg2 ~]$ cat pfile002
    ecms.__db_cache_size=281018368
    ecms.__java_pool_size=20971520
    ecms.__large_pool_size=4194304
    ecms.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
    ecms.__pga_aggregate_target=289406976
    ecms.__sga_target=545259520
    ecms.__shared_io_pool_size=0
    ecms.__shared_pool_size=222298112
    ecms.__streams_pool_size=8388608
    *.audit_file_dest=’/u01/app/oracle/admin/ecms/adump’
    *.audit_trail=‘db’
    *.compatible=‘11.2.0.4.0’
    *.control_files=’/u01/app/oracle/oradata/ecms/control01.ctl’
    .db_block_size=8192
    #
    .db_create_file_dest=’/u01/app/oracle/oradata/ecms/’
    *.db_domain=’’
    *.db_name=‘ecms’
    *.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’
    *.db_recovery_file_dest_size=4385144832
    *.db_unique_name=‘ecmsdg2’
    *.diagnostic_dest=’/u01/app/oracle’
    *.dispatchers=’(PROTOCOL=TCP) (SERVICE=ecmsXDB)’
    *.fal_client=‘ECMSDG2’
    *.fal_server=‘ECMSDG1’
    *.java_jit_enabled=TRUE
    *.java_pool_size=0
    *.large_pool_size=4194304
    *.log_archive_config=‘dg_config=(ecmsdg1,ecmsdg2)’
    *.log_archive_dest_2=‘service=ecmsdg1 async valid_for=(online_logfile,primary_role) db_unique_name=ecmsdg1’
    *.memory_target=831520768
    *.open_cursors=3500
    *.processes=150
    *.remote_login_passwordfile=‘EXCLUSIVE’
    *.service_names=‘ecms,tt’
    *.standby_file_management=‘AUTO’
    *.undo_tablespace=‘UNDOTBS1’
    备注:db_create_file_dest 权重过高,即使主备目录一致,也会按此参数设置,比较坑还要后期处理rename比较麻烦所以注释掉。

    2.3 创建目录
    /u01/app/oracle/oradata/ecms/
    /u01/app/oracle/admin/ecms/adump
    /u01/app/oracle/fast_recovery_area

    2.4 rman在线创建二级备库
    [oracle@dg1 ~]$ rman target sys/xxxxxx@ecmsdg1 auxiliary sys/xxxxxx@ecmsdg2

    Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 6 11:21:42 2019

    Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    connected to target database: ECMS (DBID=4086521191)
    connected to auxiliary database: ECMS (not mounted)

    RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;

    三、 主库与一级备库切换
    切换前状态
    A:主库B:一级备库 C:二级备库
    切换后状态
    A:一级备库 B:主库 C:一级备库

    3.1 状态检查
    SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;

    SYS@ecms>ALTER SYSTEM SWITCH LOGFILE;
    System altered.
    Elapsed: 00:00:00.02
    SYS@ecms>/

    3.2 主备切换
    SYS@ecms>alter database commit to switchover to physical standby WITH SESSION SHUTDOWN;
    Database altered.
    Elapsed: 00:00:07.97

    3.3 主库日志
    Thu Jun 06 12:13:15 2019
    alter database commit to switchover to physical standby WITH SESSION SHUTDOWN
    ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 6364] (ecms)
    Waiting for all non-current ORLs to be archived…
    All non-current ORLs have been archived.
    Waiting for all FAL entries to be archived…
    All FAL entries have been archived.
    Waiting for potential Physical Standby switchover target to become synchronized…
    Active, synchronized Physical Standby switchover target has been identified
    Switchover End-Of-Redo Log thread 1 sequence 395 has been fixed
    Switchover: Primary highest seen SCN set to 0x0.0x25636b
    ARCH: Noswitch archival of thread 1, sequence 395
    ARCH: End-Of-Redo Branch archival of thread 1 sequence 395
    ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
    ARCH: Standby redo logfile selected for thread 1 sequence 395 for destination LOG_ARCHIVE_DEST_2
    Archived Log entry 46 added for thread 1 sequence 395 ID 0xf6371a63 dest 1:
    ARCH: Archiving is disabled due to current logfile archival
    Primary will check for some target standby to have received alls redo
    Final check for a synchronized target standby. Check will be made once.
    LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
    Active, synchronized target has been identified
    Target has also received all redo
    Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/ecms/ecms/trace/ecms_ora_6364.trc
    Clearing standby activation ID 4130806371 (0xf6371a63)
    The primary database controlfile was created using the
    ‘MAXLOGFILES 21’ clause.
    There is space for up to 18 standby redo logfiles
    Use the following SQL commands on the standby database to create
    standby redo logfiles that match the primary database:
    ALTER DATABASE ADD STANDBY LOGFILE ‘srl1.f’ SIZE 52428800;
    ALTER DATABASE ADD STANDBY LOGFILE ‘srl2.f’ SIZE 52428800;
    ALTER DATABASE ADD STANDBY LOGFILE ‘srl3.f’ SIZE 52428800;
    ALTER DATABASE ADD STANDBY LOGFILE ‘srl4.f’ SIZE 52428800;
    Archivelog for thread 1 sequence 395 required for standby recovery
    Switchover: Primary controlfile converted to standby controlfile succesfully.
    Switchover: Complete - Database shutdown required
    USER (ospid: 6364): terminating the instance
    Instance terminated by USER, pid = 6364
    Completed: alter database commit to switchover to physical standby WITH SESSION SHUTDOWN
    Shutting down instance (abort)
    License high water mark = 6
    Thu Jun 06 12:13:20 2019
    Instance shutdown complete

    3.4 一级备库日志
    Thu Jun 06 12:13:18 2019
    RFS[19]: Assigned to RFS process 1994
    RFS[19]: Selected log 4 for thread 1 sequence 395 dbid -208446105 branch 982149581
    Thu Jun 06 12:13:19 2019
    Resetting standby activation ID 4130806371 (0xf6371a63)
    Thu Jun 06 12:13:19 2019
    Archived Log entry 31 added for thread 1 sequence 395 ID 0xf6371a63 dest 1:
    Media Recovery Waiting for thread 1 sequence 396
    Thu Jun 06 12:13:19 2019
    ARC3: Standby redo logfile selected for thread 1 sequence 395 for destination LOG_ARCHIVE_DEST_3
    Thu Jun 06 12:13:19 2019
    RFS[16]: Possible network disconnect with primary database
    Thu Jun 06 12:13:19 2019
    RFS[20]: Assigned to RFS process 1992
    RFS[20]: Possible network disconnect with primary database
    Thu Jun 06 12:13:19 2019
    RFS[18]: Possible network disconnect with primary database

    3.5 二级备库日志
    Thu Jun 06 11:53:02 2019
    RFS[8]: Assigned to RFS process 1431
    RFS[8]: Selected log 4 for thread 1 sequence 390 dbid -208446105 branch 982149581
    Thu Jun 06 11:53:02 2019
    Archived Log entry 8 added for thread 1 sequence 390 ID 0xf48ae18e dest 1:
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_390_ghk3jy46_.arc
    Media Recovery Waiting for thread 1 sequence 391
    Thu Jun 06 11:53:05 2019
    RFS[9]: Assigned to RFS process 1433
    RFS[9]: Selected log 4 for thread 1 sequence 391 dbid -208446105 branch 982149581
    Thu Jun 06 11:53:05 2019
    Archived Log entry 9 added for thread 1 sequence 391 ID 0xf48ae18e dest 1:
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_391_ghk3k1ff_.arc
    Media Recovery Waiting for thread 1 sequence 392
    Thu Jun 06 11:54:46 2019
    RFS[10]: Assigned to RFS process 1440
    RFS[10]: Selected log 4 for thread 1 sequence 392 dbid -208446105 branch 982149581
    Thu Jun 06 11:54:46 2019
    Archived Log entry 10 added for thread 1 sequence 392 ID 0xf48ae18e dest 1:
    Thu Jun 06 11:54:47 2019
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_392_ghk3n69t_.arc
    Identified End-Of-Redo (switchover) for thread 1 sequence 392 at SCN 0x0.2512c7
    Resetting standby activation ID 4102742414 (0xf48ae18e)
    Media Recovery End-Of-Redo indicator encountered
    Media Recovery Continuing
    Media Recovery Waiting for thread 1 sequence 393

    3.6 备库状态检查
    SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;
    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecmsdg1 PHYSICAL STANDBY MAXIMUM PERFORMANCE
    TO PRIMARY READ ONLY WITH APPLY

    级联备库状态检查(也可以切成主库)
    SQL> SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;

    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecmsdg2 PHYSICAL STANDBY MAXIMUM PERFORMANCE
    TO PRIMARY READ ONLY WITH APPLY
    SQL>

    3.7 原主库再切换成主库
    切换前状态:
    A:主库 B:一级备库C:二级备库
    切换后状态:
    A:主库 B:一级备库C:二级备库

    Thu Jun 06 12:03:43 2019
    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
    ALTER DATABASE SWITCHOVER TO PRIMARY (ecms)
    Maximum wait for role transition is 15 minutes.
    All dispatchers and shared servers shutdown
    CLOSE: killing server sessions.
    CLOSE: all sessions shutdown successfully.
    Thu Jun 06 12:03:45 2019
    SMON: disabling cache recovery
    Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/ecms/ecms/trace/ecms_ora_6164.trc
    SwitchOver after complete recovery through change 2429639
    Online logfile pre-clearing operation disabled by switchover
    Thu Jun 06 12:04:51 2019
    idle dispatcher ‘D000’ terminated, pid = (17, 1)

    Thu Jun 06 12:05:39 2019
    Standby became primary SCN: 2429637
    AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
    Switchover: Complete - Database mounted as primary
    Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY

    ##原主库又切回主库,级联备库状态变成NOT ALLOWED
    SQL> /

    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecmsdg2 PHYSICAL STANDBY MAXIMUM PERFORMANCE
    NOT ALLOWED READ ONLY WITH APPLY

    SQL> show parameter config

    NAME TYPE VALUE


    dg_broker_config_file1 string /u01/app/oracle/11.2.4/db_home
    1/dbs/dr1ecmsdg2.dat
    dg_broker_config_file2 string /u01/app/oracle/11.2.4/db_home
    1/dbs/dr2ecmsdg2.dat
    log_archive_config string dg_config=(ecmsdg1,ecmsdg2)
    SQL>

    备库切换成主库
    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
    备库日志:
    Thu Jun 06 12:16:19 2019
    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
    ALTER DATABASE SWITCHOVER TO PRIMARY (ecms)
    Maximum wait for role transition is 15 minutes.
    Switchover: Media recovery is still active
    Role Change: Canceling MRP - no more redo to apply
    Thu Jun 06 12:16:21 2019
    MRP0: Background Media Recovery cancelled with status 16037
    Errors in file /u01/app/oracle/diag/rdbms/ecmsdg1/ecms/trace/ecms_mrp0_1661.trc:
    ORA-16037: user requested cancel of managed recovery operation
    Managed Standby Recovery not using Real Time Apply
    Recovery interrupted!
    MRP0: Background Media Recovery process shutdown (ecms)
    Role Change: Canceled MRP
    All dispatchers and shared servers shutdown
    CLOSE: killing server sessions.
    CLOSE: all sessions shutdown successfully.
    Thu Jun 06 12:16:22 2019
    SMON: disabling cache recovery
    Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/ecmsdg1/ecms/trace/ecms_ora_1852.trc
    SwitchOver after complete recovery through change 2450283
    Online logfile pre-clearing operation disabled by switchover
    Online log /u01/app/oracle/oradata/ecms/ECMSDG1/onlinelog/o1_mf_1_ghjyzfwk_.log: Thread 1 Group 1 was previously cleared
    Online log /u01/app/oracle/fast_recovery_area/ECMSDG1/onlinelog/o1_mf_1_ghjyzfy5_.log: Thread 1 Group 1 was previously cleared
    Online log /u01/app/oracle/oradata/ecms/ECMSDG1/onlinelog/o1_mf_2_ghjyzk7o_.log: Thread 1 Group 2 was previously cleared
    Online log /u01/app/oracle/fast_recovery_area/ECMSDG1/onlinelog/o1_mf_2_ghjyzk98_.log: Thread 1 Group 2 was previously cleared
    Online log /u01/app/oracle/oradata/ecms/ECMSDG1/onlinelog/o1_mf_3_ghjyznhk_.log: Thread 1 Group 3 was previously cleared
    Online log /u01/app/oracle/fast_recovery_area/ECMSDG1/onlinelog/o1_mf_3_ghjyznk2_.log: Thread 1 Group 3 was previously cleared
    Standby became primary SCN: 2450281
    AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
    Switchover: Complete - Database mounted as primary
    Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY

    再转换为备库;
    alter database commit to switchover to physical standby WITH SESSION SHUTDOWN;
    日志输出
    Thu Jun 06 12:32:02 2019
    alter database commit to switchover to physical standby WITH SESSION SHUTDOWN
    ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 2055] (ecms)
    Waiting for all non-current ORLs to be archived…
    All non-current ORLs have been archived.
    Waiting for all FAL entries to be archived…
    All FAL entries have been archived.
    Waiting for potential Physical Standby switchover target to become synchronized…
    Active, synchronized Physical Standby switchover target has been identified
    Switchover End-Of-Redo Log thread 1 sequence 406 has been fixed
    Switchover: Primary highest seen SCN set to 0x0.0x25b724
    ARCH: Noswitch archival of thread 1, sequence 406
    ARCH: End-Of-Redo Branch archival of thread 1 sequence 406
    ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
    ARCH: Standby redo logfile selected for thread 1 sequence 406 for destination LOG_ARCHIVE_DEST_2
    Archived Log entry 53 added for thread 1 sequence 406 ID 0xf636bca0 dest 1:
    ARCH: Archiving is disabled due to current logfile archival
    Primary will check for some target standby to have received alls redo
    Final check for a synchronized target standby. Check will be made once.
    LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
    Active, synchronized target has been identified
    Target has also received all redo
    Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/ecmsdg1/ecms/trace/ecms_ora_2055.trc
    Clearing standby activation ID 4130782368 (0xf636bca0)
    The primary database controlfile was created using the
    ‘MAXLOGFILES 21’ clause.
    There is space for up to 18 standby redo logfiles
    Use the following SQL commands on the standby database to create
    standby redo logfiles that match the primary database:
    ALTER DATABASE ADD STANDBY LOGFILE ‘srl1.f’ SIZE 52428800;
    ALTER DATABASE ADD STANDBY LOGFILE ‘srl2.f’ SIZE 52428800;
    ALTER DATABASE ADD STANDBY LOGFILE ‘srl3.f’ SIZE 52428800;
    ALTER DATABASE ADD STANDBY LOGFILE ‘srl4.f’ SIZE 52428800;
    Archivelog for thread 1 sequence 406 required for standby recovery
    Switchover: Primary controlfile converted to standby controlfile succesfully.
    Switchover: Complete - Database shutdown required
    USER (ospid: 2055): terminating the instance
    Instance terminated by USER, pid = 2055
    Completed: alter database commit to switchover to physical standby WITH SESSION SHUTDOWN
    Shutting down instance (abort)

    归档存在gap,在级联备库中等待片刻便会同步。
    Thu Jun 06 12:33:33 2019
    RFS[24]: Assigned to RFS process 1581
    RFS[24]: Opened log for thread 1 sequence 406 dbid -208446105 branch 982149581
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_398_ghk5wwfn_.arc
    Archived Log entry 24 added for thread 1 sequence 406 rlc 982149581 ID 0xf636bca0 dest 3:
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_399_ghk5wwjh_.arc
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_400_ghk5wwlk_.arc
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_401_ghk5wwnm_.arc
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_402_ghk5wwop_.arc
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_403_ghk5wwp7_.arc
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_404_ghk5wwtv_.arc
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_405_ghk5wwz6_.arc
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ECMSDG2/archivelog/2019_06_06/o1_mf_1_406_ghk5wx24_.arc
    Identified End-Of-Redo (switchover) for thread 1 sequence 406 at SCN 0x0.25b724
    Resetting standby activation ID 4130782368 (0xf636bca0)
    Media Recovery End-Of-Redo indicator encountered
    Media Recovery Continuing
    Media Recovery Waiting for thread 1 sequence 407

    四、 一级备库与二级备库切换
    切换前状态
    A:一级备库 B:主库(指定了2个dest) C:二级备库
    切换后状态
    A:二级备库 B:一级备库 C:主库
    4.1 状态检查
    SQL> SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;

    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecmsdg2 PHYSICAL STANDBY MAXIMUM PERFORMANCE
    TO PRIMARY READ ONLY WITH APPLY

    4.2 一级备库切换成主库
    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

    4.3 二级备库日志
    Thu Jun 06 12:37:34 2019
    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
    ALTER DATABASE SWITCHOVER TO PRIMARY (ecms)
    Maximum wait for role transition is 15 minutes.
    Switchover: Media recovery is still active
    Role Change: Canceling MRP - no more redo to apply
    Thu Jun 06 12:37:36 2019
    MRP0: Background Media Recovery cancelled with status 16037
    Errors in file /u01/app/oracle/diag/rdbms/ecmsdg2/ecms/trace/ecms_mrp0_1553.trc:
    ORA-16037: user requested cancel of managed recovery operation
    Managed Standby Recovery not using Real Time Apply
    Recovery interrupted!
    MRP0: Background Media Recovery process shutdown (ecms)
    Role Change: Canceled MRP
    Killing 3 processes with pids 1600,1579,1581 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 1483
    All dispatchers and shared servers shutdown
    CLOSE: killing server sessions.
    CLOSE: all sessions shutdown successfully.
    Thu Jun 06 12:37:38 2019
    SMON: disabling cache recovery
    Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/ecmsdg2/ecms/trace/ecms_ora_1483.trc
    SwitchOver after complete recovery through change 2471716
    Online logfile pre-clearing operation disabled by switchover
    Online log /u01/app/oracle/oradata/ecms/ECMSDG2/onlinelog/o1_mf_1_ghk2opl7_.log: Thread 1 Group 1 was previously cleared
    Online log /u01/app/oracle/fast_recovery_area/ECMSDG2/onlinelog/o1_mf_1_ghk2opnn_.log: Thread 1 Group 1 was previously cleared
    Online log /u01/app/oracle/oradata/ecms/ECMSDG2/onlinelog/o1_mf_2_ghk2osvy_.log: Thread 1 Group 2 was previously cleared
    Online log /u01/app/oracle/fast_recovery_area/ECMSDG2/onlinelog/o1_mf_2_ghk2osxj_.log: Thread 1 Group 2 was previously cleared
    Online log /u01/app/oracle/oradata/ecms/ECMSDG2/onlinelog/o1_mf_3_ghk2oxgq_.log: Thread 1 Group 3 was previously cleared
    Online log /u01/app/oracle/fast_recovery_area/ECMSDG2/onlinelog/o1_mf_3_ghk2oxj8_.log: Thread 1 Group 3 was previously cleared
    Standby became primary SCN: 2471714
    AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
    Switchover: Complete - Database mounted as primary
    Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
    4.4 状态确认
    SQL> /

    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecmsdg2 PRIMARY MAXIMUM PERFORMANCE
    SESSIONS ACTIVE READ WRITE

    五、 主库与二级备库切换
    切换前状态:
    A:主库 B:一级备库 C:二级备库
    切换后状态:
    A:二级备库 B:一级备库 C:主库
    5.1 切换准备
    SYS@ecms>SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;

    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecms PRIMARY MAXIMUM PERFORMANCE
    TO STANDBY READ WRITE

    5.2 主库切换成备库
    SYS@ecms>alter database commit to switchover to physical standby WITH SESSION SHUTDOWN;
    Database altered.
    Elapsed: 00:00:05.71
    5.3 二级备库状态检查
    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecmsdg2 PHYSICAL STANDBY MAXIMUM PERFORMANCE
    TO PRIMARY READ ONLY WITH APPLY
    5.4 二级备库切换成主库

    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

    Database altered.
    5.5 二级备库切换成主库日志输出
    Thu Jun 06 14:19:53 2019
    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
    ALTER DATABASE SWITCHOVER TO PRIMARY (ecms)
    Maximum wait for role transition is 15 minutes.
    Switchover: Media recovery is still active
    Role Change: Canceling MRP - no more redo to apply
    Thu Jun 06 14:19:55 2019
    MRP0: Background Media Recovery cancelled with status 16037
    Errors in file /u01/app/oracle/diag/rdbms/ecmsdg2/ecms/trace/ecms_mrp0_2204.trc:
    ORA-16037: user requested cancel of managed recovery operation
    Recovery interrupted!
    MRP0: Background Media Recovery process shutdown (ecms)
    Role Change: Canceled MRP
    Killing 3 processes with pids 2375,2369,2373 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 2196
    All dispatchers and shared servers shutdown
    CLOSE: killing server sessions.
    CLOSE: all sessions shutdown successfully.
    Thu Jun 06 14:19:59 2019
    SMON: disabling cache recovery
    Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/ecmsdg2/ecms/trace/ecms_ora_2196.trc
    SwitchOver after complete recovery through change 2560371
    Online logfile pre-clearing operation disabled by switchover
    Online log /u01/app/oracle/oradata/ecms/ECMSDG2/onlinelog/o1_mf_1_ghk2opl7_.log: Thread 1 Group 1 was previously cleared
    Online log /u01/app/oracle/fast_recovery_area/ECMSDG2/onlinelog/o1_mf_1_ghk2opnn_.log: Thread 1 Group 1 was previously cleared
    Online log /u01/app/oracle/oradata/ecms/ECMSDG2/onlinelog/o1_mf_2_ghk2osvy_.log: Thread 1 Group 2 was previously cleared
    Online log /u01/app/oracle/fast_recovery_area/ECMSDG2/onlinelog/o1_mf_2_ghk2osxj_.log: Thread 1 Group 2 was previously cleared
    Online log /u01/app/oracle/oradata/ecms/ECMSDG2/onlinelog/o1_mf_3_ghk2oxgq_.log: Thread 1 Group 3 was previously cleared
    Online log /u01/app/oracle/fast_recovery_area/ECMSDG2/onlinelog/o1_mf_3_ghk2oxj8_.log: Thread 1 Group 3 was previously cleared
    Standby became primary SCN: 2560369
    AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
    Switchover: Complete - Database mounted as primary
    Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
    Thu Jun 06 14:20:05 2019
    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
    ALTER DATABASE SWITCHOVER TO PRIMARY (ecms)
    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (ecms)
    ORA-1109 signalled during: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY…
    Thu Jun 06 14:20:06 2019
    RFS[11]: Assigned to RFS process 2381
    RFS[11]: Database mount ID mismatch [0xf63740e4:0xf6372ed7] (4130816228:4130811607)
    RFS[11]: Client instance is standby database instead of primary
    RFS[11]: Not using real application clusters
    Shutting down instance (immediate)
    Shutting down instance: further logons disabled
    Stopping background process MMNL
    Thu Jun 06 14:20:16 2019
    Stopping background process MMON
    Thu Jun 06 14:20:17 2019
    idle dispatcher ‘D000’ terminated, pid = (17, 1)
    failed to start dispatcher for network ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’
    License high water mark = 4
    All dispatchers and shared servers shutdown
    ALTER DATABASE CLOSE NORMAL
    ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL…
    ALTER DATABASE DISMOUNT
    Shutting down archive processes
    Archiving is disabled
    Thu Jun 06 14:20:17 2019
    ARCH shutting down
    ARC3: Archival stopped
    Thu Jun 06 14:20:17 2019
    ARCH shutting down
    ARC1: Archival stopped
    Thu Jun 06 14:20:17 2019
    ARCH shutting down
    ARC0: Archival stopped
    Thu Jun 06 14:20:17 2019
    ARCH shutting down
    ARC2: Relinquishing active heartbeat ARCH role
    ARC2: Archival stopped
    Completed: ALTER DATABASE DISMOUNT
    ARCH: Archival disabled due to shutdown: 1089
    Shutting down archive processes
    Archiving is disabled
    Thu Jun 06 14:20:18 2019
    Stopping background process VKTM
    ARCH: Archival disabled due to shutdown: 1089
    Shutting down archive processes
    Archiving is disabled
    Thu Jun 06 14:20:21 2019
    Instance shutdown complete
    5.6 状态确认
    主库
    SQL> SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;
    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecmsdg2 PRIMARY MAXIMUM PERFORMANCE
    TO STANDBY READ WRITE

    一级备库

    SQL> SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;
    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecmsdg1 PHYSICAL STANDBY MAXIMUM PERFORMANCE
    NOT ALLOWED READ ONLY WITH APPLY

    二级备库(原主库)

    SYS@ecms> SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;
    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecms PHYSICAL STANDBY MAXIMUM PERFORMANCE
    NOT ALLOWED READ ONLY WITH APPLY
    Elapsed: 00:00:00.10
    SYS@ecms>

    六、 完成切换
    6.1 当前状态
    A:原主库 B:一级备库 C:二级备库
    状态检查
    A:状态(未配置dest_3所以一直是TO PRIMARY)
    SYS@ecms> SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;

    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecms PHYSICAL STANDBY MAXIMUM PERFORMANCE
    TO PRIMARY READ ONLY WITH APPLY

    B:状态
    SQL> SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;

    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecmsdg1 PHYSICAL STANDBY MAXIMUM PERFORMANCE
    NOT ALLOWED READ ONLY WITH APPLY

    SQL>
    C:状态
    SQL> SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;

    NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE


    SWITCHOVER_STATUS OPEN_MODE


    ECMS ecmsdg2 PHYSICAL STANDBY MAXIMUM PERFORMANCE
    NOT ALLOWED READ ONLY WITH APPLY

    6.2 同步状态
    一级备库(原主库)
    SYS@ecms>/
    PROCESS STATUS SEQUENCE#


    ARCH CONNECTED 0
    ARCH CONNECTED 0
    ARCH CLOSING 430
    ARCH OPENING 430
    RFS IDLE 0
    RFS IDLE 0
    RFS IDLE 0
    MRP0 WAIT_FOR_LOG 431

    一级备库
    SQL> /

    PROCESS STATUS SEQUENCE#


    ARCH CLOSING 429
    ARCH CLOSING 429
    ARCH CLOSING 426
    ARCH CLOSING 430
    RFS IDLE 0
    RFS IDLE 431
    RFS IDLE 0
    RFS IDLE 0
    RFS IDLE 0
    MRP0 APPLYING_LOG 431
    RFS IDLE 0

    主库(原二级备库)
    SQL> select max(sequence#),thread# from v$archived_log group by thread#;

    MAX(SEQUENCE#) THREAD#


           430          1
    

    6.3 参数配置
    在这里插入图片描述

    展开全文
  • 【DG】Oracle级联DG--(cascade dg) --(一主一备一级联) 【DG】Oracle级联DG--(cascade dg) --(一主一备一级联)--Cascaded Standby dataguard一主一备一级联,意思是主库将日志传输给备库,然后备库在将日志...

     

    【DG】Oracle之级联DG--(cascade dg) --(一主一备一级联)

    【DG】Oracle之级联DG--(cascade dg) --(一主一备一级联)--Cascaded Standby

     

    dataguard一主一备一级联,意思是主库将日志传输给备库,然后备库在将日志传输给级联库,主库和级联库其实没有任何关系。另外,在Oracle 11g中,关于数据同步问题,主库上的操作一般情况下是可以实时同步到备库的,但是级联库必须等备库归档时,才能同步。如果主库切换日志,那么这时级联库也能及时同步。

    Oracle 11g的级联备库是不支持实时应用的,要等源库日志切换后才会应用。Oracle 12c的级联备库支持实时应用。

     

     

    在11.2及以上版本支持级联备库,就是第二备库从第一个备库接受redo日志,而不是直接从主库接受redo日志。

    这样会减少主库的压力。实际上和正常搭建DG没什么区别,只是改一下参数即可。

    最多支持30个级联备库,因为LOG_ARCHIVE_DEST_n,只有31个。

     

    更多详细信息,参考官方文档:http://docs.oracle.com/database/121/SBYDB/log_transport.htm#SBYDB5126

     

    Oracle 级联DG部署以及切换测试:

    https://blog.csdn.net/weixin_36239782/article/details/91316703

     

     

    1 说明

    A standby database that cascades redo to other standby databases can transmit redo directly from its standby redo log file as soon as it is received from the primary database. Cascaded standby databases receive redo in real-time. They no longer have to wait for standby redo log files to be archived before redo is transmitted.

     

    启用real-time redo,不需要等归档standby redo日志文件,然后再传输到级联备库上。

     

    As of Oracle Database 12c Release 1 (12.1), a cascading standby database can either cascade redo in real-time (as it is being written to the standby redo log file) or non-real-time (as complete standby redo log files are being archived on the cascading standby).

     

    从12c开始,支持real-time级联redo(等写入备库redo log)。

     

    限制:

     

    Only physical standby databases can cascade redo.

     

    Real-time cascading requires a license for the Oracle Active Data Guard option.

     

    Non-real-time cascading is supported on destinations 1 through 10 only. (Real-time cascading is supported on all destinations.)

     

    If you specify ASYNC transport mode on destinations 1 through 10, then redo is shipped in real-time. If you do not specify a transport mode or you specify SYNC on destinations 1 through 10, then redo is shipped in non-real-time. Destinations 11 through 31 operate only in ASYNC (real-time) transport mode.

     

    在用于级联的备库中的LOG_ARCHIVE_DEST_n(1…10)指定ASYNC,则是real-time。如果不指定,或者指定SYNC,则是non-real-time。

     

    LOG_ARCHIVE_DEST_n(11…31)只支持ASYNC,即real-time传输模式。

     

    oracle 12c 支持实时级联同步了,很11g不支持

    There are new Possibilities for cascading Standby Databases in Oracle 12c. The main Differents between Oracle 12c and the previous Releases are:

    1.        Real-Time Cascading
    2.        Far Sync Standby Database
    3.        Data Guard Broker now supports cascaded Standby Database
     

     



     

    12c 的 Cascaded Standby 数据库 (文档 ID 2179701.1)

     

    适用于:

    Oracle Database - Enterprise Edition - 版本 12.1.0.1 和更高版本
    Oracle Database Cloud Schema Service - 版本 N/A 和更高版本
    Oracle Database Exadata Cloud Machine - 版本 N/A 和更高版本
    Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本
    Oracle Database Cloud Exadata Service - 版本 N/A 和更高版本
    本文档所含信息适用于所有平台
    ***Checked for relevance on 13-Jul-2015***

    用途

     这篇文档解释了 Cascaded Standby 在 oracle 12c 上的增强特性。

    详细信息

     

    Oracle 12c 的 cascading standby 数据库为用户增加了更多的选项。12c 的版本对比以前的版本增加了以下的新选项:

     

    1. 实时 Cascading
    2. Far Sync Standby 数据库
    3. Data Guard Broker 提供对 cascading standby 数据库的支持

     

    但是,你还只能从配置 physical standby 数据库去 cascade 另一个 standby 数据库。 目前 logical standby 数据库还不支持 cascade 另一个 standby 数据库。

     

     

    实时 Cascading:

     

    新版本现在支持以实时的模式将 redo 从第一个 standby 数据库传递到 cascaded standby 数据库。因此在第一个 standby 数据库,Redo 的信息会在被写到 Standby Redolog 后立即传递到 cascaded standby 数据库。

    而非实时 Cascading 意味着:只有主库的 log Switch 之后,整个 log sequence 才会被传递到最终的 Standby 数据库上。

     

     

    先决条件:

     

    • 第一个(Cascading)standby 数据库必须是物理的或者是 Far Sync Standby 数据库
    • 必须保证至少在 Cascading standby 数据库上使用 Standby Redolog
    • Active Data guard 的选项必须是有 license 的
    • Primary,Cascading,Cascaded standby 数据库的 db_unique_name 必须体现在所有数据库 log_archive_config 的 dg_config 中

     

     

    设置:

     

    首先 ,创建一个通常的 Dataguard 环境到 cascading standby 数据库。Log 的传输模式应该为 SYNC,同时在 cascading standby 配置 Standby Redolog。在创建完 cascaded standby 数据库以后就可以设置 cascading log 的传输服务了,下面是一些注意事项:

     

    • Primary,Cascading,Cascaded standby 数据库的 db_unique_name 必须体现在所有数据库 log_archive_config 的 dg_config 中。
    • 在 Cascading standby 数据库的 log_archive_dest_n 里面设置 ‘valid_for=(STANDBY_LOGFILES,STANDBY_ROLE)’ 的属性来传输给 cascaded(最终)standby 数据库。
    • 你可以通过设置 Log Transport 的模式来切换实时以及非实时的 cascading 模式:

    ASYNC = Real-Time Cascading

    SYNC = Non Real-Time Cascading

    • 你只可以设置从 log_archive_dest_1 到 log_archive_dest_10 作为非实时模式的目的地,而你可以在 cascading standby 数据库上设置所有的 log_archive_dest_n 作为实时 cascading 的目的地。
    • Cascading Standby 数据库可以运行在任何保护模式下。
    • Cascading Standby 数据库可以传输给一个或者多的 terminal standby 数据库。
    • Cascading Standby 数据库的 FAL_SERVER 应该设置为 primary 库或者是其他的 primary 直接连接的 standby 数据库。
    • Terminal Standby 数据库的 FAL_SERVER应该设置 cascading Standby 数据库或者 Primary 数据库。

     

     

    Far Sync Standby 数据库:

     

    Far Sync Standby 数据库对于 Terminal standby 数据库来说是作为一个 RedoLog repository 数据库的作用。他不含有任何的数据文件。Far Sync Standby 数据库只是启动了 Log 传输服务。Far Sync Standby 数据库的优点是它可以作为 Primary 数据库的一个在最大保护模式下的本地的 ArchiveLog Repository,而 Physical 和 logical standby 数据库可以运行在远端,请参考文档:

    Note 1565071.1: Data Guard 12c New Feature: Far Sync Standby

    来了解具体的关于 Far Sync Standby 数据库内容以及设置的步骤。

     

     

     

    Data Guard Broker 和 Cascaded Standby 数据库:

     

    Data Guard Broker 有一个新的‘RedoRoutes’的属性可以用来构建和部署 cascaded Data Guard Broker 的配置。 以下是它的格式:

     

    RedoRoutes = ‘(<Redo Source> : <Redo Destination>)’

     

    Redo Source: Redo 的来源,他可以是 db_unique_name 或者是一个本地数据库名别名的 ‘LOCAL’-Keyword(不能被 Far Sync Standby 数据库使用)

    Redo Destination: Redo 从这个数据库传输到的目的地。他可以是一个或者多个(用逗号分开)db_unique_name 或者是代表所有在 Data Guard Broker 配置中可能目的地的别名的‘ALL’-Keyword。 你可以设置到目的地的传输的模式。包括以下:

    • SYNC:                 等同于 log_archive_dest_n 中的属性 ‘SYNC AFFIRM’ 或者是非实时 Cascade
    • ASYNC:               等同于 log_archive_dest_n 中的属性 ‘ASYNC’ 或者是 实时 Cascade
    • FASTSYNC :         等同于 log_archive_dest_n 中的属性 ‘SYNC NOAFFIRM’

     

     

    例子:

     

    Primary Database:                               prim

    Cascading Standby Database:               local_stdby

    Cascaded (terminal) Standby Database: remote_stdby

    如果想实现’SYNC NOAFFIRM’的本地 standby 数据库和在实时 cascade 模式下的远程 standby 数据库,设置如下:

     

    Primary Database (prim)

    RedoRoutes = ‘(LOCAL : local_stdby FASTSYNC)’

     -> Primary 数据库只传送 Redo 到 local Standby 数据库,但是有到远程 standby 数据库的 Archive 目的地。

     

    Local Standby Database (local_stdby)

    RedoRoutes = ‘(prim : remote_stdby ASYNC)’

     -> 这里需要配置来源于 ‘prim’的 REDO 是以实时 cascade(ASYNC)的方式转发到远程的 Standby 数据库。

     

    参考

    NOTE:1565071.1 - Data Guard 12c New Feature: Far Sync Standby

     

    Cascaded Standby Databases in Oracle 12c (文档 ID 1542969.1)

     

    In this Document

     Purpose
     Details
     Real-Time Cascading
     Prerequisites
     Setup
     Far Sync Standby Database
     Data Guard Broker and Cascaded Standby Database
     References

     

    APPLIES TO:

    Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
    Information in this document applies to any platform.
    ***Checked for relevance on 13-Jul-2015***

    PURPOSE

     This Documents explains the Enhancements for Cascaded Standby Databases in Oracle 12c.

    DETAILS

     

    There are new Possibilities for cascading Standby Databases in Oracle 12c. The main Differents between Oracle 12c and the previous Releases are:

     

    1. Real-Time Cascading
    2. Far Sync Standby Database
    3. Data Guard Broker now supports cascaded Standby Database

     

    However, you can still only cascade a Standby Database from a Physical Standby Database. It is not supported to cascade a Standby Database from a Logical Standby Database.

     

     

    Real-Time Cascading

     

    It is now possible to forward Redo in Real-Time Mode from the first to the cascaded Standby Database. So the Redo Record is forwarded to the cascaded Standby Database once written into a Standby RedoLog of the first Standby Database.

    Non Real-Time Cascading means that the whole Log Sequence is transferred to the terminal Standby Database(s) after a Log Switch on the Primary Database.

     

     

    Prerequisites

     

    • First (Cascading) Standby must be a Physical or Far Sync Standby Database
    • Standby RedoLogs must be in Place and used at least on the Cascading Standby Database
    • Active Data Guard Option must be licensed
    • Primary, Cascading and Cascaded Standby Database db_unique_name must be present in the dg_config of log_archive_config on all the Databases

     

     

    Setup

     

    First of all setup a Data Guard Environment as usual to the cascading Standby Database. The Log Transport Method should be ‘SYNC’ and Standby RedoLogs must be configured on the cascading Standby Database. Once you created the cascaded Standby Database you can now setup the cascading Log Transport Services. Here are some Hints for correct Setup:

     

    • Primary, Cascading and Cascaded Standby Database db_unique_name must be present in the dg_config of log_archive_config on all the Databases
    • Setup log_archive_dest_n on the cascading Standby Database to serve the cascaded (terminal) Standby Databases using the Attribute ‘valid_for=(STANDBY_LOGFILES,STANDBY_ROLE)’
    • You can toggle between Real-Time and Non Real-Time Cascading using the Log Transport Method.

    ASYNC = Real-Time Cascading

    SYNC = Non Real-Time Cascading

    • You can only use log_archive_dest_1 until log_archive_dest_10 for Non Real-Time Cascading Destinations where all log_archive_dest_n’s can be used for Real-Time Cascading on the Cascading Standby Database
    • The Cascading Standby Standby can be in any Protection Mode
    • A Cascading Standby Database can serve one or multiple terminal Standby Databases
    • FAL_SERVER on the cascading Standby Database should be set to the Primary or any other Standby Database served by the Primary Database directly
    • FAL_SERVER on the terminal Standby Database should be set to the cascading Standby Database or the Primary Database

     

     

    Far Sync Standby Database

     

    A Far Sync Standby Database is a cascading Standby Database which acts as a Redo Log Repository for a Terminal Database. It does not contain any Datafiles. Only Log Transport Services are active on a Far Sync Standby Database. The Advantage of a Far Sync Standby Database is that it can be a local ArchiveLog Repository for the Primary Database acting in Maximum Protection Mode where the Physical or Logical Standby Database can be on a far remote Site. See

    Note 1565071.1: Data Guard 12c New Feature: Far Sync Standby

    for further Details and Setup of a Far Sync Standby Database.

     

     

     

    Data Guard Broker and Cascaded Standby Database

     

    There is a new Data Guard Broker Property called ‘RedoRoutes’ used to build and implement a cascaded Data Guard Broker Configuration. It has the following Format:

     

    RedoRoutes = ‘(<Redo Source> : <Redo Destination>)’

     

    Redo Source: This is the Source the Redo is coming from. It can be a db_unique_name or the ‘LOCAL’-Keyword which is an Alias for the local Database Name (Cannot be used for a Far Sync Standby Database)

    Redo Destination: This is the Destination where the Redo is shipped to from this Database. It can be one or more (comma separated) db_unique_name’s or the ‘ALL’-Keyword which is an Alias for all possible Destinations inside the Data Guard Broker Configuration. Optional you can also specify the Transport Method to be used to the Destination. This can be

    • SYNC:                   corresponds to log_archive_dest_n Attributes ‘SYNC AFFIRM’ or Non Real Time Cascade
    • ASYNC:                corresponds to log_archive_dest_n Attribute ‘ASYNC’ or Real Time Cascade
    • FASTSYNC :        corresponds to log_archive_dest_n Attributes ‘SYNC NOAFFIRM’

     

     

    Example:

     

    Primary Database:                               prim

    Cascading Standby Database:               local_stdby

    Cascaded (terminal) Standby Database: remote_stdby

    We want to serve the local Standby Database with ‘SYNC NOAFFIRM’ and the remote Standby Database in Real-Time Cascade Mode. So the Setting would be:

     

    Primary Database (prim)

    RedoRoutes = ‘(LOCAL : local_stdby FASTSYNC)’

     -> So the Primary Database only ships Redo to the local Standby Database, but has Archive Destination to the remote Standby Database

     

    Local Standby Database (local_stdby)

    RedoRoutes = ‘(prim : remote_stdby ASYNC)’

     -> Here we configure that the Redo coming from ‘prim’ is forwarded in Real-Time Cascade (ASYNC) to the remote Standby Database

     

     

    REFERENCES

    NOTE:1565071.1 - Data Guard 12c New Feature: Far Sync Standby

     

     

    FAL_SERVER And FAL_CLIENT Settings For Cascaded Standby (文档 ID 358767.1)

     

    In this Document

     Goal
     Solution
     References

     

    This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

    APPLIES TO:

    Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.4 [Release 9.2 to 11.2]
    Information in this document applies to any platform.
    ***Checked for relevance on 27-Sep-2012***
    ***Checked for relevance on 10-Dec-2015*** 

    GOAL

    How to configure the FAL_CLIENT and FAL_SERVER parameters in cascaded standby setup.

    FAL_SERVERspecifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.

    FAL_CLIENTspecifies the FAL (fetch archive log) client name that is used by the FAL service, configured through theFAL_SERVERparameter, to refer to the FAL client. The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database). Given the dependency ofFAL_CLIENTonFAL_SERVER, the two parameters should be configured or changed at the same time.

    You can read about the cascaded redo log solution in

    Note 409013.1: Cascaded Standby Databases in Oracle 10g/11g

    SOLUTION

    For simplification the following 3 service names are assumed, and assume all these 3 service names are available at all the 3 sites tnsnames.ora file (primary,cascaded standby and remote standby) in the same form.

     dg_prim -> primary database
     dg_standby_cas -> cascaded standby database
     dg_standby_rem -> the remote standby database.

    Assuming the above configuration the parameter needs to be the following:
    At primary:

    fal_server = ' '
    fal_client = ' '
    

    Primary will never will have gap, so no need for any fal* parameter here.

    At cascaded standby

    fal_server = 'dg_prim'
    fal_client = 'dg_standby_cas'
    

    Cascaded when has gap, can only get the archive logs from the primary 
    database. Hence the fal_server parameter. It wants the primary to send the FAL 
    request response to 'dg_standby_cas', hence fal_client setting.

    At remote standby database:

    fal_server = 'dg_standby_cas','dg_prim'
    fal_client = 'dg_standby_rem'
    

    Remote standby when has gap, can get the archive logs from the primary 
    database or cascaded standby database. Hence the fal_server parameter. It wants 
    the primary to send the FAL request response to 'dg_standby_rem', hence 
    fal_client setting.

    Note: If primary receives a FAL request from the remote standby in the above case then It ships the archive logs directly to the remote standby without going via cascaded standby. FAL_CLIENT is obsolete in Oracle 11.2.0 and is not required any more


    REFERENCES

    NOTE:1537316.1 - Data Guard Gap Detection and Resolution Possibilities
    NOTE:409013.1 - Cascaded Standby Databases in Oracle 10g/11g

     

     

    Cascaded Standby Databases in Oracle 10g/11g (文档 ID 409013.1)

     

     

    APPLIES TO:

    Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.1 [Release 9.0.1 to 11.2]
    Information in this document applies to any platform.
    ***Checked for relevance on 02-OCT-2014***
    ** checked for relevance '23-Nov-2015' **


     

    GOAL

    The information below replaces Appendix E Cascaded Destinations of Oracle Data Guard Concepts and Administration 10g Release 2 (10.2) part number B14239.
     

    This information also applies to Oracle10g Release 1 and Oracle9i releases.


    For information on Cascaded Destinations in Data Guard 11g Release 1, please see Appendix E here

    For 11g Release 2, see Chapter 6 Redo Transport Services :- http://docs.oracle.com/cd/E11882_01/server.112/e41134/log_transport.htm#SBYDB00400

    Please note that as of Version 11.2.0.2 many of the restrictions with cascaded standby databases have been lifted.  Please refer to the documentation link above for up to date information in 11.2.0.2 and cascaded standby databases.

    SOLUTION

    Summary: 

    The significant changes from the previous Oracle Database 10g Release 2 documentation include:

    1. Cascading logical standby databases from a logical standby database is not supported.
    2. Cascading standby databases (logical or physical) from a primary database that is part of an Oracle Real Application Cluster (RAC) is not supported (This restriction has been lifted in 11.2.0.2)
    3. Using Cascaded standby databases in a Data Guard Broker environment is not supported.


    Details: 

    To reduce the load on your primary system, or to reduce the bandwidth requirements imposed when your standbys are separated from the primary database through a Wide Area Network (WAN), you can implement cascaded destinations, whereby a standby database receives its redo data from another standby database, instead of directly from the primary database. 

    In a Data Guard configuration using a cascaded destination, a physical standby database can forward the redo data it receives from the primary database to another standby database. Only a physical standby database can be configured to forward redo data to another standby database. A logical standby database cannot forward redo to another standby database.

    You cannot set up a physical standby to forward redo if the primary database is part of an Oracle Real Application Cluster (RAC) (lifted as of 11.2.0.2)  or part of a Data Guard Broker environment.


    The following Data Guard configurations using cascaded destinations are supported.

    1. Primary Database > Physical Standby Database with cascaded destination > Physical Standby Database
    2. Primary Database > Physical Standby Database with cascaded destination > Logical Standby Database

    While a logical standby database cannot forward redo to another standby database, it can be configured to have its own physical standby database. In such a case, the physical standby database is not considered a Cascaded Destination because it does not receive redo that is forwarded from the primary database. Instead, it is receiving redo generated by the logical standby.  However this can only be used for Rolling Upgrades since a failover to the Logical standby database's Physical standby would not result in a new Logical Standby.  Instead it would become another Primary and no longer be part of the original Data Guard configuration.



    A physical standby database can support a maximum of nine (30 as of Version 11.2) remote destinations. When a cascaded destination is defined on a physical standby database, the physical standby will forward redo it receives from the primary to a second standby database after its standby redo log becomes full and is archived. Thus, the second standby database receiving the forwarded redo as a result of a cascaded destination will necessarily lag behind the primary database. 

    Oracle recommends that cascaded destinations be used only for offloading reporting or for applications that do not require access to data that is completely up-to-date with the primary system. This is because the very nature of a cascaded destination means that the standby database that is the end-point will be one or more log files behind the primary database. Oracle also recommends that standby databases whose primary role is to be involved in role transitions receive their redo data directly from the primary database. 

    The remainder of this note contains information about the following:

    • Configuring a cascaded destination
    • Role transitions in the presence of a cascaded destination
    • Examples of cascaded destinations
    1. Configuring a Cascaded Destination 

      To enable a physical standby database to forward incoming redo data to a cascaded destination perform the following steps:
      • Create standby redo log files on the physical standby database (if not already created).
        • If standby redo log files are not already defined, you can define them dynamically on the standby database. The standby database will begin using them after the next log switch on the primary database.
      • Define a LOG_ARCHIVE_DEST_n initialization parameter on the primary database to set up a physical standby database that will forward redo to a cascaded destination. 
        Define the destination to use:
        • LGWR ASYNC or
        • LGWR SYNC
        Optionally, set the VALID_FOR attribute so that redo forwarding is enabled even after a role transition happens between the original primary database and the intermediate standby database that is forwarding redo. This may be meaningful in cases where the databases are separated over Wide Area Networks.
      • Ensure that archiving is enabled on the physical standby database where the cascaded destinations are defined (the standby database that will forward redo).
      • Configure a LOG_ARCHIVE_DEST_n parameter (on the physical standby that will forward redo data) for each cascaded destination.

      Below are the initialization parameters for a primary database named Boston, which sends redo to a physical standby database named Chicago, that forwards the redo it receives to a cascaded standby database named Denver. In this example, the database named Denver is a logical standby database, but note that a physical standby database can forward redo to either a physical or a logical standby database. 
       

      When the cascaded destination is a logical standby database, remember that you will create it just as if the logical standby will be directly connected to the primary database (see Chapter 4 Creating a Logical Standby Database of Oracle Data Guard Concepts and Administration 10g Release 2).

       

      Boston Database (Primary Role)

      DB_UNIQUE_NAME=boston
      STANDBY_ARCHIVE_DEST=/arch1/boston/
      REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
      LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston,denver)'
      LOG_ARCHIVE_DEST_1='LOCATION=/arch1/boston/ VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston'
      LOG_ARCHIVE_DEST_2= 'SERVICE=denver VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=denver'
      LOG_ARCHIVE_DEST_3='SERVICE=chicago VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago'


      Chicago Database (Standby Role)

      DB_UNIQUE_NAME=chicago
      LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston,denver)'
      STANDBY_ARCHIVE_DEST=/arch1/chicago/
      REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
      LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago'
      LOG_ARCHIVE_DEST_2='SERVICE=denver VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=denver'
      LOG_ARCHIVE_DEST_3='SERVICE=boston VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston'


      Denver Database (Standby Role)

      DB_UNIQUE_NAME=denver
      LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston,denver)'
      STANDBY_ARCHIVE_DEST=/arch2/denver/
      REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
      LOG_ARCHIVE_DEST_1='LOCATION=/arch1/denver/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=denver'
      LOG_ARCHIVE_DEST_2='LOCATION=/arch2/denver/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=denver'

       

      In the example parameters above for the standby database Denver, STANDBY_ARCHIVE_DEST is set to /arch2/denver/ because this is a logical standby database. If Denver were a physical standby database, it would not be necessary to change STANDBY_ARCHIVE_DEST - it would match LOG_ARCHIVE_DEST_1. 

      Both the Boston primary database and the Chicago physical standby database define the LOG_ARCHIVE_DEST_2 initialization parameter as 'SERVICE=denver VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE). Hence, even if the Boston and Chicago databases switch roles, the redo data will continue to be forwarded to the Denver database. Remember, as part of the original setup of the physical standby database, you should define a local destination as VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE), that will be used for local archiving when the physical standby database transitions to the primary role.

    2. Role Transitions with Cascaded Destinations 

      Oracle recommends that standby databases primarily intended for disaster recovery purposes receive redo data directly from the primary database.  This will result in the optimum level of data protection. A cascaded destination may be used as a second line of defense, but by definition it will always be further behind than a standby database that is receiving redo directly from the primary.
       
    3. Examples of using Cascaded Destinations 

      The following scenarios demonstrate configuration options and uses for cascaded destinations. 

      Scenario 1: Physical Standby Forwarding Redo to a Remote Physical Standby 

      You have a primary database in your corporate offices and you want to create a standby database at another facility within your metropolitan area to provide zero data loss protection should there be a failure at your primary site. In addition to the local standby, you wish to maintain a geographically remote standby database 2,000 miles away at a disaster recovery site. A small amount of data loss is acceptable should failover to the remote standby be required (an acceptable trade-off in return for the extra protection against events that can impact a large geographic area and cause both the primary site and the local standby database to fail). The remote standby database also provides continuous data protection after a failover to the local standby database and improves security by enabling backups to be created and stored at the remote location, eliminating the need to ship tapes off-site. 

      While you could configure your primary database to ship redo directly to both standby databases, you may want to eliminate the potential overhead of the primary database shipping redo over a WAN to the second standby database. You solve this problem by creating the first physical standby in a local facility within your metropolitan area using the SYNC network transport to achieve zero data loss protection. A cascaded destination is defined on the local physical standby database that will forward redo received from the primary to the remote standby database using ASYNC network transport. Because the local standby manages all communication with the remote standby via a cascaded destination, there is no impact to the primary database to maintain a second standby. 

      Scenario 2: Physical Standby Forwarding Redo to a Logical Standby 

      You have a primary database in a city in the United States and you wish to deploy three complete replicas of this database to be used for end-user query and reporting in three different manufacturing plants in Europe. Your objective is to eliminate the need for users and applications at your European locations to access data that resides in the US to prevent network disruptions from making data unavailable for local access. While you can accept some latency between the time an update is made in the primary and the time it is replicated to all three European sites, you desire the data to be as up-to-date as possible and available to query and to run reports. You require a solution that is completely application transparent, and one where additional replicas can be deployed to sites in Europe if the need arises. A final requirement is the need to make this work with the limited bandwidth and very high network latency of the network connection between your US and European facilities. 

      You address your requirements by first creating a physical standby database in Europe for the primary database located in the US. You then create three logical standby databases, one in each of your European plants, and define each logical standby as a cascaded destination on your physical standby database.  One copy of the redo is shipped over the transatlantic link from the US to the physical standby in Europe. The physical standby in Europe forwards the redo to the three logical standby databases in the Europe manufacturing plants providing local access to corporate data for end-user query and reports. Room for future growth is built in - additional standby databases can be deployed in Europe without any modification to applications, without any additional overhead on your primary system, and without consuming any additional transatlantic bandwidth.

      Configure the physical standby database to forward redo data to the logical standby databases in each of your manufacturing sites as in the example above. The only difference from the example parameters, above, is that you will define two additional LOG_ARCHIVE_DEST_n parameters on the physical standby so that redo will be forwarded to all three logical standby databases.

    REFERENCES

    NOTE:1542969.1 - Cascaded Standby Databases in Oracle 12c


     

    Data Guard broker considerations for cascaded standby databases in 11.2 (文档 ID 2220933.1)

     

     

    In this Document

     Goal
     Solution
     Creating the configuration:
     Performing Role Transitions Using Data Guard Broker

     

    APPLIES TO:

    Oracle Database - Enterprise Edition - Version 11.2.0.2 to 11.2.0.4 [Release 11.2]
    Information in this document applies to any platform.

    GOAL

     To reduce the load on your primary system, or to reduce the bandwidth requirements imposed when your standbys are separated from the primary database through a Wide Area Network (WAN), you can implement cascaded destinations, whereby a standby database receives its redo data from another standby database, instead of directly from the primary database. In a Data Guard configuration using a cascaded destination, a physical standby database can forward the redo data it receives from the primary database to another standby database. Only a physical standby database can be configured to forward redo data to another standby database. Neither a logical standby database or a snapshot standby database can forward redo to another standby database.

    In 11.2 when a cascaded destination is defined on a physical standby database, the physical standby will forward redo it receives from the primary to a second standby database after its standby redo log becomes full and is archived. Thus, the second standby database receiving the forwarded redo as a result of a cascaded destination will necessarily lag behind the primary database. In addition, there is no Data Guard broker support for handling cascaded destinations. In 12c it is now possible to cascade a Standby Database in Real-Time, ie. the first Standby Database can send Redo from the Standby RedoLogs to the cascaded Standby Database. Also, the Data Guard Broker now supports cascaded standby databases using the RedoRoutes database configuration property. For complete information on 12c Data Guard broker RedoRoutes property and cascaded standby databases refer to the following MAA whitepaper:

    http://www.oracle.com/technetwork/database/availability/broker-12c-transport-config-2082184.pdf

    While the 11.2 Data Guard does not support cascaded standby databases it is still possible to utilize the broker with some additional manual configuration. The following procedure describes the additional considerations for an 11.2 broker configuration with cascaded standby databases.

    SOLUTION

     Consider the following starting configuration:

     

    When a switchover or failover occurs between A and C the following is the desired configuration:

     

     

    Creating the configuration:

    1. Create a broker configuration that includes databases A, B, and C. In 11.2 the broker does not support cascaded standby (D) so it will be handled manually.

    create configuration ‘orcl’ as
    primary database is ‘A’
    connect identifier is A;

    add database ‘B’ as
    connect identifier is B;

    add database ‘C’ as
    connect identifier is C;

    enable configuration;

    2. Manually configure cascaded redo shipping from database C to database D. On database C:

    alter system set log_archive_config='DG_CONFIG=(A,B,C,D)' scope=both;
    alter system set log_archive_dest_5='service=D valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=A' scope=both;

    3. On database D, configure the log_archive_config parameter to accept redo from C. Also, configure the fal_server parameter to point to database C:

    alter system set log_archive_config='DG_CONFIG=(A,B,C,D)' scope=both;
    alter system set fal_server=’C’ scope=both;

    4. On database A configure a log_archive_dest_n parameter that will be used to cascade redo to B when A is operating in the standby role:

    alter system set log_archive_dest_5='service=B valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=B' scope=both;

     

    Performing Role Transitions Using Data Guard Broker

    1. Prior to performing the role transition between A and C, remove database B from the broker configuration. This will prevent the broker from configuring the new primary C to ship redo to database B.

    DGMGRL> remove database B;

    2. Perform the role transition using the Data Guard broker.

    DGMGRL> switchover to C;

    or

    DGMGRL> failover to C;

    3. After the role transition to C, add in database D to the broker configuration.

    add database ‘D’ as
    connect identifier is D;

    4. Database A should automatically begin shipping redo to B using the destination defined in the previous steps. You should adjust log_archive_config accordingly:

    alter system set log_archive_config='DG_CONFIG=(A,B,C,D)' scope=both;


     

     



     

     

    2.1 准备工作

    和正常搭建DG一样,安装数据库软件,创建相应的目录,拷贝参数文件,密码文件等。我这里演示的是,添加第三个级联备库过程。

     

    2.2 主库修改参数

    SQL> Alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(cndba_p,cndba_s,cndba_ss)' scope=both;

     

    2.3 第一备库修改参数

    SQL> Alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(cndba_p,cndba_s,cndba_ss)' scope=both;

    SQL> alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=cndba_ss VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=cndba_ss' scope=spfile;

     

    2.4 第二备库修改参数

    *.DB_UNIQUE_NAME=cndba_ss

    *.FAL_SERVER=cndba_s

    *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(cndba_p,cndba_s,cndba_ss)'

    *.LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cndba_ss'

     

    2.5 主备库创建TNSNAME

     

    CNDBA_SS =

      (DESCRIPTION =

        (ADDRESS_LIST =

          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.173)(PORT = 1521))

        )

        (CONNECT_DATA =

          (SERVICE_NAME = cndba)

        )

      )

      

    [oracle@12cdg-p ~]$ tnsping cndba_ss

    TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-AUG-2017 17:36:54

    Copyright (c) 1997, 2014, Oracle.  All rights reserved.

    Used parameter files:

    /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.173)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cndba)))

    OK (0 msec)

     

    2.6 将第二备库启动到nomount

    SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcndba.ora';

    ORACLE instance started.

     

    Total System Global Area 2348810240 bytes

    Fixed Size    2927048 bytes

    Variable Size 1409287736 bytes

    Database Buffers  922746880 bytes

    Redo Buffers   13848576 bytes

     

    2.7 开始DUPLICATE

    注意:还是主库和第二备库的DUPLICATE

    [oracle@12cdg-p ~]$ rman target [email protected]_p auxiliary [email protected]_ss
    Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 16 17:38:28 2017
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
    connected to target database: CNDBA (DBID=216462194)
    connected to auxiliary database: CNDBA (not mounted)
    RMAN> duplicate target database for standby from active database nofilenamecheck;
    Starting Duplicate Db at 16-AUG-17
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=23 device type=DISK
     
    contents of Memory Script:
    {
       backup as copy reuse
       targetfile  '/u01/app/oracle/product/12.1.0.2/db_1/dbs/orapwcndba' auxiliary format
     '/u01/app/oracle/product/12.1.0.2/db_1/dbs/orapwcndba'   ;
    }
    executing Memory Script
     
    Starting backup at 16-AUG-17
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=1 device type=DISK
    Finished backup at 16-AUG-17
     
    contents of Memory Script:
    {
       restore clone from service  'cndba_p' standby controlfile;
    }
    executing Memory Script
     
    Starting restore at 16-AUG-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 cndba_p
    channel ORA_AUX_DISK_1: restoring control file
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
    output file name=/u01/app/oracle/oradata/cndba/control01.ctl
    output file name=/u01/app/oracle/fast_recovery_area/cndba/control02.ctl
    Finished restore at 16-AUG-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
     "/u01/app/oracle/oradata/cndba/temp01.dbf";
       set newname for tempfile  2 to
     "/u01/app/oracle/oradata/cndba/pdbseed/pdbseed_temp012017-08-14_12-17-51-PM.dbf";
       set newname for tempfile  3 to
     "/u01/app/oracle/oradata/cndba/sihong/temp012017-08-14_12-17-51-PM.dbf";
       switch clone tempfile all;
       set newname for datafile  1 to
     "/u01/app/oracle/oradata/cndba/system01.dbf";
       set newname for datafile  3 to
     "/u01/app/oracle/oradata/cndba/sysaux01.dbf";
       set newname for datafile  4 to
     "/u01/app/oracle/oradata/cndba/undotbs01.dbf";
       set newname for datafile  5 to
     "/u01/app/oracle/oradata/cndba/pdbseed/system01.dbf";
       set newname for datafile  6 to
     "/u01/app/oracle/oradata/cndba/users01.dbf";
       set newname for datafile  7 to
     "/u01/app/oracle/oradata/cndba/pdbseed/sysaux01.dbf";
       set newname for datafile  8 to
     "/u01/app/oracle/oradata/cndba/sihong/system01.dbf";
       set newname for datafile  9 to
     "/u01/app/oracle/oradata/cndba/sihong/sysaux01.dbf";
       set newname for datafile  10 to
     "/u01/app/oracle/oradata/cndba/sihong/sihong_users01.dbf";
       restore
       from service  'cndba_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 /u01/app/oracle/oradata/cndba/temp01.dbf in control file
    renamed tempfile 2 to /u01/app/oracle/oradata/cndba/pdbseed/pdbseed_temp012017-08-14_12-17-51-PM.dbf in control file
    renamed tempfile 3 to /u01/app/oracle/oradata/cndba/sihong/temp012017-08-14_12-17-51-PM.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 16-AUG-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 cndba_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/cndba/system01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service cndba_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/cndba/sysaux01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service cndba_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/cndba/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 cndba_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/cndba/pdbseed/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 cndba_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/cndba/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 cndba_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/cndba/pdbseed/sysaux01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service cndba_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/cndba/sihong/system01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service cndba_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/cndba/sihong/sysaux01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service cndba_p
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/cndba/sihong/sihong_users01.dbf
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    Finished restore at 16-AUG-17
    sql statement: alter system archive log current
    contents of Memory Script:
    {
       switch clone datafile all;
    }
    executing Memory Script
     
    datafile 1 switched to datafile copy
    input datafile copy RECID=3 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/system01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=4 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/sysaux01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=5 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/undotbs01.dbf
    datafile 5 switched to datafile copy
    input datafile copy RECID=6 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/pdbseed/system01.dbf
    datafile 6 switched to datafile copy
    input datafile copy RECID=7 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/users01.dbf
    datafile 7 switched to datafile copy
    input datafile copy RECID=8 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/pdbseed/sysaux01.dbf
    datafile 8 switched to datafile copy
    input datafile copy RECID=9 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/sihong/system01.dbf
    datafile 9 switched to datafile copy
    input datafile copy RECID=10 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/sihong/sysaux01.dbf
    datafile 10 switched to datafile copy
    input datafile copy RECID=11 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/sihong/sihong_users01.dbf
    Finished Duplicate Db at 16-AUG-17
    2.8 打开第二备库并启用MRP
    SQL> alter database open;
    Database altered.
    SQL> alter database recover managed standby database disconnect;
    Database altered.
    –查看MRP进程
    SQL> select process,status from v$managed_standby;
    PROCESS   STATUS
    --------- ------------
    ARCH  CLOSING
    ARCH  CLOSING
    ARCH  CONNECTED
    ARCH  CLOSING
    RFS  IDLE
    RFS  IDLE
    RFS  IDLE
    MRP0  WAIT_FOR_LOG
    8 rows selected.
    –数据库状态
    SQL> select database_role,open_mode from v$database; 
    DATABASE_ROLE OPEN_MODE
    ---------------- --------------------
    PHYSICAL STANDBY READ ONLY WITH APPLY
    2.9 查看日志序列号
    主库:
    SQL> select max(sequence#) from v$archived_log;
    MAX(SEQUENCE#)
    --------------
        46
    第一备库:
    SQL> select max(sequence#) from v$archived_log;
    MAX(SEQUENCE#)
    --------------
        46
    第二备库:
    SQL> select max(sequence#) from v$archived_log;
    MAX(SEQUENCE#)
    --------------
        46
    2.9.1 主库切换日志
    SQL> alter system switch logfile;
    System altered.
    –再查看日志序列号,全部都为47
    SQL> select max(sequence#) from v$archived_log;
    MAX(SEQUENCE#)
    --------------
        47
    </span>

     

    2 实验

    搭建级联备库参考:https://blog.csdn.net/qianglei6077/article/details/90736799

     

    2.1 查看当前DG配置

    SQL> select * from V$DATAGUARD_CONFIG;

    DB_UNIQUE_NAME       PARENT_DBUN      DEST_ROLE CURRENT_SCNCON_ID

    ------------------------------ ------------------------------ ----------------- ----------- ----------

    cndba_p        NONE      PRIMARY DATABASE    2122746     0

    cndba_s        cndba_p      PHYSICAL STANDBY    2122754     0

    cndba_ss       cndba_s      PHYSICAL STANDBY    2112269     0

     

    2.2 查看用于级联的备库参数–启用real-time redo

    SQL> show parameter LOG_ARCHIVE_DEST_2

    NAME     TYPE VALUE

    ------------------------------------ ----------- ------------------------------

    log_archive_dest_2     string SERVICE=cndba_ss ASYNC NOAFFIRM VALID_FOR=(ST

                                              ANDBY_LOGFILES,STANDBY_ROLE) D

                                              B_UNIQUE_NAME=cndba_ss

     

    可以看到启用real-time redo cascade。

     

    2.2.1 主库创建表,查看日志序列号

    SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)

    -------------

        51

     

    –创建表,并插入数据

     

    SQL> create table cndba(id number);

    Table created.

     

    SQL> insert into cndba select object_id from dba_objects;

    90947 rows created.

     

    SQL> commit;

    Commit complete.

     

    SQL> select count(*) from cndba;

      COUNT(*)

    ----------

         90947

     

    –可以看到日志没有发生切换

     

    SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)

    --------------

        51

     

    2.2.2 查看用于级联(Cascading )备库表

    SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)

    --------------

        51 --日志序列号没有变化,表示没有发生日志切换

     

    SQL>  select count(*) from cndba;

      COUNT(*)

    ----------

         90947  --由于DG默认启用实时redo应用,所以Cascading备库数据实时传输过来,下面注意是验证cascaded数据是否传输过来。

     

    2.2.3 查看级联(cascaded)的备库表

    SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)

    --------------

        51   --同样日志序列号没有变化。

     

    SQL> select count(*) from cndba;

      COUNT(*)

    ----------

         90947  --数据已经传输过来了,符合预期。

     

    从日志中也可以查看出来:

    Recovery of Online Redo Log: Thread 1 Group 4 Seq 51 Reading mem 0

    Mem# 0: /u01/app/oracle/fast_recovery_area/CNDBA_SS/onlinelog/o1_mf_4_ds84tg8t_.log

     

    2.3 修改用于级联备库(Cascading )的参数-启用non-real-time

    SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=cndba_ss SYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=cndba_ss' scope=both;

    System altered.

     

    SQL> show parameter LOG_ARCHIVE_DEST_2

    NAME     TYPE VALUE

    ------------------------------------ ----------- ------------------------------

    log_archive_dest_2     string SERVICE=cndba_ss SYNC VALID_FO

                                             R=(STANDBY_LOGFILES,STANDBY_RO

                                            LE) DB_UNIQUE_NAME=cndba_ss

     

    2.3.1 主库插入数据

    SQL> insert into cndba select object_id from dba_objects;

    90947 rows created.

     

    SQL> commit;

    Commit complete.

     

    SQL> select count(*) from cndba;

      COUNT(*)

    ----------

        181894

     

    SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)

    --------------

        51  

     

    2.3.2 查看用于级联(Cascading )备库表\

    SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)

    --------------

        51

     

    SQL> select count(*) from cndba;

      COUNT(*)

    ----------

        181894

     

    2.3.3 查看级联(cascaded)的备库表

    SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)

    --------------

        51

     

    SQL> select count(*) from cndba;

      COUNT(*)

    ----------

    181894  --可以看到数据没有同步过来。

     

    从日志也可以看出来:当前日志时51,等52日志来进程恢复。

     

    Media Recovery Waiting for thread 1 sequence 52

     

    至此对于Real-time redo的介绍已经结束了。该特性还是非常有用的,对于数据容灾更加可靠。

     


     

     



    About Me

    ........................................................................................................................

    ● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除

    ● 本文在itpub、博客园、CSDN和个人微 信公众号(xiaomaimiaolhr)上有同步更新

    ● 本文itpub地址:http://blog.itpub.net/26736162

    ● 本文博客园地址:http://www.cnblogs.com/lhrbest

    ● 本文CSDN地址:https://blog.csdn.net/lihuarongaini

    ● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

    ● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

    ● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

    ........................................................................................................................

    ● QQ群号:230161599、618766405

    ● 微 信群:可加我微 信,我拉大家进群,非诚勿扰

    ● 联系我请加QQ好友(646634621),注明添加缘由

    ● 于 2019-09-01 06:00 ~ 2019-09-31 24:00 在西安完成

    ● 最新修改时间:2019-09-01 06:00 ~ 2019-09-31 24:00

    ● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

    ● 版权所有,欢迎分享本文,转载请保留出处

    ........................................................................................................................

    ● 小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

    ● 小麦苗出版的数据库类丛书http://blog.itpub.net/26736162/viewspace-2142121/

    ● 小麦苗OCP、OCM、高可用网络班http://blog.itpub.net/26736162/viewspace-2148098/

    ● 小麦苗腾讯课堂主页https://lhr.ke.qq.com/

    ........................................................................................................................

    使用微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号(xiaomaimiaolhr)及QQ群(DBA宝典)、添加小麦苗微 信,学习最实用的数据库技术。

     

    ........................................................................................................................

      

     

     

     

     

    展开全文
  • Oracle 18c使用dbca创建级联DG 更多参考: http://blog.itpub.net/26736162/viewspace-2656076/ -------...

    Oracle 18c使用dbca创建级联DG


    更多参考: http://blog.itpub.net/26736162/viewspace-2656076/

    -------------------- dbca搭建级联备库
    --------配置tns
    CDBLHR18cdg2 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = CDBLHR18cdg2)
        )
      )  
    --------配置监听
     
        (SID_DESC =
          (GLOBAL_DBNAME = CDBLHR18cdg2)
          (ORACLE_HOME = /u08/app/oracle/product/18.0.0/dbhome_1)
          (SID_NAME = CDBLHR18cdg2)
        )
        (SID_DESC =
          (GLOBAL_DBNAME = CDBLHR18cdg2_DGMGRL)
          (ORACLE_HOME = /u08/app/oracle/product/18.0.0/dbhome_1)
          (SID_NAME = CDBLHR18cdg2)
        )
      
    --搭建过程是:备库和第2备库的操作
    dbca -silent -createDuplicateDB \
    -gdbName CDBLHR18cdg2 \
    -sid CDBLHR18cdg2 \
    -sysPassword lhr \
    -primaryDBConnectionString 192.168.59.130:1521/CDBLHR18cdg \
    -nodelist rhel6lhr \
    -databaseConfigType SINGLE \
    -createAsStandby -dbUniqueName CDBLHR18cdg2 \
    -datafileDestination '/u01/app/oracle/oradata/CDBLHR18cdg2/' \
    -initParams db_create_file_dest=/u01/app/oracle/oradata/CDBLHR18cdg2/,db_create_online_log_dest_1=/u01/app/oracle/oradata/CDBLHR18cdg2/,sga_target=800M,memory_max_target=0,memory_target=0
    ---全参数 rac环境
    set line 1000
    set pagesize 1000
    col name format a25
    col VALUE format a100
    SELECT a.NAME,
           i.instance_name,
           a.VALUE
    FROM   gv$parameter a, gv$instance i
    WHERE  a.inst_id = i.inst_id and a.name in ('dg_broker_start','db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_3','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3','log_archive_max_processes','remote_login_passwordfile','db_file_name_convert','log_file_name_convert','standby_file_management','fal_server','fal_client','dg_broker_config_file1','dg_broker_config_file2')
    ORDER BY a.name, i.instance_name;
    --主库修改参数
    Alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';
    alter system set LOG_ARCHIVE_DEST_3= 'SERVICE=CDBLHR18cdg2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CDBLHR18cdg2';
    --一级备库修改参数
    alter system set log_archive_config='dg_config=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';
    alter system set LOG_ARCHIVE_DEST_3= 'SERVICE=CDBLHR18cdg2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CDBLHR18cdg2';
    -- 二级备库修改参数
    alter system set log_archive_config='dg_config=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';
    alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDBLHR18cdg2';
    alter system set db_file_name_convert='/u01/app/oracle/oradata/CDBLHR18cdg/','/u01/app/oracle/oradata/CDBLHR18cdg2/' scope=spfile;
    alter system set log_file_name_convert='/u01/app/oracle/oradata/CDBLHR18cdg/','/u01/app/oracle/oradata/CDBLHR18cdg2/' scope=spfile;
    alter system set fal_client='CDBLHR18cdg2'; 
    alter system set fal_server='CDBLHR18cdg';
      
    shutdown immediate
    startup
    alter system register;
      
      
    --备库查询实时应用
    alter database recover managed standby database cancel;
    ALTER DATABASE flashback on;
    alter database recover managed standby database using current logfile disconnect;
    ! ps -ef|grep ora_mrp
      
      
    COL NAME FOR A100
    SET LINESIZE 9999  PAGESIZE 9999
    COL NEXT_CHANGE# FOR 999999999999999
    SELECT THREAD#, NAME, SEQUENCE#, ARCHIVED, APPLIED, A.NEXT_CHANGE#
      FROM V$ARCHIVED_LOG A
     WHERE A.SEQUENCE# >= (SELECT MAX(B.SEQUENCE#) - 3
                             FROM V$ARCHIVED_LOG B
                            WHERE B.THREAD# = A.THREAD#
                              AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#
                              AND B.RESETLOGS_CHANGE# =
                                  (SELECT D.RESETLOGS_CHANGE# FROM V$DATABASE D)
                              AND B.APPLIED = 'YES'  
    			  AND B.DEST_ID=A.DEST_ID
                              GROUP BY B.THREAD#)       
      AND A.STANDBY_DEST='NO'
     ORDER BY A.THREAD#, A.SEQUENCE#;
    alter system set dg_broker_start=true sid='*';
    dgmgrl sys/lhr@CDBLHR18cdg
    show configuration
    add database CDBLHR18cdg2 as
    connect identifier is CDBLHR18cdg2;
    show database verbose CDBLHR18c;
    show database verbose CDBLHR18cdg;
    show database verbose CDBLHR18cdg2;
    alter database recover managed standby database cancel;
    --必须配置主库
    edit database cdblhr18c set property  RedoRoutes    ='(cdblhr18cdg:cdblhr18cdg2 ASYNC)';
    enable database CDBLHR18cdg2
    edit database CDBLHR18cdg2 set property  StaticConnectIdentifier    ='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDBLHR18cdg2)(INSTANCE_NAME=CDBLHR18cdg2)(SERVER=DEDICATED)))';


    配置完结果:

    DGMGRL> show configuration
    Configuration - cdblhr18c
      Protection Mode: MaxPerformance
      Members:
      cdblhr18c    - Primary database
        cdblhr18cdg  - Physical standby database 
        cdblhr18cdg2 - Physical standby database 
    Fast-Start Failover: DISABLED
    Configuration Status:
    SUCCESS   (status updated 49 seconds ago)
    DGMGRL>








    About Me

    ........................................................................................................................

    ● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除

    ● 本文在itpub、博客园、CSDN和个人微 信公众号( xiaomaimiaolhr)上有同步更新

    ● 本文itpub地址: http://blog.itpub.net/26736162

    ● 本文博客园地址: http://www.cnblogs.com/lhrbest

    ● 本文CSDN地址: https://blog.csdn.net/lihuarongaini

    ● 本文pdf版、个人简介及小麦苗云盘地址: http://blog.itpub.net/26736162/viewspace-1624453/

    ● 数据库笔试面试题库及解答: http://blog.itpub.net/26736162/viewspace-2134706/

    ● DBA宝典今日头条号地址: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

    ........................................................................................................................

    ● QQ群号: 230161599 、618766405

    ● 微 信群:可加我微 信,我拉大家进群,非诚勿扰

    ● 联系我请加QQ好友 646634621 ,注明添加缘由

    ● 于 2019-09-01 06:00 ~ 2019-09-31 24:00 在西安完成

    ● 最新修改时间:2019-09-01 06:00 ~ 2019-09-31 24:00

    ● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

    ● 版权所有,欢迎分享本文,转载请保留出处

    ........................................................................................................................

    小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

    小麦苗出版的数据库类丛书http://blog.itpub.net/26736162/viewspace-2142121/

    小麦苗OCP、OCM、高可用网络班http://blog.itpub.net/26736162/viewspace-2148098/

    小麦苗腾讯课堂主页https://lhr.ke.qq.com/

    ........................................................................................................................

    使用 微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号( xiaomaimiaolhr)及QQ群(DBA宝典)、添加小麦苗微 信, 学习最实用的数据库技术。

    ........................................................................................................................

    欢迎与我联系

     

     



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

    展开全文
  • 在生产中为了保证Oracle数据库数据的安全性,也为了防止不可抗拒的外在因素对数据库安全产生的影响,都会...特别地,对于一些重要数据会做多种容灾的方案保证数据的安全,本文就介绍一种容灾方案级联DG的搭建,级...

    在生产中为了保证Oracle数据库数据的安全性,也为了防止不可抗拒的外在因素对数据库安全产生的影响,都会采取一些容灾的技术。特别地,对于一些重要数据会做多种容灾的方案保证数据的安全,本文就介绍一种容灾方案级联DG的搭建,级联DG是备库的备库,也就是主库将日志传输给备库之后,备库再将日志传输给级联库。主库的操作产生的日志可以实时传输给备库,但是级联库需要备库归档时才能同步,当主库切换日志时,级联库也能及时同步。

    具体搭建步骤:

    一、 主库配置
             IP              主机名  db_name  db_unique_name
    主库       192.168.253.50      ora1      ora           ora1
    备库       192.168.253.51      ora2      ora           ora2
    级联库    192.168.253.50      ora3      ora           ora3


    1.1 开启归档、force logging
    [oracle@ora1 ~]$ sqlplus / as sysdba


    SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 24 15:39:12 2016


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


    Connected to an idle instance.


    SYS@ora>startup
    ORACLE instance started.


    Total System Global Area  830930944 bytes
    Fixed Size                  2257800 bytes
    Variable Size             536874104 bytes
    Database Buffers          285212672 bytes
    Redo Buffers                6586368 bytes
    Database mounted.
    Database opened.
    SYS@ora>alter database force logging;


    Database altered.


    SYS@ora>select force_logging from v$database;


    FOR
    ---
    YES
    1.2 增加日志组
    SYS@ora>select member from v$logfile;


    MEMBER
    --------------------------------------------------------------------------------
    /u01/app/oracle/oradata/ora/redo03.log
    /u01/app/oracle/oradata/ora/redo02.log
    /u01/app/oracle/oradata/ora/redo01.log


    SYS@ora>select bytes/1024/1024 from v$log;


    BYTES/1024/1024
    ---------------
                 50
                 50
                 50


    SYS@ora>alter database add standby logfile group 4
     ('/u01/app/oracle/oradata/ora/sredo04.log') size 50m;


    Database altered.


    SYS@ora>alter database add standby logfile group 5
     ('/u01/app/oracle/oradata/ora/sredo05.log') size 50m;


    Database altered.
    SYS@ora>alter database add standby logfile group 6
     ('/u01/app/oracle/oradata/ora/sredo06.log') size 50m;


    Database altered.


    SYS@ora>alter database add standby logfile group 7
     ('/u01/app/oracle/oradata/ora/sredo07.log') size 50m;


    Database altered.


    SYS@ora>select member from v$logfile;


    MEMBER
    --------------------------------------------------------------------------------
    /u01/app/oracle/oradata/ora/redo03.log
    /u01/app/oracle/oradata/ora/redo02.log
    /u01/app/oracle/oradata/ora/redo01.log
    /u01/app/oracle/oradata/ora/sredo04.log
    /u01/app/oracle/oradata/ora/sredo05.log
    /u01/app/oracle/oradata/ora/sredo06.log
    /u01/app/oracle/oradata/ora/sredo07.log


    7 rows selected.
    1.3 生成pfile关闭数据库
    SYS@ora>create pfile from spfile;
    File created.


    SYS@ora>shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    1.4 修改主库参数文件
    添加如下内容:
    db_unique_name=ora1
    log_archive_format=%t_%s_%r.arc
    log_archive_config='DG_CONFIG=(ora1,ora2)'
    LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/ora/ 
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora1'
    LOG_ARCHIVE_DEST_2='SERVICE=ora2 LGWR SYNC
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora2'
    log_archive_dest_state_1=ENABLE
    log_archive_dest_state_2=ENABLE
    LOG_ARCHIVE_MAX_PROCESSES=4
    ## Parameters which using for switch over from Primary to Standby.
    fal_server=ora2
    ##fal_client=SBDB
    standby_file_management=AUTO
    #db_file_name_convert='ora2','ora1'
    #log_file_name_convert='ora2','ora1'
    1.5 主库配置监听与TNS
    [oracle@ora1 admin]$ vi listener.ora 
    # listener.ora Network Configuration File:
     /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    # Generated by Oracle configuration tools.


    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = ora1)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )


    ADR_BASE_LISTENER = /u01/app/oracle


    [oracle@ora1 admin]$ vi tnsnames.ora 
    # tnsnames.ora Network Configuration File:
    /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.


    ora1 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.253.50)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ora1)
        )
      )


    ora2 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.253.51)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ora2)
        )
      )
    "tnsnames.ora" 20L, 515C written                                                                                                  
    1.6 注册监听
    [oracle@ora1 admin]$ lsnrctl status
    LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-AUG-2016 15:18:16


    Copyright (c) 1991, 2013, Oracle.  All rights reserved.


    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora1)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    Start Date                24-AUG-2016 14:31:56
    Uptime                    0 days 0 hr. 46 min. 20 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File
       /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/ora1/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    Services Summary...
    Service "ora1" has 1 instance(s).
      Instance "ora", status READY, has 1 handler(s) for this service...
    Service "oraXDB" has 1 instance(s).
      Instance "ora", status READY, has 1 handler(s) for this service...
    The command completed successfully
    二、备库配置相关参数
    2.1 修改参数
    [oracle@ora1 dbs]$ scp orapwora 
    oracle@192.168.253.51:$ORACLE_HOME/dbs/orapwora2
    [oracle@ora1 dbs]$ scp initora.ora 
    oracle@192.168.253.51:$ORACLE_HOME/dbs/initora2.ora
    [oracle@ora2 dbs]$ ls
    initora2.ora  init.ora  orapwora2
    参数文件中添加如下内容:
    db_unique_name=ora2
    log_archive_format=%t_%s_%r.arc
    log_archive_config='DG_CONFIG=(ora1,ora2)'
    LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/ora/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora2'
    LOG_ARCHIVE_DEST_2='SERVICE=ora1 LGWR SYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora1'
    log_archive_dest_state_1=ENABLE
    log_archive_dest_state_2=ENABLE
    LOG_ARCHIVE_MAX_PROCESSES=4
    ## Parameters which using for switch over from Primary to Standby.
    fal_server=ora1
    ##fal_client=SBDB
    standby_file_management=AUTO
    #db_file_name_convert='ora1','ora2'
    #log_file_name_convert='ora1','ora2'
    2.2 备库配置静态监听及TNS
    [oracle@ora2 admin]$ ls
    listener.ora  samples  shrept.lst  tnsnames.ora
    [oracle@ora2 admin]$ vi listener.ora 
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.253.51)(PORT = 1521))
        )
      )


    ADR_BASE_LISTENER = /u01/app/oracle


    SID_LIST_LISTENER =
      (sid_list=
        (sid_desc=
          (sid_name=ora2)
          (oracle_home= /u01/app/oracle/product/11.2.0/dbhome_1)
          (global_dbname=ora2)
        )
      )
    [oracle@ora2 admin]$ vi tnsnames.ora 


    ora1 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.253.50)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ora1)
        )
      )


    ora2 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.253.51)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ora2)
        )
      )
    2.3 备库启动到nomount状态
    [oracle@ora2 ~]$ sqlplus / as sysdba


    SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 24 16:23:33 2016


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


    Connected to an idle instance.


    SYS@ora2>create spfile from pfile;


    File created.


    SYS@ora2>startup nomount;
    ORACLE instance started.


    Total System Global Area  830930944 bytes
    Fixed Size                  2257800 bytes
    Variable Size             536874104 bytes
    Database Buffers          285212672 bytes
    Redo Buffers                6586368 bytes
    2.4 用RMAN恢复备库(在主库、备库中执行均可)
    [oracle@ora1 ~]$ rman target sys/oracle@ora1 auxiliary sys/oracle@ora2


    Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 24 17:35:57 2016


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


    connected to target database: ora (DBID=762083517)
    connected to auxiliary database: ora (DBID=762083517, not omounted)


    RMAN>duplicate target database for standby from active database dorecover nofilenamecheck;
    。RMAN过程略去




    三、级联库配置
    3.1 修改级联库参数
    [oracle@ora2 dbs]$ ls
    hc_ora2.dat  initora2.ora  init.ora  lkora2  orapwora2
      spfileora2.ora
    [oracle@ora2 dbs]$ scp orapwora2
     192.168.253.52:$ORACLE_HOME/dbs/orapwora3
    oracle@192.168.253.52's password: 
    orapwora2                   100% 1536     1.5KB/s   00:00    
    [oracle@ora2 dbs]$ scp initora2.ora 
    192.168.253.52:$ORACLE_HOME/dbs/initora3.ora
    oracle@192.168.253.52's password: 
    initora2.ora                  100% 1254     1.2KB/s   00:00    
    参数文件中添加如下内容:
    db_unique_name=ora3
    log_archive_format=%t_%s_%r.arc
    log_archive_config='DG_CONFIG=(ora3,ora2)'
    LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/ora/ 
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora3'
    LOG_ARCHIVE_DEST_2='SERVICE=ora2 LGWR SYNC
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora2'
    log_archive_dest_state_1=ENABLE
    log_archive_dest_state_2=ENABLE
    LOG_ARCHIVE_MAX_PROCESSES=4
    ## Parameters which using for switch over from Primary to Standby.
    fal_server=ora2
    ##fal_client=SBDB
    standby_file_management=AUTO
    #db_file_name_convert='ora1','ora2'
    #log_file_name_convert='ora1','ora2'
    3.2 创建相关目录
    [oracle@ora3 ~]$ mkdir -p /u01/app/oracle/admin/ora/adump
    [oracle@ora3 ~]$ mkdir -p /u01/app/oracle/oradata/ora/
    [oracle@ora3 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/ora
    3.3 级联库配置静态监听
    [oracle@ora3 admin]$ ls
    samples  shrept.lst  tnsnames.ora
    [oracle@ora3 admin]$ vi listener.ora


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


    ADR_BASE_LISTENER = /u01/app/oracle


    SID_LIST_LISTENER =
      (sid_list=
        (sid_desc=
          (sid_name=ora3)
          (oracle_home= /u01/app/oracle/product/11.2.0/dbhome_1)
          (global_dbname=ora3)
        )
      )


    "listener.ora" [New] 18L, 342C written                                                                                            
    [oracle@ora3 admin]$ vi tnsnames.ora 


    ora1 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.253.50)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ora1)
        )
      )


    ora2 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.253.51)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ora2)
        )
      )
    ora3 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.253.52)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ora3)
        )
      )
    3.4 级联库启动到nomount状态
    [oracle@ora3 admin]$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 24 19:10:20 2016
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    Connected to an idle instance.


    SYS@ora3>create spfile from pfile;


    File created.


    SYS@ora3>startup nomount;
    ORACLE instance started.


    Total System Global Area  830930944 bytes
    Fixed Size                  2257800 bytes
    Variable Size             536874104 bytes
    Database Buffers          285212672 bytes
    Redo Buffers                6586368 bytes
    3.5 备库中TNS添加如下内容
    ora3 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.253.52)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ora3)
        )
      )
    3.6 级联库启动监听
    [oracle@ora3 admin]$ lsnrctl start


    LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-AUG-2016 19:06:37


    Copyright (c) 1991, 2013, Oracle.  All rights reserved.


    Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...


    TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    System parameter file is
     /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    Log messages written to /u01/app/oracle/diag/tnslsnr/ora3/listener/alert/log.xml
    Listening on:
     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.253.52)(PORT=1521)))


    Connecting to
     (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.253.52)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    Start Date                24-AUG-2016 19:06:37
    Uptime                    0 days 0 hr. 0 min. 0 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File
       /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/ora3/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.253.52)(PORT=1521)))
    Services Summary...
    Service "ora3" has 1 instance(s).
      Instance "ora3", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    3.7 验证备库与级联库互通情况
    [oracle@ora3 admin]$ sqlplus sys/oracle@ora2 as sysdba


    SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 24 19:07:32 2016


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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options


    [oracle@ora2 ~]$ sqlplus sys/oracle@ora3 as sysdba


    SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 24 19:15:35 2016


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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    3.8 备库在mount状态下修改参数
    SYS@ora2>show parameter log_archive_config


    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config                   string      DG_CONFIG=(ora1,ora2)


    SYS@ora2>alter system set
     log_archive_config='dg_config=(ora1,ora2,ora3)';


    System altered.


    SYS@ora2>show parameter log_archive_config


    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config                   string
          dg_config=(ora1,ora2,ora3)
    3.9 RMAN备库到级联库
    [oracle@ora2 ~]$ rman target sys/oracle@ora2 auxiliary sys/oracle@ora3


    Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 24 19:16:31 2016


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


    connected to target database: ora (DBID=762083517, not open)
    connected to auxiliary database: ora (not mounted)


    RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;


    Starting Duplicate Db at 24-AUG-16
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=10 device type=DISK


    contents of Memory Script:
    {
       backup as copy reuse
       targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwora2' auxiliary format 
     '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwora3'   ;
    }
    executing Memory Script


    Starting backup at 24-AUG-16
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=141 device type=DISK
    Finished backup at 24-AUG-16


    contents of Memory Script:
    {
       backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/ora/control01.ctl';
       restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/ora/control02.ctl' from 
     '/u01/app/oracle/oradata/ora/control01.ctl';
    }
    executing Memory Script


    Starting backup at 24-AUG-16
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    copying standby control file
    output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ora2.f tag=TAG20160824T191657 RECID=7 STAMP=920747818
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
    Finished backup at 24-AUG-16


    Starting restore at 24-AUG-16
    using channel ORA_AUX_DISK_1


    channel ORA_AUX_DISK_1: copied control file copy
    Finished restore at 24-AUG-16


    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 
     "/u01/app/oracle/oradata/ora/temp01.dbf";
       switch clone tempfile all;
       set newname for datafile  1 to 
     "/u01/app/oracle/oradata/ora/system01.dbf";
       set newname for datafile  2 to 
     "/u01/app/oracle/oradata/ora/sysaux01.dbf";
       set newname for datafile  3 to 
     "/u01/app/oracle/oradata/ora/undotbs01.dbf";
       set newname for datafile  4 to 
     "/u01/app/oracle/oradata/ora/users01.dbf";
       set newname for datafile  5 to 
     "/u01/app/oracle/oradata/ora/example01.dbf";
       set newname for datafile  6 to 
     "/u01/app/oracle/oradata/ora/ts_users01.dbf";
       backup as copy reuse
       datafile  1 auxiliary format 
     "/u01/app/oracle/oradata/ora/system01.dbf"   datafile 
     2 auxiliary format 
     "/u01/app/oracle/oradata/ora/sysaux01.dbf"   datafile 
     3 auxiliary format 
     "/u01/app/oracle/oradata/ora/undotbs01.dbf"   datafile 
     4 auxiliary format 
     "/u01/app/oracle/oradata/ora/users01.dbf"   datafile 
     5 auxiliary format 
     "/u01/app/oracle/oradata/ora/example01.dbf"   datafile 
     6 auxiliary format 
     "/u01/app/oracle/oradata/ora/ts_users01.dbf"   ;
    }
    executing Memory Script


    executing command: SET NEWNAME


    renamed tempfile 1 to /u01/app/oracle/oradata/ora/temp01.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


    Starting backup at 24-AUG-16
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00001 name=/u01/app/oracle/oradata/ora/system01.dbf
    output file name=/u01/app/oracle/oradata/ora/system01.dbf tag=TAG20160824T191708
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00002 name=/u01/app/oracle/oradata/ora/sysaux01.dbf
    output file name=/u01/app/oracle/oradata/ora/sysaux01.dbf tag=TAG20160824T191708
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00005 name=/u01/app/oracle/oradata/ora/example01.dbf
    output file name=/u01/app/oracle/oradata/ora/example01.dbf tag=TAG20160824T191708
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00003 name=/u01/app/oracle/oradata/ora/undotbs01.dbf
    output file name=/u01/app/oracle/oradata/ora/undotbs01.dbf tag=TAG20160824T191708
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00006 name=/u01/app/oracle/oradata/ora/ts_users01.dbf
    output file name=/u01/app/oracle/oradata/ora/ts_users01.dbf tag=TAG20160824T191708
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00004 name=/u01/app/oracle/oradata/ora/users01.dbf
    output file name=/u01/app/oracle/oradata/ora/users01.dbf tag=TAG20160824T191708
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
    Finished backup at 24-AUG-16


    contents of Memory Script:
    {
       switch clone datafile all;
    }
    executing Memory Script


    datafile 1 switched to datafile copy
    input datafile copy RECID=7 STAMP=920747871 file name=/u01/app/oracle/oradata/ora/system01.dbf
    datafile 2 switched to datafile copy
    input datafile copy RECID=8 STAMP=920747871 file name=/u01/app/oracle/oradata/ora/sysaux01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=9 STAMP=920747871 file name=/u01/app/oracle/oradata/ora/undotbs01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=10 STAMP=920747871 file name=/u01/app/oracle/oradata/ora/users01.dbf
    datafile 5 switched to datafile copy
    input datafile copy RECID=11 STAMP=920747871 file name=/u01/app/oracle/oradata/ora/example01.dbf
    datafile 6 switched to datafile copy
    input datafile copy RECID=12 STAMP=920747871 file name=/u01/app/oracle/oradata/ora/ts_users01.dbf


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


    executing command: SET until clause


    Starting recover at 24-AUG-16
    using channel ORA_AUX_DISK_1


    Finished recover at 24-AUG-16
    Finished Duplicate Db at 24-AUG-16
    3.10 RMAN成功之后修改备库log_archive_dest_3参数
    SYS@ora2>alter system set log_archive_dest_3='service=ora3 lgwr async
     valid_for=(all_logfiles,all_roles) db_unique_name=ora3';


    System altered.
    3.11 查看级联库状态
    SYS@ora3>select open_mode from v$database;


    OPEN_MODE
    --------------------
    MOUNTED


    SYS@ora3>select database_role,open_mode from v$database;


    DATABASE_ROLE    OPEN_MODE
    ---------------- --------------------
    PHYSICAL STANDBY MOUNTED
    四、测试主库、备库、级联库
    4.1查看主库、备库、级联库状态
    1)主库
    SYS@ora>select protection_mode,database_role,protection_level,open_mode from
     v$database;


    PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL
         OPEN_MODE
    -------------------- ---------------- -------------------- --------------------
    MAXIMUM PERFORMANCE  PRIMARY          MAXIMUM PERFORMANCE  READ
     WRITE


    2)备库
    SYS@ora2>select protection_mode,database_role,protection_level,open_mode
     from v$database;


    PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL
         OPEN_MODE
    -------------------- ---------------- -------------------- --------------------
    MAXIMUM PERFORMANCE  PHYSICAL STANDBY MAXIMUM PERFORMANCE
      MOUNTED


    3)级联库
    SYS@ora3>select protection_mode,database_role,protection_level,open_mode
     from v$database;


    PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL
         OPEN_MODE
    -------------------- ---------------- -------------------- --------------------
    MAXIMUM PERFORMANCE  PHYSICAL STANDBY MAXIMUM PERFORMANCE
      MOUNTED
    4.2 备库、级联库应用日志
    SYS@ora2>alter database recover managed standby database using current logfile
     disconnect from session;


    Database altered.


    SYS@ora3>alter database open;


    Database altered.


    SYS@ora3>alter database recover managed standby database using current logfile
     disconnect from session;


    Database altered.
    4.3 主库创建测试表空间测试
    SYS@ora>create tablespace ts_test datafile '/u01/app/oracle/oradata/ora/ts_test01.dbf' size 20M;


    Tablespace created.
    4.5 查看三库的alert日志
    1)主库
    Wed Aug 24 18:55:41 2016
    create tablespace ts_test datafile '/u01/app/oracle/oradata/ora/ts_test01.dbf' size
     20M
    Completed: create tablespace ts_test datafile
     '/u01/app/oracle/oradata/ora/ts_test01.dbf' size 20M
    2)备库:
    Wed Aug 24 19:35:45 2016
    WARNING: File being created with same name as in Primary
    Existing file may be overwritten
    Recovery created file /u01/app/oracle/oradata/ora/ts_test01.dbf
    Successfully added datafile 7 to media recovery
    Datafile #7: '/u01/app/oracle/oradata/ora/ts_test01.dbf'
    4.6 主库切换日志:
    SYS@ora>alter system switch logfile;


    System altered.
    主库
    Wed Aug 24 18:55:41 2016
    create tablespace ts_test datafile '/u01/app/oracle/oradata/ora/ts_test01.dbf' size
     20M
    Completed: create tablespace ts_test datafile
     '/u01/app/oracle/oradata/ora/ts_test01.dbf' size 20M
    Wed Aug 24 18:58:47 2016
    Thread 1 cannot allocate new log, sequence 15
    Private strand flush not complete
      Current log# 2 seq# 14 mem# 0: /u01/app/oracle/oradata/ora/redo02.log
    LGWR: Standby redo logfile selected for thread 1 sequence 15 for destination
     LOG_ARCHIVE_DEST_2
    Thread 1 advanced to log sequence 15 (LGWR switch)
      Current log# 3 seq# 15 mem# 0: /u01/app/oracle/oradata/ora/redo03.log
    Wed Aug 24 18:58:48 2016
    Archived Log entry 18 added for thread 1 sequence 14 ID 0x2d6d16bd dest 1:
    2)备库:
    Wed Aug 24 19:38:51 2016
    RFS[2]: Selected log 4 for thread 1 sequence 14 dbid 762083517 branch 920730879
    Wed Aug 24 19:38:51 2016
    Archived Log entry 5 added for thread 1 sequence 14 ID 0x2d6d16bd dest 1:
    Wed Aug 24 19:38:52 2016
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ora/1_14_920730879.arc
    WARNING: File being created with same name as in Primary
    Existing file may be overwritten
    Recovery created file /u01/app/oracle/oradata/ora/ts_test01.dbf
    Successfully added datafile 7 to media recovery
    Datafile #7: '/u01/app/oracle/oradata/ora/ts_test01.dbf'
    Recovery deleting file #7:'/u01/app/oracle/oradata/ora/ts_test01.dbf' from
     controlfile.
    Deleted file /u01/app/oracle/oradata/ora/ts_test01.dbf
    Recovery dropped tablespace 'TS_TEST'
    WARNING: File being created with same name as in Primary
    Existing file may be overwritten
    Recovery created file /u01/app/oracle/oradata/ora/ts_test01.dbf
    Successfully added datafile 7 to media recovery
    Datafile #7: '/u01/app/oracle/oradata/ora/ts_test01.dbf'
    Media Recovery Waiting for thread 1 sequence 15
    3)级联库:
    Wed Aug 24 19:38:51 2016
    RFS[2]: Selected log 4 for thread 1 sequence 14 dbid 762083517 branch 920730879
    Wed Aug 24 19:38:51 2016
    Archived Log entry 5 added for thread 1 sequence 14 ID 0x2d6d16bd dest 1:
    Wed Aug 24 19:38:52 2016
    Media Recovery Log /u01/app/oracle/fast_recovery_area/ora/1_14_920730879.arc
    WARNING: File being created with same name as in Primary
    Existing file may be overwritten
    Recovery created file /u01/app/oracle/oradata/ora/ts_test01.dbf
    Successfully added datafile 7 to media recovery
    Datafile #7: '/u01/app/oracle/oradata/ora/ts_test01.dbf'
    Recovery deleting file #7:'/u01/app/oracle/oradata/ora/ts_test01.dbf' from
     controlfile.
    Deleted file /u01/app/oracle/oradata/ora/ts_test01.dbf
    Recovery dropped tablespace 'TS_TEST'
    WARNING: File being created with same name as in Primary
    Existing file may be overwritten
    Recovery created file /u01/app/oracle/oradata/ora/ts_test01.dbf
    Successfully added datafile 7 to media recovery
    Datafile #7: '/u01/app/oracle/oradata/ora/ts_test01.dbf'
    Media Recovery Waiting for thread 1 sequence 15

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

    转载于:http://blog.itpub.net/31362743/viewspace-2124232/

    展开全文
  • Oracle12c DG环境搭建级联备库

    千次阅读 2019-06-02 10:27:35
    实际上和正常搭建DG没什么区别,只是改一下参数即可,这里通过采用ADG方式来搭建。 最多支持30个级联备库,为啥呢?因为LOG_ARCHIVE_DEST_n,只有31个。 2 实验 更多详细信息,参考官方文档:...
  • 搭建oracle 级联DG 现有架构:physical standby 一主二备,在此基础上,在主库下新建备库standby3、级联备库cascade 数据库版本 11.2.0.4 db_name=prod db为主库,dg1为备库,dg2为级联备库;DB_UNIQUE_NAME ...
  • 原DG环境为3节点DG,一主两备(非级联DG环境),主库prod切换日志时,会同时将日志发送到proddg1与proddg2 修改后: 现DG环境为3节点DG,一主两备(级联DG环境),主库prod切换日志时,会将日志发送到proddg1,然后...
  • 1.Orcle 12c 新特性---使用DBCA创建物理备库2.Orcle 12c DG 新特性---Far Synchttps://www.cndba.cn/Expect-le/article/22403.Orcle 12c DG 新特性---Active Data Guard Support for Sequenceshttps://...
  • 注意必要的目录结构.然后从主库,备库1 都能用SYS用户登陆到备库2 表示通道是联通有效的.02: 从主库生成个最新的INIT.ORA文件,复制到备库2 修改参数文件的参数...dg2: startup nomount pfile='/home/oracle/initdg.ora'
  • DG环境的搭建必须要把数据库启动到归档模式,并且为了避免开发人员使用nologging语句,我们还要把数据库设置为force logging。 查看数据库是否运行在归档模式: #su - oracle $sqlplus / as sysdba SQL>...
  • -- 级联备库  备库 IP :192.168.0.12  DB_NAME:ORA11GR2  DB_UNIQUE_NAME :ORA11GR21 -- 源库 -- 源库参数文件 [ oracle @ rac1 dbs ] $ cat initORA11GR2 . ora ORA11GR2 . __db_...
  • DG 日志级联传送 参数据配置

    千次阅读 2016-09-02 18:10:03
    DG级联安装: 数据库版本 11.2.0.1 r2 primary 10.3.4.110  r5 standby1 10.3.4.111 r6 standby2 10.3.4.112 ###########################################################主库r2参数文件设置#############...
  • Oracle DG参数说明

    2019-05-05 00:17:37
    --dg参数说明 --1.DB_NAME --数据库名字,需要保持同一个Data Guard中所有数据库DB_NAME相同 --主库和备库相同 DB_NAME='chicago' DB_NAME='chicago' --2.DB_UNIQUE_NAME DB_UNIQUE_NAME = unique_service_...
  • 关于DG环境下备库数据文件重命名的问题: ... backup current controlfile for standby format '/home/oracle/rman/standby.ctl'; 拷贝到备库进行恢复. 备库此时不设置db_file_name_convert ,log_file_
  • oracle 之物理DG 创建

    千次阅读 热门讨论 2014-04-16 11:49:31
    今天是2014-04-16,继续完成DG的整理内容。该篇日志,将记录创建DG的所需参数简要介绍,和创建物理DG的过程。 第一:参数介绍: db_unique_name(db_name):该参数指定数据库唯一名字,注意该参数将和log_archive_...
  • Oracle Data Guard是OracleMAA(Maximum Availability Architecture)中的成员之一,也是MAA中技术要求最简单的方案之一。随着Oracle新功能的引入如Active Standby Database后,加速了Oracle Data Guard的普及。响应去...
  • DG架构图如下: 计划,切换之后的架构图: DG切换: 主备切换:这里所有的数据库数据文件、日志文件的路径是一致的 【旧主库】主库primarydb切换为备库standby3主库检查switchover_status列的状态,是否...
  • Oracle 11g RAC+DG项目实战(共15集)视频

    千次阅读 2019-09-04 10:11:39
    下载地址: ...在15集视频中详细阐述了Oracle RAC的安装,RAC如何配置Active Data Guard,DG如何切换!绝对重量级的视频。掌握这个技术,资深Oracle DBA的岗位你也轻松秒杀! 实验手册在最后一集视频...
  • 延时测试(备份库执行) alter database recover managed standby database delay 120 disconnect from session; 实时同步 ALTER ...

空空如也

空空如也

1 2 3 4 5 ... 12
收藏数 230
精华内容 92
关键字:

oracle级联dg