11g Dataguard中的snapshot standby特性(r8笔记第49天)

11g中的ADG特性本身已经非常有特色,促使很多对于10g中不太灵便的备库升级到11g,对于DBA是一大福利,那么还有一个福利就是snapshot standby了。 在平时的数据更新操作中,DBA可以做好sql审核,如果对于复杂的,繁多的变更,如果有些变更有一定的依赖,数据变化情况比较大,评估有难度,很多问题 单纯在测试环境还发现不了,到了生产就是事儿。如果你饱受这种困扰,snapshot standby就是一个不错的选择。你可以让原本只读的备库可读可写,然后写写画画一番之后回归到上一次的一个临界点,继续应用归档日志。 这种操作基本上没有依赖,非常纯粹,易操作。 我们来举个例子看看。 下面的数据库是一主一备的架构。 DGMGRL> show configuration; Configuration - testmob_dg Protection Mode: MaxPerformance Databases: testmob - Primary database s2testmob - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS 我们来把备库置为snapshot standby,命令非常简单,在dg broker里面可以使用如下的方式,在sqlplus中也是一个命令就可以搞定,前提是取消日志应用。 dg broker修改为snapshot standby的日志如下: DGMGRL> convert database s2testmob to snapshot standby; Converting database "s2testmob" to a Snapshot Standby database, please wait... Database "s2testmob" converted successfully 切换完成之后再次查看,就会发现s2testmob变为了snapshot standby [oracle@teststd ~]$ dgmgrl / DGMGRL> show configuration; Configuration - testmob_dg Protection Mode: MaxPerformance Databases: testmob - Primary database s2testmob - Snapshot standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> 在备库中会有下面的一些相关日志信息。 Fri Mar 25 22:27:12 2016 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL Fri Mar 25 22:27:12 2016 MRP0: Background Media Recovery cancelled with status 16037 Errors in file /U01/app/oracle/diag/rdbms/s2testmob/testmob/trace/testmob_pr00_12884.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 50484782 Fri Mar 25 22:27:12 2016 MRP0: Background Media Recovery process shutdown (testmob) Managed Standby Recovery Canceled (testmob) Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL alter database convert to snapshot standby Starting background process RVWR Fri Mar 25 22:27:13 2016 RVWR started with pid=32, OS id=28987 Allocated 15937344 bytes in shared pool for flashback generation buffer Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/25/2016 22:27:13 krsv_proc_kill: Killing 3 processes (all RFS) Begin: Standby Redo Logfile archival End: Standby Redo Logfile archival RESETLOGS after incomplete recovery UNTIL CHANGE 50484782 Resetting resetlogs activation ID 2146999722 (0x7ff89daa) Online log /U01/app/oracle/oradata/testmob/redo01.log: Thread 1 Group 1 was previously cleared Online log /U01/app/oracle/oradata/testmob/redo02.log: Thread 1 Group 2 was previously cleared Online log /U01/app/oracle/oradata/testmob/redo03.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 50484780 Fri Mar 25 22:27:15 2016 Setting recovery target incarnation to 3 CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby Completed: alter database convert to snapshot standby ALTER DATABASE OPEN Data Guard Broker initializing... Data Guard Broker initialization complete 这个时候查看闪回区中,会发现有闪回数据库的日志信息。 [oracle@teststd flashback]$ ll total 102416 -rw-r----- 1 oracle oinstall 52436992 Mar 25 22:27 o1_mf_chblp1l1_.flb -rw-r----- 1 oracle oinstall 52436992 Mar 25 22:27 o1_mf_chblp3nb_.flb [oracle@teststd flashback]$ pwd /U01/app/oracle/fast_recovery_area/S2testmob/flashback 这个时候查看备库的信息,发现flashback_on的属性已经悄然发生改变。 SQL> select flashback_on from v$database; FLASHBACK_ON ------------------------------------ RESTORE POINT ONLY 数据库的角色和状态也发生了相应的变化。 SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE -------------------------------- ---------------------------------------- SNAPSHOT STANDBY READ WRITE 这个时候我们在备库里面就可读可写,我们创建一个用户,新建一个表。 SQL> create user jeanron identified by jeanron; User created. SQL> grant dba to jeanron; Grant succeeded. SQL> create table jeanron.test as select *from cat; Table created. 如果在主库端切换日志,在备库也能够看到RFS依旧可以正常接收归档,但是MRP肯定是还运行不了,也就意味着只会接收归档,但是日志还无法应用。 主库切换日志后,查看备库的日志信息如下: Fri Mar 25 22:36:04 2016 Archived Log entry 43 added for thread 1 sequence 1734 rlc 846934189 ID 0x7ff89daa dest 2: RFS[3]: Selected log 4 for thread 1 sequence 1735 dbid 2146967210 branch 846934189 写也写了,这种测试评估还是很有说服力的。完成之后我们就可以切换为备库状态即可。 DGMGRL> convert database s2testmob to physical standby; Converting database "s2testmob" to a Physical Standby database, please wait... Operation requires shutdown of instance "testmob" on database "s2testmob" Shutting down instance "testmob"... ORA-01031: insufficient privileges Warning: You are no longer connected to ORACLE. Please complete the following steps and reissue the CONVERT command: shut down instance "testmob" of database "s2testmob" start up and mount instance "testmob" of database "s2testmob" 在备库端还是需要重启一下备库 SQL> shutdown immediate SQL> startup mount SQL> select database_role from v$database; DATABASE_ROLE -------------------------------- SNAPSHOT STANDBY 可以直接使用一条命令即可完成切换,切换时间极短。 SQL> alter database convert to physical standby; Database altered. 查看备库的日志,发现闪回恢复回悄然完成,然后会自动删除闪回日志。 alter database convert to physical standby ALTER DATABASE CONVERT TO PHYSICAL STANDBY (testmob) Flashback Restore Start Flashback Restore Complete Drop guaranteed restore point Stopping background process RVWR Deleted Oracle managed file /U01/app/oracle/fast_recovery_area/S2testmob/flashback/o1_mf_chblp1l1_.flb Deleted Oracle managed file /U01/app/oracle/fast_recovery_area/S2testmob/flashback/o1_mf_chblp3nb_.flb Guaranteed restore point dropped Clearing standby activation ID 2207276870 (0x83905f46) The primary database controlfile was created using the 'MAXLOGFILES 16' clause. ... Fri Mar 25 22:45:03 2016 ARCH shutting downARCH shutting down ARC2: Archival stoppedARC1: Archival stopped ARC3: Archival stopped ARC0: Archival stopped Completed: alter database convert to physical standby 这个时候备库是在nomount状态 SQL> select database_role,open_mode from v$database; select database_role,open_mode from v$database * ERROR at line 1: ORA-01507: database not mounted 重新mount就报错了,需要重启一下。 SQL> alter database mount; alter database mount * ERROR at line 1: ORA-00750: database has been previously mounted and dismounted SQL> shutdown immediate SQL> startup mount 以为使用sql命令手工修改,需要在dg broker里面同步一下。 DGMGRL> show configuration; Configuration - testmob_dg Protection Mode: MaxPerformance Databases: testmob - Primary database s2testmob - Snapshot standby database Error: ORA-16810: multiple errors or warnings detected for the database Fast-Start Failover: DISABLED Configuration Status: ERROR DGMGRL> convert database s2testmob to physical standby; Converting database "s2testmob" to a Physical Standby database, please wait... Operation requires shutdown of instance "testmob" on database "s2testmob" Shutting down instance "testmob"... ORA-01031: insufficient privileges Warning: You are no longer connected to ORACLE. Please complete the following steps to finish the convert command: shut down instance "testmob" of database "s2testmob" start up and mount instance "testmob" of database "s2testmob" 其实这个时候备库还没有正式开启日志应用,重新启用一下即可。 DGMGRL> show configuration; Configuration - testmob_dg Protection Mode: MaxPerformance Databases: testmob - Primary database s2testmob - Physical standby database Error: ORA-16766: Redo Apply is stopped Fast-Start Failover: DISABLED Configuration Status: ERROR DGMGRL> edit database s2testmob set state='ONLINE'; Succeeded. DGMGRL> show configuration; Configuration - testmob_dg Protection Mode: MaxPerformance Databases: testmob - Primary database s2testmob - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS 整个过程完成之后,就跟什么都没有发生一样,一切又恢复了平静。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2016-03-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏菩提树下的杨过

Spring Security笔记:使用数据库进行用户认证(form login using database)

在前一节,学习了如何自定义登录页,但是用户名、密码仍然是配置在xml中的,这样显然太非主流,本节将学习如何把用户名/密码/角色存储在db中,通过db来实现用户认...

1981
来自专栏乐沙弥的世界

Failed to upgrade Oracle Cluster Registry configuration(root.sh)

    最近在给客户基于Suse 11 sp3安装Oracle 10g RAC,在安装完clusterware执行/u01/app/crs/root.sh时收...

961
来自专栏数据和云

偷梁换柱 | 无备份情况下的数据恢复实践

在实际环境中,许多数据库环境并没有做好完整的数据备份恢复计划及容灾方案,无法保证数据安全,并且出现一些灾难性的错误。那么我们就面临这样的问题:在什么样的最极端情...

3325
来自专栏王小雷

超详细讲解Sqoop2应用与实践

摘要:超详细讲解Sqoop2应用与实践,从hdfs上的数据导入到postgreSQL中,再从postgreSQL数据库导入到hdfs上。详细讲解创建link和创...

46810
来自专栏数据之美

Hive 中内部表与外部表的区别与创建方法

先来说下Hive中内部表与外部表的区别: Hive 创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径, 不对数据的位...

2919
来自专栏微信公众号:Java团长

详解Intellij IDEA搭建SpringBoot

Spring Boot是由Pivotal团队提供的全新框架,其设计目的是用来简化新Spring应用的初始搭建以及开发过程。该框架使用了特定的方式来进行配置,从而...

5654
来自专栏Bug生活2048

Spring Boot学习笔记(五)整合MyBatis实现数据库访问

这里主要依赖两个,一个是连接MySql的`mysql-connector-java`,还一个是SpringBoot整合MyBatis的核心依赖`mybatis-...

1022
来自专栏杨建荣的学习笔记

关于CPU使用率高的awr分析(r8笔记第46天)

今天看到一个报警信息,大体是CPU使用异常。 ZABBIX-监控系统: ------------------------------------ 报警内容: ...

3345
来自专栏蓝天

零停重启程序工具Huptime研究

零停重启目标程序,比如一个网络服务程序,不用丢失和中断任何消息实现重新启动,正在处理的消息也不会中断和丢失,重启的方法是给目标程序的进程发SIGHUP信号。...

881
来自专栏杨建荣的学习笔记

外部表的导入导出问题 (41天)

今天尝试了一下用exp导出外部表,碰到了一些问题。 ----导出 今天导出的时候发现一个严重的问题,导出一个很小的外部表花了很长时间,最后还是报错,而且生成的d...

3625

扫码关注云+社区

领取腾讯云代金券