oracle data guard配置dg_broker

青旅半醒 2022-05-10 13:24 244阅读 0赞

[https://community.oracle.com/docs/DOC-1007327][https_community.oracle.com_docs_DOC-1007327]

**本文主要包括以下内容：**

1.  配置dg broker，需要完成以下几个工作：

*  在主备库配置静态监听注册，注册的服务名为db\_unique\_name\_DGMGRL；
 *  在主备库修改tnsnames.ora文件，配置别名；
 *  主备库都开启dg\_broker\_start参数；
 *  在主库注册broker；
 *  在主库添加备库的broker并开启。

2.　Data Guard switchover；

3.   Data Guard failover；

4.   Snapshot Standby。

**环境：**

主库：ora12c       数据库：db12g   db\_unique\_name：db12g

备库：ora12c-dg  数据库：db12g   db\_unique\_name：db12g\_s

**配置dg broker：**

1. 主库配置静态监听：

![复制代码][copycode.gif]

[oracle@ora12c admin]$ cat listener.ora 
    # listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
    
    SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = db12g_dgmgrl)
         (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
         (SID_NAME = db12g)
        )
      )

![复制代码][copycode.gif]

重新加载监听：

![复制代码][copycode.gif]

[oracle@ora12c admin]$ lsnrctl reload
    
    LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-MAY-2017 19:42:33
    
    Copyright (c) 1991, 2016, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12c)(PORT=1521)))
    The command completed successfully

![复制代码][copycode.gif]

查看监听

![复制代码][copycode.gif]

[oracle@ora12c admin]$ lsnrctl status
    
    LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-MAY-2017 20:27:43
    
    Copyright (c) 1991, 2016, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12c)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
    Start Date                23-MAY-2017 10:12:47
    Uptime                    0 days 10 hr. 14 min. 56 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/ora12c/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/db12g/xdb_wallet))(Presentation=HTTP)(Session=RAW))
    Services Summary...
    Service "4d852a2714024610e0537838a8c07c53" has 1 instance(s).
      Instance "db12g", status READY, has 1 handler(s) for this service...
    Service "db12g" has 1 instance(s).
      Instance "db12g", status READY, has 1 handler(s) for this service...
    Service "db12gXDB" has 1 instance(s).
      Instance "db12g", status READY, has 1 handler(s) for this service...
    Service "db12g_CFG" has 1 instance(s).
      Instance "db12g", status READY, has 1 handler(s) for this service...
    Service "db12g_DGB" has 1 instance(s).
      Instance "db12g", status READY, has 1 handler(s) for this service...
    Service "db12g_dgmgrl" has 1 instance(s).
      Instance "db12g", status UNKNOWN, has 1 handler(s) for this service...
    Service "pdb01" has 1 instance(s).
      Instance "db12g", status READY, has 1 handler(s) for this service...
    The command completed successfully

![复制代码][copycode.gif]

2. 配置备库静态：

![复制代码][copycode.gif]

[oracle@ora12c-dg admin]$ cat listener.ora 
    # listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-dg)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
    
    SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = db12g_s_dgmgrl)
         (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
         (SID_NAME = db12g)
       )
      )

![复制代码][copycode.gif]

重建加载，与主库相同

3. 配置主库tnsnames.ora：

![复制代码][copycode.gif]

[oracle@ora12c admin]$ cat tnsnames.ora 
    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.
    
    LISTENER_DB12G =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c)(PORT = 1521))
    
    DB12G =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = db12g)
        )
      )
    
    DB12G_S =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-dg)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = db12g)
        )
      )

![复制代码][copycode.gif]

4. 备库配置tnsnames.ora：

![复制代码][copycode.gif]

[oracle@ora12c-dg admin]$ cat tnsnames.ora 
    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.
    
    LISTENER_DB12G =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-dg)(PORT = 1521))
    
    DB12G =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = db12g)
        )
      )
    
    DB12G_S =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-dg)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = db12g)
        )
      )

![复制代码][copycode.gif]

5. 在主备库同时开启dg\_broker\_start参数：

![复制代码][copycode.gif]

[oracle@ora12c admin]$ sqlplus / as sysdba
    
    SQL*Plus: Release 12.2.0.1.0 Production on Tue May 23 15:35:20 2017
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> alter system set dg_broker_start=true scope=both;
    
    System altered.

![复制代码][copycode.gif]

6. 在主节点，创建配置及注册broker：

![复制代码][copycode.gif]

[oracle@ora12c admin]$ dgmgrl sys/oracle@db12g
    DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 23 15:37:02 2017
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    Welcome to DGMGRL, type "help" for information.
    Connected to "db12g"
    Connected as SYSDBA.
    DGMGRL> create configuration my_dg_config as primary database is db12g connect identifier is db12g;
    Configuration "my_dg_config" created with primary database "db12g"
    DGMGRL>

![复制代码][copycode.gif]

7. 在配置中添加备库：

DGMGRL> add database db12g_s as connect identifier is db12g_s maintained as physical;
    Database "db12g_s" added
    DGMGRL>

8. enable 配置：

DGMGRL> enable configuration
    Enabled.
    DGMGRL>

9.  通过broker查看配置情况及数据库情况：

![复制代码][copycode.gif]

DGMGRL> show configuration
    
    Configuration - my_dg_config
    
      Protection Mode: MaxPerformance
      Members:
      db12g   - Primary database
        db12g_s - Physical standby database 
    
    Fast-Start Failover: DISABLED
    
    Configuration Status:
    SUCCESS   (status updated 34 seconds ago)

DGMGRL> SHOW DATABASE db12g;
    
    Database - db12g
    
      Role:               PRIMARY
      Intended State:     TRANSPORT-ON
      Instance(s):
        cdb1
    
    Database Status:
    SUCCESS

DGMGRL> SHOW DATABASE db12g_s;
    
    Database - db12g_s
    
      Role:               PHYSICAL STANDBY
      Intended State:     APPLY-ON
      Transport Lag:      0 seconds (computed 1 second ago)
      Apply Lag:          0 seconds (computed 1 second ago)
      Average Apply Rate: 11.00 KByte/s
      Real Time Query:    OFF
      Instance(s):
        db12g
    
    Database Status:
    SUCCESS

![复制代码][copycode.gif]

10. switchover演练，这里的切换无数据丢失，连接到主库，切换到备库：

![复制代码][copycode.gif]

[oracle@ora12c admin]$ dgmgrl sys/oracle@db12g
    DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 23 16:47:58 2017
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    Welcome to DGMGRL, type "help" for information.
    Connected to "db12g"
    Connected as SYSDBA.
    
    DGMGRL> switchover to db12g_s
    Performing switchover NOW, please wait...
    Operation requires a connection to database "db12g_s"
    Connecting ...
    Connected to "db12g_s"
    Connected as SYSDBA.
    New primary database "db12g_s" is opening...
    Operation requires start up of instance "db12g" on database "db12g"
    Starting instance "db12g"...
    ORACLE instance started.
    Database mounted.
    Database opened.
    Connected to "db12g"
    Switchover succeeded, new primary is "db12g_s"
    
    SQL> select database_role from v$database;
    
    DATABASE_ROLE
    ----------------
    PHYSICAL STANDBY
    
    SQL> select process,status,sequence# from v$managed_standby;
    
    PROCESS   STATUS        SEQUENCE#
    --------- ------------ ----------
    ARCH      CONNECTED             0
    ARCH      CONNECTED             0
    ARCH      CLOSING               1
    ARCH      CLOSING               2
    ARCH      CONNECTED             0
    ARCH      CONNECTED             0
    ARCH      CONNECTED             0
    ARCH      CONNECTED             0
    DGRD      ALLOCATED             0
    DGRD      ALLOCATED             0
    RFS       IDLE                  0
    
    PROCESS   STATUS        SEQUENCE#
    --------- ------------ ----------
    MRP0      APPLYING_LOG          3
    RFS       IDLE                  0
    RFS       IDLE                  3
    
    14 rows selected.

![复制代码][copycode.gif]

11. failover演练，如果主库发生故障，无法连接到主库，可以用下面命令进行切换。连接到备库，并切换到备库：

![复制代码][copycode.gif]

[oracle@ora12c admin]$ dgmgrl sys/oracle@db12g_s
    DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 23 17:48:32 2017
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    Welcome to DGMGRL, type "help" for information.
    Connected to "db12g_s"
    Connected as SYSDBA.
    DGMGRL> failover to db12g_s
    Performing failover NOW, please wait...
    Failover succeeded, new primary is "db12g_s"
    DGMGRL>

![复制代码][copycode.gif]

12. 当源主库开启了闪回功能，这时将源主库修复之后，可以不用重新搭建DG，而是用一命令，可将源主库变为备库。但是如果源主库未开启闪回，将需要重建：

![复制代码][copycode.gif]

DGMGRL> reinstate database db12g
    Reinstating database "db12g", please wait...
    Operation requires shut down of instance "db12g" on database "db12g"
    Shutting down instance "db12g"...
    Connected to "db12g"
    ORACLE instance shut down.
    Operation requires start up of instance "db12g" on database "db12g"
    Starting instance "db12g"...
    ORACLE instance started.
    Database mounted.
    Connected to "db12g"
    Continuing to reinstate database "db12g" ...
    Reinstatement of database "db12g" succeeded
    DGMGRL>

![复制代码][copycode.gif]

13. 开启快照备库，这是11g的新功能，开启之后备库可以进行读写，但是当重新更改会备库的模式之后，所有的更改都是丢失，备库将继续同步主库的数据：

![复制代码][copycode.gif]

[oracle@ora12c admin]$ dgmgrl sys/oracle@db12g
    DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 23 17:44:43 2017
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    Welcome to DGMGRL, type "help" for information.
    Connected to "db12g"
    Connected as SYSDBA.
    DGMGRL> CONVERT DATABASE db12g_s TO SNAPSHOT STANDBY;
    Converting database "db12g_s" to a Snapshot Standby database, please wait...
    Database "db12g_s" converted successfully
    DGMGRL>

![复制代码][copycode.gif]

14. 当结束快照数据之后，可以将其切换到物理备库：

![复制代码][copycode.gif]

[oracle@ora12c admin]$ dgmgrl sys/oracle@db12g
    DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 23 17:45:55 2017
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    Welcome to DGMGRL, type "help" for information.
    Connected to "db12g"
    Connected as SYSDBA.
    DGMGRL> CONVERT DATABASE db12g_s TO PHYSICAL STANDBY;
    Converting database "db12g_s" to a Physical Standby database, please wait...
    Operation requires shut down of instance "db12g" on database "db12g_s"
    Shutting down instance "db12g"...
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    Operation requires start up of instance "db12g" on database "db12g_s"
    Starting instance "db12g"...
    ORACLE instance started.
    Database mounted.
    Continuing to convert database "db12g_s" ...
    Database "db12g_s" converted successfully
    DGMGRL>

![复制代码][copycode.gif]

此时，物理备库再次同步主库数据。

**遇到的坑：**

1. 配置完broker之后，查看配置的时候报错：

![复制代码][copycode.gif]

DGMGRL> show configuraton
    show configuraton
         ^
    Syntax error before or at "configuraton"
    DGMGRL> show configuration
    
    Configuration - my_dg_config
    
      Protection Mode: MaxPerformance
      Members:
      db12g   - Primary database
        db12g_s - Physical standby database 
          Error: ORA-16664: unable to receive the result from a member
    
    Fast-Start Failover: DISABLED
    
    Configuration Status:
    ERROR   (status updated 59 seconds ago)

![复制代码][copycode.gif]

经过检查，是由于备库的tnsnames.ora文件中的配置错误导致无法连接到主库，其次查看备库的配置，发现很多警告，重新进行修改并重启数据库之后OK：

![复制代码][copycode.gif]

DGMGRL> show database verbose db12g_s
    
    Database - db12g_s
    
      Role:               PHYSICAL STANDBY
      Intended State:     APPLY-ON
      Transport Lag:      0 seconds (computed 0 seconds ago)
      Apply Lag:          0 seconds (computed 0 seconds ago)
      Average Apply Rate: 1.00 KByte/s
      Active Apply Rate:  183.00 KByte/s
      Maximum Apply Rate: 183.00 KByte/s
      Real Time Query:    ON
      Instance(s):
        db12g
          Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the member setting
          Warning: ORA-16675: database instance restart required for property value modification to take effect
          Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the member setting
    
      Properties:
        DGConnectIdentifier             = 'db12g_s'
        ObserverConnectIdentifier       = ''
        LogXptMode                      = 'ASYNC'
        RedoRoutes                      = ''
        DelayMins                       = '0'
        Binding                         = 'optional'
        MaxFailure                      = '0'
        MaxConnections                  = '1'
        ReopenSecs                      = '300'
        NetTimeout                      = '30'
        RedoCompression                 = 'DISABLE'
        LogShipping                     = 'ON'
        PreferredApplyInstance          = ''
        ApplyInstanceTimeout            = '0'
        ApplyLagThreshold               = '30'
        TransportLagThreshold           = '30'
        TransportDisconnectedThreshold  = '30'
        ApplyParallel                   = 'AUTO'
        ApplyInstances                  = '0'
        StandbyFileManagement           = 'AUTO'
        ArchiveLagTarget                = '0'
        LogArchiveMaxProcesses          = '8'
        LogArchiveMinSucceedDest        = '1'
        DataGuardSyncLatency            = '0'
        DbFileNameConvert               = '/u01/app/oradata, /u01/app/oracle/oradata'
        LogFileNameConvert              = '/u01/app/oradata, /u01/app/oracle/oradata'
        FastStartFailoverTarget         = ''
        InconsistentProperties          = '(monitor)'
        InconsistentLogXptProps         = '(monitor)'
        SendQEntries                    = '(monitor)'
        LogXptStatus                    = '(monitor)'
        RecvQEntries                    = '(monitor)'
        PreferredObserverHosts          = ''
        StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12c-dg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db12g_s_DGMGRL)(INSTANCE_NAME=db12g)(SERVER=DEDICATED)))'
        StandbyArchiveLocation          = '/u01/app/oracle/archivelog'
        AlternateLocation               = ''
        LogArchiveTrace                 = '0'
        LogArchiveFormat                = '%t_%s_%r.arc'
        TopWaitEvents                   = '(monitor)'
    
      Log file locations:
        Alert log               : /u01/app/oracle/diag/rdbms/db12g_s/db12g/trace/alert_db12g.log
        Data Guard Broker log   : /u01/app/oracle/diag/rdbms/db12g_s/db12g/trace/drcdb12g.log
    
    Database Status:
    WARNING
    
    DGMGRL> edit database db12g_s set property LogArchiveTrace='0';
    Property "logarchivetrace" updated
    
    DGMGRL> edit database db12g_s set property  LogArchiveFormat='%t_%s_%r.arc';
    Warning: ORA-16675: database instance restart required for property value modification to take effect
    
    Property "logarchiveformat" updated
    DGMGRL> quit
    [oracle@ora12c-dg ~]$ 
    [oracle@ora12c-dg ~]$ 
    [oracle@ora12c-dg ~]$ 
    [oracle@ora12c-dg ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 12.2.0.1.0 Production on Tue May 23 17:18:49 2017
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup 
    ORACLE instance started.
    
    Total System Global Area 1207959552 bytes
    Fixed Size                  8792152 bytes
    Variable Size             436209576 bytes
    Database Buffers          754974720 bytes
    Redo Buffers                7983104 bytes
    Database mounted.
    Database opened.

![复制代码][copycode.gif]

2. 配置归档参数时报错，无法配置，经过检查发现是log\_archive\_config参数配置错误，修改之后正常：

![复制代码][copycode.gif]

SQL> alter system set log_archive_dest_2='service=db12g_s LGWR ASYNC=20480 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB12G_S OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30' scope=both;   
    alter system set log_archive_dest_2='service=db12g_s LGWR ASYNC=20480 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB12G_S OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30' scope=both
    *
    ERROR at line 1:
    ORA-02097: parameter cannot be modified because specified value is invalid
    ORA-16053: DB_UNIQUE_NAME DB12G_S is not in the Data Guard Configuration
    
    SQL> show parameter dg_config
    SQL> alter system set log_archive_config='dg_config=(db12g,db12g_s)';
    
    System altered.
    
    SQL> alter system set log_archive_dest_2='service=db12g_s LGWR ASYNC=20480 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB12G_S OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30' scope=both;   
    
    System altered.

![复制代码][copycode.gif]

[https_community.oracle.com_docs_DOC-1007327]: https://community.oracle.com/docs/DOC-1007327
[copycode.gif]: /images/20220505/33a3657a508d49318c344a774bf1812f.png