首页 > 数据库 >Oracle 19c DG命令手动Switchover

Oracle 19c DG命令手动Switchover

时间：2025-01-16 11:33:48浏览次数：1  
标签：Switchover switchover database standby DG PID 2025 Oracle 3859

目录

1.Oracle 19c命令手动Switchover

1.1switchover主要步骤

要在 DataGuard 环境中执行 Switchover，我们必须执行以下步骤：

  • 在当前主数据库上：
    • alter database switchover toSTANDBY_DB_UNIQUE_NAME;
  • 在旧的备用数据库（新的主数据库）上：
    • alter database open;
  • 在旧的主数据库（新备用数据库）上：
    • startup mount
    • alter database recover managed standby database disconnect from session;

2.命令执行切换switchover

我的环境：

ENV Detail Primary Standby
DB Unique orcl orcl_stby
DB Name orcl orcl
hostname:Server IP db1:192.168.32.172 db2:192.168.32.172

主库上检验备库

[oracle@db1 ~]$ sqlplus / as sysdba

SQL> alter database switchover to orcl_stdy verify;
Database altered.



#报错可以在告警日志中查看SWITCHOVER VERIFY关键字
#示例（这里我故意取消LOG_ARCHIVE_DEST配置，重新配置后恢复）：
SQL> alter database switchover to orcl_stdy verify;
alter database switchover to orcl_stdy verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

日志vim /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log中：
SWITCHOVER VERIFY: Send VERIFY request to switchover target ORCL_STDY
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected.

主库开启切换

SQL> alter database switchover to orcl_stdy;

Database altered.

旧版本命令：

In previous releases this step was done in two commands:

  • on Primary: alter database commit to switchover to standby;
  • on Standby: alter database commit to switchover to primary;

主库切换日志：

2025-01-16T10:23:03.422463+08:00
alter database switchover to orcl_stdy
2025-01-16T10:23:03.422535+08:00
NET  (PID:3859): The Time Management Interface (TMI) is being enabled for role transition
NET  (PID:3859): information.  This will result in messages beingoutput to the alert log
NET  (PID:3859): file with the prefix 'TMI: '.  This is being enabled to make the timing of
NET  (PID:3859): the various stages of the role transition available for diagnostic purposes.
NET  (PID:3859): This output will end when the role transition is complete.
TMI: dbsdrv switchover to target BEGIN 2025-01-16 10:23:03.422825
NET  (PID:3859): Starting switchover [Process ID: 3859]
TMI: kcv_switchover_to_target convert to physical BEGIN 2025-01-16 10:23:03.512708
2025-01-16T10:23:03.512873+08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 3859] (orcl)
NET  (PID:3859): Waiting for target standby to receive all redo
2025-01-16T10:23:03.514556+08:00
NET  (PID:3859): Waiting for all non-current ORLs to be archived
2025-01-16T10:23:03.514658+08:00
NET  (PID:3859): All non-current ORLs have been archived
2025-01-16T10:23:03.514749+08:00
NET  (PID:3859): Waiting for all FAL entries to be archived
2025-01-16T10:23:03.514809+08:00
NET  (PID:3859): All FAL entries have been archived
2025-01-16T10:23:03.514907+08:00
NET  (PID:3859): Waiting for LAD:2 to become synchronized
2025-01-16T10:23:04.515416+08:00
NET  (PID:3859): Active, synchronized Physical Standby switchover target has been identified
NET  (PID:3859): Preventing updates and queries at the Primary
2025-01-16T10:23:05.516382+08:00
NET  (PID:3859): Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 62 has been fixed
Switchover: Primary highest seen SCN set to 0x0000000000328170
NET  (PID:3859): Noswitch archival of T-1.S-62
NET  (PID:3859): End-Of-Redo Branch archival of T-1.S-62
NET  (PID:3859): LGWR is scheduled to archive to LAD:2 after log switch
NET  (PID:3859): SRL selected for T-1.S-62 for LAD:2
NET  (PID:3859): Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
NET  (PID:3859): Waiting for target standby to apply all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3859.trc
NET  (PID:3859): Converting the primary database to a new standby database
Clearing standby activation ID 1716972720 (0x6656ecb0)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
Archivelog for thread 1 sequence 62 required for standby recovery
Offline data file 5 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 6 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 8 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 9 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 10 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 11 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 12 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
TMI: kcv_switchover_to_target convert to physical END 2025-01-16 10:23:05.834689
NET  (PID:3859): Sending request(convert to primary database) to switchover target ORCL_STDY
2025-01-16T10:23:16.333104+08:00
NET  (PID:3859): Switchover complete. Database shutdown required
USER (ospid: 3859): terminating the instance
2025-01-16T10:23:17.356528+08:00
Instance terminated by USER, pid = 3859
TMI: dbsdrv switchover to target END 2025-01-16 10:23:17.356548
Completed: alter database switchover to orcl_stdy
Shutting down ORACLE instance (abort) (OS id: 3859)
License high water mark = 15
2025-01-16T10:23:17.448848+08:00
Instance shutdown complete (OS id: 3859)

备库切换日志：

SWITCHOVER: received request 'ALTER DATABASE COMMIT TO SWITCHOVER  TO PRIMARY' from primary database.
2025-01-16T10:23:05.674778+08:00
ALTER DATABASE SWITCHOVER TO PRIMARY (orcl)
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2025-01-16 10:23:05.679175
Switchover: Media recovery is still active
 rmi (PID:22584): Role Change: Canceling MRP - no more redo to apply
2025-01-16T10:23:05.687106+08:00
......................................
......................................
......................................

2025-01-16T10:23:14.927420+08:00
CLOSE: all sessions shutdown successfully.
alter pluggable database all close
Completed: alter pluggable database all close
PDB$SEED(2):JIT: pid 22584 requesting stop
PDB$SEED(2):Buffer Cache flush deferred for PDB 2
2025-01-16T10:23:15.949594+08:00


IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
Stopping Emon pool
Buffer Cache invalidation for all PDBs started
Buffer Cache invalidation for all PDBs complete
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl_stdy/orcl/trace/orcl_rmi_22584.trc
SwitchOver after complete recovery through change 3309936
 rmi (PID:22584): ORL pre-clearing operation disabled by switchover
Online log /u01/app/oracle/oradata/ORCL_STDY/onlinelog/o1_mf_1_mqgon8wp_.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/ORCL_STDY/onlinelog/o1_mf_1_mqgon902_.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/ORCL_STDY/onlinelog/o1_mf_2_mqgoncbc_.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/ORCL_STDY/onlinelog/o1_mf_2_mqgoncf0_.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/ORCL_STDY/onlinelog/o1_mf_3_mqgongc0_.log: Thread 1 Group 3 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/ORCL_STDY/onlinelog/o1_mf_3_mqgongg7_.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 3309934
 rmi (PID:22584): RT: Role transition work is not done
 rmi (PID:22584): The Time Management Interface (TMI) is being enabled for role transition
 rmi (PID:22584): information.  This will result in messages beingoutput to the alert log
 rmi (PID:22584): file with the prefix 'TMI: '.  This is being enabled to make the timing of
 rmi (PID:22584): the various stages of the role transition available for diagnostic purposes.
 rmi (PID:22584): This output will end when the role transition is complete.
 rmi (PID:22584): Redo network throttle feature is disabled at mount time
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
2025-01-16T10:23:16.080372+08:00
 rmi (PID:22584): Database role cleared from PHYSICAL STANDBY [kcvs.c:1030]
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2025-01-16 10:23:16.080538
SWITCHOVER: completed request from primary database.

新主库（原备库）启动

#日志中可以看到，已经是Mount ---Switchover: Complete - Database mounted as primary
[oracle@db2 ~]$ sqlplus / as sysdba

SQL> alter database open;

Database altered.


#PDB别忘了启动
SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

新主库已经可以用了

旧主库（新备库）恢复

[oracle@db1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 16 10:39:52 2025
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1526723568 bytes
Fixed Size                  9135088 bytes
Variable Size             956301312 bytes
Database Buffers          553648128 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

#open
#ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

验证新的数据库角色

旧主库（新备库）

可以验证以前的主数据库的当前状态现在是 Physical Standby：

[oracle@db1 ~]$ sqlplus / as sysdba
SQL> select name,open_mode,database_role,switchover_status from v$database ;

NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORCL      MOUNTED              PHYSICAL STANDBY NOT ALLOWED

新主库（原备库）

我们可以看到以前的 Standby 数据库的当前状态现在是 Primary：

SQL> select name,open_mode,database_role,switchover_status from v$database ;

NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORCL      READ WRITE           PRIMARY          TO STANDBY

3.回切

恢复到切换之前

在新主库（原备库）上switchover切换

检测

#在新主库（原备库）上
SQL> alter database switchover to orcl verify;
alter database switchover to orcl verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

---------------------------------------------------------------------------------
#这是由于我未设置log_file_name_convert导致
SQL> alter system set db_file_name_convert='/STBYDB/','/PRMYDB/' scope=spfile;
SQL> alter system set log_file_name_convert='/STBYDB/','/PRMYDB/' scope=spfile;

#在告警日志中：
SWITCHOVER VERIFY: Send VERIFY request to switchover target ORCL
SWITCHOVER VERIFY WARNING: switchover target has dirty online redo logfiles that require clearing. It takes time to clear online redo logfiles. This may slow down switchover process.
ORA-16475 signalled during: alter database switchover to orcl verify...
#懒得配置，直接忽略了，不影响，清理redo可能会稍慢

切换

SQL> alter database switchover to orcl;

Database altered.

打开主库（原主库）

[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 16 11:14:03 2025
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter database open;

Database altered.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO



SQL> select name,open_mode,database_role,switchover_status from v$database ;

NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORCL      READ WRITE           PRIMARY          TO STANDBY

恢复备库

[oracle@db2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 16 11:15:44 2025
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1526723568 bytes
Fixed Size                  9135088 bytes
Variable Size             889192448 bytes
Database Buffers          620756992 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.


SQL> select name,open_mode,database_role,switchover_status from v$database ;

NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORCL      MOUNTED              PHYSICAL STANDBY NOT ALLOWED

###################
#open

数据测试
SQL> select name,open_mode,database_role,switchover_status from v$database ;

NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORCL      READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

标签：Switchover,switchover,database,standby,DG,PID,2025,Oracle,3859
From： https://www.cnblogs.com/shipment/p/18674678

相关文章

  • 【YashanDB知识库】YMP校验从yashandb同步到oracle的数据时，字段timestamp(0)出现不一
    本文内容来自YashanDB官网，原文内容请见https://www.yashandb.com/newsinfo/7901520.html?templateId=1718516问题现象在YMP校验过程中，从yashandb同步到oracle的数据时，字段timestamp(0)出现不一致问题的风险及影响YMP校验出现数据内容不一致问题影响的版本yashandb版本：23.2......
  • 2025 年宣布一件大事，Oracle 一键安装脚本开源了！
    大家好，这里是公众号DBA学习之路，致力于分享数据库领域相关知识。目录前言Oracle一键安装脚本脚本下载环境信息安装前准备Centos7.9Redhat8.10脚本参数一键安装11GR219C写在最后前言你没看错，就是Oracle数据库一键安装脚本部分开源了！之前很多朋友咨询我脚本......
  • C# Oracle带参数执行Sql语句
    1、封装执行函数，入参：sql语句和参数数组publicintUpdateRecord(stringsql,paramsOracleParameter[]parameters){intiResult=0;using(varconn=newOracleConnection(TeJianConn)){conn.Open();OracleCommandcommand=co......
  • Navicat连接Oracle、新建表空间、新建用户等操作
    由于工作的原因需要使用Oracle数据库，这次尝试用Navicat来连接Oracle数据库，步骤如下：1、新建连接，选择Oracle2、依次输入连接名、主机、端口、服务名、用户名、密码，先测试连接一下3、提示“连接成功”即可使用。4、Orcale的“库/表空间”的概念理解为用户，每建立一个“库/表空间”......
  • Oracle创建dblink（实现跨数据库查询）纯干货
    介绍     创建dblink实现跨库查询，创建本地数据库对远端数据库的dblink后，就可以像操作本地数据库一样操作远端数据库，这是Oracle给我们提供一个功能，让我们的跨库查询变得非常简单便捷。使用1.查看用户是否有创建dblink的权限(一般管理员用户SYS都有权限)select*fro......
  • Oracle查询表空间使用情况速度很慢
    现象监控表空间使用率及空间剩余量是数据库日常维护的重要内容。有时候在系统运行很长一段时间后，我们再去查询表空间使用情况，发现相同的查询语句执行时间会变长。这是因为我们在查询表空间使用情况的时候，需要从dba_free_space视图中获取剩余空间大小，如果该视图中对象过多，就......
  • Oracle系列---【ORA-01017用户名密码无效】
    1.问题项目启动时，报：ORA-01017用户名密码不对的错误，但是没有报具体哪个库错误，因为我是多数据源的项目。2.解决方案2.1定位到连接数据报错的用户和库名select*fromdba_audit_sessionwhereACTION_NAME='LOGON'ANDRETURNCODE=1017ORDERBYTIMESTAMPDESC;1.OS_USERNAM......
  • Oracle系列---【Oracle中密码的策略如何设置】
    在Oracle数据库中，profile是一个与用户关联的配置集合，用于控制用户账户的资源使用情况和密码策略。例如，profile可以定义用户的密码过期时间、账户锁定策略、并发会话数限制等。简单来说，profile是一组规则的集合，这些规则可以应用到一个或多个数据库用户，从而限制或管理他们的行......
  • 高级QT Widgets图形绘制技巧
    高级QTWidgets图形绘制技巧补天云火鸟博客创作软件补天云网站1Qt_Widgets基础与图形系统1.1Qt_Widgets概述1.1.1Qt_Widgets概述Qt_Widgets概述QtWidgets概述引言在软件开发领域，Qt框架因其跨平台性、强大的图形界面设计能力以及丰富的类库支持而备受开发者青睐......
  • DOMjudge8.2.3配置DOMserver（基于Docker一键配置，全网最详细，看完包会配）
    文章目录前言配置方式1：手动配置nginx+php-fpm配置方式2：Docker一键安装1.部署MariaDB数据库2.部署DOMserver3.获取初始admin密码以及judgehost密码4.修改grub5.测试6.修改配置文件6.0前言（修改配置必看）6.1[configchecker]-MySQLsettings修改常见问题及对应解决......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99