Oracle 11g物理Data Guard之Snapshot Standby数据库功能

Oracle 11g的Data Guard不仅仅带给我们的是Active Data Guard实时查询特性，参见文章《【DataGuard】Oracle 11g物理Active Data Guard实时查询（Real-time query）特性》（http://space.itpub.net/519536/viewspace-718742）。同时还带来了另外一个惊喜，这便是Snapshot Standby数据库功能，此项功能可将备库置身于“可读写状态”用于不方便在生产环境主库中测试的内容，比如模拟上线测试等任务。当备库读写状态下任务完成后，可以非常轻松的完成Snapshot Standby数据库角色切换回备库角色，恢复与主库数据同步。在Snapshot Standby数据库状态下，备库是可以接受主库传过来的日志，但是不能够将变化应用在备库中。

1.停止Redo Apply
  如果备库正处于Redo Apply过程，需要先取消。
sys@ora11gdg> alter database recover managed standby database cancel;

Database altered.

2.查看当前备库状态确保备库处于MOUNTED状态
sys@ora11gdg> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

  此时备库是物理备库角色，运行模式是MOUNTED。

3.确保闪回恢复区已指定
  友情提示：实现Snapshot Standby数据库功能并不需要开启主库和备库的闪回数据库（Flashback Database）功能，与是否开启闪回数据库无关。
sys@ora11gdg> show parameter db_recovery_file_dest

NAME                        TYPE         VALUE
--------------------------- ------------ ------------------------------------
db_recovery_file_dest       string       /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size  big integer  3852M

  确认主库闪回功能并未开启
sys@ora11g> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
NO

  确认备库闪回功能并未开启
sys@ora11gdg> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
NO

4.调整备库到Snapshot Standby数据库状态
  只需要执行一条非常简单的SQL命令便可以将备库调整到Snapshot Standby数据库。
sys@ora11gdg> alter database convert to snapshot standby;

Database altered.

sys@ora11gdg> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED

5.将备库置于对外可读写状态
sys@ora11gdg> alter database open;

Database altered.

sys@ora11gdg> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE

  一套全新的可读写数据库展现在我们面前。

6.分析切换过程中的日志信息
ora11g主库alert日志：
Mon Mar 19 18:46:28 2012
LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact
Error 3135 for archive log file 2 to 'ora11gdg'
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 2 thread 1 sequence 50 (3135)
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact

ora11gdg备库alert日志：
Mon Mar 19 18:46:26 2012
alter database convert to snapshot standby
Starting background process RVWR
Mon Mar 19 18:46:26 2012
RVWR started with pid=26, OS id=8824
Allocated 3981204 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/19/2012 18:46:26
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 1472476
Resetting resetlogs activation ID 4174194338 (0xf8cd26a2)
Online log /u01/app/oracle/oradata/ora11gdg/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/ora11gdg/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/ora11gdg/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1472474
Mon Mar 19 18:46:29 2012
Setting recovery target incarnation to 5
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby

  关键的一行提示信息“Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/19/2012 18:46:26”，这里给出了我们转换成snapshot的时刻，便于后面的回切。

7.测试备库处于Snapshot Standby数据库对主库日志的接收
  当主库切换日志时，备库依然可以接收到日志，只是并不应用
1）主库切换日志
sys@ora11g> alter system switch logfile;

System altered.

2）主库记录的alert日志内容
ora11g主库alert日志：
Mon Mar 19 18:52:00 2012
Thread 1 cannot allocate new log, sequence 52
Private strand flush not complete
  Current log# 3 seq# 51 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
Mon Mar 19 18:52:00 2012
ARC3: Standby redo logfile selected for thread 1 sequence 50 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 52 (LGWR switch)
  Current log# 1 seq# 52 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
Mon Mar 19 18:52:03 2012
Archived Log entry 91 added for thread 1 sequence 51 ID 0xf8cd26a2 dest 1:
Mon Mar 19 18:52:03 2012
LNS: Standby redo logfile selected for thread 1 sequence 51 for destination LOG_ARCHIVE_DEST_2
LNS: Standby redo logfile selected for thread 1 sequence 52 for destination LOG_ARCHIVE_DEST_2

ora11gdg备库alert日志：
Mon Mar 19 18:52:00 2012
RFS[5]: Assigned to RFS process 9174
RFS[5]: Identified database type as 'snapshot standby': Client is ARCH pid 27296
Mon Mar 19 18:52:00 2012
RFS[6]: Assigned to RFS process 9176
RFS[6]: Identified database type as 'snapshot standby': Client is ARCH pid 27300
RFS[6]: Selected log 4 for thread 1 sequence 50 dbid -120744030 branch 778023141
Mon Mar 19 18:52:00 2012
Archived Log entry 47 added for thread 1 sequence 50 ID 0xf8cd26a2 dest 1:
Mon Mar 19 18:52:03 2012
RFS[7]: Assigned to RFS process 9180
RFS[7]: Identified database type as 'snapshot standby': Client is LGWR ASYNC pid 27302
RFS[7]: Selected log 4 for thread 1 sequence 51 dbid -120744030 branch 778023141
Mon Mar 19 18:52:04 2012
Archived Log entry 48 added for thread 1 sequence 51 ID 0xf8cd26a2 dest 1:
RFS[7]: Selected log 4 for thread 1 sequence 52 dbid -120744030 branch 778023141

3）查看主库和备库归档目录下的日志文件内容
（1）主库归档日志文件
ora11g@secdb /home/oracle/arch/ora11g$ ls -ltr
total 879M
……省略其他……
-rw-r----- 1 oracle oinstall  1.1M Mar 19 18:51 1_50_778023141.arc
-rw-r----- 1 oracle oinstall  363K Mar 19 18:52 1_51_778023141.arc

（2）备库归档日志文件
ora11g@secdb /home/oracle/arch/ora11gdg$ ls -ltr
total 847M
……省略其他……
-rw-r----- 1 oracle oinstall  1.1M Mar 19 18:52 1_50_778023141.arc
-rw-r----- 1 oracle oinstall  363K Mar 19 18:52 1_51_778023141.arc

  可见，备库已经接受到主库发过来的日志。

8.在Snapshot Standby数据创建用户和表并初始化数据
sys@ora11gdg> create user ocmu identified by ocmu;

User created.

secooler@ora11gdg> grant dba to ocmu;

Grant succeeded.

secooler@ora11gdg> conn ocmu/ocmu
Connected.
ocmu@ora11gdg> create table t (x varchar2(8));

Table created.

ocmu@ora11gdg> insert into t values ('Secooler');

1 row created.

ocmu@ora11gdg> commit;

Commit complete.

ocmu@ora11gdg> select * from t;

X
--------
Secooler

  结论，此时备库是一个可任意修改和调整的状态，也就是我们要的“READ WRITE”可读写状态。
  特别注意的是，原理上实现Snapshot Standby数据库功能是基于闪回数据原理的，因此任何导致闪回数据库无法回退的动作在这里也要规避，否则Snapshot Standby数据库将无法回到曾经的备库恢复状态。

9.恢复Snapshot Standby数据库为Physical Standby数据库
1）重启备库到MOUNTED状态
ocmu@ora11gdg> conn / as sysdba
Connected.
sys@ora11gdg> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ora11gdg> startup mount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             268438616 bytes
Database Buffers           37748736 bytes
Redo Buffers                6336512 bytes
Database mounted.

sys@ora11gdg> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED

2）一条命令恢复原物理备库身份
sys@ora11gdg> alter database convert to physical standby;

Database altered.

3）备库的alert日志清楚的记录了这个切换的过程
Mon Mar 19 19:30:24 2012
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (ora11gdg)
Flashback Restore Start
Flashback Restore Complete
Stopping background process RVWR
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORA11GDG/flashback/o1_mf_7pg3n2jc_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORA11GDG/flashback/o1_mf_7pg52yst_.flb
Guaranteed restore point  dropped
Clearing standby activation ID 4174523254 (0xf8d22b76)
The primary database controlfile was created using the
'MAXLOGFILES 30' clause.
There is space for up to 27 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

  从alert日志中可以得到恢复方法使用的闪回数据库功能实现的，也就是说，即便备库没有运行在闪回数据库状态，依然可以使用闪回数据库功能完成备库的角色转换。

4）重启备库到自动恢复日志状态
（1）此时数据库处于NOMOUNTED状态，需要重新启动数据库。
  注意这里是重启数据库，而不是使用alter命令调整，否则会收到如下报错：
sys@ora11gdg> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted


sys@ora11gdg> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
sys@ora11gdg> startup mount;
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             268438616 bytes
Database Buffers           37748736 bytes
Redo Buffers                6336512 bytes
Database mounted.
sys@ora11gdg> alter database recover managed standby database disconnect;

Database altered.

（2）查看备库alert日志，可以清楚的看到恢复的过程。
Mon Mar 19 19:43:48 2012
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u01/app/oracle/oradata/ora11gdg/redo01.log
Clearing online log 1 of thread 1 sequence number 1
Completed: alter database recover managed standby database disconnect
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/ora11gdg/redo02.log
Clearing online log 2 of thread 1 sequence number 2
Clearing online redo logfile 2 complete
Media Recovery Log /home/oracle/arch/ora11gdg/1_49_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_50_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_51_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_52_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_53_778023141.arc
Media Recovery Log /home/oracle/arch/ora11gdg/1_54_778023141.arc
Media Recovery Waiting for thread 1 sequence 55

（3）查看V$ARCHIVED_LOG动态性能视图查看日志应用情况
sys@ora11gdg> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;

 SEQUENCE# FIRST_TIME        NEXT_TIME         APPLIED
---------- ----------------- ----------------- ---------
……省略其他数据……
        49 20120319 18:32:32 20120319 18:38:03 YES
        50 20120319 18:38:03 20120319 18:51:00 YES
        51 20120319 18:51:00 20120319 18:52:03 YES
        52 20120319 18:52:03 20120319 19:09:57 YES
        53 20120319 19:09:57 20120319 19:10:15 YES
        54 20120319 19:10:15 20120319 19:10:25 YES

52 rows selected.

10.开启备库到READ ONLY状态验证之前在Snapshot Standby数据库上的操作已撤销
sys@ora11gdg> alter database recover managed standby database cancel;

Database altered.

sys@ora11gdg> alter database open read only;

Database altered.

sys@ora11gdg> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

sys@ora11gdg> select username from dba_users where username = 'OCMU';

no rows selected

之前创建的测试用户OCMU不存在。结论得证。

11.小结

  这便是神奇的“Snapshot Standby数据库”功能，备库可以临时成为一个可读写的独立数据库，这极大的扩展了备库的应用场合，我们可以使用备库的这一项特殊功能将那些在生产环境中“不敢”模拟和再现的问题在备库端进行测试，测试完毕后再恢复其物理备库的身份进行日志恢复。

转载自http://blog.itpub.net/519536/viewspace-719056/，感谢原作者secooler