snapshot standby 可以将物理备库以读写方式打开，打开的时候，snapshot standby 会继续接受来自主库的redo 归档，但是备库并不会应用，可以提供临时测试使用，做一些数据临时修改，当测试完成后在恢复到打开snapshot前的状态，然后继续和主库保持同步状态，另外这种方式不需要开启flasback就可以恢复到打开前的状态。


配置步骤如下：

主库：DGWH

备库：DGBJ


1）由于flashback 功能是开启，这里先关闭它。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.


Total System Global Area 318046208 bytes

Fixed Size 1299652 bytes

Variable Size 293604156 bytes

Database Buffers 16777216 bytes

Redo Buffers 6365184 bytes

Database mounted.

SQL> alter database flashback off;


Database altered.


SQL> alter database open;


Database altered.


2）检查主备库配置角色

SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;


OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON

---------- ---------------- ------------------------------ ------------------

READ WRITE PRIMARY DGWH NO


SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;


OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON

---------- ---------------- ------------------------------ ------------------

READ ONLY PHYSICAL STANDBY DGBJ NO


3）重启standby数据库到mount状态，进行转换操作，并且跟踪alter日志。

SQL> startup mount;

ORACLE instance started.


Total System Global Area 318046208 bytes

Fixed Size 1299652 bytes

Variable Size 293604156 bytes

Database Buffers 16777216 bytes

Redo Buffers 6365184 bytes

Database mounted.

SQL>


SQL> alter database convert to snapshot standby;


Database altered.


--执行切换命令后警告日志文件如下：


alter database convert to snapshot standby

db_recovery_file_dest_size of 2048 MB is 7.71% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Allocated 3981204 bytes in shared pool for flashback generation buffer

Starting background process RVWR --启用了RVWR后台进程

Wed Oct 16 19:32:14 2013

RVWR started with pid=23, OS id=31120

Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_10/16/2013 19:32:14

RESETLOGS after incomplete recovery UNTIL CHANGE 10415243

Resetting resetlogs activation ID 776839207 (0x2e4da027)

Online log /u01/app/oracle/oradata/DGBJ/onlinelog/o1_mf_1_95wr3j1h_.log: Thread 1 Group 1 was previously cleared

Online log /u01/app/oracle/flash_recovery_area/DGBJ/onlinelog/o1_mf_1_95wr3yr7_.log: Thread 1 Group 1 was previously cleared

Online log /u01/app/oracle/oradata/DGBJ/onlinelog/o1_mf_2_95wr5lry_.log: Thread 1 Group 2 was previously cleared

Online log /u01/app/oracle/flash_recovery_area/DGBJ/onlinelog/o1_mf_2_95wr6nh5_.log: Thread 1 Group 2 was previously cleared

Online log /u01/app/oracle/oradata/DGBJ/onlinelog/o1_mf_3_95wr8nhh_.log: Thread 1 Group 3 was previously cleared

Online log /u01/app/oracle/flash_recovery_area/DGBJ/onlinelog/o1_mf_3_95wr9xsq_.log: Thread 1 Group 3 was previously cleared --清楚了先前的online log

Standby became primary SCN: 10415241 --恢复后，会回到这个点

Wed Oct 16 19:32:14 2013

Setting recovery target incarnation to 3

Converting standby mount to primary mount.

ACTIVATE STANDBY: Complete - Database mounted as primary (DGBJ)

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY: Complete (DGBJ)

Completed: alter database convert to snapshot standby


4）重启snapshot到open状态

SQL> shutdown immediate;

ORA-01507: database not mounted



ORACLE instance shut down.

SQL> startup;

ORACLE instance started.


Total System Global Area 318046208 bytes

Fixed Size 1299652 bytes

Variable Size 293604156 bytes

Database Buffers 16777216 bytes

Redo Buffers 6365184 bytes

Database mounted.

Database opened.


--检查转换后的状态

SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;


OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON

---------- ---------------- ------------------------------ ------------------

READ WRITE SNAPSHOT STANDBY DGBJ RESTORE POINT ONLY


5）对以读写方式打开的snapshot standby进行写操作


--在备库删除之前的表t

SQL> select * from t;


ID

----------

3

4

1

2


SQL> drop table t;


Table dropped.


--在主库更新表t

SQL> insert into t values (5);


1 row created.


SQL> commit;


Commit complete.


--在备库创建表t1

SQL> create table t1 as select rownum id from dual connect by rownum < 8;


Table created.


--在主库执行日志切换，跟踪alter日志，可以看到还在继续往主库传送日志。

LGWR: Standby redo logfile selected to archive thread 1 sequence 697

LGWR: Standby redo logfile selected for thread 1 sequence 697 for destination LOG_ARCHIVE_DEST_2 --发送sequence 697

Thread 1 advanced to log sequence 697

Current log# 2 seq# 697 mem# 0: /u01/app/oracle/oradata/DGWH/onlinelog/o1_mf_2_96bj1jvq_.log

Current log# 2 seq# 697 mem# 1: /u01/app/oracle/flash_recovery_area/DGWH/onlinelog/o1_mf_2_96bj25wh_.log


-- 备库的序列号从1开始

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/arch1/DGBJ

Oldest online log sequence 1

Next log sequence to archive 1

Current log sequence 1

SQL>


--连接到rman ，多了个incarnation

[oracle@localhost ~]$ rman target /


Recovery Manager: Release 11.1.0.6.0 - Production on Wed Oct 16 19:50:39 2013


Copyright (c) 1982, 2007, Oracle. All rights reserved.


connected to target database: DGWH (DBID=773380365)


RMAN> list incarnation;


using target database control file instead of recovery catalog


List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1 1 DGWH 773380365 PARENT 1 03-AUG-07

2 2 DGWH 773380365 PARENT 522753 13-SEP-13

3 3 DGWH 773380365 CURRENT 10415244 16-OCT-13


6）恢复到physical standby

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.


Total System Global Area 318046208 bytes

Fixed Size 1299652 bytes

Variable Size 293604156 bytes

Database Buffers 16777216 bytes

Redo Buffers 6365184 bytes

Database mounted.

SQL> alter database convert to physical standby;


Database altered.


SQL> shutdown immediate;

ORA-01507: database not mounted



ORACLE instance shut down.

SQL> startup;

ORACLE instance started.


Total System Global Area 318046208 bytes

Fixed Size 1299652 bytes

Variable Size 293604156 bytes

Database Buffers 16777216 bytes

Redo Buffers 6365184 bytes

Database mounted.

Database opened.

--警告日志文件

alter database convert to physical standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY (DGBJ)

Flashback Restore Start

Flashback Restore Complete

Stopping background process RVWR

Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/DGBJ/flashback/o1_mf_95wy9yg2_.flb

Guaranteed restore point dropped

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;

Setting recovery target incarnation to 2

Completed: alter database convert to physical standby


SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;


OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON

---------- ---------------- ------------------------------ ------------------

READ ONLY PHYSICAL STANDBY DGBJ NO


7) 转换回physical standby的验证

SQL> select * from t1;

select * from t1

*

ERROR at line 1:

ORA-00942: table or view does not exist

在snapshot下创建的表t1已经没有了


SQL> select *from t;


ID

----------

3

4

1

2

表t已经恢复


SQL> alter database recover managed standby database using current logfile disconnect from session;


Database altered.

SQL> select * from t;


ID

----------

3

4

5

1

2

在转换过程中，主库中insert的id=5已经在备库恢复完成,警告日志记录恢复操作如下：


Media Recovery Log /u01/arch1/DGBJ/log1_690_826058126.arc

Media Recovery Log /u01/arch1/DGBJ/log1_691_826058126.arc

Media Recovery Log /u01/arch1/DGBJ/log1_692_826058126.arc

Media Recovery Log /u01/arch1/DGBJ/log1_693_826058126.arc

Wed Oct 16 20:00:33 2013

Media Recovery Log /u01/arch1/DGBJ/log1_694_826058126.arc

Media Recovery Log /u01/arch1/DGBJ/log1_695_826058126.arc

Media Recovery Log /u01/arch1/DGBJ/log1_696_826058126.arc

Media Recovery Log /u01/arch1/DGBJ/log1_697_826058126.arc

Media Recovery Log /u01/arch1/DGBJ/log1_698_826058126.arc

Media Recovery Waiting for thread 1 sequence 699 (in transit)

Recovery of Online Redo Log: Thread 1 Group 5 Seq 699 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/DGBJ/onlinelog/standby02.log