ITPub博客

首页 > 数据库 > Oracle > ORA-01173:data dictionary indicates missing data file from system tablespace恢复

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

原创 Oracle 作者:yewushang 时间:2014-02-15 11:12:07 0 删除 编辑
问题:一朋友的库开库报错:ORA-01173: data dictionary indicates missing data file from system tablespace  
我的是11.2.0.1 他的是11.2.0.2

点击(此处)折叠或打开

  1. 版本:
  2. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
  3. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  4. Windows NT Version V6.1
  5. CPU : 8 - type 8664, 8 Physical Cores
  6. 开库报错:
  7. SQL> alter database open resetlogs;
  8. alter database open resetlogs
  9. *
  10. ERROR at line 1:
  11. ORA-01092: ORACLE instance terminated. Disconnection forced
  12. ORA-00704: bootstrap process failure
  13. ORA-00704: bootstrap process failure
  14. ORA-00604: error occurred at recursive SQL level 1
  15. ORA-01173: data dictionary indicates missing data file from system tablespace
  16. Process ID: 8572
  17. Session ID: 96 Serial number: 3



1.此问题是建立控制文件的时候少写数据文件了。

点击(此处)折叠或打开

  1. 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 \'F:\\app\\Administrator\\oradata\\orcl\\redo01.log\' SIZE 50M BLOCKSIZE 512,
  9. GROUP 2 \'F:\\app\\Administrator\\oradata\\orcl\\redo02.log\' SIZE 50M BLOCKSIZE 512,
  10. GROUP 3 \'F:\\app\\Administrator\\oradata\\orcl\\redo03.log\' SIZE 50M BLOCKSIZE 512
  11. DATAFILE
  12. \'F:\\app\\Administrator\\oradata\\orcl\\system01.dbf\',
  13. \'F:\\app\\Administrator\\oradata\\orcl\\sysaux01.dbf\',
  14. \'F:\\app\\Administrator\\oradata\\orcl\\users01.dbf\',
  15. \'F:\\app\\Administrator\\oradata\\orcl\\example01.dbf\',
  16. \'F:\\app\\Administrator\\oradata\\orcl\\CONET.DBF\'

  17. CHARACTER SET US7ASCII
  18. ;
少了undo的信息。

2.做10046事件跟踪。

点击(此处)折叠或打开

  1. alter session set events \'10046 trace name context forever, level 12\'
3.看trace。


点击(此处)折叠或打开

  1. create table bootstrap$和一系列的相关过程无问题 庆幸此过程无问题
  2. PARSING IN CURSOR #463855704 len=142 dep=2 uid=0 oct=3 lid=0 tim=426776417298 hv=361892850 ad=\'7ff648c0ac0\' sqlid=\'7bd391hat42zk\'
  3. select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
  4. END OF STMT
  5. PARSE #463855704:c=0,e=512,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,plh=0,tim=426776417297
  6. BINDS #463855704:
  7. Bind#0
  8. oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  9. oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  10. kxsbbbfp=1ba5dc10 bln=22 avl=02 flg=05
  11. value=23
  12. EXEC #463855704:c=0,e=1014,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,plh=906473769,tim=426776418454
  13. WAIT #463855704: nam=\'db file sequential read\' ela= 6985 file#=1 block#=321 blocks=1 obj#=34 tim=426776425534
  14. WAIT #463855704: nam=\'db file sequential read\' ela= 5586 file#=1 block#=225 blocks=1 obj#=15 tim=426776431233
  15. FETCH #463855704:c=0,e=12896,p=2,cr=2,cu=0,mis=0,r=1,dep=2,og=3,plh=906473769,tim=426776431399
  16. STAT #463855704 id=1 cnt=1 pid=0 pos=1 obj=15 op=\'TABLE ACCESS BY INDEX ROWID UNDO$ (cr=2 pr=2 pw=0 time=12884 us)\'
  17. STAT #463855704 id=2 cnt=1 pid=1 pos=1 obj=34 op=\'INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=1 pw=0 time=7116 us)\'
  18. CLOSE #463855704:c=0,e=24,dep=2,type=0,tim=426776431598
  19. =====================
  20. PARSING IN CURSOR #463855704 len=288 dep=2 uid=0 oct=3 lid=0 tim=426776432375 hv=1456614789 ad=\'7ff648bf718\' sqlid=\'57guu81bd4bc5\'
  21. select name,online$,contents$,undofile#,undoblock#,blocksize,dflmaxext,dflinit,dflincr,dflextpct,dflminext, dflminlen, owner#,scnwrp,scnbas, NVL(pitrscnwrp, 0), NVL(pitrscnbas, 0), dflogging, bitmapped, inc#, flags, plugged, NVL(spare1,0), NVL(spare2,0), affstrength from ts$ where ts#=:1
  22. END OF STMT
  23. PARSE #463855704:c=0,e=716,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=0,tim=426776432373
  24. BINDS #463855704:
  25. Bind#0
  26. oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  27. oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  28. kxsbbbfp=1ba5dc10 bln=22 avl=02 flg=05
  29. value=5
  30. EXEC #463855704:c=0,e=1258,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=2397009925,tim=426776433862
  31. WAIT #463855704: nam=\'db file sequential read\' ela= 4996 file#=1 block#=185 blocks=1 obj#=7 tim=426776438948
  32. WAIT #463855704: nam=\'db file sequential read\' ela= 2779 file#=1 block#=182 blocks=1 obj#=16 tim=426776441822
  33. FETCH #463855704:c=0,e=8008,p=2,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=2397009925,tim=426776441908
  34. STAT #463855704 id=1 cnt=1 pid=0 pos=1 obj=16 op=\'TABLE ACCESS CLUSTER TS$ (cr=2 pr=2 pw=0 time=8001 us)\'
  35. STAT #463855704 id=2 cnt=1 pid=1 pos=1 obj=7 op=\'INDEX UNIQUE SCAN I_TS# (cr=1 pr=1 pw=0 time=5110 us)\'
  36. CLOSE #463855704:c=0,e=62,dep=2,type=0,tim=426776442009
  37. *** 2014-02-12 10:16:00.389
  38. dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=10, mask=0x0)
  39. ----- Error Stack Dump -----
  40. ORA-01173: data dictionary indicates missing data file from system tablespace
  41. ----- Current SQL Statement for this session (sql_id=4krwuz0ctqxdt) -----
  42. select ctime, mtime, stime from obj$ where obj# = :1
  43. ----- Call Stack Trace -----
  44. calling call entry argument values in hex
  45. location type point (? means dubious value)
  46. -------------------- -------- -------------------- ----------------------------
  47. ksedst1()+129 CALL??? skdstdst() 100000000 14760BCF8 005280590
  48. 000000006
  49. ksedst()+69 CALL??? ksedst1() 14760BD38 000000000 14760BCEC
  50. 00000000B
  51. dbkedDefDump()+4541 CALL??? ksedst() 01B8AB167 000000FC8 01B8AB15B
  52. 000000000
  53. ksedmp()+43 CALL??? dbkedDefDump() 00000000A 000000000 018F9DCD0
  54. 0000282D4
  55. dbkdaKsdActDriver() CALL??? ksedmp() 000000000 000000000 000000000
  56. +2204 000000000
  57. dbgdaExecuteAction( CALL??? dbkdaKsdActDriver() 005280590 01B8AD3F0 000000000
  58. )+1103 000000000

4.尝试无undo 启动,屏蔽掉undo .

5.下载老熊的odu,挖取undo段的名称.(11g rollback segmentname和9i的10g的不一样)


点击(此处)折叠或打开

  1. unload dict
  2. unload TABLE sys.undo$
找到undo的名称。
利用隐藏参数

点击(此处)折叠或打开

  1. *._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$)
库并没有拉起来,怀疑是不能屏蔽所有,屏蔽的不合适。

4.还原undo ,把undo 重新加到控制文件里面.


点击(此处)折叠或打开

  1. SQL> CREATE CONTROLFILE REUSE DATABASE \"ORCL\" RESETLOGS NOARCHIVELOG
  2.   2 MAXLOGFILES 16
  3.   3 MAXLOGMEMBERS 3
  4.   4 MAXDATAFILES 100
  5.   5 MAXINSTANCES 8
  6.   6 MAXLOGHISTORY 292
  7.   7 LOGFILE
  8.   8 GROUP 1 'F:\app\Administrator\oradata\orcl\redo01.log' SIZE 50M BLOCKSIZE 512,
  9.   9 GROUP 2 'F:\app\Administrator\oradata\orcl\redo02.log' SIZE 50M BLOCKSIZE 512,
  10.  10 GROUP 3 'F:\app\Administrator\oradata\orcl\redo03.log' SIZE 50M BLOCKSIZE 512
  11.  11 DATAFILE
  12.  12 'F:\app\\Administrator\oradata\orcl\system01.dbf',
  13.  13 'F:\app\\Administrator\oradata\orcl\sysaux01.dbf',
  14.  14 'F:\app\\Administrator\oradata\orcl\users01.dbf',
  15.  15 'F:\app\\Administrator\oradata\orcl\UNDOTBS02.DBF',
  16.  16 'F:\app\\Administrator\oradata\orcl\example01.dbf'

  17.  18 CHARACTER SET US7ASCII
  18.  19 ;
  19. CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
  20. *
  21. 第 1 行出现错误:
  22. ORA-01503: CREATE CONTROLFILE ??
  23. ORA-01189: ????????????? RESETLOGS
  24. ORA-01110: ???? 8: 'F:\app\Administrator\oradata\orcl\UNDOTBS02.DBF'


此原因是我们已经丢弃过它一次重建过控制文件。

6.bbed修改文件头

点击(此处)折叠或打开

  1. SQL> col checkpoint_change# format xxxxxxxxx
  2. SQL> /

  3.      FILE# CHECKPOINT_CHANGE#
  4. ---------- ------------------
  5.          1 5ee4df4db
  6.          2 5ee4df4db
  7.          4 5ee4df4db
  8.          5 5ee4df4db
  9.          7 5ee4df4db

  10. SQL> select file#,checkpoint_change# from v$datafile;

  11. SQL> select resetlogs_change#, to_char(resetlogs_time,\'mm/dd/yyyy hh24:mi:ss\') time from v$database;

  12. RESETLOGS_CHANGE# TIME
  13. ----------------- --------------------------------------
  14.         5ee4df4d8 02/11/2014 18:51:36
  15.         

  16.         
  17. BBED> dump dba 1,2 offset 116;
  18.  File: F:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\SYSTEM01.DBF (1)
  19.  Block: 2 Offsets: 116 to 627 Dba:0x00400002
  20. ------------------------------------------------------------------------
  21.  d8f44dee 05000000 00000000 00000000 00000000 00000420 d00d0000 a8420632
  22.  cf0d0000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  23.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  24.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  25.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  26.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  27.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 06005359
  28.  5354454d 00000000 00000000 00000000 00000000 00000000 00000000 01000000
  29.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  30.  00000000 00000000 0a000a00 9b410632 d4f44dee 05000000 00000000 00000000
  31.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  32.  00000000 00000000 00000000 00000000 dbf44dee 05000000 b8420632 01000000
  33.  01000000 02000000 10000000 02000000 00000000 00000000 00000000 00000000
  34.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  35.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  36.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  37.  <32 bytes per line>

  38. BBED> modify /x d8f44dee dba 3,2 dba offset 116;
  39. BBED-00215: editing not allowed in BROWSE mode


  40. BBED> set mode edit
  41.         MODE Edit

  42. BBED> modify /x d8f44dee dba 3,2 dba offset 116;
  43. BBED-00207: invalid offset specifier (offset)


  44. BBED> modify /x d8f44dee dba 3,2 offset 116;
  45. Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
  46.  File: F:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\UNDOTBS02.DBF (3)
  47.  Block: 2 Offsets: 116 to 627 Dba:0x00c00002
  48. ------------------------------------------------------------------------
  49.  d8f44dee 05000000 00000000 00000000 00000000 00000400 db060000 811a0632
  50.  da060000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  51.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  52.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  53.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  54.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  55.  00000000 00000000 00000000 00000000 00000000 00000000 05000000 0800554e
  56.  444f5442 53320000 00000000 00000000 00000000 00000000 00000000 08000000
  57.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  58.  00000000 00000000 00000000 b7180632 a8f44dee 05000000 00000000 00000000
  59.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  60.  00000000 00000000 00000000 00000000 dbf44dee 05000000 9a1a0632 01000000
  61.  01000000 02000000 10000000 02000000 00000000 00000000 00000000 00000000
  62.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  63.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  64.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  65.  <32 bytes per line>

  66. BBED> sum apply
  67. Check value for File 3, Block 2:
  68. current = 0x6823, required = 0x6823


  69. BBED> modify /x b842 dba 3,2 offset 112

  70. BBED> dump dba 3,2 offset 112;
  71.  File: F:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\UNDOTBS02.DBF (3)
  72.  Block: 2 Offsets: 112 to 623 Dba:0x00c00002
  73. ------------------------------------------------------------------------
  74.  b8420632 d8f44dee 05000000 00000000 00000000 00000000 00000400 db060000
  75.  811a0632 da060000 00000000 00000000 00000000 00000000 00000000 00000000
  76.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  77.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  78.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  79.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  80.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 05000000
  81.  0800554e 444f5442 53320000 00000000 00000000 00000000 00000000 00000000
  82.  08000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  83.  00000000 00000000 00000000 00000000 b7180632 a8f44dee 05000000 00000000
  84.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  85.  00000000 00000000 00000000 00000000 00000000 dbf44dee 05000000 9a1a0632
  86.  01000000 01000000 02000000 10000000 02000000 00000000 00000000 00000000
  87.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  88.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  89.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  90.  <32 bytes per line>

7.重建控制文件

点击(此处)折叠或打开

  1. SQL> startup force nomount;
  2. ORACLE 例程已经启动。
  3. Total System Global Area 1043886080 bytes
  4. Fixed Size 2182344 bytes
  5. Variable Size 633340728 bytes
  6. Database Buffers 402653184 bytes
  7. Redo Buffers 5709824 bytes
  8. SQL> CREATE CONTROLFILE REUSE DATABASE \"ORCL\" RESETLOGS NOARCHIVELOG
  9. 2 MAXLOGFILES 16
  10. 3 MAXLOGMEMBERS 3
  11. 4 MAXDATAFILES 100
  12. 5 MAXINSTANCES 8
  13. 6 MAXLOGHISTORY 292
  14. 7 LOGFILE
  15. 8 GROUP 1 \'F:\\app\\Administrator\\oradata\\orcl\\redo01.log\' SIZE 50M BLOCKSIZE 512,
  16. 9 GROUP 2 \'F:\\app\\Administrator\\oradata\\orcl\\redo02.log\' SIZE 50M BLOCKSIZE 512,
  17. 10 GROUP 3 \'F:\\app\\Administrator\\oradata\\orcl\\redo03.log\' SIZE 50M BLOCKSIZE 512
  18. 11 DATAFILE
  19. 12 \'F:\\app\\Administrator\\oradata\\orcl\\system01.dbf\',
  20. 13 \'F:\\app\\Administrator\\oradata\\orcl\\sysaux01.dbf\',
  21. 14 \'F:\\app\\Administrator\\oradata\\orcl\\users01.dbf\',
  22. 15 \'F:\\app\\Administrator\\oradata\\orcl\\UNDOTBS02.DBF\',
  23. 16 \'F:\\app\\Administrator\\oradata\\orcl\\example01.dbf\'
  24. 17 CHARACTER SET US7ASCII
  25. 18 ;
  26. 控制文件已创建。 --已经回来

8.尝试开库

点击(此处)折叠或打开

  1. SQL> select * from v$tablespace;
  2. TS# NAME INCLUD BIGFIL FLASHB ENCRYP
  3. ---------- ------------------------------------------------------------ ------ ------ ------ ------
  4. 0 SYSTEM YES NO YES
  5. 1 SYSAUX YES NO YES
  6. 4 USERS YES NO YES
  7. 5 UNDOTBS2 YES NO YES
  8. 6 EXAMPLE YES NO YES
  9. SQL>
  10. SQL> recover database;
  11. ORA-00283: recovery session canceled due to errors
  12. ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
  13. SQL> recover database using backup controlfile;
  14. ORA-00279: ?? 25472922843 (? 02/11/2014 16:00:26 ??) ???? 1 ????
  15. ORA-00289: ??: F:\\APP\\ADMINISTRATOR\\PRODUCT\\11.2.0\\DBHOME_1\\RDBMS\\ARC0000000001_0839271096.0001
  16. ORA-00280: ?? 25472922843 (???? 1) ??? #1 ?
  17. 指定日志: {=suggested | filename | AUTO | CANCEL}
  18. cancel
  19. 介质恢复已取消。
  20. SQL> alter database open;
  21. alter database open
  22. *
  23. 第 1 行出现错误:
  24. ORA-01589: ??????????? RESETLOGS ? NORESETLOGS ??
  25. SQL> alter database open resetlogs;
  26. alter database open resetlogs
  27. *
  28. 第 1 行出现错误:
  29. ORA-01092: ORACLE instance terminated. Disconnection forced
  30. ORA-00704: bootstrap process failure
  31. ORA-39700: database must be opened with UPGRADE option
  32. 进程 ID: 5348
  33. 会话 ID: 125 序列号: 3
SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [5], [3998088408], [5], [3998099833], [33554720], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [5], [3998088407], [5], [3998099833], [33554720], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [5], [3998088405], [5], [3998099833], [33554720], [], [], [], [], [], []
进程 ID: 5412
会话 ID: 125 序列号: 3

报2662  顿时感觉无压力了,谁知道反而卡在这里,脑子犯晕了。

9.期间发现中数据文件CONET.DBF不在里面,重复6操作。
开库

点击(此处)折叠或打开

  1. SQL> recover database using backup controlfile;
  2. ORA-00279: ?? 25472922843 (? 02/11/2014 18:51:36 ??) ???? 1 ????
  3. ORA-00289: ??: F:\\APP\\ADMINISTRATOR\\PRODUCT\\11.2.0\\DBHOME_1\\RDBMS\\ARC0000000001_0839361886.0001
  4. ORA-00280: ?? 25472922843 (???? 1) ??? #1 ?




  5. 指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
  6. cancel
  7. 介质恢复已取消。
  8. SQL> alter database open resetlogs upgrade;
  9. alter database open resetlogs upgrade
  10. *
  11. 第 1 行出现错误:
  12. ORA-00600: ??????, ??: [kcvorl_2], [5], [3998086364], [5], [3998086368], [], [], [], [], [], [], []



怀疑是文件头的scn的

修改scn一致 


点击(此处)折叠或打开

  1. BBED> modify /x e3f4 dba 6,2 offset 484;
  2.  File: F:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\CONET.DBF (6)
  3.  Block: 2 Offsets: 484 to 995 Dba:0x01800002
  4. ------------------------------------------------------------------------
  5.  e3f44dee 05000000 b8420632 01000000 01000000 02000000 10000000 02000000
  6.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  7.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  8.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  9.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  10.  0c000c00 0c000100 00000000 00000000 00000000 0200c001 00000000 00000000
  11.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  12.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  13.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  14.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  15.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  16.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  17.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  18.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  19.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  20.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  21.  <32 bytes per line>

  22. BBED> sum apply;
  23. Check value for File 6, Block 2:
  24. current = 0x89de, required = 0x89de

  25. BBED>

10.开库依然是ora600-2662,但是消除了[kcvorl_2]

点击(此处)折叠或打开

  1. SQL> alter database open resetlogs upgrade;
  2. alter database open resetlogs upgrade
  3. *
  4. 第 1 行出现错误:
  5. ORA-00603: ORACLE server session terminated by fatal error
  6. ORA-00600: internal error code, arguments: [2662], [5], [3998088409], [5],
  7. [3998099833], [33554720], [], [], [], [], [], []
  8. ORA-00600: internal error code, arguments: [2662], [5], [3998088408], [5],
  9. [3998099833], [33554720], [], [], [], [], [], []
  10. ORA-01092: ORACLE instance terminated. Disconnection forced
  11. ORA-00600: internal error code, arguments: [2662], [5], [3998088405], [5],
  12. [3998099833], [33554720], [], [], [], [], [], []
  13. 进程 ID: 2680
  14. 会话 ID: 125 序列号: 3

11.尝试修改dba 33554720对应的block的scn,悲剧发生了机器死机了,bbed不能用了,心疼...

12.尝试手工推进scn
手工推进scn有几种方法.


点击(此处)折叠或打开

  1. ORADEBUG POKE
  2. *._allow_error_simulation = TRUE
  3. *._smu_debug_mode = 268435456
  4. alter session set events 'IMMEDIATE trace name ADJUST_SCN level 1';

 我用的第二种


点击(此处)折叠或打开

  1. SQL> conn / as sysdba
  2. 已连接到空闲例程。
  3. SQL> create spfile from pfile;

  4. 文件已创建。

  5. SQL> startup nomount;
  6. ORACLE 例程已经启动。

  7. Total System Global Area 1043886080 bytes
  8. Fixed Size 2182344 bytes
  9. Variable Size 633340728 bytes
  10. Database Buffers 402653184 bytes
  11. Redo Buffers 5709824 bytes
  12. SQL> CREATE CONTROLFILE REUSE DATABASE \"ORCL\" RESETLOGS NOARCHIVELOG
  13.   2 MAXLOGFILES 16
  14.   3 MAXLOGMEMBERS 3
  15.   4 MAXDATAFILES 100
  16.   5 MAXINSTANCES 8
  17.   6 MAXLOGHISTORY 292
  18.   7 LOGFILE
  19.   8 GROUP 1 \'F:\\app\\Administrator\\oradata\\orcl\\redo01.log\' SIZE 50M BLOCKSIZE 512,
  20.   9 GROUP 2 \'F:\\app\\Administrator\\oradata\\orcl\\redo02.log\' SIZE 50M BLOCKSIZE 512,
  21.  10 GROUP 3 \'F:\\app\\Administrator\\oradata\\orcl\\redo03.log\' SIZE 50M BLOCKSIZE 512
  22.  11 DATAFILE
  23.  12 \'F:\\app\\Administrator\\oradata\\orcl\\system01.dbf\',
  24.  13 \'F:\\app\\Administrator\\oradata\\orcl\\sysaux01.dbf\',
  25.  14 \'F:\\app\\Administrator\\oradata\\orcl\\users01.dbf\',
  26.  15 \'F:\\app\\Administrator\\oradata\\orcl\\example01.dbf\',
  27.  16 \'F:\\app\\Administrator\\oradata\\orcl\\CONET.DBF\',
  28.  17 \'F:\\app\\Administrator\\oradata\\orcl\\UNDOTBS02.DBF\'
  29.  18 CHARACTER SET US7ASCII
  30.  19 ;

  31. 控制文件已创建。

  32. SQL>
  33. SQL> alter database open resetlogs upgrade;

  34. 数据库已更改。

  35. SQL>

新建的undo 修改默认

点击(此处)折叠或打开

  1. SQL> create undo tablespace un01 datafile \'F:\\app\\Administrator\\oradata\\orcl\\unto01.dbf\' size 50m autoextend on;
  2. 表空间已创建
  3. SQL> alter system set undo_tablespace=un01 scope=spfile;
  4. 系统已更改。

删除老的 undo发生问题.

点击(此处)折叠或打开

  1. SQL> drop tablespace UNDOTBS2;
  2. drop tablespace UNDOTBS2
  3. *
  4. 第 1 行出现错误:
  5. ORA-01548: 已找到活动回退段 '_SYSSMU30_722648610$', 终止删除表空间

加上隐藏参数

点击(此处)折叠或打开

  1. *._corrupted_rollback_segments=(_SYSSMU30_722648610$)

成功删除

点击(此处)折叠或打开

  1. SQL> drop tablespace UNDOTBS2;

  2. 表空间已删除。

  3. SQL> show parameter undo

  4. NAME TYPE VALUE
  5. ------------------------------------ ----------- ------------------------------
  6. undo_management string MANUAL
  7. undo_retention integer 10800
  8. undo_tablespace string UNDOTBS2
  9. SQL> select * from v$tablespace;

  10.        TS# NAME INC BIG FLA ENC
  11. ---------- ------------------------------ --- --- --- ---
  12.          0 SYSTEM YES NO YES
  13.          1 SYSAUX YES NO YES
  14.          4 USERS YES NO YES
  15.          6 EXAMPLE YES NO YES
  16.          8 CONET YES NO YES
  17.          3 TEMP NO NO YES
  18.          7 SDE YES NO YES
  19.          9 UN01 YES NO YES

  20. 已选择8行。

至此完成。

下面介绍老王的方法。
把修改过的undo和数据文件发给他。通过事件拉起。

点击(此处)折叠或打开

  1. SQL> startup mount
  2. ORACLE instance started.

  3. Total System Global Area 626327552 bytes
  4. Fixed Size         2255832 bytes
  5. Variable Size         234882088 bytes
  6. Database Buffers     381681664 bytes
  7. Redo Buffers         7507968 bytes
  8. Database mounted.
  9. SQL> alter system set event=\'10513 trace name context forever,level 2 : 10512 trace name context forever,level 1: 10511 trace name context forever,level 2: 10510 trace name context forever,level 1\' scope=spfile;

  10. System altered.

  11. Elapsed: 00:00:00.01
  12. SQL> shutdown immediate
  13. ORA-01109: database not open


  14. Database dismounted.
  15. ORACLE instance shut down.
  16. SQL> exit
  17. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  18. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  19. [oracle@ben ~]$ sqlplus / as sysdba

  20. SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 13 23:30:10 2014

  21. Copyright (c) 1982, 2013, Oracle. All rights reserved.

  22. Connected to an idle instance.

  23. SQL> startup mount
  24. ORACLE instance started.

  25. Total System Global Area 626327552 bytes
  26. Fixed Size         2255832 bytes
  27. Variable Size         234882088 bytes
  28. Database Buffers     381681664 bytes
  29. Redo Buffers         7507968 bytes
  30. Database mounted.
  31. SQL> alter database open;
  32. alter database open
  33. *
  34. ERROR at line 1:
  35. ORA-01113: file 1 needs media recovery
  36. ORA-01110: data file 1: \'/home/oracle/oradata/orcl/SYSTEM01.DBF\'


  37. Elapsed: 00:00:00.04
  38. SQL> recover database;
  39. Media recovery complete.
  40. SQL> alter database open;
  41. alter database open
  42. *
  43. ERROR at line 1:
  44. ORA-01092: ORACLE instance terminated. Disconnection forced
  45. ORA-00704: bootstrap process failure
  46. ORA-39700: database must be opened with UPGRADE option
  47. Process ID: 2205
  48. Session ID: 125 Serial number: 5


  49. Elapsed: 00:00:02.67
  50. SQL> exit
  51. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  52. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  53. [oracle@ben ~]$ sqlplus / as sysdba

  54. SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 13 23:30:43 2014

  55. Copyright (c) 1982, 2013, Oracle. All rights reserved.

  56. Connected to an idle instance.

  57. SQL> startup mount;
  58. ORACLE instance started.

  59. Total System Global Area 626327552 bytes
  60. Fixed Size         2255832 bytes
  61. Variable Size         234882088 bytes
  62. Database Buffers     381681664 bytes
  63. Redo Buffers         7507968 bytes
  64. Database mounted.
  65. SQL> alter database open upgrade;
  66. alter database open upgrade
  67. *
  68. ERROR at line 1:
  69. ORA-01113: file 1 needs media recovery
  70. ORA-01110: data file 1: \'/home/oracle/oradata/orcl/SYSTEM01.DBF\'


  71. Elapsed: 00:00:00.01
  72. SQL> recover database;
  73. Media recovery complete.
  74. SQL> alter database open upgrade;

  75. Database altered.

  76. Elapsed: 00:00:06.68
事件的说明:

点击(此处)折叠或打开

  1. 10513事件用以阻止SMON在启动数据库后执行事务恢复(transaction recovery)


  2. 10512事件用以阻止SMON shrink rollback segment


  3. 10511事件用以阻止SMON check to cleanup undo dictionary


  4. 10500事件用以阻止SMON check to offline pending offline rollback segment
至此ok。改天模拟下2662和 1173。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29477587/viewspace-1081754/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-02-09

  • 博文量
    53
  • 访问量
    275706