删除online日志测试及ORA-600 [4194]错误的处理

今天做了一个关于破坏online日志的恢复测试，主要三个场景：
测试1：正常关闭数据库后删除非当前日志
测试2：正常关库后，删除在线日志文件
测试3：非正常关闭数据库，并删除当前在线日志文件我的测试环境是Oracle 10.2.0.1 32bit的数据库，OS版本为Red Hat 5.3，下面看具体测试经过：测试1：正常关闭数据库后删除非当前日志[oracle@ora10g ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on 星期三 6月 24 10:34:53 2015Copyright （c） 1982, 2005, Oracle. All rights reserved.Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSYS@ora10g> select group#,thread#,status,archived from v$log; GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 CURRENT NO
2 1 INACTIVE YES
3 1 ACTIVE YESSYS@ora10g> set line 130 pages 130
SYS@ora10g> col member for a50
SYS@ora10g> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/ora10g/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/ora10g/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/ora10g/redo01.log NOSYS@ora10g> conn zlm/zlm
Connected.
ZLM@ora10g> create table t1 as select * from dba_objects where 1=2;Table created.ZLM@ora10g> insert into t1 select * from dba_objects where rownum<11;10 rows created.ZLM@ora10g> select count（*） from t1; COUNT（*）
----------
10ZLM@ora10g> commit; （此处不commit也可，因为对在线日志归档的时候会进行commit操作）Commit complete.ZLM@ora10g> alter system archive log current;System altered.ZLM@ora10g> select group#,thread#,status,archived from v$log; GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 ACTIVE YES
2 1 CURRENT NO
3 1 INACTIVE YES--删除非当前的在线日志文件（ACTIVE的和INACTIVE的）
[oracle@ora10g backupsets]$ cd /u01/app/oracle/oradata/ora10g/
[oracle@ora10g ora10g]$ pwd
/u01/app/oracle/oradata/ora10g
[oracle@ora10g ora10g]$ ls -l
total 1461348
-rw-r----- 1 oracle oinstall 7520256 Jun 24 10:40 control01.ctl
-rw-r----- 1 oracle oinstall 7520256 Jun 24 10:40 control02.ctl
-rw-r----- 1 oracle oinstall 7520256 Jun 24 10:40 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Jun 24 10:38 example01.dbf
-rw-r----- 1 oracle oinstall 172032 Nov 29 2014 indx01.dbf
-rw-r----- 1 oracle oinstall 52429312 Jun 24 10:39 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 24 10:39 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jun 24 10:34 redo03.log
-rw-r----- 1 oracle oinstall 283123712 Jun 24 10:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 587210752 Jun 24 10:38 system01.dbf
-rw-r----- 1 oracle oinstall 52436992 Jun 23 16:17 temp01.dbf
-rw-r----- 1 oracle oinstall 173023232 Jun 24 10:38 undotbs01.dbf
-rw-r----- 1 oracle oinstall 41951232 Jun 24 10:38 users01.dbf
-rw-r----- 1 oracle oinstall 100671488 Jun 24 10:38 zlm01.dbf
[oracle@ora10g ora10g]$ rm -f redo01.log
[oracle@ora10g ora10g]$ rm -f redo03.log
[oracle@ora10g ora10g]$ ls -l redo*
-rw-r----- 1 oracle oinstall 52429312 Jun 24 10:44 redo02.log
[oracle@ora10g ora10g]$测试1：正常关闭数据库后删除非当前日志--正常关闭数据库并重启
ZLM@ora10g> shutdown immediate
ORA-01031: insufficient privileges
ZLM@ora10g> conn / as sysdba
Connected.
SYS@ora10g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora10g> startup
ORACLE instance started.Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 318767328 bytes
Database Buffers 201326592 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: "/u01/app/oracle/oradata/ora10g/redo01.log"提示无法打开日志组1的日志文件，因为之前在OS层面已经将其删除了

--观察alert日志
[oracle@ora10g ora10g]$ cd /u01/app/oracle/admin/ora10g/bdump/
[oracle@ora10g bdump]$ tail -50f alert_ora10g.log
MMON started with pid=11, OS id=2970
Wed Jun 24 10:45:54 2015
starting up 1 dispatcher（s） for network address "（ADDRESS=（PARTIAL=YES）（PROTOCOL=TCP））"...
starting up 1 shared server（s） ...
CJQ0 started with pid=10, OS id=2968
MMNL started with pid=12, OS id=2972
Wed Jun 24 10:45:55 2015
ALTER DATABASE MOUNT
Wed Jun 24 10:45:58 2015
Setting recovery target incarnation to 8
Wed Jun 24 10:45:58 2015
Successful mount of redo thread 1, with mount id 4202063779
Wed Jun 24 10:45:58 2015
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Wed Jun 24 10:45:59 2015
ALTER DATABASE OPEN
Wed Jun 24 10:45:59 2015
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=2980
Wed Jun 24 10:45:59 2015
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Wed Jun 24 10:45:59 2015
Errors in file /u01/app/oracle/admin/ora10g/bdump/ora10g_lgwr_2960.trc:
ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1]
ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [/u01/app/oracle/oradata/ora10g/redo01.log]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
Additional information: 3
Wed Jun 24 10:45:59 2015
Errors in file /u01/app/oracle/admin/ora10g/bdump/ora10g_lgwr_2960.trc:
ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1]
ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [/u01/app/oracle/oradata/ora10g/redo01.log]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
Additional information: 3
Wed Jun 24 10:45:59 2015
ARC0: STARTING ARCH PROCESSES
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the "no FAL" ARCH
ARC0: Becoming the "no SRL" ARCH
ARC1 started with pid=17, OS id=2982
Wed Jun 24 10:46:00 2015
ARC1: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=2984
Wed Jun 24 10:46:00 2015
ORA-313 signalled during: ALTER DATABASE OPEN...

发现确实是读取redo01.log文件错误，无法OPEN数据库，只停留在MOUNT状态--清空刚才被删除的2个在线日志文件（相当于重建）
SYS@ora10g> select open_mode from v$database;OPEN_MODE
----------
MOUNTEDSYS@ora10g> alter database clear logfile group 1;Database altered.SYS@ora10g> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: "/u01/app/oracle/oradata/ora10g/redo03.log"SYS@ora10g> alter database clear logfile group 3;Database altered.SYS@ora10g> alter database open;Database altered.SYS@ora10g> conn zlm/zlm
Connected.
ZLM@ora10g> select count（*） from t1; COUNT（*）
----------
10ZLM@ora10g>尽管删除了2个非在线日志文件，那么就可以很方便的将数据库打开，只要重建被删除的日志文件就行（通过CLEAR操作），且数据并不会丢失（因为是shutdown immediate方式关闭库的）

--继续查看alert日志内容
Wed Jun 24 10:48:55 2015
alter database clear logfile group 1
Wed Jun 24 10:48:56 2015
Clearing online log 1 of thread 1 sequence number 20
Wed Jun 24 10:48:56 2015
Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_2978.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: "/u01/app/oracle/oradata/ora10g/redo01.log"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Completed: alter database clear logfile group 1
Wed Jun 24 10:49:03 2015
alter database open
Wed Jun 24 10:49:03 2015
Errors in file /u01/app/oracle/admin/ora10g/bdump/ora10g_lgwr_2960.trc:
ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1]
ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1] [/u01/app/oracle/oradata/ora10g/redo03.log]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
Additional information: 3
Wed Jun 24 10:49:03 2015
Errors in file /u01/app/oracle/admin/ora10g/bdump/ora10g_lgwr_2960.trc:
ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1]
ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1] [/u01/app/oracle/oradata/ora10g/redo03.log]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
Additional information: 3
ORA-313 signalled during: alter database open...
Wed Jun 24 10:49:14 2015
alter database clear logfile group 3
Wed Jun 24 10:49:14 2015
Clearing online log 3 of thread 1 sequence number 19
Wed Jun 24 10:49:14 2015
Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_2978.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: "/u01/app/oracle/oradata/ora10g/redo03.log"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Completed: alter database clear logfile group 3
Wed Jun 24 10:49:22 2015
alter database open
Wed Jun 24 10:49:22 2015
Thread 1 advanced to log sequence 22
Thread 1 opened at log sequence 22
Current log# 1 seq# 22 mem# 0: /u01/app/oracle/oradata/ora10g/redo01.log
Successful open of redo thread 1
Wed Jun 24 10:49:23 2015
db_recovery_file_dest_size of 2048 MB is 0.04% 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.
Wed Jun 24 10:49:23 2015
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jun 24 10:49:23 2015
SMON: enabling cache recovery
Wed Jun 24 10:49:23 2015
Successfully onlined Undo Tablespace 1.
Wed Jun 24 10:49:23 2015
SMON: enabling tx recovery
Wed Jun 24 10:49:24 2015
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off （no async multimaster replication found）
Starting background process QMNC
QMNC started with pid=19, OS id=2993
Wed Jun 24 10:49:31 2015
Completed: alter database open在CLEAR操作后，OS会创建相应的日志组成员文件，然后数据库就可以正常读取了，此时并不需要通过restore和recover对数据库进行恢复和还原即可

测试2：正常关库后，删除在线日志文件

ZLM@ora10g> select group#,thread#,status,archived from v$log; GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 CURRENT NO
2 1 INACTIVE YES
3 1 UNUSED YESZLM@ora10g> insert into t1 select * from dba_objects where rownum<11;10 rows created.ZLM@ora10g> select count（*） from t1; COUNT（*）
----------
20ZLM@ora10g> alter system archive log current;System altered.ZLM@ora10g> select group#,thread#,status,archived from v$log; GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 ACTIVE YES
2 1 INACTIVE YES
3 1 CURRENT NO--正常关闭数据库
ZLM@ora10g> shutdown immediate
ORA-01031: insufficient privileges
ZLM@ora10g> conn / as sysdba
Connected.
SYS@ora10g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora10g>

--OS上删除current的在线日志文件redo03.log
[oracle@ora10g ~]$ cd /u01/app/oracle/oradata/ora10g
[oracle@ora10g ora10g]$ ls -l
total 1461348
-rw-r----- 1 oracle oinstall 7520256 Jun 24 11:03 control01.ctl
-rw-r----- 1 oracle oinstall 7520256 Jun 24 11:03 control02.ctl
-rw-r----- 1 oracle oinstall 7520256 Jun 24 11:03 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Jun 24 11:03 example01.dbf
-rw-r----- 1 oracle oinstall 172032 Nov 29 2014 indx01.dbf
-rw-r----- 1 oracle oinstall 52429312 Jun 24 11:02 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 24 10:49 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jun 24 11:03 redo03.log
-rw-r----- 1 oracle oinstall 283123712 Jun 24 11:03 sysaux01.dbf
-rw-r----- 1 oracle oinstall 587210752 Jun 24 11:03 system01.dbf
-rw-r----- 1 oracle oinstall 52436992 Jun 23 16:17 temp01.dbf
-rw-r----- 1 oracle oinstall 173023232 Jun 24 11:03 undotbs01.dbf
-rw-r----- 1 oracle oinstall 41951232 Jun 24 11:03 users01.dbf
-rw-r----- 1 oracle oinstall 100671488 Jun 24 11:03 zlm01.dbf
[oracle@ora10g ora10g]$ rm -f redo03.log
[oracle@ora10g ora10g]$ ls -l redo*
-rw-r----- 1 oracle oinstall 52429312 Jun 24 11:02 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 24 10:49 redo02.log
[oracle@ora10g ora10g]$

--启动数据库
SYS@ora10g> startup
ORACLE instance started.Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 322961632 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: "/u01/app/oracle/oradata/ora10g/redo03.log"由于启动数据库会检查所有的文件，读取到在线日志文件redo03.log时，就报错了，因为已经被删除--还原数据库
SYS@ora10g> recover database until cancel;
Media recovery complete.
SYS@ora10g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open由于之前是正常关闭数据库，在线日志的内容已经写到归档日志文件，利用归档日志进行还原，而还原后的数据库，必须用resetlogs方式来OPEN数据库SYS@ora10g> alter database open resetlogs;Database altered.SYS@ora10g> conn zlm/zlm
Connected.
ZLM@ora10g> select count（*） from t1; COUNT（*）
----------
20ZLM@ora10g>

只要是正常关闭数据库的，并且开启了归档，那么即便是删除了current的在线日志，也是可以将数据库重新打开的，并且不会丢失数据（由归档来保证）测试3：非正常关闭数据库，并删除当前在线日志文件--添加测试数据后，abort方式关闭数据库
ZLM@ora10g> select group#,thread#,status,archived from v$log; GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 CURRENT NO
2 1 UNUSED YES
3 1 INACTIVE YESZLM@ora10g> insert into t1 select * from dba_objects where rownum<11;10 rows created.ZLM@ora10g> select count（*） from t1; COUNT（*）
----------
30ZLM@ora10g> alter system archive log current;System altered.ZLM@ora10g> select group#,thread#,status,archived from v$log; GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 ACTIVE YES
2 1 CURRENT NO
3 1 INACTIVE YESZLM@ora10g> shutdown abort
ORA-01031: insufficient privileges
ZLM@ora10g> conn / as sysdba
Connected.
SYS@ora10g> shutdown abort
ORACLE instance shut down.
SYS@ora10g>--OS上将current在线日志文件redo02.log删除
[oracle@ora10g ora10g]$ rm -f redo02.log
[oracle@ora10g ora10g]$ ls -l redo*
-rw-r----- 1 oracle oinstall 52429312 Jun 24 11:16 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 24 11:08 redo03.log--再次启动数据库
SYS@ora10g> startup
ORACLE instance started.Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 327155936 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: "/u01/app/oracle/oradata/ora10g/redo02.log"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3SYS@ora10g> 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 ora10g （thread 1）
ORA-00312: online log 2 thread 1: "/u01/app/oracle/oradata/ora10g/redo02.log"SYS@ora10g> recover database until cancel;
ORA-00279: change 1497127 generated at 06/24/2015 11:08:48 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2015_06_24/o1_mf_1_2_%u_.arc
ORA-00280: change 1497127 for thread 1 is in sequence #2Specify 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: "/u01/app/oracle/oradata/ora10g/system01.dbf"ORA-01112: media recovery not startedSYS@ora10g>之前使用的方法，在此处都不成立，原因是数据库强制要求current的在线日志文件需要crash recover，重新创建自然是不行的，由于是非正常关闭数据库，也没有写入归档文件，当然也无法进行还原，那么此时只有通过别的方法来将数据库OPEN了，当然丢失数据已经是不可避免的了，也就是刚才current在线日志文件中未归档的那部分，即t1表中最后插入的10条记录，权衡利弊，即便是丢数据，也要先将数据库拉起来，那么可以通过设置隐含参数"_allow_resetlogs_corruption=true"来实现SYS@ora10g> create pfile from spfile;File created.SYS@ora10g> show parameter spfileNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileora10g.ora
SYS@ora10g> host
[oracle@ora10g ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@ora10g dbs]$ ls -l
total 7412
-rwxr-xr-x 1 oracle oinstall 5494 Aug 26 2014 alert_ora10g.log
-rwxr-xr-x 1 oracle oinstall 1544 Aug 25 2014 hc_ora10g.dat
-rwxr-xr-x 1 oracle oinstall 12920 May 3 2001 initdw.ora
-rwxr-xr-x 1 oracle oinstall 8385 Sep 11 1998 init.ora
-rw-r--r-- 1 oracle oinstall 1343 Jun 24 11:24 initora10g.ora
-rwxr-xr-x 1 oracle oinstall 24 Aug 25 2014 lkORA10G
-rw-r----- 1 oracle oinstall 1536 Nov 26 2014 orapwora10g
-rwxr-xr-x 1 oracle oinstall 7520256 Jun 24 11:09 snapcf_ora10g.f
-rw-r----- 1 oracle oinstall 3584 Jun 24 11:19 spfileora10g.ora
[oracle@ora10g dbs]$ echo "_allow_resetlogs_corruption=true" >> initora10g.ora
[oracle@ora10g dbs]$ cat initora10g.ora
ora10g.__db_cache_size=192937984
ora10g.__java_pool_size=4194304
ora10g.__large_pool_size=4194304
ora10g.__shared_pool_size=155189248
ora10g.__streams_pool_size=8388608
*._disable_image_check=TRUE
*.audit_file_dest="/u01/app/oracle/admin/ora10g/adump"
*.background_dump_dest="/u01/app/oracle/admin/ora10g/bdump"
*.compatible="10.2.0.1.0"
*.control_files="/u01/app/oracle/oradata/ora10g/control01.ctl","/u01/app/oracle/oradata/ora10g/control02.ctl","/u01/app/oracle/oradata/ora10g/control03.ctl"#Restore Controlfile
*.core_dump_dest="/u01/app/oracle/admin/ora10g/cdump"
*.db_block_size=8192
*.db_cache_size=0
*.db_domain=""
*.db_file_multiblock_read_count=16
*.db_name="ora10g"
*.db_recovery_file_dest="/u01/app/oracle/flash_recovery_area"
*.db_recovery_file_dest_size=2147483648
*.dispatchers="（PROTOCOL=TCP）（SERVICE=ora10gXDB）"
*.java_pool_size=4194304
*.job_queue_processes=10
*.large_pool_size=4194304
*.log_archive_format="%t_%s_%r.dbf"
*.nls_language="SIMPLIFIED CHINESE"
*.nls_territory="CHINA"
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile="EXCLUSIVE"
*.sga_max_size=524288000
*.sga_target=369098752
*.shared_pool_size=0
*.statistics_level="TYPICAL"
*.streams_pool_size=8388608
*.undo_management="AUTO"
*.undo_tablespace="UNDOTBS1"
*.user_dump_dest="/u01/app/oracle/admin/ora10g/udump"
_allow_resetlogs_corruption
[oracle@ora10g dbs]$ vi initora10g.ora
[oracle@ora10g dbs]$ cat initora10g.ora
ora10g.__db_cache_size=192937984
ora10g.__java_pool_size=4194304
ora10g.__large_pool_size=4194304
ora10g.__shared_pool_size=155189248
ora10g.__streams_pool_size=8388608
*._disable_image_check=TRUE
*.audit_file_dest="/u01/app/oracle/admin/ora10g/adump"
*.background_dump_dest="/u01/app/oracle/admin/ora10g/bdump"
*.compatible="10.2.0.1.0"
*.control_files="/u01/app/oracle/oradata/ora10g/control01.ctl","/u01/app/oracle/oradata/ora10g/control02.ctl","/u01/app/oracle/oradata/ora10g/control03.ctl"#Restore Controlfile
*.core_dump_dest="/u01/app/oracle/admin/ora10g/cdump"
*.db_block_size=8192
*.db_cache_size=0
*.db_domain=""
*.db_file_multiblock_read_count=16
*.db_name="ora10g"
*.db_recovery_file_dest="/u01/app/oracle/flash_recovery_area"
*.db_recovery_file_dest_size=2147483648
*.dispatchers="（PROTOCOL=TCP）（SERVICE=ora10gXDB）"
*.java_pool_size=4194304
*.job_queue_processes=10
*.large_pool_size=4194304
*.log_archive_format="%t_%s_%r.dbf"
*.nls_language="SIMPLIFIED CHINESE"
*.nls_territory="CHINA"
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile="EXCLUSIVE"
*.sga_max_size=524288000
*.sga_target=369098752
*.shared_pool_size=0
*.statistics_level="TYPICAL"
*.streams_pool_size=8388608
*.undo_management="AUTO"
*.undo_tablespace="UNDOTBS1"
*.user_dump_dest="/u01/app/oracle/admin/ora10g/udump"
_allow_resetlogs_corruption=trueSYS@ora10g> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SYS@ora10g> shutdown abort
ORACLE instance shut down.
SYS@ora10g> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora
ORACLE instance started.Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 327155936 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database openSYS@ora10g> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced注意：此处的操作，在11g中，是可以直接用resetlogs方式OPEN数据库的，但是10g还需要做更多的处理SYS@ora10g> shutdown abort
ORACLE instance shut down.
SYS@ora10g> startup
ORACLE instance started.Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 327155936 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-03113: end-of-file on communication channelSYS@ora10g> shutdown abort
ORACLE instance shut down.
SYS@ora10g> startup mount pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora
ORACLE instance started.Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 327155936 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS@ora10g> create spfile from pfile;File created.SYS@ora10g> shutdown abort
ORACLE instance shut down.
SYS@ora10g> startup mount
ORACLE instance started.Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 327155936 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS@ora10g> show parameter spfileNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileora10g.ora
SYS@ora10g> alter database open;
alter database open
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error--查看此时alert日志，报ora-600错误 [4194]
Wed Jun 24 11:55:14 2015
Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_4112.trc:
ORA-00600: internal error code, arguments: [4194], [36], [33], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [36], [33], [], [], [], [], []
Wed Jun 24 11:55:42 2015
Errors in file /u01/app/oracle/admin/ora10g/bdump/ora10g_smon_4098.trc:
ORA-00600: Message 600 not found; No message file for product=RDBMS, facility=ORA; arguments: [4194] [36] [33]
Wed Jun 24 11:55:44 2015
Fatal internal error happened while SMON was doing active transaction recovery.
Wed Jun 24 11:55:44 2015
Errors in file /u01/app/oracle/admin/ora10g/bdump/ora10g_smon_4098.trc:
ORA-00600: Message 600 not found; No message file for product=RDBMS, facility=ORA; arguments: [4194] [36] [33]
SMON: terminating instance due to error 474
Instance terminated by SMON, pid = 4098通常ORA-00600 [4194]的错误会跟回滚段错误相关，继续查看trace文件/u01/app/oracle/admin/ora10g/bdump/orcl_smon_4098.trc，看到下述相关信息：
13121D07:00000B4B 15 159 10444 14 Acq rbs SYSTEM
13122395:00000B4C 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 0
131224AB:00000B4D 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 0
13124171:00000B4E 15 159 10444 14 Rec rbs _SYSSMU1$
1312455C:00000B4F 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 1
13131FE0:00000B50 15 159 10005 4 KSL POST SENT postee=6 loc="ksasnd" id1=0 id2=0 name= type=0
13133EDD:00000B59 15 159 10444 14 Rec rbs _SYSSMU2$
13134C80:00000B5A 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 2
13135B8B:00000B5B 15 159 10005 4 KSL POST SENT postee=6 loc="ksasnd" id1=0 id2=0 name= type=0
13136B7A:00000B5F 15 159 10444 14 Rec rbs _SYSSMU3$
13136EA7:00000B60 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 3
13137438:00000B61 15 159 10005 4 KSL POST SENT postee=6 loc="ksasnd" id1=0 id2=0 name= type=0
13138A0D:00000B67 15 159 10444 14 Rec rbs _SYSSMU4$
13138FA8:00000B68 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 4
13139598:00000B69 15 159 10005 4 KSL POST SENT postee=6 loc="ksasnd" id1=0 id2=0 name= type=0
13139B3C:00000B6A 15 159 10444 14 Rec rbs _SYSSMU5$
13139E47:00000B6B 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 5
1313B8FB:00000B71 15 159 10444 14 Rec rbs _SYSSMU6$
1313BE12:00000B72 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 6
1313C276:00000B73 15 159 10005 4 KSL POST SENT postee=6 loc="ksasnd" id1=0 id2=0 name= type=0
1313C536:00000B74 15 159 10444 14 Rec rbs _SYSSMU7$
1313CAEA:00000B75 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 7
1313D804:00000B76 15 159 10444 14 Rec rbs _SYSSMU8$
1313E48F:00000B7C 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 8
1313E9E3:00000B7D 15 159 10005 4 KSL POST SENT postee=6 loc="ksasnd" id1=0 id2=0 name= type=0
1313ECF3:00000B7E 15 159 10444 14 Rec rbs _SYSSMU9$
1313F050:00000B7F 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 9
1313F7F2:00000B80 15 159 10444 14 Rec rbs _SYSSMU10$
1313FEED:00000B81 15 159 10444 13 UNDO SEG （BEFORE RECOVERY）: usn = 10SYS@ora10g> select * from v$rollname;
ERROR:
ORA-03114: not connected to ORACLESYS@ora10g> conn / as sysdba;
Connected to an idle instance.
SYS@ora10g> startup mount
ORACLE instance started.Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 327155936 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS@ora10g> select * from v$rollname;
select * from V$rollname
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views onlySYS@ora10g> select segment_name from dba_rollback_segs;
select segment_name from dba_rollback_segs
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only此时通过修改pfile参数文件，然后将undo_management="AUTO"改为undo_management="MANUAL";
并添加隐含参数"_corrupted_rollback_segments"，即添加以下内容到pfile文件中：
_corrupted_rollback_segments="_SYSSMU1$","_SYSSMU2$","_SYSSMU3$","_SYSSMU4$","_SYSSMU5$","_SYSSMU6$","_SYSSMU7$","_SYSSMU8$","_SYSSMU9$","_SYSSMU10$"目的是将undo管理方式改为手动，并标记这10个默认的回滚段为损坏，然后再用该pfile启动数据库SYS@ora10g> shutdown abort
ORACLE instance shut down.
SYS@ora10g> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora
ORACLE instance started.Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 327155936 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ora10g>SYS@ora10g> conn zlm/zlm
Connected.
ZLM@ora10g> select count（*） from t1; COUNT（*）
----------
20此时数据库已经能够打开，连接到非系统用户zlm，确认新增的10条数据已经丢失ZLM@ora10g> select * from v$rollname; USN NAME
---------- ------------------------------
0 SYSTEMZLM@ora10g> select segment_name from dba_rollback_segs;SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$11 rows selected.由于数据库已经正常open,现在回滚段现在已经可以查询到了还需要将原undo表空间删除并重建，否则还是会有问题，如:非系统用户无法使用系统回滚段ZLM@ora10g> insert into t1 select * from dba_objects where rownum<11;
insert into t1 select * from dba_objects where rownum<11
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace "ZLM"ZLM@ora10g> conn / as sysdba
Connected.
SYS@ora10g> insert into zlm.t1 select * from dba_objects where rownum<11;
insert into zlm.t1 select * from dba_objects where rownum<11
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace "ZLM"--重建UNDO表空间（先删除，后创建）
SYS@ora10g> show parameter undoNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@ora10g> set line 130 pages 130
SYS@ora10g> col file_name for a80
SYS@ora10g> col tablespace_name for a15
SYS@ora10g> select file_name,tablespace_name from dba_data_files;FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ---------------
/u01/app/oracle/oradata/ora10g/zlm01.dbf ZLM
/u01/app/oracle/oradata/ora10g/example01.dbf EXAMPLE
/u01/app/oracle/oradata/ora10g/users01.dbf USERS
/u01/app/oracle/oradata/ora10g/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/ora10g/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/ora10g/system01.dbf SYSTEM6 rows selected.SYS@ora10g> drop tablespace undotbs1 including contents and datafiles;Tablespace dropped.SYS@ora10g> select file_name,tablespace_name from dba_data_files;FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ---------------
/u01/app/oracle/oradata/ora10g/zlm01.dbf ZLM
/u01/app/oracle/oradata/ora10g/example01.dbf EXAMPLE
/u01/app/oracle/oradata/ora10g/users01.dbf USERS
/u01/app/oracle/oradata/ora10g/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/ora10g/system01.dbf SYSTEMSYS@ora10g> create undo tablespace undotbs1 datafile "/u01/app/oracle/oradata/ora10g/undotbs01.dbf" size 50m reuse autoextend on next 10m;Tablespace created.--关闭数据库，将pfile中增加的参数去除，并改回undo_management="AUTO"后，用pfile启动数据库
SYS@ora10g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora10g> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora
ORACLE instance started.Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 327155936 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ora10g> create spfile from pfile;File created.SYS@ora10g> show parameter spfileNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SYS@ora10g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora10g> startup
ORACLE instance started.Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 327155936 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ora10g> show parameter spfileNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileora10g.ora
SYS@ora10g> conn zlm/zlm
Connected.
ZLM@ora10g> select count（*） from t1; COUNT（*）
----------
20ZLM@ora10g> insert into t1 select * from dba_objects where rownum<11;10 rows created.ZLM@ora10g> select count（*） from t1; COUNT（*）
----------
30ZLM@ora10g>最后用修改完的pfile再创建spfile并启动数据库，非系统用户已经能够使用系统回滚段来进行DML事务操作了总结：
只要非当前的在线日志文件内容未丢失（开启归档，并正常关闭数据库）的情况下，数据库的数据就不会丢失（当然，归档文件也被删除的例外），非在线的可以通过CLEAR重新创建，在线的只要是正常关闭的，就会被写到归档文件中去，通过RECOVER DATABASE UNTIL CANCEL就可以还原数据，最坏的情况就是current的在线日志在数据库意外关闭的情况下丢失，这种情况丢数据在所难免，如果丢数据的范围是可接受的，那么可以通过设置_allow_resetlogs_corruption=true，就可以强行OPEN数据库，但是会存在一定的问题，11g通过open resetlogs应该是可以直接OPEN数据库的，打开后要对数据库做一个全备，而10g通过该隐含参数OPEN数据库后，会遭遇到ORA-600 [4194]的错误，需要设置undo_management=manual,并通过隐含参数_corrupted_rollback_segments="_SYSSMU1$",...,"_SYSSMU10$"将系统回滚段设置为损坏，并重建默认的UNDO表空间后，数据库才能OPEN并正常使用，同样地，打开库以后第一件事就是对数据库做一个完备。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址