ORACLE 11G-DATAGUARD使用snapshot特性打开数据库并恢复

DG正常运行于最大可用模式，DG2是备库。snapshot特性其实就是做一个快照了。

1.切换为SNAPSHOT STANDBY

BYS@dg2>select * from test;
         A
----------
       999
         8
BYS@dg2>conn / as sysdba
Connected.
SYS@dg2>alter database convert to snapshot standby;
alter database convert to snapshot standby
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_08/08/2013 18:52:19'.
ORA-01153: an incompatible media recovery is active

2.需要先关闭redo应用并转化为snapshot standby; 进行DML操作。

SYS@dg2>alter database recover managed standby database cancel;
Database altered.
SYS@dg2> alter database convert to snapshot standby;
Database altered.
转换为 snapshot standby;后变为MOUNT状态。
SYS@dg2>select status from v$instance;
STATUS
------------
MOUNTED
SYS@dg2>alter database open;
Database altered.、
查看状态
SYS@dg2>select protection_mode,database_role,open_mode from v$database;
PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY SNAPSHOT STANDBY READ WRITE
SYS@dg2>conn bys/bys
Connected.
BYS@dg2>select * from test;
         A
----------
       999
         8
BYS@dg2>insert into test values(777);
1 row created.
BYS@dg2>commit;
Commit complete.
BYS@dg2>select * from test;
         A
----------
       999
         8
       777

3.转换为snapshot standby;时的相关日志：

Thu Aug 08 19:06:13 2013
alter database convert to snapshot standby
Starting background process RVWR
Thu Aug 08 19:06:13 2013
RVWR started with pid=28, OS id=5450
Allocated 3981204 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_08/08/2013 19:06:13
krsv_proc_kill: Killing 4 processes (all RFS)
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Thu Aug 08 19:06:15 2013
SMON: disabling cache recovery
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 1002248
Resetting resetlogs activation ID 1735170099 (0x676c9833)
Online log /u01/oradata/dg/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/oradata/dg/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/oradata/dg/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1002246
Thu Aug 08 19:06:16 2013
Setting recovery target incarnation to 4
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby

##########################################

4.再切换回PHYSICAL STANDBY并查询修改的数据是否被回退

BYS@dg2>conn / as sysdba
Connected.
SYS@dg2>shutdown immediate;
SYS@dg2>startup mount;
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             281020828 bytes
Database Buffers          130023424 bytes
Redo Buffers                6103040 bytes
Database mounted.
SYS@dg2>select protection_mode,database_role,open_mode from v$database;
PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY SNAPSHOT STANDBY MOUNTED
SYS@dg2>alter database convert to physical standby;
Database altered.
切换回physical standby后不知道为什么，系统 就变为STARTED状态了。
SYS@dg2>select protection_mode,database_role,open_mode from v$database;
select protection_mode,database_role,open_mode from v$database
                                                    *
ERROR at line 1:
ORA-01507: database not mounted
SYS@dg2>select status from v$instance;
STATUS
------------
STARTED
SYS@dg2>shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SYS@dg2>startup;
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             281020828 bytes
Database Buffers          130023424 bytes
Redo Buffers                6103040 bytes
Database mounted.
Database opened.
SYS@dg2>select protection_mode,database_role,open_mode from v$database;
PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY READ ONLY
SYS@dg2>alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SYS@dg2>conn bys/bys
Connected.
查询时数据，已经恢复到启动SNAPSHOT STANDBY之前的状态了。
BYS@dg2>select * from test;
         A
----------
       999
         8

5.切换回PHYSICAL STANDBY相关日志：

Thu Aug 08 19:09:33 2013
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (dg)
krsv_proc_kill: Killing 4 processes (all RFS)
Flashback Restore Start
Flashback Restore Complete
Stopping background process RVWR
Deleted Oracle managed file /u01/flash_recovery_area/DG2/flashback/o1_mf_906yx53k_.flb
Guaranteed restore point  dropped
Clearing standby activation ID 1736279770 (0x677d86da)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Completed: alter database convert to physical standby