ITPub博客

首页 > 数据库 > Oracle > 理Oracle 手工恢复中的‘ORA-00600: internal error code, arguments: 错误

理Oracle 手工恢复中的‘ORA-00600: internal error code, arguments: 错误

原创 Oracle 作者:宋祖强 时间:2015-07-29 10:24:07 0 删除 编辑
1、错误场景,做完全恢复(归档模式),system 表空间出错

[oracle@cuugbgy ~]$!sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 16 11:01:25 2012
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             996125
         2             996125
         3             996125
         4             996125
         5             996125
         6             996125
6 rows selected.

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             994105
         2             996125
         3             996125
         4             996125
         5             996125
         6             996125
6 rows selected.

SQL> recover datafile 1;
ORA-00279: change 994105 generated at 03/16/2012 08:51:01 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_19_776098171.log
ORA-00280: change 994105 for thread 1 is in sequence #19

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 994744 generated at 03/16/2012 09:10:00 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_20_776098171.log
ORA-00280: change 994744 for thread 1 is in sequence #20
ORA-00278: log file '/disk1/arch/prod/arch_1_19_776098171.log' no longer needed for this recovery

ORA-00279: change 996065 generated at 03/16/2012 09:41:17 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_21_776098171.log
ORA-00280: change 996065 for thread 1 is in sequence #21
ORA-00278: log file '/disk1/arch/prod/arch_1_20_776098171.log' no longer needed for this recovery

ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [1], [498], [1], [23], [378], [16], []
ORA-10567: Redo is inconsistent with data block (file# 1, block# 498)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 68
ORA-01112: media recovery not started

2、recover 失败,检查controlfile 和 datafile 信息

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             996125
         2             996125
         3             996125
         4             996125
         5             996125
         6             996125
6 rows selected.

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             996125
         2             996125
         3             996125
         4             996125
         5             996125
         6             996125
6 rows selected.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                        FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- -------------------
         2          1         23   52428800          2 NO        CURRENT                                                 996125 2012-03-16 09:43:33
         1          1         22   52428800          2 YES       INACTIVE                                                996105 2012-03-16 09:42:39
----打开数据库失败

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'

------查看磁盘空间,不是空间问题,呵呵

SQL> !
[oracle@cuugbgy ~]$cp /disk1/backup/prod/cold_bak/*.dbf /u01/app/oracle/oradata/prod/
[oracle@cuugbgy ~]$df -h
Filesystem             size   used  avail capacity  Mounted on
/dev/dsk/c0t0d0s0       19G   5.2G    14G    28%    /
/devices                 0K     0K     0K     0%    /devices
ctfs                     0K     0K     0K     0%    /system/contract
proc                     0K     0K     0K     0%    /proc
mnttab                   0K     0K     0K     0%    /etc/mnttab
swap                   1.8G   988K   1.8G     1%    /etc/svc/volatile
objfs                    0K     0K     0K     0%    /system/object
sharefs                  0K     0K     0K     0%    /etc/dfs/sharetab
/usr/lib/libc/libc_hwcap1.so.1
                        19G   5.2G    14G    28%    /lib/libc.so.1
fd                       0K     0K     0K     0%    /dev/fd
swap                   1.8G    84K   1.8G     1%    /tmp
swap                   1.8G    28K   1.8G     1%    /var/run
/dev/dsk/c0t2d0s3      8.9G   9.1M   8.8G     1%    /disk3
/dev/dsk/c0t2d0s1      4.9G   122M   4.8G     3%    /disk2
/dev/dsk/c0t2d0s0      4.9G   2.0G   2.9G    42%    /disk1
/dev/dsk/c0t0d0s3       15G   4.7G   9.7G    33%    /u01
/dev/dsk/c0t0d0s7       14G   716M    14G     5%    /export/home

3、重新进行recover(restore 数据文件)

[oracle@cuugbgy ~]$!sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 16 11:04:35 2012
copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             994105
         2             994105
         3             994105
         4             994105
         5             994105
         6             994105
6 rows selected.

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             996125
         2             996125
         3             996125
         4             996125
         5             996125
         6             996125
6 rows selected.

-------做不完全恢复

SQL> recover database until cancel;
ORA-00279: change 994105 generated at 03/16/2012 08:51:01 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_19_776098171.log
ORA-00280: change 994105 for thread 1 is in sequence #19

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 994744 generated at 03/16/2012 09:10:00 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_20_776098171.log
ORA-00280: change 994744 for thread 1 is in sequence #20
ORA-00278: log file '/disk1/arch/prod/arch_1_19_776098171.log' no longer needed for this recovery

ORA-00279: change 996065 generated at 03/16/2012 09:41:17 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_21_776098171.log
ORA-00280: change 996065 for thread 1 is in sequence #21
ORA-00278: log file '/disk1/arch/prod/arch_1_20_776098171.log' no longer needed for this recovery

ORA-00279: change 996105 generated at 03/16/2012 09:42:39 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_22_776098171.log
ORA-00280: change 996105 for thread 1 is in sequence #22
ORA-00278: log file '/disk1/arch/prod/arch_1_21_776098171.log' no longer needed for this recovery

ORA-00279: change 996125 generated at 03/16/2012 09:43:33 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_23_776098171.log
ORA-00280: change 996125 for thread 1 is in sequence #23
ORA-00278: log file '/disk1/arch/prod/arch_1_22_776098171.log' no longer needed for this recovery

ORA-00308: cannot open archived log '/disk1/arch/prod/arch_1_23_776098171.log'
ORA-27037: unable to obtain file status
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3

------利用当前日志组进行恢复

[oracle@cuugbgy prod]$!sql
sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 16 11:06:29 2012
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                         FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------                      ------------- -------------------
         2          1         23   52428800          2 NO        CURRENT                                                 996125 2012-03-16 09:43:33
         1          1         22   52428800          2 YES       INACTIVE                                                996105 2012-03-16 09:42:39

SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/redo02.log
/u01/app/oracle/oradata/prod/redo01.log
/u01/app/oracle/oradata/prod/redo01a.log
/u01/app/oracle/oradata/prod/redo02a.log

SQL> recover database until cancel;
ORA-00279: change 996125 generated at 03/16/2012 09:43:33 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_23_776098171.log
ORA-00280: change 996125 for thread 1 is in sequence #23

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/prod/redo02.log    贴出当前日志的绝对路径

ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [1], [498], [4194802], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 1, block# 498)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 68

ORA-01112: media recovery not started

------恢复报错,试着用resetlogs 打开库

SQL> alter database open resetlogs;

Database altered.

-------数据库被打开

SQL> select status from v$instance;

STATUS
------------------------------------
OPEN

SQL> select * from scott.emp1;


     EMPNO ENAME                          JOB                                MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH                          CLERK                             7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN                          SALESMAN                          7698 1981-02-20 00:00:00       1600        300         30
      7369 SMITH                          CLERK                             7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN                          SALESMAN                          7698 1981-02-20 00:00:00       1600        300         30
      7369 SMITH                          CLERK                             7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN                          SALESMAN                          7698 1981-02-20 00:00:00       1600        300         30
      7369 SMITH                          CLERK                             7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN                          SALESMAN                          7698 1981-02-20 00:00:00       1600        300         30

8 rows selected.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                        FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- -------------------
         1          1          0   52428800          2 YES       UNUSED                                                       0
         2          1          1   52428800          2 NO        CURRENT                                                 996126 2012-03-16 11:08:36

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- -------------------
         1          1          2   52428800          2 YES       INACTIVE                                                996426 2012-03-16 11:12:40
         2          1          3   52428800          2 NO        CURRENT                                                 996430 2012-03-16 11:12:47

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

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

注册时间:2014-08-13

  • 博文量
    176
  • 访问量
    277122