Snapshot standby模式

    客戶某生產(chǎn)業(yè)務(wù)增量生產(chǎn)時發(fā)現(xiàn)數(shù)據(jù)庫會切換Snapshot standby模式。進(jìn)行學(xué)習(xí)測試。

一、原理說明

在Oracle 11g中,data guard最吸引人的active data guard的實(shí)時查詢特性(即可以以只讀方式打開物理standby數(shù)據(jù)庫的同時MRP進(jìn)程能繼續(xù)做recover),快照備用數(shù)據(jù)庫這個特性也是不錯,比較適用于快速部署一個臨時的與線上環(huán)境相同的測試數(shù)據(jù)庫.它是通過還原點(diǎn)(restore point)和閃回?cái)?shù)據(jù)庫的原理(flashback database),可以以讀/寫方式打開物理備用數(shù)據(jù)庫,對數(shù)據(jù)庫進(jìn)行修改,之后再根據(jù)還原點(diǎn),恢復(fù)到物理備用數(shù)據(jù)庫。

Snapshot standby模式，即在備庫進(jìn)行，開啟此模式時為了在備庫進(jìn)行一些測試操作，而又不行留存在數(shù)據(jù)庫中，當(dāng)備庫切換回physical standby物理備庫時，之前在snapshot standby模式進(jìn)行的測試將會被丟棄

備注:

物理standby是最高保護(hù)模式(maximum protection),是不能轉(zhuǎn)換為snapshot standby的.

物理standby使用了standby redo log,在create restore point后,要alter system switch logfile;,以保證還原點(diǎn)的scn在物理standby庫上是歸檔的,不然可能無法成功閃回到還原點(diǎn).

物理standby在切換為快照standby后,如果間隔很長時間,primary數(shù)據(jù)庫產(chǎn)生的大量的重做日志,這樣可以在轉(zhuǎn)換為物理standby后,通過對primary數(shù)據(jù)庫的增量備份并recover到物理standby,來加快物理standby的還原速度.

注意：

1.需首先確認(rèn)備庫已經(jīng)結(jié)束日志應(yīng)用了?。ū敬螠y試因?yàn)闆]有確認(rèn)導(dǎo)致部分步驟報(bào)錯）

2.snapshot standby模式時，閃回?cái)?shù)據(jù)庫功能可開啟也也可關(guān)閉，默認(rèn)是關(guān)閉狀態(tài)；但必須設(shè)置快速恢復(fù)區(qū)大小及路徑

二、備庫節(jié)點(diǎn)node2測試步驟：

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 19 21:46:34 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select status from v$instance;

STATUS

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

OPEN

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_06/15/2018

02:27:25'.

ORA-01153: an incompatible media recovery is active

查詢網(wǎng)絡(luò)資料：

ORA-01153: an incompatible media recovery is active

Cause: Attempted to start an incompatible media recovery or open resetlogs

during media recovery or RMAN backup . Media recovery sessions are

incompatible if they attempt to recover the same data file. Incomplete media

recovery or open resetlogs is incompatible with any media recovery. Backup or

restore by RMAN is incompatible with open resetlogs

Action: Complete or cancel the other media recovery session or RMAN backup

解決方法：

SQL> alter database recover managed standby database cancel;

Database altered.

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

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

MOUNTED              PHYSICAL STANDBY

SQL> alter database open;

alter database open

*

ERROR at line 1:

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

原因：開啟備庫應(yīng)用日志之后沒取消應(yīng)用日志，直接啟庫，報(bào)錯

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

READ ONLY            PHYSICAL STANDBY

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_06/15/2018

02:46:02'.

ORA-38786: Recovery area is not enabled.

創(chuàng)建一個目錄用于存放快速恢復(fù)區(qū)

[root@node2 oradata]# su - oracle

[oracle@node2 ~]$ cd /oradata/

[oracle@node2 oradata]$ mkdir recovery

SQL> show parameter recovery;

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 4182M

recovery_parallelism                 integer     0

SQL> alter system set db_recovery_file_dest='/oradata/recovery';

System altered.

SQL> alter system set db_recovery_file_dest_size=2G;

System altered.

解決完報(bào)錯就可以執(zhí)行快照

SQL> alter database convert to snapshot standby;

Database altered.

三、查看快照信息（可以查看alert日志，會發(fā)現(xiàn)數(shù)據(jù)庫創(chuàng)建了一個guaranteed restore point,確保我們切回主備，可應(yīng)用日志)

[oracle@node2 trace]$ tail -f alert_orcls.log 

alter database convert to snapshot standby

Starting background process RVWR

Fri Jun 15 02:51:28 2018

RVWR started with pid=19, OS id=7250 

Allocated 3981120 bytes in shared pool for flashback generation buffer

Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_06/15/2018 02:51:28

Killing 3 processes with pids 7193,7197,7195 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 7182

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

CLOSE: all sessions shutdown successfully.

四、查看備庫數(shù)據(jù)庫狀態(tài)，進(jìn)行測試

SQL>select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

MOUNTED              SNAPSHOT STANDBY

SQL>  select flashback_on from v$database;

FLASHBACK_ON

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

RESTORE POINT ONLY

SQL>  select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

MOUNTED              SNAPSHOT STANDBY

SQL> alter database open;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

READ WRITE           SNAPSHOT STANDBY

做測試

SQL> CREATE TABLE ZHU (A INT);

Table created.

SQL> INSERT INTO ZHU VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM ZHU;

         A

----------

         1

五、 恢復(fù)物理備庫，數(shù)據(jù)庫需要在mount下完成切換 ，并驗(yàn)證測試         

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  776646656 bytes

Fixed Size                  2257272 bytes

Variable Size             507514504 bytes

Database Buffers          264241152 bytes

Redo Buffers                2633728 bytes

Database mounted.

SQL> alter database convert to physical standby;

Database altered.

SQL> select status from v$instance;

STATUS

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

STARTED

切換完成后需要重啟到mount應(yīng)用日志         

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  776646656 bytes

Fixed Size                  2257272 bytes

Variable Size             507514504 bytes

Database Buffers          264241152 bytes

Redo Buffers                2633728 bytes

Database mounted.

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

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

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

PHYSICAL STANDBY MOUNTED

SQL>  alter database open; 

Database altered.

SQL>  select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

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

PHYSICAL STANDBY READ ONLY

驗(yàn)證在SNAPSHOT STANDBY模式下創(chuàng)建的表

SQL> select * from ZHU;

select * from ZHU

              *

ERROR at line 1:

ORA-00942: table or view does not exist