Oracle11g 开启db broker实现管理dg的目的
保证两边都设置的如下几个参数：
1.SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/data/u01/app/oracle/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db1';
System altered.
2.SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db1 LGWR aSYNC VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db1';
System altered.
3.SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/data/u01/app/oracle/oradata/db2/', '/data/u01/app/oracle/oradata/db1/' scope =spfile;
System altered.
4.SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT= '/data/u01/app/oracle/oradata/db2/', '/data/u01/app/oracle/oradata/db1/' scope =spfile;
System altered.
5.SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;
System altered.
6.SQL> ALTER SYSTEM SET FAL_CLIENT = db1 SCOPE=SPFILE;
System altered.
7.SQL> ALTER SYSTEM SET FAL_SERVER = db2 SCOPE=SPFILE;
System altered.
8.主库备库添加了standby logfile
9. SQL> alter database force logging;
Database altered.
前提：主库db2 备库db1
一：开启db broker
在主备库上各设置为true
SQL> alter system set dg_broker_start=true;
主备库：参数dg_broker_config_file，默认即可!当你后面创建了configuration 并且enable之后
就会在这个目录下生成相应的文件!
SQL> show parameter dg_broker_config_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /data/u01/app/oracle/product/1
1.2.0/dbhome_1/dbs/dr1db2.dat
dg_broker_config_file2 string /data/u01/app/oracle/product/1
1.2.0/dbhome_1/dbs/dr2db2.dat
修改dg_broker_config_file参数。这里就用默认的路径，也可以自己指定。如果是在RAC环境中，这个把这个文件把到共享的存储上面，如果有ASM可以放到ASM中。
二.主备库listener.ora添加db broker切换使用的监听服务，因为dg broker使用的服务名就是db_unique_name_DGMGRL，注：如果不修改监听利用原有的监听也是可以得，下面会介绍这种方法!
2.1 vim listener.ora,添加下面的红色部分!一定注意格式，严格对齐
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.21.178)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=db2)
(ORACLE_HOME=/data/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=db2))
(SID_DESC=
(GLOBAL_DBNAME=db2_DGMGRL)
(ORACLE_HOME=/data/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=db2)))
ADR_BASE_LISTENER = /data/u01/app/oracle
2.2 重新加载监听：
[oracle@beijing-fuli-Hadoop-02 admin]$ lsnrctl reload
三.配置 db broker
3.1.连接主库
[oracle@beijing-fuli-hadoop-02 admin]$ dgmgrl sys/oracle@db2
3.2.创建配置
DGMGRL> help
The following commands are available:
add Adds a standby database to the broker configuration
connect Connects to an Oracle database instance
convert Converts a database from one type to another
create Creates a broker configuration
disable Disables a configuration, a database, or fast-start failover
edit Edits a configuration, database, or instance
enable Enables a configuration, a database, or fast-start failover
exit Exits the program
failover Changes a standby database to be the primary database
help Displays description and syntax for a command
quit Exits the program
reinstate Changes a database marked for reinstatement into a viable standby
rem Comment to be ignored by DGMGRL
remove Removes a configuration, database, or instance
show Displays information about a configuration, database, or instance
shutdown Shuts down a currently running Oracle database instance
sql Executes a SQL statement
start Starts the fast-start failover observer
startup Starts an Oracle database instance
stop Stops the fast-start failover observer
switchover Switches roles between a primary and standby database
DGMGRL> help create
Creates a broker configuration
Syntax:
CREATE CONFIGURATION <configuration name> AS
PRIMARY DATABASE IS <database name>
CONNECT IDENTIFIER IS <connect identifier>;
DGMGRL> CREATE CONFIGURATION dbha_c AS PRIMARY DATABASE IS db2 CONNECT IDENTIFIER IS db2;
(解释：其中 dbha_c是随便起的名字，db2是主的数据库唯一名，identifier is是tnsnames.ora的网络服务名! 然后可以使用如下来删除创建的configuration!
DGMGRL> REMOVE CONFIGURATION;)
3.3添加备库的信息
DGMGRL> help add
DGMGRL> add database 'db1' as connect identifier is 'db1' maintained as physical;
Database "db1" added
注意identifier is 'db1' 这里的db1是你tnsnames.ora里面关于备库的那个名字!
添加了之后
3.4启动这个db broker的CONFIGURATION!
DGMGRL> help enable
DGMGRL> ENABLE CONFIGURATION;
3.5查看db broker的的相关配置
DGMGRL> help show
DGMGRL> SHOW CONFIGURATION;
如下报错：
DGMGRL> show configuration
Configuration - dbha_c
Protection Mode: MaxPerformance
Databases:
db2 - Primary database
db1 - Physical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
解决办法：
将主从库的这个目录下的文件删除，然后重新create configuration，即可!
SQL> show parameter dg_broker_config_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /data/u01/app/oracle/product/1
1.2.0/dbhome_1/dbs/dr1db2.dat
dg_broker_config_file2 string /data/u01/app/oracle/product/1
1.2.0/dbhome_1/dbs/dr2db2.dat
解释：这两文件的作用?
如下显示代表正常!!!
DGMGRL> show configuration
Configuration - dbha_c
Protection Mode: MaxPerformance
Databases:
db2 - Primary database
db1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
查看数据库的状态：
DGMGRL> show database verbose db1;
#这里要注意了。broker里面的连接的service_name是<db_unique_name>_DGMGRL,所以上面要修改一下监听!
如果不修改监听文件的话，那也可以执行下面的命令来修改StaticConnectIdentifier: