UNDO 表空间错误，无法OPEN DATABASE

farmer111

UNDO 表空间错误，无法OPEN DATABASE

　　应用环境： Solaris 10（X86） +    Oracle 10g
错误现象：
数据库在启动时，出现以下错误信息：
Errors in file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:39:36 2011
DEBUG: Replaying xcb 0x588c40e4, pmd 0x58a54c7c for failed op 8
Doing block recovery for file 2 block 2116 -------------文件2 错误
No block recovery was needed
Tue Dec 13 17:40:05 2011
Errors in file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:40:22 2011
Errors in file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
以下内容选自告警日志：
Starting ORACLE instance (normal)
......
  processes                = 150
  __shared_pool_size       = 75497472
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  sga_target               = 167772160
  control_files            = /export/home/oracle/orcl/oradata/cuug/control01.ctl, /export/home/oracle/orcl/oradata/cuug/control02.ctl, /export/home/oracle/orcl/oradata/cuug/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 79691776
  compatible               = 10.2.0.2.0
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /export/home/oracle/orcl/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=cuugXDB)
  job_queue_processes      = 10
  background_dump_dest     = /export/home/oracle/orcl/admin/cuug/bdump
  user_dump_dest           = /export/home/oracle/orcl/admin/cuug/udump
  core_dump_dest           = /export/home/oracle/orcl/admin/cuug/cdump
  audit_file_dest          = /export/home/oracle/orcl/admin/cuug/adump
  db_name                  = cuug
  open_cursors             = 300
  pga_aggregate_target     = 16777216
......
Completed: ALTER DATABASE   MOUNT
Tue Dec 13 17:38:47 2011
ALTER DATABASE RECOVER  datafile 2  
Tue Dec 13 17:38:48 2011
Media Recovery Start
SMON: enabling cache recovery
ARC2 started with pid=18, OS id=1982
Tue Dec 13 17:39:11 2011
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
fTue Dec 13 17:39:22 2011
Successfully onlined Undo Tablespace 1.
Tue Dec 13 17:39:22 2011
SMON: enabling tx recovery
Tue Dec 13 17:39:23 2011
Database Characterset is ZHS16GBK
Tue Dec 13 17:39:32 2011
Errors in file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:39:36 2011
DEBUG: Replaying xcb 0x588c40e4, pmd 0x58a54c7c for failed op 8
Doing block recovery for file 2 block 2116                  -------------文件2 错误
No block recovery was needed
Tue Dec 13 17:40:05 2011
Errors in file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:40:22 2011
Errors in file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:40:22 2011
DEBUG: Replaying xcb 0x588c40e4, pmd 0x58a54c7c for failed op 8
Doing block recovery for file 2 block 2116
No block recovery was needed
Tue Dec 13 17:40:53 2011
Errors in file /export/home/oracle/orcl/admin/cuug/bdump/cuug_smon_1945.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:40:56 2011
DEBUG: Replaying xcb 0x588c40e4, pmd 0x58a54c7c for failed op 8
Tue Dec 13 17:40:56 2011
Non-fatal internal error happenned while SMON was doing shrinking of rollback segments.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Tue Dec 13 17:40:56 2011
Doing block recovery for file 2 block 2116
No block recovery was needed
Tue Dec 13 17:41:32 2011
Errors in file /export/home/oracle/orcl/admin/cuug/bdump/cuug_pmon_1933.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:41:37 2011
Errors in file /export/home/oracle/orcl/admin/cuug/bdump/cuug_pmon_1933.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:41:37 2011
PMON: terminating instance due to error 472
Instance terminated by PMON, pid = 1933
--------Oracle 内部错误，无法正常open  database
启动数据库到mount 状态：

----------查看数据文件，发现file 2 是undo datafile
SQL> select checkpoint_change# ,file# from v$datafile;
CHECKPOINT_CHANGE#      FILE#
------------------ ----------
            785377          1
            785377          2
            785377          3
            785377          4
            785377          5
SQL> select checkpoint_change# ,file# from v$datafile_header;
CHECKPOINT_CHANGE#      FILE#
------------------ ----------
            785377          1
            785377          2
            785377          3
            785377          4
            785377          5
SQL> col name for a50
SQL> select file# ,name from v$datafile;
     FILE# NAME
---------- --------------------------------------------------
         1 /export/home/oracle/orcl/oradata/cuug/system01.dbf
         2 /export/home/oracle/orcl/oradata/cuug/undotbs01.dbf
         3 /export/home/oracle/orcl/oradata/cuug/sysaux01.dbf
         4 /export/home/oracle/orcl/oradata/cuug/users01.dbf
         5 /export/home/oracle/orcl/oradata/cuug/example01.dbf

SQL> show parameter undo
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
undo_management                      string
AUTO
undo_retention                       integer
900
undo_tablespace                      string
UNDOTBS1

---------设置undo management 为手工方式（manual）
SQL> alter system set undo_management = manual scope=spfile;
System altered.
SQL> alter system set undo_tablespace='' scope=spfile;
System altered.
SQL> startup force mount;
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size              83889008 bytes
Database Buffers           79691776 bytes
Redo Buffers                2912256 bytes
Database mounted.
----------正常打开库
SQL>alter database open;
Database altered.
-----------添加新的undo 表空间
SQL> create undo tablespace rtbs
  2   datafile '/export/home/oracle/orcl/oradata/cuug/rtbs01.dbf' size 100m;
Tablespace created.
SQL> select file_id,file_name,tablespace_name from dba_data_files;
   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         4 /export/home/oracle/orcl/oradata/cuug/users01.dbf  USERS
         3 /export/home/oracle/orcl/oradata/cuug/sysaux01.dbf SYSAUX
         2 /export/home/oracle/orcl/oradata/cuug/undotbs01.dbf UNDOTBS1                     
         1 /export/home/oracle/orcl/oradata/cuug/system01.dbf SYSTEM
         5 /export/home/oracle/orcl/oradata/cuug/example01.dbf EXAMPLE
          6 /export/home/oracle/orcl/oradata/cuug/rtbs01.dbf   RTBS
6 rows selected.
SQL> alter system set undo_management =auto scope=spfile;
System altered.
SQL> alter system set undo_tablespace = rtbs scope=spfile;
System altered.
----------重新启动数据库，正常后，删除旧的undo tablespace
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
----------重新启动数据库，数据库恢复正常

SQL> startup force
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size              83889008 bytes
Database Buffers           79691776 bytes
Redo Buffers                2912256 bytes
Database mounted.
Database opened.
SQL> col file_name for a50
SQL> select file_id,file_name ,tablespace_name from dba_data_files;
   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         4 /export/home/oracle/orcl/oradata/cuug/users01.dbf  USERS
         3 /export/home/oracle/orcl/oradata/cuug/sysaux01.dbf SYSAUX
         1 /export/home/oracle/orcl/oradata/cuug/system01.dbf SYSTEM
         5 /export/home/oracle/orcl/oradata/cuug/example01.dbf EXAMPLE
         6 /export/home/oracle/orcl/oradata/cuug/rtbs01.dbf   RTBS
SQL> show parameter undo
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
undo_management                      string                            AUTO
undo_retention                       integer                           900
undo_tablespace                      string                                                    rtbs