文档课题：运用ogg实现oracle 10g到19c schema级别的同步.

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

目 标 端：192.168.133.109 数据库oracle 19.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语句，然后应用到目标数据库中.

原理图如下：

注意：以下是在ogg同步过单表的基础上进行修改以达到同步schema的文档记录.至于ogg同步单表的记录请参照以下博客.

https://blog.51cto.com/u_12991611/5928120

1、系统检查

--源端

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

Enterprise Linux Enterprise Linux Server release 5.11 (Carthage)

Oracle Linux Server release 5.11

Red Hat Enterprise Linux Server release 4 (Tikanga)

--目标端

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

CentOS Linux release 7.9.2009 (Core)

NAME="CentOS Linux"

VERSION="7 (Core)"

ID="centos"

ID_LIKE="rhel fedora"

VERSION_ID="7"

PRETTY_NAME="CentOS Linux 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"

 

CentOS Linux release 7.9.2009 (Core)

CentOS Linux release 7.9.2009 (Core)

2、字符集检查

--源端

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

 

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.AL32UTF8

--目标端

sys@SIMDB 2022-12-12 16:18:49> 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-ogg ogg]$ ./ggsci

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

Successfully logged 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

 

Sending STOP request to EXTRACT EXTA ...

Request processed.

 

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

 

Sending STOP request to EXTRACT DP1 ...

Request processed.

 

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-12 16:29:55  INFO    OGG-01750  Successfully unregistered EXTRACT EXTA from database.

Deleted EXTRACT EXTA.

 

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

Deleted EXTRACT 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-ogg ogg]$ cd dirdat

[oracle@leo-10g-ogg dirdat]$ ll

total 28

-rw-rw-rw- 1 oracle oinstall 20186 Dec 11 15:39 ra000000

-rw-rw-rw- 1 oracle oinstall  1310 Dec 12 15:57 ra000001

-rw-rw-rw- 1 oracle oinstall  1004 Dec 12 15:57 ra000002

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

3.2、目标端

3.2.1、停止应用进程

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

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

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

 

Oracle GoldenGate Command Interpreter for Oracle

Version 19.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

Operating system character set identified as UTF-8.

 

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

 

GGSCI (leo-19c-ogg) 1> dblogin userid ogg@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                                          

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

 

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

 

Sending STOP request to REPLICAT REP1 ...

Request processed.

 

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

Deleted REPLICAT 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-ogg ogg]$ cd dirdat

[oracle@leo-19c-ogg dirdat]$ ll

total 8

-rw-r----- 1 oracle oinstall 1348 Dec 12 15:57 ra000003

-rw-r----- 1 oracle oinstall 1134 Dec 12 15:57 ra000004

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

3.2.4、删旧数据

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

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

 

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

Version 19.16.0.0.0

 

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.16.0.0.0

 

sys@SIMDB 2022-12-12 16:32:16> drop user scott cascade;

 

User dropped.

4、OGG配置

4.1、源端

4.1.1、数据准备

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

SQL> alter user scott account unlock;

 

User altered.

SQL> alter user scott identified by tiger;

 

User altered.

SQL> conn scott/tiger

Connected.

SQL> select table_name from user_tables;

 

TABLE_NAME

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

DEPT

EMP

BONUS

SALGRADE

 

4 rows selected.

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;

 

Please select the minimum SCN

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

 

2657697

 

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

 

SCNDATE

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

2022-12-12 17:06:39

注意：

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

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

--建directory name.

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

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

 

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 12 December, 2022 17:20:52

 

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.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_SCHEMA_01":  "/******** AS SYSDBA" directory=expdp_dir dumpfile=scott.dmp flashback_scn=2657697 logfile=expdp.log schemas=scott

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 256 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/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/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object 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

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

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

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

  /home/oracle/scott.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:21:02

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

4.1.3、添加附加日志

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

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

/u01/app/ogg

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

 

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

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

 

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.

 

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

 

Successfully logged into database.

 

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

 

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

 

Logging of supplemental redo data enabled for table SCOTT.BONUS.

 

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

 

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

 

2022-12-12 16:59: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 guarantee uniqueness.  KEYCOLS may be used to define the key.

 

Logging of supplemental redo data enabled for table SCOTT.SALGRADE.

 

2022-12-12 16:59: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 guarantee uniqueness.  KEYCOLS may be used to define the key.

 

Logging of supplemental redo data enabled for table SCOTT.TESTDDL.

 

注意：

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

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

区别如下：

add schematrandata scott  --scott用户

add trandata scott.*      --scott用户下的所有表

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

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

4.1.4、抓取进程

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

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

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

 

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

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

 

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.

 

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

Successfully logged into database.

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

 

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

EXTRACT added.

 

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

EXTTRAIL added.

--为exta进程配置参数

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

添加如下：

EXTRACT exta

setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

setenv (ORACLE_SID=orcl)

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

userid ogg@ORCL,password ogg

GETTRUNCATES

REPORTCOUNT EVERY 30 MINUTES,RATE

numfiles 5000

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

DISCARDROLLOVER AT 3:00

WARNLONGTRANS 2h,CHECKINTERVAL 3m

EXTTRAIL ./dirdat/ra,MEGABYTES 1024

DYNAMICRESOLUTION

NOCOMPRESSUPDATES

NOCOMPRESSDELETES

STATOPTIONS REPORTFETCH

DDL INCLUDE ALL

DDLOPTIONS ADDTRANDATA

DDLOPTIONS REPORT

TRANLOGOPTIONS EXCLUDEUSER ogg

table SCOTT.*;

 

参数说明:

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

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

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

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

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

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

--启动exta抓取进程

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

 

Sending START request to MANAGER ...

EXTRACT EXTA 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-ogg ogg]$ cd $OGG_HOME

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

 

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

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

 

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.

 

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

Successfully logged into database.

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

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

EXTRACT added.

--添加远程trail文件.

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

RMTTRAIL added.

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

添加如下：

extract dp1

userid ogg@ORCL,password ogg

setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

passthru

rmthost 192.168.133.109,mgrport 7809

rmttrail ./dirdat/ra

table SCOTT.*;

 

参数说明:

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

DYNAMICRESOLUTION：动态解析

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

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

 

--启用投递进程

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

 

Sending START request to MANAGER ...

EXTRACT DP1 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-ogg ogg]$ ./ggsci

GGSCI (leo-19c-ogg) 1> dblogin userid ogg@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

 

Successfully created 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

REPLICAT added.

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

添加如下：

REPLICAT rep1

setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

--setenv (ORACLE_SID=)

userid ogg@SIMDB,password ogg

REPORT AT 08:59

REPORTCOUNT EVERY 30 MINUTES, RATE

CACHEMGR CACHESIZE 2048MB,CACHEDIRECTORY ./dirtmp

REPERROR DEFAULT, ABEND

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

DISCARDROLLOVER AT 3:00

GETTRUNCATES

ALLOWNOOPUPDATES

APPLYNOOPUPDATES

ASSUMETARGETDEFS

DBOPTIONS DEFERREFCONST

DBOPTIONS SUPPRESSTRIGGERS

DDL INCLUDE MAPPED

DDLOPTIONS REPORT

DDLERROR DEFAULT IGNORE RETRYOP

MAP SCOTT.*, TARGET SCOTT.*;

 

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

参数说明:

dbOptions IntegratedParams：设置并行度

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

Reportrollover：指定何时生成 report 文件

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

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

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

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

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

5、目标端初始化数据

5.1、目标端导入

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

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

--导入数据

> create directory 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_dir dumpfile=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 ~]$ ll

total 280

drwxr-xr-x  2 oracle oinstall      6 Dec 10 18:27 dump

-rw-r--r--  1 oracle oinstall      0 Dec 12 17:43 impdp.log

-rw-------  1 oracle oinstall    254 Dec 12 17:43 nohup.out

-rw-r-----  1 oracle oinstall 282624 Dec 12 17:35 scott.dmp

drwxr-xr-x. 2 oracle oinstall     71 Dec 10 15:25 scripts

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

 

Import: Release 19.0.0.0.0 - Production on Mon Dec 12 17:47:08 2022

Version 19.16.0.0.0

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

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

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/TABLE/TABLE

Processing object 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

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/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object 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 2022-12-12 17:46:55> conn scott/tiger

Connected.

scott@SIMDB 2022-12-12 17:50:50> select * from dept;

 

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING    NEW YORK

        20 RESEARCH       DALLAS

        30 SALES           CHICAGO

        40 OPERATIONS     BOSTON

 

scott@SIMDB 2022-12-12 17:50:57> 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-09 00: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 rows selected.

6、初始化后操作

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

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

sys@SIMDB 2022-12-12 17:51:53> @?/rdbms/admin/utlrp.sql

--收集统计信息

sys@SIMDB 2022-12-10 15:41:59> exec dbms_stats.gather_database_stats(degree =>4);

 

PL/SQL procedure successfully completed.

 

--启动replicat进程.

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

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

 

Oracle GoldenGate Command Interpreter for Oracle

Version 19.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

Operating system character set identified as UTF-8.

 

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

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

Successfully logged into database.

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

 

Sending START request to MANAGER ...

REPLICAT REP1 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');

 

1 row created.

 

SQL> commit;

 

Commit complete.

--目标端查询.

sys@SIMDB 2022-12-10 15:48:57> conn scott/tiger

Connected.

scott@SIMDB 2022-12-12 17:57:08> 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);

 

Table altered.

 

SQL> desc dept

 Name                    Null?    Type

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

 DEPTNO                  NOT NULL NUMBER(8)

 DNAME                            VARCHAR2(14)

 LOC                              VARCHAR2(13)

--目标端查询.

scott@SIMDB 2022-12-10 15:52:30> 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 from user_constraints where table_name='DEPT'

 

OWNER                          CONSTRAINT_NAME                C TABLE_NAME                     INDEX_NAME

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

SCOTT                          PK_DEPT                        P DEPT                           PK_DEPT

--目标端查询

scott@SIMDB 2022-12-12 18:11:30> select owner,constraint_name,constraint_type,table_name,index_name from user_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);

 

Table altered.

 

SQL> select owner,constraint_name,constraint_type,table_name,index_name from user_constraints where 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 2022-12-12 18:13:47> select owner,constraint_name,constraint_type,table_name,index_name from user_constraints where 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

7.4、建表测试

--源端建testddl表.

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

 

Table created.

 

SQL> begin

  2  for i in 1..1000 loop

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

  4  end loop;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL> commit;

 

Commit complete.

 

SQL> select count(*) from testddl;

 

  COUNT(*)

----------

      1000

--目标端查询.

scott@SIMDB 2022-12-12 18:21:48> select table_name from user_tables;

 

TABLE_NAME

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

TESTDDL

DEPT

EMP

BONUS

SALGRADE

 

scott@SIMDB 2022-12-12 18:23:22> select count(*) from testddl

 

  COUNT(*)

----------

      1000

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