文档课题：运用ogg实现oracle 10g到19c的单表迁移.
核心思想：利用导入导出初始化数据后通过OGG同步增量数据.
源 端：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安装包不同.
1、系统检查
1.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)
1.2、目标端
[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、安装ogg
说明：创建ogg安装目录,通常使用系统用户oracle作为ogg的安装用户.
2.1、创建目录
--源端
[root@leo-10g-ogg ~]# mkdir -p /u01/app/ogg
--目标端
[root@leo-19c-ogg:~]# mkdir -p /u01/app/ogg
[root@leo-19c-ogg:~]# chown -R oracle:oinstall /u01/app/ogg
[root@leo-19c-ogg:~]# mkdir -p /u01/setup/ogg
2.2、环境变量
--源端
[oracle@leo-10g-ogg ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export OGG_HOME=/u01/app/ogg
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=.:$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$OGG_HOME:/lib/usr/lib:/usr/local/lib
说明：最后5行为新增部分.
[oracle@leo-10g-ogg ~]$ source .bash_profile
--目标端
[oracle@leo-19c-ogg ~]$ vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
# OracleBegin
umask 022
export TMP=/tmp
export TMPDIR=$TMP
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=simdb
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$PATH
export PERL5LIB=$ORACLE_HOME/perl/lib
alias sas='sqlplus / as sysdba'
alias awr='sqlplus / as sysdba @?/rdbms/admin/awrrpt'
alias ash='sqlplus / as sysdba @?/rdbms/admin/ashrpt'
alias alert='vi $ORACLE_BASE/diag/rdbms/*/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
#export PS1="[`whoami`@`hostname`:"'\w]$ '
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias adrci='rlwrap adrci'
export OGG_HOME=/u01/app/ogg
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=.:$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$OGG_HOME:/lib/usr/lib:/usr/local/lib
alias ggsci='cd $OGG_HOME;rlwrap ggsci'
说明：最后6行为新增部分.
[oracle@leo-19c-ogg ~]$ source .bash_profile
2.3、ogg安装包
--源端
sftp> cd /u01/app/ogg
sftp> lcd F:\installmedium\ogg
sftp> put fbo_ggs_Linux_x64_ora10g_64bit.tar
[root@leo-10g-ogg ~]# chown -R oracle:oinstall /u01/app/ogg
[oracle@leo-10g-ogg ogg]$ pwd
/u01/app/ogg
[oracle@leo-10g-ogg ogg]$ tar -xvf fbo_ggs_Linux_x64_ora10g_64bit.tar
--目标端
sftp> cd /u01/setup/ogg
sftp> lcd F:\installmedium\ogg
sftp> put 191004_fbo_ggs_Linux_x64_shiphome.zip
[root@leo-19c-ogg:~]# chown -R oracle:oinstall /u01/setup
[oracle@leo-19c-ogg ogg]$ pwd
/u01/setup/ogg
[oracle@leo-19c-ogg ogg]$ unzip -q 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@leo-19c-ogg ogg]$ ll
total 543540
-rw-r--r-- 1 oracle oinstall 556240981 Nov 30 10:41 191004_fbo_ggs_Linux_x64_shiphome.zip
drwxr-xr-x 3 oracle oinstall 19 Oct 18 2019 fbo_ggs_Linux_x64_shiphome
-rw-r--r-- 1 oracle oinstall 1413 May 29 2019 OGG-19.1.0.0-README.txt
-rw-r--r-- 1 oracle oinstall 332523 Oct 21 2019 OGG_WinUnix_Rel_Notes_19.1.0.0.4.pdf
2.4、安装ogg软件
--源端
解压fbo_ggs_Linux_x64_ora10g_64bit.tar后ogg软件即安装成功.
--目标端
说明：ogg自12c以后可以图形化安装，也可以静默安装.
[oracle@leo-19c-ogg response]$ pwd
/u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response
[oracle@leo-19c-ogg response]$ vi oggcore.rsp
修改如下两行:
INSTALL_OPTION=ORA19c
SOFTWARE_LOCATION=/u01/app/ogg
静默安装:
[oracle@leo-19c-ogg response]$ /u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller -silent -responseFile /u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 67565 MB Passed
Checking swap space: must be greater than 150 MB. Actual 8191 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-12-10_02-32-41PM. Please wait ...[oracle@leo-19c-ogg response]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2022-12-10_02-32-41PM.log
Successfully Setup Software.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2022-12-10_02-32-41PM.log' for more details.
3、数据库配置
3.1、源端
3.1.1、开启归档
[oracle@leo-10g-ogg oracle]$ mkdir -p /u01/app/oracle/archivelog
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2083560 bytes
Variable Size 419431704 bytes
Database Buffers 788529152 bytes
Redo Buffers 14692352 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog/';
System altered.
SQL> alter database open;
Database altered.
3.1.2、开启强制生成日志&补充日志
SQL> select log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
LOG_MODE FOR SUPPLEME SUP SUP
------------ --- -------- --- ---
NOARCHIVELOG NO NO NO NO
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> select log_mode,force_logging,supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_all from v$database;
LOG_MODE FOR SUPPLEME SUP SUP
------------ --- -------- --- ---
NOARCHIVELOG YES YES NO NO
思考：对于逻辑复制，如何将源端操作准确无误地反应到目标端？
源端数据库和目标端数据库的数据块结构可能完全不一样，存在无法通过rowid信息进行精确查找定位的问题，此时就需要开启supplemental log，当启用supplemental logging，对于修改操作oracle就会同时附加一些能够唯一标识修改记录的列到rebo log中.如果表有主键或唯一键，只需附加主键或唯一建信息即可，此时生成的redo日志量最少；如果某些表无法创建主键或唯一键，或者数据表本来就不存在主键/唯一键，此时oracle会将所有列都作为附加信息记录到redo中，那么redo就可能增长很快，此时对性能会产生很大影响.所以oracle建议所有需要复制的表都需存在主键或唯一键.supplemental logging可以在数据库级设置，也可以在表级设置.对于数据库级有两种类型：minimal logging和identification key logging，其主要区别在于写入redolog中的数据详尽程度不同.
综上：GoldenGate要准确知道源端数据修改的具体列信息，就需要更为详细的日志信息，所以需开启supplemental log.
3.1.3、recyclebin
说明：10gr2需将recyclebin设置为off，11g(包括)以后不用.
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> alter system set recyclebin=off;
System altered.
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
3.1.4、enable_ goldengate_replication
说明：oracle 11g前不需要设置enable_ goldengate_replication参数.
3.2、目标端
3.2.1、enable_ goldengate_replication
开启enable_goldengate_replication.
sys@SIMDB 2022-12-09 14:46:22> show parameter enable_goldengate_replication
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
sys@SIMDB 2022-12-09 14:46:32> alter system set enable_goldengate_replication=true;
System altered.
sys@SIMDB 2022-12-09 14:49:48> show parameter enable_goldengate_replication
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
4、创建用户、表空间及授权
4.1、源端
创建ogg表空间、用户并授权：
create tablespace ogg_tbs datafile '/u01/app/oracle/oradata/orcl/ogg_tbs01.dbf' size 1G autoextend on maxsize 30G;
create user ogg identified by ogg default tablespace ogg_tbs;
grant connect,resource,unlimited tablespace to ogg;
grant create session,alter session,create sequence,create table to ogg;
grant select any dictionary,select any table to ogg;
grant alter any table to ogg;
grant flashback any table to ogg;
grant execute on dbms_flashback to ogg;
grant execute on utl_file to ogg;
grant select any transaction to ogg;
grant become user to ogg;
exec dbms_streams_auth.grant_admin_privilege('ogg');
grant insert on system.logmnr_restart_ckpt$ to ogg;
grant update on sys.streams$_capture_process to ogg;
4.2、目标端
创建ogg表空间和用户，并授权.
create tablespace ogg_tbs datafile '/u01/app/oracle/oradata/SIMDB/ogg_tbs01.dbf' size 1G autoextend on maxsize 30G;
create user ogg identified by ogg default tablespace ogg_tbs;
grant connect,resource,unlimited tablespace to ogg;
grant create session,alter session to ogg;
grant select any dictionary,select any table to ogg;
grant alter any table to ogg;
grant flashback any table to ogg;
grant execute on dbms_flashback to ogg;
grant execute on utl_file to ogg;
grant dba to ogg;
grant select any transaction to ogg;
exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ogg');
5、配置DDL同步
5.1、运行ddl脚本
早期版本11204之前通常按如下操作.
[oracle@leo-10g-ogg ogg]$ cd $OGG_HOME
[oracle@leo-10g-ogg ogg]$ pwd
/u01/app/ogg
[oracle@leo-10g-ogg ogg]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Dec 10 14:52:28 2022
Copyright (c) 1982, 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
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:ogg --输入ogg用户
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup.sql;
GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:ogg --输入ogg用户
You will be prompted for the mode of installation.
……
Using OGG as a GoldenGate schema name, INITIALSETUP as a mode of installation.
Working, please wait ...
RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.
Enter yes or no:yes --输入yes
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
……
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @role_setup.sql;
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:ogg --输入ogg用户
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> grant GGS_GGSUSER_ROLE to ogg;
Grant succeeded.
SQL> @ddl_enable.sql
Trigger altered.
5.2、配置tns
说明：若环境为多实例则需要配置tns登录创建的ogg用户,19c PDB也需要tns登录到相应的数据库.
[oracle@leo-10g-ogg admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = leo-10g-ogg)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
[oracle@leo-10g-ogg admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = leo-10g-ogg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = leo-10g-ogg)(PORT = 1521))
--重启监听
[oracle@leo-10g-ogg admin]$ lsnrctl stop
[oracle@leo-10g-ogg admin]$ lsnrctl start
6、OGG配置
6.1、源端
6.1.1、数据准备
本次使用scott用户的dept、emp表作为测试.注意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.
6.1.2、打开表级附加日志
[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.
注意：此处测试表级同步，选择scott的DEPT、EMP两张表作为示例.
GGSCI (leo-10g-ogg) 2> add trandata scott.dept
Logging of supplemental redo data enabled for table SCOTT.DEPT.
GGSCI (leo-10g-ogg) 3> add trandata scott.emp
Logging of supplemental redo data enabled for table SCOTT.EMP.
问题：数据库附加日志打开后还需要对每张表执行add trandata吗？答案是肯定的，如果不执行add trandata，insert同步没有问题（ORACLE数据库），但在同步update或delete时，就会因为丢失主键报同步错误.不开启表级的最小附加日志，update的redo信息不记录没有进行更新的字段信息，如主键不更新的话主键不记录在redo中，所以会导致同步失败.
--查附加日志是否添加成功.
GGSCI (leo-10g-ogg) 4> info trandata scott.dept
Logging of supplemental redo log data is enabled for table SCOTT.DEPT
GGSCI (leo-10g-ogg) 5> info trandata scott.emp
Logging of supplemental redo log data is enabled for table SCOTT.EMP
6.1.3、创建目录
注意：必须在ogg的home目录下启动ggsci，这样才能把子目录建在ogg的home目录下，如果不在相应位置建立子目录，在后面编辑参数文件时会报错.
[oracle@leo-11g-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.
--创建 OGG工作目录
GGSCI (leo-10g-ogg) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter files /u01/app/ogg/dirprm: created
Report files /u01/app/ogg/dirrpt: created
Checkpoint files /u01/app/ogg/dirchk: created
Process status files /u01/app/ogg/dirpcs: created
SQL script files /u01/app/ogg/dirsql: created
Database definitions files /u01/app/ogg/dirdef: created
Extract data files /u01/app/ogg/dirdat: created
Temporary files /u01/app/ogg/dirtmp: created
Veridata files /u01/app/ogg/dirver: created
Veridata Lock files /u01/app/ogg/dirver/lock: created
Veridata Out-Of-Sync files /u01/app/ogg/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/app/ogg/dirver/oosxml: created
Veridata Parameter files /u01/app/ogg/dirver/params: created
Veridata Report files /u01/app/ogg/dirver/report: created
Veridata Status files /u01/app/ogg/dirver/status: created
Veridata Trace files /u01/app/ogg/dirver/trace: created
Stdout files /u01/app/ogg/dirout: created
重要目录说明：
dirchk：存放检查点(checkpoint)文件
dirdat：存放trail文件
dirdef：存放通过defgen工具生成的源或目标端数据定义文件
dirpcs：存放进程状态文件
dirprm：存放配置参数文件
dirrpt：存放进程报告文件
dirsql：存放sql脚本文件
dirtmp：当事务所需要的内存超过已分配的内存时，默认存储在此目录
6.1.4、mgr进程
6.1.4.1、源端配置
GGSCI (leo-10g-ogg) 2> edit param mgr
添加如下内容
port 7809
DYNAMICPORTLIST 7810-7829
userid ogg@ORCL, PASSWORD ogg
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 8
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
--ACCESSRULE, PROG *, IPADDR 12.*.*.*, PRI 1, ALLOW
参数说明：
PORT:指定Manager使用的端口.
Dynamicportliist:配置捕获和复制进程使用的端口范围.
AUTORESTART:使抽取/复制进程失败后自动重启.
PURGEOLDEXTRACTS:参数指定当根据checkpoint发现已完成抽取和复制的trail文件将被自动删除，但保留最近10个.
Purgeddlhistory和purgemarkerhistory分别删除历史DD、历史表和marker表中的过期数据，以控制其不会过于庞大.
GGSCI (leo-10g-ogg) 3> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (leo-10g-ogg) 4> start mgr
Manager started.
GGSCI (leo-10g-ogg) 5> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
6.1.4.2、目标端配置
注意：提前在目标端配置并开启mgr进程，否则源端投递进程启动会失败.
[oracle@leo-19c-ogg ~]$ cd $OGG_HOME
[oracle@leo-19c-ogg ogg]$ pwd
/u01/app/ogg
[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> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter file /u01/app/ogg/dirprm: created.
Report file /u01/app/ogg/dirrpt: created.
Checkpoint file /u01/app/ogg/dirchk: created.
Process status files /u01/app/ogg/dirpcs: created.
SQL script files /u01/app/ogg/dirsql: created.
Database definitions files /u01/app/ogg/dirdef: created.
Extract data files /u01/app/ogg/dirdat: created.
Temporary files /u01/app/ogg/dirtmp: created.
Credential store files /u01/app/ogg/dircrd: created.
Masterkey wallet files /u01/app/ogg/dirwlt: created.
Dump files /u01/app/ogg/dirdmp: created.
GGSCI (leo-19c-ogg as ogg@simdb) 2> edit param mgr
添加以下：
port 7809
DYNAMICPORTLIST 7810-7829
userid ogg@SIMDB, password ogg
--AUTOSTART EXTRACT *
AUTORESTART EXTRACT *,RETRIES 3,WAITMINUTES 7,RESETMINUTES 60
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 8
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
参数说明：
AUTORESTART:表示每7分钟尝试重新启动所有进程，共尝试三次.以后每60分钟清零，再按照每7分钟尝试一次共试三次.
GGSCI (leo-19c-ogg as ogg@simdb) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (leo-19c-ogg as ogg@simdb) 4> start mgr
Manager started.
GGSCI (leo-19c-ogg as ogg@simdb) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
6.1.5、抓取进程
--配置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 now
2022-12-04 21:08:54 INFO OGG-01749 Successfully registered EXTRACT EXTA to start managing log retention at SCN 1050831.
EXTRACT added.
GGSCI (leo-10g-ogg) 3> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXTA 00:00:00 00:13:15
--添加本地队列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.DEPT;
table SCOTT.EMP;
参数说明:
GETUPDATEBEFORES：是否在队列中写入后镜像，也可进行维护事务历史表.默认是复制；
GETTRUNCATES：是否在队列中进行复制truncate操作，默认是不复制；
BR BRINTERVAL：对于存在长事务恢复情况下，恢复到检查点时间界限；
CACHEMGR CACHESIZE：主要用于控制存放未提交事务的虚拟内存和文件缓存空间；
WARNLONGTRANS：长事务警告频率，用于收集监控长事务情况；
CHECKINTERVAL：同样也是作为长事务WARNLONGTRANS监控频率；
--启动exta抓取进程
GGSCI (leo-10g-ogg) 6> start EXTA
Sending START request to MANAGER ...
EXTRACT EXTU1 starting
GGSCI (leo-10g-ogg) 7> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTA 04:11:41 00:00:00
6.1.6、投递进程
[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.DEPT;
table SCOTT.EMP;
参数说明:
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:34:50
EXTRACT RUNNING EXTA 00:02:00 00:00:01
注意：启动投递进程 dp1前，目标端mgr进程需先启动.
6.2、目标端
6.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.
6.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 942 IGNORE
MAP SCOTT.DEPT, TARGET SCOTT.DEPT;
MAP SCOTT.EMP, TARGET SCOTT.EMP;
特别说明：应用进程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做一次文件过期设定.
7、目标端初始化数据
7.1、查源端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
----------------------------------------
2481634
7.2、源端导出数据
源端导出数据，用于目标端.
[oracle@leo-10g-ogg ogg]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@leo-10g-ogg ogg]$ exp scott/tiger@orcl file=/home/oracle/scott.dmp tables=dept,emp rows=y log=/home/oracle/exp.log
Export: Release 10.2.0.4.0 - Production on Sun Dec 10 15:13:24 2022
Copyright (c) 1982, 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
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
Export terminated successfully without warnings.
--将源端dmp文件scp到目标端.
[oracle@leo-10g-ogg ~]$ scp scott.dmp oracle@192.168.133.109:/home/oracle/
7.3、目标端导入
--创建用户并授予权限.
sys@SIMDB 2022-12-10 15:15:54> create user scott identified by tiger;
User created.
sys@SIMDB 2022-12-10 15:16:11> grant connect,resource to scott;
Grant succeeded.
sys@SIMDB 2022-12-10 15:16:28> alter user scott quota unlimited on users;
User altered.
--导入数据
[oracle@leo-19c-ogg ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@leo-19c-ogg ~]$ imp scott/tiger file=/home/oracle/scott.dmp full=y
Import: Release 19.0.0.0.0 - Production on Sat Dec 10 15:30:01 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
Version 19.16.0.0.0
Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
--验证测试数据.
sys@SIMDB 2022-12-10 15:31:32> conn scott/tiger
Connected.
scott@SIMDB 2022-12-10 15:31:37> 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.
scott@SIMDB 2022-12-10 15:31:41> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
7.4、初始化后操作
初始化数据完成后的相关配置.
--编译无效对象(非必须)
sys@SIMDB 2022-12-10 15:41:54> @?/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 2481634 --2481634为目标端初始化之前源端所查得的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
8、数据验证
8.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-10 15:49:02> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT ChongQing
8.2、DDL测试
源端修改字段长度.
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> alter table dept modify deptno number(5);
Table altered.
SQL> desc dept
Name Null? Type
----------------------- -------- ----------------
DEPTNO NOT NULL NUMBER(5)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
--目标端查询.
scott@SIMDB 2022-12-10 15:52:30> desc dept
Name Null? Type
----------------------- -------- ----------------
DEPTNO NOT NULL NUMBER(5)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
8.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
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-10 15:58:05> 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
8.4、建表测试
--源端
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.
--目标端
scott@SIMDB 2022-12-10 16:01:33> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------
DEPT
EMP
说明：目标端除dept、emp表以外的操作均不同步.
1439
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
