Oracle ADG switch步骤

1 预备工作

1.1 主备库补丁检查

判断：oracle用户数据库软件补丁版本一致

$opatch lspatches

1.2 主备库参数检查

判断：数据库参数设置是否正确

SQL> set linesize 250 pagesize 200

col name format a25

col value format a100

select NAME,VALUE from v$parameter where NAME='db_name';

select NAME,VALUE from v$parameter where NAME='db_unique_name';

select NAME,VALUE from v$parameter where NAME='log_archive_config';

select NAME,VALUE from v$parameter where NAME='control_files';

select NAME,VALUE from v$parameter where regexp_like(NAME,'^log_archive_dest_[0-9]') and VALUE is not null;

select NAME,VALUE from v$parameter where regexp_like(NAME,'^log_archive_dest_state_[0-9]') and VALUE = 'defer';

select NAME,VALUE from v$parameter where NAME='remote_login_passwordfile';

select NAME,VALUE from v$parameter where NAME='log_archive_format';

select NAME,VALUE from v$parameter where NAME='fal_server';

select NAME,VALUE from v$parameter where NAME='db_file_name_convert';

--db_create_file_dest或者log_file_name_convert必须设定

select NAME,VALUE from v$parameter where NAME='db_create_file_dest';

select NAME,VALUE from v$parameter where NAME='log_file_name_convert';

select NAME,VALUE from v$parameter where NAME='standby_file_management';

select NAME,VALUE from v$parameter where NAME='compatible';

--LOG_ARCHIVE_MAX_PROCESSES —》大于4或者5，但是不能过大

select NAME,VALUE from v$parameter where NAME='log_archive_max_processes';

 

1.3 备库状态

1.3.1 从库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

 

1.3.2 主库切换归档

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

 

1.3.3 从库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

 

 

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

 

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

 

1.4.1 从库MRP进程是否存在

返回结果为：MRP*

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

PROCESS

---------

MRP0

 

1.4.2 从库归档应用路径状态

状态为：MANAGED REAL TIME APPLY

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=10;

 

RECOVERY_MODE

-----------------------

MANAGED REAL TIME APPLY

 

1.5 LOG_ARCHIVE_MAX_PROCESSES个数

1.5.1 从库查询

注意：不低于4个不大于6个

 

SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES

 

1.6 确保LOG_FILE_NAME_CONVERT或者OMF开启

1.6.1 从库查询

判断：db_create_file_dest非空，并且oracle拥有目录权限

判断：LOG_FILE_NAME_CONVERT非空，并且oracle拥有目录权限

SQL> show parameter LOG_FILE_NAME_CONVERT

SQL> show parameter db_create_file_dest

判断：redo日志位置

SQL> select MEMBER from v$logfile;

 

1.7 确认GAPS大小

1.7.1 主库查询

获取：thread的sequence

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

   THREAD#  SEQUENCE#

---------- ----------

 1  1191

 

1.7.2 从库查询

注意：每个thread的sequence相差不超过2

SQL> 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#;

   THREAD# MAX(SEQUENCE#)

---------- --------------

 1      1189

 

1.8 主从对应的数据文件一致

注意：从库数据文件对应状态和数量与主库一致

1.8.1 主库查询

temp文件

SQL> set linesize 200 pagesize 200

col filename format a70

col status format a10

col tablespace format a30

SELECT TMP.NAME FILENAME, TMP.STATUS,TS.NAME TABLESPACE FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#;

非temp文件

SQL> SELECT TF.NAME FILENAME, TF.STATUS,TS.NAME TABLESPACE FROM V$DATAFILE TF, V$TABLESPACE TS WHERE TF.TS#=TS.TS#;

 

1.8.2 从库查询

temp文件

SQL> set linesize 200 pagesize 200

col filename format a70

col status format a10

col tablespace format a30

SELECT TMP.NAME FILENAME, TMP.STATUS,TS.NAME TABLESPACE FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#;

非temp文件

SQL> SELECT TF.NAME FILENAME, TF.STATUS,TS.NAME TABLESPACE FROM V$DATAFILE TF, V$TABLESPACE TS WHERE TF.TS#=TS.TS#;

2 执行切换

2.1 主库查询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不用停止

       JOB WHAT

---------- ----------------------------------------------------------------------------------------------------

      4001 wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);

      4002 wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS'),wwv_flow_platform.get_preference('SMTP_HOST_PORT'));

 

col task_name format a40

select  /*+rule */ t1.task_name,t1.status from dba_autotask_task t1;

 

--以下任务不用处理

TASK_NAME  STATUS

---------------------------------------- --------

gather_stats_prog  ENABLED

auto_space_advisor_prog  ENABLED

AUTO_SQL_TUNING_PROG  ENABLED

 

 

col owner format a20

col job_name format a40

select /*+rule */ t2.owner, t2.job_name, t2.state, t2.ENABLED, to_char(t2. NEXT_RUN_DATE,'yyyy-mm-dd hh24:mi:ss') from dba_scheduler_jobs t2 WHERE ENABLED='TRUE';

--11g以下几个不用停止

OWNER      JOB_NAME

-------------------- ----------------------------------------

SYS      PURGE_LOG

SYS      ORA$AUTOTASK_CLEAN

SYS      DRA_REEVALUATE_OPEN_FAILURES

SYS      BSLN_MAINTAIN_STATS_JOB

SYS      RSE$CLEAN_RECOVERABLE_SCRIPT

SYS      SM$CLEAN_AUTO_SPLIT_MERGE

ORACLE_OCM      MGMT_CONFIG_JOB

ORACLE_OCM      MGMT_STATS_CONFIG_JOB

EXFSYS      RLM$EVTCLEANUP

EXFSYS      RLM$SCHDNEGACTION

 

2.2 查询正在执行的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;

 

2.3 关闭调度和job

--阻塞job

SQL> show parameter job_queue_processes

 

NAME      TYPE  VALUE

-------------------------- ----------- ----------------------

job_queue_processes      integer  100

SQL> ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH SID='*';

--调度关闭

SQL> EXECUTE DBMS_SCHEDULER.DISABLE( <job_name> );

 

 

Note: Job candidates to be disabled among others: oracle text sync and optimizer, RMAN backups, application garbage collectors, application background agents.

 

2.4 主库开启trace

SQL> SHOW PARAMETER log_archive_trace

NAME      TYPE  VALUE

---------------------- ----------- ------------------------------

log_archive_trace      integer  0

 

 

Set Data Guard trace level to 8191 on both the primary and the target physical standby databases

SQL> ALTER SYSTEM SET log_archive_trace=8191;

 

 

Trace output will appear under the destination pointed to by the database parameter BACKGROUND_DUMP_DEST with “mrp” in the file name.

 

2.5 tail 主库日志

SQL> SHOW PARAMETER background_dump_dest

tail –f <background_dump_dest location>/alert*

2.6 从库开启trace

SQL> SHOW PARAMETER log_archive_trace

NAME      TYPE  VALUE

---------------------- ----------- ------------------------------

log_archive_trace      integer  0

 

 

Set Data Guard trace level to 8191 on both the primary and the target physical standby databases

SQL> ALTER SYSTEM SET log_archive_trace=8191;

 

 

Trace output will appear under the destination pointed to by the database parameter BACKGROUND_DUMP_DEST with “mrp” in the file name.

 

2.7 tail 从库日志

SQL> SHOW PARAMETER background_dump_dest

tail –f <background_dump_dest location>/alert*

 

2.8 所有的rac环境（主rac、从rac）

注意：仅仅保留一个节点操作

2.9 主库切换

注意：rac只保留一个节点，做操作！！

2.9.1 主库检查是否可以切换

状态为TO STANDBY or SESSIONS ACTIVE均可切换

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

-----------------

TO STANDBY

 

 

A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either mis-configured or is not functioning properly.

2.9.2 主库切换为备库 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

 

If an ORA-16139 error is encountered, as long as V$DATABASE.DATABASE_ROLE=’PHYSICAL STANDBY’, then you can proceed. A common case where this can occur is when there are a large number of data files. Once managed recovery is started on the new standby, the database will recover.

If the role was not changed then you need to cancel the switchover and review the alert logs and trace files further.

 

2.9.3 主库日志

Switchover: Primary controlfile converted to standby controlfile succesfully.

Tue Mar 15 16:12:15 2011

MRP0 started with pid=17, OS id=2717 

MRP0: Background Managed Standby Recovery process started (SFO)

Serial Media Recovery started

Managed Standby Recovery not using Real Time Apply

Online logfile pre-clearing operation disabled by switchover

Media Recovery Log /u01/app/flash_recovery_area/SFO/archivelog/2011_03_15/o1_mf_1_133_6qzl0yvd_.arc

Identified End-Of-Redo for thread 1 sequence 133

Resetting standby activation ID 0 (0x0)

Media Recovery End-Of-Redo indicator encountered

Media Recovery Applied until change 4314801

MRP0: Media Recovery Complete: End-Of-REDO (SFO)

MRP0: Background Media Recovery process shutdown (SFO)

Tue Mar 15 16:12:21 2011

Switchover: Complete - Database shutdown required (SFO)

Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN

 

2.9.4 从库日志

Tue Mar 15 16:12:15 2011

RFS[8]: Assigned to RFS process 2715

RFS[8]: Identified database type as 'physical standby': Client is Foreground pid 2568

Media Recovery Log /u01/app/flash_recovery_area/NYC/archivelog/2011_03_15/o1_mf_1_133_6qzl0yjp_.arc

Identified End-Of-Redo for thread 1 sequence 133

Resetting standby activation ID 2680651518 (0x9fc77efe)

Media Recovery End-Of-Redo indicator encountered

Media Recovery Continuing

Resetting standby activation ID 2680651518 (0x9fc77efe)

Media Recovery Waiting for thread 1 sequence 134

 

 

2.10 从库切换

2.10.1 从库检查是否可以切换

状态为：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

 

 

A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that redo apply is active and that redo transport is configured and working properly. Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.

2.10.2 从库切换为主库

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

 

 

2.10.3 从库日志

Tue Mar 15 16:16:44 2011

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

ALTER DATABASE SWITCHOVER TO PRIMARY (NYC)

Maximum wait for role transition is 15 minutes.

Switchover: Media recovery is still active

Role Change: Canceling MRP - no more redo to apply

Tue Mar 15 16:16:45 2011

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/diag/rdbms/nyc/NYC/trace/NYC_pr00_2467.trc:

ORA-16037: user requested cancel of managed recovery operation

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Waiting for MRP0 pid 2460 to terminate

Errors in file /u01/app/diag/rdbms/nyc/NYC/trace/NYC_pr00_2467.trc:

ORA-16037: user requested cancel of managed recovery operation

Tue Mar 15 16:16:45 2011

MRP0: Background Media Recovery process shutdown (NYC)

Role Change: Canceled MRP

 

2.10.4 打开数据库

 

SQL> ALTER DATABASE OPEN;

 

 

Note: There will be an increase in I/O activity while the new primary’s standby redo logs are cleared.

2.11 新从库处理

注意：对应的standby redo实例开启归档应用进程

2.11.1 启动新从库

SQL> STARTUP




SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;




set linesize 150 pagesize 30

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




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;

 

 

 

 

Note: If you were using a delay for your standby then you would restart the apply without real time apply:

 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Finally, if the database is a RAC, then start all secondary instances on the new standby.

2.11.2 调整log_archive_trace和job_queue_processes

SQL> ALTER SYSTEM SET log_archive_trace=<prior value>;

SQL> ALTER SYSTEM SET job_queue_processes=<value saved> scope=both sid='*'

2.11.3 启动剩余节点

SQL> startup

2.12 新主库调整

2.12.1 启动剩余节点

SQL> startup

2.12.2 开启job和调度

SQL> EXECUTE DBMS_SCHEDULER.ENABLE(<for each job name captured>);

2.12.3 调整log_archive_trace和job_queue_processes

SQL> ALTER SYSTEM SET log_archive_trace=<prior value>;

SQL> ALTER SYSTEM SET job_queue_processes=<value saved> scope=both sid='*'