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
没成功,去看看主备库的两个监听文件。
本文由个人兴趣爱好所写,如有内容问题,及时联系作者更改。