ORA-0113,ORA-0110的解决办法

因为一次系统盘空间满了,执行sql总是没反应,或者提示空间不足。
慌乱之中输入:shutdown abort重启了。
重新启动悲剧了!

SQL> alter pluggable database orclpdb open;
alter pluggable database orclpdb open
*
ERROR at line 1:
ORA-01113: file 25 needs media recovery
ORA-01110: data file 25: '/opt/oracle/g5/dbf/kt_csearch03.dbf'

后来林工进入rman执行:帮忙各种排查:list failure;  advise failure; repair failure;

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
21497      CRITICAL OPEN      25-DEC-18     System datafile 9: '/opt/oracle/oradata/ORCL/ORCLpdb/system01.dbf' needs media recovery
21500      HIGH     OPEN      25-DEC-18     One or more non-system datafiles need media recovery
21485      HIGH     OPEN      25-DEC-18     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=305 device type=DISK
analyzing automatic repair options complete


RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/oracle/diag/rdbms/orcl/ORCL/hm/reco_2080152763.hm

contents of repair script:
   # restore from standby and recover datafile
   sql 'ORCLPDB' 'alter database datafile 15 offline';
   restore ( datafile 15 from service "orcldg" );
   recover datafile 15;
   sql 'ORCLPDB' 'alter database datafile 15 online';
   # recover datafile
   sql 'ORCLPDB' 'alter database datafile 9, 10, 12, 16, 20, 21, 22, 23, 24, 25 offline';
   recover datafile 9, 10, 12, 16, 20, 21, 22, 23, 24, 25;
   sql 'ORCLPDB' 'alter database datafile 9, 10, 12, 16, 20, 21, 22, 23, 24, 25 online';

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

sql statement: alter database datafile 15 offline

Starting restore at 25-DEC-18
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldg
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00015 to /opt/oracle/oradata/ORCL/apple.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 25-DEC-18

Starting recover at 25-DEC-18
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of repair command at 12/25/2018 12:10:10
RMAN-03015: error occurred in stored script Repair Script
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover
 if needed datafile 15
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 15: '/opt/oracle/oradata/ORCL/apple.dbf'
ORA-01122: database file 15 failed verification check
ORA-01110: data file 15: '/opt/oracle/oradata/ORCL/apple.dbf'
ORA-01130: database file version 12.2.0.1.0 incompatible with ORACLE version 12.2.0.0.0

然后下面会有一些原因分析。

后来看了这个地址:https://blog.csdn.net/e_wsq/article/details/79596005

最后在rman下面执行:recover datafile 23;

RMAN> recover datafile 23;

Starting recover at 25-DEC-18
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 25-DEC-18

再次执行:alter pluggable database orclpdb open; 终于搞定!

总结
(1)系统盘,放一个2G的文件,一旦空间满了,删除掉可以顺利执行sql  (最后问题搞定后,记得再传上去。)
比如执行:alter database tempfile '/opt/oracle/oradata/ORCL/ORCLpdb/temp01.dbf' resize 1024M;
(2)林工说的,不要轻易执行:shutdown abort;出问题很麻烦。
(3)redo能移都移走吧。
(4)定期 backup database;

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值