Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

0    186    1

Tags:

👉 本文共约6882个字,系统预计阅读时间或需26分钟。

前言部分

导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,\~O(∩_∩)O\~:

① Failover后DG环境的恢复方法(重点)

② DG的基本维护操作

③ GC客户端软件的安装

④ 利用GC快速搭建一套DG环境

⑤ Failover和Switchover的区别

⑥ 其它维护操作

本文简介

10月23和24日考完了OCM,感觉过关的法则就是“真题+多练”,练习过10来遍,基本就可以考过了。OCM的考试内容除了GC这块小麦苗没有接触过,其它内容基本都算熟。基本命令熟记于心,不熟的命令可以立马找到官方文档,善用OEM和SQL Developer工具。所以,想快速通过OCM考试的朋友可以私下联系小麦苗,小麦苗会把自己的经验全都教给大家。

好了,废话不多说了。最近小麦苗的DBA宝典微信群里,有朋会友问到了Failover操作后,如何恢复到最初的DG环境。这个问题,小麦苗大概知道利用闪回可以实现,只是没有做过实验,或者曾经做过实验,只是没有记录文档,反正就是年纪大了,想不起来了。好吧,最近就抽个时间把这个实验做一遍。有不对的地方,依然请大家指出。

2017-11-06_151651

实验准备

实验环境介绍

实验环境为练习OCM的虚拟机环境:

项目Source DBTarget DB
DB 类型单机单机
DB VERSION11.2.0.3.011.2.0.3.0
DB 存储FSFS
OS版本及kernel版本OEL linux 5.4 32OEL linux 5.4 32
DB_NAMEPROD1PROD1
ORACLE_SIDPROD1SBDB1
ORACLE_HOME/u01/app/oracle/product/11.2.0/dbhome_1/u01/app/oracle/product/11.2.0/db_1
hosts文件10.190.104.111 edsir4p1.us.oracle.com edsir4p1 10.190.104.28 edsir1p8.us.oracle.com edsir1p8

实验目标

备库执行FAILOVER后,通过闪回数据库技术重新恢复DG环境,而不用重新搭建DG。

实验过程

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

利用GC快速搭建DG环境

小麦苗手头的DG环境是在一个主机上,测试多有不便,刚好,最近练习OCM的环境还在,就用练习OCM的环境来做这个实验吧。若已经有DG环境的朋友可以略过该小节内容。

安装GC客户端软件

1、起动GC服务器,首先确保EMREP数据库处于OPEN状态,监听也已经启动,GC服务器启动日志为:/u01/app/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log

cd /u01/app/oracle/Middleware/oms11g/bin

./emctl start oms

2、安装agent

https://10.190.104.28:4900/agent_download/ 从这里下载

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

右键保存到桌面。

a、在需要安装agent的机器上mkdir /u01/app/agentbase 创建目录,并将agentDownload.linux文件cp到/u01/app/agentbase目录下,并且赋予可执行权限。

b、在服务端OMS启动的情况下,在客户端执行:

./agentDownload.linux -b /u01/app/agentbase -m edsir1p8.us.oracle.com -r 7799 -y

安装过程中要输入偶数机上OMS的密码

c、安装完成要用root执行:

[root@edsir4p1 \~]## sudo /u01/app/agentbase/agent11g/root.sh

没有root密码要使用sudo执行,注意:一定要执行该脚本,它会设置一些文件的权限(该脚本会把\$AGENT_HOME/bin/nm*的几个文件的所有者修改为root。)。如果不执行,那么搭建DG可能会报错:“ERROR: NMO not setuid-root (Unix-only)”

d、进入/u01/app/agentbase/agent11g/bin

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

./emctl status agent 检查同步状态

./emctl upload agent 上传同步

./emctl secure agent 重新注册agent,用于安装时密码输错

过程如下所示:

使用GC快速搭建物理备库

从浏览器打开https://10.190.104.28:7799/em/,使用sysman用户进行登录。

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

使用sys用户登录PROD1数据库。

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

等待大约10分钟即可自动完成DG的搭建和配置工作。期间,可以查看主库和备库的告警日志以及数据文件夹的大小来预估搭建完成时间。

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

创建完成后:

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

启用实时应用

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

Oracle利用闪回数据库(flashback)功能修复Failover后的DG环境

开启主备库闪回

告警日志:

此时,备库并没有开启闪回,需要在备库上手动开启闪回:

实验1

实验1、PROD1意外宕机,SBDB1执行Failover操作变为主库;然后将PROD1利用闪回数据库功能闪回到SBDB1变为主库的SCN时间点,然后将PROD1转换为备库,最后利用switchover转换为最初的环境。\<===PROD1需要开启闪回

Failover操作

切换之前确保监听使用静态监听、fal_client、fal_server、log_archive_dest_1和log_archive_dest_2参数已正确配置。

主库操作:

备库操作:

Primary重新加入

Failover后的Primary数据库,实际上已经失去了和DG的关联,如果Primary故障严重,是难以保障对应的归档数据可以顺利传输的。如果希望Primary重新回到DG环境,关键就是恢复的时间点。要求Primary回到Standby切换角色的那个时间点,理论上就可以“延续”操作。

查询原备库变为新主库的SCN

在原备库端,查看v\$database视图,可以看到这个库成为primary的具体时间。

原主库执行闪回操作

注意:重新加入的原Primary是不能恢复角色的,而是只能先成为Standby角色。应用后续的日志达到同步。

Oracle DG在发生Failover之后,当主库解决问题,是不可以直接回到DG环境的。这个过程往往需要一些辅助组建的配合。如RMAN、Flashback,都可以简化重回DG的过程时间。

注意:如果原主库查询不到test_bylhr2表的数据,则需要仔细检查监听使用静态监听、fal_client、fal_server、log_archive_dest_1和log_archive_dest_2参数已正确配置。

执行switchover切换成初始环境

新主库:

新备库执行:

备库查询:

至此,该实验完毕。即主备库执行Failover后,通过闪回重新恢复最初的DG环境。

实验2

实验2、PROD1意外宕机,SBDB1执行Failover操作变为主库;然后将SBDB1利用闪回数据库功能闪回到SBDB1变为主库的SCN时间点,此时SBDB1仍然是主库的角色,然后将SBDB1转换为备库。\<===SBDB1需要开启闪回,会丢失部分数据。

Failover操作

主库操作:

备库操作:

新主库闪回

原主库打开

备库查询:

DGMGRL中查看配置:

说明直接在原备库上执行闪回操作也可以让Failover后的环境重新恢复最初的环境。不过这也意味着部分数据的丢失。

实验总结

  1. 主库在开启闪回数据库功能后,备库并不会开启闪回数据库的功能。
  2. PROD1为主库,SBDB1为备库;若PROD1意外宕机,则SBDB1执行Failover操作变为主库;此时若想恢复DG环境,则有3种处理办法:
  3. 将PROD1利用闪回数据库功能闪回到SBDB1变为主库的SCN时间点,然后将PROD1转换为备库,最后利用switchover转换为最初的环境。\<===PROD1需要开启闪回
  4. 将SBDB1利用闪回数据库功能闪回到SBDB1变为主库的SCN时间点,此时SBDB1仍然是主库的角色,然后将SBDB1转换为备库。\<===SBDB1需要开启闪回,会丢失部分数据。
  5. 利用RMAN重新搭建DG环境。详情请参考:http://blog.itpub.net/26736162/viewspace-1753130/

参考文章

MOS或官网

https://docs.oracle.com/cd/E11882_01/server.112/e41134/scenarios.htm#SBYDB4889

https://docs.oracle.com/cd/E11882_01/server.112/e41134/scenarios.htm#SBYDB00900

This chapter describes scenarios you might encounter while administering your Data Guard configuration. Each scenario can be adapted to your specific environment.聽Table 13-1lists the scenarios presented in this chapter.

Table 13-1 Data Guard Scenarios

ReferenceScenario
Section 13.1Configuring Logical Standby Databases After a Failover
Section 13.2Converting a Failed Primary Into a Standby Database Using Flashback Database
Section 13.3Using Flashback Database After Issuing an Open Resetlogs Statement
Section 13.4Recovering After the NOLOGGING Clause Is Specified
Section 13.5Creating a Standby Database That Uses OMF or Oracle ASM
Section 13.6Recovering From Lost-Write Errors on a Primary Database
Section 13.7Converting a Failed Primary into a Standby Database Using RMAN Backups
Section 13.8Changing the Character Set of a Primary Without Re-Creating Physical Standbys

这8个实验,建议有兴趣的朋友都做一遍。

http://blog.itpub.net/24500180/viewspace-1068495/

http://blog.itpub.net/24500180/viewspace-1068141/

http://blog.itpub.net/24500180/viewspace-1069602/

After a failover occurs, the original primary database can no longer participate in the Data Guard configuration until it is repaired and established as a standby database in the new configuration. To do this, you can use the Flashback Database feature to recover the failed primary database to a point in time before the failover occurred, and then convert it into a physical or logical standby database in the new configuration. The following sections describe:

Flashing Back a Failed Primary Database into a Physical Standby Database

Flashing Back a Failed Primary Database into a Logical Standby Database

Note:

You must have already enabled Flashback Database on the original primary database before the failover. See Oracle Database Backup and Recovery User's Guide for more information.

Flashing Back a Logical Standby Database to a Specific Applied SCN

See Also:

Oracle Data Guard Broker for automatic reinstatement of the failed primary database as a new standby database (as an alternative to using Flashback Database)

13.2.1 Flashing Back a Failed Primary Database into a Physical Standby Database

The following steps assume that a failover has been performed to a physical standby database and that Flashback Database was enabled on the old primary database at the time of the failover. This procedure brings the old primary database back into the Data Guard configuration as a physical standby database.

Step 1 Determine the SCN at which the old standby database became the primary database.

On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V\$DATABASE;

Step 2 Flash back the failed primary database.

Shut down the old primary database (if necessary), mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in Step 1.

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;

Step 3 Convert the database to a physical standby database.

Perform the following steps on the old primary database:

Issue the following statement on the old primary database:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

This statement will dismount the database after successfully converting the control file to a standby control file.

Shut down and restart the database:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Step 4 Start transporting redo to the new physical standby database.

Perform the following steps on the new primary database:

Issue the following query to see the current state of the archive destinations:

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, -

> ERROR,SRL FROM V\$ARCHIVE_DEST_STATUS;

If necessary, enable the destination:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;

Perform a log switch to ensure the standby database begins receiving redo data from the new primary database, and verify it was sent successfully. Issue the following SQL statements on the new primary database:

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,-

> ERROR,SRL FROM V\$ARCHIVE_DEST_STATUS;

On the new standby database, you may also need to change the LOG_ARCHIVE_DEST_n initialization parameters so that redo transport services do not transmit redo data to other databases.

Step 5 Start Redo Apply on the new physical standby database.

Issue the following SQL statement on the new physical standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE -

> USING CURRENT LOGFILE DISCONNECT;

Redo Apply automatically stops each time it encounters a redo record that is generated as the result of a role transition, so Redo Apply will need to be restarted one or more times until it has applied beyond the SCN at which the new primary database became the primary database. Once the failed primary database is restored and is running in the standby role, you can optionally perform a switchover to transition the databases to their original (pre-failure) roles. See Section 8.2.1, "Performing a Switchover to a Physical Standby Database" for more information.

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

4 × 3 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部