使用OGG+expdp迁移用户时，job的处理方法

本次实验的目的是确认使用OGG+expdp迁移用户时，job的处理方法


***************************************************


源端：


创建新用户tmp_user并授权：
sqlplus / as sysdba
create user tmp_user identified by tmp_user default tablespace users;
grant connect,resource to tmp_user;


连接到该用户，创建测试用表和主键索引、序列：
conn tmp_user/tmp_user;
create table a (aid number,avalue varchar2(10));
create unique index uniq_aid on a(aid);
alter table a add constraint pk_a_id primary key(aid) using index;
create sequence seq_a start with 1;


创建job，每分钟向该表插入数据：
variable job number;
begin
  sys.dbms_job.submit(job => :job,
                      what => 'insert into a select seq_a.nextval,to_char(sysdate,''hh24:mi:ss'') from dual; commit;',
                      next_date => sysdate,
                      interval => 'sysdate+1/1440');
  commit;
end;
/


创建ogg进程，用于从源端同步tmp_user用户下的所有对象到目标端的tmp_user下：


<1>创建抽取进程
GGSCI (rhlinux) 1> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT_1       00:00:00      00:00:03    
EXTRACT     RUNNING     PUMP_1      00:00:00      00:00:00    




GGSCI (rhlinux) 2> dblogin userid ogg password ogg
aSuccessfully logged into database.


GGSCI (rhlinux) 3> dd extract e_tmp,tranlog ,begin now
EXTRACT added.




GGSCI (rhlinux) 4> add exttrail ./dirdat/tp,extract e_tmp
EXTTRAIL added.




GGSCI (rhlinux) 5> eidt params e_tmp
ERROR: Invalid command.


GGSCI (rhlinux) 6> edit params e_tmp  




GGSCI (rhlinux) 7> view params e_tmp


extract e_tmp
userid ogg password ogg
exttrail ./dirdat/tp
table tmp_user.*;


<2>创建投递进程：


GGSCI (rhlinux) 8> add extract p_tmp,exttrailsource ./dirdat/tp,begin now
EXTRACT added.




GGSCI (rhlinux) 9> add rmttrail ./dirdat/tp,extract p_tmp
RMTTRAIL added.




GGSCI (rhlinux) 10> edit params p_tmp






GGSCI (rhlinux) 11> view params p_tmp


extract p_tmp
userid ogg password ogg
rmthost 192.168.73.134,mgrport 7811
rmttrail ./dirdat/tp
passthru
table tmp_user.a;




<3>启动进程并查看是否有同步：


GGSCI (rhlinux) 12> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT_1       00:00:00      00:00:05    
EXTRACT     STOPPED     E_TMP       00:00:00      00:09:26    
EXTRACT     RUNNING     PUMP_1      00:00:00      00:00:01    
EXTRACT     STOPPED     P_TMP       00:00:00      00:02:42    




GGSCI (rhlinux) 13> start e_tmp


Sending START request to MANAGER ...
EXTRACT E_TMP starting




GGSCI (rhlinux) 14> start p_tmp


Sending START request to MANAGER ...
EXTRACT P_TMP starting




GGSCI (rhlinux) 15> stats p_tmp


Sending STATS request to EXTRACT P_TMP ...


Start of Statistics at 2015-07-14 23:40:44.


Output to ./dirdat/tp:


Extracting from TMP_USER.A to TMP_USER.A:


*** Total statistics since 2015-07-14 23:40:10 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00


*** Daily statistics since 2015-07-14 23:40:10 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00


*** Hourly statistics since 2015-07-14 23:40:10 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00


*** Latest statistics since 2015-07-14 23:40:10 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00


End of Statistics.


GGSCI (rhlinux) 16> exit


查看当前scn号，基于scn导出tmp_user下的所有数据：


[oracle@rhlinux ogg]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 14 23:43:48 2015


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> col current_scn for 9999999999999999999999   
SQL> select current_scn from v$database;


            CURRENT_SCN
-----------------------
               10287659


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options




[oracle@rhlinux u02]$ expdp system/system schemas=tmp_user dumpfile=tmp_user.dmp logfile=expdp_tmp_user.log directory=U02 flashback_scn=10287659


Export: Release 11.2.0.1.0 - Production on Tue Jul 14 23:47:46 2015


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_04":  system/******** schemas=tmp_user dumpfile=tmp_user.dmp logfile=expdp_tmp_user.log directory=U02 flashback_scn=10287659 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
. . exported "TMP_USER"."A"                              5.679 KB      18 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_04 is:
  /u02/tmp_user.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully completed at 23:48:33


传输到目标端：
[oracle@rhlinux u02]$ scp *tmp_user*   192.168.73.134:/home/oracle
oracle@192.168.73.134's password: 
expdp_tmp_user.log                                                                                                                   100% 1657     1.6KB/s   00:00    
tmp_user.dmp                                                                                                                         100%  192KB 192.0KB/s   00:00 


目标端导入：


[oracle@ogg dirdat]$ impdp system/system dumpfile=tmp_user.dmp logfile=impdp_tmp_user.log directory=ORA_HOME


Import: Release 11.2.0.1.0 - Production on Sat Jul 18 07:06:24 2015


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=tmp_user.dmp logfile=impdp_tmp_user.log directory=ORA_HOME 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TMP_USER"."A"                              5.679 KB      18 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 07:06:53




创建replicat进程
[oracle@ogg ogg]$ ./ggsci


Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25


Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.






GGSCI (ogg) 1> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP_1       00:00:00      00:00:01    




GGSCI (ogg) 2> add replicat r_tmp ,exttrail ./dirdat/tp,checkpointtable ogg.checkpoint
REPLICAT added.


GGSCI (ogg) 3> edit params r_tmp


GGSCI (ogg) 4> view params r_tmp


replicat r_tmp
userid ogg password ogg
assumetargetdefs
discardfile ./dirout/discard_r_tmp.log,purge ,megebytes 100
handlecollisions
REPERROR (-1,DISCARD)
REPERROR (-1403,DISCARD)


map tmp_user.a,target tmp_user.a;




启动replicat进程，查看状态
GGSCI (ogg) 5>  start r_tmp ,aftercsn 10287659


Sending START request to MANAGER ...
REPLICAT R_TMP starting




GGSCI (ogg) 6> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP_1       00:00:00      00:00:06    
REPLICAT    RUNNING     R_TMP       00:00:00      00:00:03  


ggserr.log显示内容为：
2015-07-18 07:12:56  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, r_tmp.prm:  REPLICAT R_TMP started.
2015-07-18 07:12:56  INFO    OGG-01373  Oracle GoldenGate Delivery for Oracle, r_tmp.prm:  User requested start after CSN 10287659.
2015-07-18 07:12:56  INFO    OGG-01374  Oracle GoldenGate Delivery for Oracle, r_tmp.prm:  Transaction delivery commencing at position Seqno 0, RBA 1593, Transaction ID 19.20.2295, CSN 10287732, 4 transaction(s) skipped.
2015-07-18 07:12:56  WARNING OGG-03504  Oracle GoldenGate Delivery for Oracle, r_tmp.prm:  NLS_LANG character set AL32UTF8 on the target is different from the source database character set WE8MSWIN1252. Replication may not be valid if the source data has an incompatible character for the target NLS_LANG character set.


核对数据：
@源端
[oracle@rhlinux u02]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 15 00:01:19 2015


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select count(1) from tmp_user.a;


  COUNT(1)
----------
        36
        
@目标端
[oracle@ogg ogg]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 18 07:16:12 2015


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select count(1) from tmp_user.a;


  COUNT(1)
----------
        45
        
由于目标端的tmp_user.a来源于两部分：
（1）从源端通过OGG同步过来
（2）目标端的job定时插入数据


因此数据比预期多。




教训和解决方法：
使用OGG+expdp迁移数据时，应该将目标端的job暂时停掉，在应用切换时再开启。


具体暂停方法：
column SCHEMA_USER for a10
column what for a30
column interval for a30
column SCHEMA_USER for a10
SQL>        SELECT job,a.SCHEMA_USER,a.LAST_DATE, a.NEXT_DATE, a.interval,what,a.FAILURES,a.BROKEN FROM Dba_Jobs a ORDER BY a.SCHEMA_USER;


       JOB SCHEMA_USE LAST_DATE NEXT_DATE INTERVAL
---------- ---------- --------- --------- ------------------------------
WHAT                             FAILURES B
------------------------------ ---------- -
      4001 APEX_03020 18-JUL-15 18-JUL-15 sysdate + 8/24
           0
wwv_flow_cache.purge_sessions(          0 N
p_purge_sess_older_then_hrs =>
 24);


      4002 APEX_03020 18-JUL-15 18-JUL-15 sysdate + 10/1440
           0
wwv_flow_mail.push_queue(wwv_f          0 N




       103 TMP_USER   18-JUL-15 18-JUL-15 sysdate+1/1440
insert into a select seq_a.nex          0 N
tval,to_char(sysdate,'hh24:mi:
ss') from dual; commit;


修改job必须在其创建者的schema下。及时在sys用户下也无法修改其他用户下的job
SQL> exec dbms_job.broken(103,true,sysdate+1000);
BEGIN dbms_job.broken(103,true,sysdate+1000); END;


*
ERROR at line 1:
ORA-23421: job number 103 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 793
ORA-06512: at "SYS.DBMS_JOB", line 254
ORA-06512: at line 1




SQL> conn tmp_user/tmp_user
Connected.
SQL> exec dbms_job.broken(103,true,sysdate+1000);


PL/SQL procedure successfully completed.


查看状态：
SQL> set lines 200
SQL> SELECT job,a.SCHEMA_USER,a.LAST_DATE, a.NEXT_DATE, a.interval,what,a.FAILURES,a.BROKEN FROM user_jobs a ORDER BY a.SCHEMA_USER;


       JOB SCHEMA_USE LAST_DATE NEXT_DATE INTERVAL                       WHAT                             FAILURES B
---------- ---------- --------- --------- ------------------------------ ------------------------------ ---------- -
       103 TMP_USER   18-JUL-15 01-JAN-00 sysdate+1/1440                 insert into a select seq_a.nex          0 Y
                                                                         tval,to_char(sysdate,'hh24:mi:
                                                                         ss') from dual; commit;
                                                                         
最后一列B（BROKEN）=Y表示当前job是broken的状态。
需要注意：如果执行dbms_job.broken时该job正在执行，虽然查看到当前状态为broken，但job本次执行完成后会自动改为原状态（非broken）
（见官方文档PL/SQL Packages and Types Reference 812页：
Note: If you set job as broken while it is running, Oracle resets the
job's status to normal after the job completes. Therefore, only
execute this procedure for jobs that are not running.）






迁移时还需要注意sequence。
@源端
SQL> conn / as sysdba
Connected.
SQL> set lines 200
SQL> select 
  2  d.sequence_owner,d.sequence_name,d.min_value,d.max_value,d.last_number,d.increment_by,d.cache_size
  3  from dba_sequences d
  4  where d.sequence_owner='TMP_USER';


SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE LAST_NUMBER INCREMENT_BY CACHE_SIZE
------------------------------ ------------------------------ ---------- ---------- ----------- ------------ ----------
TMP_USER                       SEQ_A                                   1 1.0000E+28         161            1         20




@目标端
SQL> conn / as sysdba
Connected.
SQL> set lines 200
SQL> select 
  2  d.sequence_owner,d.sequence_name,d.min_value,d.max_value,d.last_number,d.increment_by,d.cache_size
  3  from dba_sequences d
  4  where d.sequence_owner='TMP_USER';


SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE LAST_NUMBER INCREMENT_BY CACHE_SIZE
------------------------------ ------------------------------ ---------- ---------- ----------- ------------ ----------
TMP_USER                       SEQ_A                                   1 1.0000E+28         121            1         20


注：目标端last_number不是1的原因是执行job调用了该序列。停掉job后无增长了。


在目标端修改序列的方法：
SQL> alter sequence tmp_user.seq_a increment by 40;


Sequence altered.


SQL> select tmp_user.seq_a.nextval from dual;


   NEXTVAL
----------
       152


SQL> alter sequence tmp_user.seq_a increment by 1;


Sequence altered.
其中increment by的值可以稍微大一点，目的就是避免目标端出现主键冲突问题（本测试中目标端表未设置约束）。






切换应用时，需要将job重新启动（改为非broken状态），同时停掉replicat进程，避免数据重复更新
SQL> conn tmp_user/tmp_user
Connected.
SQL> exec dbms_job.broken(103,false,sysdate+1/1440);


PL/SQL procedure successfully completed.


SQL>set lines 200
SQL> SELECT job,a.SCHEMA_USER,a.LAST_DATE, a.NEXT_DATE, a.interval,what,a.FAILURES,a.BROKEN FROM user_jobs a ORDER BY a.SCHEMA_USER;


       JOB SCHEMA_USE LAST_DATE NEXT_DATE INTERVAL                       WHAT                             FAILURES B
---------- ---------- --------- --------- ------------------------------ ------------------------------ ---------- -
       103 TMP_USER   18-JUL-15 18-JUL-15 sysdate+1/1440                 insert into a select seq_a.nex          8 N
                                                                         tval,to_char(sysdate,'hh24:mi:
                                                                         ss') from dual; commit;




SQL> select count(1) from a;


  COUNT(1)
----------
        89


SQL> select count(1) from a;


  COUNT(1)
----------
        89
        
        
修改broken为false后，还需要手工运行一次该job，才能恢复正常：
SQL> conn tmp_user/tmp_user
Connected.
SQL> exec dbms_job.run(103);


PL/SQL procedure successfully completed.


SQL> 
SQL> 
SQL> select count(1) from tmp_user.a;


  COUNT(1)
----------
        90


一段时间后再次查看：
SQL> select count(1) from tmp_user.a;


  COUNT(1)
----------
        91




*****************************************************


通过以上实验得出结论：
使用OGG+expdp迁移用户时，在目标端导入job后需要将job停掉（或者直接使用语句，导入时指定job为broken状态）；
在应用切换后，再将job重新启动。


另外，迁移过程中还需要注意sequence的一致性，避免因为目标端与源端的sequence不一致造成报错。

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

转载于:http://blog.itpub.net/26451536/viewspace-1806758/