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表不存在了，恢复到转换之前的状态了！

ITPub博客

Oracle 11R2 snapshot Data Guard

最新文章