11g Active DataGuard初探(r5笔记第54天)
原本dataguard中日志应用和数据库只读查询是一个互斥的关系,两者不能并存。如果需要应用日志,则数据库只能在Mount状态下 使用recover managed standby database disconnect from session来不断地从后台进行日志应用。如果想查看备库中的数据情况,则只能使用recover managed standby database cancel来取消日志应用,然后把数据库启动到read only 状态下。这种情况从道理上也讲也是有理有据,但是终归还是感觉不够方便,毕竟我们希望备库能够起到一些作用,不只是应用日志,一些大查询可以直接在备库上执行,能够分担主库的压力。11g的active dataguard就做到了这一点,重点就在于所说的active,所以这个时候数据库启动到了read only状态下,而且可以同时应用日志。如果配置备库的模式级别较高,甚至感觉和主库是一致的。我们来简单看看这个特性。我们来看看备库的信息。idle> select name,database_role from v$database;NAME DATABASE_ROLE--------- ----------------TEST11G PHYSICAL STANDBY1 row selected.对应的Instance信息。此时在Mount状态。idle> select instance_name,status from v$instance;INSTANCE_NAME STATUS---------------- ------------DG11G MOUNTED1 row selected.查看日志的应用情况,发现最新的记录中日志应用的需要为 78idle> select *from v$dataguard_statusRemote File Server Warning 0 28 0 NO 01-JUN-15 RFS[2]: No standby redo logfiles of size 84490 blocks availableLog Apply Services Informational 0 29 0 NO 01-JUN-15 Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_77_880742847.dbfLog Apply Services Warning 0 30 0 NO 01-JUN-15 Media Recovery Waiting for thread 1 sequence 7830 rows selected.我们在主库切换一下日志。#### primary databasealter system switch logfile;备库这边很快得到相应,可见dataguard的日志应用是没有问题的。这些部分和在10g中是一致的。########## standby alert logMon Jun 01 22:46:51 2015RFS[2]: No standby redo logfiles of size 57697 blocks availableRFS[2]: Opened log for thread 1 sequence 78 dbid 1028247664 branch 880742847Archived Log entry 110 added for thread 1 sequence 78 rlc 880742847 ID 0x3d942dcb dest 2:Mon Jun 01 22:46:54 2015Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_78_880742847.dbfMedia Recovery Waiting for thread 1 sequence 79这个时候我们取消日志应用,把数据库启动起来。idle> recover managed standby database cancel;Media recovery complete.idle> alter database open;Database altered.这个时候查看数据库的状态是read only.idle> select open_mode from v$database;OPEN_MODE--------------------READ ONLY1 row selected.这个时候我们来启用日志应用,这个也是在11g中的特色了。idle> recover managed standby database using current logfile disconnect from session;Media recovery complete.这个时候查看状态就发生了细微的变化。idle> select open_mode from v$database;OPEN_MODE--------------------READ ONLY WITH APPLY1 row selected.这个时候为了验证,我们从主库做点什么,比如创建一个小表看看备库能够在open状态也能应用日志。主库中执行。sys@TEST11G> conn n1/n1Connected.n1@TEST11G> create table aaa as select *from cat;Table created.n1@TEST11G> select count(*)from aaa; COUNT(*)---------- 191 row selected.这个时候在备库中马上查看是没有效果的。##### standby datababsen1@TEST11G> select *from aaa;select *from aaa *ERROR at line 1:ORA-00942: table or view does not existn1@TEST11G> show userUSER is "N1"n1@TEST11G> 这个时候我们尝试切一下主库的日志,看看备库有啥反应。#### primary alter system switch logfile;备库中的alert日志显示如下:### standby logMon Jun 01 22:59:57 2015RFS[2]: Selected log 8 for thread 1 sequence 79 dbid 1028247664 branch 880742847Mon Jun 01 22:59:57 2015Archived Log entry 111 added for thread 1 sequence 79 ID 0x3d942dcb dest 1:Archived Log entry 112 added for thread 1 sequence 79 ID 0x3d942dcb dest 3:Mon Jun 01 22:59:57 2015Media Recovery Log /u02/dg11g/switchover/DG11G/archivelog/1_79_880742847.dbfMedia Recovery Waiting for thread 1 sequence 80这个时候再次在备库查看,就发现数据变更都同步过来了。n1@TEST11G> select count(*) from aaa; COUNT(*)---------- 191 row selected.n1@TEST11G> show userUSER is "N1"