Implement Oracle Datagurad Failover

fateame · 发表于 2018-9-22 07:21:49

　　生产环境中，可能会有各种各样的原因导致数据库不能提供服务，例如数据库库的硬件故障，操作系统故障或软件bug，人为的失误(例如rm -rf /)等，在这种情况下，dataguard显得尤为重要，当主库不可用的时候，可以将备库failover成主库继续提供服务，failover和switchover不同的是，执行failover后，原有的dataguard配置将会失效且有可能会丢失部分数据，所以在生产环境中能用switchover解决的问题则应尽量采用switchover！switchover执行过程参考：http://ylw6006.blog.51cto.com/470441/849829
　　一：使用sql命令执行failover
　　
1：查看当前的主库是dg1，物理备库为dg2，将主库关闭，模拟主库故障
　　

[oracle@dg2 ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;
Configuration - DG_BROKER_CONFIG
Protection Mode: MaxAvailability
Databases:
dg1 - Primary database
dg2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
READ WRITE PRIMARY dg1 NO
SQL> shutdown abort
ORACLE instance shut down.

　　

　　2：在备库dg2上执行failover过程，failover执行的命令和switchover类似，只是在这个过程中，主库已经不可用
　　

SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
READ ONLY WITH APPLY PHYSICAL STANDBY dg2 NO
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
READ ONLY PHYSICAL STANDBY dg2 NO
出现这个错误说明日志并未完全的应用
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
READ ONLY WITH APPLY PHYSICAL STANDBY dg2 NO
SQL> alter database recover managed standby database finish;
Database altered.
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
MOUNTED PRIMARY dg2 NO
SQL> alter database open;
Database altered.
SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
READ WRITE PRIMARY dg2 NO

　　

　　二：使用datagurad broker进行failover
　　
1:开启主库和备库的闪回功能，主要用于主库恢复后的reinstate操作，当前主库为dg2,备库为dg1
　　

SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
READ WRITE PRIMARY dg2 YES
SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
READ ONLY WITH APPLY PHYSICAL STANDBY dg1 YES
[oracle@dg1 ~]$ dgmgrl sys/123456@dg1
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;
Configuration - DG_BROKER_CONFIG
Protection Mode: MaxAvailability
Databases:
dg2 - Primary database
dg1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

　　

　　2:关闭主库dg2,模拟主库故障
　　

SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
READ WRITE PRIMARY dg2 YES
SQL> shutdown abort
ORACLE instance shut down.
DGMGRL> show configuration;
Configuration - DG_BROKER_CONFIG
Protection Mode: MaxAvailability
Databases:
dg2 - Primary database
dg1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-01034: ORACLE not available
ORA-16625: cannot reach database "dg2"
DGM-17017: unable to determine configuration status

　　

　　3：执行failover
　　

DGMGRL> failover to dg1;
Performing failover NOW, please wait...
Failover succeeded, new primary is "dg1"
DGMGRL> show configuration;
Configuration - DG_BROKER_CONFIG
Protection Mode: MaxAvailability
Databases:
dg1 - Primary database
Warning: ORA-16629: database reports a different protection level from the protection mode
dg2 - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
WARNING

　　

　　4:重新启动原主库dg2
　　

SQL> startup
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 352324912 bytes
Database Buffers 54525952 bytes
Redo Buffers 8466432 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from
being opened
SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
MOUNTED PRIMARY dg2 YES

　　

　　5:执行reinstate操作
　　

DGMGRL> reinstate database dg2;
Reinstating database "dg2", please wait...
Operation requires shutdown of instance "dg" on database "dg2"
Shutting down instance "dg"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dg" on database "dg2"
Starting instance "dg"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps and reissue the REINSTATE command:
start up and mount instance "dg" of database "dg2"
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 352324912 bytes
Database Buffers 54525952 bytes
Redo Buffers 8466432 bytes
Database mounted.
Database opened.
SQL> select open_mode,database_role,db_unique_name,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME FLASHBACK_ON
-------------------- ---------------- -------------------- ------------------
READ ONLY PHYSICAL STANDBY dg2 YES
DGMGRL> show configuration;
Configuration - DG_BROKER_CONFIG
Protection Mode: MaxAvailability
Databases:
dg1 - Primary database
dg2 - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> enable database dg2;
Error: ORA-16661: the standby database needs to be reinstated
Failed.
DGMGRL> reinstate database dg2;
Reinstating database "dg2", please wait...
Reinstatement of database "dg2" succeeded
DGMGRL> show configuration;
Configuration - DG_BROKER_CONFIG
Protection Mode: MaxAvailability
Databases:
dg1 - Primary database
dg2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

　　

　　总结
　　
1：非到万不得已的情况，不使用failover，优先考虑switchover
　　
2：若执行failover，则考虑使用sql命令完成
　　
3：执行failover后，应及时备份数据，并重构dg环境

账号		自动登录	找回密码
密码			立即注册

Centos6.5×64安装配置openmeetings3.0.3详

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

[经验分享] Implement Oracle Datagurad Failover

浏览过的版块

扫码加入运维网微信交流群