Oracle 中 Failover 和 Switchover 的区别（oracle switchover_status）

25-04-29 2

在这篇文章中，我们将带领您了解Oracle中Failover和Switchover的区别的全貌，包括oracleswitchover_status的相关情况。同时，我们还将为您介绍有关-failove

在这篇文章中，我们将带领您了解Oracle 中 Failover 和 Switchover 的区别的全貌，包括oracle switchover_status的相关情况。同时，我们还将为您介绍有关-failover-abort-not-elected master mymaster问题、11G、12C Data Guard Physical Standby Switchover 转换参考手册、12c DataGuard switchover to 'primary'、4.1 ORACLE DATAGUARD SWITCHOVER 步骤的知识，以帮助您更好地理解这个主题。

本文目录一览：

Oracle 中 Failover 和 Switchover 的区别（oracle switchover_status）
-failover-abort-not-elected master mymaster问题
11G、12C Data Guard Physical Standby Switchover 转换参考手册
12c DataGuard switchover to 'primary'
4.1 ORACLE DATAGUARD SWITCHOVER 步骤

Oracle 中 Failover 和 Switchover 的区别（oracle switchover_status）

1. 什么是 Failover？
2. 什么是 Switchover？
3. 配置 Connect Time Failover
4. 配置 Transparent Application Failover

1. 什么是 Failover?

A failover is when the primary database is unavailable. Failover is performed only in the event of a catastrophic failure of the primary database, and the failover results in a transition of a standby database to the primary role. The database administrator can configure Data Guard to ensure no data loss.

2. 什么是 Switchover?

A switchover is a role reversal between the primary database and one of its standby databases. A switchover ensures no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role. The transition occurs without having to re-create either database.

3. 配置 Connect Time Failover

Connect time failover will reroute incoming connections to the instance that has just become primary. This type of failover should work in cases where the old primary node is down, old primary network is down, old primary listener is down,
or old primary instance is now the standby.
When the old primary network is down, failover functionality is built into the basic layer of Oracle Net. We simply tcp timeout and fail to the next host in the list.

DBname_alias =
          (DESCRIPTION =
                   (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))
                          (ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))
                    )
           (CONNECT_DATA =
                          (INSTANCE_NAME=DBname_primary)
                          (SERVICE_NAME = DBname)
           )
           )

4. 配置 Transparent Application Failover

For application failover, all existing connections from the current primary must failover to the new primary. One of the biggest obstacles to overcome is the lag time from when the standby database becomes the primary database. Client connections should continue to retry the failover until the standby has been opened as the new production.

This could be done by having an alias similar to the following:

DBname_TAF=
           (DESCRIPTION=
                       (address_list=
                               (load_balance=off)
                               (failover=on)
                               (ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))
                               (ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))
                       )
            (CONNECT_DATA=
                       (instance_name=DBname_primary)
                       (SERVICE_NAME=DBname)
                       (FAILOVER_MODE=
                       (TYPE=session)
                       (METHOD=BASIC)
                       (RETRIES=200)
                       (DELAY = 3)))
           )

TAF will try to failover to the second node in the address_list. If it cannot connect, it will wait 3 seconds and retry again. It will retry a total of 200 times. This delay will provide the DBA with enough time to perform a switchover or activate the standby as the new production.

参考资料：
Switchover and Failover Operations
Oracle Database Failover and Switchover

-failover-abort-not-elected master mymaster问题

redis配置sentinel，master shutdown后主从切换失败，提示错误

-failover-abort-not-elected master mymaster x.x.x.x 6379

原因是sentinel.conf缺少配置，若redis.conf中添加

protected-mode yes
bind 0.0.0.0

则sentinel.conf中添加该属性。

11G、12C Data Guard Physical Standby Switchover 转换参考手册

Switchover 转换

Step 1: switchover 切换先前检查

（1）确保主备两端 log_archive_config 和 db_unique_name 参数都已经正确设置。

需要注意的是，主备两端 db_unique_name 不能一样。

（2）在备库检查 MRP 进程是否正在运行

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE ''MRP%'';

（3）在主库检查是否是 REAL TIME APPLY 模式

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

RECOVERY_MODE

-----------------------

MANAGED REAL TIME APPLY

如果不是，需要执行如下：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

（4）在备库检查 LOG_ARCHIVE_MAX_PROCESSES 参数是否为 4 或者更高

SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES

（5）检查备库 online redo log 文件已经被清除

SQL> SELECT DISTINCT L.GROUP# FROM V$LOG L, V$LOGFILE LF

WHERE L.GROUP# = LF.GROUP#

AND L.STATUS NOT IN (''UNUSED'', ''CLEARING'',''CLEARING_CURRENT'');

SQL>ALTER DATABASE CLEAR LOGFILE GROUP <ORL GROUP# from the query above>;

如果报如下错误：

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

ALTER DATABASE CLEAR LOGFILE GROUP 2

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

需要执行 recover managed standby database cancel;

更改之后再执行 recover managed standby database using current logfile disconnect;

确定备库 db_file_name_convert、log_file_name_convert 两个参数已经设置，若未设置，可能会报如下错误：

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

ALTER DATABASE CLEAR LOGFILE GROUP 3

ERROR at line 1:

ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 3 thread 1: ''/data/crfdb11/redo03.log''

（6）确保没有大的 GAP 产生

在主库查询当前的归档序列

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

验证备库已应用到。

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = ''YES'' AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = ''CURRENT'') GROUP BY THREAD#;

（7）检查备库临时表空间是否存在

SQL> SELECT TMP.NAME FILENAME, BYTES, TS.NAME TABLESPACE FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#;

（8）检查备库数据文件表空间是否有 offline 的

SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS=''OFFLINE'';

SQL> ALTER DATABASE DATAFILE ‘datafile-name’ ONLINE;

Step 2: 主库 JOB 的处理

SQL> SELECT * FROM DBA_JOBS_RUNNING;

SQL> SELECT OWNER, JOB_NAME, START_DATE, END_DATE, ENABLED

FROM DBA_SCHEDULER_JOBS

WHERE ENABLED=''TRUE''

AND OWNER <> ''SYS'';

SQL> SHOW PARAMETER job_queue_processes

SQL> ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH SID=’*’;

SQL> EXECUTE DBMS_SCHEDULER.DISABLE( <job_name> );

Step 3: 停止中间件

Step 4: 查看主库 switchover 状态

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

--------------------

TO STANDBY

附： A:switchover_status 出现 session active/not allowed

当出现 session active 的时候表示还有活动的 session, 则运行

Alter database commit to switchover to physical standby with session shutdown;

当出现 not allowed 时，在官方文档说转换会不成功，但是我测试的时候成功了。

B.ora- 01153: an incompatible media recovery is active

运行下面代码

Alter database recover managed standby database disconnect from session;

Step 5: 将主库切换成备库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

Step 6: 查看备库 switchover 状态

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

TO PRIMARY

附：若不是用此语句切换：

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown

补充：若出现：ORA-16139: media recovery required

是因为没有执行：

alter database recover managed standby database disconnect from session;

Step 7: 将备库切换成主库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Step 8: 打开新主库

SQL> ALTER DATABASE OPEN;

Step 9: 更正不匹配的临时文件

SQL> SELECT TMP.NAME FILENAME, BYTES, TS.NAME TABLESPACE

FROM V$TEMPFILE TMP, V$TABLESPACE TS

WHERE TMP.TS#=TS.TS#;

Step 10: 重启新备库

SQL> SHUTDOWN ABORT;

SQL> STARTUP MOUNT;

SQL> alter database open read only;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Step 11: 更改服务器 IP 地址

这个可以用 xmanager 连过去，在界面上改，也可以直接用命令来修改。

vi /etc/sysconfig/network-scripts/ifcfg-eth0

/etc/init.d/network restart

Step 12: JOB 的处理

SQL> ALTER SYSTEM SET job_queue_processes=<value> scope=both sid=’*’

SQL> EXECUTE DBMS_SCHEDULER.ENABLE(<for each job name>);

Step 13: 在主库查看 log_archive_dest 参数的状态是否有效

因为这里与监听和启动顺序有关，如果值为 error，则归档不能传送到备库。

SQL>select status,database_mode from v$archive_dest_status;

Status 状态为 valid 即可。如果有错误，一般都是启动顺序和监听问题。如果启动顺序没有问题，那么就把主库重新启动一下。

Step 14: 修改参数及 tnsnames.ora 文件

参考搭建文档在主库和备库修改以下参数

主库先修改此参数：

alter system set db_unique_name=''orcl'' scope=spfile;

重启后再修改下面参数：

alter system set log_archive_dest_1=''location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl'' sid=''*'';

alter system set log_archive_dest_2=''service=standby lgwr async COMPRESSION=ENABLE valid_for=(online_logfiles,primary_role) db_unique_name=standby'' scope=both;

alter system set fal_server=orcl scope=both sid=''*'';

alter system set log_archive_config=''dg_config=(orcl,standby)'' scope=both sid=''*'';

备库先修改此参数：

alter system set db_unique_name=''standby'' scope=spfile;

重启后再修改下面参数：

alter system set log_archive_dest_1=''location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=standby'' sid=''*'';

alter system set log_archive_dest_2='''' scope=both;

alter system set fal_server=orcl scope=both sid=''*'';

alter system set log_archive_config=''dg_config=(orcl,standby)'' scope=both sid=''*'';

修改参数完之后，修改 tnsnames.ora 文件，把 orcl 和 STANDBY 的 HOST 对换。

Step 15: 验证同步状态

在主库运行：

SQL> alter system switch logfile;

然后在主备库分别运行：

SQL>select sequence#,applied from v$archived_log;

确保 applied 都为 YES。

如果用：

SQL> select max(sequence#) from v$archived_log;

查看，如果主备库显示的一致，则只能表明归档已经传送到了备库，并不能表明该归档已经应用。而 applied 状态为 YES，就表明该归档已经应用到了备库。

如果等了很长时间 applied 都无法应用，可以尝试一下方法：

SQL>alter database recover managed standby database cancel;

SQL>alter database recover managed standby database disconnect from session;

这 2 个语句是取消 recover manager，在启动它。

Switchover 切换失败处理步骤

Step 1: 先关闭主库

SQL>shutdown immediate;

Step 2: 关闭备库

SQL>recover managed standby database cancel;

SQL>shutdown immediate;

Step 3: 在主库上的操作

SQL>startup mount;

SQL> alter database commit to switchover to primary;

SQL>alter database open;

SQL>alter system set log_archive_dest_2=''service=standby_db'' scope=both;

Step 4: 在备库上的操作

SQL>startup mount;

SQL>alter database recover managed standby database disconnect from session;

Step 5: 主库切换日志，在备库上的 alert 日志上查看是否成功。

12c DataGuard switchover to 'primary'

一、环境描述
Oracle 12c
RHEL 7

二、操作
注意：需要在物理standby节点执行切换，因为主节点要重启。

1.查看配置

DGMGRL> show configuration;

Configuration - dg_config

  Protection Mode: MaxAvailability
  Members:
  orcl   - Primary database
    orcldg - Physical standby database 

Fast-Start Failover: disABLED

Configuration Status:
SUCCESS   (status updated 30 seconds ago)

2.验证主库

DGMGRL> validate database orcl;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    orcl:  Off

3.验证物理备库

DGMGRL> validate database orcldg;

  Database Role:     Physical standby database
  Primary Database:  orcl

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    orcl:    Off
    orcldg:  On

  Standby Apply-Related information:
    Apply State:      Running
    Apply Lag:        13 hours 29 minutes 48 seconds (computed 1 second ago)
    Apply Delay:      0 minutes

4.进行切换

DGMGRL> switchover to orcldg;
Performing switchover Now, please wait...
New primary database "orcldg" is opening...
Oracle Clusterware is restarting database "orcl" ...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

        shut down instance "orcl" of database "orcl"
        start up instance "orcl" of database "orcl"

5.切换后确认

DGMGRL> show configuration;

Configuration - dg_config

  Protection Mode: MaxAvailability
  Members:
  orcldg - Primary database
    orcl   - Physical standby database 

Fast-Start Failover: disABLED

Configuration Status:
SUCCESS   (status updated 30 seconds ago)

DGMGRL>

4.1 ORACLE DATAGUARD SWITCHOVER 步骤

验证主库是否能执行角色转换到备库原主库执行

sql> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected

开始把物理主库改变为物理备库在原主库执行。
```
sql> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
或
sql> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown;
```
Note

如果有活动的session可以使用此选项，否则转换会遇到ORA-01093错误，也可以杀掉活动会话或等活动会话后进行转换.

关闭并重启主库（原主库执行）

sql> SHUTDOWN IMMEDIATE;
sql> STARTUP MOUNT;

验证备库是否能执行角色转换到主库（原备库执行）

sql> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
1 row selected

开始把物理备库转换成物理主库（原备库执行）
```
sql> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
```
如果报ORA-16139: media recovery required，可能是由于未应用日志引起，可先执行 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disCONNECT FROM SESSION;

打开备库，然后关闭重启.（原备库执行）

sql> ALTER DATABASE OPEN;
sql> SHUTDOWN IMMEDIATE;
sql> STARTUP;

验证是否转换成功（原备库执行）
```
sql> ALTER SYstem SWITCH LOGFILE;
```

应用归档日志（原主库上执行）

ALTER  DATABASE RECOVER MANAGED STANDBY DATABASE disCONNECT FROM SESSION; --非实时应用日志，此为应用归档日志

关于Oracle 中 Failover 和 Switchover 的区别和oracle switchover_status的介绍现已完结，谢谢您的耐心阅读，如果想了解更多关于-failover-abort-not-elected master mymaster问题、11G、12C Data Guard Physical Standby Switchover 转换参考手册、12c DataGuard switchover to 'primary'、4.1 ORACLE DATAGUARD SWITCHOVER 步骤的相关知识，请在本站寻找。

本文标签：