物理ADG的snapshot standby测试
作者：胡少瑞 sunny Hu
一：确定主备库均为打开闪回数据库功能，本例中主库为dg1,备库为dg2. 1
二：重新启动备库到mount状态，进行转换操作，转换完成后可以看到闪回的进程rvwr已经自动启动 2
四：打开数据库，期间观察切换过程日志如下，注意红色字体部分，实际上是产生了一个闪回数据库的保障点 3
六：主库上执行日志切换，观察主库的日志输入，红色字体部分说明日志有继续传输到dg2 5
七：查看快照数据库，已经多了一个incarnation，日志序列号从1开始... 6
八:重新转换快照数据库到物理备库状态，查看相关的日志信息... 7
九：重新将备库置于ADG模式下，之前的测试数据已经不存在... 8
11g的DataGuard提供了一个快照数据库(snapshot standby)的功能，也就是可以将物理备库临时以读写的方式打开，主库的归档日志会继续传输到备库，但备库不应用，等到将备库重新切换为physical standby的时候继续应用，从而实现主库和备库的数据一致，值得一提的是这个功能不需要开启主备库的闪回数据库功能，先前在这里存在误区,这个功能还是比较赞的，可以直接拿物理备库做下临时测试数据库！
一：确定主备库均为打开闪回数据库功能，本例中主库为dg1,备库为dg2
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 dg1 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 WITH APPLY PHYSICAL STANDBY dg2 NO
二：重新启动备库到mount状态，进行转换操作，转换完成后可以看到闪回的进程rvwr已经自动启动
SQL> alter database recover managed standby database cancel;
Database altered.
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 dg2 NO
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 343936304 bytes
Database Buffers 62914560 bytes
Redo Buffers 8466432 bytes
Database mounted.
SQL> alter database convert to snapshot standby;
Database altered.
SQL> !ps -ef |grep rvwr
oracle 14767 1 0 15:20 00:00:00 ora_rvwr_dg
oracle 14788 14756 0 15:24 pts/4 00:00:00 /bin/bash -c ps -ef |grep rvwr
oracle 14790 14788 0 15:24 pts/4 00:00:00 grep rvwr
四：打开数据库，期间观察切换过程日志如下，注意红色字体部分，实际上是产生了一个闪回数据库的保障点
SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
MOUNTED SNAPSHOT STANDBY dg2 RESTORE POINT ONLY
SQL> alter database open;
Database altered.
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 dg2 RESTORE POINT ONLY
[oracle@dg2~]$ tail -f /u01/app/oracle/diag/rdbms/dg2/dg/trace/alert_dg.log
Sat May 05 15:16:55 2012
alter database recover managed standby database cancel
Sat May 05 15:16:55 2012
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/dg2/dg/trace/dg_pr00_14678.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1107151
Sat May 05 15:16:56 2012
MRP0: Background Media Recovery process shutdown (dg)
Managed Standby Recovery Canceled (dg)
Completed: alter database recover managed standby database cancel
Sat May 05 15:20:03 2012
alter database convert to snapshot standby
Starting background process RVWR
Sat May 05 15:20:03 2012
RVWR started with pid=33, OS id=14767
Allocated 3981120 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_05/05/2012 15:20:03
krsv_proc_kill: Killing 4 processes (all RFS)
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Sat May 05 15:20:09 2012
SMON: disabling cache recovery
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1107151
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Resetting resetlogs activation ID 1695253094 (0x650b8266)
Online log /u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_1_7s76q5w1_.log: Thread 1 Group 1 was
previously cleared
Online log /u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_2_7s76q94s_.log: Thread 1 Group 2 was
previously cleared
Online log /u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_3_7s76qbhq_.log: Thread 1 Group 3 was
previously cleared
Standby became primary SCN: 1107149
Sat May 05 15:20:11 2012
Setting recovery target incarnation to 3
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
五：对快照数据库进行写数据操作
SQL> create user test identified by test;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
[oracle@dg1~]$ sqlplus test/test@dg2
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 5 15:28:42 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t1 (id number);
Table created.
SQL> insert into t1 values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
六：主库上执行日志切换，观察主库的日志输入，红色字体部分说明日志有继续传输到dg2
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 dg1 NO
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/standbylog
Oldest online log sequence 42
Next log sequence to archive 44
Current log sequence 44
SQL> alter system switch logfile;
System altered.
[oracle@dg1~]$ tail -f /u01/app/oracle/diag/rdbms/dg1/dg/trace/alert_dg.log
Sat May 05 15:34:10 2012
LGWR: Standby redo logfile selected to archive thread 1 sequence 45
LGWR: Standby redo logfile selected for thread 1 sequence 45 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 45 (LGWR switch)
Current log# 2 seq# 45 mem# 0: /u01/app/oracle/oradata/dg/redo02.log
Sat May 05 15:34:11 2012
Archived Log entry 72 added for thread 1 sequence 44 ID 0x650b8266 dest 1:
七：查看快照数据库，已经多了一个incarnation，日志序列号从1开始
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 dg2 RESTORE POINT ONLY
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/standbylog
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
[oracle@dg2~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 5 15:35:43 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DG (DBID=1694605607)
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 DG 1694605607 PARENT 1 2011-09-17-09:46:04
2 2 DG 1694605607 PARENT 995548 2012-04-22-12:43:25
3 3 DG 1694605607 CURRENT 1107152 2012-05-05-15:20:09
八:重新转换快照数据库到物理备库状态，查看相关的日志信息
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 348130608 bytes
Database Buffers 58720256 bytes
Redo Buffers 8466432 bytes
Database mounted.
SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
MOUNTED SNAPSHOT STANDBY dg2 RESTORE POINT ONLY
SQL> alter database convert to physical standby;
Database altered.
[oracle@dg2~]$ tail -f /u01/app/oracle/diag/rdbms/dg2/dg/trace/alert_dg.log
Sat May 05 15:40:10 2012
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (dg)
krsv_proc_kill: Killing 3 processes (all RFS)
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Stopping background process RVWR
Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/DG2/flashback/o1_mf_7t9o53gj_.flb
Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/DG2/flashback/o1_mf_7t9o57qv_.flb
Guaranteed restore point dropped
Clearing standby activation ID 1695816619 (0x65141bab)
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;
Shutting down archive processes
Archiving is disabled
Sat May 05 15:40:19 2012
ARCH shutting down
ARC3: Archival stopped
Sat May 05 15:40:19 2012
ARCH shutting down
ARC2: Archival stopped
Sat May 05 15:40:19 2012
ARCH shutting downSat May 05 15:40:19 2012
ARCH shutting down
ARC0: Archival stopped
ARC1: Archival stopped
Sat May 05 15:40:20 2012
Completed: alter database convert to physical standby
九：重新将备库置于ADG模式下，之前的测试数据已经不存在
SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
select open_mode,database_role,db_unique_name,flashback_on from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 348130608 bytes
Database Buffers 58720256 bytes
Redo Buffers 8466432 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 ONLY WITH APPLY PHYSICAL STANDBY dg2 NO
SQL> select * from test.t1;
select * from test.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from dba_users where username='TEST';
no rows selected
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30109892/viewspace-2091365/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30109892/viewspace-2091365/
896
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
