标签：00 12 10g leo oracle ogg
文档课题：运用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 [email protected]:/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分别添加进抓取、投递、应用进程.
标签：00,
12,
10g,
leo,
oracle,
ogg
From： https://blog.51cto.com/u_12991611/5948186