Oracle 18c DG切换保护模式 和 主备库角色
👉 本文共约1397个字，系统预计阅读时间或需6分钟。
1 查看DG是否正常
搭建好DG后腰验证DG是否同步正常，可以直接在主库创建测试表，看数据能否同步过去，另外就是查看相关配置是否正常，然后切换下日志，看备库是否正常应用。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | #主库查看对应的archive_dest 是否正常，这里正常可以保证归档数据可以传送到备库： [dave@www.cndba.cn dbs]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Thu Aug 30 15:30:14 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select DEST_NAME,STATUS,ERROR from v$archive_dest where DEST_ID=2; DEST_NAME STATUS ERROR ------------------------- ------------------ -------------------------------------- LOG_ARCHIVE_DEST_2 VALID #备库检查MRP进程是否启动： SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# ------------------ ------------------------ ---------- ARCH CONNECTED 0 DGRD ALLOCATED 0 DGRD ALLOCATED 0 ARCH CLOSING 17 ARCH CONNECTED 0 ARCH CLOSING 18 MRP0 APPLYING_LOG 19 RFS IDLE 0 RFS IDLE 19 RFS IDLE 0 10 rows selected.v #如果没有启动，手工启动MRP: SQL> alter database recover managed standby database disconnect; Database altered. #备库检查日志是否apply，因为可能日志传过来，但并没有入库，在主备库同时查询，2边一致，DG就是同步的。 [dave@www.cndba.cn dbs]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Thu Aug 30 15:30:14 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select sequence#,applied from v$archived_log; SEQUENCE# APPLIED ---------- ------------------ 9 YES 10 YES 11 YES 12 YES 13 YES 14 YES 15 YES 16 YES 17 YES 18 YES 10 rows selected. |
2 启用Real-Time Apply
在Oracle 12c 之前的Data Guard 环境中，默认情况下备库apply redo 是从备库的归档文件中读数据的。在Oracle 12c 中，默认的策略改成直接从standby redo log file中读取，采用real-time apply的方式进行。
对于物理standby，如果配置了standby redo日志并且处于ARCHIVELOG模式，在执行alter database recover managed standby database语句启动MRP时默认就是Real-Time Apply。
如果想取消MRP，执行以下语句：
1 | SQL> alter database recover managed standby database cancel; |
关于该特性的详细说明，可以参考我的博客：
Oracle 12c 新特性 — DG 默认使用 Real-Time Apply
https://www.cndba.cn/cndba/dave/article/213
3 DG保护模式切换
Oracle DG有三种不同的保护模式供选择：最大可用(Maximum Availability)，最大性能(Maximum Performance)，最大保护(Maximum Protection)。关于他们的区别这里不再描述，可以参考官方手册的说明。 Oracle 默认采用的是最大性能。
1 2 3 4 5 | SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL ---------------------------------------- -------------------------------- ------------------------ MAXIMUM PERFORMANCE PRIMARY MAXIMUM PERFORMANCE |
三种模式之前切换的时候，有2个必要条件：
- 配置了standby redo log
- Log_archve_dest_2 参数需要配置sync， affirm的方式， 这个我们在安装的时候已经配置过。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | alter system set log_archive_dest_2='service=cndba_s lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=cndba_s' scope=both ; SQL> select member from v$logfile; MEMBER ---------------------------------------------------------------------------------------- /u01/app/oracle/oradata/cndba/redo01.log /u01/app/oracle/oradata/cndba/redo02.log /u01/app/oracle/oradata/cndba/redo03.log /u01/app/oracle/oradata/cndba/stdredo01.log /u01/app/oracle/oradata/cndba/stdredo02.log /u01/app/oracle/oradata/cndba/stdredo03.log /u01/app/oracle/oradata/cndba/stdredo04.log SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_dest_2 string service=cndba_s lgwr affirm sync valid_for=(online_logfiles, primary_role) db_unique_name=cndba_s |
所以三种模式之间可以随意切换。
3.1 最大性能切换到最大可用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | #主库： [dave@www.cndba.cn dbs]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Thu Aug 30 15:30:14 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL ---------------------------------------- -------------------------------- --------------------------- MAXIMUM PERFORMANCE PRIMARY MAXIMUM PERFORMANCE #在主库进行切换： SQL> alter database set standby database to maximize availability; Database altered. #主库验证： SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL ---------------------------------------- -------------------------------- -------------------------- MAXIMUM AVAILABILITY PRIMARY MAXIMUM AVAILABILITY #备库验证： SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL ---------------------------------------- -------------------------------- ------------------------- MAXIMUM AVAILABILITY PHYSICAL STANDBY MAXIMUM AVAILABILITY |
3.2 最大可用切换到最大保护
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | #主库： [dave@www.cndba.cn dbs]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Thu Aug 30 15:30:14 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL ---------------------------------------------------------- ---------------------------------------- MAXIMUM AVAILABILITY PRIMARY MAXIMUM AVAILABILITY #在主库进行切换： SQL> alter database set standby database to maximize protection; Database altered. #主库验证： SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL ------------------------------------------------------- ---------------------------------------- MAXIMUM PROTECTION PRIMARY MAXIMUM PROTECTION #备库验证： SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL ------------------------------------------------------- ---------------------------------------- MAXIMUM PROTECTION PHYSICAL STANDBY MAXIMUM PROTECTION |
3.3 最大保护切换到最大可用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | #主库： [dave@www.cndba.cn dbs]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Thu Aug 30 15:30:14 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL ---------------------- -------------------------------- ---------------------------------------- MAXIMUM PROTECTION PRIMARY MAXIMUM PROTECTION #在主库进行切换： SQL> alter database set standby database to maximize availability; Database altered. #主库验证： SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL -------------------------- -------------------------------- ---------------------------------------- MAXIMUM AVAILABILITY PRIMARY MAXIMUM AVAILABILITY #备库验证： SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL ------------------------- -------------------------------- ---------------------------------------- MAXIMUM AVAILABILITY PHYSICAL STANDBY MAXIMUM AVAILABILITY |
3.4 最大可用切换到最大性能
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | #主库： [dave@www.cndba.cn dbs]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Thu Aug 30 15:30:14 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL ---------------------------- -------------------------------- ---------------------------------------- MAXIMUM AVAILABILITY PRIMARY MAXIMUM AVAILABILITY #在主库进行切换： SQL> alter database set standby database to maximize performance; Database altered. #主库验证： SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL ------------------------- -------------------------------- ---------------------------------------- MAXIMUM PERFORMANCE PRIMARY MAXIMUM PERFORMANCE #备库验证： SQL> select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL -------------------------- -------------------------------- ---------------------------------------- MAXIMUM PERFORMANCE PHYSICAL STANDBY MAXIMUM PERFORMANCE |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
