Oracle在11r1就開始支援snapshot data guard了，本次測試版本為11.2.0.3
1、取消DG端恢復模式
SYS@jzh>select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
SYS@jzh>recover managed standby database cancel;
Media recovery complete.
2、開啟flashback
SYS@jzh>alter database flashback on;
Database altered.
SYS@jzh>alter system set db_recovery_file_dest='/u01/oracle/fast_recovery_area' scope=spfile;-------閃回目錄
System altered.
SYS@jzh>alter system set db_recovery_file_dest_size=3g; -----閃回區大小
System altered.
SYS@jzh>show parameter flash
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string
db_flash_cache_size big integer 0
db_flashback_retention_target integer 1440 --------指定資料庫可以回退的時間，單位為分鐘，預設1440分鐘，也就是一天
3、關閉資料庫並啟動到mount狀態
SYS@jzh>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@jzh>startup mount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
SYS@jzh>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
4、轉換snapshot data guard
SYS@jzh>alter database convert to snapshot standby;
Database altered.
5、關閉資料庫並啟動至read、write模式
SYS@jzh>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@jzh>startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
Database opened.
SYS@jzh>select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
JZH READ WRITE SNAPSHOT STANDBY
6、建立測試表並插入資料
SYS@jzh>create table snapshot (id int,name varchar2(20));
Table created.
SYS@jzh>insert into snapshot values(001,'shanghai');
1 row created.
SYS@jzh>insert into snapshot values(002,'beijing');
1 row created.
SYS@jzh>commit;
Commit complete.
SYS@jzh>select * from snapshot;
ID NAME
---------- --------------------
1 shanghai
2 beijing
7、轉換成physical standby
SYS@jzh>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@jzh>startup mount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
SYS@jzh>alter database convert to physical standby;
Database altered.
8、將備庫啟動至read only模式
SYS@jzh>shu immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@jzh>startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
Database opened.
SYS@jzh>select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
JZH READ ONLY PHYSICAL STANDBY
9、驗證測試表是否存在
SYS@jzh>select * from snapshot;
select * from snapshot
*
ERROR at line 1:
ORA-00942: table or view does not exist
可以看到snapshot表不存在了，恢復到轉換之前的狀態了！

Oracle 11R2 snapshot Data Guard

相關文章