客户需要把运行在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防止他们对刷新数据产生的影响
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
- 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
- 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 fastas 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'''||');' fromdba_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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/14431099/viewspace-1212505/ ，如需转载，请注明出处，否则将追究法律责任。
