Implement Oracle snapshot database with Dataguard-ylw6006
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 Area417546240 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 10 15:20 ? 00:00:00 ora_rvwr_dg
[*]oracle 14788 147560 15:24 pts/4 00:00:00 /bin/bash -c ps -ef |grep rvwr
[*]oracle 14790 147880 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
[*]
[*]$ 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.
[*]
[*]$ 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.
[*]
[*]$ 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
[*]
[*]$ 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 KeyInc Key DB NameDB ID STATUSReset SCNReset Time
[*]------- ------- -------- ---------------- --- ---------- ----------
[*]1 1 DG 1694605607 PARENT1 2011-09-17-09:46:04
[*]2 2 DG 1694605607 PARENT995548 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 Area417546240 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.
[*]
[*]$ 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 pointdropped
[*]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 Area417546240 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
页:
[1]