博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
11g Dataguard中的snapshot standby特性
阅读量:5879 次
发布时间:2019-06-19

本文共 7532 字,大约阅读时间需要 25 分钟。

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
整个过程完成之后,就跟什么都没有发生一样,一切又恢复了平静。

转载地址:http://ngcix.baihongyu.com/

你可能感兴趣的文章
Python对进程Multiprocessing子进程返回值
查看>>
tomcat下java.io.NotSerializableException错误的解决方法
查看>>
Python入门之函数式开发
查看>>
IOS配置SSH一定需要先配置hostname和domain-name吗?
查看>>
java基础第十二天
查看>>
Django之MTV
查看>>
三级菜单
查看>>
DIY强大的虚拟化环境-升级存储主机
查看>>
Spring源码解析(三)——容器创建
查看>>
document.bgcolor设置文档的背景颜色
查看>>
星期天写了点蛋疼的东西(1)
查看>>
A10的上网链路负载实现
查看>>
文件I/O
查看>>
橙子引擎CEO尚韬: 蓝海破冰,重新定义TV游戏
查看>>
Spring中factory-method的使用
查看>>
zTree默认选中指定节点并执行事件
查看>>
编译安装syslog-ng debian
查看>>
通过爬妹子图片来学习async/await
查看>>
【python】编程语言入门经典100例--35
查看>>
cookie增加Secure属性
查看>>