oracle dg snapshot,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