Oracle 11g Data Guard Broker操作筆記，11gbroker

Oracle 11g Data Guard Broker操作筆記，11gbroker

轉載請註明出處：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