问题描述
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错误分析
这里通过配置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表空间等操作。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。