Oracle 19c - 使用 DGMGRL 执行 Dataguard switchover切换
Oracle 19c - 使用 DGMGRL 执行 Dataguard 切换
描述:-
- 我们将使用 SWITCHOVER 命令切换主数据库和备用数据库的角色
- 切换是主数据库与其备用数据库之一之间的角色反转。切换可保证不会丢失数据,通常用于主系统的计划内维护。在切换期间,主数据库将转换为备用角色,备用数据库将转换为主角色。
- 所有参与的数据库都处于良好状态,没有任何错误或警告。
- 主数据库和备用数据库的状态为 TRANSPORT-ON 和 APPLY-ON
- 备用重做日志文件在主数据库上配置。
- 代理配置文件将更新以反映角色的更改。
- 重新启动新的备用数据库并开始重做应用服务。
- 新的主数据库以 read/wirte 模式打开,并启动 redo 传输服务。
环境详情:-
ENV Detail | Primary | Standby |
---|---|---|
DB Unique | orcl | orcl_stdy |
DB Name | orcl | orcl |
hostname:Server IP | db1:192.168.32.172 | db2:192.168.32.172 |
1.检查当前主库
使用 SHOW DATABASE VERBOSE 命令检查主数据库的状态、运行状况和属性
[oracle@db1 ~]$ dgmgrl sys/Oracle123
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 11 15:53:03 202 5
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved .
Welcome to DGMGRL, type "help" for information.
Connected to "orcl"
Connected as SYSDBA.
------------------------------------------------------------------
DGMGRL> SHOW CONFIGURATION
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stdy - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 5 seconds ago)
---------------------------------------------------------------------
#检查主:
DGMGRL> SHOW DATABASE VERBOSE orcl;
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Properties:
DGConnectIdentifier = 'orcl'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'db1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/orcl/orcl/trace/drcorcl.log
Database Status:
SUCCESS
2.检查作为切换目标的备库
DGMGRL> SHOW DATABASE VERBOSE orcl_stdy
Database - orcl_stdy
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 7.00 KByte/s
Active Apply Rate: 968.00 KByte/s
Maximum Apply Rate: 975.00 KByte/s
Real Time Query: ON
Instance(s):
orcl
Properties:
DGConnectIdentifier = 'orcl_stdy'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'db2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_stdy_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/orcl_stdy/orcl/trace/alert_orcl.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/orcl_stdy/orcl/trace/drcorcl.log
Database Status:
SUCCESS
DGMGRL> show database orcl_stdy
Database - orcl_stdy
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 7.00 KByte/s
Real Time Query: ON
Instance(s):
orcl
Database Status:
SUCCESS
3.验证数据库,确保准备好执行切换(主上)
#切换前做好检查
DGMGRL> VALIDATE DATABASE orcl
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
orcl: Off
Managed by Clusterware:
orcl: NO
Validating static connect identifier for the primary database orcl...
The static connect identifier allows for a connection to database "orcl".
#数据库层面检查
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
DGMGRL> SHOW CONFIGURATION;
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stdy - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 51 seconds ago)
4.执行切换
#执行切换
DGMGRL> switchover to orcl_stdy;
Performing switchover NOW, please wait...
Operation requires a connection to database "orcl_stdy"
Connecting ...
Connected to "orcl_stdy"
Connected as SYSDBA.
New primary database "orcl_stdy" is opening...
Operation requires start up of instance "orcl" on database "orcl"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "orcl"
Database mounted.
Database opened.
Connected to "orcl"
Switchover succeeded, new primary is "orcl_stdy"
DGMGRL>
DGMGRL>
DGMGRL>
#切换完成
检查状态
Old Primary:
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN orcl PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
......
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
service_names string orcl
......
New Primary:
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN orcl PRIMARY MAXIMUM PERFORMANCE
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
......
db_name string orcl
db_unique_name string orcl_stdy
global_names boolean FALSE
instance_name string orcl
service_names string orcl_stdy
......
#我这里两边环境名称除了db_unique_name都是一样的,所有名称一样
DGMGRL> SHOW CONFIGURATION;
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
orcl_stdy - Primary database
orcl - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 32 seconds ago)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库