问题现象：
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#