首页 » ORACLE » Troubleshoot RMAN session fail ORA-01861 during restore database

Troubleshoot RMAN session fail ORA-01861 during restore database

朋友一套数据库版本10.2.0.5 on HPUX 11, 源库是noarchivelog read-only database open mode时备份,在目标库(其它主机)还原时遇到 ora-1861, 错误很简单就是遇到了不标准的时间日期字符串,如2月30日,4月31日等。

RMAN> run
{
allocate channel t1 type 'SBT_TAPE'
parms 'ENV=(NSR_SERVER=crmbeif,NSR_DATA_VOLUME_POOL=yjboss,NSR_CLIENT=yjbossdb)';
...
restore database;
release channel t1;
release channel t2;
release channel t3;

}

ORA-01861: literal does not match format string

 

首先定位递归SQL, 使用了系统级errorstack trace
启用

SQL> alter system set events='1861 trace name errorstack level 10';

— 运行上面的rman脚本,直至报出ora-1861错误

关闭

SQL> alter system set events='1861 trace name errorstack OFF';

查看trace file

*** ACTION NAME:(0000001 FINISHED70) 2018-08-03 17:26:31.746
*** MODULE NAME:(rman@rzcrmdb1 (TNS V1-V3)) 2018-08-03 17:26:31.746
*** SERVICE NAME:() 2018-08-03 17:26:31.746
*** SESSION ID:(10981.9) 2018-08-03 17:26:31.746
*** 2018-08-03 17:26:31.746
ksedmp: internal or fatal error
ORA-01861: literal does not match format string
Current SQL statement for this session:
select fhscn ,to_date(fhtim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian') ,fhcrs ,fhrls 
,to_date(fhrlc,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian') ,fhafs ,fhrfs ,fhrft ,hxerr 
,fhfsz ,fhsta into :b1,:b2:b3,:b4,:b5,:b6:b7,:b8:b9,:b10:b11,:b12:b13,:b14,:b15,:b16 
 from x$kcvfhall where hxfil=:b17
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst()+64          call     ksedst1()            000000000 ? 000000001 ?
ksedmp()+2176        call     ksedst()             000000000 ?
                                                   C000000000000D20 ?
                                                   4000000004043C80 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
$cold_ksddoa()+1424  call     ksedmp()             00000000A ?
                                                   9FFFFFFFFFFF3720 ?
                                                   60000000000BA270 ?
                                                   9FFFFFFFFFFF3CF0 ?
                                                   C000000000000F24 ?
                                                   4000000003609750 ?
ksdpcg()+576         call     $cold_ksddoa()       9FFFFFFFFFFF3D28 ?
                                                   9FFFFFFFBF3A6730 ?
                                                   9FFFFFFFFFFF3D00 ?
                                                   60000000000BA270 ?
ksdpec()+336         call     ksdpcg()             9FFFFFFFBF3B0160 ?
                                                   9FFFFFFFBF3B0170 ?
                                                   000000000 ?
ksfpec()+320         call     ksdpec()             9FFFFFFFFFFF48C0 ?
kgesev()+320         call     ksfpec()             C000000000000491 ?
                                                   000000745 ?
                                                   40000000030E4BB0 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
ksesec0()+160        call     kgesev()             60000000000318D0 ?
                                                   6000000000379320 ?
                                                   000000745 ?
                                                   6000000000032D00 ?
                                                   6000000000032CF0 ?
dteerr()+128         call     ksesec0()            000000745 ? 000000000 ?
                                                   000000000 ?
ldxerr()+144         call     dteerr()             9FFFFFFFFFFF4930 ?
                                                   000000745 ?
$cold_ldxstd()+64    call     ldxerr()             9FFFFFFFFFFF4930 ?
                                                   000000745 ?
                                                   60000000000BA270 ?
                                                   C000000000000593 ?
                                                   4000000002038020 ?
                                                   9FFFFFFFFFFF4940 ?
                                                   9FFFFFFFFFFF4A18 ?
                                                   000000000 ?
evadsi()+1152        call     $cold_ldxstd()       9FFFFFFFFFFF4930 ?
                                                   9FFFFFFFBF303390 ?
                                                   9FFFFFFFBF30282C ?
                                                   9FFFFFFFBF362AA0 ?
                                                   000000013 ?
                                                   C0000002CDDBC6A8 ?
evaopn2()+7104       call     evadsi()             C0000002CDDBCD88 ?
                                                   60000000000BA270 ?
                                                   C0000000000011AB ?
                                                   4000000002F5EAB0 ?
                                                   00002C263 ? 000000008 ?
                                                   9FFFFFFFBF302AB8 ?
                                                   C0000002CDDBC6A8 ?
evaopn2()+9472       call     evaopn2()            9FFFFFFFBEBA0088 ?
                                                   9FFFFFFFFFFF4C80 ?
                                                   60000000000BA270 ?
                                                   9FFFFFFFFFFF5240 ?
                                                   C0000000000011AB ?
                                                   4000000002F5F3F0 ?
                                                   00002C2A1 ? 000000000 ?
opifcr()+1456        call     evaopn2()            9FFFFFFFBE912CF0 ?
                                                   9FFFFFFFFFFF5270 ?
                                                   60000000000BA270 ?
                                                   9FFFFFFFFFFF5830 ?
                                                   C0000000000011AB ?
                                                   4000000002F38E90 ?
                                                   00002C261 ? 000000000 ?
qerfxFetch()+1328    call     opifcr()             9FFFFFFFFFFF6490 ?
                                                   000000002 ?
                                                   60000000000BA270 ?
                                                   C000000000001736 ?
                                                   4000000002F78D80 ?
                                                   000028427 ?
                                                   9FFFFFFFBEBA0334 ?
                                                   000000000 ?
opifch2()+6176       call     qerfxFetch()         C0000002CDDBD520 ?
                                                   4000000001CBE7D0 ?
                                                   9FFFFFFFFFFF6490 ?
                                                   000000002 ?
                                                   60000000000BA270 ?
                                                   C0000000000021CB ?
                                                   4000000002F59740 ?
                                                   000028223 ?
opiefn0()+672        call     opifch2()            9FFFFFFFFFFF7450 ?
                                                   400000000316BE00 ?
                                                   00002021F ?
                                                   9FFFFFFFFFFF6300 ?
                                                   60000000000BA270 ?
                                                   C000000000000F26 ?
                                                   6000000000031A50 ?
                                                   040202D89 ?
opiefn()+304         call     opiefn0()            C00000000000038F ?
                                                   9FFFFFFFBF3B1C4A ?
                                                   9FFFFFFFFFFF74A8 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
opiodr()+2144        call     opiefn()             00000004E ? 000000004 ?
                                                   9FFFFFFFFFFF9C30 ?
                                                   C0000000000018B7 ?
                                                   4000000002F441C0 ?
                                                   9FFFFFFFFFFF7558 ?
ttcpip()+1680        call     opiodr()             00000004E ? 000000004 ?
                                                   4000000001C03410 ?
                                                   0000046C0 ?
                                                   9FFFFFFFFFFF7560 ?
opitsk()+2368        call     ttcpip()             600000000003DF40 ?
                                                   000000001 ?
                                                   9FFFFFFFFFFF9C30 ?
                                                   000000000 ?
                                                   9FFFFFFFFFFF9DA0 ?
                                                   9FFFFFFFFFFF9B94 ?
                                                   4000000001CEBE50 ?
                                                   000000000 ?
opiino()+1664        call     opitsk()             000000000 ? 000000000 ?
                                                   60000000000BA270 ?
                                                   4000000002944200 ?
                                                   000028089 ?
                                                   4000000001C03428 ?
opiodr()+2144        call     opiino()             00000003C ?
                                                   9FFFFFFFFFFFC5F0 ?
                                                   9FFFFFFFFFFFED90 ?
                                                   9FFFFFFFFFFFBAB0 ?
                                                   60000000000BA270 ?
                                                   C0000000000018B7 ?
opidrv()+1248        call     opiodr()             00000003C ? 000000004 ?
                                                   4000000001C03140 ?
                                                   0000046C0 ?
                                                   9FFFFFFFFFFFC600 ?
                                                   60000000000BA270 ?
sou2o()+240          call     opidrv()             00000003C ?
                                                   60000000000C6CA8 ?
                                                   9FFFFFFFFFFFED90 ?
opimai_real()+496    call     sou2o()              9FFFFFFFFFFFEDB0 ?
                                                   00000003C ? 000000004 ?
                                                   9FFFFFFFFFFFED90 ?
main()+240           call     opimai_real()        000000000 ?
                                                   9FFFFFFFFFFFEDE0 ?
main_opd_entry()+80  call     main()               000000002 ?
                                                   9FFFFFFFFFFFF290 ?
                                                   60000000000BA270 ?
                                                   C000000000000004 ?
******************** Session Cursor Dump **********************

============
Plan Table
============
-----------------------------------------------------+-----------------------------------+
| Id  | Operation                | Name              | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT         |                   |       |       |       |           |
| 1   |  FIXED TABLE FIXED INDEX | X$KCVFHALL (ind:1)|       |       |       |           |
-----------------------------------------------------+-----------------------------------+
 
Content of other_xml column
===========================
  db_version     : 10.2.0.5
  parse_schema   : SYS
  plan_hash      : 1567485612
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('optimizer_dynamic_sampling' 1)
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      RBO_OUTLINE
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "X$KCVFHALL"@"SEL$1")
    END_OUTLINE_DATA
  */


----------------------------------------
Cursor#11(9fffffffbf3b1c48) state=FETCH curiob=9fffffffbeba0088
 curflg=66 fl2=0 par=0000000000000000 ses=c0000002f3d3c320
 sqltxt(c0000002fde2f128)=
select fhscn ,to_date(fhtim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian') ,fhcrs ,
fhrls ,to_date(fhrlc,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian') ,fhafs ,fhrfs ,
fhrft ,hxerr ,fhfsz ,fhsta into :b1,:b2:b3,:b4,:b5,:b6:b7,:b8:b9,:b10:b11,:b12:b13,:b14,:b15,:b16  
from x$kcvfhall where hxfil=:b17
  hash=e780e1d2a3462ef7d179ac375eb3ad69
  parent=c0000002cef3fb90 maxchild=01 plk=c0000002cfa94c38 ppn=n
cursor instantiation=9fffffffbeba0088 used=1533288391
 child#0(c0000002fde2eef8) pcs=c0000002cef3f160
  clk=c0000002cfa94cf0 ci=c0000002cef3ee88 pn=c0000002fdbe0920 ctx=c0000002cddbed70
 kgsccflg=0 llk[9fffffffbeba0090,9fffffffbeba0090] idx=0
 xscflg=c0110c76 fl2=5440001 fl3=42222008 fl4=100
 Bind bytecodes
  Opcode = 2   Bind Twotask Scalar Sql In (may be out) Copy 
  oacdef = c0000002cef3f0b0   Offsi = 48, Offsi = 0
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=9fffffffbf10d5a8  bln=22  avl=02  flg=05
  value=47
 Frames pfr 9fffffffbf11fef8 siz=3344 efr 9fffffffbf11ff60 siz=3320

Note:
确认是在查询x$kcvfhall 时触发,SQL文本内有2个TO_DATE转换时间的字段,所以问题就出在fhtim或fhrlc. 如果找到那条记录就可以看到那个错误值 。

KCVFHALL ==》[K]enel [C]ache Reco[V]ery Component [F]ile [H]eaders [ALL]

关于这个X$KCVFHALL这是个数据文件头的视图,  没有找到太多信息,应该是recovery 时在获取数据文件头部的信息,如scn, 可以使用下面的脚本确认问题记录

set serveroutput on 

Declare 
  LIT_MISMATCH EXCEPTION;
  PRAGMA EXCEPTION_INIT(LIT_MISMATCH, -1861);
  CURSOR BP IS select hxfil,FHFNO,HXFNM, fhtim ,  fhrlc  , FHTNM  from x$kcvfhall;
  RECID NUMBER;
  D1 DATE;
  D2 DATE;
BEGIN
   <>
   FOR BPREC IN BP LOOP
     BEGIN
       select to_date(fhtim,'MM/DD/RR HH24:MI:SS') , to_date(fhrlc,'MM/DD/RR HH24:MI:SS') INTO D1,D2  from x$kcvfhall where hxfil=BPREC.hxfil;
      EXCEPTION
       WHEN LIT_MISMATCH THEN
       DBMS_OUTPUT.PUT_LINE('hxfil = ' || BPREC.hxfil || ' Error');
       DBMS_OUTPUT.PUT_LINE('HXFNM = ' || BPREC.HXFNM);
     END;
   END LOOP BP_REC;
END;
/

Note:

hxfil 是datafile_id, xhfnam是datafile name, 同样也可以去v$datafile里验证, 如果数据文件头上有错误的检查点时间也就意味着数据文件头上有错误数据对于这个平台上的checkpoint,首先要确认数据的错误程度,可以dump 数据文件头或dd.

Headers dump all data files: file_hdrs event
level 1 – represents the control file dump all the data file header.
level 2 – dump all data files represent a common header -generic header.
level 3 – level2 and contains header information in the datafile
level 10 – represent a complete file header dump all data files.

这里使用dump 数据文件头的命令:

SQL> alter session set events 
   'immediate trace name FILE_HDRS level 10';

要确认在源库和目标库之间没有经过其它平台的数据转换, 确认是否源库也存在这些错误记录,如果源库没有,目标库各别存在,可以尝试重新备份部分文件。
如果源库也存在问题,建议收集所有的参数文件和alert中的报错给ORACLE SR确认。
如果源库没有问题,目标库使用的RAW裸设置,建议用0x00清空所有ORACLE数据文件的前64K,如下:

$ dd if=/dev/zero of=/dev/....filename...   bs=8k count=8 conv=notrunc

建议对于利旧或者其它新加入数据库的设备,使用前用zero(0x00)去清空一下数据文件头,总是一个比较好的方法!

打赏

, ,

对不起,这篇文章暂时关闭评论。