oracle 데이터 이관후 정합성 체크

1. 원본/대상 비교용 임시 테이블 생성 

create table test..verify_tab_cnt as
select t.owner
     , t.table_name
     , (select round(sum(bytes)/1024/1024) from dba_segments s where s.owner = t.owner and s.segment_name = t.table_name) size_MB
     , (select partitioning_type from dba_part_tables p where p.owner = t.owner and p.table_name = t.table_name) partitioning_type
     , cast(null as number) as scn
     , cast(null as number) as asis_cnt
     , cast(null as number) as tobe_cnt
     , cast(null as date  ) as asis_start_dt
     , cast(null as date  ) as tobe_start_dt
     , cast(null as date  ) as asis_end_dt
     , cast(null as date  ) as tobe_end_dt
from dba_tables t
where 1=1
and owner in (select username from dba_users where account_status = 'OPEN' and username not like 'SYS%' and username not in ('OGG','DBSNMP'))
order by owner, table_name

 

2. 대상 DB 특정 scn값으로 해당 시점에 테이블별 count수 확인

만약 parallel replicat를 사용중이라면 해당 replicat를 중지하고 synchorinize replicat 작업후 count수 확인

 

declare

    l_scn   constant number := nnnnn; 
    l_count number := 0;

begin

    dbms_output.enable(null);

    execute immediate 'alter session set db_file_multiblock_read_count = 128'; 

    for t_row in ( 
        select * 
          from ( 
            select owner 
                 , table_name 
                 , case when size_MB < 100  then 1 
                        when size_MB < 500  then 2 
                        when size_MB < 5000 then 4 
                        else 8 
                   end as degree 
                 , rowid as v_rid 
              from test.verify_tab_cnt 
             where owner in ('TEST1','TEST2','TEST3') 
               and scn is null 
               and lnnvl(partitioning_type='RANGE') 
             order by size_MB, owner, table_name 
            ) 
    loop

        update spadmin.verify_tab_cnt set scn = l_scn, tobe_start_dt = sysdate where rowid = t_row.v_rid;

        execute immediate 'select /*+ index_ffs(t) parallel('||t_row.degree||') */count(*)

                                  from '|| t_row.owner ||'.'|| t_row.table_name || ' as of scn '||l_scn||' t' into l_count;

        update spadmin.verify_tab_cnt set tobe_cnt = l_count, tobe_end_dt = sysdate where rowid = t_row.v_rid;

        commit;

    end loop;
end;
/

 

3. 원본 DB 특정 scn 값으로 해당 시점에 테이블별 count수 확인 

declare

    l_scn   constant number := nnnnn;
    l_count number := 0;

begin

    dbms_output.enable(null);

    execute immediate 'alter session set db_file_multiblock_read_count = 128';

    for t_row in (
        select *
          from (
            select owner
                 , table_name
                 , case when size_MB < 100  then 1
                        when size_MB < 500  then 2
                        when size_MB < 5000 then 4
                        else 8
                   end as degree
                 , rowid as v_rid
              from test.verify_tab_cnt
             where owner in ('TEST1','TEST2','TEST3')
               and scn is null
               and lnnvl(partitioning_type='RANGE')
             order by size_MB, owner, table_name
            )
    loop

        update spadmin.verify_tab_cnt set scn = l_scn, asis_start_dt = sysdate where rowid = t_row.v_rid;

        execute immediate 'select /*+ index_ffs(t) parallel('||t_row.degree||') */count(*)

                                  from '|| t_row.owner ||'.'|| t_row.table_name || ' as of scn '||l_scn||' t' into l_count;

 

        update test.verify_tab_cnt set asis_cnt = l_count, asis_end_dt = sysdate where rowid = t_row.v_rid;

        commit;

    end loop;
end;
/

 

4. 비교

위에 원본에 있는 비교 테이블을 대상 DB로 이관하여 비교 진행

merge into test.verify_tab_cnt t
using (
select *
from test.verify_tab_cnt_asis
where scn is not null
and owner in ('TEST1','TEST2','TEST3')
) s
on (t.owner = s.owner and t.table_name = s.table_name and t.scn = s.scn)
when matched then
update
set t.asis_cnt      = s.asis_cnt
  , t.asis_start_dt = s.asis_start_dt
  , t.asis_end_dt   = s.asis_end_dt
;