Oracle 11g数据库异常中断时在线重做日志或回滚表空间时的恢复

1. 在线重做日志文件损坏：在打开数据库时报在线日志文件不存在。
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DATA/dbs10/onlinelog/group_1.261.750979135'
ORA-17503: ksfdopn:2 Failed to open file
+DATA/dbs10/onlinelog/group_1.261.750979135
ORA-15012: ASM file '+DATA/dbs10/onlinelog/group_1.261.750979135' does not
exist

通过v$log文件查看日志文件的状态：
select group#,sequence#,archived,status from v$log;

尝试增加三组redo log文件，再删除现有的redo log文件
ALTER DATABASE
  ADD LOGFILE GROUP 5
      SIZE 50M;
      
ALTER DATABASE
  ADD LOGFILE GROUP 6
      SIZE 50M;
      
ALTER DATABASE
  ADD LOGFILE GROUP 7
      SIZE 50M;

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;


SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance dbs101 (thread 1)
ORA-00312: online log 1 thread 1: '+DATA/dbs10/onlinelog/group_1.261.750979135'


SQL> c /1/2
  1* alter database drop logfile group 2
SQL> /
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance dbs101 (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '+DATA/dbs10/onlinelog/group_2.262.750979139'

由于logfile group 1和logfile group 2不能被删除。只能使用隐含参数：
_allow_resetlogs_corruption=true

SQL> startup mount pfile=initdbs101.ora
ORACLE instance started.

Total System Global Area  318046208 bytes
Fixed Size                  1343916 bytes
Variable Size             192941652 bytes
Database Buffers          117440512 bytes
Redo Buffers                6320128 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DATA/dbs10/onlinelog/group_1.261.750979135'
ORA-17503: ksfdopn:2 Failed to open file
+DATA/dbs10/onlinelog/group_1.261.750979135
ORA-15012: ASM file '+DATA/dbs10/onlinelog/group_1.261.750979135' does not
exist

执行恢复命令：
SQL> recover database until cancel;
ORA-00279: change 2968119 generated at 06/06/2011 01:47:09 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_109_750979134.dbf
ORA-00280: change 2968119 for thread 1 is in sequence #109


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-10879: error signaled in parallel recovery slave
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: '+DATA/dbs10/datafile/system.256.750978803'

用open resetlogs打开数据库
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2968128], [0],
[2968179], [4194432], [], [], [], [], [], []
Process ID: 12670
Session ID: 16 Serial number: 3

出现ora-00600错误，参数是2662，这表明SCN不一致。用下列语句调整scn。
SQL> alter session set events '10015 trace name adjust_scn level 2'

Session altered.

终于可以打开数据库：
SQL> alter database open;

Database altered.

SQL>

清楚重做在线log文件
SQL> select group#,sequence#,archived,status from v$log;

    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         1          1 NO  INACTIVE
         2          2 NO  CURRENT
         5          0 YES UNUSED
         6          0 YES UNUSED
         7          0 YES UNUSED

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

成功切换每一个redo log文件。再用hcheck.sql来检查数据词典。
SQL> @hcheck.sql
Package created.

No errors.

Package body created.

No errors.
HCheck Version 8i-11/2.00

Problem:  SEG$ bad LISTS/GROUPS (==1) - See Tar:2470806.1
May be Ok for LOBSEGMENT/SECUREFILE in release 11gR1+
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=26161 TYPE#=8 Lists=2 Groups=1
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=26217 TYPE#=8 Lists=2 Groups=1
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=26673 TYPE#=8 Lists=2 Groups=1
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=26713 TYPE#=8 Lists=2 Groups=1
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=28161 TYPE#=8 Lists=2 Groups=1
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=28249 TYPE#=8 Lists=2 Groups=1

Found 6 potential problems
Contact Oracle Support with the output
to check if the above needs attention or not

PL/SQL procedure successfully completed.

从检查结果看，有6个Bad SEG$

SQL> @getobjectbyfileblock.sql
Enter file number: 2
Enter block number: 26161
old   3: where file_id = &m_fileno
new   3: where file_id = 2
old   4:                        and &m_blockno between block_id and block_id + blocks - 1
new   4:                        and 26161 between block_id and block_id + blocks - 1

SEGMENT_NAME
---------------------------------------------------------------------------------
SYS_LOB0000064701C00004$$

这些Bad SEG$是lob段，所以这不是问题。

2. 回滚段表空间损坏：在打开数据库时报回滚段表空间文件不存在。
SQL> startup
ORACLE instance started.

Total System Global Area  318046208 bytes
Fixed Size                  1343916 bytes
Variable Size             192941652 bytes
Database Buffers          117440512 bytes
Redo Buffers                6320128 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '+DATA/dbs10/datafile/undotbs1.258.750978805'

根据10g和以前版本，如果回滚段有活动的事务，数据库将不能打开。但是在11g下，我们可以
先删除回滚段表空间文件。然后打开数据库。
SQL> alter database datafile '+DATA/DBS10/DATAFILE/UNDOTBS1.258.750978805' offline drop;

Database altered.

SQL> alter database open;

Database altered.

因为有活动的数据是依赖undotbs1的，所以要先建一个回滚段表空间。
SQL> create undo tablespace undotbs2 datafile size 400m;

Tablespace created.

SQL> alter system set undo_tablespace = undotbs2 scope=spfile;

System altered.

SQL> c /spfile/both
  1* alter system set undo_tablespace = undotbs2 scope=both
SQL> /

System altered.

然后清除以前的undo表空间，发现有活动的回滚段。
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_2081786551$' found, terminate
dropping tablespace

Errors in file /u01/app/oracle/diag/rdbms/dbs10/dbs101/trace/dbs101_cjq0_26517.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '+DATA/dbs10/datafile/undotbs1.258.750978805'

通过下列语句找出所有的活动的回滚段
SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU44_1395808335$          OFFLINE
_SYSSMU43_3869177594$          OFFLINE
_SYSSMU42_867897250$           OFFLINE
_SYSSMU41_1452998875$          OFFLINE
_SYSSMU40_933238269$           OFFLINE
_SYSSMU39_3562224729$          OFFLINE
_SYSSMU38_2870321593$          OFFLINE
_SYSSMU37_3969440647$          OFFLINE
_SYSSMU36_105869933$           OFFLINE
_SYSSMU35_2735686354$          OFFLINE

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU34_3251807999$          OFFLINE
_SYSSMU33_4284371207$          OFFLINE
_SYSSMU32_3263887734$          OFFLINE
_SYSSMU31_1927446693$          OFFLINE
_SYSSMU30_1066154611$          OFFLINE
_SYSSMU29_570456662$           OFFLINE
_SYSSMU28_2267970705$          OFFLINE
_SYSSMU27_411017042$           OFFLINE
_SYSSMU26_2131622431$          OFFLINE
_SYSSMU25_2776655453$          OFFLINE
_SYSSMU24_3686259686$          OFFLINE

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU23_627091026$           OFFLINE
_SYSSMU22_3885878544$          OFFLINE
_SYSSMU21_206695975$           OFFLINE
_SYSSMU20_1283926875$          OFFLINE
_SYSSMU19_1430735543$          OFFLINE
_SYSSMU18_2457174784$          OFFLINE
_SYSSMU17_805851657$           OFFLINE
_SYSSMU16_2878312875$          OFFLINE
_SYSSMU15_2333014389$          OFFLINE
_SYSSMU14_134819161$           OFFLINE
_SYSSMU13_1500115710$          OFFLINE

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU12_1496645057$          OFFLINE
_SYSSMU11_1497308259$          OFFLINE
_SYSSMU10_2606106477$          NEEDS RECOVERY  --活动的回滚段
_SYSSMU9_3975004876$           NEEDS RECOVERY
_SYSSMU8_286634474$            NEEDS RECOVERY
_SYSSMU7_574775315$            NEEDS RECOVERY
_SYSSMU6_3013749843$           NEEDS RECOVERY
_SYSSMU5_3147209834$           NEEDS RECOVERY
_SYSSMU4_562970181$            NEEDS RECOVERY
_SYSSMU3_1483640053$           NEEDS RECOVERY
_SYSSMU2_3037561007$           NEEDS RECOVERY

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU54_3733785159$          ONLINE
_SYSSMU53_992393063$           ONLINE
_SYSSMU52_688238673$           ONLINE
_SYSSMU51_3390404512$          ONLINE
_SYSSMU50_1400133140$          ONLINE
_SYSSMU49_2898878127$          ONLINE
_SYSSMU48_1653808116$          ONLINE
_SYSSMU47_2050131453$          ONLINE
_SYSSMU46_84941823$            ONLINE
_SYSSMU45_1386642068$          ONLINE

54 rows selected.

SQL>

使用隐含参数标志所有活动的回滚段
_corrupted_rollback_segments=(_SYSSMU10_2606106477$,_SYSSMU9_3975004876$,_SYSSMU8_286634474$,_SYSSMU7_574775315$,_SYSSMU6_3013749843$,_SYSSMU5_3147209834$,_SYSSMU4_562970181$,_SYSSMU3_1483640053$,_SYSSMU2_3037561007$)

[oracle@dbs4 dbs]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 7 00:50:26 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile=initdbs101.ora
ORACLE instance started.

Total System Global Area  318046208 bytes
Fixed Size                  1343916 bytes
Variable Size             192941652 bytes
Database Buffers          117440512 bytes
Redo Buffers                6320128 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL>

成功删除了旧的undo表空间。

使用hcheck.sql来检查数据词典
SQL> @"E:\work\others\tom\doc\Backup and Recovery\hcheck.sql"

Package created.

No errors.

Package body created.

No errors.
HCheck Version 8i-11/2.00

Problem:  SEG$ bad LISTS/GROUPS (==1) - See Tar:2470806.1
May be Ok for LOBSEGMENT/SECUREFILE in release 11gR1+
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=26161 TYPE#=8 Lists=2 Groups=1
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=26217 TYPE#=8 Lists=2 Groups=1
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=26673 TYPE#=8 Lists=2 Groups=1
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=26713 TYPE#=8 Lists=2 Groups=1
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=28161 TYPE#=8 Lists=2 Groups=1
Bad SEG$ lists/groups : TS#=1 RFILE#=2 BLK#=28249 TYPE#=8 Lists=2 Groups=1

Found 6 potential problems
Contact Oracle Support with the output
to check if the above needs attention or not

PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25105315/viewspace-697245/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25105315/viewspace-697245/