配置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
