Oracle DataGuard主备切换之自动切换

简介: Oracle DataGuard主备切换之自动切换

今天主要介绍使用DG_broker工具管理切换DG主备。

1、设置primary和standby启动时参数文件为spfile

  1. SQL>  show parameter spfile;

2、设置DG_BROKER_START为TRUE

将主备库的dg_broker_start的VALUE设置为true。

  1. SQL>alter system set dg_broker_start=true scope=both;
  2. SQL>! ps -ef|grep dmon
  3. SQL>show parameter dg_broker_start
  4. SQL>SELECT d.DBID,
  5. d.DB_UNIQUE_NAME,
  6. d.FORCE_LOGGING,
  7. d.FLASHBACK_ON,
  8. d.FS_FAILOVER_STATUS,
  9. d.FS_FAILOVER_CURRENT_TARGET,
  10. d.FS_FAILOVER_THRESHOLD,
  11. d.FS_FAILOVER_OBSERVER_PRESENT,
  12. d.FS_FAILOVER_OBSERVER_HOST
  13. FROM v$database d;

3、配置监听

需要在listener.ora静态添加一个service_name为db_unique_name_DGMGRL.db_domain的注册。这个service_name会在DGMGRL重启数据库的时候用到。通过DGMGRL重启数据库时DMON进程会先将数据库关闭,然后DGMGRL用此service_name通过listener连接到数据库,发出启动命令。tnsnames.ora主备库保持一致。

主库:

  1. [oracle@standbynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

  2. # listener.ora Network Configuration File: /u01/oracle/product/11.2.0/db_1/network/admin/listener.ora
  3. # Generated by Oracle configuration tools.

  4. LISTENER =
  5.  (DESCRIPTION_LIST =
  6.    (DESCRIPTION =
  7.      (ADDRESS_LIST =
  8.        (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521))
  9.      )
  10.    )
  11.  )

  12. SID_LIST_LISTENER =
  13.  (SID_LIST =
  14.    (SID_DESC =
  15.      (GLOBAL_DBNAME=orcl11gdg)
  16.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  17.      (SID_NAME = orcl11g)
  18.    )
  19.    (SID_DESC =
  20.      (GLOBAL_DBNAME = orcl11gdg_DGMGRL.lhr.com)
  21.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  22.      (SID_NAME = orcl11g)
  23.    )
  24.  )

  25. ADR_BASE_LISTENER = /u01/app/oracle
  26. LOGGING_LISTENER = OFF
  27. ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

  1. [oracle@standbynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

  2. tnsorcl11g =
  3.  (DESCRIPTION =
  4.    (ADDRESS_LIST =
  5.      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
  6.    )
  7.    (CONNECT_DATA =
  8.      (SERVER = DEDICATED)
  9.      (SERVICE_NAME = orcl11g)
  10.    )
  11.  )

  12. tnsorcl11gstandby =
  13.  (DESCRIPTION =
  14.    (ADDRESS_LIST =
  15.      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
  16.    )
  17.    (CONNECT_DATA =
  18.      (SERVER = DEDICATED)
  19.      (SERVICE_NAME = orcl11g)
  20.    )
  21.  )
  22. tns_orcl11g_DGMGRL =

  23.  (DESCRIPTION =

  24.    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))

  25.    (CONNECT_DATA =

  26.      (SERVER = DEDICATED)

  27.      (SERVICE_NAME = orcl11g_DGMGRL.lhr.com)

  28.    )

  29.  )
  30. tns_orcl11gstandby_DGMGRL =

  31.  (DESCRIPTION =

  32.    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))

  33.    (CONNECT_DATA =

  34.      (SERVER = DEDICATED)

  35.      (SERVICE_NAME = orcl11gdg_DGMGRL.lhr.com)

  36.    )

备库:

  1. [oracle@primarynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

  2. # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  3. # Generated by Oracle configuration tools.

  4. LISTENER =
  5.  (DESCRIPTION_LIST =
  6.    (DESCRIPTION =
  7.      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  8.      (ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521))
  9.    )
  10.  )
  11. SID_LIST_LISTENER =
  12.  (SID_LIST =
  13.    (SID_DESC =
  14.      (GLOBAL_DBNAME=orcl11g_DGMGRL.lhr.com)
  15.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  16.      (SID_NAME = orcl11g)
  17.    )
  18.  )
  19. ADR_BASE_LISTENER = /u01/app/oracle

image.svg

  1. [oracle@primarynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

  2.  (DESCRIPTION =
  3.    (ADDRESS_LIST =
  4.      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
  5.    )
  6.    (CONNECT_DATA =
  7.      (SERVICE_NAME = orcl11g)
  8.    )
  9.  )
  10. tnsorcl11gstandby =
  11.  (DESCRIPTION =
  12.    (ADDRESS_LIST =
  13.      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
  14.    )
  15.    (CONNECT_DATA =
  16.      (SERVICE_NAME = orcl11g)
  17.    )
  18.  )
  19. tns_orcl11g_DGMGRL =
  20.  (DESCRIPTION =
  21.    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
  22.    (CONNECT_DATA =
  23.      (SERVER = DEDICATED)
  24.      (SERVICE_NAME = orcl11g_DGMGRL.lhr.com)
  25.    )
  26.  )
  27. tns_orcl11gstandby_DGMGRL =
  28.  (DESCRIPTION =
  29.    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
  30.    (CONNECT_DATA =
  31.      (SERVER = DEDICATED)
  32.      (SERVICE_NAME = orcl11gdg_DGMGRL.lhr.com)
  33.    )

  34.  )

4、dgmgrl中配置broker并启用

  1. [oracle@primarynode ~]$ dgmgrl
  2. DGMGRL> connect sys/password@tns_orcl11gdg_DGMGRL
  3. DGMGRL> show configuration

  1. #如果之前有配置,请先移除
  2. DGMGRL> REMOVE CONFIGURATION;
  3. --查看参数文件的状态
  4. DGMGRL> SHOW CONFIGURATION;
  5. #创建新的参数文件CONFIGURATION:
  6. DGMGRL> create configuration 'fsf_orcl11g_lhr' as primary database is 'orcl11gdg' connect identifier is tns_orcl11gstandby_DGMGRL;
  7. --添加备库:
  8. DGMGRL> add database 'orcl11g' as connect identifier is tns_orcl11g_DGMGRL maintained as physical;
  9. --启用参数文件:
  10. DGMGRL> ENABLE CONFIGURATION;
  11. --查看参数文件的状态
  12. DGMGRL> SHOW CONFIGURATION;
  13. --启用备库
  14. DGMGRL> ENABLE DATABASE 'orcl11g'

5、切换主备库

  1. DGMGRL> switchover to orcl11g

6、验证主备是否切换成功

主备库都执行

  1. SQL> set lines 999
  2. SQL> select database_role,open_mode from v$database;

在上图中我们可以看到主备已经完成切换。当然也可以切换保护模式,大家自行测试,这里我再不演示。

重新调至最大可用模式

  1. DGMGRL> EDIT DATABASE 'orcl11g' SET PROPERTY 'LogXptMode'='ASYNC';
  2. DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

遇到oracle DGMGRL ORA-16603报错的解决方法(DG Broker)

备库执行:

  1. [oracle@primarynode dbs]$cd $ORACLE_HOME/dbs
  2. [oracle@primarynode dbs]$rm -rf dr*
  3. SQL> alter system set dg_broker_start=false;
  4. SQL> alter system set dg_broker_start=true;
  5. SQL> show parameter dg_broker

参考链接:

https://blog.csdn.net/tuning_optmization/article/details/79235281

相关文章
|
3月前
|
Oracle 关系型数据库 数据库
手把手教你Oracle DataGuard主备切换(switchover)
手把手教你Oracle DataGuard主备切换(switchover)
425 4
|
6月前
|
运维 Oracle 容灾
Oracle dataguard 容灾技术实战(笔记),教你一种更清晰的Linux运维架构
Oracle dataguard 容灾技术实战(笔记),教你一种更清晰的Linux运维架构
|
3月前
|
Oracle 网络协议 安全
Oracle 11g DataGuard搭建保姆级教程
Oracle 11g DataGuard搭建保姆级教程
174 4
|
6月前
|
Oracle 关系型数据库
oracle 19c 搭建dataguard 简要命令
通过service 完成dg 搭建。
152 0
|
12月前
|
Oracle 关系型数据库 数据库
Flink CDC中oracle dataguard模式下,有没有cdc备库的方案?
Flink CDC中oracle dataguard模式下,有没有cdc备库的方案?
176 1
|
Oracle 网络协议 关系型数据库
Oracle11g DataGuard部署与维护
DataGuard是Oracle高可用必备技能。
|
Oracle 网络协议 关系型数据库
oracle 11g dataguard 配置步骤
oracle 11g dataguard 配置步骤
216 0
|
Oracle 关系型数据库
|
Oracle 容灾 安全
实战篇:生产库升级,容灾库 Oracle DataGuard 如何升级?
实战篇:生产库升级,容灾库 Oracle DataGuard 如何升级?
实战篇:生产库升级,容灾库 Oracle DataGuard 如何升级?
|
运维 Oracle 关系型数据库
实战篇:Oracle DataGuard 出现 GAP 修复完整步骤
实战篇:Oracle DataGuard 出现 GAP 修复完整步骤
实战篇:Oracle DataGuard 出现 GAP 修复完整步骤