Oracle ADG switchover

主库：192.168.194.99
备库：192.168.194.88
密码：Oracle_123456



正切

============================================
主库执行
===========================================
批量杀会话

ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|xargs kill -9

查看本地会话是否还存在

ps -ef|grep LOCAL=NO|grep -v grep


su - orabi

sqlplus / as sysdba

查看是否还有事务

select s.sid,s.serial#,USED_UBLK from v$transaction t,v$session s where t.addr=s.taddr;
>>>>>>如果存在大事务，则停实例会非常漫长，需要沟通是否要杀掉大事务



生成全局检查点，并切换归档日志
alter system checkpoint;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;



set line2222
col status for a10
col type for a10
col error for a20
col gap_status for a20
col synchronization_status for a30
col recovery_mode for a60
select inst_id,status,DEST_ID,TYPE,database_mode,ERROR,GAP_STATUS,SYNCHRONIZED,SYNCHRONIZATION_STATUS,RECOVERY_MODE from GV$ARCHIVE_DEST_STATUS where STatus <> 'INACTIVE' and type = 'PHYSICAL';

INST_ID STATUS DEST_ID TYPE DATABASE_MODE ERROR GAP_STATUS SYN SYNCHRONIZATION_STATUS RECOVERY_MODE
---------- ---------- ---------- ---------- --------------- -------------------- -------------------- --- ------------------------------
1 VALID 2 PHYSICAL OPEN_READ-ONLY NO GAP NO CHECK CONFIGURATION MANAGED REAL TIME APPLY

set line2222
col name for a10
col database_role for a20
col switchover_status for a20
col PROTECTION_MODE for a25
select inst_id,name,database_role,protection_mode,switchover_status from gv$database;


INST_ID NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS

---------- ---------- -------------------- ------------------------- --------------------
1 TESDBPRIMARY MAXIMUM PERFORMANCE TO STANDBY



show parameter log_archive_config;
show parameter log_archive_dest_1;
show parameter log_archive_dest_2;

ALTER DATABASE SWITCHOVER TO TESDBVERIFY;
ALTER DATABASE SWITCHOVER TO ANADB;




============================================
-- 启动原主库
============================================
sqlplus / as sysdba


startup

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

select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TESDBREAD ONLY WITH APPLY PHYSICAL STANDBY



-- 启动新主库到open

alter database open


============================================
切换结束

============================================






回切

主库：192.168.194.99
备库：192.168.194.88
密码：Oracle_123456



==============================================
新备库库
============================================

set linesize 150;
set pagesize 20;
column name format a13;
column value format a20;
column unit format a30;
column TIME_COMPUTED format a30;
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

NAME VALUE UNIT TIME_COMPUTED
------------- -------------------- ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 02/21/2025 01:22:11
apply lag +00 00:00:00 day(2) to second(0) interval 02/21/2025 01:22:11


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

PROCESS THREAD# GROUP# SEQUENCE# STATUS
--------- ---------- ---------------------------------------- ---------- ------------
ARCH 1 4 575886 CLOSING
ARCH 0 N/A 0 CONNECTED
ARCH 0 N/A 0 CONNECTED
ARCH 1 5 575887 CLOSING
RFS 0 N/A 0 IDLE
RFS 1 11 575888 IDLE
RFS 0 N/A 0 IDLE
MRP0 1 N/A 575888 APPLYING_LOG


set line222
col database_role for a20
col switchover_status for a20
col PROTECTION_MODE for a25
select inst_id,name,database_role,protection_mode,switchover_status from gv$database;


INST_ID NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
---------- ------------- -------------------- ------------------------- --------------------
1 TESDBPHYSICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED


select * from v$archive_gap;


-- 新主库

alter system checkpoint;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

ALTER DATABASE SWITCHOVER TO ANADB_DG VERIFY;
ALTER DATABASE SWITCHOVER TO ANADB_DG;

alter database open;


-- 新备库库

startup

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

set linesize 150;
set pagesize 20;
column name format a13;
column value format a20;
column unit format a30;
column TIME_COMPUTED format a30;
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

NAME VALUE UNIT TIME_COMPUTED
------------- -------------------- ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 02/21/2025 00:13:49
apply lag +00 00:00:00 day(2) to second(0) interval 02/21/2025 00:13:49

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

PROCESS THREAD# GROUP# SEQUENCE# STATUS
--------- ---------- ---------------------------------------- ---------- ----------
ARCH 1 5 575883 CLOSING
ARCH 0 N/A 0 CONNECTED
ARCH 1 4 575882 CLOSING
ARCH 1 5 575881 CLOSING
RFS 0 N/A 0 IDLE
RFS 1 1 575884 IDLE
RFS 0 N/A 0 IDLE
RFS 0 N/A 0 IDLE
MRP0 1 N/A 575884 APPLYING_LOG