用Broker搭建Oracle Data Guard的必须注意知识点

最近在用Broker 配置和管理Data Guard(Oracle version : 10.2.0.1.0),以前的数据库都基本不设置db_domain的，当使用database link的时候，启用了global_names=true,也设置了db_domain，所以以前都能成功顺利的用Broker配置和管理Data Guard的设置，现在不行了，我指的不行，是指用broker做switchover的时候，主备库的instances不自动的启动，需要手动的去启动。

当我用show configuration的时候，broker 也提示是successs的,如下所示：

DGMGRL> show configuration;

Configuration
  Name:                dgmgrl_1
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    prod - Physical standby database
    sbdb - Primary database

Current status for "dgmgrl_1":
SUCCESS

 

但是当我switchover的时候，自动启动instances失败如下所示：

DGMGRL> switchover to PROD;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "SBDB" on database "sbdb"
Shutting down instance "SBDB"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "PROD" on database "prod"
Shutting down instance "PROD"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "SBDB" on database "sbdb"
Starting instance "SBDB"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "PROD" on database "prod"
Starting instance "PROD"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "PROD"
You must start instance "PROD" manually
Switchover succeeded, new primary is "prod"

 

从上面的错误提示可以看出，肯定是监听配置的有问题，下面是我以前配置的listener.ora

listener.ora

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=vmoel5u4.oracle.com)(PORT=1521))
     )
   )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=PROD_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME=PROD)
     )
    (SID_DESC=
      (GLOBAL_DBNAME=PROD)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME=PROD)
     )
   )

 

 

于是查了下数据库的db_domain：

SQL> show parameter db_domain;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_domain                            string                           ORACLE.COM

 

发现db_domain是有指的，改成下面的格式就可以了

listener.ora

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=vmoel5u4.oracle.com)(PORT=1521))
     )
   )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=PROD_DGMGRL.ORACLE.COM)      ---(大小写敏感的case sensitive)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME=PROD)
     )
    (SID_DESC=
      (GLOBAL_DBNAME=PROD)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME=PROD)
     )
   )

 

再次做switchover,这次自动启动instances成功：

DGMGRL> switchover to PROD;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "SBDB" on database "sbdb"
Shutting down instance "SBDB"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "PROD" on database "prod"
Shutting down instance "PROD"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "SBDB" on database "sbdb"
Starting instance "SBDB"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "PROD" on database "prod"
Starting instance "PROD"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod"
DGMGRL> show configuration;

Configuration
  Name:                dgmgrl_1
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    prod - Primary database
    sbdb - Physical standby database

Current status for "dgmgrl_1":
SUCCESS

 

Oralce教程上是这么说的：

To enable the Data Guard broker CLI to restart instances during the course of broker operations,a service with a specific name must be statically registered with the local listener of each instance.The value of the GLOBAL_DBNAME attribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain.