昨天有一哥们把日志文件给清了,现在数据库起不来了,唉...哪位大侠知道该咋办呀???? SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started. Total System Global Area 3340451840 bytes
Fixed Size 2149000 bytes
Variable Size 2013267320 bytes
Database Buffers 1308622848 bytes
Redo Buffers 16412672 bytes
Database mounted.
ORA-00320: cannot read file header from log 2 of thread 1
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ora11/redo02.log'
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
SQL> select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARCHIVED STATUS
---------- ---------- --------- ------------------------------------------------
2 1694 NO CURRENT
1 1693 NO INACTIVE
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started. Total System Global Area 3340451840 bytes
Fixed Size 2149000 bytes
Variable Size 2013267320 bytes
Database Buffers 1308622848 bytes
Redo Buffers 16412672 bytes
Database mounted.
ORA-00320: cannot read file header from log 2 of thread 1
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ora11/redo02.log'
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
SQL> select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARCHIVED STATUS
---------- ---------- --------- ------------------------------------------------
2 1694 NO CURRENT
1 1693 NO INACTIVE
解决方案 »
- 安装oracle10g时出了个问题,实在解决不了了
- 请教,以下语句我在一个示例中执行不会有问题,但是在两个库中就会报错,是不是左连的问题?是的话,如何解决?
- oracle同时读取clob字段和其他字段的sql语句问题
- sql中截取字符串的问题(急)
- 分区表如何根据ID尾数分区
- 如何在一個select语句產生的結果的最前面添加一個空行??(用)sql實現
- 散分,散分,刚学Oracle,会个面!
- 哪位有Oracle的函数库,要中文的
- 在c#中oracle做为数据库,varchar2的问题
- redo buffer的待机数不断增加,db buffer的击中率不到20%???我该怎么办
- oracle 数据库恢复问题?
- 高分请教一下,oracle中的所说"数据库"和mssql中所说的"数据库"到底有何不同?
利用addlog新建日志
conn / as sysdba;
startup mount;
recover database until cancel;
alter database open resetlogs;
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/ORA11/archivelog/2009_06_11/o1_mf_1_1694_%u_.arc
ORA-00280: change 83957998 for thread 1 is in sequence #1694
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/opt/oracle/flash_recovery_area/ORA11/archivelog/2009_06_11/o1_mf_1_1694_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux-x86_64 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/ora11/system01.dbf'
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/ORA11/archivelog/2009_06_11/o1_mf_1_1694_%u_.arc
ORA-00280: change 83957998 for thread 1 is in sequence #1694
Specify log: { <RET>=suggested | filename | AUTO | CANCEL} 在这里输入cancel
先到mount状态。SQL> recover database until cancel using backup controlfile;
到指定用redo来recovery的时候,输入cancel。因为你的redo文件已经没有了如果提示media recovery complete的话就成功了。然后
SQL>alter database open resetlogs;
SQL> recover database until cancel;
ORA-00279: change 84105402 generated at 06/12/2009 09:04:52 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/ORA11/archivelog/2009_06_12/o1_mf_1_8_%u_.arc
ORA-00280: change 84105402 for thread 1 is in sequence #8
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/ora11/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/ora11/system01.dbf'
1、 查看原来表中数据
SQL>; conn test/test
Connected.
SQL>; select * from test; TEL
----------
1
2
3
2、插入新数据
SQL>; insert into test values(4);
1 row created.
SQL>; commit;
Commit complete.
SQL>;
3、 正常关闭数据库
4、 利用os command删除所有redo文件
5、 启动数据库
SQL>; startup
ORACLE instance started.
Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
6、 查看当前日志状态
SQL>; select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
1 1 2 104857600 1 YES INACTIVE
487837 01-9月 -05 2 1 4 104857600 1 NO CURRENT
487955 01-9月 -05 3 1 3 104857600 1 YES INACTIVE
487839 01-9月 -05
看来redo01.log不是当前日志,对于这类非当前日志可以直接clear,系统会重新自动生成一个redo文件 7、SQL>; alter database clear logfile group 1;
Database altered.
7、 继续启动db
SQL>; alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
8、 看来redo也得恢复,但是redo02是当前redo,直接clear是不行的
SQL>; alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of thread 1 needs to be archived
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
尝试clear unarchived logfile group ,报错:
SQL>; alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
看来他是因为找不到这个文件,从有效的备份中cp一个过来看看
SQL>; host cp /T3/ORACLE/oradatabak/redo02* /T3/ORACLE/oradata/ORA9 SQL>; alter database clear unarchived logfile group 2; Database altered.
搞定………. 9、 按照oracle的某些做法也是可以的
SQL>; alter database clear unarchived logfile group 1 unrecoverable datafile; Database altered. 10、但是对于非当前日志就都可以,下面看看redo03
SQL>; alter database clear logfile group 3; Database altered. 结论:
如果数据库是正常shutdown,非当前日志都可以直接clear来重新生成,而且不丢失数据,因为正常关闭db,数据已经写入dbf文件了。唯独当前日志不可以,当前日志必须首先从有效的备份中拷贝一个日志文件过来,然后用
alter database clear unarchived logfile group n 或alter database clear unarchived logfile group n,除此之外,还可以用下面的方法来做 方法二:用cancel模式恢复数据库
前面的出错提示,步骤都一样,唯独恢复的方法不一样
SQL>; startup
ORACLE instance started.
Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
看看丢失了哪些redo
SQL>; host ls /T3/ORACLE/oradarta/ORA9/redo*
/T3/ORACLE/oradarta/ORA9/redo*: No such file or directory
看来redo都丢了
直接recover
本贴来自天极网群乐社区--http://q.yesky.com/group/review-6992095.html
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Current log sequence 8
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
shutdown immeidate;
startup mount;
recover database until cancel;
alter database open resetlogs;_allow_resetlogs_corruption强制启动数据库,设置此参数之后,在数据库Open过程中,Oracle会跳过某些一致性检查,从而使数据库可能跳过不一致状态,Open打开
非常不幸,这样的问题被你碰到了,根据我做的实验,一般情况下,失去logfile可以通过我和welyngj说介绍的方法来进行恢复的,但是,估计是你的机器没有正常的关机,比如说断电,或者直接关系统,造成了恢复不成功的问题。我在我自己的机器上通过shutdown abort的方式,和可以模拟出你的情况实验如下
一 可恢复状况
SQL> insert into test1.tt1 values(3,'111','');
SQL> insert into test1.tt1 values(4,'111','');
SQL> insert into test1.tt1 values(5,'111','');
SQL> commit;
SQL>shutdown immedaite;待shutdown后,删除掉redon.log恢复
SQL>startup mount;
SQL>recover database until cancel using backup controlfile;
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancel.SQL>alter database open resetlogs数据库打开,恢复成功
二 不可恢复状况
SQL> insert into test1.tt1 values(6,'111','');
SQL> insert into test1.tt1 values(7,'111','');
SQL> insert into test1.tt1 values(8,'111','');
SQL> commit;
SQL>shutdown abort;
拷贝redon.log到其他的地方。SQL>startup mount;
SQL>recover database until cancel using backup controlfile;
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancel.提示数据库不一致,已经无法恢复了(如果没有redo的备份的话)。由于我已经备份过redo了
所以我还是可以恢复我的database还是使用上面的方法,不过是到
SQL>recover database until cancel using backup controlfile;
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
这里
输入你备份的那个redo.log路径就可以了指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
F:\developer\oracle\product\10.2.0\oradata\REDO03.LOG
已应用的日志。
完成介质恢复。不过这里你没有redo的备份,还是可以通过设置隐含参数来启动数据库,
如welyngj所说的_allow_resetlogs_corruption设置为true
可以create pfile='init.ora的路径' from spfile; 把spfile导出到pfile后,在导出的init.ora里加入_allow_resetlogs_corruption=true然后从pfile启动
startup pfile='init.ora的路径';这时数据库可以起来,但是这个数据库是很危险的,把数据库的数据exp出来,重建数据库。
SQL> in mount mode ,execute:
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
SP2-0640: Not connected
SQL> connect /as sysdba
Connected.
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;System altered.SQL> shutdown immeidate;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediata
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.Total System Global Area 3340451840 bytes
Fixed Size 2149000 bytes
Variable Size 2013267320 bytes
Database Buffers 1308622848 bytes
Redo Buffers 16412672 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 84105402 generated at 06/12/2009 09:04:52 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/ORA11/archivelog/2009_06_12/o1_mf_1_8_%u_.arc
ORA-00280: change 84105402 for thread 1 is in sequence #8
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/ora11/system01.dbf'
ORA-01112: media recovery not started
非常不幸,这样的问题被你碰到了, 根据我做的实验,一般情况下,失去logfile可以通过我和welyngj说介绍的方法来进行恢复的,但是,估计是你的机器没有正常的关机,比如说断电,或者直接关系统,造成了恢复不成功的问题。 我在我自己的机器上通过shutdown abort的方式,和可以模拟出你的情况 实验如下
一 可恢复状况
SQL> insert into test1.tt1 values(3,'111','');
SQL> insert into test1.tt1 values(4,'111','');
SQL> insert into test1.tt1 values(5,'111','');
SQL> commit;
SQL>shutdown immedaite; 待shutdown后,删除掉redon.log 恢复
SQL>startup mount;
SQL>recover database until cancel using backup controlfile;
指定日志: { <RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancel. SQL>alter database open resetlogs 数据库打开,恢复成功
二 不可恢复状况
SQL> insert into test1.tt1 values(6,'111','');
SQL> insert into test1.tt1 values(7,'111','');
SQL> insert into test1.tt1 values(8,'111','');
SQL> commit;
SQL>shutdown abort;
拷贝redon.log到其他的地方。 SQL>startup mount;
SQL>recover database until cancel using backup controlfile;
指定日志: { <RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancel. 提示数据库不一致,已经无法恢复了(如果没有redo的备份的话)。 由于我已经备份过redo了
所以我还是可以恢复我的database 还是使用上面的方法,不过是到
SQL>recover database until cancel using backup controlfile;
指定日志: { <RET>=suggested | filename | AUTO | CANCEL}
这里
输入你备份的那个redo.log路径就可以了 指定日志: { <RET>=suggested | filename | AUTO | CANCEL}
F:\developer\oracle\product\10.2.0\oradata\REDO03.LOG
已应用的日志。
完成介质恢复。 不过这里你没有redo的备份,还是可以通过设置隐含参数来启动数据库,
如welyngj所说的_allow_resetlogs_corruption设置为true
可以create pfile='init.ora的路径' from spfile; 把spfile导出到pfile后,在导出的init.ora里加入_allow_resetlogs_corruption=true 然后从pfile启动
startup pfile='init.ora的路径'; 这时数据库可以起来,但是这个数据库是很危险的,把数据库的数据exp出来,重建数据库。
1.SQL>create pfile='/opt/oracle/product/11g/db/dbs/init.ora' from spfile;2.修改/opt/oracle/product/11g/db/dbs/init.ora在文件最后加上一行:_allow_resetlogs_corruption=true 3.SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup pfile='/opt/oracle/product/11g/db/dbs/init.ora';
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started. Total System Global Area 3340451840 bytes
Fixed Size 2149000 bytes
Variable Size 2013267320 bytes
Database Buffers 1308622848 bytes
Redo Buffers 16412672 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
Process ID: 15711
Session ID: 665 Serial number: 5不知中间是否缺少步骤?
alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started. Total System Global Area 3340451840 bytes
Fixed Size 2149000 bytes
Variable Size 2013267320 bytes
Database Buffers 1308622848 bytes
Redo Buffers 16412672 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
show一把看看
show parameter _allow_
如果加进去了这个隐含参数,是可以打开
------------------------------------ ---------------------------------
VALUE
------------------------------
_allow_resetlogs_corruption boolean
TRUE
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/opt/oracle/product/11g/db/dbs/init.ora' mount;
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started.Total System Global Area 3340451840 bytes
Fixed Size 2149000 bytes
Variable Size 2013267320 bytes
Database Buffers 1308622848 bytes
Redo Buffers 16412672 bytes
Database mounted.
SQL> show parameter _allow_NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
_allow_resetlogs_corruption boolean
TRUE
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
上一次恢复有可能没有用那个参数:
recover database until cancel;
alter database open resetlogs;
如果不行,不知道大家还有没有招?
应该可以的,我以前做过这样的恢复,而且刚刚又做了一次实验,没有问题的。不过recovery应该不需要做了,他现在一没有归档,而没有redo,做也是直接cancel。直接设置好隐性参数,open resetlogs,就可以了。
sorry,反馈的好快,刚才没有看到上面的内容,隐性都已经做了,你就在recovery一把吧,要不他一直觉得你是completely的。
也可以先查看一下,redo的文件有没有生成,如果有的话,就先直接alter database open一下试试