首页 > 代码库 > Oracle 11g Data Guard Broker操作笔记
Oracle 11g Data Guard Broker操作笔记
转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/41548669
一、设置
1、设置broker
在主备库上各设置为true
SQL> alter system set dg_broker_start=true;
2、在主库上操作
[oracle@edsir5p17 admin]$ dgmgrl sys/oracle@PROD1
DGMGRL> help
DGMGRL> help create
DGMGRL> CREATE CONFIGURATION c1 AS PRIMARY DATABASE IS PROD1 CONNECT IDENTIFIER IS PROD1;
DGMGRL> help add
DGMGRL> ADD DATABASE DG AS CONNECT IDENTIFIER IS DG;
DGMGRL> help enable
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> help show
DGMGRL> SHOW CONFIGURATION;
3、备库操作
SQL> SQL> shutdown immediate;
SQL> startup open read only;
最好加一下read only,因为有时broker有时会把它拉回到mount状态！！！
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /flash
db_recovery_file_dest_size big integer 1G
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
二、snapshot
1、主库
DGMGRL> SHOW CONFIGURATION
DGMGRL> help convert
DGMGRL> CONVERT DATABASE DG TO SNAPSHOT STANDBY; ---如果这里有失败可能是监听DG_DGMGRL没设
DGMGRL> SHOW CONFIGURATION;
2、备库
变成TEST库后，确实可以写入数据
SQL> create table t1(id int,name varchar2(10));
Table created.
SQL> insert into t1 values(1,‘AAAA‘);
1 row created.
SQL> commit;
Commit complete.
三、还原到物理备库
1、主库
DGMGRL> help convert
DGMGRL> CONVERT DATABASE DG TO PHYSICAL STANDBY; ---如果这里有失败可能是监听DG_DGMGRL没设
四、来回切换库
1、在主库PROD1上操作
DGMGRL> SHOW CONFIGURATION
Configuration
Name: c1
Enabled: YES
Protection Mode: MaxPerformance
Databases:
prod1 - Primary database
dg - Physical standby database
Fast-Start Failover: DISABLED
Current status for "c1":
SUCCESS
DGMGRL> help switch
Unrecognized command "switch", try "help"
DGMGRL> help switchover
Switch roles between the primary database and a standby database
Syntax:
SWITCHOVER TO <standby database name>;
DGMGRL> SWITCHOVER TO DG;
Performing switchover NOW, please wait...
New primary database "dg" is opening...
Operation requires shutdown of instance "PROD1" on database "prod1"
Shutting down instance "PROD1"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "PROD1" on database "prod1"
Starting instance "PROD1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dg"
DGMGRL> SHOW CONFIGURATION
Configuration
Name: c1
Enabled: YES
Protection Mode: MaxPerformance
Databases:
dg - Primary database
prod1 - Physical standby database
Fast-Start Failover: DISABLED
Current status for "c1":
SUCCESS
DGMGRL> SWITCHOVER TO PROD1
Performing switchover NOW, please wait...
New primary database "prod1" is opening...
Operation requires shutdown of instance "DG" on database "dg"
Shutting down instance "DG"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "DG" on database "dg"
Starting instance "DG"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod1"
五、修改传输模式
DGMGRL> SHOW DATABASE VERBOSE PROD1
DGMGRL> help edit
DGMGRL> EDIT DATABASE PROD1 SET PROPERTY LogXptMode=SYNC; ---主库
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability; ---主库
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed.
这个错误的主要原因是（主库和备库都要改EDIT DATABASE XXXX SET PROPERTY）
DGMGRL> EDIT DATABASE PROD1 SET PROPERTY LogXptMode=SYNC; ---主库
DGMGRL> EDIT DATABASE DG SET PROPERTY LogXptMode=SYNC; ---备库
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability; ---主库
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability; ---备库
以上Error: ORA-16627: 错误马上消失。。。。
++++备库的一些基本命令可以记下：
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
六、故障切换
1、主库
DGMGRL> help enable;
DGMGRL> ENABLE FAST_START FAILOVER;
2、备库
[oracle@edsir5p18 ~]$ dgmgrl sys/oracle@DG
DGMGRL> help start
DGMGRL> START OBSERVER
3、主库
[oracle@edsir5p17 ~]$ ps -ef |grep pmon
oracle 3444 1 0 16:41 ? 00:00:00 ora_pmon_PROD1
oracle 4534 29892 0 17:04 pts/2 00:00:00 grep pmon
oracle 10888 1 0 09:03 ? 00:00:00 ora_pmon_PROD2
[oracle@edsir5p17 ~]$ kill -9 3444
4、备库
（1）发现信息
DGMGRL> START OBSERVER
Observer started
17:05:29.04 Friday, November 21, 2014
Initiating Fast-Start Failover to database "dg"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "dg"
17:05:37.62 Friday, November 21, 2014
（2）stop observer（观察到上面的信息就停止掉，以防来回切换）
用ctr+c即可
5、主库
[oracle@edsir5p17 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Fri Nov 21 17:10:14 2014
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1313792 bytes
Variable Size 176161792 bytes
Database Buffers 234881024 bytes
Redo Buffers 6127616 bytes
Database mounted.
6、切回去
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: c1
Enabled: YES
Protection Mode: MaxAvailability
Databases:
dg - Primary database
prod1 - Physical standby database (disabled)
- Fast-Start Failover target
Fast-Start Failover: ENABLED
Current status for "c1":
Warning: ORA-16607: one or more databases have failed
DGMGRL> SWITCHOVER TO PROD1
Performing switchover NOW, please wait...
Error: ORA-16541: database is not enabled
Failed.
DGMGRL> DISABLE FAST_START FAILOVER;
Error: ORA-16652: fast-start failover target standby database is disabled
Failed.
DGMGRL> DISABLE FAST_START FAILOVER force;
Disabled.
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: c1
Enabled: YES
Protection Mode: MaxAvailability
Databases:
dg - Primary database
prod1 - Physical standby database (disabled)
Fast-Start Failover: DISABLED
Current status for "c1":
Warning: ORA-16608: one or more databases have warnings
DGMGRL> switchover to prod1; ----------------------------------->这个错误的一般出现在VM中。。。
Performing switchover NOW, please wait...
Error: ORA-16541: database is not enabled
Failed.
Unable to switchover, primary database is still "dg"
DGMGRL> show database verbose prod1
Current status for "prod1":
Error: ORA-16661: the standby database needs to be reinstated
实在搞不定，重置了
DGMGRL> reinstate database PROD1
Reinstating database "prod1", please wait...
Operation requires shutdown of instance "PROD1" on database "prod1"
Shutting down instance "PROD1"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "PROD1" on database "prod1"
Starting instance "PROD1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "prod1" ...
Reinstatement of database "prod1" succeeded
DGMGRL> show configuration;
Configuration
Name: c1
Enabled: YES
Protection Mode: MaxAvailability
Databases:
dg - Primary database
prod1 - Physical standby database
Fast-Start Failover: DISABLED
Current status for "c1":
Warning: ORA-16610: command "EDIT DATABASE prod1 SET PROPERTY" in progress
DGMGRL> SWITCHOVER TO PROD1
Performing switchover NOW, please wait...
New primary database "prod1" is opening...
Operation requires shutdown of instance "DG" on database "dg"
Shutting down instance "DG"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "DG" on database "dg"
Starting instance "DG"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod1"
DGMGRL> show configuration;
Configuration
Name: c1
Enabled: YES
Protection Mode: MaxAvailability
Databases:
prod1 - Primary database
dg - Physical standby database
Fast-Start Failover: DISABLED
Current status for "c1":
SUCCESS
Oracle 11g Data Guard Broker操作笔记