通过OGG自动备份delete数据

OGG安装（略）

初始化数据

# 导出数据
expdp \'/ as sysdba\' directory=DUMPDIR dumpfile=SCOTT20211223.dmp schemas=SCOTT logfile=SCOTT20211223.log  CONTENT=METADATA_ONLY
# 历史库导入数据
impdp \'/ as sysdba\' directory=dumpdir dumpfile=SCOTT_DEL20211223.dmp logfile=SCOTT_DEL20211223.log remap_schema=SCOTT:SCOTT_DEL

对历史表添加字段

# (OPTYPE:操作类型，COMMITTIMESTAMP：操作时间)
alter table SCOTT_DEL.TEST add (OPTYPE VARCHAR2(10) not null, COMMITTIMESTAMP DATE not null);


# 如果表比较多，找出没有OPTYPE，COMMITTIMESTAMP的表

select t.owner,t.table_name
  from dba_tables t
 where t.OWNER = 'SCOTT_DEL'
   and t.TABLE_NAME not in
       (select table_name
          from (select tc.TABLE_NAME, count(*)
                  from dba_tab_cols tc
                 where tc.owner = 'SCOTT_DEL'
                   and tc.COLUMN_NAME in ('OPTYPE','COMMITTIMESTAMP')
                 group by tc.TABLE_NAME
                having count(*) = 2))

配置抽取进程

# 抽取进程

edit params PRE_E41

EXTRACT PRE_E41
SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
-- 使用别名登录
USERIDALIAS orcl
TRANLOGOPTIONS FETCHPARTIALLOB
FETCHOPTIONS USESNAPSHOT
FETCHOPTIONS USELATESTVERSION
GETTRUNCATES
LOGALLSUPCOLS
UPDATERECORDFORMAT FULL
CACHEMGR CACHESIZE 5G;
EXTTRAIL /oggbase/dirdat/orcl_del/od
GETUPDATEBEFORES
-- table
table SCOTT.TEST;


# 投递进程
edit params PRE_P41

EXTRACT PRE_P41
USERIDALIAS orcl
SETENV (NLS_LANG = 'AMERICAN_AMERICA.AL32UTF8')
RMTHOST 192.168.1.2, MGRPORT 3324
GETTRUNCATES
RMTTRAIL /oggbase_remote/dirdat/orcl_del/od
GetUpdateBefores
TABLE SCOTT.*;

# 使用集成模式 注册数据库
dblogin USERIDALIAS orcl
register extract PRE_E41 database

# 添加抽取 投递进程
add extract PRE_E41, integrated tranlog, begin now
add exttrail /oggbase/dirdat/orcl_del/od, extract PRE_E41
add extract PRE_P41,exttrailsource /oggbase/dirdat/orcl_del/od
add rmttrail /oggbase_remote/dirdat/orcl_del/od, extract PRE_P41,megabytes 500

配置复制进程

## 拼接map语句
select 'map scott.' || lower(t.OBJECT_NAME) || ',target '|| t.OWNER||'.' ||
       lower(t.OBJECT_NAME) || ',COLMAP(USEDEFAULTS,OPTYPE = @GETENV(''GGHEADER'', ''OPTYPE''),COMMITTIMESTAMP = @DATENOW());'
          from dba_objects t
         where t.OWNER = 'SCOTT_DEL'
           and t.OBJECT_TYPE = 'TABLE';

## 复制进程
REPLICAT PRE_R41
USERIDALIAS OCHIS
--HANDLECOLLISIONS
DBOPTIONS NOSUPPRESSTRIGGERS
BATCHSQL BATCHESPERQUEUE 200, OPSPERBATCH 10000
DDL INCLUDE ALL &
    EXCLUDE INSTR 'read only' &
    EXCLUDE INSTR 'grant select' &
    EXCLUDE INSTR 'read write' &
    EXCLUDE INSTR 'RENAME TO' &
    EXCLUDE INSTR 'alter trigger' &
    EXCLUDE INSTR 'add supplemental log'
IGNOREINSERTS
IGNOREUPDATES 
-- 将delete转换为insert(忽略insert,update)
INSERTDELETES
DDLOPTIONS MAPSESSIONSCHEMA SCOTT TARGET SCOTT_DEL,MAPSCHEMAS
-- scott(由上面sql拼接而成)
map scott.test,target scott_del.test,COLMAP(USEDEFAULTS,OPTYPE = @GETENV('GGHEADER', 'OPTYPE'),COMMITTIMESTAMP = @DATENOW());

# 添加进程
add replicat PRE_R41, exttrail /oggbase_remote/dirdat/orcl_del/od, checkpointtable, ogg.ptcheckpoint