`
itspace
  • 浏览: 956737 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

记一次数据库open打开错误ora-00600 [3619]处理

 
阅读更多
事情起源:
客户需求:数据文件过大,客户业务表格清空数据之后需要缩小数据文件
SQL> select file_id,max(BLOCK_ID) from dba_extents where OWNER='TERSA' and SEGMENT_NAME='AA065' group by file_id;  

   FILE_ID MAX(BLOCK_ID)
---------- -------------
         4        102537

SQL> select max(BLOCK_ID) from dba_extents where file_id=4;

MAX(BLOCK_ID)
-------------
       102537

SQL> truncate table TERSA.AA065;

Table truncated.

SQL> select max(BLOCK_ID) from dba_extents where file_id=4;

MAX(BLOCK_ID)
-------------
        22793

SQL> select 22793*8192 from dual;

22793*8192
----------
186720256

SQL> !ls -l /lank/db/lank/users01.dbf
-rw-r----- 1 ora10g dba 841490432 Nov 14 13:18 /lank/db/lank/users01.dbf

客户将其缩小至200m
SQL> alter database datafile '/lank/db/lank/users01.dbf' resize 200m;

Database altered.

谁知!!!出于业务原因,该库在指定时间进行定期闪回,由于4号文件进行了resize缩小数据文件操作,闪回不支持
SQL> flashback database to restore point p1;
flashback database to restore point p1
*
ERROR at line 1:
ORA-38766: cannot flashback data file 4; file resized smaller
ORA-01110: data file 4: '/lank/db/lank/users01.dbf'

这时,同事将4号文件offline,将数据库进行了强制闪回,这里需要注意的是闪回之后如果打开数据库,4号文件的数据将全部丢失,同事看到此错误时,心里没底了。
SQL> alter database datafile 4 offline;

Database altered.

SQL> flashback database to restore point p1;
flashback database to restore point p1
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 4 will be lost if RESETLOGS is done
ORA-01110: data file 4: '/lank/db/lank/users01.dbf'

于是打算今天的数据不进行闪回,将数据库恢复至最新状态
SQL> recover database;
ORA-00279: change 11000524201524 generated at 11/14/2011 13:09:10 needed for
thread 1
ORA-00289: suggestion : /lank/db/arch/1_425_764441717.dbf
ORA-00280: change 11000524201524 for thread 1 is in sequence #425


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 11000524205605 generated at 11/14/2011 13:10:25 needed for
thread 1
ORA-00289: suggestion : /lank/db/arch/1_426_764441717.dbf
ORA-00280: change 11000524205605 for thread 1 is in sequence #426
ORA-00278: log file '/lank/db/arch/1_425_764441717.dbf' no longer needed for
this recovery

。。。
Log applied.
Media recovery complete.
SQL> alter database datafile 4 online;

Database altered.

悲剧的是数据库打开时,出现[3619]错误
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [3619], [1], [0], [], [], [], [], []

后台日志显示:
Mon Nov 14 13:56:34 CST 2011
Errors in file /app/admin/lank/udump/lank_ora_5394.trc:
ORA-00600: internal error code, arguments: [3619], [1], [0], [], [], [], [], []
ORA-600 signalled during: alter database open...

跟踪文件显示:
Media Recovery drop redo thread 1
File 1 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 3 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 5 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 6 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 7 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 8 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 9 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 10 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
File 11 (stop scn 11000524225166) completed recovery at checkpoint scn 11000524225166
*** 2011-11-14 13:56:34.778
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [3619], [1], [0], [], [], [], [], []
Current SQL statement for this session:




进一步检查相关文件的ckpt
SQL> select file#,checkpoint_change#,last_change#,status from v$datafile;

     FILE#  CHECKPOINT_CHANGE#        LAST_CHANGE# STATUS
---------- ------------------- ------------------- -------
         1      11000524225166      11000524225166 SYSTEM
         3      11000524225166      11000524225166 ONLINE
         4      11000524201524                     ONLINE
         5      11000524225166      11000524225166 ONLINE
         6      11000524225166      11000524225166 ONLINE
         7      11000524225166      11000524225166 ONLINE
         8      11000524225166      11000524225166 ONLINE
         9      11000524225166      11000524225166 ONLINE
        10      11000524225166      11000524225166 ONLINE
        11      11000524225166      11000524225166 ONLINE

10 rows selected.


SQL> select file#,fuzzy,checkpoint_change# from v$datafile_header;

     FILE# FUZ  CHECKPOINT_CHANGE#
---------- --- -------------------
         1 NO       11000524225166
         3 NO       11000524225166
         4 NO       11000524225167
         5 NO       11000524225166
         6 NO       11000524225166
         7 NO       11000524225166
         8 NO       11000524225166
         9 NO       11000524225166
        10 NO       11000524225166
        11 NO       11000524225166

10 rows selected.

SQL> select checkpoint_change#,last_change# from v$datafile;

     CHECKPOINT_CHANGE#          LAST_CHANGE#
----------------------- ---------------------
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167

10 rows selected.

SQL>
SQL> select file#,fuzzy,checkpoint_change# from v$datafile_header;

     FILE# FUZ      CHECKPOINT_CHANGE#
---------- --- -----------------------
         1 NO           11000524225166
         3 NO           11000524225166
         4 NO           11000524225167
         5 NO           11000524225166
         6 NO           11000524225166
         7 NO           11000524225166
         8 NO           11000524225166
         9 NO           11000524225166
        10 NO           11000524225166
        11 NO           11000524225166

10 rows selected.

SQL> alter database backup controlfile to trace noresetlogs;

Database altered.

cp /lank/db/lank/control01.ctl /lank/db/lank/control01.ctl_bak

重建控制文件,并进行数据库recover

SQL> recover database;
Media recovery complete.
SQL> select file#,fuzzy,checkpoint_change# from v$datafile_header;

     FILE# FUZ      CHECKPOINT_CHANGE#
---------- --- -----------------------
         1 NO           11000524225167
         3 NO           11000524225167
         4 NO           11000524225167
         5 NO           11000524225167
         6 NO           11000524225167
         7 NO           11000524225167
         8 NO           11000524225167
         9 NO           11000524225167
        10 NO           11000524225167
        11 NO           11000524225167

10 rows selected.

SQL> select checkpoint_change#,last_change# from v$datafile;

     CHECKPOINT_CHANGE#          LAST_CHANGE#
----------------------- ---------------------
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167
         11000524225167        11000524225167

10 rows selected.

SQL>
SQL> alter database open;

Database altered.
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics