DG备库缺失归档文件GAP日志

问题现象：

XXXsdgebus-dg GAP手工注册归档

#出现GAP

idle>select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

---------- ------------- --------------

         1        140513         140513

一、备库查询：

#查询DG架构中存在的UNIQUE_NAME

log_archive_config                DG_CONFIG=(tdcxrac,sdgebus)

#查询DB_UINQIE_NAME

db_name                              string                 ebus

db_unique_name                       string                 sdgebus

#备库查询到的信息可以得到：

#主库db_name= ebus db_unique_name=tdcxrac

fal_client                           string                 sdgebus

fal_server                           string                 TDCXRAC1, TDCXRAC2

#idle>!tnsping TDCXRAC1

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =XXX)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tdcxrac)))

OK (80 msec)

#本机IP地址

idle>!/sbin/ifconfig

inet addr:XXX

#备库查询：是一个单实例

idle>select inst_id,instance_number,instance_name from gv$instance;

   INST_ID INSTANCE_NUMBER INSTANCE_NAME

---------- --------------- --------------------------------

         1               1 ebus

二、主库查询

#主库 RAC环境

SQL> select inst_id,instance_number,instance_name from gv$instance;

   INST_ID INSTANCE_NUMBER INSTANCE_NAME

---------- --------------- ----------------

         1               1 tdcxrac1

         2               2 tdcxrac2

#参数fal

fal_client                           string      tdcxrac

fal_server                           string      sdgebus

#tnsping

SQL> !tnsping sdgebus

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sdgebus) (FAILOVER_MODE= (TYPE=session)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))

OK (90 msec)

#查询主库max_sequence

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

        140914

#备库GAP

idle>select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

---------- ------------- --------------

         1        140513         140513

三、主库日志传输至备库

#备库缺失日志

idle>select THREAD#,sequence#,name,registrar,applied from gv$archived_log where thread#=1 and sequence#=140513;

no rows selected

#归档日志名称参数查询

log_archive_format                %t_%s_%r.log

#查询备库缺失的归档名称

SQL>  select inst_id,thread#,sequence#,name from gv$archived_log where sequence#=140513;

   INST_ID    THREAD#  SEQUENCE#  NAME

---------- ---------- -------------------- ---------- ----------

         1          1     140513

+DATA/tdcxrac/archivelog/2018_05_19/thread_1_seq_140513.25007.976502281

          2          1     140513

+DATA/tdcxrac/archivelog/2018_05_19/thread_1_seq_140513.25007.976502281

#使用COPY备份归档文件至指定路径 Failed

RMAN> backup as copy archivelog sequence 140513 thread 1 format '/tmp/thread_1_seq_140513.25007.log';

skipping archive log file +DATA/tdcxrac/archivelog/2018_05_19/thread_1_seq_140513.25007.976502281; already backed up 1 time(s)

Finished backup at 29-MAY-18

---提示该归档已有备份--直接skip 跳过去了，没有备份

#使用COPY备份归档文件

#查询当前的归档日志保留策略

RMAN> show archivelog deletion policy;  

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ENMO are:

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

#查询RMAN 参数配置

RMAN> show all;

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

#修改RMAN 保留策略：

#冗余度6，代表备份相同的文件，可以备份6次

RMAN> configure retention policy to redundancy 6;  

#copy 备份

RMAN>  backup as copy archivelog sequence 140513 thread 1 format '/tmp/thread_1_seq_140513.25007.log';

#RMAN参数保留策略还原

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

#可选方案：备份归档

backup as compressed backupset archivelog sequence between ${SEQ1} and ${SEQ2} thread ${THD} format '/picclife/backup/rman_gap/arch_%

u_%d_%t' not backed up 22 times;

#本次使用的是，修改备份冗余测试，使用操作系统拷贝方式备份归档，归档文件未压缩，scp传输备库，需要的时间很长

#可以使用以上命令，临时修改备份参数-- 且是备份片方式，压缩了归档文件，传输速度，操作简单

#使用备份片传输后，备库注册，restore还原归档文件

RMAN> catalog start with '/picclife/backup/rman_gap';

RMAN> restore archivelog from sequence 140858 until sequence 140864 thread 1;

#传输至备库

idle>archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /picclife/app/oracle/arch

idle>!/sbin/ifconfig

eth0      Link encap:Ethernet  HWaddr 00:50:56:BD:2B:FF  

          inet addr:XXXX

tdcxrac2:/tmp$ scp thread_1_seq_140513.25007.log oracle@XXX:/picclife/app/oracle/arch

#备库注册：

idle>alter database register logfile '/picclife/app/oracle/arch/thread_1_seq_140513.25007.log';

四、备库检查缺失归档

#缺失归档的数量

#正常的归档数量

select (a.max - b.min +1) as "num_seq"

 from (select max(sequence#) max from v$archived_log)a,

 (select LOW_SEQUENCE# min from v$archive_gap )b;

    num_seq

----------

       405

#查询备库正常接收的归档数量    

   #查询count(*) 备库存在的归档日志数量

idle>select count(*) from v$archived_log where sequence#>=(select LOW_SEQUENCE# min from v$archive_gap);   

  COUNT(*)

----------

       381

#缺失--25个归档日志  --对比

#缺失归档文件的序列号sequence#

#通过WPS表格报表显示：明显备库不存在的SEQUENCE#

#查询主库-sequence# SQL> select sequence# from v$archived_log where sequence#>=140513 group by sequence# order by 1;

#查询备库-sequence# idle>select sequence# from v$archived_log where sequence#>=(select LOW_SEQUENCE# min from v$archive_gap) order by 1;

#选择两列 =》 开始菜单=》条件格式=>突出显示单元格规则=》重复值突出！！！

SEQUENCE#		SEQUENCE#
----------		----------
140513		140514
140514		140515
140515		140516

###还可以使用外部表，可以两个表进行集合运算找出备库缺失的sequence#,本次直接使用offlice表格工具，对比统计sequence#