归档丢失如何恢复数据文件
测试说明。
一个数据文件损坏，有全库热备，部分归档丢失，如何恢复？
如果有备份我们可以直接恢复该数据文件，restore操作再recover，但是此时要看归档是否缺失，如果缺失则无法继续recover,该数据文件也无法上线。
1 恢复数据文件
RMAN> restore datafile 7;
Starting restore at 2021-08-20 14:37:19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /oracle/base/oradata/PROD/users01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/base/19.3/dbs/0g06vqb8_1_1
channel ORA_DISK_1: piece handle=/oracle/base/19.3/dbs/0g06vqb8_1_1 tag=TAG20210820T105431
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2021-08-20 14:37:23
2 recover 数据文件
RMAN> recover datafile 7;
Starting recover at 2021-08-20 14:41:25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/20/2021 14:41:29
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 9 and starting SCN of 5576278 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 8 and starting SCN of 5576202 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 7 and starting SCN of 5576198 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 6 and starting SCN of 5576195 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 5 and starting SCN of 5576192 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 4 and starting SCN of 5576189 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 3 and starting SCN of 5570027 found to restore
此时报找不到归档。既然归档丢了，此时该数据文件涉及的数据肯定也丢了，此时有两个方法解决，但是都会丢数据
（1）整个库恢复到新的redo，也就是能前滚多少旧滚多少，尽量追到新的redo数据。
（2）将通过数据字典查找该表空间涉及的对象，将备份恢复到一个新的数据，再迁移表空间或者使用数据泵导出导入数据。
我们看种，注意此时肯定有数据丢失，如果允许其他表空间也丢失数据就采取整个做法。
1 restore整个数据库。（此时控制文件没有损坏）
RMAN> restore database;
Starting restore at 2021-08-20 14:44:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
skipping datafile 7; already restored to file /oracle/base/oradata/PROD/users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oracle/base/oradata/PROD/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/base/oradata/PROD/tbs_u1.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/base/oradata/PROD/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/base/oradata/PROD/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oracle/base/19.3/dbs/cachetblsp.dbf
channel ORA_DISK_1: restoring datafile 00008 to /oracle/base/oradata/PROD/tbs_lob.dbf
channel ORA_DISK_1: restoring datafile 00009 to /oracle/base/oradata/PROD/creccoa.dbf
channel ORA_DISK_1: reading from backup piece /oracle/base/19.3/dbs/0g06vqb8_1_1
channel ORA_DISK_1: piece handle=/oracle/base/19.3/dbs/0g06vqb8_1_1 tag=TAG20210820T105431
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 2021-08-20 14:46:50
此时数据库处于mounted状态，下面恢复数据
RMAN> recover database;
Starting recover at 2021-08-20 14:50:39
using channel ORA_DISK_1
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/20/2021 14:50:41
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 9 and starting SCN of 5576278 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 8 and starting SCN of 5576202 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 7 and starting SCN of 5576198 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 6 and starting SCN of 5576195 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 5 and starting SCN of 5576192 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 4 and starting SCN of 5576189 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 3 and starting SCN of 5570027 found to restore
发现依然没有归档日志，这个日志之前我们以将删除了，当然也没有备份过。
此时不做recover操作，也就是保持restore后的状态，但是这个备份时基于hot backup所以如果不执行有效的recover，由于数据文件记录的SCN不一致，数据库依然无法打开。
我尝试打开。
SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/base/oradata/PROD/system01.dbf'
SQL>select status, checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by status, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time
STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
-------------- ------------------ ------------------------------------------------------------------------ ----------
ONLINE 5571714 20-AUG-2021 10:54:32 8
此时的检查的点时间时备份时的时间。
此时数据库无法打开，要求所有文件做介质恢复。
下面我们基于available redo 做介质恢复
RMAN> recover database until available redo;
Starting recover at 2021-08-20 14:52:08
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 8 is already on disk as file /oracle/base/oradata/PROD/redo02.log
archived log for thread 1 with sequence 9 is already on disk as file /oracle/base/oradata/PROD/redo03.log
archived log for thread 1 with sequence 10 is already on disk as file /oracle/base/oradata/PROD/redo01.log
warning: attempt media recovery until thread 1, sequence 3
Finished recover at 2021-08-20 14:52:10
此时发现了当前的日志存在，但是注意由于3 4 5 6 7丢失所以，waring部分说的很清楚了，恢复到thread 1 ,sequence3为止
因为日志需要连续性，缺失则无法跳过执行，从原理讲确实也不允许。
下面我们尝试打开数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
由于时不完全恢复，此时的日志没有意义，需要重置redo,从新的序列号开始，并且会clear旧的redo.
下面使用resetlogs打开数据库。
SQL> alter database open resetlogs;
Database altered.
下面查询日志序列号是否重置。
SQL> select thread#,sequence#,status from v$log;
THREAD# SEQUENCE# STATUS
---------- ---------- --------------------------------
1 1 CURRENT
1 0 UNUSED
1 0 UNUSED
后我们要重新备份数据库，因为之前的备份已经没有意义了，因为日志时新的。
RMAN> backup as compressed backupset database ;
Starting backup at 2021-08-20 14:56:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/base/oradata/PROD/system01.dbf
input datafile file number=00003 name=/oracle/base/oradata/PROD/sysaux01.dbf
input datafile file number=00004 name=/oracle/base/oradata/PROD/undotbs01.dbf
input datafile file number=00009 name=/oracle/base/oradata/PROD/creccoa.dbf
input datafile file number=00002 name=/oracle/base/oradata/PROD/tbs_u1.dbf
input datafile file number=00005 name=/oracle/base/19.3/dbs/cachetblsp.dbf
input datafile file number=00008 name=/oracle/base/oradata/PROD/tbs_lob.dbf
input datafile file number=00007 name=/oracle/base/oradata/PROD/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2021-08-20 14:56:13
channel ORA_DISK_1: finished piece 1 at 2021-08-20 14:57:48
piece handle=/oracle/base/19.3/dbs/0j0708gd_1_1 tag=TAG20210820T145612 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 2021-08-20 14:57:48
Starting Control File and SPFILE Autobackup at 2021-08-20 14:57:48
piece handle=/oracle/base/19.3/dbs/c-492834486-20210820-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2021-08-20 14:57:50
此时测试结束！
相关文章