标题: 数据库switch over [打印本页] 作者: mahan 时间: 2026-3-1 17:43 标题: 数据库switch over 切第一阶段 - 检查ADG运行状态
见EXCEL
(77.66)
SQL>
ORACLE一体机 执行
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM ARCHIVE LOG CURRENT;
备库执行
set linesize 150 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');
第二阶段 - 停止重要的应用（最好）
第三阶段 - 生产机切换角色
①停止dba_jobs
--主库查询job作业（提前执行）
SQL> set linesize 200 pagesize 300
col WHAT format a100
col PRIV_USER format a20
select t.JOB,t.WHAT,t.PRIV_USER,to_char(t.NEXT_DATE,'yyyy-mm-dd hh24:mi:ss') from dba_jobs t;
-以下job不用停止
4001和4002
查询正在执行的job作业
set linesize 500 pagesize 200
select JOB_NAME,OWNER,ELAPSED_TIME from DBA_SCHEDULER_RUNNING_JOBS;
set linesize 500 pagesize 200
select JOB,THIS_DATE start_time from DBA_JOBS_RUNNING;
关闭调度和job
--阻塞job（不执行）
SQL> show parameter job_queue_processes
NAME TYPE VALUE
-------------------------- ----------- ----------------------
job_queue_processes integer 1000
SQL> ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH SID='*';
--调度关闭
如有正在运行的job通过一下命令关闭：
exec DBMS_JOB.BROKEN(job=>169,broken=>TRUE);
commit;
exec DBMS_JOB.BROKEN(job=>62,broken=>TRUE);
commit;
exec DBMS_JOB.BROKEN(job=>65,broken=>TRUE);
commit;
exec DBMS_JOB.BROKEN(job=>63,broken=>TRUE);
commit;
exec DBMS_JOB.BROKEN(job=>189,broken=>TRUE);
commit;
exec DBMS_JOB.BROKEN(job=>2847,broken=>TRUE);
commit;
exec DBMS_JOB.BROKEN(job=>64,broken=>TRUE);
commit;
exec DBMS_JOB.BROKEN(job=>43,broken=>TRUE);
commit;
Note: Job candidates to be disabled among others: oracle text sync and optimizer, RMAN backups, application garbage collectors, application background agents.
检查生产机是否具备切换条件
②主库检查是否可以切换（一体机）
状态为TO STANDBY or SESSIONS ACTIVE均可切换
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
④主库节点切换角色至standby：
登陆172.16.77.66：
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
第四阶段 - 灾备机切换角色（service情况）
①检查RAC1节点是否具备切换条件
从库检查是否可以切换
状态为：TO PRIMARY or SESSIONS ACTIVE
SQL> set linesize 200
COLUMN NAME FORMAT A24
COLUMN VALUE FORMAT A16
COLUMN DATUM_TIME FORMAT A24
SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO PRIMARY
查询后关闭节点2
shutdown immediate
②修改RAC1节点角色
从库切换为主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
打开数据库
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
select open_mode from v$database;
alter database open;
alter system switch logfile;
alter system set log_archive_dest_state_2=ENABLE;
alter system archive log current;
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');
exadata启动归档应用进程
启动归档日志应用进程：
登陆172.16.77.66
SQL> startup
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
SQL> ALTER SYSTEM SET job_queue_processes=1000 scope=both sid='*';
SQL> show parameter job_queue_processes
--查看应用延迟
SQL> set linesize 150 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');
从库接收到归档状态为IN-MEMRY
最新的归档状态：IN-MEMRY和YES
SQL> select t.SEQUENCE#,t.THREAD#,t.APPLIED from V$ARCHIVED_LOG t,(SELECT max(SEQUENCE#) SEQUENCE#,THREAD# FROM V$ARCHIVED_LOG group by THREAD#) t1
where t1.SEQUENCE#=t.SEQUENCE# and t1.THREAD#=t.THREAD#;
从库MRP进程是否存在
返回结果为：MRP*
SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
PROCESS
---------
MRP0
从库归档应用路径状态
状态为：MANAGED REAL TIME APPLY
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=10;
RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
--查看应用延迟
SQL> set linesize 150 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');