带主体复制环境的数据库迁移

带主体复制环境的数据库迁移

上一篇 / 下一篇  2009-06-12 13:56:15 / 个人分类：Oracle

查看( 74 ) / 评论( 3 )

源数据库：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！