硬盘有坏兆，导致redo02.log损坏，将oradata里文件和dbs里文件拷贝到新装系统新装oracle里相应位置处，但数据库无法启动，提示日志大小不足要求，网上的相关文章试了一周都没用，请大家帮帮忙（系统是Red Hat Linux 9，oracle是oracle9.2.0.4.0）在运行startup后，报如下错误，请大家帮帮忙，急，因数据没有备份，连表结构都没有了，所以这个如果不能启动备份出大部分数据，会很惨，拜托大家了，帮帮忙吧SQL> startup
ORACLE instance started.Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00327: log 2 of thread 1, physical size 100831 less than needed 204800
ORA-00312: online log 2 thread 1: '/oracle/opt/ora9/oradata/sugang/redo02.log'查看日志表里，显示SQL> select * from v$log；
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
1 1 21428 104857600 1 NO INACTIVE
416734172 06-6月 -09 2 1 21429 104857600 1 NO CURRENT
416747498 06-6月 -09 3 1 21427 104857600 1 NO INACTIVE
416720778 06-6月 -09对不起，没有多少分可给，但是只要能解决，分不够我会想办法的，请一定要帮帮忙
ORACLE instance started.Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00327: log 2 of thread 1, physical size 100831 less than needed 204800
ORA-00312: online log 2 thread 1: '/oracle/opt/ora9/oradata/sugang/redo02.log'查看日志表里，显示SQL> select * from v$log；
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
1 1 21428 104857600 1 NO INACTIVE
416734172 06-6月 -09 2 1 21429 104857600 1 NO CURRENT
416747498 06-6月 -09 3 1 21427 104857600 1 NO INACTIVE
416720778 06-6月 -09对不起，没有多少分可给，但是只要能解决，分不够我会想办法的，请一定要帮帮忙
redo01.log 104,858,112
redo02.log 51,625,984
redo03.log 104,858,112
http://topic.csdn.net/u/20090611/11/ccf29f8a-b8a5-4491-9bf6-98f404b07e7b.html
主要看后面的隐含参数的方法。这里你的信息确认一下
现在日志文件大小为
redo01.log 104,858,112
redo02.log 51,625,984
redo03.log 104,858,112SQL> select * from v$log；
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
1 1 21428 104857600 1 NO INACTIVE
416734172 06-6月 -09 2 1 21429 104857600 1 NO CURRENT
416747498 06-6月 -09 3 1 21427 104857600 1 NO INACTIVE
416720778 06-6月 -09 有没有resize过redo文件或者重做过。最好看看aler信息，看能不能找到导致前面死机的信息或者更多的信息。
SQL>recover database until cancel using backup controlfile;出现输入指定日志 进行恢复的时候，不要输入cancel
而是先尝试的输入redo1文件的路径如果不行，才来一次尝试redo3文件的路径如果不行，就只有最后一搏了，
Inthirties关注Oracle数据库 维护 优化，安全，备份，恢复，迁移，故障处理如果你需要帮助或想和我一起学习的请联系
联系方式QQ：370140387
电子邮件：[email protected]
网站: http://www.inthirties.com
你操作试试。有问题再贴出来。
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 416747498 generated at 06/06/2009 03:29:12 needed for thread
1
ORA-00289: suggestion : /oracle/opt/ora9/product/9.2/dbs/arch1_21429.dbf
ORA-00280: change 416747498 for thread 1 is in sequence #21429
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/opt/ora9/oradata/sugang/redo01.log
ORA-00310: archived log contains sequence 21428; sequence 21429 required
ORA-00334: archived log: '/oracle/opt/ora9/oradata/sugang/redo01.log'
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: '/oracle/opt/ora9/oradata/sugang/system01.dbf'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 416747498 generated at 06/06/2009 03:29:12 needed for thread
1
ORA-00289: suggestion : /oracle/opt/ora9/product/9.2/dbs/arch1_21429.dbf
ORA-00280: change 416747498 for thread 1 is in sequence #21429
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/opt/ora9/oradata/sugang/redo03.log
ORA-00310: archived log contains sequence 21427; sequence 21429 required
ORA-00334: archived log: '/oracle/opt/ora9/oradata/sugang/redo03.log'
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: '/oracle/opt/ora9/oradata/sugang/system01.dbf'
Inthirties关注Oracle数据库 维护 优化，安全，备份，恢复，迁移，故障处理如果你需要帮助或想和我一起学习的请联系
联系方式QQ：370140387
电子邮件：[email protected]
网站: http://www.inthirties.com
2.alter system set "_allow_resetlogs_corruption"=true scope=spfile;
3.shutdown immediate
4.startup mount
5.recover database using backup controlfile until cancel
6.cancel
7.alter database open resetlogs;
你确定第6步正确吗？
SQL> recover database using backup controlfile until cancel
ORA-00279: change 416747498 generated at 06/06/2009 03:29:12 needed for thread
1
ORA-00289: suggestion : /oracle/opt/ora9/product/9.2/dbs/arch1_21429.dbf
ORA-00280: change 416747498 for thread 1 is in sequence #21429
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: '/oracle/opt/ora9/oradata/sugang/system01.dbf'
ORA-01112: media recovery not started
输入recover database using backup controlfile until cancel命令后，
就接着执行cancel命令，你怎么还输入别的。然后不管它报错与否，直接执行下一步操作。
你别管他的提示，cancel后alter database open resetlogs;
vc555大虾谈谈我个人的看法
LZ说没有备份，
我觉得有可能指的是建库以后一直没有冷备份，如果是有从建库一来的完整归档的话，是可以尝试着做不完全恢复的。另外
刚才看到上面的兄弟说加隐藏参数不行报错，我刚才一直在实验库上做相关的实验，所以没有留意他的步骤。如果有错误的地方，还请多指教。本人也不是高手，只是做过一些恢复的案例和项目，对恢复这块特别感兴趣，所以有相关的问题都会积极参与，如果有不对的地方，还请多多指教谢谢。
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: '/oracle/opt/ora9/oradata/sugang/system01.dbf'
ORA-00312: online log 2 thread 1: '/oracle/opt/ora9/oradata/sugang/redo02.log'
错，后来按大家说的修改了_allow_resetlogs_corruption参数，然后用了recover database until cancel using backup controlfile，还是报错
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 416747498 generated at 06/06/2009 03:29:12 needed for thread
1
ORA-00289: suggestion : /oracle/opt/ora9/product/9.2/dbs/arch1_21429.dbf
ORA-00280: change 416747498 for thread 1 is in sequence #21429
Specify log: { <RET>=suggested | filename | AUTO | CANCEL}
/oracle/opt/ora9/oradata/sugang/redo01.log
ORA-00310: archived log contains sequence 21428; sequence 21429 required
ORA-00334: archived log: '/oracle/opt/ora9/oradata/sugang/redo01.log'
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: '/oracle/opt/ora9/oradata/sugang/system01.dbf'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 416747498 generated at 06/06/2009 03:29:12 needed for thread
1
ORA-00289: suggestion : /oracle/opt/ora9/product/9.2/dbs/arch1_21429.dbf
ORA-00280: change 416747498 for thread 1 is in sequence #21429
Specify log: { <RET>=suggested | filename | AUTO | CANCEL}
/oracle/opt/ora9/oradata/sugang/redo03.log
ORA-00310: archived log contains sequence 21427; sequence 21429 required
ORA-00334: archived log: '/oracle/opt/ora9/oradata/sugang/redo03.log'
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: '/oracle/opt/ora9/oradata/sugang/system01.dbf' 然后又有人说recover database until cancel，我也执行了，还是报错
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
后来又执行recover database using backup controlfile until cancel
输入cancel ，也报错
ORA-00279: change 416747498 generated at 06/06/2009 03:29:12 needed for thread
1
ORA-00289: suggestion : /oracle/opt/ora9/product/9.2/dbs/arch1_21429.dbf
ORA-00280: change 416747498 for thread 1 is in sequence #21429
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: '/oracle/opt/ora9/oradata/sugang/system01.dbf'
ORA-01112: media recovery not started
最后我又执行了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: '/oracle/opt/ora9/oradata/sugang/system01.dbf'
Oracle数据库联机日志文件丢失处理方法 试验一：用命令清空日志组方法
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
注意直接粘代码，别用语言去描述你的操作过程，极度怀疑你的操作有问题！SYS@tl> select group#,status from v$log; GROUP# STATUS
---------- --------------------------------
1 CURRENT
3 UNUSED
2 UNUSEDSYS@tl> shutdown abort
ORACLE 例程已经关闭。
SYS@tl> host del D:\oracle\product\10.2.0\oradata\tl\REDO01.LOGSYS@tl> startup
ORACLE 例程已经启动。Total System Global Area 314572800 bytes
Fixed Size 1296452 bytes
Variable Size 109053884 bytes
Database Buffers 197132288 bytes
Redo Buffers 7090176 bytes
数据库装载完毕。
ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员
ORA-00312: 联机日志 1 线程 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TL\REDO01.LOG'
SYS@tl> alter system set "_allow_resetlogs_corruption"=true scope=spfile;系统已更改。SYS@tl> shutdown immediate
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SYS@tl> startup mount
ORACLE 例程已经启动。Total System Global Area 314572800 bytes
Fixed Size 1296452 bytes
Variable Size 109053884 bytes
Database Buffers 197132288 bytes
Redo Buffers 7090176 bytes
数据库装载完毕。
SYS@tl> recover database using backup controlfile until cancel
ORA-00279: 更改 1875206 (在 06/16/2009 16:01:37 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TL\ARCHIVELOG\2009
_06_16\O1_MF_1_1_%U_.ARC
ORA-00280: 更改 1875206 (用于线程 1) 在序列 #1 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
介质恢复已取消。
SYS@tl> alter database open resetlogs;数据库已更改。SYS@tl> select status from v$instance;STATUS
------------------------
OPENSYS@tl>
Cause: A log file has shrunk in size. This is likely to have been caused by operator or operating system error.
Action: Restore the log file from backup. If backup is not available, drop this log and re-create. If the database was shut down cleanly, no further action should be required; otherwise incomplete recovery may be required.
2.数据库无备份；
3.丢失的是current日志。同时满足上述3个条件，只有用_allow_resetlogs_corruption方法。对于当前日志损坏，只有当数据库是正常关闭，才能用drop this log and re-create方法，而且9i起也需做until cancel恢复。当然inthirties在上面提到的没备份，用全部归档来实现还原，理论上也是可以的。但是要求从数据文件创建起的全部归档都存在，且控制文件未重建。
同意vc555大侠athena530 搞定了没有。
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
EXP-00056: ORACLE error 4045 encountered
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_XMLGEN
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_METADATA", line 345
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 1156
ORA-06512: at "SYS.DBMS_METADATA", line 1Aborted怎么办好呢？
连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集即将导出整个数据库...
. 正在导出表空间定义
. 正在导出配置文件
. 正在导出用户定义
. 正在导出角色
. 正在导出资源成本
. 正在导出回退段定义
. 正在导出数据库链接
. 正在导出序号
. 正在导出目录别名
. 正在导出上下文名空间
. 正在导出外部函数库名称
. 导出 PUBLIC 类型同义词
. 导出私有类型同义词
. 正在导出对象类型定义
. 正在导出系统过程对象和操作
. 正在导出 pre-schema 过程对象和操作
. 正在导出群集定义
EXP-00056: 遇到 ORACLE 错误 4045
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_XMLGEN
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_METADATA", line 345
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 1156
ORA-06512: at "SYS.DBMS_METADATA", line 1
帮你在CNOUG里发个贴问问。
http://www.oracle.com.cn/viewthread.php?tid=141438&extra=&frombbs=1
首先，请保证oracle的安装镜像oracle9.2.0_1.iso已经被挂载到/mnt/cdrom上了
然后在oracle用户下输入：
$ cd $ORACLE_HOME/bin
$ ./runInstaller
此时会出现OUI图形界面。
图形界面会要求设置安装路径，既选择安装包的位置。如果按照本文的步骤，默认的安装包是补丁的安装包。
更改这个安装包的路径到/mnt/cdrom中，会找到可以被认出的包（具体的路径和名字记不清了，反正进到该路径就能找到，就那么一个，很好找）。
选择好安装包之后，点击确定。就会看到要求安装数据库的提示。
进入选择“安装类型”时，不要选择“standalone”，选择最后一个“自定义”。
一路按照提示进行，就会发现到达某个页面，显示出已经安装的内容和没有安装的内容。数据库相关的组件基本上全部安装过了，唯一剩下的没有被安装的是两个，其中一个就是“Oracle Label Security”，另外一个名字记不清了。
勾选这两个没有被安装的项，选择确定。然后按照提示一路安装下去。
很快，OLS将安装完毕。
2）Run $ORACLE_HOME/rdbms/admin/catnools.sql script to disable OLS
这个步骤是在sqlplus下进行的。
$sqlplus "/as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/catnools.sql
等待该命令运行完毕。这样再执行exp就行了。