检查数据库告警日志发现如下错误提示:
Mon Aug 01 15:57:39 2016Errors in file /u01/app/oracle/diag/rdbms/sjjhdb/sjjhdb1/trace/sjjhdb1_m000_31546.trc:
ORA-01135: file 43 accessed for DML/query is offline
ORA-01110: data file 43: '+DATA/dt_gspopulation08.dbf'
从错误信息可以明显的看到,43号数据文件处于脱机状态,而数据库中的DML/query操作一直在试图访问该文件,从这里可以明确的知道,该文件是意外脱机,并且该文件里存储的数据是业务需要的数据。我们通过检查来验证一下43号数据文件的状态:
SQL> select file#,name , status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------------------------------------ -------
34 +DATA/dt_center05.dbf ONLINE
35 +DATA/dt_center06.dbf ONLINE
36 +DATA/dt_gspopulation01.dbf ONLINE
37 +DATA/dt_gspopulation02.dbf ONLINE
38 +DATA/dt_gspopulation03.dbf ONLINE
39 +DATA/dt_gspopulation04.dbf ONLINE
40 +DATA/dt_gspopulation05.dbf ONLINE
41 +DATA/dt_gspopulation06.dbf ONLINE
42 +DATA/dt_gspopulation07.dbf ONLINE
43 +DATA/dt_gspopulation08.dbf RECOVER
44 +DATA/dt_gspopulation008.dbf ONLINE
49 rows selected.(部分结果省略了)
查看43号文件头部scn号:
SQL> select file#,status,checkpoint_change# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
34 ONLINE 8153080523
35 ONLINE 8153080523
36 ONLINE 8153080523
37 ONLINE 8153080523
38 ONLINE 8153080523
39 ONLINE 8153080523
40 ONLINE 8153080523
41 ONLINE 8153080523
42 ONLINE 8153080523
43 OFFLINE 8063759520
44 ONLINE 8153080523
49 rows selected.(部分结果省略了)
可以非常清楚的看到,43号数据文件处于脱机状态且需要介质恢复,头部scn号比其他数据文件差距一个数量级。
遇到这种情况第一步肯定是视图将43号文件联机,当然,肯定会提示需要介质恢复:
SQL>alter database datafile 43 online;
第 1 行出现错误:
ORA-01113: file 43 media recovery
ORA-01110: datafile 43: '+DATA/dt_gspopulation08.dbf'
SQL> recover datafile 43;
ORA-00279: change 8063759520 generated at 07/27/2016 16:27:18 needed for thread 2
ORA-00289: suggestion : +DATA
ORA-00280: change 8063759520 for thread 2 is in sequence #8909
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '+DATA'
ORA-17503: ksfdopn:2 Failed to open file +DATA
ORA-15045: ASM file name '+DATA' is not in reference form
ORA-00308: cannot open archived log '+DATA'
ORA-17503: ksfdopn:2 Failed to open file +DATA
ORA-15045: ASM file name '+DATA' is not in reference form
介质恢复出现问题,不能打开‘+DATA’指定目录下的归档日志文件,需要07/27/2016 16:27:18 这个时间点的2号线程的8909号日志文件,从时间看,这已经是4天前了,如果没有对归档日志做备份,那么这只能通过修改数据文件头scn号进行不完全恢复,将数据文件联机。非常幸运的是该库是在归档模式下运行的且归档日志备份存量是一周。所以这个恢复起来就相对比较简单,但是要扫描5天的备份集,会花费一会儿时间。
RMAN> recover datafile 43;
省略部分输出信息。。。
channel default: deleting archived log(s)
archived log file name=+DATA/sjjhdb/archivelog/2016_08_01/thread_2_seq_9310.576.918758139 RECID=16760 STAMP=918758148
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6697
channel ORA_DISK_1: reading from backup piece /backup/sjjh_bak/rmanbackup/hdrc4tt0_1_1
channel ORA_DISK_1: piece handle=/backup/sjjh_bak/rmanbackup/hdrc4tt0_1_1 tag=ARC_TAG20160801T040530
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel default: deleting archived log(s)
archived log file name=+DATA/sjjhdb/archivelog/2016_08_01/thread_1_seq_6697.576.918758155 RECID=16761 STAMP=918758164
Finished recover at 01-AUG-16
RMAN>
看到Finished recover at 01-AUG-16可以确定介质恢复已经完成,接着就是将43号文件联机:
SQL> alter database datafile 43 online;
Database altered.
SQL>
检查联机后的43号文件信息:
SQL> select file#,status,checkpoint_change# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
23 ONLINE 8157837655
24 ONLINE 8157837655
25 ONLINE 8157837655
26 ONLINE 8157837655
27 ONLINE 8157837655
28 ONLINE 8157837655
29 ONLINE 8157837655
30 ONLINE 8157837655
31 ONLINE 8157837655
32 ONLINE 8157837655
33 ONLINE 8157837655
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
34 ONLINE 8157837655
35 ONLINE 8157837655
36 ONLINE 8157837655
37 ONLINE 8157837655
38 ONLINE 8157837655
39 ONLINE 8157837655
40 ONLINE 8157837655
41 ONLINE 8157837655
42 ONLINE 8157837655
43 ONLINE 8159145457
44 ONLINE 8157837655
49 rows selected.
SQL>
可以看到43号数据文件的头部scn号已经比其他文件高出一部分,这个不用担心,正常的,因为在日志恢复的时候,部分操作相当于通过提前已经写入到43号文件中,导致43号文件头部scn号超前,但是过一段时间,所有数据文件的头部scn都会一致的。接着检查数据文件状态:
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------------------------------------ -------
34 +DATA/dt_center05.dbf ONLINE
35 +DATA/dt_center06.dbf ONLINE
36 +DATA/dt_gspopulation01.dbf ONLINE
37 +DATA/dt_gspopulation02.dbf ONLINE
38 +DATA/dt_gspopulation03.dbf ONLINE
39 +DATA/dt_gspopulation04.dbf ONLINE
40 +DATA/dt_gspopulation05.dbf ONLINE
41 +DATA/dt_gspopulation06.dbf ONLINE
42 +DATA/dt_gspopulation07.dbf ONLINE
43 +DATA/dt_gspopulation08.dbf ONLINE
44 +DATA/dt_gspopulation008.dbf ONLINE
49 rows selected.
可以看到,43号文件状态已经为online状态。数据库告警日志也不再报出先前的错误信息。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31403259/viewspace-2138915/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31403259/viewspace-2138915/