11g备库切换最好经验

1、备库中mrp是否在运行
SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
如果没有则启动
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using CURRENT LOGFILE DISCONNECT;

2、检查备库redo状态
SELECT DISTINCT L.GROUP# FROM V$LOG L, V$LOGFILE LF
 WHERE L.GROUP# = LF.GROUP#
 AND L.STATUS NOT IN ('UNUSED', 'CLEARING','CLEARING_CURRENT');
 如果有结果，则停止mrp后
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 清理redo
 ALTER DATABASE CLEAR LOGFILE GROUP <上面查出来的在线日志组号>;

3、确认主备库同步gap
主库：SELECT THREAD#, SEQUENCE# FROM V$THREAD;
备库：SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
     WHERE APPLIED = 'YES'
     AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#
     FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT')
     GROUP BY THREAD#;
如果超过3个，需要手工处理

4、验证临时文件个数相同
SELECT TMP.NAME FILENAME, BYTES, TS.NAME TABLESPACE
     FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#;

5、没有offline文件
SELECT NAME FROM V$DATAFILE WHERE STATUS='OFFLINE';

6、没有后台作业在运行
SELECT * FROM DBA_JOBS_RUNNING;
SELECT OWNER, JOB_NAME, START_DATE, END_DATE, ENABLED FROM
     DBA_SCHEDULER_JOBS WHERE ENABLED='TRUE' AND OWNER <> 'SYS';

7、创建还原点
备库：
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using CURRENT LOGFILE DISCONNECT;
 
主库：
 CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;
 
 -----------------
 开始切换
 
 1、主切到备
主库状态
  SELECT SWITCHOVER_STATUS FROM V$DATABASE;
  应该是TO STANDBY 或 SESSIONS ACTIVE

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

2、备切到主
备库状态
  SELECT SWITCHOVER_STATUS FROM V$DATABASE;
  应该是TO PRIMARY 或 SESSIONS ACTIVE
  
ALTER DATABASE Commit to Switchover to PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE OPEN;

3、原主库切换到备用状态
SHU IMMEDIATE
STARTUP MOUNT
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using CURRENT LOGFILE DISCONNECT;

4、清理还原点
主备库上 DROP RESTORE POINT SWITCHOVER_START_GRP;