签到成功

知道了

CNDBA社区CNDBA社区

Oracle 18c Data Guard 切换保护模式

2018-07-19 23:00 2159 0 原创 Oracle 18c
作者： leo
本文链接：http://www.cndba.cn/leo1990/article/2904

1.1.切换保护模式示例

默认DG保护模式是最大性能，在此基础上切换到其他保护模式。http://www.cndba.cn/leo1990/article/2904http://www.cndba.cn/leo1990/article/2904

http://www.cndba.cn/leo1990/article/2904
http://www.cndba.cn/leo1990/article/2904http://www.cndba.cn/leo1990/article/2904http://www.cndba.cn/leo1990/article/2904
http://www.cndba.cn/leo1990/article/2904
http://www.cndba.cn/leo1990/article/2904
http://www.cndba.cn/leo1990/article/2904
http://www.cndba.cn/leo1990/article/2904

1.1.1.DG最大性能切换最大可用

1）查看主备库当前DG保护模式
查看主库当前保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PERFORMANCE       PRIMARY      MAXIMUM PERFORMANCE     READ WRITE
查看备库当前保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PERFORMANCE       PHYSICAL STANDBY       MAXIMUM PERFORMANCE     READ ONLY WITH APPLY
2）修改参数
SQL> alter system set log_archive_dest_2='service=pdbcndba_s sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s';

System altered.
3）切换保护模式
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Database altered.
4）验证查看主备库保护模式是否切换成功
查看主库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM AVAILABILITY       PRIMARY      MAXIMUM AVAILABILITY     READ WRITE
查看备库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM AVAILABILITY       PHYSICAL STANDBY       MAXIMUM AVAILABILITY     READ ONLY WITH APPLY
5）备库修改参数
此步骤可以不用做，只是为了方便switchover。
SQL> alter system set log_archive_dest_2='service=pdbcndba_p sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_p';

System altered.

1.1.2.DG最大可用切换到最大保护

1）查看主备库当前保护模式
查看主库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM AVAILABILITY       PRIMARY      MAXIMUM AVAILABILITY     READ WRITE
查看备库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM AVAILABILITY       PHYSICAL STANDBY       MAXIMUM AVAILABILITY     READ ONLY WITH APPLY

2）切换保护模式
因为最大可用和最大保护可以使用相同的参数这里直接可以做切换
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;  

Database altered.
3）验证主备库保护模式是否切换成功
主库查看保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PROTECTION       PRIMARY      MAXIMUM PROTECTION     READ WRITE
备库查看保护模式
SQL>  select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PROTECTION       PHYSICAL STANDBY       MAXIMUM PROTECTION     READ ONLY WITH APPLY

1.1.3.DG最大保护切换到最大可用

1）查看主备库当前DG保护模式
主库查看保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PROTECTION       PRIMARY      MAXIMUM PROTECTION     READ WRITE
备库查看保护模式
SQL>  select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PROTECTION       PHYSICAL STANDBY       MAXIMUM PROTECTION     READ ONLY WITH APPLY
2）切换保护模式
因为最大可用和最大保护可以使用相同的参数这里直接可以做切换
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Database altered.
4）验证查看主备库保护模式是否切换成功
查看主库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM AVAILABILITY       PRIMARY      MAXIMUM AVAILABILITY     READ WRITE
查看备库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM AVAILABILITY       PHYSICAL STANDBY       MAXIMUM AVAILABILITY     READ ONLY WITH APPLY

1.1.4.DG最大可用切换到最大性能

1）查看主备库当前DG保护模式
查看主库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM AVAILABILITY       PRIMARY      MAXIMUM AVAILABILITY     READ WRITE
查看备库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM AVAILABILITY       PHYSICAL STANDBY       MAXIMUM AVAILABILITY     READ ONLY WITH APPLY
2）修改参数
SQL> alter system set log_archive_dest_2='service=pdbcndba_s async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s';

System altered.
3）切换保护模式
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.
4）验证查看主备库保护模式是否切换成功
查看主库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PERFORMANCE       PRIMARY      MAXIMUM PERFORMANCE     READ WRITE
查看备库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PERFORMANCE       PHYSICAL STANDBY       MAXIMUM PERFORMANCE     READ ONLY WITH APPLY
5）备库修改参数
此步骤可以不用做，只是为了方便switchover。
SQL> alter system set log_archive_dest_2='service=pdbcndba_p async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_p';

System altered.

1.1.5.DG最大性能切换到最大保护

1）查看主备库当前DG保护模式
查看主库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PERFORMANCE       PRIMARY      MAXIMUM PERFORMANCE     READ WRITE
查看备库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PERFORMANCE       PHYSICAL STANDBY       MAXIMUM PERFORMANCE     READ ONLY WITH APPLY
2）修改参数
SQL> alter system set log_archive_dest_2='service=pdbcndba_s sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s';

System altered.
3）切换保护模式
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance
根据提示DG最大性能模式切换到最大保护模式是需要主库在MOUNT状态下切换，还有另外一种方法在线可以切换，那就是先切换到最大可用模式在切换到最大保护模式。
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Database altered.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

Database altered.
4）验证查看主备库保护模式是否切换成功
查看主库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PROTECTION       PRIMARY      MAXIMUM PROTECTION     READ WRITE
查看备库保护模式
SQL>  select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PROTECTION       PHYSICAL STANDBY       MAXIMUM PROTECTION     READ ONLY WITH APPLY
5）备库修改参数
此步骤可以不用做，只是为了方便switchover。
SQL> alter system set log_archive_dest_2='service=pdbcndba_p sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_p';

System altered.

1.1.6.DG最大保护切换到最大性能

1）查看主备库当前DG保护模式
查看主库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PROTECTION       PRIMARY      MAXIMUM PROTECTION     READ WRITE
查看备库保护模式
SQL>  select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PROTECTION       PHYSICAL STANDBY       MAXIMUM PROTECTION     READ ONLY WITH APPLY
2）修改参数
SQL> alter system set log_archive_dest_2='service=pdbcndba_s async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s';
alter system set log_archive_dest_2='service=pdbcndba_s async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16159: Cannot change protected standby destination attributes
3）切换保护模式
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.
4）再次修改参数
SQL> alter system set log_archive_dest_2='service=pdbcndba_s async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s';

System altered.
5）验证查看主备库保护模式是否切换成功
查看主库保护模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PERFORMANCE       PRIMARY      MAXIMUM PERFORMANCE     READ WRITE
查看备库保护模式
SQL>  select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE        DATABASE_ROLE      PROTECTION_LEVEL     OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
MAXIMUM PERFORMANCE       PHYSICAL STANDBY       MAXIMUM PERFORMANCE     READ ONLY WITH APPLY
6）备库修改参数
此步骤可以不用做，只是为了方便switchover。
SQL> alter system set log_archive_dest_2='service=pdbcndba_p async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_p';

System altered.

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
leo

leo

关注

坚持你的坚持

  • 202
    原创
  • 0
    翻译
  • 39
    转载
  • 16
    评论
  • 访问：674842次
  • 积分：1270
  • 等级：核心会员
  • 排名：第8名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ