redhat 6.4 oracle11g dataguard 物理备库切换快照备库演示

概述：oracle dg中对于备库的角色我们一般有physical standby物理备库、snapshot standby 快照备库以及logic standby 逻辑备库三种角色，不同的角色将会满足不同的需求；此文将演示物理备库切换到快照备库的过程。物理备库我们基本上只能够以只读的模式供业务使用，当遇到需要数据库升级，应用软件需要在相同的负载下使用新版本的功能，我们可以快速将物理备库切换至逻辑备库，在对逻辑备库进行操作到相应的条件进行测试，等到测试完成后我们在切换回物理备库进行数据同步就ok啦。

实验：命令相当简单只要物理备库搭建完成后，只需一条命令就完成了，这里也做一个记录供以后参考；

查看备库状态：

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,FLASHBACK_ON from v$database;


OPEN_MODE                                PROTECTION_MODE                DATABASE_ROLE                    SWITCHOVER_STAT FLASH
---------------------------------------- ------------------------------ -------------------------------- --------------- -----
READ ONLY WITH APPLY                     MAXIMUM AVAILABILITY           PHYSICAL STANDBY                 NOT ALLOWED     NO

备库闪回开启为可选项

切换物理备库为逻辑备库：

SQL> aLTER DATABASE CONVERT TO SNAPSHOT STANDBY;
aLTER DATABASE CONVERT TO SNAPSHOT STANDBY
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_01/08/2018 19:16:20'.
ORA-01153: an incompatible media recovery is active

提示需要将日志恢复进程关闭

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;


Database altered.


SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2257880 bytes
Variable Size             545262632 bytes
Database Buffers          289406976 bytes
Redo Buffers                2355200 bytes
Database mounted.
Database opened.

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE                                PROTECTION_MODE                DATABASE_ROLE                    SWITCHOVER_STAT
---------------------------------------- ------------------------------ -------------------------------- ---------------
READ WRITE                               MAXIMUM AVAILABILITY           SNAPSHOT STANDBY                 NOT ALLOWED

主库都切换日志

SQL> alter system switch logfile ;


System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL>

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/fast_recovery_area
Oldest online log sequence     72
Next log sequence to archive   74
Current log sequence           74

查看备库归档日志应用情况以及传输情况：

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


 SEQUENCE# APPLIED
---------- ------------------
        50 YES
        51 YES
        52 YES
        53 YES
        54 YES
        55 YES
        56 YES
        57 YES
        58 YES
        59 YES
        60 YES


 SEQUENCE# APPLIED
---------- ------------------
        61 YES
        62 YES
        63 YES
        64 YES
        65 YES
        66 NO
        67 NO
        68 NO
        69 NO
        70 NO
        71 NO
        72 NO
        73 NO


24 rows selected.

[root@jakki fast_recovery_area]# ls
1_31_964541157.dbf  1_39_964541157.dbf  1_47_964541157.dbf      1_55_964541157.dbf  1_63_964541157.dbf  1_71_964541157.dbf
1_32_964541157.dbf  1_40_964541157.dbf  1_48_964541157.dbf      1_56_964541157.dbf  1_64_964541157.dbf  1_72_964541157.dbf
1_33_964541157.dbf  1_41_964541157.dbf  1_49_964541157.dbf.bak  1_57_964541157.dbf  1_65_964541157.dbf  1_73_964541157.dbf
1_34_964541157.dbf  1_42_964541157.dbf  1_50_964541157.dbf      1_58_964541157.dbf  1_66_964541157.dbf  JAKKI
1_35_964541157.dbf  1_43_964541157.dbf  1_51_964541157.dbf      1_59_964541157.dbf  1_67_964541157.dbf
1_36_964541157.dbf  1_44_964541157.dbf  1_52_964541157.dbf      1_60_964541157.dbf  1_68_964541157.dbf
1_37_964541157.dbf  1_45_964541157.dbf  1_53_964541157.dbf      1_61_964541157.dbf  1_69_964541157.dbf
1_38_964541157.dbf  1_46_964541157.dbf  1_54_964541157.dbf      1_62_964541157.dbf  1_70_964541157.db

此时备库归档日志已经传输完毕但是未能够应用，需要等到切换至物理备库后再进行应用；

备库dml操作：

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;


OPEN_MODE                                PROTECTION_MODE                DATABASE_ROLE                    SWITCHOVER_STAT
---------------------------------------- ------------------------------ -------------------------------- ---------------
READ WRITE                               MAXIMUM AVAILABILITY           SNAPSHOT STANDBY                 NOT ALLOWED

此时备库数据库处于可读写操作，我们可以对备库进行我们的应用测试，譬如创建表，对表进行dml操作测试；对备库进行数据升级等等

SQL> create table dg_t (id number,name varchar2(20));


Table created.


SQL> insert into dg_t values （11,'cube');

1 row created.

SQL> insert into dg_t values (2,'jakki');

1 row created.

SQL> insert into dg_t values (3,'haha');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dg_t;

        ID NAME
---------- ----------------------------------------
        11 cube
         2 jakki
         3 haha


SQL>  update dg_t set id=1 where name='cube'

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dg_t;


        ID NAME
---------- ----------------------------------------
         1 cube
         2 jakki
         3 haha

SQL> delete dg_t where id=3;


1 row deleted.


SQL> commit;


Commit complete.


SQL> select * from dg_t;


        ID NAME
---------- ----------------------------------------
         1 cube
         2 jakki


SQL> create table dg_t2 as select * from dg_t;


Table created.


SQL> drop table dg_t;


Table dropped.


SQL> select * from dg_t2;


        ID NAME
---------- ----------------------------------------
         1 cube
         2 jakki


SQL> select * from dg_t;
select * from dg_t
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/fast_recovery_area
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

备库是有自己的归档日志的

我们重新切换回physical standby进行查看：

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.


Total System Global Area  839282688 bytes
Fixed Size                  2257880 bytes
Variable Size             545262632 bytes
Database Buffers          289406976 bytes
Redo Buffers                2355200 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;


Database altered.


SQL> shutdown immediate;
ORA-01507: database not mounted




ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area  839282688 bytes
Fixed Size                  2257880 bytes
Variable Size             545262632 bytes
Database Buffers          289406976 bytes
Redo Buffers                2355200 bytes
Database mounted.
Database opened.
SQL> 

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;


OPEN_MODE                                PROTECTION_MODE                DATABASE_ROLE                    SWITCHOVER_STAT
---------------------------------------- ------------------------------ -------------------------------- ---------------
READ ONLY WITH APPLY                     MAXIMUM AVAILABILITY           PHYSICAL STANDBY                 NOT ALLOWED


SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


 SEQUENCE# APPLIED
---------- ------------------
        50 YES
        51 YES
        52 YES
        53 YES
        54 YES
        55 YES
        56 YES
        57 YES
        58 YES
        59 YES
        60 YES


 SEQUENCE# APPLIED
---------- ------------------
        61 YES
        62 YES
        63 YES
        64 YES
        65 YES
        66 YES
        67 YES
        68 YES
        69 YES
        70 YES
        71 YES


 SEQUENCE# APPLIED
---------- ------------------
        72 YES
        73 YES
        74 YES
        75 YES
        76 YES
        77 YES
        78 IN-MEMORY


29 rows selected.

重新与主库进行物理同步了 ；

总结:至此physical standby 切换snapshot standby 实验完成，了解不同的角色能够启动的作用，以满足不同业务的需求；