oracle11g pysical standby開啟臨時讀寫

1、確定和檢查相關資訊

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
資料庫處於mount狀態下，是物理standby
檢查當前模式是否在閃回的條件下
SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
NO

透過檢查，standby閃回沒有開啟，在進行切換時需要開啟閃回

2、對standby資料庫進行操作
--取消日誌在standby端的應用
SQL> alter database recover managed standby database cancel;

Database altered.

--設定閃回區域和閃回大小
SQL> alter system set db_recovery_file_dest_size = 40g;

System altered.

SQL> alter system set db_recovery_file_dest = '/u01/db_recover';

System altered.

另外，建立閃回目錄
mkdir /u01/db_recover
chown -R oracle:oinstall /u01/db_recover

--切換到snap database狀態
SQL> shutdown immediate
ORA-01109: database not open

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

Total System Global Area 8551575552 bytes
Fixed Size                  2270360 bytes
Variable Size            2147486568 bytes
Database Buffers         6392119296 bytes
Redo Buffers                9699328 bytes
Database mounted.

SQL> alter database convert to snapshot standby;
Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED

--開啟資料庫

SQL> alter database open;
Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE
可以看到資料庫已經處理可讀寫的狀態了

4、對snap database進行讀寫測試

--切換日誌     
SQL> alter system switch logfile;

System altered.

--建表測試

SQL> create table mytest as select * from dba_tables;

Table created.

SQL> create table mytest1 as select * from dba_tables;

Table created.

SQL> select count(*) from dba_tables;

  COUNT(*)
----------
      4622

SQL> drop table mytest purge;

Table dropped.

5、測試完成後，還原到physical standby

--關閉資料庫並切換到mount狀態
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 8551575552 bytes
Fixed Size                  2270360 bytes
Variable Size            2147486568 bytes
Database Buffers         6392119296 bytes
Redo Buffers                9699328 bytes
Database mounted.

--切換到physical standby狀態
SQL> alter database convert to physical standby;

Database altered.

--關閉閃回
SQL> alter database flashback off;

Database altered.

--重新開啟資料庫physical standby模式
SQL> shutdown immediate;
ORA-01507: database not mounted

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

Total System Global Area 8551575552 bytes
Fixed Size                  2270360 bytes
Variable Size            2147486568 bytes
Database Buffers         6392119296 bytes
Redo Buffers                9699328 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION NODELAY;
Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

--檢查剛才的表
SQL> select count(*) from mytest1;
select count(*) from mytest1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
剛才建立的mytest1表已經不存在了

6、說明
需要注意的點：
--10g中建立的還原點必須在閃回後進行刪除，否則閃回恢復區可能會被撐滿，導致DB Hang住。11g中不用擔心這個問題，在重新轉換為physical standby時會自動進行還原點刪除。
--在進行轉換時，需要設定閃回區
--物理standby是最高保護模式(maximum protection),是不能轉換為snapshot standby的.
--物理standby使用了standby redo log,在create restore point後,要alter system switch logfile，以保證還原點的scn在物理standby庫上是歸檔的,不然可能無法成功閃回到還原點.
--物理standby在切換為快照standby後,如果間隔很長時間,primary資料庫產生的大量的重做日誌,這樣可以在轉換為物理standby後,透過對primary資料庫的增量備份並recover到物理standby,來加快物理standby的還原速度。