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