配置dg broker

primary名称：

SYS@orsid1> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string      +DATA, +DATA
db_name                              string      oradb
db_unique_name                       string      oradb
global_names                         boolean     FALSE
instance_name                        string      orsid1
lock_name_space                      string
log_file_name_convert                string      +DATA, +DATA, +ARCH, +ARCH
processor_group_name                 string
service_names                        string      oradb

broker配置需放置shared storage中，以便RAC能顺利读取配置。

SYS@orsid1> alter system set dg_broker_config_file1='+DATA/oradb/datafile/dr1oradb.dat'  scope=both sid='*';
SYS@orsid1> alter system set dg_broker_config_file2='+DATA/oradb/datafile/dr2oradb.dat'  scope=both sid='*';

运行dg broker：

SYS@orsid1> alter system set dg_broker_start=TRUE scope=both sid='*';

添加listener

注意 GLOBAL_DBNAME 为 **_DGMGRL.**

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 )
     (SID_NAME = orsid1 )
     (GLOBAL_DBNAME=oradb )
    )
    (SID_DESC =
      (GLOBAL_DBNAME = oradb_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orsid1)
      (SERVICE_NAME = oradb)
    )
)

standby名称：


SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string      +DATA, /u01/app/oracle/oradata
db_name                              string      oradb
db_unique_name                       string      ADG
global_names                         boolean     FALSE
instance_name                        string      ADG
lock_name_space                      string
log_file_name_convert                string      +DATA, /u01/app/oracle/oradata
                                                 , +ARCH, /u01/app/oracle/flash
                                                 _recovery_area
processor_group_name                 string
service_names                        string      ADG

运行dg broker：

或许可以省略指定broker配置文件路径 dg_broker_config_file1，待测试，。

SYS@ADG> alter system set dg_broker_start=TRUE scope=both sid='*';

添加standby的listener:


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
     (SID_NAME = ADG)
     (GLOBAL_DBNAME=ADG)
    )
    (SID_DESC =
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
     (SID_NAME = ADG)
     (GLOBAL_DBNAME=ADG_DGMGRL )
     (SERVICE_NAME=ADG )
    )
 )

dgmgrl 配置

在primary进行dg broker设置，不需在standby中设置。如果添加database的config，需要在主库里enable将其生效（更新） 登陆时候需要输入账号密码，以减少控制standby的影响

DGMGRL> connect sys/oracle
DGMGRL> CREATE CONFIGURATION 'oradb_config' as PRIMARY DATABASE IS 'oradb' connect identifier is 'oradb' ;
DGMGRL> add database 'ADG' AS CONNECT IDENTIFIER IS 'ADG' MAINTAINED AS PHYSICAL;   
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - oradb_config

  Protection Mode: MaxPerformance
  Databases:
    oradb - Primary database
    ADG   - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

故障排错

刚刚开始的时候出现异常，后来通过删除DGMGRL的配置，重新配置后正常： 有配置，确实单引号导致异常，Physical standby disable ，解决办法是将config删除重新添加

DGMGRL> remove database 'ADG';
Removed database "ADG" from the configuration

DGMGRL> add database 'ADG' AS CONNECT IDENTIFIER IS 'ADG' MAINTAINED AS PHYSICAL;                   
Database "ADG" added
DGMGRL> show configuration

Configuration - oradb_config

  Protection Mode: MaxPerformance
  Databases:
    oradb - Primary database
    ADG   - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

switchover

switch to standby server

oracle@orcl ~ $ dgmgrl sys/oracle@ORCL

DGMGRL> switchover to 'ADG';
Performing switchover NOW, please wait...
Operation requires a connection to instance "ADG" on database "ADG"
Connecting to instance "ADG"...
Connected.
New primary database "ADG" is opening...
Operation requires startup of instance "ORCL" on database "ORCL"
Starting instance "ORCL"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ADG"

switch back to original primary

oracle@orcl ~ $ dgmgrl sys/oracle@ADG

DGMGRL> switchover to 'ORCL'
Performing switchover NOW, please wait...
Operation requires a connection to instance "ORCL" on database "ORCL"
Connecting to instance "ORCL"...
Connected.
New primary database "ORCL" is opening...
Operation requires startup of instance "ADG" on database "ADG"
Starting instance "ADG"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ORCL"

failover

  • 切换failover前，原primary需启用flashback, 否则会出现 reinstate 失败
SYS@PRIMARY> SELECT FLASHBACK_ON FROM V$DATABASE;
SYS@PRIMARY> ALTER DATABASE FLASHBACK ON;

oracle@orcl ~ $ dgmgrl sys/oracle@ADG

DGMGRL> failover to 'ADG'
Performing failover NOW, please wait...
Failover succeeded, new primary is "ADG"

DGMGRL> show configuration ; 

Configuration - ORCL

  Protection Mode: MaxPerformance
  Databases:
    ADG  - Primary database
    ORCL - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> reinstate database 'ORCL'; 
Reinstating database "ORCL", please wait...
Operation requires shutdown of instance "ORCL" on database "ORCL"
Shutting down instance "ORCL"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ORCL" on database "ORCL"
Starting instance "ORCL"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ORCL" ...
Operation requires shutdown of instance "ORCL" on database "ORCL"
Shutting down instance "ORCL"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ORCL" on database "ORCL"
Starting instance "ORCL"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ORCL" ...
Reinstatement of database "ORCL" succeeded

snapshot standby

DGMGRL> show configuration 

Configuration - ORCL

  Protection Mode: MaxPerformance
  Databases:
    ORCL - Primary database
    ADG  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

CONVERT DATABASE 'ADG' TO SNAPSHOT STANDBY;
Converting database "ADG" to a Snapshot Standby database, please wait...
Database "ADG" converted successfully
DGMGRL> CONVERT DATABASE 'ADG' TO PHYSICAL STANDBY;
Converting database "ADG" to a Physical Standby database, please wait...
Operation requires shutdown of instance "ADG" on database "ADG"
Shutting down instance "ADG"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ADG" on database "ADG"
Starting instance "ADG"...
ORACLE instance started.
Database mounted.
Continuing to convert database "ADG" ...
Operation requires shutdown of instance "ADG" on database "ADG"
Shutting down instance "ADG"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ADG" on database "ADG"
Starting instance "ADG"...
ORACLE instance started.
Database mounted.
Database "ADG" converted successfully

ORA-16857: standby disconnected from redo source for longer than specified threshold

Try setting TransportDisconnectedThreshold higher than the default of 30.

EDIT DATABASE 'ORASTBDB' SET PROPERTY TransportDisconnectedThreshold='120';