这几天自己笔记本上的10g数据库莫名挂了,启动报错,可能是由于我的本经常重启造成的
System parameters with non-default values:^M
processes = 400^M
cpu_count = 4^M
__shared_pool_size = 88080384^M
__large_pool_size = 4194304^M
__java_pool_size = 4194304^M
__streams_pool_size = 0^M
sga_target = 293601280^M
control_files = D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL^M
db_block_size = 8192^M
__db_cache_size = 188743680^M
compatible = 10.2.0.1.0^M
db_file_multiblock_read_count= 16^M
db_recovery_file_dest = D:\oracle\product\10.2.0\db_1\flash_recovery_area^M
db_recovery_file_dest_size= 2147483648^M
undo_management = AUTO^M
undo_tablespace = UNDOTBS1^M
recyclebin = OFF^M
remote_login_passwordfile= EXCLUSIVE^M
db_domain = ^M
dispatchers = (PROTOCOL=TCP) (SERVICE=orclXDB)^M
job_queue_processes = 0^M
audit_file_dest = D:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\ADUMP^M
background_dump_dest = D:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\BDUMP^M
user_dump_dest = D:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\UDUMP^M
core_dump_dest = D:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\CDUMP^M
db_name = orcl^M
open_cursors = 300^M
pga_aggregate_target = 96468992^M
aq_tm_processes = 0^M
PMON started with pid=2, OS id=2796^M
MMAN started with pid=4, OS id=2888^M
PSP0 started with pid=3, OS id=2904^M
DBW0 started with pid=5, OS id=2892^M
LGWR started with pid=6, OS id=2832^M
CKPT started with pid=7, OS id=2932^M
SMON started with pid=8, OS id=2772^M
RECO started with pid=9, OS id=2808^M
MMON started with pid=10, OS id=2760^M
Mon Feb 17 20:13:24 2014^M
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...^M
Mon Feb 17 20:13:24 2014^M
starting up 1 shared server(s) ...^M
MMNL started with pid=11, OS id=2032^M
Mon Feb 17 20:13:28 2014^M
alter database mount exclusive^M
Mon Feb 17 20:13:33 2014^M
Setting recovery target incarnation to 5^M
Mon Feb 17 20:13:34 2014^M
Successful mount of redo thread 1, with mount id 1367575656^M
Mon Feb 17 20:13:34 2014^M
Database mounted in Exclusive Mode^M
Completed: alter database mount exclusive^M
Mon Feb 17 20:13:34 2014^M
alter database open^M
Mon Feb 17 20:13:37 2014^M
LGWR: STARTING ARCH PROCESSES^M
ARC0 started with pid=15, OS id=4192^M
ARC1 started with pid=16, OS id=4196^M
Mon Feb 17 20:13:37 2014
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Mon Feb 17 20:13:38 2014
Errors in file d:\oracle\product\10.2.0\db_1\admin\orcl\bdump\orcl_lgwr_2832.trc:
ORA-00600: internal error code, arguments: [3705], [1], [2], [2], [0], [], [], []
Mon Feb 17 20:13:51 2014
Errors in file d:\oracle\product\10.2.0\db_1\admin\orcl\bdump\orcl_lgwr_2832.trc:
ORA-00600: internal error code, arguments: [3705], [1], [2], [2], [0], [], [], []
Mon Feb 17 20:13:51 2014
LGWR: terminating instance due to error 470
Mon Feb 17 20:13:51 2014
Errors in file d:\oracle\product\10.2.0\db_1\admin\orcl\bdump\orcl_arc1_4196.trc:
ORA-00470: LGWR process terminated with error
Mon Feb 17 20:13:52 2014
Errors in file d:\oracle\product\10.2.0\db_1\admin\orcl\bdump\orcl_arc1_4196.trc:
ORA-00470: LGWR process terminated with error
折腾了一段,看到帖子http://www.itpub.net/thread-1766257-1-1.html,就查看看是不是因为数据文件的头文件
SCN不一致导致的数据库无法open:
SQL> select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header
2 group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;
STATUS CHECKPOINT_CHANGE# CHECKPOINT_T RESETLOGS_CHANGE# RESETLOGS_TI COUNT(*) FUZ
------- ------------------ ------------ ----------------- ------------ ---------- ---
ONLINE 297236292 26-NOV-13 275473245 25-JAN-13 1 YES
ONLINE 301652101 14-FEB-14 275473245 25-JAN-13 1 NO
ONLINE 301652103 14-FEB-14 275473245 25-JAN-13 5 YES
SQL> SELECT FILE#,TABLESPACE_NAME,checkpoint_change# FROM v$datafile_header
2 /
FILE# TABLESPACE_NAME CHECKPOINT_CHANGE#
---------- ------------------------------ ------------------
1 SYSTEM 301652103
2 UNDOTBS1 301652103
3 SYSAUX 301652103
4 USERS 301652103
5 EXAMPLE 301652103
6 TEST2 297236292
8 UNIS_ZG 301652101
7 rows selected.
SQL>
把数据文件
6 TEST2 297236292
8 UNIS_ZG 301652101
这两个OFFLINE
SQL> ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\DSG_TEST.DBF' OFFLINE;
Database altered.
SQL> ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\TEST09.DBF' OFFLINE;
Database altered.
SQL>
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL>
结果还是报错,根据ORA-01194去查发现有Oracle Hidden Parameter:_allow_resetlogs_corruption这样一个隐含参数
在http://www.eygle.com/archives/2005/10/oracle_hidden_allow_resetlogs_corruption.html里面老盖有详细说明
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL>
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 96469672 bytes
Database Buffers 188743680 bytes
Redo Buffers 7139328 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL>
问题解决