Adg主备节点的切换

Oracle 19c单实例的ADG主备切换

前提准备：（主备库打开监听）

lsnrctl start

主备切换

开始时，主库启动到open 状态，备库启动到mount状态

1.主库

sqlplus / as sysdba

SQL>startup   

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

NAME OPEN_MODE SWITCHOVER_STATUS
--------- -------------------- --------------------
PROD1 READ WRITE TO STANDBY

SQL> alter system switch logfile;

System altered.

SQL> alter system archive log current;

System altered.

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 849530880 bytes
Fixed Size 1348244 bytes
Variable Size 511708524 bytes
Database Buffers 331350016 bytes
Redo Buffers 5124096 bytes
Database mounted.
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
RECOVERY NEEDED

SQL> alter database open;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

2、备库

在主库启动时，备库在mount状态
sqlplus / as sysdba

SQL>startup mount

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.
这里可能会出现错误：
ORA-16139: media recovery required。
下面有解决方法
解决完后，可以不要再次执行了。直接执行下面的命令。

SQL> alter database open;

Database altered.

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

NAME OPEN_MODE SWITCHOVER_STATUS
--------- -------------------- --------------------
PROD1 READ WRITE TO STANDBY

:black_circle:出现了ORA-16139: media recovery required错误处理

在当前备库执行过程展示如下：

SQL>  alter database commit to switchover to primary with session shutdown;                       
alter database commit to switchover to primary with session shutdown
*
ERROR at line 1:
ORA-16139: media recovery required

怀疑可能是由于有日志未应用造成的，执行如下语句查询：

SQL> select APPLIED,SEQUENCE# from v$archived_log;

APPLIED    SEQUENCE#
--------- ----------
YES                8
YES                9
YES               12
YES               13
YES               15
YES               16
YES               17
YES               10
YES               14
YES               11
YES               19

APPLIED    SEQUENCE#
--------- ----------
YES               18
NO                20
NO                21
NO                22
NO                23

16 rows selected.

SQL> ALTER  DATABASE RECOVER MANAGED STANDBY  DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> alter database commit to switchover to primary;

Database altered.

3、新备库（原主库）启用实时日志应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

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

NAME OPEN_MODE SWITCHOVER_STATUS
--------- -------------------- --------------------
RMANPRI READ ONLY NOT ALLOWED

4、新主库切换日志

SQL> alter system switch logfile;

System altered.

5、分别查看当前主备库切换后当前日志序列号

主库：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/archive
Oldest online log sequence 52
Next log sequence to archive 54
Current log sequence 54

备库：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/archive
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0

6、查看当前主备库状态

主库：

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

NAME OPEN_MODE SWITCHOVER_STATUS
--------- -------------------- --------------------
RMANPRI READ WRITE TO STANDBY

备库：

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

NAME OPEN_MODE SWITCHOVER_STATUS
--------- -------------------- --------------------
RMANPRI   READ ONLY WITH APPLY NOT ALLOWED

🚩还有可能出现的问题：

 select name,open_mode,switchover_status from v$database;

NAME      OPEN_MODE            SWITCHOVER_STATUS

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

ITPUXDB   READ WRITE           FAILED DESTINATION

解决方法：

一般都是监听的问题，可能是没开或者是两节点tnsping不通.
可以运行下面命令：
tnsping itpuxdb
tnsping itpuxdg
没成功，去看看主备库的两个监听文件。

本文由个人兴趣爱好所写，如有内容问题，及时联系作者更改。