使用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不一致造成報錯。
***************************************************
源端：
建立新使用者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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- exp遷移庫時，發現各使用者的job建到了system使用者裡
- 系統遷移時關於JOB,TRIGGER,CONSTRAINT,sequence的處理AI
- 關於資料庫使用impdp+network_link遷移時，job的遷移資料庫
- xtts遷移時ORA-353處理TTS
- 使用Elastic Job的時候報“Job conflict with register center”，如何處理？AST
- Jenkins Job遷移Jenkins
- Oracle Job 遷移小記Oracle
- 單體JOB向分散式JOB遷移案例分散式
- 處理表的行遷移的問題
- scp使用者遷移rsync
- expdp,impdp的多使用者遷移
- SQL SERVER 資料庫遷移孤立使用者的解決方法SQLServer資料庫
- 使用者清理及使用者資料遷移
- 遷移oracle使用者密碼Oracle密碼
- 海量資料處理_使用外部表進行資料遷移
- 自然語言處理中的遷移學習(下)自然語言處理遷移學習
- 自然語言處理中的遷移學習(上)自然語言處理遷移學習
- Logical Standby中Job的處理
- oracle對JOB失敗的處理Oracle
- python2.6升級到3.3.0 以及依賴庫在遷移時的處理Python
- 遷移後處理外來鍵約束的問題
- 忘記密碼的資料庫使用者處理方法密碼資料庫
- 不同使用者，不同表空間遷移
- 在使用 zabbix 4 時, orabbix 會報錯的處理方法
- job掛死處理步驟
- 什麼是遷移學習？什麼時候使用遷移學習？遷移學習
- 【遷移】SqlServer 遷移到 MySQL 方法ServerMySql
- Oracle資料使用者密碼過期處理方法Oracle密碼
- 影像資料不足時的處理方法
- 【遷移】使用rman遷移資料庫資料庫
- win10使用者資料夾遷移操作方法 win10怎麼移動使用者資料夾位置Win10
- oracle job使用方法--實現定時任務Oracle
- 海量資料處理_資料泵分批資料遷移
- exp/imp和expdp/imp在跨使用者邏輯遷移資料時的差異
- 關於root（其他）使用者拒絕登陸mysql的處理方法MySql
- 使用者密碼過期的處理密碼
- Oracle JOB 使用方法Oracle
- LINUX忘記ROOT使用者密碼處理方法（linux單使用者模式）Linux密碼模式