删除online日志测试及ora-600 [4194]错误的处理，ora-6004194
今天做了一个关于破坏online日志的恢复测试，主要三个场景： 测试1：正常关闭数据库后删除非当前日志 测试2：正常关库后，删除在线日志文件 测试3：非正常关闭数据库，并删除当前在线日志文件我的测试环境是Oracle 10.2.0.1 32bit的数据库，OS版本为red hat 5.3，下面看具体测试经过：
测试1：正常关闭数据库后删除非当前日志
[oracle@ora10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 6月 24 10:34:53 2015
Copyright (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 options
SYS@ora10g> select group#,thread#,status,archived from v$log;
GROUP# THREAD# STATUS ARC ---------- ---------- ---------------- --- 1 1 CURRENT NO 2 1 INACTIVE YES 3 1 ACTIVE YES
SYS@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 NO
SYS@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(*) ---------- 10
ZLM@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 ---------- MOUNTED
SYS@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(*) ---------- 10
ZLM@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 YES
ZLM@ora10g> insert into t1 select * from dba_objects where rownum<11;
10 rows created.
ZLM@ora10g> select count(*) from t1;
COUNT(*) ---------- 20
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 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(*) ---------- 20
ZLM@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 YES
ZLM@ora10g> insert into t1 select * from dba_objects where rownum<11;
10 rows created.
ZLM@ora10g> select count(*) from t1;
COUNT(*) ---------- 30
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
ZLM@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: 3
SYS@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 #2
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: '/u01/app/oracle/oradata/ora10g/system01.dbf'
ORA-01112: media recovery not started
SYS@ora10g>
之前使用的方法，在此处都不成立，原因是数据库强制要求current的在线日志文件需要crash recover，重新创建自然是不行的，由于是非正常关闭数据库，也没有写入归档文件，当然也无法进行还原，那么此时只有通过别的方法来将数据库OPEN了，当然丢失数据已经是不可避免的了，也就是刚才current在线日志文件中未归档的那部分，即t1表中最后插入的10条记录，权衡利弊，即便是丢数据，也要先将数据库拉起来，那么可以通过设置隐含参数"_allow_resetlogs_corruption=true"来实现
SYS@ora10g> create pfile from spfile;
File created.
SYS@ora10g> show parameter spfile
NAME 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=true
SYS@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 open
SYS@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 channel
SYS@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 spfile
NAME 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 = 10
SYS@ora10g> select * from v$rollname; ERROR: ORA-03114: not connected to ORACLE
SYS@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 only
SYS@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 SYSTEM
ZLM@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 undo
NAME 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 SYSTEM
6 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 SYSTEM
SYS@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 spfile
NAME 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 spfile
NAME 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(*) ---------- 20
ZLM@ora10g> insert into t1 select * from dba_objects where rownum<11;
10 rows created.
ZLM@ora10g> select count(*) from t1;
COUNT(*) ---------- 30
ZLM@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并正常使用，同样地，打开库以后第一件事就是对数据库做一个完备。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。