【大数据实时数据同步】超级详细的生产环境OGG(GoldenGate)12，2024年最新330页PDF10万字的知识点总结

--加入下面内容 PORT 7809 DYNAMICPORTLIST 7810-7860 AUTORESTART ER *, RETRIES 3, WAITMINUTES 5 PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 30 lagreporthours 1 laginfominutes 30 lagcriticalminutes 60 --保存退出后，重启管理进程，让配置生效： GGSCI (source) 8> stop mgr Manager process is required by other GGS processes. Are you sure you want to stop it (y/n)?y

Sending STOP request to MANAGER ... Request processed. Manager stopped.

GGSCI (source) 9> start mgr Manager started. GGSCI (source) 10> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING


### 2、源端新增抽取进程配置

GGSCI (source) 12> edit params e_sc
--加入如下内容： extract e_sc userid ogg,password ogg setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8) setenv(ORACLE_SID="orcl") reportcount every 30 minutes,rate numfiles 5000 discardfile ./dirrpt/e_sc.dsc,append,megabytes 1000 warnlongtrans 2h,checkinterval 30m exttrail ./dirdat/sc dboptions allowunusedcolumn tranlogoptions archivedlogonly tranlogoptions altarchivelogdest primary /u01/arch ddl include mapped ddloptions addtrandata,report notcpsourcetimer nocompressupdates fetchoptions USEROWID NOCOMPRESSDELETES ----------scott.EMP table SCOTT.EMP,tokens( TKN-CSN = @GETENV('TRANSACTION', 'CSN'), TKN-COMMIT-TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'), TKN-OP-TYPE = @GETENV ('GGHEADER', 'OPTYPE') ); ----------SCOTT.DEPT table SCOTT.DEPT,tokens( TKN-CSN = @GETENV('TRANSACTION', 'CSN'), TKN-COMMIT-TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'), TKN-OP-TYPE = @GETENV ('GGHEADER', 'OPTYPE') );


### 3、源端配置投递进程配置

GGSCI (source) 13>edit params d_sc --加入如下内容： extract d_sc rmthost 192.168.1.10,mgrport 7809,compress userid ogg,password ogg PASSTHRU numfiles 5000 rmttrail ./dirdat/sc dynamicresolution table scott.*;


### 4、源端增加抽取进程

GGSCI (source) 16> add extract e_sc,tranlog,begin now EXTRACT added.

GGSCI (source) 17> add exttrail ./dirdat/sc,extract e_sc,megabytes 500 EXTTRAIL added.


### 5、源端增加投递进程

add extract d_sc,exttrailsource ./dirdat/sc add rmttrail ./dirdat/sc,extract d_sc,megabytes 500


### 6、源端添加表级别附加日志

GGSCI (source) 40> dblogin userid ogg,password ogg Successfully logged into database.

GGSCI (source as ogg@orcl) 41> add trandata SCOTT.EMP

Logging of supplemental redo data enabled for table SCOTT.EMP. TRANDATA for scheduling columns has been added on table 'SCOTT.EMP'. TRANDATA for instantiation CSN has been added on table 'SCOTT.EMP'. GGSCI (source as ogg@orcl) 42>

GGSCI (source as ogg@orcl) 42> add trandata SCOTT.DEPT

Logging of supplemental redo data enabled for table SCOTT.DEPT. TRANDATA for scheduling columns has been added on table 'SCOTT.DEPT'. TRANDATA for instantiation CSN has been added on table 'SCOTT.DEPT'. GGSCI (source as ogg@orcl) 43> info trandata SCOTT.EMP

Logging of supplemental redo log data is enabled for table SCOTT.EMP.

Columns supplementally logged for table SCOTT.EMP: EMPNO.

Prepared CSN for table SCOTT.EMP: 1108355


### 7、启动Extract和PUMP进程

GGSCI (source) 38> start er *

Sending START request to MANAGER ... EXTRACT D_SC starting

Sending START request to MANAGER ... EXTRACT E_SC starting

GGSCI (source) 39> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING D_SC 00:00:00 00:00:04
EXTRACT RUNNING E_SC 00:00:00 00:00:04


### 8、目的端编辑REPLICAT进程配置

GGSCI (ogg) 1> edit params mgr ---加入下面配置 replicat r_sc setenv(NLS_LANG=AMERICAN_AMERICA.UTF8) userid ogg,password ogg reportcount every 30 minutes,rate reperror default,abend numfiles 20000 checkpointsecs 40 assumetargetdefs discardfile ./dirrpt/r_sc.dsc,append,megabytes 1000 allownoopupdates ddl & include mapped & exclude objname scott.*_audit & exclude optype create & objtype 'table' & exclude optype drop & objtype 'table' & exclude objtype 'index' & objname scott.*_his & exclude instr 'constraint' & --exclude instr 'null' & exclude instr 'trigger' & exclude instr 'rename to' & exclude instr 'grant' & exclude instr 'revoke' & exclude instr 'analyze' ddloptions report allowduptargetmap ----------EMP getinserts getupdates getdeletes noupdatedeletes map SCOTT.EMP,target SCOTT.EMP,keycols(EMPNO),colmap( usedefaults, etltime=@DATENOW() ); ignoreinserts ignoreupdates getdeletes INSERTDELETES map SCOTT.EMP,target SCOTT.EMP_HIS,keycols(EMPNO),colmap( usedefaults, etltime=@DATENOW() ); NOINSERTDELETES updatedeletes getinserts getupdates getdeletes map SCOTT.EMP,target SCOTT.EMP_AUDIT,keycols(EMPNO),colmap( EMPNO=@if(@strfind(@token('tkn-op-type'),'PK UPDATE')>0,before.EMPNO,EMPNO), csn=@token('tkn-csn'), optime=@token('tkn-commit-ts'), optype=@if(@strfind(@token('tkn-op-type'),'PK UPDATE')>0,'DELETE',@token('tkn-op-type')), inserttime=@eval(@strfind(@token('tkn-op-type'),'INSERT')>0,@token('tkn-commit-ts')), curdate=@DATENOW() ); ignoreinserts getupdates ignoredeletes map SCOTT.EMP,target SCOTT.EMP_AUDIT,keycols(EMPNO),colmap( usedefaults, csn=@token('tkn-csn'), optime=@token('tkn-commit-ts'), optype=@case(@token('tkn-op-type'),'PK UPDATE','INSERT'), inserttime=@token('tkn-commit-ts'), curdate=@DATENOW() ),filter(@strfind(@token('tkn-op-type'),'PK UPDATE') >0),insertallrecords; ----------DEPT getinserts getupdates getdeletes noupdatedeletes map SCOTT.DEPT,target SCOTT.DEPT,keycols(DEPTNO),colmap( usedefaults, etltime=@DATENOW() ); ignoreinserts ignoreupdates getdeletes INSERTDELETES map SCOTT.DEPT,target SCOTT.DEPT_HIS,keycols(DEPTNO),colmap( usedefaults, etltime=@DATENOW() ); NOINSERTDELETES updatedeletes getinserts getupdates getdeletes map SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap( DEPTNO=@if(@strfind(@token('tkn-op-type'),'PK UPDATE')>0,before.DEPTNO,DEPTNO), csn=@token('tkn-csn'), optime=@token('tkn-commit-ts'), optype=@if(@strfind(@token('tkn-op-type'),'PK UPDATE')>0,'DELETE',@token('tkn-op-type')), inserttime=@eval(@strfind(@token('tkn-op-type'),'INSERT')>0,@token('tkn-commit-ts')), curdate=@DATENOW() ); ignoreinserts getupdates ignoredeletes map SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap( usedefaults, csn=@token('tkn-csn'), optime=@token('tkn-commit-ts'), optype=@case(@token('tkn-op-type'),'PK UPDATE','INSERT'), inserttime=@token('tkn-commit-ts'), curdate=@DATENOW() ),filter(@strfind(@token('tkn-op-type'),'PK UPDATE') >0),insertallrecords;


### 9、添加应用进程

GGSCI (ogg) 8> dblogin userid ogg,password ogg Successfully logged into database.

GGSCI (ogg as ogg@ogg) 9> ADD CHECKPOINTTABLE ogg.ckpt Successfully created checkpoint table ogg.ckpt.

GGSCI (ogg as ogg@ogg) 10> add replicat r_sc,exttrail ./dirdat/sc checkpointtable ogg.ckpt


### 10、源端导出emp.dept表并传到目的端应用



#### 10.1、源端通过数据泵导出表

[oracle@source ogg12] $mkdir /u01/exp--创建目录 [oracle@source ogg12]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 20 18:34:35 2018

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

SQL> create directory expdir as '/u01/exp';

Directory created.

SQL> grant read,write on directory expdir to scott;

Grant succeeded.

SQL> grant dba to scott;--这个权限是为了测试方便所以给的dba权限。生产慎用

Grant succeeded. SQL> set num 50 SQL> select current_scn from v$database;---查询当前scn

			       CURRENT_SCN

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

[oracle@source ~]$ expdp ' / as sysdba' directory=EXPDIR dumpfile=expdp_20180920.dmp logfile=expdp_20180920.log tables=SCOTT.EMP,SCOTT.DEPT exclude=ref_constraint flashback_scn=1108836

Export: Release 11.2.0.4.0 - Production on Thu Sep 20 18:45:47 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=EXPDIR dumpfile=expdp_20180920.dmp logfile=expdp_20180920.log tables=SCOTT.EMP,SCOTT.DEPT exclude=ref_constraint flash back_scn=1108836 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 KB Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.562 KB 14 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /u01/exp/expdp_20180920.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Thu Sep 20 18:46:21 2018 elapsed 0 00:00:24


#### 10.2、目的端配置导入目录

[oracle@ogg ~] $mkdir /u01/imp [oracle@ogg ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 20 18:34:24 2018

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

SQL> create directory expdir as '/u01/exp';

Directory created.

SQL> create directory impdir as '/u01/imp';

Directory created.

SQL> grant read,write on directory impdir to scott;

Grant succeeded.


#### 10.3、将源端导出的dump文件传到目的端

[oracle@source ~]$ scp /u01/exp/expdp_20180920.* 192.168.1.10:/u01/imp/ oracle@192.168.1.10's password: expdp_20180920.dmp 100% 168KB 168.0KB/s 00:00
expdp_20180920.log


#### 10.4、目标端导入数据到原表

impdp ' / as sysdba' directory=IMPDIR dumpfile=expdp_20180920.dmp logfile=expdp_20180920.log


#### 10.5、OGG库审计表数据初始化

create table SCOTT.EMP_AUDIT ( EMPNO NUMBER(4), CSN NUMBER, OPTIME TIMESTAMP(6), OPTYPE VARCHAR2(20), INSERTTIME TIMESTAMP(6), CURDATE TIMESTAMP(6) ); create table SCOTT.DEPT_AUDIT ( DEPTNO NUMBER(2), CSN NUMBER, OPTIME TIMESTAMP(6), OPTYPE VARCHAR2(20), INSERTTIME TIMESTAMP(6), CURDATE TIMESTAMP(6) ); insert into SCOTT.EMP_AUDIT select EMPNO,1108836,sysdate,'INITIAL',sysdate,'' from SCOTT.EMP; insert into SCOTT.DEPT_AUDIT select DEPTNO,1108836,sysdate,'INITIAL',sysdate,'' from SCOTT.DEPT; commit;


#### 10.6、OGG库添加ETLTIME字段

---目标表 alter table scott.emp add etltime timestamp; alter table scott.dept add etltime timestamp; ---HIS表 alter table scott.emp_HIS add etltime timestamp; alter table scott.dept_HIS add etltime timestamp;


#### 10.7、OGG库ETLTIME字段数据初始化

----目标表 update scott.emp set etltime=sysdate; update scott.dept set etltime=sysdate; commit;


#### 10.8、OGG库添加索引，防止进程lag过高

CREATE INDEX scott.emp_idx ON scott.emp_audit(empno); CREATE INDEX scott.dept_idx ON scott.dept_audit(deptno);


### 11、启动应用进程

start replicat r_sc aftercsn 1108836


### 12、验证



#### 12.1、源端操作

[oracle@source ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 20 19:11:57 2018

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

SQL> alter table dept add test number;

Table altered.

SQL> insert into dept values(50,'a','a','1');

1 row created.

SQL> alter table dept drop column test;

Table altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile; alter system switch logfile;

System altered.


#### 12.2、目的端查看


![在这里插入图片描述](https://p3-xtjj-sign.byteimg.com/tos-cn-i-73owjymdk6/262537eb5efe46c5a714bfb1f90c3290~tplv-73owjymdk6-jj-mark-v1:0:0:0:0:5o6Y6YeR5oqA5pyv56S-5Yy6IEAg5py65Zmo5a2m5Lmg5LmL5b-DQUk=:q75.awebp?rk3s=f64ab15b&x-expires=1771857126&x-signature=7CbiSU9W%2BWD5vKsRRMxzmSL0jiQ%3D)  
 看一下日志，DDL也正常应用了：

2018-09-20 19:00:49 INFO OGG-06510 Using the following key columns for target table SCOTT.DEPT_AUDIT: DEPTNO.

2018-09-20 19:00:49 INFO OGG-03010 Performing implicit conversion of column data from character set UTF-8 to zhs16gbk.

2018-09-20 19:00:49 INFO OGG-00482 DDL found, operation [alter table dept drop column test (size 34)].

2018-09-20 19:00:49 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table "SCOTT"."DEPT" drop column test (size 44)].

2018-09-20 19:00:49 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT].

2018-09-20 19:00:49 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].

2018-09-20 19:00:49 INFO OGG-00484 Executing DDL operation.

2018-09-20 19:00:50 INFO OGG-00483 DDL operation successful.

2018-09-20 19:00:50 INFO OGG-01408 Restoring current schema for DDL operation to [OGG].

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Go语言开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以戳这里获取