前言

前幾天，技術(shù)交流群里看到大家討論 Oracle 數(shù)據(jù)泵導(dǎo)入時使用 table_exists_action 參數(shù)，存在一些疑惑。于是，我打算通過 LogMiner 來分析一下在線重做日志，看看到底是怎么玩的。

• 關(guān)于 LogMiner 的官方文檔：Using LogMiner to Analyze Redo Log Files
• 關(guān)于 TABLE_EXISTS_ACTION 的官方文檔：TABLE_EXISTS_ACTION
  

建議先閱讀簡單了解一下，下面👇🏻就開始~ ヾ(?°?°?)ﾉﾞ

環(huán)境準(zhǔn)備

已有 Oracle 11GR2 數(shù)據(jù)庫環(huán)境，已開啟歸檔模式。

1、安裝 LogMiner

Oracle 自帶的 sql 腳本與 LogMiner 相關(guān)的有以下三個：

在默認(rèn)情況下，Oracle已經(jīng)安裝了LogMiner工具，如果沒有安裝，可以依次執(zhí)行以下 sql 腳本，創(chuàng)建 LogMiner 相關(guān)的對象：

sqlplus / as sysdba @?/rdbms/admin/dbmslm.sql
sqlplus / as sysdba @?/rdbms/admin/dbmslmd.sql

腳本需要用 SYS 用戶執(zhí)行，可重復(fù)執(zhí)行。

2、創(chuàng)建數(shù)據(jù)字典文件

DBMS_LOGMNR_D.BUILD 過程需要訪問可以放置字典文件的目錄。 因為 PL/SQL 過程通常不訪問用戶目錄，所以必須指定一個目錄供 DBMS_LOGMNR_D.BUILD 過程使用，否則該過程將失敗。

mkdir /oradata/orcl/logmnr
sqlplus / as sysdba
CREATE DIRECTORY utlfile AS '/oradata/orcl/logmnr';
alter system set utl_file_dir='/oradata/orcl/logmnr' scope=spfile;
shutdown immediate;
startup;

要指定目錄，需要在初始化參數(shù)文件中設(shè)置初始化參數(shù) UTL_FILE_DIR，需要重啟數(shù)據(jù)庫生效參數(shù)。

執(zhí)行 PL/SQL 過程 DBMS_LOGMNR_D.BUILD。 指定字典的文件名和文件的目錄路徑名。 此過程創(chuàng)建字典文件。 例如，輸入以下內(nèi)容在 /oradata/orcl/logmnr 中創(chuàng)建文件 dictionary.ora：

EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora','/oradata/orcl/logmnr',DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

由于本次僅作實驗所用，不單獨創(chuàng)建用戶和表空間。

3、添加在線重做日志

通過 LogMiner.ADD_LOGFILE 添加所有 REDO LOG ：

-- 查詢所有在線重做日志
select member from v$logfile;
-- 添加所有在線重做日志
BEGIN 
    DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/orcl/redo03.log',OPTIONS => DBMS_LOGMNR.NEW);
    DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/orcl/redo01.log',OPTIONS => DBMS_LOGMNR.ADDFILE);
    DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/orcl/redo02.log',OPTIONS => DBMS_LOGMNR.ADDFILE);
end;

添加第一個文件時，OPTIONS 需要指定 DBMS_LOGMNR.NEW，后面添加的文件指定 DBMS_LOGMNR.ADDFILE。

4、啟動 LogMiner

begin
    DBMS_LOGMNR.START_LOGMNR(DictFileName => '/oradata/orcl/logmnr/dictionary.ora');
end;

這里需要注意的是，執(zhí)行啟動 LogMiner 的 SESSION 才可以進行查詢，否則不能查詢。

5、準(zhǔn)備數(shù)據(jù)泵導(dǎo)入數(shù)據(jù)

創(chuàng)建用戶和測試表：

create user tea identified by tea;
grant dba to tea;
conn tea/tea
create table tea (id number,text varchar2(20));
insert into tea values (1,'test1');
insert into tea values (2,'test2');
commit;

數(shù)據(jù)泵導(dǎo)出表：

expdp system/oracle directory=DATA_PUMP_DIR dumpfile=tea.dmp logfile=tea.log tables=tea.tea

6、查詢 LogMiner 記錄

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';
SELECT timestamp, sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='TEA' AND seg_owner='TEA';

通過查詢可以看到上面建表的 DDL 語句已經(jīng)被查詢到。

開始實驗

數(shù)據(jù)泵導(dǎo)入?yún)?shù) TABLE_EXISTS_ACTION，通常用于數(shù)據(jù)庫中表已存在的情況下，導(dǎo)入數(shù)據(jù)時處理的參數(shù)。

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

可用選項有 4 種，接下來我們依次使用參數(shù)來進行測試。

• SKIP：跳過當(dāng)前表進行下一個。 如果 CONTENT 參數(shù)設(shè)置為 DATA_ONLY，這不是有效選項。
• APPEND：從源加載數(shù)據(jù)并保持現(xiàn)有數(shù)據(jù)不變。
• TRUNCATE：刪除現(xiàn)有表數(shù)據(jù)，然后從源加載數(shù)據(jù)。
• REPLACE：刪除現(xiàn)有表，然后從源創(chuàng)建并加載數(shù)據(jù)。 如果 CONTENT 參數(shù)設(shè)置為 DATA_ONLY，這不是有效選項。

默認(rèn)值：SKIP（注意，如果指定了 CONTENT=DATA_ONLY，則默認(rèn)值是 APPEND，而不是 SKIP）

1、SKIP

SKIP 參數(shù)是指導(dǎo)入時跳過已存在的表，添加參數(shù) TABLE_EXISTS_ACTION=SKIP 測試。

執(zhí)行導(dǎo)入：

impdp system/oracle directory=DATA_PUMP_DIR dumpfile=tea.dmp logfile=tea.log tables=tea.tea table_exists_action=skip

這個參數(shù)比較簡單明了，就是直接跳過了存在的表，不進行導(dǎo)入，表數(shù)據(jù)不變。

2、APPEND

APPEND 參數(shù)是指導(dǎo)入時對已存在表進行增量導(dǎo)入，添加參數(shù)TABLE_EXISTS_ACTION=APPEND 測試。

由于目前表數(shù)據(jù)一樣，無法看出效果，先修改表中數(shù)據(jù)：

delete from tea.tea where id=2;
insert into tea.tea values (3,'test3');
commit;

執(zhí)行導(dǎo)入：

impdp system/oracle directory=DATA_PUMP_DIR dumpfile=tea.dmp logfile=tea.log tables=tea.tea table_exists_action=append

由于建表時沒有主鍵唯一限制，因此允許存在重復(fù)數(shù)據(jù)，導(dǎo)入后數(shù)據(jù)如下：

select * from tea.tea;

當(dāng)使用 APPEND 參數(shù)，如果發(fā)現(xiàn)存在表，將導(dǎo)入數(shù)據(jù)進行增量導(dǎo)入，如果有唯一限制時，有重復(fù)數(shù)據(jù)，將會導(dǎo)入失敗。

也可以通過在導(dǎo)入命令行上指定 DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS 來覆蓋此行為。如果有必須加載的數(shù)據(jù)，但可能會導(dǎo)致違反約束，可以考慮禁用約束，加載數(shù)據(jù)，然后在重新啟用約束之前刪除有問題的行。

3、TRUNCATE

TRUNCATE 參數(shù)會刪除原表中所有的數(shù)據(jù)，并且導(dǎo)入新數(shù)據(jù)，添加參數(shù)TABLE_EXISTS_ACTION=TRUNCATE 測試。

執(zhí)行導(dǎo)入：

impdp system/oracle directory=DATA_PUMP_DIR dumpfile=tea.dmp logfile=tea.log tables=tea.tea table_exists_action=truncate

查詢導(dǎo)入后數(shù)據(jù)：

select * from tea.tea;

可以看到之前的數(shù)據(jù)已經(jīng)不存在，數(shù)據(jù)重新導(dǎo)入。

SELECT timestamp, sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='TEA' AND seg_owner='TEA';

通過比對導(dǎo)入時間和 LogMiner 表中記錄時間，可以看到 TEA 表執(zhí)行了 TRUNCATE 操作。

4、REPLACE

REPLACE 參數(shù)會刪除已存在的表然后重新創(chuàng)建，并且導(dǎo)入新數(shù)據(jù)，添加參數(shù)TABLE_EXISTS_ACTION=REPLACE 測試。

導(dǎo)入前插入幾條數(shù)據(jù)：

insert into tea values(3,'test3');
insert into tea values(4,'test4');
commit;

執(zhí)行導(dǎo)入：

impdp system/oracle directory=DATA_PUMP_DIR dumpfile=tea.dmp logfile=tea.log tables=tea.tea table_exists_action=replace

通過導(dǎo)入過程沒有看到任何關(guān)于表已存在的提示，導(dǎo)入正常，查詢數(shù)據(jù)：

數(shù)據(jù)只存在導(dǎo)入的數(shù)據(jù)，導(dǎo)入前新增的數(shù)據(jù)已經(jīng)消失。

SELECT timestamp, sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='TEA' AND seg_owner='TEA';

通過比對導(dǎo)入時間和 LogMiner 表中記錄時間，可以看到 TEA 表先執(zhí)行 DROP PURGE 操作，然后執(zhí)行 CREATE TABLE 重新創(chuàng)建表。

總結(jié)

使用 SKIP、APPEND 或 TRUNCATE 時，不會修改源中現(xiàn)有的表相關(guān)對象，例如索引、授權(quán)、觸發(fā)器和約束。
對于 REPLACE，如果依賴對象未被顯式或隱式排除（使用 EXCLUDE）并且它們存在于源轉(zhuǎn)儲文件或系統(tǒng)中，則會從源中刪除并重新創(chuàng)建它們。