dataguard主库删除归档日志后从库恢复的方法

------------------方法1在主库上使用备份的进行恢复丢失的归档日志------------------------
1.发现主库备份后删除了归档,但是这些归档从库还没应用,也没有传到从库
从库应用的最新的归档日志为592
SQL> connect / as sysdba
Connected.
SQL> Select Max(t.SEQUENCE#) From V$archived_Log t;


MAX(T.SEQUENCE#)
----------------
             592
             
主库的归档日志
SQL> connect / as sysdba
Connected.
SQL> Column Name format a100;
SQL> Column THREAD# format 99;
SQL> Column SEQUENCE# format 999999;
SQL> Column STANDBY_DEST format a10;
SQL> Column ARCHIVED format a10;
SQL> Column APPLIED format a10;
SQL> Column STATUS format a10;
SQL> Column fal format a10;
SQL> Column COMPLETION_TIME format a20;
SQL> Select t.SEQUENCE#,t.STANDBY_DEST,t.APPLIED,t.STATUS From V$archived_Log t Where t.SEQUENCE#>=592 And t.STANDBY_DEST <>'YES';


SEQUENCE# STANDBY_DE APPLIED    STATUS
--------- ---------- ---------- ----------
      592 NO         NO         D
      593 NO         NO         D
      594 NO         NO         D
      595 NO         NO         D
      596 NO         NO         D
      597 NO         NO         D
      598 NO         NO         D
      599 NO         NO         D
      600 NO         NO         D
      601 NO         NO         D
      602 NO         NO         D


11 rows selected.


发现主库的日志从592开始就删除掉了,因为主库是备份后才删除的,那么我们可以恢复这些删除的日志,然后让从库应用


2.将那些日志恢复到自定义的目录
2.1先创建目录
cd /u01/app/oracle/archive_log
mkdir gap_log


2.2 恢复归档
run{
allocate channel ci type disk;
set archivelog destination to '/u01/app/oracle/archive_log/gap_log';
restore archivelog sequence 592;
restore archivelog sequence 593;
restore archivelog sequence 594;
restore archivelog sequence 595;
restore archivelog sequence 596;
restore archivelog sequence 597;
restore archivelog sequence 598;
restore archivelog sequence 599;
restore archivelog sequence 600;
restore archivelog sequence 601;
restore archivelog sequence 602;
release channel ci;
}


好像执行如上命令后,主库会自动将该文件应用到从库了,V$archived_Log里也有相应的记录,deleted状态为NO,还是挺智能的.


--------------------方法二:采用注册的方式恢复----------------------------------------------
1.主库模拟归档日志丢失
模拟将没有传到从库的归档日志拷贝到另外一个目录
[oracle@localhost archive_log]$ mv arch_994182077* ./gap_log/


2.物理删除后需要进行crosscheck,要不字典信息里还会有相应归档日志的记录
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;


查询归档字典表Select Name From v$archived_log t Order By t.SEQUENCE# Desc 相应的值为空


3.主库从新注册归档日志
3.1 单个或少量日志注册
SQL> alter database register physical logfile  '/u01/app/oracle/archive_log/gap_log/arch_994182077_1_615.arc';
SQL> alter database register physical logfile  '/u01/app/oracle/archive_log/gap_log/arch_994182077_1_616.arc';
SQL> alter database register physical logfile  '/u01/app/oracle/archive_log/gap_log/arch_994182077_1_617.arc';
SQL> alter database register physical logfile  '/u01/app/oracle/archive_log/gap_log/arch_994182077_1_618.arc';
SQL> alter database register physical logfile  '/u01/app/oracle/archive_log/gap_log/arch_994182077_1_619.arc';
SQL> alter database register physical logfile  '/u01/app/oracle/archive_log/gap_log/arch_994182077_1_620.arc';
SQL> alter database register physical logfile  '/u01/app/oracle/archive_log/gap_log/arch_994182077_1_621.arc';

ORA-16225: Missing LogMiner session name for Streams
报该错误的话需要加上physical关键字

3.2.大量日志注册


rman> catalog start with '/u01/app/oracle/archive_log/gap_log';


4.步骤3完成后数据库会自动应用恢复回来的文件

----------------方法三:将丢失的日志文件恢复到备库直接应用------------------------------
1.主库模拟归档日志丢失
模拟将没有传到从库的归档日志拷贝到另外一个目录
[oracle@localhost archive_log]$ mv arch_994182077* ./gap_log/


2.物理删除后需要进行crosscheck,要不字典信息里还会有相应归档日志的记录
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
查询归档字典表Select Name From v$archived_log t Order By t.SEQUENCE# Desc 相应的值为空


3.将相应的归档日志拷贝到从库
scp arch_994182077_1_625.arc oracle@192.168.1.85:/u01/app/oracle/archlog/gap_log/
scp arch_994182077_1_626.arc oracle@192.168.1.85:/u01/app/oracle/archlog/gap_log/
scp arch_994182077_1_627.arc oracle@192.168.1.85:/u01/app/oracle/archlog/gap_log/
scp arch_994182077_1_628.arc oracle@192.168.1.85:/u01/app/oracle/archlog/gap_log/
scp arch_994182077_1_629.arc oracle@192.168.1.85:/u01/app/oracle/archlog/gap_log/
scp arch_994182077_1_630.arc oracle@192.168.1.85:/u01/app/oracle/archlog/gap_log/
scp arch_994182077_1_631.arc oracle@192.168.1.85:/u01/app/oracle/archlog/gap_log/


4.在从库上注册
alter database register logfile  '/u01/app/oracle/archlog/gap_log/arch_994182077_1_625.arc';
alter database register logfile  '/u01/app/oracle/archlog/gap_log/arch_994182077_1_626.arc';
alter database register logfile  '/u01/app/oracle/archlog/gap_log/arch_994182077_1_627.arc';
alter database register logfile  '/u01/app/oracle/archlog/gap_log/arch_994182077_1_628.arc';
alter database register logfile  '/u01/app/oracle/archlog/gap_log/arch_994182077_1_629.arc';
alter database register logfile  '/u01/app/oracle/archlog/gap_log/arch_994182077_1_630.arc';
alter database register logfile  '/u01/app/oracle/archlog/gap_log/arch_994182077_1_631.arc';


5.数据库会自动应用如上的归档日志


这种方法的话在主库查询v$archived_log视图是看不到从库相应日志的应用情况

--The End --