Oracle DG switchover

Oracle DG switchover
（12C以上）

1. 检查DB状态

show parameter db_unique_name;
show parameter log_Archive_dest;
show parameter log_file_name_convert;

select ts#,name,ts#,status from v$tempfile;

SELECT thread#, instance, status FROM v$thread;

select name,role,instance,thread#,sequence#,action from gv$dataguard_process;

select dbid,name,db_unique_name,log_mode,open_mode,database_role,activation#,switchover#,protection_mode,protection_level,checkpoint_change#,archive_change#
from v$database;

select status,DEST_ID,TYPE,ERROR,GAP_STATUS,SYNCHRONIZED,SYNCHRONIZATION_STATUS,RECOVERY_MODE from V$ARCHIVE_DEST_STATUS where STatus <> ‘INACTIVE’;

如有异常 可以打开跟踪日志用于故障诊断 主从都开（默认不用）
–alter system set log_archive_trace=8191 sid=’*’;
–show parameter background_dump_dest;

Standby:
SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS;

检查当前会话 ，没应用连接

select i.instance_name,i.host_name,s.inst_id,sid,s.serial#,s.username,s.status,action,schemaname,osuser,process,blocking_session
from gv$sessions,gv$instance i
where s.inst_id=i.inst_id
and s.username is not null
and s.status =‘ACTIVE’;

insert into t1(id) values(3);
select * from t1;

DG broker 核对状态

2. 切换检查
   alter database switchover to dbname_2289 verify;

3. 无异常 执行切换
   alter database switchover to dbname_2289;

BDUMP下查看日志
alert_dbname_2288.log, alert_dbname_2289.log

NAME TYPE VALUE

---

background_dump_dest string D:\ORACLE\12201\RDBMS\TRACE

日志中看切换过程:

Starting switchover
……
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
……
ALTER DATABASE SWITCHOVER TO PRIMARY

4. Open the new primary database
   dbname_2289
   SQL>alter database open;

5. 重启 new standby
   dbname_2288
   SQL>shut abort
   SQL>startup
   SQL>alter database recover managed standby database disconnect;

6. 检查DG状态

select DB_UNIQUE_NAME,DATABASE_ROLE from v$database;

如果之前打开过跟踪日志，现在可以关闭
alter system set log_archive_trace=0;

primary:
alter system switch logfile;

select dest_id,error,status from vKaTeX parse error: Expected 'EOF', got '#' at position 57: …ct max(sequence#̲),thread# from …log_history group by thread#;
select name,role,instance,thread#,sequence#,action from gv$dataguard_process;

standby :
select max(sequence#),thread# from vKaTeX parse error: Expected 'EOF', got '#' at position 29: …group by thread#̲; select name,r…dataguard_process;
SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS;

（老版本查看方式，12.2以后不用）
–select thread#,sequence#,process,status from gv$managed_standby; // deprecated in Oracle 12.2