具有备库是所有生产系统的必备要求之一，不管使用什么技术实现的主备库数据同步，在做主备库切换前手工比对一下各对象的一致状态是必要步骤，下面分享笔者做状态比对所使用的一些方法，供读者参考和选择。
1.创建目标端到源端的dblink，名称为gsht69 比对原端对象与目标端对象数 select a.owner,a.object_type,a.cnt src_cnt,decode(b.cnt,null,0,b.cnt) tgt_cnt from (select owner,object_type,count(*) cnt from dba_objects@gsht69 group by owner,object_type) a left join (select owner,object_type,count(*) cnt from dba_objects group by owner,object_type) b on a.owner=b.owner and a.object_type=b.object_type
2.如果哪里不同可以用下面的语句进行比较出来，注意要 双项比对
select owner,object_name from dba_objects@gsht69
where object_name not like 'BIN$%'
and object_type not like '%PARTITION%'
and generated='N'
and owner in ('TRADE')
and object_type='INDEX'
minus
select owner,object_name from dba_objects
where object_name not like 'BIN$%'
and object_type not like '%PARTITION%'
and generated='N'
and owner in ('TRADE')
and object_type='INDEX'
3. 比对主键和外键的约束个数
比对源端和目标端主键和外键的约束个数是否一致。
select a.owner,a.constraint_type,a.cnt src_cnt,decode(b.cnt,null,0,b.cnt) tgt_cnt from
(select owner,constraint_type,count(*) cnt from dba_constraints@gsht69 group by owner,constraint_type) a
left join
(select owner,constraint_type,count(*) cnt from dba_constraints group by owner,constraint_type) b
on a.owner=b.owner and a.constraint_type=b.constraint_type
where a.owner in ('TRADE') and a.constraint_type in ('P','R')
--and a.cnt<>decode(b.cnt,null,0,b.cnt);
如若有不同，可以用下面语句进行查询比对哪些不同
select table_name,constraint_name,CONSTRAINT_TYPE from dba_constraints@gsht69
where owner='TRADE'
--and constraint_type='C'
--and constraint_name not like 'BIN$%'
and constraint_name not like 'SYS%'
minus
select table_name,constraint_name,CONSTRAINT_TYPE from dba_constraints
where owner='TRADE'
--and constraint_type='C'
--and constraint_name not like 'BIN$%'
and constraint_name not like 'SYS%'
4. 比对对象状态
比对源端和目标端同一用户下的所有对象状态是否一致。
select owner, object_name, object_type, status
from dba_objects
where object_name in (select object_name
from dba_objects
where status = 'INVALID'
and owner in ('TRADE'))
and object_name not in (select object_name
from dba_objects@gsht69
where status = 'INVALID'
and owner in ('TRADE'))
and status = 'INVALID'
order by owner, object_type, object_name;
5. 比对序列值
比对源端和目标端序列值，检查源端是否有比目标端大的 sequence 。如果目标端 sequence 的 last_number 不小于源端的值，则数据正确。
select a.sequence_owner,a.sequence_name,a.last_number src_last_number,b.last_number tgt_last_number from
(select SEQUENCE_OWNER,SEQUENCE_NAME,LAST_NUMBER from dba_sequences@gsht69) a
left join
(select SEQUENCE_OWNER,SEQUENCE_NAME,LAST_NUMBER from dba_sequences) b
on a.sequence_owner=b.sequence_owner and a.sequence_name=b.sequence_name
where a.sequence_owner in ('TRADE') and a.last_number > b.last_number
