源数据库:windows 2003 32bit,oracle9.2.0.5
目标数据库:windows 2008 64bit,oracle 10.2.0.3
-- 在源数据库服务器上执行的操作
-- 1.全库备份
exp system/system file=d:\db_backup\db.dmp full=y direct=y log=d:\db_backup\db.log
-- shutdown数据库
-- 修正另一主体复制站点的tns,连接到新的服务器上
-- 在目标数据库服务器上执行的操作
-- 以sysdba登录
-- 2.创建源数据库有,目标数据库没有的表空间
CREATE TABLESPACE ywuser DATAFILE
'F:\oracle\product\10.2.0\oradata\testj\ywuser.DBF' SIZE 3000M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
......
-- 3.创建数据库用户
create user ywuser identified by ywuser;
grant dba to ywuser;
alter user ywuser default tablespace ywuser;
create user repadmin identified by repadmin;
grant resource,connect to repadmin;
-- 4给repadmin用户授权
begin
dbms_repcat_admin.grant_admin_any_schema(username=>'repadmin');
end;
/
grant comment any table to repadmin;
grant lock any table to repadmin;
grant select any dictionary to repadmin;
begin
dbms_defer_sys.register_propagator(username=>'repadmin');
end;
/
begin
dbms_repcat_admin.register_user_repgroup(username=>'repadmin',privilege_type=>'receiver',list_of_gnames=>null);
end;
/
-- 5.数据库参数修正
alter system set global_names=true;
alter database rename global_name to web.test;
-- 6.创建数据库链接
-- 注意:配置网络服务名test,连接到主体复制站点
DROP PUBLIC DATABASE LINK test.test;
CREATE PUBLIC DATABASE LINK test.testconnect to repadmin identified by repadmin USING 'test';
-- check it!
select * from global_name@test.test;
-- 7.全库导入:rows=n
imp system/systemfile=d:\db_backup\db.dmp buffer=40960000 full=y log=d:\db_backup\imp_full.log rows=n ignore=y indexes=n
-- 全库导入:rows=y
-- 把触发器置为无效
-- 连接到业务用户
spool disable_trigger.sql
select 'alter trigger '||trigger_name||' disable;'
from user_triggers
/
spool off
@disable_trigger.sql
-- 把其他用户的约束置为无效
spool disable_constrain.sql
select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
from dba_constraints
where owner in ('SH','OE','HR','PM','QS','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','SCOTT','PERFSTAT');
spool off
@disable_constrain.sql
host del disable_trigger.sql
host del disable_constrain.sql
-- 执行导入操作
imp system/systemfile=d:\db_backup\db.dmp buffer=40960000 full=y log=d:\db_backup\imp_full_data.log rows=y ignore=y feedback=10000
-- 8.开启入队功能:
BEGIN
dbms_aqadm.start_queue('SYSTEM.DEF$_AQCALL', TRUE, FALSE);
COMMIT;
END;
/
-- 9.测试复制环境
-- do sth
-- conn repadmin/repadmin
-- 监控延迟事务
select * from deftran where rownum < 10;
select count(*) from DEFTRANDEST;
select count(*) from deftran;
select count(*) from system.def$_aqcall;
select count(*) from defcall;
select count(*) from deferror;
-- Push所有的事务
variable v_ret number;
exec :v_ret := DBMS_DEFER_SYS.PUSH(destination=>'test.test',parallelism=>4,stop_on_error=>false);
commit;
-- Purge所有延迟事务
variable v_ret number;
exec :v_ret := DBMS_DEFER_SYS.PURGE(delay_seconds=>0);
commit;
-- 重新监控,并登陆到另一主体复制站点检查数据是否已复制
-- 把业务用户的触发器重置为有效
-- 连接到业务用户
spool enable_trigger.sql
select 'alter trigger '||trigger_name||' enable;'
from user_triggers
/
spool off
@enable_trigger.sql
-- ALL IS OK!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-606018/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/6906/viewspace-606018/