TOM kyte提供的一个数据库迁移方式

关键字: 物化视图, migration,materialized views,快照,prebuilt table

对于要求停机时间很短的迁移,可以一试.

a) create empty tables in new database....
b) put constraints etc on them (deferrable please, important for mview groups!)
c) refresh them (fill them up)
d) put mv logs on remote tables
e) keep fast refreshing after that (could switch d/c above to avoid possible double complete refresh)
f) when ready to cut over -- kill access to old db, do a last refresh -- drop materialized views and there you go.

ops$tkyte@ORA9IR2> create table emp as select * from emp@ora9ir1 where 1=0;
Table created.

ops$tkyte@ORA9IR2> create table dept as select * from dept@ora9ir1 where 1=0;
Table created.

ops$tkyte@ORA9IR2> alter table emp add constraint emp_pk primary key ( empno ) deferrable;
Table altered.

ops$tkyte@ORA9IR2> alter table dept add constraint dept_pk primary key ( deptno ) deferrable;
Table altered.

ops$tkyte@ORA9IR2> alter table emp add constraint emp_fk_dept foreign key (deptno ) references dept(deptno) deferrable;
Table altered.

ops$tkyte@ORA9IR2> create materialized view emp on prebuilt table refresh complete as
select * from emp@ora9ir1;
Materialized view created.

ops$tkyte@ORA9IR2> create materialized view dept on prebuilt table
refresh complete as select * from dept@ora9ir1;
Materialized view created.

ops$tkyte@ORA9IR2> begin
dbms_refresh.make(
name =>'group1',
list =>'emp,dept',
next_date =>sysdate,
interval =>'sysdate+1/24',
implicit_destroy =>true);
end;
/

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> exec dbms_refresh.refresh('GROUP1' );
PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select mview_name, last_refresh_type from user_mviews;

MVIEW_NAME LAST_REF
------------------------------ --------
DEPT COMPLETE
EMP COMPLETE

ops$tkyte@ORA9IR2> @connect scott/tiger@ora9ir1.localdomain
ops$tkyte@ORA9IR2> set termout off
scott@ORA9IR1> REM GET afiedt.buf NOLIST
scott@ORA9IR1> set termout on
scott@ORA9IR1> create materialized view log on emp;
Materialized view log created.

scott@ORA9IR1> create materialized view log on dept;
Materialized view log created.

scott@ORA9IR1> @connect /
scott@ORA9IR1> set termout off
ops$tkyte@ORA9IR2> REM GET afiedt.buf NOLIST
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> alter materialized view emp refresh fast;
Materialized view altered.

ops$tkyte@ORA9IR2> alter materialized view dept refresh fast;
Materialized view altered.

ops$tkyte@ORA9IR2> @connect scott/tiger@ora9ir1.localdomain
ops$tkyte@ORA9IR2> set termout off
scott@ORA9IR1> REM GET afiedt.buf NOLIST
scott@ORA9IR1> set termout on
scott@ORA9IR1> update emp set ename = initcap(ename) where rownum = 1;
1 row updated.

scott@ORA9IR1> update dept set dname = initcap(dname) where rownum = 1;
1 row updated.

scott@ORA9IR1> commit;
Commit complete.

scott@ORA9IR1>
scott@ORA9IR1> @connect /
scott@ORA9IR1> set termout off
ops$tkyte@ORA9IR2> REM GET afiedt.buf NOLIST
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> exec dbms_refresh.refresh( 'GROUP1' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select mview_name, last_refresh_type from user_mviews;

MVIEW_NAME LAST_REF
------------------------------ --------
DEPT FAST
EMP FAST

ops$tkyte@ORA9IR2> drop materialized view emp;
Materialized view dropped.

ops$tkyte@ORA9IR2> drop materialized view dept;
Materialized view dropped.

ops$tkyte@ORA9IR2> select count(*) from emp;

COUNT(*)
----------
14

ops$tkyte@ORA9IR2> select count(*) from dept;

COUNT(*)
----------
4

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60565/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/936/viewspace-60565/