因为一次系统盘空间满了,执行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;