删增redo log 遇到了ORA-00366错误
本帖最后由 lnwxzyp 于 2014-3-29 21:15 编辑Hi Maclean:
版本介绍：
1.OS： windows 2003 R2 x64
2.Database:10.2.0.5.8
3.Bundle Patch apply 14134053
4.数据库运行于非归档模式
今天给一个数据库做优化的时候发现他们的日志频率切换太快，发现使用了默认的三组日志szie:50M，同时由于他们安装的ORACLE_HOME居然是在C盘，其中包括redo log、temporary tablespace、undotbs、system、sysaux、controlfile这些也都在C盘的C:\oracle\product\10.2.0\oradata\orcl目录下。
于是我就考虑把日志给创建到盘阵所在的D盘。首先是查看了v$log发现日志组3处于非活动的状态，于是我就把日志组3先给删除掉，然后新建group 3。
alter database drop logfile group 3;
alter database add logfile group 3 ('D:\orayadata\orcl\REDO03.LOG') size 384M;
然后发出了一条日志切换命令 alter system switch logfile;
Sat Mar 29 16:14:29 中国标准时间 2014
Thread 1 advanced to log sequence 21373 (LGWR switch)
Current log# 3 seq# 21373 mem# 0: D:\ORAYADATA\ORCL\REDO03.LOG
继续查看发现group 1 也处于非活动状态，于是照样删除重建
alter database drop logfile group 1;
alter database add logfile group 1 ('D:\orayadata\orcl\REDO01.LOG') size 384M;
当我再次发出日志切换命令时，这个时候数据库crash了。。。马上去查看alert log发现如下提示:
Sat Mar 29 16:18:41 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_464.trc:
ORA-00366: log 3 of thread 1, checksum error in the file header
ORA-00312: online log 3 thread 1: 'D:\ORAYADATA\ORCL\REDO03.LOG'
Sat Mar 29 16:18:41 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_464.trc:
ORA-00366: log 3 of thread 1, checksum error in the file header
ORA-00312: online log 3 thread 1: 'D:\ORAYADATA\ORCL\REDO03.LOG'
Sat Mar 29 16:18:41 中国标准时间 2014
LGWR: terminating instance due to error 366
Sat Mar 29 16:20:52 中国标准时间 2014
Instance terminated by LGWR, pid = 464
然后尝试重新打开数据库：发现无法open，只能到mount
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 0 402653184 1 YES UNUSED 0
2 1 21372 52428800 1 NO ACTIVE 1.3800E+13 29-3? -14
5 1 0 402653184 1 YES UNUSED 0
4 1 0 402653184 1 YES UNUSED 0
3 1 21373 402653184 1 NO CURRENT 1.3800E+13 29-3? -14
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
3 ONLINE D:\ORAYADATA\ORCL\REDO03.LOG NO
2 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG NO
1 ONLINE D:\ORAYADATA\ORCL\REDO01.LOG NO
4 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG NO
5 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO05.LOG NO
SQL> alter system archive log current;
alter system archive log current
*
第 1 行出现错误:
ORA-01109: ??????
SQL> alter system switch logfile;
alter system switch logfile
*
第 1 行出现错误:
ORA-01109: ??????
SQL> alter session set nls_language='AMERICAN';
Session altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE
*
ERROR at line 1:
ORA-01109: database not open
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-00366: log 3 of thread 1, checksum error in the file header
ORA-00312: online log 3 thread 1: 'D:\ORAYADATA\ORCL\REDO03.LOG'
MOS上去查询，找到如下一篇文章
Error: ORA 366
Text: log %s of thread %s, checksum error in the file header
---------------------------------------------------------------------------
Cause: The file header for the redo log contains a checksum that does not
match the value calculated from the file header as read from disk. This
means the file header is corrupted
Action: Find and install correct version of log or reset logs.
现在数据库无法OPEN，日志无法切换，日志也无法Clear
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 2 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 3 thread 1: 'D:\ORAYADATA\ORCL\REDO03.LOG'
这个数据库在27号的时候还遇到过很多问题，比如：
Thu Mar 27 11:27:54 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_552.trc:
ORA-01115: 从文件 114 读取块时出现 IO 错误 (块 # 1)
ORA-01110: 数据文件 114: 'D:\ORAYADATA\ORCL\TBS_DATA99.DBF'
ORA-27091: 无法将 I/O 排队
ORA-27070: 异步读取/写入失败
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 1450) 系统资源不足，无法完成请求的服务。
Thu Mar 27 11:39:30 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1624.trc:
ORA-01115: ??? 208 ?????? IO ?? (? # 3)
ORA-01110: ???? 208: 'D:\ORAYADATA\ORCL\TEMP05.DBF'
ORA-27091: ??? I/O ??
ORA-27070: ????/????
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 1450) 系统资源不足，无法完成请求的服务。
Thu Mar 27 10:05:53 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw0_1512.trc:
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: 'D:\ORAYADATA\ORCL\TBS_DATA2.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 3) 系统找不到指定的路径。
后来不知道局方是怎么解决的，反正吃完午餐好像这些问题好像都消失了。。不知道是不是类似的故障又突发了，希望能不重新装数据库能解决 1、猜测C: D:\是属于不同磁盘的，且D:\更加有问题
2、不是 current logfile group有问题，都可以用clear unarchived LOGFILE GROUP 来尝试，但是你的group #2 group #3分别是目前的active 和current
3、 现在的情况下尝试用常规方法启动库，如果实在不行 可以尝试用隐藏参数+resetlogs等方式来强制打开数据库，并建议重建数据库 本帖最后由 lnwxzyp 于 2014-3-29 21:20 编辑
非常感谢Maclean的及时回复，C盘和D盘确实是属于不同的盘阵，这个库目前有3.4TB的数据，之前是用导出的dmp文件来导入恢复的，再恢复一次比较耗费时间，能否说明一下用什么隐藏参数可以打开数据库？
无非是一些随便搜索下就能找到的东西，
http://www.askmaclean.com/archives/database-force-open-example.html 估计是生产库，隐藏参数强制打开数据库会丢失数据，再备份重建吧 Maclean Liu(刘相兵 发表于 2014-3-29 21:54 static/image/common/back.gif
无非是一些随便搜索下就能找到的东西，
http://www.askmaclean.com/archives/database-force-open-example. ...
非常感谢。。 本帖最后由 lnwxzyp 于 2014-3-30 15:24 编辑
在群友小催的无私帮助下，终于把问题给解决了，现在把解决过程列出来供大家参考：
alert.log里面还存在如下错误：
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1040.trc:
ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员
ORA-00312: 联机日志 3 线程 1: 'D:\ORAYADATA\ORCL\REDO03.LOG'
ORA-27046: 文件大小不是逻辑块大小的倍数
OSD-04000: 逻辑块大小不匹配 (OS 4096)
自己的思路是看是否存在对redo log进行resize的命令给缩小到50M，然后把之前删除的50M的redo03.log 给替换到D盘的相应位置，后来搜索了一下发现没有这种命令。。自己首先折腾了一番：
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database until cancel;
ORA-00279: change 13800313631981 generated at 03/29/2014 16:14:29 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_03_29\O1_MF_1_21373_%U_.ARC
ORA-00280: change 13800313631981 for thread 1 is in sequence #21373
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
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: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
ORA-01112: media recovery not started
SQL> alter database open resetlogs
2 ;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00366: log 3 of thread 1, checksum error in the file header
ORA-00312: online log 3 thread 1: 'D:\ORAYADATA\ORCL\REDO03.LOG'
SQL> recover database until cancel
ORA-00279: change 13800313631981 generated at 03/29/2014 16:14:29 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_03_29\O1_MF_1_21373_%U_.ARC
ORA-00280: change 13800313631981 for thread 1 is in sequence #21373
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log 'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_03_29\O1_MF_1_21373_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????
ORA-00308: cannot open archived log 'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_03_29\O1_MF_1_21373_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????
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: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: 'D:\ORAYADATA\ORCL\REDO03.LOG'
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 0 402653184 1 YES UNUSED 0
2 1 21372 52428800 1 NO ACTIVE 1.3800E+13 29-MAR-14
5 1 0 402653184 1 YES UNUSED 0
4 1 0 402653184 1 YES UNUSED 0
3 1 21373 402653184 1 NO CURRENT 1.3800E+13 29-MAR-14
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
实在没辙，不过这个库本身是有exp的backup，所以大不了就重装一下系统再来导入一次，不过还是抱着希望在askmaclean上发了帖子，ML很快就恢复了我的问题，提到常规恢复或者使用内部手段去恢复。自己想了一个方法是先导出控制文件然后把group 3的redo03.log从D:盘复制到C盘 然后在控制文件当中修改redo的路径
alter database backup controlfile to trace as 'c:\ct.txt'
然后用其中采用 RESETLOGS的语句修改其中redo log存放于D盘的路径，把D盘的REDO03.LOG复制到C盘相应的目录下,接着执行如下语句:
sqlplus "/as sysdba"
sql>STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 200
MAXINSTANCES 8
MAXLOGHISTORY 37904
LOGFILE
GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG' SIZE 384M,
GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG' SIZE 50M,
GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG' SIZE 384M,
GROUP 4 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG' SIZE 384M,
GROUP 5 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO05.LOG' SIZE 384M
DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF',
'D:\ORAYADATA\ORCL\TBS_INDX01',
'D:\ORAYADATA\ORCL\TBS_DATA1.DBF',
'D:\ORAYADATA\ORCL\TBS_INDX02',
'D:\ORAYADATA\ORCL\TBS_INDX03',
'D:\ORAYADATA\ORCL\TBS_INDX04',
'D:\ORAYADATA\ORCL\TBS_INDX05',
'D:\ORAYADATA\ORCL\TBS_INDX06',
'D:\ORAYADATA\ORCL\TBS_DATA2.DBF',
'D:\ORAYADATA\ORCL\TBS_DATA3.DBF',
'D:\ORAYADATA\ORCL\TBS_DATA4.DBF',
'D:\ORAYADATA\ORCL\TBS_DATA5.DBF',
'D:\ORAYADATA\ORCL\TBS_DATA6.DBF',
'D:\ORAYADATA\ORCL\TBS_DATA7.DBF',
'D:\ORAYADATA\ORCL\TBS_DATA8.DBF',
'D:\ORAYADATA\ORCL\TBS_DATA9.DBF',
'D:\ORAYADATA\ORCL\TBS_DATA10.DBF',
'D:\ORAYADATA\ORCL\SYSAUX01.DBF.DBF',
'D:\ORAYADATA\ORCL\SYSTEM01.DBF.DBF',
'D:\ORAYADATA\ORCL\UNDOTBS01.DBF.DBF'
CHARACTER SET ZHS16GBK
;
SQL> ALTER DATABASE RECOVER DATABASE USING BACKUP CONTROLFILE;
SQL> ALTER DATABASE OPEN RESETLOGS;
执行之后提示SYSTEM 需要恢复，然后查看alert log里面出现了一大堆如下错误
Sat Mar 29 22:11:47 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3040.trc:
ORA-00600: ??????, ??: , , , , , , [], []
Sat Mar 29 22:11:49 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3040.trc:
ORA-00600: ??????, ??: , , , , , , [], []
在小催的帮助下结合ML的那篇帖子中提到多次启停数据库或者使用10015时间可以解决，算了一下SCN的差别不大，先启用了这个隐藏参数：
alter system set "_allow_resetlogs_corruption"= TRUE scope=spfile;
alter log当中显示如下：
Sat Mar 29 22:08:07 中国标准时间 2014
ALTER SYSTEM SET _allow_resetlogs_corruption=TRUE SCOPE=SPFILE;
Sat Mar 29 22:08:14 中国标准时间 2014
Shutting down instance (immediate)
Sat Mar 29 22:08:14 中国标准时间 2014
Shutting down instance: further logons disabled
Sat Mar 29 22:08:14 中国标准时间 2014
Stopping background process CJQ0
Sat Mar 29 22:08:14 中国标准时间 2014
Stopping background process MMNL
Sat Mar 29 22:08:15 中国标准时间 2014
Background process MMNL not dead after 10 seconds
Sat Mar 29 22:08:15 中国标准时间 2014
Stopping background process MMON
Sat Mar 29 22:08:16 中国标准时间 2014
Background process MMON not dead after 30 seconds
License high water mark = 9
Sat Mar 29 22:08:16 中国标准时间 2014
Job queue slave processes stopped
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Sat Mar 29 22:08:18 中国标准时间 2014
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Sat Mar 29 22:08:18 中国标准时间 2014
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sat Mar 29 22:08:21 2014
Instance shutdown complete
Sat Mar 29 22:08:29 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
IMODE=BR
ILAT =121
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.5.0.
System parameters with non-default values:
processes = 1000
event = 8298501 trace name context forever, level 10
__shared_pool_size = 369098752
__large_pool_size = 16777216
__java_pool_size = 16777216
__streams_pool_size = 0
spfile = C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA
sga_target = 2550136832
control_files = C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
db_block_size = 8192
__db_cache_size = 2130706432
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = c:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size= 2147483648
_allow_resetlogs_corruption= TRUE
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes = 10
audit_file_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP
background_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP
user_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
core_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP
db_name = orcl
open_cursors = 300
pga_aggregate_target = 848297984
PMON started with pid=2, OS id=2756
PSP0 started with pid=3, OS id=2852
MMAN started with pid=4, OS id=1328
DBW0 started with pid=5, OS id=1748
LGWR started with pid=6, OS id=2716
CKPT started with pid=7, OS id=2604
SMON started with pid=8, OS id=2284
RECO started with pid=9, OS id=2116
CJQ0 started with pid=10, OS id=2652
MMON started with pid=11, OS id=1272
Sat Mar 29 22:08:30 中国标准时间 2014
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=1964
Sat Mar 29 22:08:30 中国标准时间 2014
starting up 1 shared server(s) ...
Sat Mar 29 22:09:00 中国标准时间 2014
alter database open
Sat Mar 29 22:09:00 中国标准时间 2014
ORA-1507 signalled during: alter database open...
Sat Mar 29 22:09:05 中国标准时间 2014
alter database mount
Sat Mar 29 22:09:10 中国标准时间 2014
Setting recovery target incarnation to 1
Sat Mar 29 22:09:10 中国标准时间 2014
Successful mount of redo thread 1, with mount id 1371321345
Sat Mar 29 22:09:10 中国标准时间 2014
Database mounted in Exclusive Mode
Completed: alter database mount
Sat Mar 29 22:11:22 中国标准时间 2014
alter database open
Sat Mar 29 22:11:22 中国标准时间 2014
ORA-1589 signalled during: alter database open...
Sat Mar 29 22:11:31 中国标准时间 2014
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 13800313631981
Sat Mar 29 22:11:42 中国标准时间 2014
Online log C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG: Thread 1 Group 4 was previously cleared
Online log C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO05.LOG: Thread 1 Group 5 was previously cleared
Sat Mar 29 22:11:45 中国标准时间 2014
Setting recovery target incarnation to 2
Sat Mar 29 22:11:46 中国标准时间 2014
************************************************************
Warning: The SCN headroom for this database is only 14 days!
************************************************************
Sat Mar 29 22:11:46 中国标准时间 2014
Assigning activation ID 1371321345 (0x51bcb401)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Sat Mar 29 22:11:46 中国标准时间 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Mar 29 22:11:46 中国标准时间 2014
SMON: enabling cache recovery
Sat Mar 29 22:11:47 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3040.trc:
ORA-00600: ??????, ??: , , , , , , [], []
Sat Mar 29 22:11:49 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3040.trc:
ORA-00600: ??????, ??: , , , , , , [], []
Sat Mar 29 22:11:49 中国标准时间 2014
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 3040
ORA-1092 signalled during: alter database open resetlogs...
Sat Mar 29 22:11:50 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3040.trc:
ORA-00600: ??????, ??: , , , , , , [], []
ORA-01092: ORACLE ???????????
ORA-00600: ??????, ??: , , , , , , [], []
Sat Mar 29 22:11:52 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3040.trc:
ORA-00600: ??????, ??: , , , , , , [], []
ORA-00600: ??????, ??: , , , , , , [], []
ORA-01092: ORACLE ???????????
ORA-00600: ??????, ??: , , , , , , [], []
Sat Mar 29 22:11:53 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3040.trc:
ORA-00600: ??????, ??: , , , , , , [], []
ORA-00600: ??????, ??: , , , , , , [], []
ORA-01092: ORACLE ???????????
ORA-00600: ??????, ??: , , , , , , [], []
Sat Mar 29 22:11:55 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3040.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: , , , , , , [], []
ORA-00600: internal error code, arguments: , , , , , , [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: , , , , , , [], [] 本帖最后由 lnwxzyp 于 2014-3-30 15:26 编辑
重启了两次，每次都执行了recover database命令，仍然无法OPEN，之后发现在alert log里面新出来的日志中 ORA-00600已经没有了，随之出现了大量的ORA-00600和错误，同时也注意到了这么一个提示： 7 redo blocks read, 0 data blocks need recovery
Sat Mar 29 22:23:50 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
IMODE=BR
ILAT =121
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.5.0.
System parameters with non-default values:
processes = 1000
event = 8298501 trace name context forever, level 10
__shared_pool_size = 369098752
__large_pool_size = 16777216
__java_pool_size = 16777216
__streams_pool_size = 0
spfile = C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA
sga_target = 2550136832
control_files = C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
db_block_size = 8192
__db_cache_size = 2130706432
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = c:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size= 2147483648
_allow_resetlogs_corruption= TRUE
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes = 10
audit_file_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP
background_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP
user_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
core_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP
db_name = orcl
open_cursors = 300
pga_aggregate_target = 848297984
PMON started with pid=2, OS id=2044
PSP0 started with pid=3, OS id=308
MMAN started with pid=4, OS id=180
DBW0 started with pid=5, OS id=724
LGWR started with pid=6, OS id=2976
CKPT started with pid=7, OS id=676
SMON started with pid=8, OS id=2788
RECO started with pid=9, OS id=2204
CJQ0 started with pid=10, OS id=836
MMON started with pid=11, OS id=2364
Sat Mar 29 22:23:50 中国标准时间 2014
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=1944
Sat Mar 29 22:23:50 中国标准时间 2014
starting up 1 shared server(s) ...
Sat Mar 29 22:23:51 中国标准时间 2014
ALTER DATABASE MOUNT
Sat Mar 29 22:23:55 中国标准时间 2014
Setting recovery target incarnation to 2
Sat Mar 29 22:23:55 中国标准时间 2014
Successful mount of redo thread 1, with mount id 1371311223
Sat Mar 29 22:23:55 中国标准时间 2014
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Sat Mar 29 22:24:32 中国标准时间 2014
alter database open
Sat Mar 29 22:24:32 中国标准时间 2014
ORA-1113 signalled during: alter database open...
Sat Mar 29 22:24:43 中国标准时间 2014
ALTER DATABASE RECOVER datafile 1
Sat Mar 29 22:24:43 中国标准时间 2014
Media Recovery Start
parallel recovery started with 7 processes
Sat Mar 29 22:24:43 中国标准时间 2014
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Completed: ALTER DATABASE RECOVER datafile 1
Sat Mar 29 22:24:53 中国标准时间 2014
alter database open
ORA-1113 signalled during: alter database open...
Sat Mar 29 22:25:04 中国标准时间 2014
ALTER DATABASE RECOVER datafile 2
Sat Mar 29 22:25:04 中国标准时间 2014
Media Recovery Start
parallel recovery started with 7 processes
Sat Mar 29 22:25:04 中国标准时间 2014
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Completed: ALTER DATABASE RECOVER datafile 2
Sat Mar 29 22:25:08 中国标准时间 2014
alter database open
ORA-1113 signalled during: alter database open...
Sat Mar 29 22:25:17 中国标准时间 2014
ALTER DATABASE RECOVER database
Sat Mar 29 22:25:17 中国标准时间 2014
Media Recovery Start
parallel recovery started with 7 processes
Sat Mar 29 22:25:25 中国标准时间 2014
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Completed: ALTER DATABASE RECOVER database
Sat Mar 29 22:25:41 中国标准时间 2014
alter database open
Sat Mar 29 22:25:41 中国标准时间 2014
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Sat Mar 29 22:25:42 中国标准时间 2014
Started redo scan
Sat Mar 29 22:25:42 中国标准时间 2014
Completed redo scan
7 redo blocks read, 0 data blocks need recovery
Sat Mar 29 22:25:42 中国标准时间 2014
Started redo application at
Thread 1: logseq 1, block 2, scn 13800313631985
Sat Mar 29 22:25:42 中国标准时间 2014
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Sat Mar 29 22:25:42 中国标准时间 2014
Completed redo application
Sat Mar 29 22:25:42 中国标准时间 2014
Completed crash recovery at
Thread 1: logseq 1, block 9, scn 13800313651992
0 data blocks read, 0 data blocks written, 7 redo blocks read
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
Sat Mar 29 22:26:00 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_2788.trc:
ORA-00600: internal error code, arguments: , [], [], [], [], [], [], []
Sat Mar 29 22:26:01 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_2780.trc:
ORA-00600: 内部错误代码, 参数: , , , [], [], [], [], []
Sat Mar 29 22:26:02 中国标准时间 2014
ORACLE Instance orcl (pid = 8) - Error 600 encountered while recovering transaction (11, 1).
Sat Mar 29 22:26:02 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_2788.trc:
ORA-00600: internal error code, arguments: , [], [], [], [], [], [], []
Sat Mar 29 22:26:03 中国标准时间 2014
Doing block recovery for file 54 block 4093
Resuming block recovery (PMON) for file 54 block 4093
Block recovery from logseq 2, block 90 to scn 13800313652078
Sat Mar 29 22:26:03 中国标准时间 2014
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Block recovery stopped at EOT rba 2.92.16
Block recovery completed at rba 2.92.16, scn 3213.583730028
Doing block recovery for file 2 block 3697
Resuming block recovery (PMON) for file 2 block 3697
Block recovery from logseq 2, block 90 to scn 13800313652075
Sat Mar 29 22:26:03 中国标准时间 2014
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Block recovery completed at rba 2.92.16, scn 3213.583730028
Sat Mar 29 22:26:04 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_2780.trc:
ORA-00600: 内部错误代码, 参数: , , , [], [], [], [], []
DEBUG: Replaying xcb 0x12b698f8, pmd 0xf1b31e8 for failed op 8
Doing block recovery for file 54 block 1608
No block recovery was needed
Sat Mar 29 22:26:22 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_2780.trc:
ORA-00600: 内部错误代码, 参数: , , , [], [], [], [], []
ORA-00600: 内部错误代码, 参数: , , , [], [], [], [], []
Sat Mar 29 22:26:23 中国标准时间 2014
DEBUG: Replaying xcb 0x12b698f8, pmd 0xf1b31e8 for failed op 8
Doing block recovery for file 54 block 1608
No block recovery was needed
Sat Mar 29 22:26:23 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_2780.trc:
ORA-00600: 内部错误代码, 参数: , , , [], [], [], [], []
ORA-00600: 内部错误代码, 参数: , , , [], [], [], [], []
Sat Mar 29 22:26:25 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_2780.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: , , , [], [], [], [], []
ORA-00600: internal error code, arguments: , , , [], [], [], [], []
Sat Mar 29 22:26:40 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_2788.trc:
ORA-00600: internal error code, arguments: , , , [], [], [], [], []
Sat Mar 29 22:26:41 中国标准时间 2014
DEBUG: Replaying xcb 0x12b698f8, pmd 0xf1b31e8 for failed op 8
Doing block recovery for file 54 block 1608
No block recovery was needed
Sat Mar 29 22:26:42 中国标准时间 2014
Fatal internal error happened while SMON was doing active transaction recovery.
Sat Mar 29 22:26:42 中国标准时间 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_2788.trc:
ORA-00600: internal error code, arguments: , , , [], [], [], [], [] 本帖最后由 lnwxzyp 于 2014-3-30 15:47 编辑
小催提供的解决办法是将UNDO管理改为手动重启之后新建UNDO设为默认 删除老的UNDO
SQL>ALTER SYSTEM SET undo_management='MANUAL' SCOPE=SPFILE;
重启之后发现已经可以OPEN了，然后执行一下命令：
SQL>create undo tablespace undotbs2 datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS02.DBF' size 4096M
SQL>ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=SPFILE;
SQL>ALTER SYSTEM SET undo_management='AUTO' SCOPE=SPFILE;
重启之后发现原来的UNDOTBS1无法删掉也不能OFFLINE
SQL>drop tablespace undotbs1 including contents and datafiles;
要删掉UNDOTBS1 这里还需要一个隐藏参数 _corrupted_rollback_segments
SQL> SELECT segment_name,tablespace_name,owner,status FROM dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME OWNER STATUS
------------------------------ ------------------------------ ------ ----------------
SYSTEM SYSTEM SYS ONLINE
_SYSSMU18$ UNDOTBS1 PUBLIC OFFLINE
_SYSSMU17$ UNDOTBS1 PUBLIC OFFLINE
_SYSSMU16$ UNDOTBS1 PUBLIC OFFLINE
_SYSSMU15$ UNDOTBS1 PUBLIC OFFLINE
_SYSSMU14$ UNDOTBS1 PUBLIC OFFLINE
_SYSSMU13$ UNDOTBS1 PUBLIC OFFLINE
_SYSSMU12$ UNDOTBS1 PUBLIC OFFLINE
_SYSSMU11$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU42$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU41$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU40$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU39$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU38$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU37$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU36$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU35$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU34$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU33$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU32$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU31$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU30$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU29$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU28$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU27$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU26$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU25$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU24$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU23$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU22$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU21$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU20$ UNDOTBS2 PUBLIC ONLINE
_SYSSMU19$ UNDOTBS2 PUBLIC ONLINE
执行一下命令 报错
SQL>alter system set "_corrupted_rollback_segments" = (_SYSSMU11$) scope=spfile;
先关闭数据库然后通过spfile创建一个pfile init.ora 往init.ora里面添加隐藏参数
_CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU11$)
然后手动备份原有的spfile，再通过init.ora来创建一个新的spfile启动
SQL> alter database datafile 2 offline;
alter database datafile 2 offline
*
第 1 行出现错误:
ORA-01145: 除非启用了介质恢复, 否则不允许立即脱机
SQL> show parameter _CORRUPTED
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_corrupted_rollback_segments string _SYSSMU11$
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 2550136832 bytes
Fixed Size 2079704 bytes
Variable Size 352322600 bytes
Database Buffers 2181038080 bytes
Redo Buffers 14696448 bytes
数据库装载完毕。
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
第 1 行出现错误:
ORA-01109: 数据库未打开
SQL> alter database open;
数据库已更改。
SQL> drop tablespace undotbs1 including contents and datafiles;
表空间已删除。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 2550136832 bytes
Fixed Size 2079704 bytes
Variable Size 352322600 bytes
Database Buffers 2181038080 bytes
Redo Buffers 14696448 bytes
数据库装载完毕。
SQL> alter database open;
数据库已更改。
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string C:\ORACLE\PRODUCT\10.2.0\DB_1\
DBS\SPFILEORCL.ORA
然后顺便把日志也的大小也改一下：
alter database drop logfile group 2;
手动删除C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG文件;
alter database add logfile group 2('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG') size 402653184
最后执行以下命令 重建temporary tablespace消除alert log里面的警告
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP07.DBF' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP06.DBF' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORAYADATA\ORCL\TEMP05.DBF' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORAYADATA\ORCL\TEMP04.DBF' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORAYADATA\ORCL\TEMP03.DBF' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORAYADATA\ORCL\TEMP02.DBF' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORAYADATA\ORCL\TEMP01.DBF' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' REUSE;
ALTER TABLESPACE TBS_TEMP ADD TEMPFILE 'D:\ORAYADATA\ORCL\TBS_TEMP.DBF' REUSE;
ALTER TABLESPACE ITPAGE_TEMP ADD TEMPFILE 'D:\ORAYADATA\ORCL\ITPAGE_TEMP.DBF' REUSE;
最后要reset参数 ，reset的意思是把参数从spfile里面删除：
SQL>ALTER SYSTEM RESET "_corrupted_rollback_segments" SCOPE=SPFILE SID='*';
SQL>ALTER SYSTEM RESET "_allow_resetlogs_corruption" SCOPE=SPFILE SID='*';
页:
[1]