Oracle 11g DG Broker开启和配置

255次阅读

共计 5917 个字符，预计需要花费 15 分钟才能阅读完成。

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:

DGMGRL>edit database db1 set property StaticConnectIdentifier= ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beijing-fuli-hadoop-01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db1)(INSTANCE_NAME=db1)(SERVER=DEDICATED)))’;

DGMGRL> edit database db2 set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beijing-fuli-hadoop-02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db2)(INSTANCE_NAME=db2)(SERVER=DEDICATED)))’;

四：关于 db broker 的相关命令：

4.1：切换主从!

DGMGRL> help switchover

DGMGRL> switchover to db1

Performing switchover NOW, please wait…

Operation requires a connection to instance “db1” on database “db1”

Connecting to instance “db1″…

Connected.

New primary database “db1” is opening…

Operation requires startup of instance “db2” on database “db2”

Starting instance “db2″…

ORACLE instance started.