【DataGuard】Oracle 11g physical standby switchover

環境：DataGuard physical standby

主庫：Oracle 11g RAC

備庫：Oracle 11g 單例項

 

主備切換

主庫切換為備庫

 

查詢主庫狀態

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

---------------- --------------------

PRIMARY  TO STANDBY

 

查詢備庫狀態

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY NOT ALLOWED

 

關閉主庫另外一個節點例項

[root@rac2 ~]# srvctl stop instance -d enmo -n rac2

 

[root@rac2 ~]# srvctl status database -d enmo

Instance enmo1 is running on node rac1

Instance enmo2 is not running on node rac2

 

查詢備庫是否啟用最大效能模式

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

如果不是執行：

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

 

查詢備庫日誌是否全部應用

SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

no rows selected

 

主庫切換為備庫

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

 

查詢新備庫狀態

SQL>  select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY RECOVERY NEEDED

 

將新備庫啟動到mount狀態

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size            2232960 bytes

Variable Size           620760448 bytes

Database Buffers    209715200 bytes

Redo Buffers          2396160 bytes

Database mounted.

 

備庫切換為主庫

查詢備庫狀態

SQL>  select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY SWITCHOVER PENDING

 

說明：SWITCHOVER_STATUS正常應該是to_primary或session active。

 

查詢日誌應用情況

SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

---------- ---------- --------- --------- ---------

     1     144 31-JUL-14 31-JUL-14 NO

     1     145 31-JUL-14 31-JUL-14 NO

 

說明：日誌沒有完全應用。開啟日誌應用

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

 

查詢末應用日誌

SQL>  select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

no rows selected

 

查詢備庫狀態

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY TO PRIMARY

 

備庫切換為主庫

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

 

查詢新主庫狀態

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

---------------- --------------------

PRIMARY  NOT ALLOWED

 

Open新的主庫

SQL> alter database open;

Database altered.

 

在新的備庫（原來的主庫）開啟日誌應用

SQL>  alter database recover managed standby database using current logfile disconnect from session;

Database altered.

 

查詢新主庫狀態

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

---------------- --------------------

PRIMARY  TO STANDBY

 

查詢新備庫狀態

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY NOT ALLOWED

 

日誌應用驗證

備庫查詢日誌應用情況

SQL> select THREAD#,SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log order by 2 ;

THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

---------- ---------- --------- --------- ---------

     1     146 03-AUG-14 03-AUG-14 YES

     1     147 03-AUG-14 03-AUG-14 YES

     1     148 03-AUG-14 03-AUG-14 YES

 

 

主庫切換日誌

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

 

備庫查詢日誌應用情況
SQL> select THREAD#,SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log order by 2 ;

THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

---------- ---------- --------- --------- ---------

     1     146 03-AUG-14 03-AUG-14 YES

     1     147 03-AUG-14 03-AUG-14 YES

     1     148 03-AUG-14 03-AUG-14 YES

     1     149 03-AUG-14 03-AUG-14 YES

     1     150 03-AUG-14 03-AUG-14 YES

 

主庫建立使用者

SQL> create user test01 identified by test ;

User created.

 

備庫查詢此使用者

SQL> select * from dba_user where username='TEST01';

select * from dba_user where username='TEST01'

              *

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

 

說明：備庫沒有open read only。

 

備庫開啟read only 模式

SQL> alter database open read only;

alter database open read only

*

ERROR at line 1:

ORA-10456: cannot open standby database; media recovery session may be in

Progress

 

說明：需要重啟例項。

 

備庫重啟到read only

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup open read only;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size            2232960 bytes

Variable Size           620760448 bytes

Database Buffers    209715200 bytes

Redo Buffers          2396160 bytes

Database mounted.

Database opened.

備庫開啟日誌應用

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

重新查詢使用者是否建立

SQL> select username from dba_users where username='TEST01';

USERNAME

---------

TEST01               

 

重新切換回原主備庫

主庫切換為備庫

 

查詢主庫狀態

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

---------------- -------------------- --------------------

PRIMARY  TO STANDBY        READ WRITE

 

查詢備庫狀態

SQL> select database_role,switchover_status,open_mode from gv$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

---------------- -------------------- --------------------

PHYSICAL STANDBY NOT ALLOWED         READ ONLY WITH APPLY

PHYSICAL STANDBY NOT ALLOWED         READ ONLY WITH APPLY

 

關閉備庫另一個例項

[root@rac2 ~]# srvctl stop instance -d enmo -n rac2

 

查詢是否最大效能模式

SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE      PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

 

查詢備庫日誌是否全部應用

SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

---------- ---------- --------- --------- ---------

     2      14 28-JUL-14 28-JUL-14 NO

     1     144 31-JUL-14 31-JUL-14 NO

     1     145 31-JUL-14 31-JUL-14 NO

 

主庫切換成備庫並啟動到mount狀態

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

 

查詢新備庫狀態

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

---------------- -------------------- --------------------

PHYSICAL STANDBY RECOVERY NEEDED      READ WRITE

 

新備庫啟動到mount狀態

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size            2232960 bytes

Variable Size           524291456 bytes

Database Buffers    306184192 bytes

Redo Buffers          2396160 bytes

Database mounted.

 

查詢新備庫狀態

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

---------------- -------------------- --------------------

PHYSICAL STANDBY RECOVERY NEEDED      MOUNTED

 

 

備庫切換為主庫

查詢備庫狀態

SQL> select database_role,switchover_status,open_mode from gv$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

---------------- -------------------- --------------------

PHYSICAL STANDBY TO PRIMARY          READ ONLY WITH APPLY

 

將備庫切換為主庫

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

 

查詢新主庫狀態

SQL> select database_role,switchover_status,open_mode from gv$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

---------------- -------------------- --------------------

PRIMARY  NOT ALLOWED       MOUNTED

 

open新主庫

SQL> alter database open ;

Database altered.

 

新備庫開啟read only

SQL> alter database open read only;

Database altered.

 

新備庫開啟日誌應用

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


------------------end--------------------
            
                DBA_建瑾
                2014.8.19