一篇文章带你搞懂 Oracle ADG 主备切换

1、常用命令

查看数据库当前角色

select DATABASE_ROLE from v$database;

查看数据库现在是否支持转换为Primary角色或Standby角色

select switchover_status from v$database;

开启备库日志同步（mount 下执行）

alter database recover managed standby database disconnect from session;

关闭日志同步

alter database recover managed standby database cancel;

查看主备库连接状态

select process, pid, status, thread#,sequence#,blocks from v$managed_standby;

查看日志列表

archive log list

手动切换日志

alter system switch logfile;

2 、主库切换至备库

2.1 切换指令

select name,open_mode,switchover_status from v$database;
 
alter system switch logfile;
 
alter system archive log current;
 
alter database commit to switchover to physical standby with session shutdown;
 
shutdown abort
 
startup mount
 
select switchover_status from v$database;
 
alter database open;
 
select switchover_status from v$database;

此时查询到主库（orcl19）的角色和切换状态都已转变

2.2 在新备库（原主库）启动实时日志应用

查询进程MRP是否启动

select process,pid,status from v$managed_standby;

输出说明：MRP进程已经启动。处于等待状态即等待将主库传输过来的日志应用到数据库

2.3 验证备库状态

select database_role,switchover_status from v$database;

2.4 切换备库为主库(完成角色切换)

alter database commit to switchover to primary with session shutdown wait;

# 打开数据库
alter database open;

2.5 查看备库是否完成角色切换

select database_role,switchover_status from v$database;

2.6 在新主库（原备库）上执行日志切换以启动日志传输

alter system switch logfile;

# 在新备库（原主库）上执行同样的操作，看最大sequence#是否相同
select sequence# from v$log;

3、备库切换为主库

3.1 主库执行（主库切为备库）

# 主备切换的命令
alter database commit to switchover to physical standby with session shutdown wait;

# 执行日志同步
shutdown immediate;
startup mount;
alter database recover managed standby database disconnect from session;

3.2 备库执行（备库切为主库）

alter database commit to switchover to primary;
alter database open;

主备切换完成！

参考文档：https://blog.youkuaiyun.com/m0_53148885/article/details/127566659