一次利用mv線上遷移資料、切換系統的過程

環境簡要介紹：

客戶需要把執行在windows平臺下11gR1下的db儘可能的線上遷移到aix6.1 rac11gR1下，資料庫量不大，不到5g，在權衡各種因素之後選擇了mv重新整理的辦法，透過測試，效果還算理想，客戶可以接受，停機時間大約5分鐘，透過最佳化可能還能繼續縮短停機時間，由於在停機遷移db的同時，還有和應用相關的很多東西都需要修改，這個時間大約也需要5分鐘左右，因此暫時沒有做進一步的最佳化

[@more@]

下面是整個資料遷移的過程和步驟：

術語：53代表aix系統上的db，26代表windows上的db

1． 在53上drop之前建立的使用者：

drop user user1 cascade;

drop user user2 cascade;

2．在53上create user：

create user USER1
identified by irc
default tablespace TBS_COMMON
temporary tablespace TEMP
profile DEFAULT;-- Grant/Revoke role privileges
grant connect to USER1;
grant exp_full_database to USER1;
grant imp_full_database to USER1;
grant resource to USER1;-- Grant/Revoke system privileges
grant alter any table to USER1;
grant create any table to USER1;
grant drop any table to USER1;
grant unlimited tablespace to USER1;

--========================================

create user USER2
identified by user2
default tablespace TBS_USER2
temporary tablespace TEMP
profile DEFAULT;-- Grant/Revoke role privileges
grant connect to USER2;
grant exp_full_database to USER2;
grant imp_full_database to USER2;
grant resource to USER2;-- Grant/Revoke system privileges
grant unlimited tablespace to USER2;

--=============================

3．在26上drop mv log（如果事先測試時已經建立）

select 'drop materialized view log on '||owner||'.'||table_name||';' from dba_tables
where owner in ('USER1','USER2')
and tablespace_name is not null AND table_name not like 'MLOG$_%'
and table_name not in (select table_name from dba_tab_cols where owner in ('USER1','USER2')
and data_type in ('LONG','LONG RAW')
)
4.從26匯出2個使用者並且匯入到53：

c:exp ‘sys/system@win26 as sysdba’ file=c:test.dmp owner=(‘user1’,’USER2’) rows=n statistics=none

imp user1/irc@aix53 file=c:test.dmp fromuser=user1 touser=user1

imp user2/user2@aix53 file=c:test.dmp fromuser=user2 touser=user2

5．在53上編譯無效物件

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects
where owner in ('USER1','USER2')
and status='INVALID'

6.清空26回收站裡的物件

7． 比較26和53各使用者物件的數量

'USER1','USER2')
and object_type<>'MATERIALIZED VIEW'
and object_name not in (
select log_table from dba_mview_logs
)
and object_name not like 'MLOG$_%'

and object_name not like 'RUPD$_%'
group by owner,object_type,status
order by owner,object_type,status

8．比較約束的數量

select owner,constraint_type,count(*)
from dba_constraints@dbl
where owner in ('USER1','USER2')
group by owner,constraint_type
order by owner,constraint_type

9．Disable53上的primary、foreign、trigger防止他們對重新整理資料產生的影響

1. diaable foreign key：

select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
from dba_constraints
where owner in ('USER1','USER2')
and constraint_type='R'
order by owner,table_name

--=========================

2. disable primary key：

select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
from dba_constraints
where owner in ('USER1','USER2')
and constraint_type='P'

order by owner,table_name

--============================

3. disable trigger：

select 'alter trigger '||owner||'."'||trigger_name||'" disable;'
from dba_triggers
where owner in ('USER1','USER2')
order by owner,table_name

10． 在26上執行下面指令碼的查詢結果建立materialized view log：

select 'create materialized view log on '||owner||'.'||table_name||' tablespace '||tablespace_name||';' from dba_tables
where owner in ('USER1','USER2')
and tablespace_name is not null AND table_name not like 'MLOG$_%'
and table_name not in (select table_name from dba_tab_cols where owner in ('USER1','USER2')
and data_type in ('LONG','LONG RAW')
)
order by owner,table_name

11. 在53上執行下面查詢結果建立materialized view：

select 'create materialized view '||owner||'.'||table_name||' on prebuilt table refresh fast as select * from '||owner||'.'||table_name||'@dbl;' from dba_tables
where owner in ('USER1','USER2')
and tablespace_name is not null and table_name not like 'MLOG$_%'
and table_name not in (select table_name from dba_tab_cols where owner in ('USER1','USER2')
and data_type in ('LONG','LONG RAW')
)
order by owner,table_name

12. 執53上執行下面查詢的結果進行完全重新整理

select 'exec dbms_mview.refresh('''||owner||'.'||mview_name||''',''complete'''||');' from dba_mviews
where owner in ('USER1','USER2')
order by owner,mview_name

13. 執行下面查詢結果把mv新增到重新整理組sys.ref_group中：（sys.ref_group已經事先在53上建立）

select 'exec dbms_refresh.add(name=>'||'''"SYS"'||'.'||'"REF_GROUP"'''
||','||'list=>'''||'"'||owner||'"'||'.'||'"'||mview_name||'"'||''''||');'
from dba_mviews
where owner in ('USER1','USER2')

13. 在53上增量重新整理資料使其儘可能的和26同步：

Exec dbms_refresh.refresh('ref_group');

14.蒐集53上3個使用者的statistics

A. exec dbms_stats.gather_schema_stats('user1');

B. exec dbms_stats.gather_schema_stats('user2');

15.從26上查出不能透過mv複製的表(存在long，long raw資料型別的表mv不支援)，目前只有NODE_PROPERTIES表

select owner,table_name,data_type from dba_tab_cols where owner in ('USER1','USER2') and data_type in ('LONG','LONG RAW');

16. 停止應用

17．執行c:temp exp_imp_node.bat從26上匯出表NODE_PROPERTIES的資料並且匯入到53上

18. Exec dbms_refresh.refresh('ref_group');

19.在53上處理沒有主鍵不能使用mv重新整理資料的表

select 'insert into '||owner||'.'||table_name||' select * from '||
owner||'.'||table_name||'@dbl;'
from (
select owner,table_name from dba_tables
where owner in ('USER1','USER2')
and tablespace_name is not null AND table_name not like 'MLOG$_%'
and table_name not in (select table_name from dba_tab_cols where owner in ('USER1','USER2')
and data_type in ('LONG','LONG RAW')
)
minus

select owner,table_name from dba_constraints
where owner in ('USER1','USER2')
and constraint_type='P'
)
20.驗證53和26上的資料是否一致,如果資料完全一致，繼續執行20步以後的操作，證明資料沒有問題，切換可以成功；如果資料不一致，情況比較麻煩，要看具體情況而定，必要時停止本次遷移，繼續使用老系統

declare
v26 number :=0;
v53 number :=0;
v_str26 varchar2(100);
v_str53 varchar2(100);
cursor c1 is select owner,mview_name from dba_mviews
where owner in ('USER1','USER2')
order by owner,mview_name;
begin
dbms_output.put_line('table_name '||' count53 '||'count26');
for i in c1 loop
v_str53:='select count(*) from '||i.owner||'.'||i.mview_name;
execute immediate v_str53 into v53;
v_str26:='select count(*) from '||i.owner||'.'||i.mview_name||'@dbl';
execute immediate v_str26 into v26;
if v53<>v26 then
dbms_output.put_line(i.owner||'.'||i.mview_name||' '||v53||' '||v26);
end if ;
end loop;

end;
22．執行下面查詢結果啟用pk、fk、trigger：

A.enable foreign key：

select 'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name||';'
from dba_constraints
where owner in ('USER1','USER2')
and constraint_type='P'
order by owner,table_name

--=========================

B.enable primary key：

select 'alter table '||owner||'.'||table_name||' ENABLE constraint '||constraint_name||';'
from dba_constraints
where owner in ('USER1','USER2')
and constraint_type='R'

order by owner,table_name

--============================

C.ENABLE trigger：

select 'alter trigger '||owner||'."'||trigger_name||'" enable;'
from dba_triggers
where owner in ('USER1','USER2')
order by owner,table_name

23.在53上編譯無效物件

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects
where owner in ('USER1','USER2')
and status='INVALID'

23．從53上drop掉mv

select 'drop materialized view '||owner||'.'||table_name||';'
from dba_tables
where owner in ('USER1','USER2')
and tablespace_name is not null and table_name not like 'MLOG$_%'
and table_name not in (select table_name from dba_tab_cols where owner in ('USER1','USER2')
and data_type in ('LONG','LONG RAW')
)
order by owner,table_name

24．啟用應用