具有备库是所有生产系统的必备要求之一，不管使用什么技术实现的主备库数据同步，在做主备库切换前手工比对一下各对象的一致状态是必要步骤，下面分享笔者做状态比对所使用的一些方法，供读者参考和选择。

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