Use the physical standby database as a test environment

最新推荐文章于 2017-12-04 10:12:50 发布
最新推荐文章于 2017-12-04 10:12:50 发布
阅读量1.2k 收藏
点赞数
分类专栏： ----Dataguard
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cainiaofly/article/details/18822573
版权

前言：

       在日常项目中，出于各种原因，可能需要临时将standby database置为read write模式，需求完成后再恢复正常DataGuard结构。本文经过在10g，11g上DG的验证，梳理了3个比较容易实现的方式。


1. 使用activate stanby database,并利用手动guarantee restore point 还原，适用于 10g&11g


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> create restore point before_test guarantee flashback database;

Restore point created.


SQL> alter database activate standby database;    

Database altered.

SQL> select database_role,open_mode,protection_mode,protection_level from v$database;

DATABASE_ROLE     OPEN_MODE          PROTECTION_MODE       PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PRIMARY      MOUNTED          MAXIMUM AVAILABILITY UNPROTECTED

SQL> alter database open;

Database altered.

SQL> select database_role,open_mode,protection_mode,protection_level from v$database;    --- standby DB is in read write mode now

DATABASE_ROLE     OPEN_MODE          PROTECTION_MODE       PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PRIMARY      READ WRITE          MAXIMUM AVAILABILITY RESYNCHRONIZATION

alter database activate standby database
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (test3)
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 3097888
Archived Log entry 77 added for thread 1 sequence 62 ID 0x37014efb dest 1:
Resetting resetlogs activation ID 922832635 (0x37014efb)
Online log /u02/test3/redo01a.log: Thread 1 Group 1 was previously cleared
Online log /u03/test3/redo01b.log: Thread 1 Group 1 was previously cleared
Online log /u02/test3/redo02a.log: Thread 1 Group 2 was previously cleared
Online log /u03/test3/redo02b.log: Thread 1 Group 2 was previously cleared
Online log /u02/test3/redo03a.log: Thread 1 Group 3 was previously cleared
Online log /u03/test3/redo03b.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 3097886
Tue Jan 28 12:39:05 2014
Setting recovery target incarnation to 7                                 ------------ incarnation plus 1
ACTIVATE STANDBY: Complete - Database mounted as primary
Completed: alter database activate standby database


alter database open
Tue Jan 28 12:41:14 2014
Assigning activation ID 960825149 (0x3945073d)
LGWR: Primary database is in MAXIMUM AVAILABILITY mode
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
Thread 1 advanced to log sequence 2 (thread open)
Tue Jan 28 12:41:14 2014
ARC0: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /u02/test3/redo02a.log
  Current log# 2 seq# 2 mem# 1: /u03/test3/redo02b.log
Successful open of redo thread 1
Tue Jan 28 12:41:14 2014
ARC1: Becoming the 'no SRL' ARCH
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Jan 28 12:41:14 2014
SMON: enabling cache recovery
Archived Log entry 78 added for thread 1 sequence 1 ID 0x3945073d dest 1:
Tue Jan 28 12:41:15 2014
PING[ARC2]: Heartbeat failed to connect to standby 'test3_A'. Error is 16009.
[2926] Successfully onlined Undo Tablespace 1.
Undo initialization finished serial:0 start:76794 end:77134 diff:340 (3 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Tue Jan 28 12:41:18 2014
QMNC started with pid=25, OS id=2950
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open





SQL> alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance

SQ L> startup mount force
ORACLE instance started.

Total System Global Area  622149632 bytes
Fixed Size            2230912 bytes
Variable Size          184550784 bytes
Database Buffers      432013312 bytes
Redo Buffers            3354624 bytes
Database mounted.

SQL> flashback database to restore point before_test;

Flashback complete.

SQL> alter database convert to physical standby;

Database altered.


SQL> select status from v$instance;

STATUS
------------
STARTED


SQL> startup force  
ORACLE instance started.

Total System Global Area  622149632 bytes
Fixed Size            2230912 bytes
Variable Size          184550784 bytes
Database Buffers      432013312 bytes
Redo Buffers            3354624 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.





2. 使用convert to snapshot standby,还原点会自动进行创建与删除。仅适用11g


SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_01/27/2014 21:05:29
krsv_proc_kill: Killing 3 processes (all RFS)
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Mon Jan 27 21:05:30 2014
SMON: disabling cache recovery
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 3167297
Resetting resetlogs activation ID 922832635 (0x37014efb)
Online log /u02/test3/redo01a.log: Thread 1 Group 1 was previously cleared
Online log /u03/test3/redo01b.log: Thread 1 Group 1 was previously cleared
Online log /u02/test3/redo02a.log: Thread 1 Group 2 was previously cleared
Online log /u03/test3/redo02b.log: Thread 1 Group 2 was previously cleared
Online log /u02/test3/redo03a.log: Thread 1 Group 3 was previously cleared
Online log /u03/test3/redo03b.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 3167295
Mon Jan 27 21:05:30 2014
Setting recovery target incarnation to 8
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby


SQL> select database_role,open_mode from v$database;

DATABASE_ROLE     OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED

SQL> alter database open;

Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE     OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE



SQL> startup mount force
ORACLE instance started.

Total System Global Area  622149632 bytes
Fixed Size            2230912 bytes
Variable Size          184550784 bytes
Database Buffers      432013312 bytes
Redo Buffers            3354624 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.


ALTER DATABASE CONVERT TO PHYSICAL STANDBY
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (test3)
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point  dropped
Clearing standby activation ID 960771251 (0x394434b3)
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
Mon Jan 27 21:26:20 2014
ARCH shutting down
ARC2: Archival stopped
Mon Jan 27 21:26:20 2014
ARCH shutting down
ARC1: Archival stopped
Mon Jan 27 21:26:20 2014
ARCH shutting down
ARC0: Archival stopped
Mon Jan 27 21:26:20 2014
ARCH shutting down
ARC3: Archival stopped
Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY



SQL> startup force  
ORACLE instance started.

Total System Global Area  622149632 bytes
Fixed Size            2230912 bytes
Variable Size          184550784 bytes
Database Buffers      432013312 bytes
Redo Buffers            3354624 bytes
Database mounted.
Database opened.
SQL> select database_role,open_mode from v$database;

DATABASE_ROLE     OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY


SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (test3)
Mon Jan 27 21:28:44 2014
MRP0 started with pid=26, OS id=27157
MRP0: Background Managed Standby Recovery process started (test3)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u02/test3/redo01a.log
Clearing online log 1 of thread 1 sequence number 1
Completed: alter database recover managed standby database using current logfile disconnect from session
Mon Jan 27 21:28:55 2014
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u02/test3/redo02a.log
Clearing online log 2 of thread 1 sequence number 2
Clearing online redo logfile 2 complete
Media Recovery Log /u01/archivelogs/test3/1_81_800115694.dbf
Media Recovery Log /u01/archivelogs/test3/1_82_800115694.dbf
Media Recovery Log /u01/archivelogs/test3/1_83_800115694.dbf
Recovery of Online Redo Log: Thread 1 Group 5 Seq 84 Reading mem 0
  Mem# 0: /u02/test3/standby_redo05a.log
  Mem# 1: /u03/test3/standby_redo05b.log


3. 使用failover将standby置为primary,并利用guarantee restore point 还原 ，适用于10g&11g


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> create restore point before_test guarantee flashback database;

Restore point created.

standby 端：

   alter database recover managed standby database finish force;   

  （force表示立刻停止RFS进程,且此时standby的switchover_status才是to primary。否则是not allowed，如果尝试切换角色会提示ORA-16139: media recovery required）

   alter database commit to switchover to physical primary;
   alter database open;
   
   
alter database commit to switchover to primary
ALTER DATABASE SWITCHOVER TO PRIMARY (test3)
Maximum wait for role transition is 15 minutes.
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/test3_b/test3/trace/test3_ora_27147.trc
Standby terminal recovery start SCN: 3170407
RESETLOGS after complete recovery through change 3170409
Online log /u02/test3/redo01a.log: Thread 1 Group 1 was previously cleared
Online log /u03/test3/redo01b.log: Thread 1 Group 1 was previously cleared
Online log /u02/test3/redo02a.log: Thread 1 Group 2 was previously cleared
Online log /u03/test3/redo02b.log: Thread 1 Group 2 was previously cleared
Online log /u02/test3/redo03a.log: Thread 1 Group 3 was previously cleared
Online log /u03/test3/redo03b.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 3170406
Mon Jan 27 21:44:14 2014
Setting recovery target incarnation to 9
Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE     OPEN_MODE
---------------- --------------------
PRIMARY      MOUNTED



SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> startup force     
ORACLE instance started.

Total System Global Area  622149632 bytes



Fixed Size            2230912 bytes

Variable Size          184550784 bytes
Database Buffers      432013312 bytes
Redo Buffers            3354624 bytes
Database mounted.
Database opened.
SQL> select database_role,open_mode from v$database;

DATABASE_ROLE     OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select process,pid,status,client_process,sequence# from v$managed_standby;

PROCESS      PID STATUS      CLIENT_P  SEQUENCE#
--------- ---------- ------------ -------- ----------
ARCH       29865 CLOSING      ARCH           37
ARCH       29867 CLOSING      ARCH           38
MRP0       29876 APPLYING_LOG N/A           39
RFS           29882 IDLE      UNKNOWN        0
RFS           29884 IDLE      LGWR           39



Bond的杂货铺
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle dg打开备库为读写数据库,慎重!
rudy gao
08-01 6763
--确保standby数据库置于flashback on 状态 --设置闪回恢复区 alter system set db_recovery_dest_size=2g; alter system set db_recovery_file_dest='+DATA'; --取消备库的自动恢复状态 alter database recover managed standby d
Oracle Dataguard物理备库三种临时读写方法
你好我是李白
07-26 1472
包含以下三部分内容，都可以实现物理备库临时读写以及再恢复为physical standby： flashback database闪回physical standby activate standby database snapshot standby 1. 在physical standby闪回恢复主库误操作数据 只要Flashback dest空间跟归档在，可以任意闪回。 1.1 查询当前primary SCN SYS@honor1>selectto_c...
dataguard类型转换与模式转化
cuangu9341的博客
03-10 74
修改数据保护模式步骤 前提：是否满足转换模式的配置要求 最大保护（Maximum Protection）： Standby Database 必须配置Standby Redo Log，Primary Dat...
【翻译自mos文章】将一个失败的primary database 复原为physical standby 库的步骤
msdnchina的专栏@JiNan,ShanDong
04-04 1988
将一个失败的primary database 复原为physical standby 库的步骤
Reinstate Failed Primary Database into Physical Standby
gumengkai的博客
12-04 435
官方文档： Step by Step Guide on How To Reinstate Failed Primary Database into Physical Standby (Doc ID 738642.1) 一. 利用闪回 前提条件： a. failover之前需要在主库和备库开启闪回 b. 必须有"备转主"的时刻到现在所有的闪回日志 c. 主备数据库能够通过网络连接
Oracle创建Physical Standby Database案例
Watson的专栏
06-30 9186
Creating a Physical Standby Database This case is created, operated and followed the steps from oracle online help documentation.  The configuration of the two sites server: Primary Database: [roo
【原】Configuring Oracle Data Guard In Physical Standby Database
weixin_34124939的博客
12-13 120
作者：david_zhang@sh 【转载时请以超链接形式标明文章】 http://www.cnblogs.com/david-zhang-index/p/5042640.html参照文档：https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-configuri...
Creating A Physical Standby With RMAN Active Duplicate In 11.2.0.3
weixin_33738578的博客
10-28 118
2019独角兽企业重金招聘Python工程师标准>>> ...
Creating Physical Standby Using RMAN DUPLICATE
09-27 283
Step by step guide on how to create a physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE ...
oracle physical standby配置
03-19
Oracle物理备用库(Physical Standby)配置是在数据库高可用性架构中的一种常见实践，它确保在主数据库出现故障时，可以快速切换到备用数据库继续提供服务。以下是对配置过程的详细解释： 1. **启用Force Logging**：...
oracle standby database
05-18
关于oracle中如何创建standby database的学习资料！
Oracle standby database的建立与维护.pdf
10-10
Oracle Standby Database是一种重要的数据库高可用性和灾难恢复解决方案，它为Oracle数据库提供了一种容错机制，能够在主数据库出现故障时迅速切换到备用数据库，确保业务连续性。本文将详细介绍Oracle Standby ...
Oracle8i Standby Database Concepts and Administration Release 2
04-06
Oracle8i Standby Database Concepts and Administration Release 2 (8.1.6)
Oracle Standby Database技术的原理及实现.pdf
10-10
Oracle Standby Database技术的原理及实现.pdf
使用增量备份修复dataguard与日常备份策略的选择
BOND 的杂货铺
06-11 3795
一.前言          在我们生产环境中，dataguard通常用来提供容灾方案，因此有效地保护归档日志就显得尤为重要，否则一旦形成日志gap，且归档日志被误删除，进而导致DG会失去数据同步的能力，从这个角度来说DG似乎又显得十分脆弱。本文会简要地提到如何制定较好的备份策略，但是最重要的目的是来看看如何修复gap。或许有人想到了重建DG，特别是在11g可以在线重建，但是在真正的生产环境
Oracle 10.2.0.5 Dataguard creation and switchover/failover practise with RMAN in redhat 5.8 x64
BOND 的杂货铺
09-13 1821
前言：此文属个人记录总结，以自身认为有必要的内容进行记录。  具体内容还是请参考官方文档。 一.准备工作 在standby端准备好目录 alter database force logging; 可以先将primary端改为 maximize availability，不影响结果 为了实时应用，必须在standby使用standby redo，member尺寸与onlin
Oracle11g 开始，创建DG更便捷了，有木有？
BOND 的杂货铺
10-30 892
从11g 开始主库不需要先做一次全库热备，加上from active database参数就可以实现在线创建DG，又强大了一步，有木有？  稍微注意几点： 1.如果要启用实时应用，务必先在primary端创建好standby redo log,这一步容易忘记。当然所后补上也无大碍。 2.本创建过程结束后，需要手动打开日志应用。 3.无意中没有配置log_archive_dest_2,
Add extra standby database to complete database migration to new servers with minimal outage
BOND 的杂货铺
04-19 365
When we have to upgrade Redhat Operating System due to license expiration for a Production database which has been configured Data Guard, as we know Redhat dosen`t support self-upgrade therefore we have to build brand new servers then migrate the primary a
recover standby database from service
最新发布
05-24
To recover a standby database from a service, you can follow the below steps: 1. Connect to the primary database using SQL*Plus or any other database client tool. 2. Check the status of the standby ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值