主備資料庫狀態手工比對（一）

具有備庫是所有生產系統的必備要求之一，不管使用什麼技術實現的主備庫資料同步，在做主備庫切換前手工比對一下各物件的一致狀態是必要步驟，下面分享筆者做狀態比對所使用的一些方法，供讀者參考和選擇。

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