数据库switch over

mahan

切第一阶段 - 检查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


结束远程连接进程（暂不执行）
[oracle@ex01db02 ~]$ ps -ef|grep 'LOCAL=NO'|grep ora8 |grep -v grep|awk '{print $2}'|xargs -i kill -9 {}

③关闭数据库
登陆节点二
shutdown immediate



④主库节点切换角色至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');

select to_char(current_scn) from v$database;


SQL> ALTER DATABASE OPEN;
开启新主库节点2
startup

第Z3阶段 - 开启原主库RAC2节点
登陆172.16.110.201
su - oracle

cd conn

. foc

SQL>satrtup


第Z4阶段 -修改域名
域名指向变更
修改focdb.sda.cn与focdb.shandongair.com.cn 指向改为一体机ip


第五阶段 - 应用恢复

第六阶段 - ADG重新应用

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');

第七阶段 - 开启job作业

--一体机主库任意一节点

启动已被关闭的jobs

SQL> exec DBMS_JOB.BROKEN(job=>169,broken=>FALSE);
SQL> commit;
SQL> exec DBMS_JOB.BROKEN(job=>62,broken=>FALSE);
SQL> commit;
SQL> exec DBMS_JOB.BROKEN(job=>65,broken=>FALSE);
SQL> commit;
SQL> exec DBMS_JOB.BROKEN(job=>63,broken=>FALSE);
SQL> commit;
SQL> exec DBMS_JOB.BROKEN(job=>189,broken=>FALSE);
SQL> commit;
SQL> exec DBMS_JOB.BROKEN(job=>2847,broken=>FALSE);
SQL> commit;
SQL> exec DBMS_JOB.BROKEN(job=>64,broken=>FALSE);
SQL> commit;
SQL> exec DBMS_JOB.BROKEN(job=>43,broken=>FALSE);
commit;

第八阶段

大厦灾备机启动归档应用进程


登陆172.16.77.237
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


检查ADG整体运行状态

检查ADG应用情况
从库sequence号
SQL> SELECT max(SEQUENCE#), to_char(max(FIRST_TIME),'yyyy-mm-dd hh24:mi:ss') FIRST_TIME FROM V$ARCHIVED_LOG group by THREAD#;

MAX(SEQUENCE#) FIRST_TIME
-------------- -------------------
1189 2018-11-13 14:26:09

主库切换归档
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

从库sequence增加1
SQL> SELECT max(SEQUENCE#), to_char(max(FIRST_TIME),'yyyy-mm-dd hh24:mi:ss') FROM V$ARCHIVED_LOG group by THREAD#;

MAX(SEQUENCE#) TO_CHAR(MAX(FIRST_T
-------------- -------------------
1190 2018-11-13 14:26:13


从库接收到归档状态为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#;

SEQUENCE# THREAD# APPLIED
---------- ---------- ---------
1190 1 IN-MEMORY

从库MRP进程和本地归档路径状态

从库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');