本文介绍了解决Oracle数据库中ORA-01555错误的方法，包括通过增加SCN、配置_minimum_giga_scn参数以及使用_corrupted_rollback_segments参数等步骤。
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: {=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错误分析
这里通过配置errorstack与10046两个事件来分析此故障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表空间等操作。
扫一扫
512
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
