机器异常断电,oracle不能正常启动,可以mount,不能open。数据库未备份过。
错误码:
At startup, the database will mount, but gives the following errors at open:
ORA-00312: "online log %s thread %s: '%s'"
Cause: This message reports the filename for details of another message.
Action: Other messages will accompany this message. See the
associated messages for the appropriate action to take.
ORA-00322: "log %s of thread %s is not current copy"
Cause: Check of log file header at database open found that an online log
appears to be an incorrectly restored backup.
Action: Restore correct file or reset logs.
Recover the loss of an inactive, online redo log group.
SQL>ALTER DATABASE CLEAR LOGFILE 'filename';
If there is more than one LOGFILE in the redo log group then you must
specify all the log files.
For example, the following fails:
SVRMGR> alter database clear logfile '/vobs/oracle/dbs/log3.log';
alter database clear logfile '/vobs/oracle/dbs/log3.log'
*
ORA-1514: error in log specification: no such log
ORA-1517: log member: '/vobs/oracle/dbs/log3.log
But, specifying all the log files in the group works:
SVRMGR> ALTER DATABASE CLEAR LOGFILE ('/vobs/oracle/dbs/t4.log','/vobs/oracle/;
Statement processed.
介绍一个意外情况,如果上面的方法不成功,数据库还是不能open,采用recover,restore都不成功,说明logfile可能已经损坏,数据库打开需要做一致性检查,所以不能正常打开。因为数据库未作过备份,为了尽可能地减少损失,采用打开数据库,然后立刻导出数据的方法。
oracle有一个不推荐的方法,可以使数据库在不进行一致性检查的方式下打开数据库。
做法:
1。为保险起见,将数据库的系统表空间,数据文件,控制文件均做一次备份:copy。
2。在初始化配置文件中(init.ora)加上:
_allow_resetlogs_corruption=true
_corrupted_rollback_segments=(将所有rollback_segments=(...)的内容加到这里)
将rollback_segments=(...)前加上#
3。startup mount
recover database until cancel;
cancel
alter database open resetlogs;
此时数据库已经可以打开了,请立刻将数据export出来,然后重新安装oracle,重新建立database,再将数据import。
注意:这种方法属于数据库的不完全恢复,最后提交的sql可能会丢失,毕竟能够尽可能地减少损失,可以试试。
以上操作我均做过测试。
16:00:10 SQL> VARIABLE v_cur REFCURSOR;
16:03:21 SQL> BEGIN
16:03:26 2 OPEN :v_cur FOR SELECT * FROM TB;
16:03:44 3 END;
16:03:47 4 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.93
16:03:48 SQL> PRINT v_cur;
COL1 COL2 COLNEW
---------- ------------------------------ --------------------
A aaa aaa
A aab aab
A aac aac
B bba bba
B bbb bbb
B bbc bbc
B bbd bbd
C ccc ccc
c TEMP TEMP
d ddd ddd
e eee eee