程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> 刪除online日志測試及ora-600[4194]錯誤的處理

刪除online日志測試及ora-600[4194]錯誤的處理

編輯：DB2教程

刪除online日志測試及ora-600[4194]錯誤的處理

今天做了一個關於破壞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並正常使用，同樣地，打開庫以後第一件事就是對數據庫做一個完備。