5.3.1综述
根据和应用工程师的沟通，了解到数据库的情况如下。
当前数据库的数据量为300G左右，数据库存在多张大表，都是blob字段的。
经过测试，如果采取传统的导入导出的方式进行数据迁移，在停止系统应用后，从源库导出数据耗时
在8小时左右，把数据导入目库又需耗时8小时，这样需要业务数据库停机达16小时，不能满足实
际的生产环境的要求。
考虑到这次的迁移方式是异构系统之间的(linux to aix)，我们最终决定采取流复制方案来进
行数据迁移。
5.3.2迁移步骤：
在目标库准备迁移环境:
- 在AIX平台的RAC数据库上，建立源库的数据库用户
- 建立与源库同名的表空间,并为表空间赋予相应的datafile
- 把表空间赋予相应的用户，
- 在目标库上建立listener.ora,tnsnames.ora
在源库准备数据迁移条件
- 开启源库的归档，设置流复制相关的初始化参数.
- 重启源库,使第5步设置的参数生效。(源库重启时间<10分钟）
- 在目库配置流复制（队列表、队列、应用进程）
- 在源库配置流复制（队列表、队列、capture process,propagation process）
- 首次同步（export 用参数<源> import<用参数>）
- 在源端启动捕获进程
验证流复制，调试应用
- 在源端配置心跳表
- 比对数据（数据割接当晚），确认迁移成功
- 修改应用对应到目库
5.3.3详细迁移步骤
1）对数据库的spfile进行备份:
create pfile='/home/oracle/pfile.ora' from spfile;
2）修改初始化参数
||||||||||||||||||||||||||||||||||||||||||||||
b.1 流复制环境准备
alter system set global_names=true scope = both;
alter system set compatible='10.2.0.3' scope=spfile;
alter system set job_queue_processes = 10 scope=both;
alter system set aq_tm_processes=1 scope=both;
alter system set logmnr_max_persistent_sessions=2 scope=spfile;
alter system set parallel_max_servers=135 scope=both;
alter system set open_links=4 scope=spfile;
alter system set sga_target = 4g scope=spfile;
alter system set streams_pool_size = 0 scope=both;
alter system set statistics_level='TYPICAL' scope=both;
alter system set undo_retention=3600 scope=both;
alter system set "_job_queue_interval"=1 scope=spfile;
show parameters global_names;
alter database force logging;
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
3>改动了上面的参数后，重新启动数据库：
4>设置数据库为归档模式:
conn / as sysdba
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/arch1' scope=spfile sid='bsp1921';
ALTER SYSTEM set log_archive_dest_1='LOCATION=/arch2' scope=spfile sid='bsp1922';
---for node1
alter system set log_archive_format ='bsp1921_%t_%s_%r.arc' scope=spfile sid='bsp1921';
---for node2
alter system set log_archive_format ='bsp1922_%t_%s_%r.arc' scope=spfile sid='bsp1922';
--shutdown all nodes;
conn / as sysdba
shutdown immediate;
---set the database to archive log mode;
startup mount
alter database archivelog;
alter database open;
--- startup the reset of the nodes;
conn / as sysdba
修改全局库名:
源库
Alter database rename global_name to bsp192.src;
目库
Alter database rename global_name to bsp.dst;
在源库为日志挖掘建立独立的表空间：
connect / as sysdba;
CREATE TABLESPACE logmnr_tbs DATAFILE ‘/dev/raw/raw67' SIZE 15600M;
BEGIN DBMS_LOGMNR_D.SET_TABLESPACE('logmnr_tbs'); END;
/
源端使能数据库级补充日志登载：
connect / as sysdba;
alter database add supplemental log data (PRIMARY KEY, UNIQUE, FOREIGN KEY) columns;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;(耗时很长>30分钟)
建流管理用户和授权(源端、目端都有)：
||||||||||||||
源端
connect / as SYSDBA
REM 流管理用户使用独立的表空间：
create user ADMSR identified by ADMSR;
CREATE TABLESPACE admsr_tbs DATAFILE '/dev/raw/raw68 ' SIZE 15600M;
ALTER USER admsr DEFAULT TABLESPACE sradm_tbs QUOTA UNLIMITED ON sradm_tbs;
GRANT DBA to ADMSR; /* 10g要求dba角色以简化配置 */
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('ADMSR'); /* 赋予流管理特权 */
||||||||||||
目端
connect / as SYSDBA
REM 流管理用户使用独立的表空间：
create user ADMSR identified by ADMSR;
CREATE TABLESPACE sradm_tbs DATAFILE '/dev/rlv_raw96_16g' SIZE 15600M;
ALTER USER admsr DEFAULT TABLESPACE sradm_tbs QUOTA UNLIMITED ON sradm_tbs;
GRANT DBA to ADMSR; /* 10g要求dba角色以简化配置 */
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('ADMSR'); /* 赋予流管理特权 */
修改源库和目库的tnsnames.ora
修改tnsnames.ora
|||||||||||||||||||||
src =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server67-v)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server68-v)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = bsp192)
)
)
dst =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = zhyw1v)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = zhyw2v)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = bsp192)
)
)
建立db_link
||||||||||||||||||||||||||||
源端做下面操作
||||||||||||
connect admsr/admsr ; /* 以下在源端建到目库的db link */
create database link bsp.dst connect to admsr identified by admsr using 'dst';
||||||||||||
目端做下面操作
connect admsr/admsr; /* 以下在目端建到源库的db link */
create database link bsp192.src connect to admsr identified by admsr using 'src';
准备目端的互置用户
DROP USER GIAPUSER CASCADE;
CREATE USER GIAPUSER
IDENTIFIED BY VALUES 'giapuser'
DEFAULT TABLESPACE bspdata
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for GIAPUSER
GRANT DBA TO GIAPUSER;
GRANT CONNECT TO GIAPUSER;
GRANT RESOURCE TO GIAPUSER;
ALTER USER GIAPUSER DEFAULT ROLE ALL;
-- 2 System Privileges for GIAPUSER
GRANT UNLIMITED TABLESPACE TO GIAPUSER;
GRANT SELECT ANY TABLE TO GIAPUSER;
-- 3 Object Privileges for GIAPUSER
GRANT DELETE, SELECT, UPDATE ON GIAPSYS.MSG_HISTORY TO GIAPUSER;
GRANT DELETE, INSERT ON GIAPSYS.MSG_QUEUE TO GIAPUSER;
GRANT SELECT ON GIAPSYS.SECT_SEQUENCE TO GIAPUSER;
把其他相关用户也建立起来
CREATE USER GIAPSYS
IDENTIFIED BY VALUES 'oracle'
DEFAULT TABLESPACE BSPSYS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 1 Role for GIAPSYS
GRANT DBA TO GIAPSYS;
ALTER USER GIAPSYS DEFAULT ROLE ALL;
-- 1 System Privilege for GIAPSYS
GRANT UNLIMITED TABLESPACE TO GIAPSYS;
在目端把这些用户建立起来。
GIAPCOUT (不导出数据)
DROP USER GIAPCOUNT CASCADE;
CREATE USER GIAPCOUNT
IDENTIFIED BY VALUES 'oracle'
DEFAULT TABLESPACE BSPCOUNT
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for GIAPCOUNT
GRANT DBA TO GIAPCOUNT;
GRANT CONNECT TO GIAPCOUNT;
GRANT RESOURCE TO GIAPCOUNT;
ALTER USER GIAPCOUNT DEFAULT ROLE ALL;
-- 1 System Privilege for GIAPCOUNT
GRANT UNLIMITED TABLESPACE TO GIAPCOUNT;
-- 2 Object Privileges for GIAPCOUNT
GRANT SELECT ON GIAPUSER.JCJ_CJXX TO GIAPCOUNT;
GRANT SELECT ON GIAPUSER.JCJ_JJXX TO GIAPCOUNT;
GIAPLOG (导出日志)
DROP USER GIAPLOG CASCADE;
CREATE USER GIAPLOG
IDENTIFIED BY VALUES 'oracle'
DEFAULT TABLESPACE BSPLOG
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 1 Role for GIAPLOG
GRANT DBA TO GIAPLOG;
ALTER USER GIAPLOG DEFAULT ROLE ALL;
-- 1 System Privilege for GIAPLOG
GRANT UNLIMITED TABLESPACE TO GIAPLOG;
HNSYS (导出日志)
DROP USER HNSYS CASCADE;
CREATE USER HNSYS
IDENTIFIED BY VALUES 'oracle'
DEFAULT TABLESPACE BSPDATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 1 Role for HNSYS
GRANT CONNECT TO HNSYS WITH ADMIN OPTION;
ALTER USER HNSYS DEFAULT ROLE ALL;
-- 1 System Privilege for HNSYS
GRANT SELECT ANY TABLE TO HNSYS WITH ADMIN OPTION;
-- 4 Object Privileges for HNSYS
GRANT SELECT ON SYS.V_$MYSTAT TO HNSYS;
GRANT SELECT ON SYS.V_$SESSION TO HNSYS;
GRANT SELECT ON SYS.V_$SESSTAT TO HNSYS;
GRANT SELECT ON SYS.V_$STATNAME TO HNSYS;
JLCX (导出日志)
DROP USER JLCX CASCADE;
CREATE USER JLCX
IDENTIFIED BY VALUES 'oracle'
DEFAULT TABLESPACE BSPTEST
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for JLCX
GRANT RESOURCE TO JLCX;
GRANT CONNECT TO JLCX;
ALTER USER JLCX DEFAULT ROLE ALL;
-- 2 System Privileges for JLCX
GRANT UNLIMITED TABLESPACE TO JLCX;
GRANT SELECT ANY TABLE TO JLCX;
-- 1 Object Privilege for JLCX
GRANT INSERT, SELECT, UPDATE ON GIAPSYS.USER_LIST TO JLCX;
JLSYS (导出日志)
DROP USER JLSYS CASCADE;
CREATE USER JLSYS
IDENTIFIED BY VALUES 'oracle'
DEFAULT TABLESPACE BSPDATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 1 Role for JLSYS
GRANT CONNECT TO JLSYS;
ALTER USER JLSYS DEFAULT ROLE ALL;
-- 1 Object Privilege for JLSYS
GRANT SELECT ON GIAPSYS.USER_LIST TO JLSYS;
配置流复制
流互置配置
目端（队列表、队列、应用进程）
# destination db 目标库操作，建立queue XDQ01(队列表XDT，队列用户ADMSR,队列名XDQ01),建立rules(用户为GIAPUSER，源库名为 # BSP192.SRC), ,流应用进程 XA01(进程名 XA01,应用到的用户GIAPUSER），修改APPLY的参数（应用进程名，出现应用错误的时候默认丢弃）
#打开这个job
connect admsr/admsr
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'DT', --队列表
queue_name => 'DQ01', --队列
queue_user => 'ADMSR'); --队列用户
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'GIAPUSER', --要应用的用户
streams_type => 'APPLY', --流类型为应用进程
streams_name => 'A01', --应用进程名
queue_name => 'ADMSR.DQ01', --应用进程从其出列LCRs的队列名
include_dml => true, --建立缺省DML规则
include_ddl => true, --建立缺省DDL规则
source_database => 'BSP192.SRC'); --源库名
END;
/
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'A01', --应用进程名
apply_user => 'GIAPUSER'); --应用进程要应用到的用户
END;
/
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'A01',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );
END;
/
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = 'A01';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'A01');
end if;
END;
/
源端（队列表、队列、capture process,propagation process)
# souce db 在目库中建立queue MSQ01(队列名 MSQ01,队列表名 MST，队列用户 为ADMSR)
connect admsr/admsr;
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name =>'SQ001', --队列名
queue_table =>'ST', --队列表名
queue_user => 'ADMSR'); --队列用户
END;
/
#设置捕获规则 （要捕获用户名为 GIAPUSER，捕获进程名 MC01，捕获的队列名，源数据库名 BSP192.SRC)
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'GIAPUSER', --要捕获的用户名
streams_type => 'CAPTURE', --流类型为捕获
streams_name => 'C001', --捕获进程名
queue_name => 'ADMSR.SQ001', --捕获进程要入列LCR的队列名
include_dml => true, --增加DML规则
include_ddl => true, --增加DDL规则
source_database => 'BSP192.SRC');
END;
/
#设置传播参数（传播对应的用户名为GIAPUSER，传播进程名 M2XP01，传播的源队列为 MSQ01,传播的目标队列为 XDQ01)
#源数据库的名字为BSP192.SRC
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'GIAPUSER', --要传播改变的用户名
streams_name => 'P001', --传播名
source_queue_name => 'ADMSR.SQ001', --传播的源队列
destination_queue_name => 'ADMSR.DQ01@BSP.DST', --传播的目队列
include_dml => true, --建立dml规则
include_ddl => true, --建立ddl规则
source_database => 'BSP192.SRC',
queue_to_queue => false);
END;
/
首次同步（export 用参数<源> import<用参数>）
# initiation 对数据进行初始化（在源库把用户EXP出来，选择 object_consistent=y)
nohup exp USERID=system/oracle@src OWNER=GIAPUSER FILE=BSP192.SRC_giapuser.dmp LOG=BSP192.SRC_giapuser_exp.log OBJECT_CONSISTENT=Y STATISTICS = NONE BUFFER=157286400 &
||||||||||||||||||||||||||||||||||||||||||||||
#对数据进行初始化，在目标库把源库导出的数据导入
nohup imp USERID=system/oracle@dst FULL=Y CONSTRAINTS=Y FILE=BSP192.SRC_giapuser.dmp IGNORE=Y COMMIT=Y LOG=BSP192.SRC_giapuser_imp.log STREAMS_INSTANTIATION=Y BUFFER=157286400 &
把giapuser下的trigger 禁止
使用toad做相关操作
capture 启动
SQL> exec dbms_capture_adm.start_capture(capture_name=>'C02');
PL/SQL procedure successfully completed.
源端互置用户中建立心跳表
源端管理用户作业定期更新心跳表
# heartbeat table 在源库建立心跳表(giapuser下的 htbt 表）
connect admsr/admsr@BSP192.SRC
alter session set nls_date_format='YYMMDD[HH24:MI:SS]';
create table giapuser.htbt(name varchar2(10) not null, rqsj date default sysdate not null);
alter table giapuser.htbt add constraint PK_HTBT primary key (name);
grant update,insert on giaplog.htbt to sradm;
#建立一个job，在源库不断更新心跳表的数据(admsr)
set serveroutput on
variable jobno number;
variable instno number;
begin
select instance_number into :instno from sys.v_$instance;
dbms_job.submit(:jobno, 'update giapuser.htbt set rqsj=sysdate;', trunc(sysdate+1/24/60,'MI'), 'trunc(SYSDATE+1/24/60,''MI'')', TRUE, :instno);
commit;
end;
# heartbeat table check 查看源库和目库的心跳表数据，是否一致了。
connect admsr/admsr@BSP192.DST
alter session set nls_date_format='YYMMDD[HH24:MI:SS]';
set linesize 120
select global_name, name, rqsj from global_name left join (select * from giapuser.htbt) on 1=1 union
select global_name, name, rqsj from global_name@BSP.DST left join (select * from giapuser.htbt@BSP.DST) on 1=1 ;
同样方法，对另外两个用户,jiaplog,jiapsys做基于用户的流复制。并用EM随时观察流复制的健康状态。
当晚数据迁移：
做数据比对操作:
在目库把RY_RXXX的重复值删除的存储过程:
CREATE OR REPLACE PROCEDURE giapuser.TMP100323_SP_DELDUP_RXXX
AS
CURSOR C1 IS SELECT RXZJ,TS FROM giapuser.tmp100323_RY_RXXX;
BEGIN
FOR REC_C1 IN C1 LOOP
DELETE FROM giapuser.RY_RXXX
WHERE RXZJ=REC_C1.RXZJ
and rownum<REC_C1.TS;
END LOOP;
END;
/
|||||||||||||
spool on之后写全库比对的脚本:
select
'select * from giapuser.'||tname||'@bsp192.src minus select * from giapuser.'||tname||';'
from
(
select distinct table_name tname from dba_tab_columns where owner='GIAPUSER' and data_type not in ('BLOB')
and data_type not like '%ROWID%'
)
|||||||||||||||||
开始做相关表的比对
drop table tmp100325_minus_ry_rxxx purge;
create table tmp100325_minus_ry_rxxx as
select RYBH,GMSFHM,XM,SLH,TXLY,RXFW,SFJD,TXSM,CZDW,CZR,WRITETIME,RXZJ,RXXB,RXCSRQ,RXMZ,RXRYSX,RXRYLB,XMPY,CZBS,CZSJ
from giapuser.RY_RXXX
minus
select RYBH,GMSFHM,XM,SLH,TXLY,RXFW,SFJD,TXSM,CZDW,CZR,WRITETIME,RXZJ,RXXB,RXCSRQ,RXMZ,RXRYSX,RXRYLB,XMPY,CZBS,CZSJ
from giapuser.RY_RXXX@bsp.dst
--exp USERID=giapuser/oracle buffer=102400000 file=exp_TMP_RY_RXXX_20091108.dmp LOG=exp_TMP_RY_RXXX_20091108.log /
-- COMPRESS=N ROWS=Y DIRECT=N recordlength=65535 FEEDBACK=10 tables=/(TMP_RY_RXXX_20091108/)
-- 源库
drop table tmp100325_minus_sj_rybh purge;
create table tmp100325_minus_sj_rybh as
select SJ_RYBH,SJ_CJBH,SJ_SJRYBH,SJ_GMSFHM,SJ_XM,ZZBH,XZZ,SJ_KY,SJ_WFSS,SJ_SJRYLB,RAS,SJ_BGRYZSRGX,SJ_ZSLYJGK,
SJ_QKQK,SJ_SYS,SJ_JBS,SJ_XDS,SZ_ZSDD,SJ_TBDW,SJ_TBR,SJ_TBSJ,SJ_CXSJ,SJ_CXLY,SJ_CXTBR,SJ_CXSPR,SJ_CXDW,
SJ_ZLRQK,SJ_FXQZ,SJ_FXJD,SJ_FXSJ,SJ_QZZL,SJ_TLYXQ,SJ_RLRXM,SJ_RLRXB,SJ_RLRSFZH,SJ_RLSJ,SJ_RLQK,CZDW,
CZR,CZSJ,CZBS from giapuser.JCJ_SJRY
minus
select SJ_RYBH,SJ_CJBH,SJ_SJRYBH,SJ_GMSFHM,SJ_XM,ZZBH,XZZ,SJ_KY,SJ_WFSS,SJ_SJRYLB,RAS,SJ_BGRYZSRGX,SJ_ZSLYJGK,
SJ_QKQK,SJ_SYS,SJ_JBS,SJ_XDS,SZ_ZSDD,SJ_TBDW,SJ_TBR,SJ_TBSJ,SJ_CXSJ,SJ_CXLY,SJ_CXTBR,SJ_CXSPR,SJ_CXDW,
SJ_ZLRQK,SJ_FXQZ,SJ_FXJD,SJ_FXSJ,SJ_QZZL,SJ_TLYXQ,SJ_RLRXM,SJ_RLRXB,SJ_RLRSFZH,SJ_RLSJ,SJ_RLQK,CZDW,
CZR,CZSJ,CZBS from giapuser.JCJ_SJRY@bsp.dst;
delete from giapuser.JCJ_SJRY@bsp.dst where SJ_RYBH in (select SJ_RYBH from tmp100325_minus_sj_rybh);
-- 0 rows
insert into giapuser.JCJ_SJRY@bsp.dst select * from giapuser.JCJ_SJRY
where SJ_RYBH in (select SJ_RYBH from tmp100325_minus_sj_rybh);
-- 2 rows
drop table tmp100325_minus_RY_JBXX purge;
create table tmp100325_minus_RY_JBXX as
select RYBH,RYZZBH,HH,GMSFHM,XM,XMPY,HKLB,TSHK,YHZGX,CYM,CYMPY,YWX,YWM,ZJZL,ZJHM,BMCH,
BMCHPY,XB,MZ,CSRQ,CSSJ,CSZMBH,CSDQ,CSQX,CSXZ,JHRYSFZH,JHRYXM,JHGXY,JHRESFZH,JHREXM,
JHGXE,FQSFZH,FQXM,MQSFZH,MQXM,POSFZH,POXM,JGDQ,JGQX,JGXZ,QFRQ,YXQX,ZJXY,ZZMM,WHCD,
HYZK,BYZK,SG,XX,SF,ZC,ZW,ZY,ZYLB,FWCS,LXDH,ZWSP,SWRQ,SWZXRQ,SWZXLB,SWZMBH,HJDBH,
GJDQ,HJQH,HJZRQ,HJXZ,ZZDBH,ZZDQH,ZZDZRQ,ZZDXZ,SJJZDQH,SJJZDZRQ,SJJZDXZ,ZZSY,ZZCS,
ZZZBH,ZZZQFRQ,ZZZJZRQ,QZZL,QZHM,QZYXQ,TLSY,RJSJ,NLKSJ,QTQX,QTZZXZ,GMLSZHY,GMLSZHE,
LSXMY,LSXMPYY,LSXME,LSXMPYE,LSXMS,LSXMPYS,HSLBS,HYLBS,HDQLBS,HSXLBS,HXZLBS,LKBSRQ,
LKBSYY,QWGJDQ,QWSSQX,QWXXDZ,ZWBH,DNABH,WFSBBH,XXJB,RYLB,RYSX,SFZX,RHQK,BZ,DJSJ,
SLSJ,CZDW,CZR,CZBS,CZSJ from giapuser.RY_JBXX
minus
select RYBH,RYZZBH,HH,GMSFHM,XM,XMPY,HKLB,TSHK,YHZGX,CYM,CYMPY,YWX,YWM,ZJZL,ZJHM,BMCH,
BMCHPY,XB,MZ,CSRQ,CSSJ,CSZMBH,CSDQ,CSQX,CSXZ,JHRYSFZH,JHRYXM,JHGXY,JHRESFZH,JHREXM,
JHGXE,FQSFZH,FQXM,MQSFZH,MQXM,POSFZH,POXM,JGDQ,JGQX,JGXZ,QFRQ,YXQX,ZJXY,ZZMM,WHCD,
HYZK,BYZK,SG,XX,SF,ZC,ZW,ZY,ZYLB,FWCS,LXDH,ZWSP,SWRQ,SWZXRQ,SWZXLB,SWZMBH,HJDBH,
GJDQ,HJQH,HJZRQ,HJXZ,ZZDBH,ZZDQH,ZZDZRQ,ZZDXZ,SJJZDQH,SJJZDZRQ,SJJZDXZ,ZZSY,ZZCS,
ZZZBH,ZZZQFRQ,ZZZJZRQ,QZZL,QZHM,QZYXQ,TLSY,RJSJ,NLKSJ,QTQX,QTZZXZ,GMLSZHY,GMLSZHE,
LSXMY,LSXMPYY,LSXME,LSXMPYE,LSXMS,LSXMPYS,HSLBS,HYLBS,HDQLBS,HSXLBS,HXZLBS,LKBSRQ,
LKBSYY,QWGJDQ,QWSSQX,QWXXDZ,ZWBH,DNABH,WFSBBH,XXJB,RYLB,RYSX,SFZX,RHQK,BZ,DJSJ,
SLSJ,CZDW,CZR,CZBS,CZSJ from giapuser.RY_JBXX@bsp.dst;
delete from giapuser.RY_JBXX@bsp.dst where RYBH in (select RYBH from tmp100325_minus_RY_JBXX);
-- 4 rows deleted
insert into giapuser.RY_JBXX@bsp.dst select * from giapuser.RY_JBXX
where RYBH in (select RYBH from tmp100325_minus_RY_JBXX);
-- 4 rows created
drop table tmp100325_minus_JG_CYRY purge;
create table tmp100325_minus_JG_CYRY as
select CYRY_BH,CYRY_RYBH,CYRY_JGBH,CYRY_GMSFHM,CYRY_XM,CYRY_ZZBH,CYRY_XZZ,CYRY_SX,CYRY_BMMC,CYRY_GZLY,CYRY_PYQX,
CYRY_ZW,CYRY_SJ,CYRY_QTLXDH,RAS,CYRY_ZSQK,CYRY_PCSYJ,CYRY_PXZH,CYRY_PXZS,CYRY_ZXYY,CYRY_ZXBS,CYRY_ZXRQ,
CYRY_ZDFWDXLB,CYRY_BWCS,CYRY_ZYGX,CYRY_ZDRTLB,CYRY_KZCS,CYRY_ZYWT,CYRY_BZ,CZDW,CZR,CZSJ,CYRY_DJDW,
CYRY_DJR,CYRY_DJRQ,CZBS from giapuser.JG_CYRY
minus
select CYRY_BH,CYRY_RYBH,CYRY_JGBH,CYRY_GMSFHM,CYRY_XM,CYRY_ZZBH,CYRY_XZZ,CYRY_SX,CYRY_BMMC,CYRY_GZLY,CYRY_PYQX,
CYRY_ZW,CYRY_SJ,CYRY_QTLXDH,RAS,CYRY_ZSQK,CYRY_PCSYJ,CYRY_PXZH,CYRY_PXZS,CYRY_ZXYY,CYRY_ZXBS,CYRY_ZXRQ,
CYRY_ZDFWDXLB,CYRY_BWCS,CYRY_ZYGX,CYRY_ZDRTLB,CYRY_KZCS,CYRY_ZYWT,CYRY_BZ,CZDW,CZR,CZSJ,CYRY_DJDW,
CYRY_DJR,CYRY_DJRQ,CZBS from giapuser.JG_CYRY@bsp.dst;
delete from giapuser.JG_CYRY@bsp.dst where CYRY_BH in (select CYRY_BH from tmp100325_minus_JG_CYRY);
-- 0 rows deleted
insert into giapuser.JG_CYRY@bsp.dst select * from giapuser.JG_CYRY
where CYRY_BH in (select CYRY_BH from tmp100325_minus_JG_CYRY);
-- 2 rows created
5.3.1综述
根据和应用工程师的沟通，了解到数据库的情况如下。
当前数据库的数据量为300G左右，数据库存在多张大表，都是blob字段的。
经过测试，如果采取传统的导入导出的方式进行数据迁移，在停止系统应用后，从源库导出数据耗时
在8小时左右，把数据导入目库又需耗时8小时，这样需要业务数据库停机达16小时，不能满足实
际的生产环境的要求。
考虑到这次的迁移方式是异构系统之间的(linux to aix)，我们最终决定采取流复制方案来进
行数据迁移。
5.3.2迁移步骤：
在目标库准备迁移环境:
- 在AIX平台的RAC数据库上，建立源库的数据库用户
- 建立与源库同名的表空间,并为表空间赋予相应的datafile
- 把表空间赋予相应的用户，
- 在目标库上建立listener.ora,tnsnames.ora
在源库准备数据迁移条件
- 开启源库的归档，设置流复制相关的初始化参数.
- 重启源库,使第5步设置的参数生效。(源库重启时间<10分钟）
- 在目库配置流复制（队列表、队列、应用进程）
- 在源库配置流复制（队列表、队列、capture process,propagation process）
- 首次同步（export 用参数<源> import<用参数>）
- 在源端启动捕获进程
验证流复制，调试应用
- 在源端配置心跳表
- 比对数据（数据割接当晚），确认迁移成功
- 修改应用对应到目库
扫一扫
323
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
