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 --