管理和维护DG

DG管理和维护

1.DG角色在线转换

1.1 角色（主备）

和DG有关的角色：

primary database 主库，在线服务应用
physical standby database 备库，物理备库，在线备份主库数据与主库完全一样
logical standby database 备库，逻辑备库，与主库数据保持一致但是物理结构可能不同。

1.2 角色切换（主备切换）

角色切换的两种情况：

switchover：人为有计划的进行角色切换（例如主库升级，硬件升级等）
failover：主库崩溃，配置相关参数可以自动实现切换
角色切换的顺序：
primary database先切换成备库standby模式，然后再选择一个备库升级为主库。

2.切换前准备

2.1 确定主备相关参数：

2.1.1 查看主库角色和保护模式及级别

SYS@proe> select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY          MAXIMUM AVAILABILITY RESYNCHRONIZATION

2.1.2 通过参数查看主库对应的备库信息

SYS@proe>show parameter fal_server;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_server                           string      stddb

2.1.3 查看主库对应的convert参数

SYS@proe>show parameter db_file_name_convert;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
db_file_name_convert                 string      /u01/app/oracle/oradata/stddb/,/u01/app/oracle/oradata/proe /

2.1.4 查看主库归档位置以及对应备库信息

SYS@proe> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=stddb valid_for=(onlin
                                                 e_logfiles,primary_role) db_un
                                                 ique_name=stddb
SYS@stddb> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=stddb valid_for=(onlin
                                                 e_logfiles,primary_role) db_un
                                                 ique_name=pridb

2.2 网络检查

2.2.1 使用tnsping

[oracle@11g ~]$ tnsping stddb

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-JUL-2020 12:37:21

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 11gtest)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stddb)))
OK (20 msec)

[oracle@11gtest ~]$ tnsping proe

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 19-JUL-2020 17:31:35

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 11g)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = proe)))
OK (110 msec)

2.2.2 进行远程登录测试

[oracle@11gtest ~]$ sqlplus  sys/123456@proe as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 19 17:33:18 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@proe>

[oracle@11g ~]$ sqlplus  sys/123456@stddb as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 22 12:39:40 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@stddb>

3.切换方法

3.1 主库转备库

注意：原则上至多只能有一个主库，但是备库可以多个，所以需要先进行主库转备库操作。

3.1.1 查看主库角色切换状态

SYS@proe>select name,database_role,switchover_status from v$database;

NAME                      DATABASE_ROLE    SWITCHOVER_STATUS
------------------------- ---------------- --------------------
PROE                      PRIMARY          TO STANDBY

3.1.2 正常主库切换备库命令

SYS@proe>alter database commit to switchover to physical standby;

Database altered.

3.1.2 执行切换后的操作

SYS@proe>shutdown abort
ORACLE instance shut down.
SYS@proe>startup mount;
ORACLE instance started.
# 查看此时角色切换状态
SYS@proe>select name,database_role,switchover_status from v$database;

NAME                      DATABASE_ROLE    SWITCHOVER_STATUS
------------------------- ---------------- --------------------
PROE                      PHYSICAL STANDBY RECOVERY NEEDED
# 查看角色保护模式和级别
SYS@proe> select name,database_role,protection_mode,protection_level from v$database;

NAME                      DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
------------------------- ---------------- -------------------- --------------------
PROE                      PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

3.1.3 异常切换命令

"TO STANDBY"意味着主库此时可以切换为备库，如果状态为SESSION ACTIVE可以使用以下命令切换。然后执行上述切换后的操作。

SYS@proe>alter database commit to switchover to physical standby with session shutdown;

3.2 备库转主库

3.2.1 查看备库角色切换状态

SYS@stddb>select name,database_role,switchover_status from v$database;

NAME      DATABASE_ROLE    SWITCHOVER_STATUS
--------- ---------------- --------------------
PROE      PHYSICAL STANDBY TO PRIMARY

3.2.2 备库切换主库命令

SYS@stddb>alter database commit to switchover to primary;

Database altered.

3.2.2 执行切换后的操作

# 查看此时角色切换状态
SYS@stddb>select name,database_role,switchover_status from v$database;

NAME      DATABASE_ROLE    SWITCHOVER_STATUS
--------- ---------------- --------------------
PROE      PRIMARY          NOT ALLOWED
# 查看角色保护模式和级别
SYS@stddb>select name,database_role,protection_mode,protection_level from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
--------- ---------------- -------------------- --------------------
PROE      PRIMARY          MAXIMUM PERFORMANCE  UNPROTECTED
# 备库切换为主库后实例状态
SYS@stddb>select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
stddb            MOUNTED
# 将数据库启动到open
SYS@stddb>alter database open;

Database altered.
# 再次查看转换后的角色保护模式及级别，可以看到和原来的主库一致。
SYS@stddb>select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

3.3 启动现在备库日志应用服务

原来的主库已经切换为备库，查看此时数据库打开模式

SYS@proe>select name,open_mode from v$database;

NAME                      OPEN_MODE
------------------------- --------------------
PROE                      MOUNTED

数据库启动到open

SYS@proe>alter database open;

Database altered.

启动数据库的日志应用服务

SYS@proe>alter database recover managed standby database using current logfile disconnect;
Database altered.
# 查看数据库打开模式
SYS@proe>select name,open_mode from v$database;

NAME                      OPEN_MODE
------------------------- --------------------
PROE                      READ ONLY WITH APPLY

一次完整的主备切换完成

来自为知笔记(Wiz)