记录一次异机恢复问题:RMAN-06025 no backup of archive log …… found to restore
log to 记录 of 恢复 No 一次 found
2023-09-14 09:12:57 时间
一 问题描述
在客户使用RAC环境,增加数据文件时候,错误的将datafile加到了本地磁盘,而发现错误后,又执行了offline datafile操作,数据文件状态变成为recover,非online。之后没及时发现,归档日志被删除。
在执行如下恢复sql
恢复数据库的时候报错如下:
二 问题排查
排查下这个数据文件的状态,看到是RECOVER,不是正常的ONLINE:
set line222
col name for a60
SQL> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
---------- ------------------------------------------------------------ --- ------------------ -------
34 /u01/app/oracle/oradata/orcl/iemr_df04.dbf 1.2727E+13 ONLINE
35 /u01/app/oracle/oradata/orcl/system01.dbf 1.2707E+13 RECOVER
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
34 /u01/app/
oracle/oradata/orcl/iemr_df04.dbf ONLINE
35 /u01/app/oracle/oradata/orcl/system01.dbf RECOVER
推测是这个问题导致的。
原因是源库上有数据文件状态为RECOVER
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
34 /u01/app/oracle/oradata/orcl/iemr_df04.dbf ONLINE
35 /u01/app/oracle/oradata/orcl/system01.dbf RECOVER
在源库执行offline datafile 操作:
alter database datafile 35 offline drop;
提升成功,但是并没有起到作用
查询这个数据文件是否有数据,确定数据文件是否为空
SELECT distinct b.owner, b.segment_name
FROM dba_data_files a,
dba_extents b
WHERE a.file_id = b.file_id
AND a.tablespace_name ='SYSTEM'
AND a.file_name ='+DATA/orcl/datafile/system01.dbf';
发现是空的。
删除数据文件命令(建议不要删)
alter tablespace system drop datafile '+DATA/orcl/datafile/system01.dbf';
如果一定要处理的话,可以通过bbed的方式去修复后删除。
三 解决办法
rman备份需要加skip,例如 backup database skip inaccessible;
rman恢复的时候执行offline datafile 操作。
SQL> alter database datafile 35 offline drop;
Database altered.
SQL> set line222
SQL> col name for a60
SQL> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
---------- ------------------------------------------------------------ --- ------------------ -------
34 /u01/app/oracle/oradata/orcl/iemr_df04.dbf 1.2727E+13 ONLINE
35 /u01/app/oracle/oradata/orcl/system01.dbf 1.2707E+13 RECOVER
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4593921 generated at 07/09/2022 12:48:34 needed for thread 1
ORA-00289: suggestion : /home/oracle/1_41_1104664055.dbf
ORA-00280: change 4593921 for thread 1 is in sequence #41
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 4593946 generated at 07/09/2022 12:48:50 needed for thread 1
ORA-00289: suggestion : /home/oracle/1_42_1104664055.dbf
ORA-00280: change 4593946 for thread 1 is in sequence #42
ORA-00278: log file '/home/oracle/1_41_1104664055.dbf' no longer needed for
this recovery
ORA-00308: cannot open archived log '/home/oracle/1_42_1104664055.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
结论:recover不在需要35号已经被删除的archive log,正常完成recover 操作。
四 总结
我们在一些恢复案例中,会经常遇到一些奇怪的问题,其中有的是源端数据文件不规范而导致恢复过程出错,比较常见的错误就是数据文件状态为recover,非online状态,要在备份的时候仔细查看源库的状态。还有要多看恢复日志,有些看起来不像报错的,也得注意。
相关文章
- MySQL复制报错(Slave failed to initialize relay log info structure from the repository)
- ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE
- ERROR: Error in Log_event::read_log_event()
- Maintaining Online Redo Log Files
- Tcode SCU3查看table log的error message - 如何查找necessary PFCG role
- Application log save debug - how log data is persisted to database table
- ABAP webservice和log on对话框相关的实现细节调试
- Product change时关于change_log的讨论
- set Spring log level to debug so that we can learn more from log
- atitit 2010 2010 diary log events memorabilia v3 taf .docx No finish , wait to finish 1.6 yLu
- Atitit 数据库与存储引擎设计与实现 attilax总结 1.1. 数据库的实现有很多种, 遵循一些理论规范,如 Fix Rules、Write-Ahead Log、Force-log-at-
- 【Codeforces 231C】To Add or Not to Add
- You currently don‘t have access to this membership resource. To resolve this issue, agree to the lat
- 数据库如何使用预写式日志(Write Ahead Log, WAL) 解决 IO 问题的?
- UNIX命令,统计当前目录(含子目录)下所有后缀为.log的文件中ERROR出现的行数
- 007-log-log4j2、slf4j+log4j2
- Go语言自学系列 | golang标准库log
- 你还没有真正理解的innodb_flush_log_at_trx_commit
- How to Setup Archive Log Destination Directory in Oracle Database