DATAGUARD BROKER配置步骤

配置broker的前提是你的有个运行正常的dataguard，我只写了在dataguard基础上配置的broker，dataguard的步骤省略了。

1.主备库查看dg_broker_start的状态

SQL> show parameter dg_broker_start;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start     boolean FALSE
该参数默认会被设为FALSE，设为TRUE会随数据库实例而自动启动
SQL>alter system set dg_broker_start = true scope=both;
Systemaltered.
SQL>SHOW PARAMETER DG_BROKER_START
 
NAME                              TYPE    VALUE
----------------------------------------------- ------------------------------
dg_broker_start                boolean TRUE


2.启用 Flashback
可以通过如下SQL 查看是否启用了Flashback：
SQL>select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
 
在主备库查看：
SQL>show parameter db_recovery_file_dest 
NAME                              TYPE    VALUE
----------------------------------------------- ------------------------------
db_recovery_file_dest               string    /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size        big integer 1G


在启用Flashback database 之前，需要先设置db_recovery_file_dest_size参数，而后才可以设置db_recovery_file_dest
如果在备库开启闪回需要先取消recover 进程，不然会报错。
主库：
SQL>alter database flashback on;
Database altered.
SQL>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
备库：
SQL>alter database recover managed standby database cancel;
Databasealtered.
SQL>alter database flashback on;
Database altered.
SQL>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL>alter database recover managed standby database disconnect from session using current logfile;
Database altered.


3.配置主库、备库的db_domain
主库：
SQL> shutdown immediate
vi /u01/app/oracle/product/11.2.0/db_1/dbs/initdsdfdb.ora
*.db_domain='cuiyan1.com'




SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdsdfdb.ora';
SQL> create spfile from pfile;
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;
SQL> show parameter db_domain;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain     string cuiyan1.com


备库；
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate
vi /u01/app/oracle/product/11.2.0/db_1/dbs/initdsdfdb.ora
*.db_domain='cuiyan2.com'
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdsdfdb.ora';
SQL> create spfile from pfile;
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> show parameter db_domain;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain     string cuiyan2.com
4.配置监听
设置listener 
在Data Guard Broker环境中，需要在listener.ora静态添加一个service_name为db_unique_name_DGMGRL.db_domain的注册。这个service_name会在DGMGRL重启数据库的时候用到。通过DGMGRL重启数据库时DMON进程会先将数据库关闭，然后DGMGRL用此service_name通过listener连接到数据库，发出启动命令。这个环节很容易出错，下面的配置方法是经过实验成功的。
主库：
vi $ORACLE_HOME/network/admin/listener.ora


SID_LIST_LISTENER =
 (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = dsdfdbm_DGMGRL.cuiyan1.com)----------------<db_unique_name>_DGMGRL.<db_domain>
      (ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = dsdfdb)
    )
   (SID_DESC =
     (GLOBAL_DBNAME = dsdfdbm)------------db_unique_name
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
     (SID_NAME = dsdfdb)
    )
  )




LISTENER =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.0.51)(PORT = 1521))
  )


ADR_BASE_LISTENER =/u01/app/oracle



$ lsnrctl reload


备库：
vi $ORACLE_HOME/network/admin/listener.ora


SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = dsdfdbs_DGMGRL.cuiyan2.com)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
     (SID_NAME = dsdfdb)
    )
  )


LISTENER =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.0.52)(PORT = 1521))
  )


ADR_BASE_LISTENER =/u01/app/oracle


$ lsnrctl reload


主备库的tnsnames.ora
dsdfdbs_DGMGRL =
        (DESCRIPTION =
                (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST =10.255.0.52)(PORT = 1521))
                )
        (CONNECT_DATA = (SERVICE_NAME =dsdfdbm_DGMGRL.cuiyan2.com)
    )
  )
dsdfdbm_DGMGRL =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =10.255.0.51)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dsdfdbs_DGMGRL.cuiyan1.com)
    )
  )
dsdfdbm =
    (DESCRIPTION =
       (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.0.51)(PORT = 1521))
       )
      (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = dsdfdbm)
      )
    )
dsdfdbs =
    (DESCRIPTION =
       (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.0.52)(PORT = 1521))
       )
      (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = dsdfdbs)
      )
    )


5.修改主备库保护模式
一般创建完备库，默认的保护模式是最大性能,主备库都改
SQL> select database_role,protection_mode,protection_level from v$database;


DATABASE_ROLE PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE


切换到最高可用模式
SQL> alter database set standby database to maximize availability;


Database altered.


SQL> select database_role,protection_mode,protection_level from v$database;


DATABASE_ROLE PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY


6.创建配置及添加备库
[oracle@ibpsdba ~]$ dgmgrl
DGMGRL> connect sys/123456@dsdfdbm
Connected.


语法:
 
  CREATE CONFIGURATION <configurationname> AS
    PRIMARY DATABASE IS <database name>   --主库的DB_UNIQUE_NAME
CONNECT IDENTIFIER IS <connect identifier>; --主库的TNS_NAME


  ADD DATABASE <database name>            --备库的DB_UNIQUE_NAME
    [AS CONNECT IDENTIFIER IS <connect identifier>]  --备库的TNS_NAME
    [MAINTAINED AS {PHYSICAL|LOGICAL}];


DGMGRL> create configuration 'Broker' as primary database is 'dsdfdbm' connect identifier is dsdfdbm_DGMGRL;
Configuration "Broker" created with primary database "dsdfdbm"


DGMGRL> add database 'dsdfdbs' as connect identifier is dsdfdbs_DGMGRL maintained as physical;
Database "dsdfdbs" added


DGMGRL> show configuration;


Configuration - Broker


  Protection Mode: MaxAvailability
  Databases:
    dsdfdbm - Primary database
    dsdfdbs - Physical standby database


Fast-Start Failover: DISABLED


Configuration Status:
DISABLED


DGMGRL> ENABLE CONFIGURATION;
Enabled.


DGMGRL> show configuration;


Configuration - Broker


  Protection Mode: MaxAvailability
  Databases:
    dsdfdbm - Primary database
    dsdfdbs - Physical standby database (disabled)


Fast-Start Failover: DISABLED


Configuration Status:
SUCCESS


DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.


DGMGRL> show configuration;


Configuration - Broker


  Protection Mode: MaxAvailability
  Databases:
    dsdfdbm - Primary database
      Warning: ORA-16819: fast-start failover observer not started


    dsdfdbs - (*) Physical standby database
      Warning: ORA-16819: fast-start failover observer not started


Fast-Start Failover: ENABLED


Configuration Status:
WARNING


DGMGRL> START OBSERVER FILE='/u01/app/oracle/product/11.2.0/db_1/dbs/dr1dsdfdbm.dat';
Observer started
注意:
*启动之后，该前台进程不会退出，会一直挂在这。 直到从其他窗口关闭。
*默认情况下，observer会创建一个二进制的文件 fsfo.dat来保存主库和备库的连接信息。 这个文件会在调用dgmgrl命令的当前窗口下生成。


DGMGRL> show configuration;


Configuration - Broker


  Protection Mode: MaxAvailability
  Databases:
    dsdfdbm - Primary database
    dsdfdbs - (*) Physical standby database


Fast-Start Failover: ENABLED


Configuration Status:

SUCCESS

测试故障转移


主库：
SQL> shutdown abort    
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area 3206836224 bytes
Fixed Size    2232640 bytes
Variable Size  654315200 bytes
Database Buffers 2533359616 bytes
Redo Buffers   16928768 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from
being opened
备库：
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 31970
Session ID: 1 Serial number: 165




SQL> select * from t1;
ERROR:
ORA-03114: not connected to ORACLE
退出重新登录
SQL> select open_mode from v$database;


OPEN_MODE
--------------------
READ WRITE
SQL> insert into t1 values(15,'9999');
1 row created.


DGMGRL> connect sys/123456@dsdfdbm
Connected.
DGMGRL> show configuration;
ORA-16795: the standby database needs to be re-created


Configuration details cannot be determined by DGMGRL


DGMGRL> connect sys/123456@dsdfdbs
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


Failed.


主库退出重新登录
SQL> select open_mode from v$database;


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