客户某生产业务增量生产时发现数据库会切换Snapshot standby模式。进行学习测试。
一、原理说明
在Oracle 11g中,data guard最吸引人的active data guard的实时查询特性(即可以以只读方式打开物理standby数据库的同时MRP进程能继续做recover),快照备用数据库这个特性也是不错,比较适用于快速部署一个临时的与线上环境相同的测试数据库.它是通过还原点(restore point)和闪回数据库的原理(flashback database),可以以读/写方式打开物理备用数据库,对数据库进行修改,之后再根据还原点,恢复到物理备用数据库。
Snapshot standby模式，即在备库进行，开启此模式时为了在备库进行一些测试操作，而又不行留存在数据库中，当备库切换回physical standby物理备库时，之前在snapshot standby模式进行的测试将会被丢弃
备注:
物理standby是最高保护模式(maximum protection),是不能转换为snapshot standby的.
物理standby使用了standby redo log,在create restore point后,要alter system switch logfile;,以保证还原点的scn在物理standby库上是归档的,不然可能无法成功闪回到还原点.
物理standby在切换为快照standby后,如果间隔很长时间,primary数据库产生的大量的重做日志,这样可以在转换为物理standby后,通过对primary数据库的增量备份并recover到物理standby,来加快物理standby的还原速度.
注意：
1.需首先确认备库已经结束日志应用了！（本次测试因为没有确认导致部分步骤报错）
2.snapshot standby模式时，闪回数据库功能可开启也也可关闭，默认是关闭状态；但必须设置快速恢复区大小及路径
二、备库节点node2测试步骤：
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 19 21:46:34 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> 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_06/15/2018
02:27:25'.
ORA-01153: an incompatible media recovery is active
查询网络资料：
ORA-01153: an incompatible media recovery is active
Cause: Attempted to start an incompatible media recovery or open resetlogs
during media recovery or RMAN backup . Media recovery sessions are
incompatible if they attempt to recover the same data file. Incomplete media
recovery or open resetlogs is incompatible with any media recovery. Backup or
restore by RMAN is incompatible with open resetlogs
Action: Complete or cancel the other media recovery session or RMAN backup
解决方法：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress
原因：开启备库应用日志之后没取消应用日志，直接启库，报错
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY PHYSICAL STANDBY
SQL> 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_06/15/2018
02:46:02'.
ORA-38786: Recovery area is not enabled.
创建一个目录用于存放快速恢复区
[root@node2 oradata]# su - oracle
[oracle@node2 ~]$ cd /oradata/
[oracle@node2 oradata]$ mkdir recovery
SQL> show parameter recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 4182M
recovery_parallelism integer 0
SQL> alter system set db_recovery_file_dest='/oradata/recovery';
System altered.
SQL> alter system set db_recovery_file_dest_size=2G;
System altered.
解决完报错就可以执行快照
SQL> alter database convert to snapshot standby;
Database altered.
三、查看快照信息（可以查看alert日志，会发现数据库创建了一个guaranteed restore point,确保我们切回主备，可应用日志)
[oracle@node2 trace]$ tail -f alert_orcls.log
alter database convert to snapshot standby
Starting background process RVWR
Fri Jun 15 02:51:28 2018
RVWR started with pid=19, OS id=7250
Allocated 3981120 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_06/15/2018 02:51:28
Killing 3 processes with pids 7193,7197,7195 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 7182
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
四、查看备库数据库状态，进行测试
SQL>select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED SNAPSHOT STANDBY
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED SNAPSHOT STANDBY
SQL> alter database open;
Database altered.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE SNAPSHOT STANDBY
做测试
SQL> CREATE TABLE ZHU (A INT);
Table created.
SQL> INSERT INTO ZHU VALUES (1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM ZHU;
A
----------
1
五、 恢复物理备库，数据库需要在mount下完成切换 ，并验证测试
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 264241152 bytes
Redo Buffers 2633728 bytes
Database mounted.
SQL> alter database convert to physical standby;
Database altered.
SQL> select status from v$instance;
STATUS
------------
STARTED
切换完成后需要重启到mount应用日志
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 264241152 bytes
Redo Buffers 2633728 bytes
Database mounted.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
SQL> alter database open;
Database altered.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY
验证在SNAPSHOT STANDBY模式下创建的表
SQL> select * from ZHU;
select * from ZHU
*
ERROR at line 1:
ORA-00942: table or view does not exist
