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

最新推荐文章于 2023-06-05 21:55:50 发布
最新推荐文章于 2023-06-05 21:55:50 发布
阅读量1.1k 收藏 1
点赞数
文章标签： 数据库 操作系统
问题：一朋友的库开库报错：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
  7. 开库报错：
  8. SQL> alter database open resetlogs;
  9. alter database open resetlogs
  10. *
  11. ERROR at line 1:
  12. ORA-01092: ORACLE instance terminated. Disconnection forced
  13. ORA-00704: bootstrap process failure
  14. ORA-00704: bootstrap process failure
  15. ORA-00604: error occurred at recursive SQL level 1
  16. ORA-01173: data dictionary indicates missing data file from system tablespace
  17. Process ID: 8572
  18. 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\'

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

2.做10046事件跟踪。

点击(此处)折叠或打开

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


点击(此处)折叠或打开

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

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


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

6.bbed修改文件头

点击(此处)折叠或打开

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

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

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

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

  16. RESETLOGS_CHANGE# TIME
  17. ----------------- --------------------------------------
  18.         5ee4df4d8 02/11/2014 18:51:36
  19.         

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

  43.  <32 bytes per line>

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


  49. BBED> set mode edit
  50.         MODE Edit

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


  56. BBED> modify /x d8f44dee dba 3,2 offset 116;
  57. Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
  58.  File: F:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\UNDOTBS02.DBF (3)
  59.  Block: 2 Offsets: 116 to 627 Dba:0x00c00002
  60. ------------------------------------------------------------------------
  61.  d8f44dee 05000000 00000000 00000000 00000000 00000400 db060000 811a0632
  62.  da060000 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.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  66.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  67.  00000000 00000000 00000000 00000000 00000000 00000000 05000000 0800554e
  68.  444f5442 53320000 00000000 00000000 00000000 00000000 00000000 08000000
  69.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  70.  00000000 00000000 00000000 b7180632 a8f44dee 05000000 00000000 00000000
  71.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  72.  00000000 00000000 00000000 00000000 dbf44dee 05000000 9a1a0632 01000000
  73.  01000000 02000000 10000000 02000000 00000000 00000000 00000000 00000000
  74.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  75.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  76.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  78.  <32 bytes per line>

  80. BBED> sum apply
  81. Check value for File 3, Block 2:
  82. current = 0x6823, required = 0x6823


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

  87. BBED> dump dba 3,2 offset 112;
  88.  File: F:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\UNDOTBS02.DBF (3)
  89.  Block: 2 Offsets: 112 to 623 Dba:0x00c00002
  90. ------------------------------------------------------------------------
  91.  b8420632 d8f44dee 05000000 00000000 00000000 00000000 00000400 db060000
  92.  811a0632 da060000 00000000 00000000 00000000 00000000 00000000 00000000
  93.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  94.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  95.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  96.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  97.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 05000000
  98.  0800554e 444f5442 53320000 00000000 00000000 00000000 00000000 00000000
  99.  08000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  100.  00000000 00000000 00000000 00000000 b7180632 a8f44dee 05000000 00000000
  101.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  102.  00000000 00000000 00000000 00000000 00000000 dbf44dee 05000000 9a1a0632
  103.  01000000 01000000 02000000 10000000 02000000 00000000 00000000 00000000
  104.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  105.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  106.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  108.  <32 bytes per line>

7.重建控制文件

点击(此处)折叠或打开

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

8.尝试开库

点击(此处)折叠或打开

  1. SQL> select * from v$tablespace;
  3. TS# NAME INCLUD BIGFIL FLASHB ENCRYP
  4. ---------- ------------------------------------------------------------ ------ ------ ------ ------
  5. 0 SYSTEM YES NO YES
  6. 1 SYSAUX YES NO YES
  7. 4 USERS YES NO YES
  8. 5 UNDOTBS2 YES NO YES
  9. 6 EXAMPLE YES NO YES
  11. SQL>
  14. SQL> recover database;
  15. ORA-00283: recovery session canceled due to errors
  16. ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
  19. SQL> recover database using backup controlfile;
  20. ORA-00279: ?? 25472922843 (? 02/11/2014 16:00:26 ??) ???? 1 ????
  21. ORA-00289: ??: F:\\APP\\ADMINISTRATOR\\PRODUCT\\11.2.0\\DBHOME_1\\RDBMS\\ARC0000000001_0839271096.0001
  22. ORA-00280: ?? 25472922843 (???? 1) ??? #1 ?
  25. 指定日志: {=suggested | filename | AUTO | CANCEL}
  26. cancel
  27. 介质恢复已取消。
  28. SQL> alter database open;
  29. alter database open
  30. *
  31. 第 1 行出现错误:
  32. ORA-01589: ??????????? RESETLOGS ? NORESETLOGS ??
  35. SQL> alter database open resetlogs;
  36. alter database open resetlogs
  37. *
  38. 第 1 行出现错误:
  39. ORA-01092: ORACLE instance terminated. Disconnection forced
  40. ORA-00704: bootstrap process failure
  41. ORA-39700: database must be opened with UPGRADE option
  42. 进程 ID: 5348
  43. 会话 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 ?




  9. 指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
  10. cancel
  11. 介质恢复已取消。
  12. SQL> alter database open resetlogs upgrade;
  13. alter database open resetlogs upgrade
  14. *
  15. 第 1 行出现错误:
  16. 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

  22.  <32 bytes per line>

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

  28. 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有几种方法.


点击(此处)折叠或打开

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

 我用的第二种


点击(此处)折叠或打开

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

  5. 文件已创建。

  7. SQL> startup nomount;
  8. ORACLE 例程已经启动。

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

  35. 控制文件已创建。

  37. SQL>
  38. SQL> alter database open resetlogs upgrade;

  40. 数据库已更改。

  42. SQL>

新建的undo 修改默认

点击(此处)折叠或打开

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

删除老的 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;

  3. 表空间已删除。

  5. SQL> show parameter undo

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

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

  25. 已选择8行。

至此完成。

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

点击(此处)折叠或打开

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

  4. Total System Global Area 626327552 bytes
  5. Fixed Size         2255832 bytes
  6. Variable Size         234882088 bytes
  7. Database Buffers     381681664 bytes
  8. Redo Buffers         7507968 bytes
  9. Database mounted.
  10. 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;

  12. System altered.

  14. Elapsed: 00:00:00.01
  15. SQL> shutdown immediate
  16. ORA-01109: database not open


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

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

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

  30. Connected to an idle instance.

  32. SQL> startup mount
  33. ORACLE instance started.

  35. Total System Global Area 626327552 bytes
  36. Fixed Size         2255832 bytes
  37. Variable Size         234882088 bytes
  38. Database Buffers     381681664 bytes
  39. Redo Buffers         7507968 bytes
  40. Database mounted.
  41. SQL> alter database open;
  42. alter database open
  43. *
  44. ERROR at line 1:
  45. ORA-01113: file 1 needs media recovery
  46. ORA-01110: data file 1: \'/home/oracle/oradata/orcl/SYSTEM01.DBF\'


  49. Elapsed: 00:00:00.04
  50. SQL> recover database;
  51. Media recovery complete.
  52. SQL> alter database open;
  53. alter database open
  54. *
  55. ERROR at line 1:
  56. ORA-01092: ORACLE instance terminated. Disconnection forced
  57. ORA-00704: bootstrap process failure
  58. ORA-39700: database must be opened with UPGRADE option
  59. Process ID: 2205
  60. Session ID: 125 Serial number: 5


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

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

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

  73. Connected to an idle instance.

  75. SQL> startup mount;
  76. ORACLE instance started.

  78. Total System Global Area 626327552 bytes
  79. Fixed Size         2255832 bytes
  80. Variable Size         234882088 bytes
  81. Database Buffers     381681664 bytes
  82. Redo Buffers         7507968 bytes
  83. Database mounted.
  84. SQL> alter database open upgrade;
  85. alter database open upgrade
  86. *
  87. ERROR at line 1:
  88. ORA-01113: file 1 needs media recovery
  89. ORA-01110: data file 1: \'/home/oracle/oradata/orcl/SYSTEM01.DBF\'


  92. Elapsed: 00:00:00.01
  93. SQL> recover database;
  94. Media recovery complete.
  95. SQL> alter database open upgrade;

  97. Database altered.

  99. Elapsed: 00:00:06.68
事件的说明：

点击(此处)折叠或打开

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


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


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


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

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

转载于:http://blog.itpub.net/29477587/viewspace-1081754/

cizhaorun3127
关注
12C ORA-错误汇总3 ORA-01500 to ORA-02098
ugghhj的博客
01-26 2088
12C ORA-错误汇总3 ORA-01500 to ORA-02098
12C ORA-错误汇总1 ORA-00000 to ORA-00877
ugghhj的博客
11-10 1540
12C ORA-错误汇总1 ORA-00000 to ORA-00877
ORA-01173: data dictionary indicates missing data file from system tablespace
脑子进水养啥鱼？的博客
09-08 821
背景： 重建控制文件后，启动数据库时报错： Errors in file /home/oracle/app/diag/rdbms/sptdb/sptdb/trace/sptdb_ora_10222.trc: ORA-01173: data dictionary indicates missing data file from system tablespace Errors in file /home/oracle/app/diag/rdbms/sptdb/sptdb/trace/sptdb_ora_102
oracle恢复主键丢失,案例:Oracle重建控制文件丢失undo异常恢复 ORA-01173模拟与恢复...
weixin_28883589的博客
04-06 749
天萃荷净重建控制文件丢失undo异常恢复 ORA-01173模拟与恢复数据库异常关闭，使用resetlogs方式重建控制文件，不包含undo表空间相关数据库，然后尝试resetlogs打开数据库，非常容易出现ORA-01173: data dictionary indicates missing data file from system tablespace之类的错误，本文通过模拟该错误，并简单...
linux ora 01092,ORA-01173的模拟与故障处理
weixin_39959569的博客
05-26 505
1，数据库版本www.htz.pw>select*fromv$versionwhererownum<3;BANNER—————————————————————-OracleDatabase10gEnterpriseEditionRelease10.2.0.4.0–64biPL/SQLRelease10.2.0.4.0–Productionwww.h...
ORA-16433,ORA-01173恢复
Goolen的专栏
04-17 3480
同学打包上传了一个有问题的库，正好这几天没啥事，下载下来试着恢复了一下，恢复过程如下： 不知道他之前做过什么操作，也不知道这个库是怎么损坏的，下载下来后大致看了一下 打包文件信息如下： +++initorcl.ora文件内容： orcl.__oracle_base='D:\app\oracle'#ORACLE_BASE set from environment *.audit_fil
oracle登录_报ora00604,WIN平台ORACLE启动ORA-00604 ORA-01555 ORA-01173报错
weixin_29944883的博客
04-05 756
2触发ORA-00704 ORA-00604 ORA-01555报错d:\wendang\SkyDrive\rs2\sql>sqlplus/assysdbaSQL*Plus:Release11.2.0.3.0ProductiononSunOct1220:47:472014Copyright(c)1982,2011,Oracle.Allrightsre...
12C ORA 错误汇总18 ORA 57000 to ORA 65535 EXP 00000 to EXP 00114
ugghhj的博客
11-07 2576
12C ORA 错误汇总18 ORA 57000 to ORA 65535 EXP 00000 to EXP 00114
Oracle数据恢复专题
ORACLE数据库数据恢复、性能优化、故障诊断来问问MACLEAN
07-08 791
备份恢复是Oracle中永恒的话题， 只要有数据 就有备份恢复的需求。 而在国内对于备份以及备份的可用性往往被企业所忽视。这造成了再数据库恢复上存在着东西方的差异。 更多的老外DBA把经历花在对Oracle内部原理和性能优化的研究上。 如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复! 诗檀软件专业数据库修复团队 服务热线 ： 13764045638 ...
RAC: Frequently Asked Questions [ID 220970.1]
11-22 1523
RAC: Frequently Asked Questions [ID 220970.1]Applies to: Oracle Server - Enterprise Edition - Version 9....
system表空间丢失损坏，或者丢失异常处理
dale
11-29 1235
system表空间丢失损坏，或者丢失异常处理
经典故障:四个雷,3*2*2*3种随机方法的特殊恢复案例
sqlora的专栏
03-02 561
前言 最近我们公司恢复大牛翔宇哥给我们精心准备了个故障，埋了四个雷，整个恢复过程感觉像是过山车，起起伏伏，觉得很有意思。跟翔宇哥和roger老板学了点皮毛，文章中有描述不太清楚的地方，还请各位看官多多担待。今天把记录总结出来，供大家分享。 恢复文件 就给一个压缩的system,起库。 恢复过程 首先，获取system文件的字符集，数据库名，然后创建参数文件，重建控制文件，这里就不过多介绍，话不多说，先尝试启动数据库。 第一个雷 SQL> startup nomount pfil.
Oracle错误提示列表
Frist_huangsuli的专栏
04-13 3578
ORA-00568: 超出中断处理程序的最大数 orA-00574: osndnt: $CANCEL 失败 (中断) orA-00575: osndnt: $QIO 失败 (发送 out-of-band 中断) orA-00576: 带内中断协议错误 orA-00577: 带外中断协议错误 orA-00578: 重置协议错误 orA-00579: osndnt: 服务器收到连接请求格式
[转] ORACLE 错误编号表二
weixin_30782293的博客
03-08 135
ORA-01171: 数据文件因高级检查点错误而将脱机ORA-01172: 线程的恢复停止在块（在文件中）ORA-01173: 数据字典指明从系统表空间丢失的数据文件ORA-01174: DB_FILES 为 ，但需要为 才能兼容ORA-01175: 例程允许数据字典具有多于个文件ORA-01176: 控制文件允许数据字典具有多于个文件ORA-01177: 数据文件与字典不匹配- 可能...
ORA-00600 [4185]错误处理过程
m15217321304的博客
03-31 754
--//数据库启动报ORA-00600 4185错误 SQL> startup ORACLE instance started. Total System Global Area 242208768 bytes Fixed Size 2227176 bytes Variable Size 184550424 bytes Data...
oracle无法导出用户数据,Oracle数据库案例整理-导入导出Oracle数据失败-数据库用户被锁...
weixin_36261424的博客
04-05 807
1.1 现象描述导入数据失败，系统显示如下错误信息：SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing op...
oracle坏块修复步骤（索引坏块rebuild和表表坏块用rman,dbms_repair恢复）
一个只求实际的程序猿
06-05 3376
b）如果是文件系统且没做raid,但有备份和归档,在messages里会显示具体哪个磁盘出问题了,更换磁盘,然后用数据文件备份和归档、在线日志恢复到最后的时间点。,oracle会对基表加share锁,由于share锁和row-X是不兼容的,也就是说,在建立索引期间,无法对基表进行DML操作。实际上,oracle在rebuild时,在创建新索引过程中,并不会删除旧索引,直到新索引rebuild成功。c）如果是文件系统且没做raid,没有备份,那么就要按下面的步骤3里的操作恢复好坏块后,再更换磁盘。
mysql修复坏块_数据恢复：模拟2个逻辑坏块
weixin_33272515的博客
02-18 351
TESTA:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>SQL>SQL>SQL> create table...
Oracle 错误代码大全
黄泉颤抖
03-01 4261
最近在网上看见一个牛人总结的oracle的错误代码大全，觉得非常的有必要，于是山寨过来看看 ORA-00001:违反唯一约束条件(.) ORA-00017:请求会话以设置跟踪事件 ORA-00018:超出最大会话数 ORA-00019:超出最大会话许可数 ORA-00020:超出最大进程数() ORA-00021:会话附属于其它某些进程；无法转换会话 ORA-00022:无效的会话ID；访...
dbca建库ORA-19504:failed to create file "+DATA"
最新发布
04-03
好的，用户遇到了DBCA创建数据库时出现ORA-19504错误的问题，需要解决。首先，我需要理解这个错误的具体含义和可能的原因。根据引用中的信息，类似的ORA-01565错误涉及到无法识别或打开spfile，可能与存储路径或权限...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值