环境简要介绍：

客户需要把运行在windows平台下11gR1下的db尽可能的在线迁移到aix6.1 rac11gR1下，数据库量不大，不到5g，在权衡各种因素之后选择了mv刷新的办法，通过测试，效果还算理想，客户可以接受，停机时间大约5分钟，通过优化可能还能继续缩短停机时间，由于在停机迁移db的同时，还有和应用相关的很多东西都需要修改，这个时间大约也需要5分钟左右，因此暂时没有做进一步的优化

下面是整个数据迁移的过程和步骤：

术语：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各用户对象的数量

select owner,object_type,status,count(*)
from dba_objects@dbl
where owner in ('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．启用应用