ORACLE 11G SNAPSHOT STANDBY執行個體

ORACLE 11g PHYSICAL STANDBY已經不僅僅隻提供災難恢複功能，其ACTI VE DATA GUARD 特性讓備庫處于

RED ONLY狀态，可以提供做報表查詢、讀寫分離使用；還新增了SNAPSHOT STANDBY 特性，此特性能讓備庫

暫時處于可讀可寫狀态，為各種性能、壓力測試提供了環境，重要的是測試完成後又可轉換回PHYSICAL STANDBY，

備庫的資料繼續與主庫保持一緻。

SNAPSHOT STANDBY 限制及條件

1. 隻有PHYSICAL STANDBY能轉換為SNAPSHOT STANDBY。

2. DB_RECOVERY_FILE_DEST必需設定，這裡FLASHBACK DATABASE不是必須的。

3.隻有PHYSICAL STANDBY 處于mount模式才能轉換為SNAPSHOT STANDBY。

設定閃回恢複區

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=500m scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/arch/flasharch' scope=both;

System altered.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

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

檢視備庫目前模式

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

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE

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

PHYSICAL STANDBY ogg READ ONLY WITH APPLY

此時備庫為PHYSICAL STANDBY，正處于READ ONLY REAL TIME APPLY 模式

取消備庫恢複管理模式

SQL> alter database recover managed standby database cancel;

Database altered.

建立SNAPSHOT STANDBY

建立SNAPSHOT STANDBY 指令非常簡單

SQL> alter database convert to snapshot standby;

Database altered.

檢視alter log 裡面的資訊，指令已經執行完成，ORACLE建立了guaranteed restore point

Managed Standby Recovery Canceled (ogg)

Completed: alter database recover managed standby database cancel

Wed Oct 30 11:08:18 2013

alter database convert to snapshot standby

Starting background process RVWR

Wed Oct 30 11:08:18 2013

RVWR started with pid=26, OS id=5156

Allocated 3981204 bytes in shared pool for flashback generation buffer

Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_10/30/2013 11:08:18

檢視備庫模式已經變為SNAPSHOT STADNBY

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

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE

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

SNAPSHOT STANDBY ogg MOUNTED

檢視閃回恢複區已經産生檔案

SQL> !ls -lt /u01/arch/flasharch/OGG/flashback

total 102528

-rw-r----- 1 oracle oinstall 52436992 Oct 30 11:37 o1_mf_970y12xh_.flb

-rw-r----- 1 oracle oinstall 52436992 Oct 30 11:08 o1_mf_970y15og_.flb

開啟備庫，進行寫測試

SQL> alter database open;

Database altered.

在備庫建立一個新使用者

SQL> create user xhl_snapstb identified by xhl;

User created.

SQL> grant dba to xhl_snapstb;

Grant succeeded.

在備庫建立一個新表

SQL> create table xhl_snapstb.xhl as select * from dba_users;

Table created.

SQL> select count(*) from xhl_snapstb.xhl;

COUNT(*)

----------

删除原有表

SQL> select table_name,TABLESPACE_NAME from dba_tables where tablespace_name = 'TBS_XHL';

TABLE_NAME TABLESPACE_NAME

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

XHL TBS_XHL

SQL> DROP TABLE XHL PURGE;

Table dropped.

DROP TABLESPACE測試

Wed Oct 30 11:23:47 2013

drop tablespace TBS_XHL including contents and datafiles

ORA-38881 signalled during: drop tablespace TBS_XHL including contents and datafiles...

這個是不允許的

檢視日志傳輸狀态

在SNAPSHOT STANDBY模式下，日志正常傳輸但不應用，下面語句進行确認

SQL> select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIME

from V$DATAGUARD_STATS

WHERE NAME LIKE '%lag'; 2 3

CTIME NAME VALUE DATUM_TIME

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

20131030 11:34:35 transport lag +00 00:00:00 10/30/2013 11:34:34

20131030 11:34:35 apply lag +00 00:26:34 10/30/2013 11:34:34

transport lag 時間為0

apply lag 時間為26分34秒

我們将現在時間20131030 11:34:35 與之前 guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_10/30/2013 11:08:18

時間對比，剛好将近26分鐘

轉換回PHYSICAL STADNBY

現在我們轉換會PHYSICAL STANDBY ，确認在SANPSHOT STANDBY模式下所做的修改已經恢複，

必須在mount模式下進行操作

将資料庫切換到mount狀态

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

Total System Global Area 523108352 bytes

Fixed Size 1365740 bytes

Variable Size 318769428 bytes

Database Buffers 197132288 bytes

Redo Buffers 5840896 bytes

Database mounted.

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

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE

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

SNAPSHOT STANDBY ogg MOUNTED

convert to physical standby

SQL> alter database convert to physical standby;

Database altered.

檢視alter log 可以看到資料庫進行falshback restore完成，并将閃回恢複區的檔案delete

Wed Oct 30 11:40:36 2013

alter database convert to physical standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY (ogg)

Flashback Restore Start

Flashback Restore Complete

Drop guaranteed restore point

Stopping background process RVWR

Deleted Oracle managed file /u01/arch/flasharch/OGG/flashback/o1_mf_970y12xh_.flb

Deleted Oracle managed file /u01/arch/flasharch/OGG/flashback/o1_mf_970y15og_.flb

Guaranteed restore point dropped

Clearing standby activation ID 2931571730 (0xaebc3c12)

SQL> ! ls -lt /u01/arch/flasharch/OGG/flashback

total 0

重新開機資料庫到mount狀态

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

select database_role,db_unique_name,open_mode from v$database

ERROR at line 1:

ORA-01507: database not mounted

SQL> alter database mount;

alter database mount

ERROR at line 1:

ORA-00750: database has been previously mounted and dismounted

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 523108352 bytes

Fixed Size 1365740 bytes

Variable Size 318769428 bytes

Database Buffers 197132288 bytes

Redo Buffers 5840896 bytes

Database mounted.

确認PHYSICAL STANDBY狀态

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

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE

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

PHYSICAL STANDBY ogg MOUNTED

開啟為READ ONLY-- REAL TIME APPLY狀态

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,db_unique_name,open_mode from v$database;

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE

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

PHYSICAL STANDBY ogg READ ONLY WITH APPLY

檢視LAG情況

此時日志傳輸、日志應用LAG都為 0，備庫已經重新與主庫同步

SQL> select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIME

from V$DATAGUARD_STATS

WHERE NAME LIKE '%lag'; 2 3

CTIME NAME VALUE DATUM_TIME

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

20131030 12:13:23 transport lag +00 00:00:00 10/30/2013 12:13:22

20131030 12:13:23 apply lag +00 00:00:00 10/30/2013 12:13:22

确認在SNAPSHOT STANDBY下面的操作都已復原

SQL> select table_name,TABLESPACE_NAME from dba_tables where tablespace_name = 'TBS_XHL';

TABLE_NAME TABLESPACE_NAME

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

XHL TBS_XHL

被删除的表已經恢複

SQL> select count(*) from xhl_snapstb.xhl;

select count(*) from xhl_snapstb.xhl

ERROR at line 1:

ORA-00942: table or view does not exist

新增表的操作已經撤銷

1* SELECT USERNAME FROM DBA_USERS WHERE USERNAME= 'XHL_SNAPSTB'

SQL> /

no rows selected

新增使用者已經撤銷

總結：SNAPSHOT STANDBY 模式将備庫置于可讀寫狀态，可以在此備庫上來回折騰，這個

結合REAL APPLICATION TESTING 做更新前測試非常友善。要注意如果在SNAPSHOT STANDBY

上面的資料更改操作過大，恢複回PHYSICAL STANDBY的時間會非常長。

ORACLE 11G SNAPSHOT STANDBY執行個體

SNAPSHOT STANDBY 限制及條件

設定閃回恢複區

檢視備庫目前模式

取消備庫恢複管理模式

建立SNAPSHOT STANDBY

開啟備庫，進行寫測試

在備庫建立一個新使用者

在備庫建立一個新表

删除原有表

DROP TABLESPACE測試

檢視日志傳輸狀态

轉換回PHYSICAL STADNBY

将資料庫切換到mount狀态

convert to physical standby

重新開機資料庫到mount狀态

确認PHYSICAL STANDBY狀态

開啟為READ ONLY-- REAL TIME APPLY狀态

檢視LAG情況

确認在SNAPSHOT STANDBY下面的操作都已復原

繼續閱讀

快速删除ORACLE重複記錄

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List