Oracle adg的switchover切换

原创杨露瑶云和恩墨 2022-11-25

1531

一、Oracle 11g

1. 数据库状态查看

# pri

SQL> select * from v$dataguard_stats;

复制

2. 切换角色

原主库切换为新备库

alter database commit to switchover to physical standby with session shutdown;
# 切换命令执行结束后实例关闭


startup
复制

原备库切换为新主库

alter database commit to switchover to primary;
# 切换命令执行结束后实例进入mount状态

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED


alter database open;
复制

新备库应用日志

alter database recover managed standby database using current logfile disconnect from session;
复制

二、Oracle 19c

1. 切换前的检查

确认备库已经做好准备

alter database switchover to <target standby db_unique_name> verify;

# 如果MRP进程非正常运行或备库未与主库同步会报错ORA-16470
SQL> alter database switchover to oraclestd verify;
alter database switchover to oraclestd verify
*
ERROR at line 1:
ORA-16470: Redo Apply is not running on switchover target

复制

检查ORL(online redo log)的状态

# 如果是脏的那么会报出如下信息：

SQL> alter database switchover to oraclestd verify;
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

复制

确认 log_file_name_convert 参数在目标备库被正确设置

show parameter log_file_name_convert;

# 如果主备库的文件路径只有db_unique_name发生改变
# 备库执行
alter system set LOG_FILE_NAME_CONVERT='oracle','oraclestd' scope=spfile;

# 主库执行
alter database switchover to oraclestd verify;
复制

2. 切换角色

在主库和备库同时开启 trace，用于发生问题时候的诊断

SQL>alter system set log_archive_trace=8191 sid='*';
复制

切换备库为新主库

# 主库执行
SQL> alter database switchover to oraclestd;

Database altered.

SQL> exit

# 备库执行
SQL> exit

# 新主库执行
[oracle@ora19std ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 25 05:57:03 2022
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> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

SQL> alter pluggable database all 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@ora19 trace]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 25 05:58:15 2022
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1879046152 bytes
Fixed Size                  9136136 bytes
Variable Size             452984832 bytes
Database Buffers         1409286144 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY            PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>  alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs;

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


复制

三、问题报错

同步确认报错ORA-16466

SQL> alter database switchover to oraclestd verify;
alter database switchover to oraclestd verify
*
ERROR at line 1:
ORA-16466: invalid switchover target
复制

查看主备库switchover_status

select NAME,DATABASE_ROLE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

# 主库
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
FAILED DESTINATION

# 备库
SQL> select SWITCHOVER_STATUS from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED


复制

查看告警
查看告警得知主备库监听未打开

***********************************************************************

Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19std)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oraclestd)(CID=(PROGRAM=oracle)(HOST=ora19)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 19.0.0.0.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.3.0.0.0
  Time: 25-NOV-2022 04:41:07
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12541

TNS-12541: TNS:no listener
    ns secondary err code: 12560
    nt main err code: 511

TNS-00511: No listener
    nt secondary err code: 111
    nt OS err code: 0

复制

解决问题

$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-NOV-2022 04:43:22

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

Starting /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ora19std/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19std)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19std)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                25-NOV-2022 04:43:23
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora19std/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19std)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "oraclestd" has 1 instance(s).
  Instance "oraclestd", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


# 主库
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
RESOLVABLE GAP

复制

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle adg的switchover切换

一、Oracle 11g

1. 数据库状态查看

2. 切换角色

二、Oracle 19c

1. 切换前的检查

2. 切换角色

三、问题报错

同步确认报错ORA-16466

评论

相关阅读