今天早上过来,发现一台数据库的实例没有起来,于是进行了启动操作,但是奇怪的是启动正常进行,在启动完成后的几秒钟内系统崩溃,进程也没有了,在进入sys下又可以重启,启动过程一切正常,但是现象也同样。查看alert.log后发现如下信息:
Sun Feb
25
10
:
49
:
28
2007
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE = BR
ILAT = 18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0 .
System parameters with non-default values:
processes = 150
__shared_pool_size = 117440512
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
nls_language = AMERICAN
sga_target = 599785472
control_files = /home/u01/app/oracle/oradata/orcl/control01.ctl , /home/u01/app/oracle/oradata/orcl/control02.ctl , /home/u01/app/oracle/oradata/orcl/control03.ctl
db_block_size = 8192
__db_cache_size = 465567744
compatible = 10.2.0.1.0
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE = BR
ILAT = 18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0 .
System parameters with non-default values:
processes = 150
__shared_pool_size = 117440512
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
nls_language = AMERICAN
sga_target = 599785472
control_files = /home/u01/app/oracle/oradata/orcl/control01.ctl , /home/u01/app/oracle/oradata/orcl/control02.ctl , /home/u01/app/oracle/oradata/orcl/control03.ctl
db_block_size = 8192
__db_cache_size = 465567744
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = /home/u01/app/oracle/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=orclXDB)
job_queue_processes = 10
background_dump_dest = /home/u01/app/oracle/admin/orcl/bdump
user_dump_dest = /home/u01/app/oracle/admin/orcl/udump
core_dump_dest = /home/u01/app/oracle/admin/orcl/cdump
audit_file_dest = /home/u01/app/oracle/admin/orcl/adump
db_name = orcl
open_cursors = 300
pga_aggregate_target = 199229440
PMON started with pid=2, OS id=15504
MMAN started with pid=4, OS id=15508
PSP0 started with pid=3, OS id=15506
DBW0 started with pid=5, OS id=15510
LGWR started with pid=6, OS id=15512
CKPT started with pid=7, OS id=15514
SMON started with pid=8, OS id=15516
RECO started with pid=9, OS id=15518
CJQ0 started with pid=10, OS id=15520
MMON started with pid=11, OS id=15522
Sun Feb 25 10:49:32 2007
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=15524
Sun Feb 25 10:49:32 2007
starting up 1 shared server(s) ...
db_recovery_file_dest = /home/u01/app/oracle/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=orclXDB)
job_queue_processes = 10
background_dump_dest = /home/u01/app/oracle/admin/orcl/bdump
user_dump_dest = /home/u01/app/oracle/admin/orcl/udump
core_dump_dest = /home/u01/app/oracle/admin/orcl/cdump
audit_file_dest = /home/u01/app/oracle/admin/orcl/adump
db_name = orcl
open_cursors = 300
pga_aggregate_target = 199229440
PMON started with pid=2, OS id=15504
MMAN started with pid=4, OS id=15508
PSP0 started with pid=3, OS id=15506
DBW0 started with pid=5, OS id=15510
LGWR started with pid=6, OS id=15512
CKPT started with pid=7, OS id=15514
SMON started with pid=8, OS id=15516
RECO started with pid=9, OS id=15518
CJQ0 started with pid=10, OS id=15520
MMON started with pid=11, OS id=15522
Sun Feb 25 10:49:32 2007
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=15524
Sun Feb 25 10:49:32 2007
starting up 1 shared server(s) ...
Sun Feb 25 10:49:33 2007
ALTER DATABASE MOUNT
Sun Feb 25 10:49:38 2007
Setting recovery target incarnation to 2
Sun Feb 25 10:49:38 2007
Successful mount of redo thread 1, with mount id 1143260989
Sun Feb 25 10:49:38 2007
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Sun Feb 25 10:49:38 2007
ALTER DATABASE OPEN
Sun Feb 25 10:49:38 2007
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Sun Feb 25 10:49:38 2007
Started redo scan
Sun Feb 25 10:49:38 2007
Completed redo scan
2215 redo blocks read, 56 data blocks need recovery
Sun Feb 25 10:49:39 2007
Started redo application at
Thread 1: logseq 1050, block 92122
Sun Feb 25 10:49:39 2007
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1050 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo02.log
Sun Feb 25 10:49:39 2007
Recovery of Online Redo Log: Thread 1 Group 3 Seq 1051 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo03.log
Sun Feb 25 10:49:39 2007
Completed redo application
Sun Feb 25 10:49:39 2007
ALTER DATABASE MOUNT
Sun Feb 25 10:49:38 2007
Setting recovery target incarnation to 2
Sun Feb 25 10:49:38 2007
Successful mount of redo thread 1, with mount id 1143260989
Sun Feb 25 10:49:38 2007
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Sun Feb 25 10:49:38 2007
ALTER DATABASE OPEN
Sun Feb 25 10:49:38 2007
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Sun Feb 25 10:49:38 2007
Started redo scan
Sun Feb 25 10:49:38 2007
Completed redo scan
2215 redo blocks read, 56 data blocks need recovery
Sun Feb 25 10:49:39 2007
Started redo application at
Thread 1: logseq 1050, block 92122
Sun Feb 25 10:49:39 2007
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1050 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo02.log
Sun Feb 25 10:49:39 2007
Recovery of Online Redo Log: Thread 1 Group 3 Seq 1051 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo03.log
Sun Feb 25 10:49:39 2007
Completed redo application
Sun Feb 25 10:49:39 2007
Completed crash recovery at
Thread 1: logseq 1051, block 1174, scn 4827420640
56 data blocks read, 30 data blocks written, 2215 redo blocks read
Sun Feb 25 10:49:39 2007
Thread 1 advanced to log sequence 1052
Thread 1 opened at log sequence 1052
Current log# 1 seq# 1052 mem# 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Successful open of redo thread 1
Sun Feb 25 10:49:39 2007
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Feb 25 10:49:39 2007
SMON: enabling cache recovery
Sun Feb 25 10:49:41 2007
Successfully onlined Undo Tablespace 1.
Sun Feb 25 10:49:41 2007
SMON: enabling tx recovery
Sun Feb 25 10:49:41 2007
Database Characterset is UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=15538
Sun Feb 25 10:49:49 2007
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sun Feb 25 10:49:49 2007
Completed: ALTER DATABASE OPEN
Thread 1: logseq 1051, block 1174, scn 4827420640
56 data blocks read, 30 data blocks written, 2215 redo blocks read
Sun Feb 25 10:49:39 2007
Thread 1 advanced to log sequence 1052
Thread 1 opened at log sequence 1052
Current log# 1 seq# 1052 mem# 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Successful open of redo thread 1
Sun Feb 25 10:49:39 2007
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Feb 25 10:49:39 2007
SMON: enabling cache recovery
Sun Feb 25 10:49:41 2007
Successfully onlined Undo Tablespace 1.
Sun Feb 25 10:49:41 2007
SMON: enabling tx recovery
Sun Feb 25 10:49:41 2007
Database Characterset is UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=15538
Sun Feb 25 10:49:49 2007
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sun Feb 25 10:49:49 2007
Completed: ALTER DATABASE OPEN
Sun Feb 25 10:49:53 2007
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_j002_15544.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Sun Feb 25 10:49:55 2007
Doing block recovery for file 2 block 6132
Block recovery from logseq 1052, block 136 to scn 4827422434
Sun Feb 25 10:49:55 2007
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1052 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Block recovery completed at rba 1052.5592.16, scn 1.532455139
Doing block recovery for file 2 block 41
Block recovery from logseq 1052, block 75 to scn 4827420772
Sun Feb 25 10:49:55 2007
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1052 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Block recovery completed at rba 1052.136.16, scn 1.532453477
Sun Feb 25 10:49:57 2007
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_j000_15540.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Sun Feb 25 10:49:58 2007
DEBUG: Replaying xcb 0x4261f68c, pmd 0x427dde28 for failed op 8
Doing block recovery for file 2 block 6132
Block recovery from logseq 1052, block 136 to scn 4827422434
Sun Feb 25 10:49:58 2007
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1052 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Block recovery completed at rba 1052.5592.16, scn 1.532455139
Sun Feb 25 10:50:02 2007
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_j000_15540.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_j000_15540.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Sun Feb 25 10:50:13 2007
DEBUG: Replaying xcb 0x4261f68c, pmd 0x427dde28 for failed op 8
Doing block recovery for file 2 block 6132
Block recovery from logseq 1052, block 136 to scn 4827422434
Sun Feb 25 10:50:13 2007
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1052 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Block recovery completed at rba 1052.5592.16, scn 1.532455139
Sun Feb 25 10:50:15 2007
Thread 1 cannot allocate new log, sequence 1053
Private strand flush not complete
Current log# 1 seq# 1052 mem# 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Sun Feb 25 10:50:18 2007
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_dbw0_15510.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Sun Feb 25 10:50:18 2007
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_dbw0_15510.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Sun Feb 25 10:50:18 2007
DBW0: terminating instance due to error 471
Instance terminated by DBW0, pid = 15510
Sun Feb 25 10:55:31 2007
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_j002_15544.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Sun Feb 25 10:49:55 2007
Doing block recovery for file 2 block 6132
Block recovery from logseq 1052, block 136 to scn 4827422434
Sun Feb 25 10:49:55 2007
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1052 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Block recovery completed at rba 1052.5592.16, scn 1.532455139
Doing block recovery for file 2 block 41
Block recovery from logseq 1052, block 75 to scn 4827420772
Sun Feb 25 10:49:55 2007
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1052 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Block recovery completed at rba 1052.136.16, scn 1.532453477
Sun Feb 25 10:49:57 2007
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_j000_15540.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Sun Feb 25 10:49:58 2007
DEBUG: Replaying xcb 0x4261f68c, pmd 0x427dde28 for failed op 8
Doing block recovery for file 2 block 6132
Block recovery from logseq 1052, block 136 to scn 4827422434
Sun Feb 25 10:49:58 2007
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1052 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Block recovery completed at rba 1052.5592.16, scn 1.532455139
Sun Feb 25 10:50:02 2007
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_j000_15540.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_j000_15540.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Sun Feb 25 10:50:13 2007
DEBUG: Replaying xcb 0x4261f68c, pmd 0x427dde28 for failed op 8
Doing block recovery for file 2 block 6132
Block recovery from logseq 1052, block 136 to scn 4827422434
Sun Feb 25 10:50:13 2007
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1052 Reading mem 0
Mem# 0 errs 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Block recovery completed at rba 1052.5592.16, scn 1.532455139
Sun Feb 25 10:50:15 2007
Thread 1 cannot allocate new log, sequence 1053
Private strand flush not complete
Current log# 1 seq# 1052 mem# 0: /home/u01/app/oracle/oradata/orcl/redo01.log
Sun Feb 25 10:50:18 2007
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_dbw0_15510.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Sun Feb 25 10:50:18 2007
Errors in file /home/u01/app/oracle/admin/orcl/bdump/orcl_dbw0_15510.trc:
ORA-00600: internal error code, arguments: [4194], [42], [41], [], [], [], [], []
Sun Feb 25 10:50:18 2007
DBW0: terminating instance due to error 471
Instance terminated by DBW0, pid = 15510
Sun Feb 25 10:55:31 2007
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
分析日志发现主要报的:
ORA-
00600
: internal error code
,
arguments:
[
4194
]
,
[
42
]
,
[
41
]
,
[]
,
[]
,
[]
,
[]
,
[]
查阅metalink上的相关资料得到如下错误信息:
A mismatch has been detected between Redo records and rollback (Undo)
records.
We are validating the Undo record number relating to the change being
applied against the maximum undo record number recorded in the undo block.
This error is reported when the validation fails.
ARGUMENTS:
Arg [ a ] Maximum Undo record number in Undo block
Arg [ b ] Undo record number from Redo block
IMPACT:
PROCESS FAILURE
POSSIBLE ROLLBACK SEGMENT CORRUPTION
SUGGESTIONS:
This error may indicate a rollback segment corruption.
This may require a recovery from a database backup depending on
the situation.
records.
We are validating the Undo record number relating to the change being
applied against the maximum undo record number recorded in the undo block.
This error is reported when the validation fails.
ARGUMENTS:
Arg [ a ] Maximum Undo record number in Undo block
Arg [ b ] Undo record number from Redo block
IMPACT:
PROCESS FAILURE
POSSIBLE ROLLBACK SEGMENT CORRUPTION
SUGGESTIONS:
This error may indicate a rollback segment corruption.
This may require a recovery from a database backup depending on
the situation.
第一次我使用了recover database until cancel和alter database open resetlogs;进行,结果还是错误。
通过网络上的几个案例查阅后感觉POSSIBLE ROLLBACK SEGMENT CORRUPTION可能性不较大,于是采用了重建UNDO的方法。
第二次操作方法如下:
SQL> startup mount
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 1220772 bytes
Variable Size 125833052 bytes
Database Buffers 465567744 bytes
Redo Buffers 7163904 bytes
Database mounted.
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 1220772 bytes
Variable Size 125833052 bytes
Database Buffers 465567744 bytes
Redo Buffers 7163904 bytes
Database mounted.
SQL> create undo tablespace undotbs02 datafile '/home/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 100m
2 ;
create undo tablespace undotbs02 datafile '/home/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 100m
*
ERROR at line 1:
ORA-01109: database not open
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/u01/app/oracle/oradata/orcl/system01.dbf
/home/u01/app/oracle/oradata/orcl/undotbs01.dbf
/home/u01/app/oracle/oradata/orcl/sysaux01.dbf
/home/u01/app/oracle/oradata/orcl/users01.dbf
SQL> alter database datafile '/home/u01/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1056
Current log sequence 1058
SQL> create undo tablespace undotbs2 datafile '/home/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 100m;
Tablespace created.
SQL> ALTER SYSTEM SET undo_tablespace='UNDOTBS2' ;
System altered.
2 ;
create undo tablespace undotbs02 datafile '/home/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 100m
*
ERROR at line 1:
ORA-01109: database not open
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/u01/app/oracle/oradata/orcl/system01.dbf
/home/u01/app/oracle/oradata/orcl/undotbs01.dbf
/home/u01/app/oracle/oradata/orcl/sysaux01.dbf
/home/u01/app/oracle/oradata/orcl/users01.dbf
SQL> alter database datafile '/home/u01/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1056
Current log sequence 1058
SQL> create undo tablespace undotbs2 datafile '/home/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 100m;
Tablespace created.
SQL> ALTER SYSTEM SET undo_tablespace='UNDOTBS2' ;
System altered.
通过以上操作,数据库就不会出现上面的问题了!