运用ogg实现oracle 10g到19c schema级别的同步

denglj · 发表于 2022-12-16 11:47:48

本帖最后由 denglj 于 2022-12-16 11:49 编辑

文档课题：运用ogg实现oracle10g到19cschema级别的同步.

源端：192.168.133.108 数据库oracle 10.2.0.4 64位，实例名：orcl

目标端：192.168.133.109数据库oracle19.16.0.0 64位，实例名：simdb

ogg安装包：

源端：fbo_ggs_Linux_x64_ora10g_64bit.tar

目标端：191004_fbo_ggs_Linux_x64_shiphome.zip

说明：源端与目标端的ogg安装包不同.

核心思想：利用导入导出初始化数据后通过OGG同步增量数据.

进程介绍：

manager：控制ogg启动、状态、停止等.

extract：捕获源端数据，运用其内在checkpoint机制，周期性检查并记录其读写位置，通常是写入到本地trail文件.

pump：把trail文件以数据块形式通过tcp/ip协议发送到目标端.

replicat：读取目标端trail文件内容，并将其解析为dml或ddl语句，然后应用到目标数据库中.

原理图如下：

登录/注册后可看大图

78841639be6724ff95.png (280.93 KB, 下载次数: 320)

下载附件

2022-12-16 11:30 上传

注意：以下是在ogg同步过单表的基础上进行测试以达到同步schema的文档记录.ogg同步单表的请查看此前文档.

1、系统检查

--源端

[oracle@leo-10g-ogg~]$ cat /etc/*release

EnterpriseLinux Enterprise Linux Server release 5.11 (Carthage)

OracleLinux Server release 5.11

RedHat Enterprise Linux Server release 4 (Tikanga)

--目标端

[root@leo-19c-ogg:~]#cat /etc/*release

CentOSLinux release 7.9.2009 (Core)

NAME="CentOSLinux"

VERSION="7(Core)"

ID="centos"

ID_LIKE="rhelfedora"

VERSION_ID="7"

PRETTY_NAME="CentOSLinux 7 (Core)"

ANSI_COLOR="0;31"

CPE_NAME="cpe:/o:centos:centos:7"

HOME_URL="https://www.centos.org/"

BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"

CENTOS_MANTISBT_PROJECT_VERSION="7"

REDHAT_SUPPORT_PRODUCT="centos"

REDHAT_SUPPORT_PRODUCT_VERSION="7"

CentOSLinux release 7.9.2009 (Core)

2、字符集检查

--源端

SQL>select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.AL32UTF8

--目标端

sys@SIMDB > select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.AL32UTF8

3、前期准备

3.1、源端
3.1.1、停止抓取、投递进程

--源端停止ogg抓取进程以及投递进程.

[oracle@leo-10g-ogg~]$ cd $OGG_HOME

[oracle@leo-10g-oggogg]$ ./ggsci

GGSCI(leo-10g-ogg) 1> dblogin userid ogg@ORCL,password ogg

Successfullylogged into database.

GGSCI(leo-10g-ogg) 2> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DP1 00:00:00 00:00:08

EXTRACT RUNNING EXTA 00:00:00 00:00:02

GGSCI(leo-10g-ogg) 3> stop exta

SendingSTOP request to EXTRACT EXTA ...

Requestprocessed.

GGSCI(leo-10g-ogg) 4> stop dp1

SendingSTOP request to EXTRACT DP1 ...

Requestprocessed.

GGSCI(leo-10g-ogg) 5> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED DP1 00:00:00 00:00:07

EXTRACT STOPPED EXTA 00:00:00 00:00:12

3.1.2、删抓取、投递进程

--删除源端抓取、投递进程以便后续重建.

GGSCI(leo-10g-ogg) 6> delete exta

2022-12-15 09:29:55 INFO OGG-01750 Successfully unregistered EXTRACT EXTA from database.

DeletedEXTRACT EXTA.

GGSCI(leo-10g-ogg) 7> delete dp1

DeletedEXTRACT DP1.

GGSCI(leo-10g-ogg) 8> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

3.1.3、删队列文件

--删除源端队列文件.

[oracle@leo-10g-ogg~]$ cd $OGG_HOME

[oracle@leo-10g-oggogg]$ cd dirdat

[oracle@leo-10g-oggdirdat]$ rm -rf *

3.2、目标端
3.2.1、停止应用进程

--目标端停止ogg应用进程.

[oracle@leo-19c-ogg~]$ cd $OGG_HOME

[oracle@leo-19c-oggogg]$ ./ggsci

OracleGoldenGate Command Interpreter for Oracle

Version19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO

Linux,x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29

Operatingsystem character set identified as UTF-8.

GGSCI(leo-19c-ogg) 1> dblogin userid ogg@SIMDB,password ogg

Successfullylogged into database.

GGSCI(leo-19c-ogg as ogg@simdb) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP1 00:00:00 00:00:03

GGSCI(leo-19c-ogg as ogg@simdb) 3> stop rep1

SendingSTOP request to REPLICAT REP1 ...

Requestprocessed.

GGSCI(leo-19c-ogg as ogg@simdb) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT STOPPED REP1 00:00:00 00:00:02

3.2.2、删应用进程

--目标端删除应用进程以便后续重建.

GGSCI(leo-19c-ogg as ogg@simdb) 5> delete rep1

DeletedREPLICAT REP1.

GGSCI(leo-19c-ogg as ogg@simdb) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

3.2.3、删队列文件

--目标端删除队列文件

[oracle@leo-19c-ogg~]$ cd $OGG_HOME

[oracle@leo-19c-oggogg]$ cd dirdat

[oracle@leo-19c-oggdirdat]$ rm -rf *

3.2.4、删旧数据

说明：此次依然以scott用户作为测试，所以目标端删除此前导入的scott用户数据.

[oracle@leo-19c-oggdirdat]$ sqlplus / as sysdba

SQL*Plus:Release 19.0.0.0.0 - Production on Mon Dec 15 09:32:16 2022

Version19.16.0.0.0

Connectedto:

OracleDatabase 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version19.16.0.0.0

sys@SIMDB > drop user scott cascade;

Userdropped.

4、OGG配置
4.1、源端
4.1.1、数据准备

本次使用schema为scott作为测试.注意oracle19.16没有该用户.

SQL>alter user scott account unlock;

Useraltered.

SQL>alter user scott identified by tiger;

Useraltered.

SQL>conn scott/tiger

Connected.

SQL>select table_name from user_tables;

TABLE_NAME

------------------------------

DEPT

EMP

BONUS

SALGRADE

4 rowsselected.

4.1.2、expdp数据

--源端导出数据，查看当前scn.

SQL>SELECT TO_CHAR(MIN(start_scn) ) AS "Please select the minimum SCN"FROM v$transaction

2 UNION ALL

3 SELECT TO_CHAR(current_scn) FROM v$database;

Pleaseselect the minimum SCN

----------------------------------------

2657697

SQL>select to_char(scn_to_timestamp(2657697),'yyyy-mm-dd hh24:mi:ss') scndate fromdual;

SCNDATE

-------------------

2022-12-15 09:50:39

注意：

a、此处scn以及时间需记录，后续操作会使用到.

b、查看scn也可以用selectdbms_flashback.get_system_change_number from dual;

--建directoryname.

SQL>create directory expdp_dir as ‘/home/oracle’;

[oracle@leo-10g-ogg~]$ expdp \"/ as sysdba \" directory=expdp_dir dumpfile=scott.dmpflashback_scn=2657697 logfile=expdp.log schemas=scott

Export:Release 10.2.0.4.0 - 64bit Production on Monday, 15 December, 2022 10:10:52

Connectedto: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bitProduction

Withthe Partitioning, OLAP, Data Mining and Real Application Testing options

FLASHBACKautomatically enabled to preserve database integrity.

Starting"SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA"directory=expdp_dir dumpfile=scott.dmp flashback_scn=2657697 logfile=expdp.logschemas=scott

Estimatein progress using BLOCKS method...

Processingobject type SCHEMA_EXPORT/TABLE/TABLE_DATA

Totalestimation using BLOCKS method: 256 KB

Processingobject type SCHEMA_EXPORT/USER

Processingobject type SCHEMA_EXPORT/SYSTEM_GRANT

Processingobject type SCHEMA_EXPORT/ROLE_GRANT

Processingobject type SCHEMA_EXPORT/DEFAULT_ROLE

Processingobject type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processingobject type SCHEMA_EXPORT/TABLE/TABLE

Processingobject type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processingobject type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processingobject type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processingobject type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processingobject type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

.. exported "SCOTT"."DEPT" 5.656 KB 4 rows

.. exported "SCOTT"."EMP" 7.820 KB 14 rows

.. exported "SCOTT"."SALGRADE" 5.585 KB 5 rows

.. exported "SCOTT"."TESTDDL" 12.66 KB 1000 rows

.. exported "SCOTT"."BONUS" 0 KB 0 rows

Mastertable "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

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

Dumpfile set for SYS.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/scott.dmp

Job"SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at10:11:02

注意：此处需添加flashback_scn进行导出,以防止ogg搭建成功后目标端应用进程出现OGG-01004、OGG-01003、OGG-01154异常.

4.1.3、添加附加日志

[oracle@leo-10g-oggoracle]$ cd $OGG_HOME

[oracle@leo-10g-oggogg]$ pwd

/u01/app/ogg

[oracle@leo-10g-oggogg]$ ./ggsci

OracleGoldenGate Command Interpreter for Oracle

Version11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

Linux,x64, 64bit (optimized), Oracle 10g on Apr 21 2011 22:26:49

GGSCI(leo-10g-ogg) 1> dblogin userid ogg@ORCL,password ogg --注意要是多实例需配置tns登录创建的ogg

Successfullylogged into database.

GGSCI(leo-10g-ogg) 3> add trandata scott.*

2022-12-15 10:30:28 WARNING OGG-00869 No unique key is defined for table BONUS. Allviable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Loggingof supplemental redo data enabled for table SCOTT.BONUS.

Loggingof supplemental redo log data is already enabled for table SCOTT.DEPT.

Loggingof supplemental redo log data is already enabled for table SCOTT.EMP.

2022-12-15 10:30:28 WARNING OGG-00869 No unique key is defined for table SALGRADE.All viable columns will be used to represent the key, but may not guaranteeuniqueness. KEYCOLS may be used todefine the key.

Loggingof supplemental redo data enabled for table SCOTT.SALGRADE.

2022-12-15 10:30:28 WARNING OGG-00869 No unique key is defined for table TESTDDL.All viable columns will be used to represent the key, but may not guaranteeuniqueness. KEYCOLS may be used todefine the key.

Loggingof supplemental redo data enabled for table SCOTT.TESTDDL.

注意：

a、数据库附加日志打开后还需要对每张表执行addtrandata吗？答案是肯定的，如果不执行add trandata，insert同步没有问题（ORACLE数据库），但在同步update或delete时，就会因为丢失主键报同步错误.不开启表级的最小附加日志，update的redo信息不记录没有进行更新的字段信息，如主键不更新的话主键不记录在redo中，所以会导致同步失败.

b、addschematrandata scott在oracle 10.2.0.4执行会报错“OGG-01778，FeatureADD/INFO/DELETE SCHEMATRANDATA unsupported on the current database version.Upgrade database version to Oracle 11g or higher”.以上采用的addtrandata scott.*的语句.

区别如下：

add schematrandatascott --scott用户

addtrandata scott.* --scott用户下的所有表

--查附加日志是否添加成功.

GGSCI(leo-10g-ogg) 6> info trandata scott.dept

Loggingof supplemental redo log data is enabled for table SCOTT.DEPT

GGSCI(leo-10g-ogg) 7> info trandata scott.emp

Loggingof supplemental redo log data is enabled for table SCOTT.EMP

GGSCI(leo-10g-ogg) 8> info trandata scott.bonus

Loggingof supplemental redo log data is enabled for table SCOTT.BONUS

GGSCI(leo-10g-ogg) 9> info trandata scott.salgrade

Loggingof supplemental redo log data is enabled for table SCOTT.SALGRADE

4.1.4、抓取进程

--重新配置extract抓取进程exta，负责从源端数据表或日志中捕获数据.

[oracle@leo-10g-oggogg]$ cd $OGG_HOME

[oracle@leo-10g-oggogg]$ ./ggsci

OracleGoldenGate Command Interpreter for Oracle

Version11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

Linux,x64, 64bit (optimized), Oracle 10g on Apr 21 2011 22:26:49

GGSCI(leo-10g-ogg) 1> dblogin userid ogg@ORCL,password ogg

Successfullylogged into database.

GGSCI(leo-10g-ogg) 2> add extract exta,tranlog,begin 2022-12-15 09:50:39

2022-12-1217:25:04 INFO OGG-01749 Successfully registered EXTRACT EXTA to start managing log retention atSCN 2662793.

EXTRACTadded.

GGSCI(leo-10g-ogg) 3> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EXTA 00:18:24 00:00:26

--添加本地队列ra，exta进程捕捉到的有效数据将写入本地队列.

GGSCI(leo-10g-ogg) 4> add exttrail ./dirdat/ra,extract exta

EXTTRAILadded.

--为exta进程配置参数

GGSCI(leo-10g-ogg) 5> edit param exta

添加如下：

EXTRACTexta

setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

setenv(ORACLE_SID=orcl)

setenv(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)

useridogg@ORCL,password ogg

GETTRUNCATES

REPORTCOUNTEVERY 30 MINUTES,RATE

numfiles5000

DISCARDFILE./dirrpt/exta_ss.dsc, APPEND, MEGABYTES 1024

DISCARDROLLOVERAT 3:00

WARNLONGTRANS2h,CHECKINTERVAL 3m

EXTTRAIL./dirdat/ra,MEGABYTES 1024

DYNAMICRESOLUTION

NOCOMPRESSUPDATES

NOCOMPRESSDELETES

STATOPTIONSREPORTFETCH

DDLINCLUDE ALL

DDLOPTIONSADDTRANDATA

DDLOPTIONSREPORT

TRANLOGOPTIONSEXCLUDEUSER ogg

tableSCOTT.*;

参数说明:

GETUPDATEBEFORES：是否在队列中写入后镜像，也可进行维护事务历史表.默认是复制；

GETTRUNCATES：是否在队列中进行复制truncate操作，默认是不复制；

BRBRINTERVAL：对于存在长事务恢复情况下，恢复到检查点时间界限；

CACHEMGRCACHESIZE：主要用于控制存放未提交事务的虚拟内存和文件缓存空间；

WARNLONGTRANS：长事务警告频率，用于收集监控长事务情况；

CHECKINTERVAL：同样也是作为长事务WARNLONGTRANS监控频率；

--启动exta抓取进程

GGSCI(leo-10g-ogg) 6> start exta

SendingSTART request to MANAGER ...

EXTRACTEXTA starting

GGSCI(leo-10g-ogg) 7> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXTA 00:20:31 00:00:02

4.1.5、投递进程

[oracle@leo-10g-oggogg]$ cd $OGG_HOME

[oracle@leo-10g-oggogg]$ ./ggsci

OracleGoldenGate Command Interpreter for Oracle

Version11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

Linux,x64, 64bit (optimized), Oracle 10g on Apr 21 2011 22:26:49

GGSCI(leo-10g-ogg) 1> dblogin userid ogg@ORCL,password ogg

Successfullylogged into database.

--源端数据库配置extract投递进程dp1.

GGSCI(leo-10g-ogg) 2> add extract dp1,exttrailsource ./dirdat/ra

EXTRACTadded.

--添加远程trail文件.

GGSCI(leo-10g-ogg) 3> add rmttrail ./dirdat/ra,extract dp1,megabytes 1024

RMTTRAILadded.

GGSCI(leo-10g-ogg) 4> edit param dp1

添加如下：

extractdp1

useridogg@ORCL,password ogg

setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

passthru

rmthost192.168.133.109,mgrport 7809

rmttrail./dirdat/ra

tableSCOTT.*;

参数说明:

PASSTHRU：不登录到数据库操作（数据投递不必登录数据库）

DYNAMICRESOLUTION：动态解析

Rmthost：远端主机（IP或者主机名解析）

Rmttrail：目标端trail文件存储位置以及名称

--启用投递进程

GGSCI(leo-10g-ogg) 5> start dp1

SendingSTART request to MANAGER ...

EXTRACTDP1 starting

GGSCI(leo-10g-ogg) 6> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DP1 00:00:00 00:01:22

EXTRACT RUNNING EXTA 00:00:00 00:00:02

注意：启动投递进程 dp1前，目标端mgr进程需先启动.

4.2、目标端
4.2.1、添加checkpointtable

[oracle@leo-19c-ogg~]$ cd $OGG_HOME

[oracle@leo-19c-oggogg]$ ./ggsci

GGSCI (leo-19c-ogg) 1> dblogin useridogg@SIMDB,password ogg

Successfully logged into database.

GGSCI (leo-19c-ogg as ogg@simdb) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI(leo-19c-ogg as ogg@simdb) 3> add checkpointtable ogg.rep1_ckpt

Successfullycreated checkpoint table ogg.rep1_ckpt.

说明：ogg.rep1_ckp此前在同步表时已添加过，此处不需再添加.

4.2.2、应用进程

GGSCI(leo-19c-ogg as ogg@simdb) 4> add replicat rep1, exttrail ./dirdat/ra,checkpointtable ogg.rep1_ckpt

REPLICATadded.

GGSCI(leo-19c-ogg as ogg@simdb) 5> edit param rep1

添加如下：

REPLICATrep1

setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

--setenv(ORACLE_SID=)

useridogg@SIMDB,password ogg

REPORTAT 08:59

REPORTCOUNTEVERY 30 MINUTES, RATE

CACHEMGRCACHESIZE 2048MB,CACHEDIRECTORY ./dirtmp

REPERRORDEFAULT, ABEND

DISCARDFILE./dirrpt/rep1.dsc, APPEND, MEGABYTES 1024

DISCARDROLLOVERAT 3:00

GETTRUNCATES

ALLOWNOOPUPDATES

APPLYNOOPUPDATES

ASSUMETARGETDEFS

DBOPTIONSDEFERREFCONST

DBOPTIONSSUPPRESSTRIGGERS

DDLINCLUDE MAPPED

DDLOPTIONSREPORT

DDLERRORDEFAULT IGNORE RETRYOP

MAPSCOTT.*, TARGET SCOTT.*;

特别注意：应用进程rep1在目标端完成初始化之后才启动.

参数说明:

dbOptionsIntegratedParams：设置并行度

EOFDELAYCSECS：控制replicat 进程检查新数据的频度

Reportrollover：指定何时生成report 文件

Reperror：控制记录MAP 发生错误时的信息，这里指定default和abend

Default：设置对所有错误的响应记录

Abend：回滚事务并终止处理异常.ABEND是默认值

CACHEMGRCACHESIZE ：可以来控制OGG进程的内存使用.

DISCARDROLLOVERAT 3:00:为防止discard file被写满，每天3：00做一次文件过期设定.

5、目标端初始化数据
5.1、目标端导入

--将源端dmp文件scp到目标端.

[oracle@leo-10g-ogg ~]$ scp scott.dmporacle@192.168.133.109:/home/oracle/

--导入数据

> createdirectory impdp_dir as ‘/home/oracle’

[oracle@leo-19c-ogg~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@leo-19c-ogg~]$ nohup impdp \"/ as sysdba \" directory=impdp_dirdumpfile=scott.dmp logfile=impdp.log schemas=scott table_exists_action=replace&

[1] 28885

[oracle@leo-19c-ogg~]$ nohup: ignoring input and appending output to ‘nohup.out’

[oracle@leo-19c-ogg~]$ tail -5000f nohup.out

Import:Release 19.0.0.0.0 - Production on Mon Dec 15 11:10:08 2022

Version19.16.0.0.0

Connectedto: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Mastertable "SYS"."SYS_IMPORT_SCHEMA_01" successfullyloaded/unloaded

Starting"SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA"directory=impdp_dir dumpfile=scott.dmp logfile=impdp.log schemas=scotttable_exists_action=replace

Processingobject type SCHEMA_EXPORT/USER

Processingobject type SCHEMA_EXPORT/SYSTEM_GRANT

Processingobject type SCHEMA_EXPORT/ROLE_GRANT

Processingobject type SCHEMA_EXPORT/DEFAULT_ROLE

Processingobject type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processingobject type SCHEMA_EXPORT/TABLE/TABLE

Processingobject type SCHEMA_EXPORT/TABLE/TABLE_DATA

. .imported "SCOTT"."DEPT" 5.656 KB 4 rows

. .imported "SCOTT"."EMP" 7.820 KB 14 rows

. .imported "SCOTT"."SALGRADE" 5.585 KB 5 rows

. .imported "SCOTT"."TESTDDL" 12.66 KB 1000 rows

. .imported "SCOTT"."BONUS" 0 KB 0 rows

Processingobject type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processingobject type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processingobject type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processingobject type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processingobject type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_IMPORT_SCHEMA_01"successfully completed at Mon Dec 12 17:47:13 2022 elapsed 0 00:00:04

5.2、验证数据.

sys@SIMDB > conn scott/tiger

Connected.

scott@SIMDB > select * from dept;

DEPTNO DNAME LOC

------------------------ -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

scott@SIMDB > select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- ------------------- ---------- --------------------

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981-06-0900:00:00 2450 10

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

14 rowsselected.

6、初始化后操作

初始化数据完成后的相关配置.

--编译无效对象(非必须)

sys@SIMDB > @?/rdbms/admin/utlrp.sql

--收集统计信息

sys@SIMDB > exec dbms_stats.gather_database_stats(degree =>4);

PL/SQLprocedure successfully completed.

--启动replicat进程.

[oracle@leo-19c-ogg~]$ cd $OGG_HOME

[oracle@leo-19c-oggogg]$ ./ggsci

OracleGoldenGate Command Interpreter for Oracle

Version19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO

Linux,x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29

Operatingsystem character set identified as UTF-8.

GGSCI(leo-19c-ogg) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT STOPPED REP1 00:00:00 01:27:20

GGSCI(leo-19c-ogg) 2> dblogin userid ogg@SIMDB,password ogg

Successfullylogged into database.

GGSCI(leo-19c-ogg as ogg@simdb) 3> start REP1, aftercsn 2657697 --2657697为目标端初始化之前源端所查得的scn.

SendingSTART request to MANAGER ...

REPLICATREP1 starting

GGSCI(leo-19c-ogg as ogg@simdb) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP1 00:00:00 00:00:01

7、数据验证
7.1、DML测试

--源端模拟插入一条数据.

SQL>conn scott/tiger

Connected.

SQL>select * from dept;

DEPTNO DNAME LOC

------------------------ -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL>insert into dept values (50,'IT','ChongQing');

1row created.

SQL>commit;

Commitcomplete.

--目标端查询.

sys@SIMDB > conn scott/tiger

Connected.

scott@SIMDB > select * from dept;

DEPTNO DNAME LOC

------------------------ -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

50 IT ChongQing

7.2、DDL测试

--源端修改字段长度.

SQL>desc dept;

Name Null? Type

------------------------------------------------- ----------------------------

DEPTNO NOT NULL NUMBER(5)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL>alter table dept modify deptno number(8);

Tablealtered.

SQL>desc dept

Name Null? Type

----------------------- ------------------------

DEPTNO NOT NULL NUMBER(8)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

--目标端查询.

scott@SIMDB > desc dept

Name Null? Type

----------------------- ------------------------

DEPTNO NOT NULL NUMBER(8)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

7.3、增加约束检查

--源端查询

SQL>set line 200

SQL>select owner,constraint_name,constraint_type,table_name,index_name fromuser_constraints where table_name='DEPT'

OWNER CONSTRAINT_NAME C TABLE_NAME INDEX_NAME

------------------------------------------------------------ - ------------------------------------------------------------

SCOTT PK_DEPT P DEPT PK_DEPT

--目标端查询

scott@SIMDB > selectowner,constraint_name,constraint_type,table_name,index_name fromuser_constraints where table_name='DEPT';

OWNER CONSTRAINT_NAME C TABLE_NAME INDEX_NAME

------------------------------ - -------------------- --------------------

SCOTT PK_DEPT P DEPT PK_DEPT

--源端增加唯一约束

SQL>alter table dept add constraint uk_dept_dname unique (dname);

Tablealtered.

SQL>select owner,constraint_name,constraint_type,table_name,index_name from user_constraintswhere table_name='DEPT';

OWNER CONSTRAINT_NAME C TABLE_NAME INDEX_NAME

------------------------------------------------------------ - ------------------------------------------------------------

SCOTT PK_DEPT P DEPT PK_DEPT

SCOTT UK_DEPT_DNAME U DEPT UK_DEPT_DNAME

--目标端查询

scott@SIMDB > selectowner,constraint_name,constraint_type,table_name,index_name fromuser_constraints where table_name='DEPT';

OWNER CONSTRAINT_NAME C TABLE_NAME INDEX_NAME

------------------------------ - -------------------- --------------------

SCOTT PK_DEPT PDEPT PK_DEPT

SCOTT UK_DEPT_DNAME U DEPT UK_DEPT_DNAME

7.4、建表测试

--源端建testddl表.

SQL>create table testddl (id number(10));

Tablecreated.

SQL>begin

2 fori in 1..1000 loop

3 insert into testddl (id) values (i);

4 endloop;

5 end;

6 /

PL/SQLprocedure successfully completed.

SQL>commit;

Commitcomplete.

SQL>select count(*) from testddl;

COUNT(*)

----------

1000

--目标端查询.

scott@SIMDB > select table_name from user_tables;

TABLE_NAME

--------------------

TESTDDL

DEPT

EMP

BONUS

SALGRADE

scott@SIMDB > select count(*) from testddl

COUNT(*)

----------

1000

说明：以上为ogg实现单个schema级别的复制，若要实现数据库级别的复制，建议将需要复制的schema分别添加进抓取、投递、应用进程.

帐号		自动登录	找回密码
密码			注册

[Oracle] 运用ogg实现oracle 10g到19c schema级别的同步

浏览过的版块