【DG】之 Snapshot standby模式

Oracle物理備庫互轉快照備庫.txt

Snapshot standby 模式，即在備庫進行，開啟此模式時為了在備庫進行一些測試操作，而又不

留存在資料庫中，當備庫切換回physical standby物理備庫時，
之前在snapshot standby模式進行的測試將會被丟棄。

Oracle 11g 物理 Data Guard 之 Snapshot Standby 資料庫功能

注意： 1. 需首先 確認備庫已經 結束日誌應用 了！

       2.snapshot standby 模式時 ，閃回資料庫功能可開啟也也可關閉， 預設是關閉狀態； 但必須設定快速恢復區大小及路徑

——查詢狀態：（本次是 不開閃回資料庫功能）

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

NO

 

SQL> select database_role,open_mode from v$database;          

 

DATABASE_ROLE     OPEN_MODE

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

PHYSICAL STANDBY   READ ONLY

 

——進行切換到 snapshot standby 模式：

SQL> alter database convert to snapshot standby; （是在 open 狀態下操作的）

 

Database altered.

 

——再次檢視狀態：（變為 mount ）

SQL> select database_role,open_mode from v$database;

 

DATABASE_ROLE     OPEN_MODE

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

SNAPSHOT STANDBY MOUNTED

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

RESTORE POINT ONLY

 

——最後啟動資料庫至 open 下（已經變為 read write ，可以做任何測試操作了）

SQL> alter database open;

 

Database altered.

 

SQL> select database_role,open_mode from v$database;

 

DATABASE_ROLE     OPEN_MODE

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

SNAPSHOT STANDBY   READ WRITE

 

SQL>

 

此時可以檢視 alert 日誌，會發現資料庫建立了一個 guaranteed restore point ，確保我們切回主備，可應用日誌。

=========================================================================

alter database convert to snapshot standby

Starting background process RVWR

Thu Oct 20 19:52:16 2016

RVWR started with pid=53, OS id=10342

Allocated 3981120 bytes in shared pool for flashback generation buffer

Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_10/20/2016 19:52:16

Killing 3 processes with pids 10290,10294,10298 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 10039

All dispatchers and shared servers shutdown

==========================================================================

 

——此時備庫已經開啟，可以做任何測試，測試如下：

SQL> show user

USER is "SYS"

SQL> create table hr.st(x int);

 

Table created.

 

SQL> insert into hr.st values(1);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from hr.st;

 

         X

----------

          1

 

—— 恢復物理備庫，資料庫需要在 mount 下完成切換：

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

Total System Global Area   830930944 bytes

Fixed Size                   2257800 bytes

Variable Size              679480440 bytes

Database Buffers           146800640 bytes

Redo Buffers                 2392064 bytes

Database mounted.

SQL>

SQL> alter database convert to physical standby ;

 

Database altered.

 

切換完成後，資料庫需要再次重啟至 mount

SQL> shutdown immediate;

ORA-01507: database not mounted

 

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area   830930944 bytes

Fixed Size                   2257800 bytes

Variable Size              679480440 bytes

Database Buffers           146800640 bytes

Redo Buffers                 2392064 bytes

Database mounted.

 

——應用日誌，使同步：

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

Media recovery complete.

SQL>

SQL> select database_role,open_mode from v$database;

 

DATABASE_ROLE     OPEN_MODE

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

PHYSICAL STANDBY MOUNTED

 

SQL> recover managed standby database cancel;

Media recovery complete.

SQL>

SQL> alter database open;

 

Database altered.

 

SQL> select database_role,open_mode from v$database;

 

DATABASE_ROLE     OPEN_MODE

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

PHYSICAL STANDBY READ ONLY

 

——最後驗證 snapshot standby 模式下建立的表（正常是不存在的）

SQL> select * from hr.st;

select * from hr.st

                 *

ERROR at line 1:

ORA-00942: table or view does not exist

結束！！！！！！！