断电导致数据库不一致

转载：http://blog.sina.com.cn/s/blog_4cef5c7b01016efu.html

---

断电导致数据库文件不一致，可能是controlfile，也可能是system数据文件报错，无备份，非归档

1、首先查看alter.log ，查看报错信息，如果是redo不一致可以尝试加，_ALLOW_RESETLOGS_CORRUPTION = TRUE 隐含参数试试

2、看到控制文件不一致时，首先是将control02.ctl 拷贝为 control01.ctl，使两份控制文件一致，启动还是报错，报system数据文件问题

 

一、尝试启动

SQL> show parameter _ALLOW_RESETLOGS_CORRUPTION

SQL> recover database;

 

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using  BACKUP CONTROLFILE;

ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1

ORA-00289: suggestion :

/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc

ORA-00280: change 2433115 for thread 1 is in sequence #42

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log

'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

ORA-00308: cannot open archived log

'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> recover database using  BACKUP CONTROLFILE;

ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1

ORA-00289: suggestion :

/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc

ORA-00280: change 2433115 for thread 1 is in sequence #42

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'

显示表空间需要修复

SQL> recover datafile 1;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using  BACKUP CONTROLFILE until cancel;

ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1

ORA-00289: suggestion :

/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc

ORA-00280: change 2433115 for thread 1 is in sequence #42

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: '/opt/oracle/oradata/oradb/bak/system01.dbf'

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'

仍然需要恢复表空间

SQL> recover datafile 1;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

二、加入隐含参数启动

加入隐含参数！，关闭重启（数据库正常启动后，把该参数还改回false）

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

SQL> shutdown immediate

sqlplus / as sysdba


SQL> startup mount

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1

ORA-00289: suggestion :

/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc

ORA-00280: change 2433115 for thread 1 is in sequence #42

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log

'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

ORA-00308: cannot open archived log

'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

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: '/opt/oracle/oradata/oradb/bak/system01.dbf'

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;

此时，系统好像hang住了一样，很长一段时间没有反映，过了一会，报错如下！

alter database open RESETLOGS

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

此时查看alter日志信息：又出现2662错误，

Errors in file /opt/oracle/admin/oradb/udump/oradb_ora_10369.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2433141], [0], [2443300], [12595947], [], []

Fri Jan 18 15:58:21 2008

Errors in file /opt/oracle/admin/oradb/udump/oradb_ora_10369.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2433141], [0], [2443300], [12595947], [], []

Fri Jan 18 15:58:21 2008

Error 600 happened during db open, shutting down database

USER: terminating instance due to error 600

Instance terminated by USER, pid = 10369

ORA-1092 signalled during: alter database open RESETLOGS...

 

再次重启登录

SQL> startup

ORACLE instance started.

Total System Global Area  616562688 bytes

Fixed Size                  1220868 bytes

Variable Size             167775996 bytes

Database Buffers          440401920 bytes

Redo Buffers                7163904 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'

SQL> recover database;
Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

检查日志如下：爽呀，又出现了ORA-00600 中的4194错误，

Database Characterset is UTF8

Fri Jan 18 16:00:14 2008

Errors in file /opt/oracle/admin/oradb/udump/oradb_ora_10443.trc:

ORA-00600: internal error code, arguments: [4194], [38], [34], [], [], [], [], []

Fri Jan 18 16:00:14 2008

Errors in file /opt/oracle/admin/oradb/bdump/oradb_smon_10429.trc:

ORA-00600: internal error code, arguments: [4194], [59], [57], [], [], [], [], []

 

4149错误一般根undo有关系，因为当前redo损坏，undo数据也大都不一致了。

设置隐含参数：
将undo改变成手工管理的，然后重启数据库。

SQL> startup mount
SQL> show parameter undo

SQL> alter system set undo_management='manual' scope=spfile;

SQL> shutdown immediate

SQL> quit


SQL> startup mount

SQL> alter database open

成功开库

三、创建新的自动管理的undo表空间 

因为设置了undo手动管理，update数据库时会出现问题，重新创建自动管理undo表空间，并指定新的undo表空间

show parameter undo

create undo tablespace undotbs2  datafile '/opt/oracle/oradata/orcl/undotbs02.dbf' size 500M autoextend on next 10m maxsize unlimited;

alter system set undo_tablespace=undotbs2;

drop tablespace undotbs1 including contents and datafiles;

总结：

系统掉电一般引起redo损坏，最糟糕的就是引起当前redo损坏，针对当前redo损坏，又没有备份，数据库处于非归档模式时，只能通过加入隐含参数方式恢复！

一般步骤如下：

1：首先加入_ALLOW_RESETLOGS_CORRUPTION = TRUE隐含参数，最后resetlogs打开数据库，如果无法打开，进入第二步。

 

2：查看日志信息，根据具体报错信息，确认方法，本例是出现ORA-00600: internal error code, arguments: [2662], [0], [2433141], [0], [2443300], [12595947], [], []，

ORA-600 [2662] "Block SCN is ahead of Current SCN"，说明当前数据库的数据块的SCN早于当前的SCN，主要是和存储在UGA变量中的dependent SCN进行比较，如果当前的SCN小于它，数据库就会产生这个ORA-600 [2662]的错误了.于是想到使用ADJUST_SCN事件来调整当前的SCN，使其大于dependent SCN. 此时我们可以通过Oracle的内部事件来调整SCN:

解决这个问题一般方法是：
调整SCN有两种常用方法:
1.通过immediate trace name方式(在数据库Open状态下，即通过用_allow_resetlogs_corruption=TRUE隐含参数打开数据库之后报错600)
alter session set events 'IMMEDIATE trace name ADJUST_SCN level x';
2.通过10015事件(在数据库无法打开，mount状态下)
alter session set events '10015 trace name adjust_scn level x';
注:level 1为增进SCN 10亿 (1 billion) (1024*1024*1024),通常Level 1已经足够。也可以根据实际情况适当调整。

 

本文通过此种方法设置无效，scn没有得到提升！

 

3：一般情况下redo损坏的时候，undo数据也大都不一致，因此通过scn调整完毕，resetlogs数据库后还会出现ORA-00600: internal error code, arguments: [4194]错误，这个错误可以通过设置undo解决：

设置隐含参数：_corrupted_rollback_segments
将undo改变成手工管理的，然后重启数据库，

本例只是将undo改变成手工管理，问题解决。

---