过程记录：
1、根据报错提示：
Errors in file /home/oracle/app/oracle/product/9.2.0/admin/sdh/bdump/sdh_lgwr_19099.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/sybdata/sdh/sdh/redo01.log'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
去相应目录检查，/sybdata/sdh/sdh/目录空了，三个redolog文件全部丢失？--谁干的？！
只能考虑强行打开数据库。
=======================================================================================
2、检查redolog组的状态：
SQL> col member for a50
select a.member,a.group#,b.thread#,b.bytes/1024/1024 "Log_file_Size(M)",b.members,b.status
from v$logfile a,v$log b
where a.group#=b.group#;
SQL> 2 3
MEMBER GROUP# THREAD# Log_file_Size(M) MEMBERS STATUS
-------------------------------------------------- ---------- ---------- ---------------- ---------- --------------------------------
/sybdata/sdh/sdh/redo01.log 1 1 100 1 UNUSED
/sybdata/sdh/sdh/redo02.log 2 1 100 1 INACTIVE
/sybdata/sdh/sdh/redo03.log 3 1 100 1 INVALIDATED
========================================================================================
3、将redolog1、2drop重建，但是redolog3是当前的redolog，无法删除重建；
========================================================================================
4、希望能把redolog3给clear掉，失败：
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of thread 1
ORA-00312: online log 3 thread 1: '/sybdata/sdh/sdh/redo03.log'
===========================================================================
5、试试recover database until cancel，失败:
SQL> recover database until cancel;
ORA-00279: change 9056210129192 generated at 10/28/2009 13:10:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/app/oracle/product/9.2.0/dbs/arch1_32720.dbf
ORA-00280: change 9056210129192 for thread 1 is in sequence #32720
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/home/oracle/app/oracle/product/9.2.0/dbs/arch1_32720.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/home/oracle/app/oracle/product/9.2.0/dbs/arch1_32720.dbf'
ORA-27037: unable to obtain file status
HP-UX 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: '/oradata/sdh/sdh/system01.dbf'
SQL> recover database until cancel;
ORA-00279: change 9056210129192 generated at 10/28/2009 13:10:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/app/oracle/product/9.2.0/dbs/arch1_32720.dbf
ORA-00280: change 9056210129192 for thread 1 is in sequence #32720
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/home/oracle/app/oracle/product/9.2.0/dbs/arch1_32720.dbf'
ORA-27037: unable to obtain file status
HP-UX 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: '/oradata/sdh/sdh/system01.dbf'
SQL> recover database until cancel;
ORA-00279: change 9056210129192 generated at 10/28/2009 13:10:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/app/oracle/product/9.2.0/dbs/arch1_32720.dbf
ORA-00280: change 9056210129192 for thread 1 is in sequence #32720
Specify log: {=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: '/oradata/sdh/sdh/system01.dbf'
ORA-01112: media recovery not started
=============================================================================================
6、再试试alter database open resetlogs,失败：
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: '/oradata/sdh/sdh/system01.dbf'
=============================================================================================
7、在pfile中加入隐藏参数：allow_resetlogs_corruption=TRUE，通过pfile将数据库起到mount状态，还是无法打开数据库，看到告警日志有报错：
ORA-00600: internal error code, arguments: [2256], [0], [3221225472], [2108], [3880116460], [], [], []
=============================================================================================
8、查到SCN号已经通过Oracle的内部事件来调整SCN，先查一下当前的SCN号：
SQL> select file#,CHECKPOINT_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 9.0562E+12
2 9.0562E+12
3 9.0562E+12
4 9.0562E+12
5 9.0562E+12
6 9.0562E+12
7 9.0562E+12
8 9.0562E+12
9 9.0562E+12
10 9.0562E+12
11 9.0562E+12
FILE# CHECKPOINT_CHANGE#
---------- ------------------
12 9.0562E+12
13 9.0562E+12
14 9.0562E+12
15 9.0562E+12
16 9.0562E+12
17 9.0562E+12
18 9.0562E+12
19 9.0562E+12
20 9.0562E+12
20 rows selected.
SCN号大得相当惊人，于是这么来调整：
alter session set event '10015 trace name adjust_scn level 8436';
level 24 数值的计算是通过ora-00600 报错信息计算出来的。(2108+1)*4 =24，（第四个数+1）*4。
这么一调整，数据库还是无法打开。
报错：ORA-00600: internal error code, arguments: [4193], [1248], [1252], [], [], [], [], []
=============================================================================================
9、这个报错表示undo也有问题，在mount状态下把undotbs对应的数据文件给drop掉，再打开数据库：
alter database datafile '/oradata/sdh/sdh/undotbs01.dbf' offline drop;
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------------------
OPEN
10、数据库打开后，新建了一个undotbs02，将默认的undotbs指向了新建的undotbs02。
一切正常。
阅读(2699) | 评论(0) | 转发(0) |
