Oracle - ADG主备切换

一、常用指令

# 查看数据库当前角色

# SQL> select DATABASE_ROLE from v$database;

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

# SQL> select switchover_status from v$database;

# 查看主备库连接状态

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

二、 主库切换至备库

2.1 切换指令

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

# SQL> alter system switch logfile;

# SQL> alter system archive log current;

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

# SQL> shutdown abort

# SQL> startup mount

# SQL> select switchover_status from v$database;

# SQL> alter database open;

# SQL> select switchover_status from v$database;

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

2.2 在新备库/原主库（orcl）启动日志应用

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

查询进程MRP是否启动

# SQL> select process,pid,status from v$managed_standby;

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

2.3 验证备库状态

# select database_role,switchover_status from v$database;

2.4 切换备库未主库（完成角色替换）

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

打开数据库

# SQL> alter database open;

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

# SQL> select database_role,switchover_status from v$database;

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

# alter system switch logfile;
# select sequence# from v$log;

在新备库/原主库上执行同样的操作，看最大sequence#是否相同

三、备切换回主

在主库（主切备）

# 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;

在备库（备切主）

# SQL> alter database commit to switchover to primary;
# SQL> alter database open;