当前位置: 首页 > BACKUP & RESTORE, ORA > 正文

WIN平台ORACLE启动ORA-00604 ORA-01555 ORA-01173报错处理

         11.2.0.3环境数据库启动时报ORA-00704 ORA-00604 ORA-01555错误,通过手动增加scn后,启动数据库再报ORA-01173错误。数据库来至于网络,版本为11.2.0.1,我本机的版本是11.2.0.3

1 参数配置

*._allow_resetlogs_corruption=true

#*._corrupted_rollback_segments=’_SYSSMU1_4293228286$’,’_SYSSMU2_2039428205$’,’_SYSSMU3_62847029$’,’_SYSSMU4_2852015879$’,’_SYSSMU5_865959144$’,’_SYSSMU6_4222004433$’,’_SYSSMU7_3167120690$’,’_SYSSMU8_3640122478$’,’_SYSSMU9_4125436319$’,’_SYSSMU10_1599800294$’,’_SYSSMU11_4274605233$’,’_SYSSMU12_3262263327$’,’_SYSSMU13_3701178243$’,’_SYSSMU14_2274826003$’,’_SYSSMU15_3989461359$’,’_SYSSMU16_2896103364$’,’_SYSSMU17_1001039816$’,’_SYSSMU18_826213832$’,’_SYSSMU19_2524552265$’,’_SYSSMU20_3030429363$’,’_SYSSMU21_3208651888$’,’_SYSSMU22_136948633$’,’_SYSSMU23_3630425231$’,’_SYSSMU24_3375812654$’,’_SYSSMU25_3954145094$’,’_SYSSMU26_3823963024$’,’_SYSSMU27_2697353812$’,’_SYSSMU28_1119046978$’,’_SYSSMU29_2253969065$’,’_SYSSMU30_722648610$’

#*._minimum_giga_scn=30

#*._smu_debug_mode=268435456

*.audit_file_dest=’d:\app\luoping\admin\orcl\adump’

*.audit_trail=’db’

*.compatible=’11.2.0.0.0′

*.control_files=’d:\app\luoping\oradata\orcl\control01.ctl’

*.db_block_size=8192

*.db_domain=”

*.db_name=’orcl’

*.db_recovery_file_dest=’d:\app\luoping\fast_recovery_area’

*.db_recovery_file_dest_size=4259315712

*.diagnostic_dest=’d:\app\luoping’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’

#*.event=’10513 trace name context forever, level 2′

*.memory_target=1715470336

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile=’EXCLUSIVE’

#*.undo_management=’manual’

*.undo_management=’AUTO’

*.undo_tablespace=’UNDOTBS2′

WIN环境,配置好参数后,还需要创建服务,才可以正常的启动数据库。

2 触发ORA-00704 ORA-00604 ORA-01555报错

d:\wendang\SkyDrive\rs2\sql>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun Oct 12 20:47:47 2014

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

使用指定的参数文件启动数据库

www.htz.pw > startup nomount force pfile=’d:\123.ora’;

ORACLE instance started.

 

Total System Global Area 1720328192 bytes

Fixed Size                  2255904 bytes

Variable Size            1006633952 bytes

Database Buffers          704643072 bytes

Redo Buffers                6795264 bytes

 

这里需要重建控制文件,有2个原因,1,原控制文件有问题;2,数据文件路径发生变化

www.htz.pw > CREATE CONTROLFILE REUSE DATABASE “ORCL” RESETLOGS  NOARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 ‘d:\app\luoping\oradata\orcl\redo01.log’  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 ‘d:\app\luoping\oradata\orcl\redo02.log’  SIZE 50M BLOCKSIZE 512,

 10    GROUP 3 ‘d:\app\luoping\oradata\orcl\redo03.log’  SIZE 50M BLOCKSIZE 512

 11  DATAFILE

 12   ‘d:\app\luoping\oradata\orcl\system01.dbf’,

 13  ‘d:\app\luoping\oradata\orcl\sysaux01.dbf’,

 14  ‘d:\app\luoping\oradata\orcl\users01.dbf’,

 15  ‘d:\app\luoping\oradata\orcl\example01.dbf’

 16  CHARACTER SET US7ASCII

 17  ;

 

Control file created.

 

www.htz.pw > recover database using backup controlfile until cancel;

ORA-00279: change 25472922843 generated at 02/11/2014 18:51:36 needed for

thread 1

ORA-00289: suggestion :

D:\APP\LUOPING\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_10_12\O1_MF_1_1_%U_.ARC

ORA-00280: change 25472922843 for thread 1 is in sequence #1

 

 

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

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: ‘D:\APP\LUOPING\ORADATA\ORCL\SYSTEM01.DBF’

 

 

ORA-01112: media recovery not started

 

 

www.htz.pw > alter database open resetlogs upgrade;

alter database open resetlogs upgrade

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number 23 with name “_SYSSMU23_3630425231$” too small

Process ID: 5720

Session ID: 191 Serial number: 1

这里看到已经触发了ORA-01555的报错。

3 ORA-00704 ORA-00604 ORA-01555错误分析

这里通过配置errorstack10046两个事件来分析此故障

www.htz.pw > oradebug setmypid

Statement processed.

www.htz.pw > oradebug event 10046 trace name context forever,level 12;

Statement processed.

www.htz.pw > oradebug event 1555 trace name errorstack level 12;

Statement processed.

 

www.htz.pw > oradebug tracefile_name;

D:\APP\LUOPING\diag\rdbms\orcl\orcl\trace\orcl_ora_2120.trc

www.htz.pw > alter database open resetlogs upgrade;

alter database open resetlogs upgrade

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number 23 with name “_SYSSMU23_3630425231$” too small

Process ID: 2120

Session ID: 191 Serial number: 1

 

 

d:\wendang\SkyDrive\rs2\sql>grep -E “^Block he|^0x0” D:\APP\LUOPING\diag\rdbms\orcl\orcl\trace\orcl_ora_2120.trc

Block header dump:  0x004000f1

0x01   0x0017.00e.00007a22  0x020005f7.175c.19  –U-    1  fsc 0x0000.ee4dfbc1

Block header dump:  0x00400152

0x01   0x0005.020.00000009  0x00c00190.0003.01  CBU-    0  scn 0x0000.000082c3

0x02   0x001b.00b.0000001d  0x020002b3.0017.39  –U-    5  fsc 0x005f.e75a5db7

Block header dump:  0x0040020b

0x01   0x0000.025.00000002  0x00400225.0004.6a  –U-   15  fsc 0x0000.00000261

Block header dump:  0x00400151

0x01   0x001a.014.00006fcd  0x02000e2c.158c.02  C—    0  scn 0x0005.ee3b0882

Block header dump:  0x00400152

0x01   0x0005.020.00000009  0x00c00190.0003.01  CBU-    0  scn 0x0000.000082c3

0x02   0x001b.00b.0000001d  0x020002b3.0017.39  –U-    5  fsc 0x005f.e75a5db7

Block header dump:  0x004000f1

0x01   0x0017.00e.00007a22  0x020005f7.175c.19  –U-    1  fsc 0x0000.ee4dfbc1

Block header dump:  0x00400141

0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

0x02   0x0000.057.0000001f  0x00400225.004a.3d  –U-    1  fsc 0x0000.e7582918

Block header dump:  0x004000e1

0x01   0x0000.013.00000026  0x0040008e.004d.15  –U-    1  fsc 0x0000.ededda9c

Block header dump:  0x004000b9

0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

0x02   0x0005.021.0000057b  0x00c00b83.019f.14  –U-    1  fsc 0x0000.001bda09

Block header dump:  0x004000b6

0x01   0x000a.01a.0000023b  0x00c01066.0063.0a  C—    0  scn 0x0000.000ed9b4

0x02   0x0000.003.0000001c  0x00400216.0048.09  –U-    1  fsc 0x0000.e75824f9

 

通过上面的信息我们基本上可以确认通过增加SCN就可以解决此ORA-01555

下面是一些其它的信息分析,如果通过上面信息不能确认通过增加SCN可以解决此问题,我们还可以看下面这些信息。

===================================================

CURRENT SESSION’S INSTANTIATION STATE

————————————-

current session=0x000007FF65871AB8

KGI STATE DUMP for user sess=000007FF65877A08 current sess=000007FF65871AB8

————————————-

INSTANTIATION OBJECT: object=00000000055AB190

type=”KOKA open cursor”[2] lock=0000000000000000 pn=0000000000000000 handle=0000000000000000 lkhandle=0000000000000000 body=0000000000000000 level=0

flags=[40] executions=0

kgiobses=000007FF65877A08 kgiobuse=000007FF65877A08

REST OF INSTANTIATION OBJECT:

address=00000000055AB260 size=16

0055AB260 00000000 00000005 0000000A 00000000  […………….]

————————————-

INSTANTIATION OBJECT: object=00000000055AB098

type=”KOKA pseudo cursor”[3] lock=0000000000000000 pn=0000000000000000 handle=0000000000000000 lkhandle=0000000000000000 body=0000000000000000 level=0

flags=[40] executions=0

kgiobses=000007FF65877A08 kgiobuse=000007FF65877A08

REST OF INSTANTIATION OBJECT:

address=00000000055AB168 size=16

0055AB160                   00000000 00000005          [……..]

0055AB170 0000000A 00000000                    [……..]       

KGI STATE DUMP DONE for user session=000007FF65877A08

 

—– Session Cursor Dump —–

Current cursor: 5, pgadep=1

 

 

—————————————-

—————————————-

Cursor#5(0x00000000055A1D68) state=FETCH curiob=0x00000000055B11F0

 curflg=2007 fl2=200000 par=0x00000000055A1BB8 ses=0x000007FF65871AB8

—– Dump Cursor sql_id=4krwuz0ctqxdt xsc=0x00000000055B11F0 cur=0x00000000055A1D68 —–

 

LibraryHandle:  Address=000007FF6447C788 Hash=199b75b9 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD

  ObjectName:  Name=select ctime, mtime, stime from obj$ where obj# = :1

    FullHashValue=fa0bd3f60d6ee4f2495f9af8199b75b9 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=429618617 OwnerIdn=0

  Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1

  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0

  Concurrency:  DependencyMutex=000007FF6447C838(0, 1, 0, 0) Mutex=000007FF6447C8B8(191, 25, 0, 6)

  Flags=RON/PIN/TIM/PN0/DBN/[10012841]

  WaitersLists: 

    Lock=000007FF6447C818[000007FF6447C818,000007FF6447C818]

    Pin=000007FF6447C7F8[000007FF6447C7F8,000007FF6447C7F8]

    LoadLock=000007FF6447C870[000007FF6447C870,000007FF6447C870]

  Timestamp:  Current=10-12-2014 21:00:09

  HandleReference:  Address=000007FF6447C958 Handle=0000000000000000 Flags=[00]

  LibraryObject:  Address=000007FF5EECE0B0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]

    ChildTable:  size=’16’

      Child:  id=’0′ Table=000007FF5EECEF60 Reference=000007FF5EECE998 Handle=000007FF6447C328

    Children: 

      Child:  childNum=’0′

        LibraryHandle:  Address=000007FF6447C328 Hash=0 LockMode=N PinMode=S LoadLockMode=0 Status=VALD

          Name:  Namespace=SQL AREA(00) Type=CURSOR(00)

          Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=1 TotalLockCount=2 TotalPinCount=6

          Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0

          Concurrency:  DependencyMutex=000007FF6447C3D8(0, 0, 0, 0) Mutex=000007FF6447C8B8(191, 25, 0, 6)

          Flags=RON/PIN/PN0/EXP/CHD/[10012111]

          WaitersLists: 

            Lock=000007FF6447C3B8[000007FF6447C3B8,000007FF6447C3B8]

            Pin=000007FF6447C398[000007FF6447C398,000007FF6447C398]

            LoadLock=000007FF6447C410[000007FF6447C410,000007FF6447C410]

          LibraryObject:  Address=000007FF5EECD0B0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]

            DataBlocks: 

              Block:  #=’0′ name=KGLH0^199b75b9 pins=0 Change=NONE  

                Heap=000007FF6447C270 Pointer=000007FF5EECD150 Extent=000007FF5EECD030 Flags=I/-/P/A/-/-

                FreedLocation=0 Alloc=2.859375 Size=3.937500 LoadTime=8209051

              Block:  #=’6′ name=SQLA^199b75b9 pins=0 Change=NONE  

                Heap=000007FF5EECE768 Pointer=000007FF5DBEAB40 Extent=000007FF5DBE9EE8 Flags=I/-/P/A/-/E

                FreedLocation=0 Alloc=9.656250 Size=11.859375 LoadTime=0

          NamespaceDump: 

            Child Cursor:  Heap0=000007FF5EECD150 Heap6=000007FF5DBEAB40 Heap0 Load Time=10-12-2014 21:00:09 Heap6 Load Time=10-12-2014 21:00:09

  NamespaceDump: 

    Parent Cursor:  sql_id=4krwuz0ctqxdt parent=000007FF5EECE150 maxchild=1 plk=y ppn=n    kkscs=000007FF5EECE628 nxt=0000000000000000 flg=18 cld=0 hd=000007FF6447C328 par=000007FF5EECE150

   Mutex 000007FF5EECE628(0, 0) idn 0

   ct=0 hsh=0 unp=0000000000000000 unn=0 hvl=5eeceff8 nhv=0 ses=0000000000000000

   hep=000007FF5EECE6C0 flg=80 ld=1 ob=000007FF5EECD0B0 ptr=000007FF5DBEAB40 fex=000007FF5DBE9EE8

cursor instantiation=0x00000000055B11F0 used=1413118809 exec_id=16777216 exec=1

 child#0(0x000007FF6447C328) pcs=0x000007FF5EECE628

  clk=0x000007FF64943C48 ci=0x000007FF5EECD150 pn=0x000007FF64943AB0 ctx=0x000007FF5DBEAB40

 kgsccflg=0 llk[0x00000000055B11F8,0x00000000055B11F8] idx=0

 xscflg=c01504f6 fl2=45040001 fl3=40222108 fl4=100

—– Bind Byte Code (IN) —–

  Opcode = 1   Unoptimized

  Offsi = 48, Offsi = 0

—– Bind Info (kkscoacd) —–

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=0c8df3a0  bln=22  avl=02  flg=05

  value=20

 Frames pfr 0x000000000C8DF790 siz=4400 efr 0x000000000C8DFD10 siz=4384

 Cursor frame dump

  enxt: 4.0×00000010  enxt: 3.0×00000128  enxt: 2.0×00000020  enxt: 1.0x00000fd8

  pnxt: 1.0×00000010

 kxscphp=0x00000000055B09A8 siz=1992 inu=1032 nps=904

 kxscdfhp=0x00000000055B1068 siz=984 inu=88 nps=0

 kxscbhp=0x000000000C8D0068 siz=984 inu=168 nps=48

 kxscwhp=0x00000000055B06F0 siz=4056 inu=368 nps=0

Starting SQL statement dump

SQL Information

user_id=0 user_name=SYS module=sqlplus.exe action=

sql_id=4krwuz0ctqxdt plan_hash_value=1218588913 problem_type=0

—– Current SQL Statement for this session (sql_id=4krwuz0ctqxdt) —–

select ctime, mtime, stime from obj$ where obj# = :1

sql_text_length=53

sql=select ctime, mtime, stime from obj$ where obj# = :1

Compilation Environment Dump

 

 

 

其实SQL直接在ERRORSTACK就可以看到了

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=12, mask=0x0)

—– Error Stack Dump —–

ORA-01555: snapshot too old: rollback segment number 23 with name “???” too small

—– Current SQL Statement for this session (sql_id=4krwuz0ctqxdt) —–

select ctime, mtime, stime from obj$ where obj# = :1

 

 

 

PARSING IN CURSOR #89854448 len=52 dep=1 uid=0 oct=3 lid=0 tim=8209051861 hv=429618617 ad=’7ff6447c788′ sqlid=’4krwuz0ctqxdt’

select ctime, mtime, stime from obj$ where obj# = :1

END OF STMT

PARSE #89854448:c=0,e=211,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=8209051860

BINDS #89854448:

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=0c8df3a0  bln=22  avl=02  flg=05

  value=20

EXEC #89854448:c=0,e=421,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1218588913,tim=8209052348

WAIT #89854448: nam=’db file sequential read’ ela= 258 file#=1 block#=337 blocks=1 obj#=36 tim=8209052644

WAIT #89854448: nam=’db file sequential read’ ela= 258 file#=1 block#=338 blocks=1 obj#=36 tim=8209052994

WAIT #89854448: nam=’db file sequential read’ ela= 255 file#=1 block#=241 blocks=1 obj#=18 tim=8209053320

4 配置_minimum_giga_scn参数,增加SCN

我这里的环境是WIN 7 64位,11.2.0.3还可以通过_minimum_giga_scn此参数来增加SCN。此参数在部分平台已经失效

增加*._minimum_giga_scn=25参数

www.htz.pw > alter database open resetlogs upgrade;

alter database open resetlogs upgrade

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01173: data dictionary indicates missing data file from system tablespace

Process ID: 4700

Session ID: 191 Serial number: 1

5 ORA-01173错误的处理

看到了ORA-01173错误的出现,由于在创建控制文件中没有包括UNDO表空间导致的。这里常用的解决方案有2个:1,将UNDO的数据文件增加回控制文件,2,配置_corrupted_rollback_segments参数。下面是通过配置_corrupted_rollback_segments参数来处理,但是此方案在特殊情况下不可用,曾经遇到见2次。配置_corrupted_rollback_segments参数需要得到UNDO段的名字,数据库非OPEN状态下获取UNDO段的名字见BLOG

*._allow_resetlogs_corruption=true

*._corrupted_rollback_segments=’_SYSSMU1_4293228286$’,’_SYSSMU2_2039428205$’,’_SYSSMU3_62847029$’,’_SYSSMU4_2852015879$’,’_SYSSMU5_865959144$’,’_SYSSMU6_4222004433$’,’_SYSSMU7_3167120690$’,’_SYSSMU8_3640122478$’,’_SYSSMU9_4125436319$’,’_SYSSMU10_1599800294$’,’_SYSSMU11_4274605233$’,’_SYSSMU12_3262263327$’,’_SYSSMU13_3701178243$’,’_SYSSMU14_2274826003$’,’_SYSSMU15_3989461359$’,’_SYSSMU16_2896103364$’,’_SYSSMU17_1001039816$’,’_SYSSMU18_826213832$’,’_SYSSMU19_2524552265$’,’_SYSSMU20_3030429363$’,’_SYSSMU21_3208651888$’,’_SYSSMU22_136948633$’,’_SYSSMU23_3630425231$’,’_SYSSMU24_3375812654$’,’_SYSSMU25_3954145094$’,’_SYSSMU26_3823963024$’,’_SYSSMU27_2697353812$’,’_SYSSMU28_1119046978$’,’_SYSSMU29_2253969065$’,’_SYSSMU30_722648610$’

*._minimum_giga_scn=25

#*._smu_debug_mode=268435456

*.audit_file_dest=’d:\app\luoping\admin\orcl\adump’

*.audit_trail=’db’

*.compatible=’11.2.0.0.0′

*.control_files=’d:\app\luoping\oradata\orcl\control01.ctl’

*.db_block_size=8192

*.db_domain=”

*.db_name=’orcl’

*.db_recovery_file_dest=’d:\app\luoping\fast_recovery_area’

*.db_recovery_file_dest_size=4259315712

*.diagnostic_dest=’d:\app\luoping’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’

#*.event=’10513 trace name context forever, level 2′

*.memory_target=1715470336

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile=’EXCLUSIVE’

*.undo_management=’manual’

#*.undo_management=’AUTO’

*.undo_tablespace=’UNDOTBS2′

6 数据库正常打开

www.htz.pw > alter database open resetlogs upgrade;

 

Database altered.

这里看到数据库已经正常的以upgrade模式打开了,下面需要增加一些temp表空间等操作。

此数据库来至于网络

本文固定链接: http://www.htz.pw/2014/10/29/win%e5%b9%b3%e5%8f%b0oracle%e5%90%af%e5%8a%a8ora-00604-ora-01555-ora-01173%e6%8a%a5%e9%94%99%e5%a4%84%e7%90%86.html | 认真就输

该日志由 huangtingzhong 于2014年10月29日发表在 BACKUP & RESTORE, ORA 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: WIN平台ORACLE启动ORA-00604 ORA-01555 ORA-01173报错处理 | 认真就输
关键字: ,