Archive 2011年2月17日

ORA-00600: internal error code, arguments: [4194]

留下评论

在修改_allow_resetlogs_corruption参数为TRUE，并强制打开数据库后，遭遇下述错误信息： 该错误信息来自alert告警日志文件：

Thu Feb 17 14:07:30 2011
Errors in file /u01/app/admin/orcl/bdump/orcl_smon_26850.trc:
ORA-00600: internal error code, arguments: [4194], [41], [31], [], [], [], [], []

通常ORA-00600 4194级的错误是跟回滚段错误相关！ 继续查看TRACE文件（/u01/app/admin/orcl/bdump/orcl_smon_26850.trc） 看到有下述相关信息：

 Acq rbs _SYSSMU1$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 1 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU2$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 2 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU3$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 3 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU4$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 4 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU5$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 5 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU6$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 6 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU7$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 7 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU8$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 8 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU9$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 9 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU10$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 10 Onlined

Google之，获取相关解决办法： ① 启动数据库，获取回滚段信息：

SYS@orcl > conn / as sysdba;
Connected to an idle instance.
SYS@orcl > startup
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
SYS@orcl > select * from v$rollname;
ERROR:
ORA-03114: not connected to ORACLE
SYS@orcl > conn / as sysdba;
Connected.
SYS@orcl > startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SYS@orcl > conn / as sysdba;
Connected.
SYS@orcl > select * from V$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
         1 _SYSSMU1$
         2 _SYSSMU2$
         3 _SYSSMU3$
         4 _SYSSMU4$
         5 _SYSSMU5$
         6 _SYSSMU6$
         7 _SYSSMU7$
         8 _SYSSMU8$
         9 _SYSSMU9$
        10 _SYSSMU10$
11 rows selected.
SYS@orcl >

此时，数据库虽可以使用，但是还是有问题的，过一段儿时间，莫名其妙的宕机！！！再次尝试启动数据库的时候，却非常正常，并未报错！！！

SYS@orcl > startup
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
SEGMENT_NAME
------------------------------
_SYSSMU11$
12 rows selected.
SYS@orcl > select segment_name from dba_rollback_segs;
select segment_name from dba_rollback_segs
*
ERROR at line 1:
ORA-03135: connection lost contact
SYS@orcl >

② 开始通过调整参数的方式，来修复数据库：

SYS@orcl > conn / as sysdba;
Connected to an idle instance.
SYS@orcl > create pfile from spfile;
File created.
SYS@orcl >

修改pfile，在该文件中，添加如下信息：

[oracle@rhel10g dbs]$ tail -2  /u01/app/oracle/dbs/initorcl.ora
*.undo_management='MANUAL'
_corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$',
'_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$',
'_SYSSMU9$','_SYSSMU10$','_SYSSMU11$'

这两个参数表示，回滚段手工管理，并设置_corrupted_rollback_segments后面的那11个回滚段为损坏状态！ ③ 然后，用该PFILE来启动数据库：

SYS@orcl > startup pfile=/u01/app/oracle/dbs/initorcl.ora
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select * from V$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
SYS@orcl > select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
SEGMENT_NAME
------------------------------
_SYSSMU11$
12 rows selected.

数据库正常启动，并未像刚才的那样，莫名其妙的“死”掉！！！但是，数据看出现下述症状，普通用户在数据库内无法做事务操作，而SYS用户却可以。

SYS@orcl > select count(*) from m;
  COUNT(*)
----------
       176
SYS@orcl > insert into m select * from m;
176 rows created.
SYS@orcl >

上述看到，SYS用户正常做操作。而HR用户执行事务就报错：ORA-01552！

SYS@orcl > conn hr/hr;
Connected.
HR@orcl > select count(*) from t;
  COUNT(*)
----------
       214
HR@orcl > insert into t select * from t;
insert into t select * from t
            *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
HR@orcl >

④ 删除，并重建UNDO表空间：

SYS@orcl > conn / as sysdba;
Connected.
SYS@orcl > show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SYS@orcl > col file_name for a40
SYS@orcl > col tablespace_name for a15
SYS@orcl > set line 120
SYS@orcl > select file_name,tablespace_name from dba_data_files;
FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ---------------
/u01/app/oradata/orcl/users01.dbf        USERS
/u01/app/oradata/orcl/sysaux01.dbf       SYSAUX
/u01/app/oradata/orcl/undotbs1.dbf       UNDOTBS1
/u01/app/oradata/orcl/system01.dbf       SYSTEM
/u01/app/oradata/orcl/example01.dbf      EXAMPLE
SYS@orcl > drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SYS@orcl > select file_name,tablespace_name from dba_data_files;
FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ---------------
/u01/app/oradata/orcl/users01.dbf        USERS
/u01/app/oradata/orcl/sysaux01.dbf       SYSAUX
/u01/app/oradata/orcl/system01.dbf       SYSTEM
/u01/app/oradata/orcl/example01.dbf      EXAMPLE
SYS@orcl > create undo tablespace undotbs1 datafile '/u01/app/oradata/orcl/undotbs1.dbf' size 50m;
Tablespace created.
SYS@orcl >

⑤ 关闭实例，生成新的参数文件：

SYS@orcl > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl >

⑥ 修改PFILE，这次要去掉_corrupted_rollback_segments参数的设置，并将undo_management改回为AUTO：

[oracle@rhel10g ~]$ tail -3 /u01/app/oracle/dbs/initorcl.ora
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/admin/orcl/udump'
*.undo_management='auto'

⑦ 然后创建SPFILE：

SYS@orcl > create spfile from pfile;
File created.
SYS@orcl > !strings /u01/app/oracle/dbs/spfileorcle.ora | grep undo
strings: '/u01/app/oracle/dbs/spfileorcle.ora': No such file
SYS@orcl > !strings /u01/app/oracle/dbs/spfileorcl.ora | grep undo
*.undo_management='auto'
*.undo_tablespace='UNDOTBS1'
SYS@orcl >

⑧ 用新SPFILE启动数据库，并做校验：

SYS@orcl > startup
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select count(*) from m;
  COUNT(*)
----------
       352
SYS@orcl > select * from v$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
         1 _SYSSMU1$
         2 _SYSSMU2$
         3 _SYSSMU3$
         4 _SYSSMU4$
         5 _SYSSMU5$
         6 _SYSSMU6$
         7 _SYSSMU7$
         8 _SYSSMU8$
         9 _SYSSMU9$
        10 _SYSSMU10$
11 rows selected.
SYS@orcl > select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
11 rows selected.
SYS@orcl > select count(*) from m;
  COUNT(*)
----------
       352
SYS@orcl > conn hr/hr;
Connected.
HR@orcl > select count(*) from t;
  COUNT(*)
----------
       214
HR@orcl > insert into t select * from t;
214 rows created.
HR@orcl > commit;
Commit complete.
HR@orcl > select count(*) from t;
  COUNT(*)
----------
       428
HR@orcl >

运行一段时间，发现数据库并无其它异常。至此，解决ORA-00600 4194的错误！

Oracle _disable_logging and _allow_resetlogs_corruption

留下评论

本文简单研究oracle隐含参数_disable_logging与_allow_resetlogs_corruption两个参数。
仅作学习测试用，实际情况需慎重，做好数据库的备份，以防丢失数据。
1 启动数据库，获取相关信息：

SYS@orcl > startup
ORACLE instance started.

Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             167772528 bytes
Database Buffers          331350016 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/dbs/spfileorcl
                                                 .ora
SYS@orcl > show parameter _disable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_disable_logging                     boolean     FALSE
SYS@orcl > select count(*) from m;

  COUNT(*)
----------
        44

SYS@orcl > select * from V$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          0   52428800          1 YES UNUSED                       0
         2          1          0   52428800          1 YES UNUSED                       0
         3          1          1   52428800          1 NO  CURRENT                1098823 17-FEB-11

可以看到，当前数据库使用SPFILE参数文件，_disable_logging参数为FALSE，测试表m有44条记录，当前日志组为第3组。
2修改隐含参数_disable_logging为TRUE，并重启数据库使之生效：

SYS@orcl > alter system set "_disable_logging"=true scope=spfile;

System altered.

SYS@orcl > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl > startup;
ORACLE instance started.

Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             167772528 bytes
Database Buffers          331350016 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > show parameter _disable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_disable_logging                     boolean     TRUE
SYS@orcl > select * from V$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          0   52428800          1 YES UNUSED                       0
         2          1          0   52428800          1 YES UNUSED                       0
         3          1          1   52428800          1 NO  CURRENT                1098823 17-FEB-11

SYS@orcl > select count(*) from m;

  COUNT(*)
----------
        44

SYS@orcl > insert into m select * from m;

44 rows created.

SYS@orcl > commit;

Commit complete.

SYS@orcl > select * from V$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          0   52428800          1 YES UNUSED                       0
         2          1          0   52428800          1 YES UNUSED                       0
         3          1          1   52428800          1 NO  CURRENT                1098823 17-FEB-11

插入44条记录，这44条记录写入当前组第3组日志文件中的。

SYS@orcl > alter system switch logfile;

System altered.

SYS@orcl > select * from V$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          2   52428800          1 NO  CURRENT                1101521 17-FEB-11
         2          1          0   52428800          1 YES UNUSED                       0
         3          1          1   52428800          1 NO  ACTIVE                 1098823 17-FEB-11

SYS@orcl > select count(*) from m;

  COUNT(*)
----------
        88

SYS@orcl > insert into m select * from m;

88 rows created.

SYS@orcl > commit;

Commit complete.

SYS@orcl > select count(*) from m;

  COUNT(*)
----------
       176

SYS@orcl > select * from V$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          2   52428800          1 NO  CURRENT                1101521 17-FEB-11
         2          1          0   52428800          1 YES UNUSED                       0
         3          1          1   52428800          1 NO  ACTIVE                 1098823 17-FEB-11

SYS@orcl > alter system checkpoint;

System altered.
SYS@orcl > select * from V$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          2   52428800          1 NO  CURRENT                1101521 17-FEB-11
         2          1          0   52428800          1 YES UNUSED                       0
         3          1          1   52428800          1 NO  INACTIVE               1098823 17-FEB-11

SYS@orcl >

向m表插入记录（此时共有176条记录），做完日志切换，并做检查点，看到第三组状态有ACTIVE变为INACTIVE，说明检查点完成。
3 强制启动数据库FORCE：

SYS@orcl > startup force;
ORACLE instance started.

Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             167772528 bytes
Database Buffers          331350016 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select count(*) from m;

  COUNT(*)
----------
       176

SYS@orcl > select * from V$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          2   52428800          1 NO  INACTIVE               1101521 17-FEB-11
         2          1          3   52428800          1 NO  CURRENT                1121523 17-FEB-11
         3          1          1   52428800          1 NO  INACTIVE               1098823 17-FEB-11

看到数据库一切正常，且M表中的176条记录完好。
4 继续插入测试数据，又插入176条记录，这176条记录对应的日志是在第2组的。

SYS@orcl > insert into m select * from m;

176 rows created.

SYS@orcl > alter system switch logfile;

System altered.

SYS@orcl > select * from V$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          2   52428800          1 NO  INACTIVE               1101521 17-FEB-11
         2          1          3   52428800          1 NO  ACTIVE                 1121523 17-FEB-11
         3          1          4   52428800          1 NO  CURRENT                1121706 17-FEB-11

SYS@orcl > select count(*) from m;

  COUNT(*)
----------
       352

SYS@orcl > insert into m select * from m;

352 rows created.

SYS@orcl > select * from V$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          2   52428800          1 NO  INACTIVE               1101521 17-FEB-11
         2          1          3   52428800          1 NO  ACTIVE                 1121523 17-FEB-11
         3          1          4   52428800          1 NO  CURRENT                1121706 17-FEB-11

SYS@orcl > startup force;
ORACLE instance started.

Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             167772528 bytes
Database Buffers          331350016 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 3 change 1121523 time 02/17/2011 11:45:37
ORA-00312: online log 2 thread 1: '/u01/app/oradata/orcl/redo02.log'

SYS@orcl > select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          2   52428800          1 NO  INACTIVE               1101521 17-FEB-11
         3          1          4   52428800          1 NO  CURRENT                1121706 17-FEB-11
         2          1          3   52428800          1 NO  ACTIVE                 1121523 17-FEB-11

不做检查点，FORCE强制启动数据库，发现报错！！！第2个日志组需要恢复。为什么呢，该怎么恢复呢？
5 修改另外一个隐含参数_allow_resetlogs_corruption为TRUE，并做恢复：

SYS@orcl > show parameter _allow

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption          boolean     FALSE
SYS@orcl > alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SYS@orcl > startup force;
ORACLE instance started.

Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             167772528 bytes
Database Buffers          331350016 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 3 change 1121523 time 02/17/2011 11:45:37
ORA-00312: online log 2 thread 1: '/u01/app/oradata/orcl/redo02.log'

SYS@orcl > alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SYS@orcl > recover database using backup controlfile until cancel;
ORA-00279: change 1121524 generated at 02/17/2011 11:45:37 needed for thread 1
ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL/
archivelog/2011_02_17/o1_mf_1_3_%u_.arc
ORA-00280: change 1121524 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/u01/app/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_3_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

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: '/u01/app/oradata/orcl/system01.dbf'

SYS@orcl > alter database open resetlogs;

Database altered.

SYS@orcl > select count(*) from m;

  COUNT(*)
----------
       176

SYS@orcl >

6 结果，数据库不完全恢复成功，但是丢失数据了。那么又丢失了哪些数据呢？
结论：①_disable_logging参数是禁止数据库产生日志的。可以看到，在日志组为ACTIVE状态下（检查点未完成），startup force时，该日志组需要恢复。也就是为什么做过检查点（alter system checkpoint）后，startup force时，数据还在，而未做检查点时，数据丢失；
②_allow_resetlogs_corruption参数允许在特定情况下将数据库强制打开，不做校验；
③本测试环境：数据库非归档模式，未做任何备份；

SYS@orcl > !uname -a
Linux rhel10g 2.6.18-164.el5xen #1 SMP Tue Aug 18 16:06:30 EDT 2009 i686 athlon i386 GNU/Linux

SYS@orcl > select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SYS@orcl >