oracle 11g dg broker開啟和配置

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.

Database mounted.

Database opened.

Switchover succeeded, new primary is "db1"

DGMGRL>

4.2：把從庫切換成snapshot狀態!(前提是開啟了flashback database)

主庫操作

DGMGRL> SHOW CONFIGURATION

DGMGRL> help convert

DGMGRL> CONVERT DATABASE db1 TO SNAPSHOT STANDBY;

DGMGRL> SHOW CONFIGURATION;

4.3: 修改下dg同步redo的方式，以及保護模式!

DGMGRL> help edit

DGMGRL> EDIT DATABASE db2 SET PROPERTY LogXptMode=SYNC; ---主庫

DGMGRL> EDIT DATABASE db1 SET PROPERTY LogXptMode=SYNC; ---備庫

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability; ---主庫

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability; ---備庫