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
转载于:https://blog.51cto.com/5073392/1313598
扫一扫
392
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
