Oracle Study之案例--Oracle 11g DataGuard Snapshot Standby
来源:互联网
作者:佚名
时间:2014-11-28 21:31
OracleStudy之案例--Oracle11gDataGuardSnapshotStandbyOracle11g的DataGuard不仅仅带给我们的是ActiveDataGuard实时查询特性,同时还带来了另外一个新特性,这便
Oracle Study之案例--Oracle 11g DataGuard Snapshot Standby
Oracle 11g的Data Guard不仅仅带给我们的是Active Data Guard实时查询特性,同时还带来了另外一个新特性,这便是Snapshot Standby数据库功能,此项功能可将备库置身于“可读写状态”用于不方便在生产环境主库中测试的内容,比如模拟上线测试等任务。当备库读写状态下任务完成后,可以非常轻松的完成Snapshot Standby数据库角色切换回备库角色,恢复与主库数据同步。在Snapshot Standby数据库状态下,备库是可以接收主库传过来的日志,但是不能对日志进行应用。
案例分析:
1、查看数据库信息Primay DB:
14:47:09 SYS@ prod >select name,database_role,protection_mode from v$database;
NAME DATABASE_ROLE PROTECTION_MODE
--------- ---------------- --------------------
PROD PRIMARY MAXIMUM PERFORMANCE
14:46:52 SYS@ prod >select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
536
Standby DB:
14:47:04 SYS@ shdb >select name,database_role,protection_mode from v$database;
NAME DATABASE_ROLE PROTECTION_MODE
--------- ---------------- --------------------
PROD PHYSICAL STANDBY MAXIMUM PERFORMANCE
14:46:09 SYS@ shdb >select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
536
2、切换备库到Snapshot Standby1)必须终止Media Recover Process
14:48:05 SYS@ shdb >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_11/27/2014 14:50:05'.
ORA-01153: an incompatible media recovery is active
14:50:05 SYS@ shdb >recover managed standby database cancel;
Media recovery complete.
2)必须建立Recover Area
snapshot standby实际上是基于flashback database的运行机制,恢复到原先的standby状态
14:50:44 SYS@ shdb >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_11/27/2014 14:50:58'.
ORA-38786: Recovery area is not enabled.
3)启用recover area
14:50:58 SYS@ shdb >show parameter recover
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0
14:52:51 SYS@ shdb >alter system set db_recovery_file_dest_size=2g;
System altered.
14:53:12 SYS@ shdb >alter system set db_recovery_file_dest='/dsk4/backup';
System altered.
14:53:18 SYS@ shdb >show parameter recover
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_recovery_file_dest string /dsk4/backup
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer
3)转换成功
14:54:13 SYS@ shdb >alter database convert to snapshot standby;
Database altered.
Elapsed: 00:00:03.10
14:54:41 SYS@ shdb >select status from v$instance;
STATUS
------------
MOUNTED
告警日志:
主库:
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/bjdb/prod/trace/prod_nsa2_2960.trc:
ORA-03135: connection lost contact
Error 3135 for archive log file 5 to 'shdb'
Errors in file /u01/app/oracle/diag/rdbms/bjdb/prod/trace/prod_nsa2_2960.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 5 thread 1 sequence 537 (3135)
Errors in file /u01/app/oracle/diag/rdbms/bjdb/prod/trace/prod_nsa2_2960.trc:
ORA-03135: connection lost contact
备库:
alter database convert to snapshot standby
ORA-38784 signalled during: alter database convert to snapshot standby...
Thu Nov 27 14:53:12 2014
ALTER SYSTEM SET db_recovery_file_dest_size='2G' SCOPE=MEMORY;
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
ALTER SYSTEM SET db_recovery_file_dest='/dsk4/backup' SCOPE=MEMORY;
Thu Nov 27 14:53:18 2014
db_recovery_file_dest_size of 2048 MB is 0.00% 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.
Thu Nov 27 14:54:38 2014
alter database convert to snapshot standby
Starting background process RVWR
Thu Nov 27 14:54:38 2014
RVWR started with pid=21, OS id=2198
Allocated 3981204 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_11/27/2014 14:54:38
krsv_proc_kill: Killing 3 processes (all RFS)
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Thu Nov 27 14:54:41 2014
SMON: disabling cache recovery
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 8596005
Resetting resetlogs activation ID 219765236 (0xd1959f4)
Online log /dsk2/oradata/shdb/redo04b.log: Thread 1 Group 4 was previously cleared
Online log /dsk1/oradata/shdb/redo04a.log: Thread 1 Group 4 was previously cleared
Online log /dsk2/oradata/shdb/redo05b.log: Thread 1 Group 5 was previously cleared
Online log /dsk1/oradata/shdb/redo05a.log: Thread 1 Group 5 was previously cleared
Standby became primary SCN: 8596003
Thu Nov 27 14:54:41 2014
Setting recovery target incarnation to 3
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
14:54:55 SYS@ shdb >select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_11/27/2014 14:54:38
在日志中可以看到,Database建立了restore point