oralce搭建DG恢复归档时遇到一个"大坑"

来源：互联网发布：淘宝蚂蚁花呗是什么编辑：程序博客网时间：2024/06/05 19:30

一.问题描述：
在对一系统搭建DG时，采用直接用rman在线上库进复制方式来搭建，线上库为oracle 11.2.0.4 RAC，数据量约2.6T，花了13小时完成复制。
在recover应用归档时，报错：
SQL> recover standby database;
ORA-00279: change 13310241470515 generated at needed for thread 1
ORA-00289: suggestion : /sssarch/archlog/sss1_65525_855755437.arc
ORA-00280: change 13310241470515 for thread 1 is in sequence #65525

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/sssarch/archlog/sss1_65525_855755437.arc
ORA-00328: archived log ends at change 13310240560123, need later change
13310241470515
ORA-00334: archived log: '/sssarch/archlog/sss1_65525_855755437.arc'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/sssdata1/dgsss/datafile/system01.dbf'

以上直接输入/sssarch/archlog/sss1_65525_855755437.arc或auto都报错

检查alert log:

ALTER DATABASE RECOVER LOGFILE '/sssarch/archlog/sss1_65525_855755437.arc'
Media Recovery Log /sssarch/archlog/sss1_65525_855755437.arc
Errors with log /sssarch/archlog/sss1_65525_855755437.arc
Errors in file /dgsss/oraplm/diag/rdbms/dgplmdr/dgplmdr/trace/dgplmdr_pr00_1284.trc:
ORA-00328: archived log ends at change 13310240560123, need later change 13310241470515
ORA-00334: archived log: '/sssarch/archlog/sss1_65525_855755437.arc'
ORA-328 signalled during: ALTER DATABASE RECOVER LOGFILE '/sssarch/archlog/sss1_65525_855755437.arc' ...
ALTER DATABASE RECOVER CANCEL
Signalling error 1152 for datafile 1!
Errors in file /dgsss/oraplm/diag/rdbms/dgplmdr/dgplmdr/trace/dgplmdr_pr00_1284.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/sssdata1/dgsss/datafile/system01.dbf'
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...

二.问题分析：

先检查各文件的scn及对应时间，确认建立DG所用的备份或copy是否正常

数据文件最大scn:
SQL> select max(substr(checkpoint_change#,1,14)) from v$datafile_header;

MAX(SUBSTR(CHECKPOINT_CHANGE#,1,14))
--------------------------------------------------------
13310585432418

生产库查询对应时间：
SQL> select to_char(scn_to_timestamp(13310585432418),'YYYY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SCN_TO_TIME
-------------------
2016-11-24 07:35:29

数据库scn(所有数据文件头最小的scn):
SQL> select max(substr(checkpoint_change#,1,14)) from v$database;

MAX(SUBSTR(CHECKPOINT_CHANGE#,1,14))
--------------------------------------------------------
13310228043312

数据文件scn:
SQL> select max(substr(checkpoint_change#,1,14)) from v$datafile;

MAX(SUBSTR(CHECKPOINT_CHANGE#,1,14))
--------------------------------------------------------
13310228043312

生产库查询对应时间：

SQL> select to_char(scn_to_timestamp(13310228043312),'YYYY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SCN_TO_TIME
-------------------
2016-11-23 17:44:35
控制文件scn:
SQL> select max(substr(controlfile_change#,1,14)) from v$database;

MAX(SUBSTR(CONTROLFILE_CHANGE#,1,14))
--------------------------------------------------------
13310241470515

恢复提示的scn 13310241470515其实就是控制文件的scn。

生产库查询对应时间：
SQL> select to_char(scn_to_timestamp(13310241470515),'YYYY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SCN_TO_TIME
-------------------
2016-11-23 18:03:53

从上面看大致是2016-11-23 18:03:53开始在线进行rman复制，2016-11-24 07:35:29左右完成复制。

SQL> set line 1000
SQL> set pagesize 1000
SQL> col "first_change#" for 99999999999999999
SQL> col "next_change#" for 99999999999999999
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select thread#,sequence#,first_change#,first_time,next_change#,next_time from v$archived_log where sequence#=65525;

SQL>select thread#,sequence#,first_change#,first_time,next_change#,next_time from v$archived_log where first_change# like '1331024%';

THREAD# SEQUENCE#      FIRST_CHANGE# FIRST_TIME                NEXT_CHANGE# NEXT_TIME
---------- ---------- ------------------ ------------------- ------------------ -------------------
         1      65526     13310240560124 2016-11-23 18:02:39     13310245677970 2016-11-23 18:09:52
         1      65526     13310240560124 2016-11-23 18:02:39     13310245677970 2016-11-23 18:09:52
         1      65527     13310245677970 2016-11-23 18:09:52     13310251487868 2016-11-23 18:19:07
         1      65527     13310245677970 2016-11-23 18:09:52     13310251487868 2016-11-23 18:19:07
         2      28800     13310240560999 2016-11-23 18:02:40     13310258536564 2016-11-23 18:28:47
         2      28800     13310240560999 2016-11-23 18:02:40     13310258536564 2016-11-23 18:28:47
上面日志的时序图为：13310240560124-->13310240560999-->13310245677970-->13310251487868-->13310258536564，对应恢复时所用log顺序为thread 1 65526-->thread 2 28800-->thread 1 65527 -->thread 2 288801
上面报错提示需要scn为13310241470515的日志，是落在thread 2 28800的log上。
再回到最先开始recover时的报错：
SQL> recover standby database;
ORA-00279: change 13310241470515 generated at needed for thread 1
ORA-00289: suggestion : /sssarch/archlog/sss1_65525_855755437.arc
ORA-00280: change 13310241470515 for thread 1 is in sequence #65525
oracle提示scn 13310241470515在sss1_65525_855755437.arc文件中，建议用这个文件来恢复，其实上面查到scn是在thread 2 28800的log上。
原来oracle也有很大坑啊。猜测估计单实例无法智能知道第二个节点的log信息，故出现上面有坑的提示。

三.问题解决
恢复时，先引导一下两节点第一个log的恢复，手工输入thread 1 65526和thread 2 28800 log，如下操作：
SQL> recover standby database;
ORA-00279: change 13310241470515 generated at 11/23/2016 18:02:39 needed for
thread 1
ORA-00289: suggestion : /sssarch/archlog/sss1_65526_855755437.arc
ORA-00280: change 13310241470515 for thread 1 is in sequence #65526

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/sssarch/archlog/sss1_65526_855755437.arc
ORA-00279: change 13310241470515 generated at needed for thread 2
ORA-00289: suggestion : /sssarch/archlog/sss2_28799_855755437.arc
ORA-00280: change 13310241470515 for thread 2 is in sequence #28799

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/sssarch/archlog/sss2_28800_855755437.arc
ORA-00279: change 13310245677970 generated at 11/23/2016 18:09:52 needed for
thread 1
ORA-00289: suggestion : /sssarch/archlog/sss1_65527_855755437.arc
ORA-00280: change 13310245677970 for thread 1 is in sequence #65527
ORA-00278: log file '/sssarch/archlog/sss1_65526_855755437.arc' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto

输入auto后，后续就能自动完成recover。

0 0