怎么解决Oracle报错ORA

118次阅读

共计 5910 个字符，预计需要花费 15 分钟才能阅读完成。

这篇文章主要介绍“怎么解决 Oracle 报错 ORA-01194、ORA-01110 问题”，在日常操作中，相信很多人在怎么解决 Oracle 报错 ORA-01194、ORA-01110 问题问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么解决 Oracle 报错 ORA-01194、ORA-01110 问题”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

使用_allow_resetlogs_corruption 打开无归档日志 rman 备份库，运维 DBA 反映服务器宕机后，开启数据库报错 ORA-01194 ORA-01110，分析原因为 Oracle SCN 不一致导致数据库无法启动，使用_allow_resetlogs_corruption 打开数据库

1.rman 还原恢复操作

-- 还原数据库
RMAN  restore database;
-- 恢复数据库
RMAN  recover database;
Starting recover at 2012-03-08 21:20:45
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/08/2012 21:20:47
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 2936 and starting SCN of 25991695 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2935 and starting SCN of 25991652 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2934 and starting SCN of 25991649 found to restore
……………………
RMAN-06025: no backup of archived log for thread 1 with sequence 2902 and starting SCN of 25991156 found to restore
这里报日志缺少，实际上是备份的数据库文件后，没有备份归档日志，归档日志全部丢失

进行不完全恢复

SQL recover database until cancel; ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1 ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf ORA-00280: change 25991194 for thread 1 is in sequence #2902 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: /opt/oracle/oradata/chf/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/chf/system01.dbf

2. 查看相关 SCN

SQL  select file#,to_char(checkpoint_change#, 999999999999) from v$datafile;
 FILE# TO_CHAR(CHECK
---------- -------------
 1 25992214
 2 25992214
 3 25992214
 4 25992214
 5 25992214
 6 25992214
 7 25992214
 8 25992214
 9 25992214
 10 25992214
 11 25992214
 FILE# TO_CHAR(CHECK
---------- -------------
 13 25992214
 14 25992214
13 rows selected.
SQL  select file#,online_status,to_char(change#, 999999999999) from v$recover_file;
 FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
 1 ONLINE 25991194
 2 ONLINE 25991194
 3 ONLINE 25991194
 4 ONLINE 25991194
 5 ONLINE 25991194
 6 ONLINE 25991194
 7 ONLINE 25991194
 8 ONLINE 25991194
 9 ONLINE 25991194
 10 ONLINE 25991194
 11 ONLINE 25991194
 FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
 13 ONLINE 25991194
 14 ONLINE 25991194
13 rows selected.
SQL  select file#,to_char(checkpoint_change#, 999999999999) from v$datafile_header;
 FILE# TO_CHAR(CHECK
---------- -------------
 1 25991194
 2 25991194
 3 25991194
 4 25991194
 5 25991194
 6 25991194
 7 25991194
 8 25991194
 9 25991194
 10 25991194
 11 25991194
 FILE# TO_CHAR(CHECK
---------- -------------
 13 25991194
 14 25991194
13 rows selected.-- 发现数据文件 scn 和控制文件不一致，重建控制文件，然后查询相关 scnSQL  select file#,to_char(checkpoint_change#, 999999999999) from v$datafile;
 FILE# TO_CHAR(CHECK
---------- -------------
 1 25991194
 2 25991194
 3 25991194
 4 25991194
 5 25991194
 6 25991194
 7 25991194
 8 25991194
 9 25991194
 10 25991194
 11 25991194
 FILE# TO_CHAR(CHECK
---------- -------------
 13 25991194
 14 25991194
13 rows selected.
SQL  select file#,online_status,to_char(change#, 999999999999) from v$recover_file;
 FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
 1 ONLINE 25991194
 2 ONLINE 25991194
 3 ONLINE 25991194
 4 ONLINE 25991194
 5 ONLINE 25991194
 6 ONLINE 25991194
 7 ONLINE 25991194
 8 ONLINE 25991194
 9 ONLINE 25991194
 10 ONLINE 25991194
 11 ONLINE 25991194
 FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
 13 ONLINE 25991194
 14 ONLINE 25991194
13 rows selected.
SQL  select file#,to_char(checkpoint_change#, 999999999999) from v$datafile_header;
 FILE# TO_CHAR(CHECK
---------- -------------
 1 25991194
 2 25991194
 3 25991194
 4 25991194
 5 25991194
 6 25991194
 7 25991194
 8 25991194
 9 25991194
 10 25991194
 11 25991194
 FILE# TO_CHAR(CHECK
---------- -------------
 13 25991194
 14 25991194
13 rows selected.
-- 此时所有 scn 均一致

3. 尝试打开数据库

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/chf/system01.dbf SQL recover database using backup controlfile until cancel; ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1 ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf ORA-00280: change 25991194 for thread 1 is in sequence #2902 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: /opt/oracle/oradata/chf/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/chf/system01.dbf

5. 使用隐含参数打开数据库

SQL  create pfile= /tmp/pfile  from spfile;
File created.
-------/tmp/pfile 中加上 ----------
_allow_resetlogs_corruption= TRUE
---------------------------------
SQL  startup mount pfile= /tmp/pfile  force
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2230912 bytes
Variable Size 419431808 bytes
Database Buffers 192937984 bytes
Redo Buffers 7548928 bytes
Database mounted.
SQL  alter database open resetlogs;
Database altered.
SQL  select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

到此，关于“怎么解决 Oracle 报错 ORA-01194、ORA-01110 问题”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！

正文完