运用ogg实现oracle 10g到19c的单表迁移

denglj · 发表于 2022-12-14 16:08:46

文档课题：运用ogg实现oracle10g到19c的单表迁移.

源端：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同步增量数据.

原理分析：

ogg主要是四个服务.

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

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

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

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

登录/注册后可看大图

70292639981e0d1b70.png (280.93 KB, 下载次数: 302)

下载附件

2022-12-14 15:57 上传

1、系统检查1.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)

1.2、目标端

[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、安装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

exportPATH

exportORACLE_BASE=/u01/app/oracle

exportORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

exportORACLE_SID=orcl

exportPATH=$ORACLE_HOME/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin

exportLD_LIBRARY_PATH=$ORACLE_HOME/lib

exportOGG_HOME=/u01/app/ogg

exportNLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"

exportNLS_LANG=AMERICAN_AMERICA.AL32UTF8

exportPATH=.:$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$OGG_HOME

exportLD_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

exportPATH

#OracleBegin

umask022

exportTMP=/tmp

exportTMPDIR=$TMP

exportNLS_LANG=AMERICAN_AMERICA.AL32UTF8

exportORACLE_BASE=/u01/app/oracle

exportORACLE_HOME=/u01/app/oracle/product/19.3.0/db

exportORACLE_TERM=xterm

exportTNS_ADMIN=$ORACLE_HOME/network/admin

exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

exportORACLE_SID=simdb

exportPATH=/usr/sbin:$PATH

exportPATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$PATH

exportPERL5LIB=$ORACLE_HOME/perl/lib

aliassas='sqlplus / as sysdba'

aliasawr='sqlplus / as sysdba @?/rdbms/admin/awrrpt'

aliasash='sqlplus / as sysdba @?/rdbms/admin/ashrpt'

aliasalert='vi $ORACLE_BASE/diag/rdbms/*/$ORACLE_SID/trace/alert_$ORACLE_SID.log'

#exportPS1="[`whoami`@`hostname`:"'\w]$ '

aliassqlplus='rlwrap sqlplus'

aliasrman='rlwrap rman'

aliasadrci='rlwrap adrci'

exportOGG_HOME=/u01/app/ogg

exportNLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"

exportNLS_LANG=AMERICAN_AMERICA.AL32UTF8

exportPATH=.:$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$OGG_HOME

exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$OGG_HOME:/lib/usr/lib:/usr/local/lib

aliasggsci='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-oggogg]$ pwd

/u01/app/ogg

[oracle@leo-10g-oggogg]$ 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-oggogg]$ pwd

/u01/setup/ogg

[oracle@leo-19c-oggogg]$ unzip -q 191004_fbo_ggs_Linux_x64_shiphome.zip

[oracle@leo-19c-oggogg]$ ll

total543540

-rw-r--r--1 oracle oinstall 556240981 Nov 30 10:41 191004_fbo_ggs_Linux_x64_shiphome.zip

drwxr-xr-x3 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-oggresponse]$ pwd

/u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response

[oracle@leo-19c-oggresponse]$ vi oggcore.rsp

修改如下两行:

INSTALL_OPTION=ORA19c

SOFTWARE_LOCATION=/u01/app/ogg

静默安装:

[oracle@leo-19c-oggresponse]$ /u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller -silent-responseFile/u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

StartingOracle Universal Installer...

CheckingTemp space: must be greater than 120 MB. Actual 67565 MB Passed

Checkingswap space: must be greater than 150 MB. Actual 8191 MB Passed

Preparingto launch Oracle Universal Installer from /tmp/OraInstall2022-12-13_10-32-41AM.Please wait ...[oracle@leo-19c-ogg response]$ You can find the log of thisinstall session at:

/u01/app/oraInventory/logs/installActions2022-12-13_10-32-41AM.log

SuccessfullySetup Software.

Theinstallation of Oracle GoldenGate Core was successful.

Pleasecheck '/u01/app/oraInventory/logs/silentInstall2022-12-13_10-32-41AM.log' formore details.

3、数据库配置3.1、源端3.1.1、开启归档

[oracle@leo-10g-oggoracle]$ mkdir -p /u01/app/oracle/archivelog

SQL>shutdown immediate;

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

SQL>startup mount;

ORACLEinstance started.

TotalSystem Global Area 1224736768 bytes

Fixed Size 2083560 bytes

VariableSize 419431704 bytes

DatabaseBuffers 788529152 bytes

RedoBuffers 14692352 bytes

Databasemounted.

SQL>alter database archivelog;

Databasealtered.

SQL>alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog/';

Systemaltered.

SQL>alter database open;

Databasealtered.

3.1.2、开启强制生成日志&补充日志

SQL>selectlog_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_ALLfrom v$database;

LOG_MODE FOR SUPPLEME SUP SUP

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

NOARCHIVELOGNO NO NO NO

SQL>alter database add supplemental log data;

Databasealtered.

SQL>alter database force logging;

Databasealtered.

SQL>select log_mode,force_logging,supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_allfrom v$database;

LOG_MODE FOR SUPPLEME SUP SUP

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

NOARCHIVELOGYES YES NO NO

思考：对于逻辑复制，如何将源端操作准确无误地反应到目标端？

源端数据库和目标端数据库的数据块结构可能完全不一样，存在无法通过rowid信息进行精确查找定位的问题，此时就需要开启supplementallog，当启用supplementallogging，对于修改操作oracle就会同时附加一些能够唯一标识修改记录的列到rebolog中.如果表有主键或唯一键，只需附加主键或唯一建信息即可，此时生成的redo日志量最少；如果某些表无法创建主键或唯一键，或者数据表本来就不存在主键/唯一键，此时oracle会将所有列都作为附加信息记录到redo中，那么redo就可能增长很快，此时对性能会产生很大影响.所以oracle建议所有需要复制的表都需存在主键或唯一键.supplementallogging可以在数据库级设置，也可以在表级设置.对于数据库级有两种类型：minimallogging和identification key logging，其主要区别在于写入redolog中的数据详尽程度不同.

综上：GoldenGate要准确知道源端数据修改的具体列信息，就需要更为详细的日志信息，所以需开启supplementallog.

3.1.3、recyclebin

说明：10gr2需将recyclebin设置为off，11g(包括)以后不用.

SQL>show parameter recyclebin

NAME TYPE VALUE

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

recyclebin string on

SQL>alter system set recyclebin=off;

Systemaltered.

SQL>show parameter recyclebin

NAME TYPE VALUE

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

recyclebin string OFF

3.1.4、enable_goldengate_replication

说明：oracle11g前不需要设置enable_goldengate_replication参数.

3.2、目标端3.2.1、enable_goldengate_replication

开启enable_goldengate_replication.

sys@SIMDB > show parameter enable_goldengate_replication

NAME TYPE VALUE

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

enable_goldengate_replication boolean FALSE

sys@SIMDB > alter system set enable_goldengate_replication=true;

Systemaltered.

sys@SIMDB > show parameter enable_goldengate_replication

NAME TYPE VALUE

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

enable_goldengate_replication boolean TRUE

4、创建用户、表空间及授权4.1、源端

创建ogg表空间、用户并授权：

createtablespace ogg_tbs datafile '/u01/app/oracle/oradata/orcl/ogg_tbs01.dbf' size1G autoextend on maxsize 30G;

createuser ogg identified by ogg default tablespace ogg_tbs temporary tablespace TEMPquota unlimited on ogg;

grantconnect,resource,unlimited tablespace to ogg;

grantcreate session,alter session,create sequence,create table to ogg;

grantselect any dictionary,select any table to ogg;

grantalter any table to ogg;

grantflashback any table to ogg;

grantexecute on dbms_flashback to ogg;

grantexecute on utl_file to ogg;

grantselect any transaction to ogg;

grantbecome user to ogg;

execdbms_streams_auth.grant_admin_privilege('ogg');

grantinsert on system.logmnr_restart_ckpt$ to ogg;

grantupdate on sys.streams$_capture_process to ogg;

说明：抓取进程使用的数据库用户需要额外的权限，后面四种权限也需授予数据库用户ogg.生产环境中，为确保ogg正常运行，通常在目标端授予ogg用户dba权限.

4.2、目标端

创建ogg表空间和用户，并授权.

createtablespace ogg_tbs datafile '/u01/app/oracle/oradata/SIMDB/ogg_tbs01.dbf' size1G autoextend on maxsize 30G;

createuser ogg identified by ogg default tablespace ogg_tbs;

grantconnect,resource,unlimited tablespace to ogg;

grantcreate session,alter session to ogg;

grantselect any dictionary,select any table to ogg;

grantalter any table to ogg;

grantflashback any table to ogg;

grantexecute on dbms_flashback to ogg;

grantexecute on utl_file to ogg;

grantdba to ogg;

grantselect any transaction to ogg;

execDBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ogg');

5、配置DDL同步5.1、运行ddl脚本

早期版本11204之前通常按如下操作.

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

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

/u01/app/ogg

[oracle@leo-10g-oggogg]$ sqlplus / as sysdba

SQL>@marker_setup.sql

Markersetup script

Youwill 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.

EnterGoldenGate schema name:ogg --输入ogg用户

Markersetup table script complete, running verification script...

Pleaseenter the name of a schema for the GoldenGate database objects:

Settingschema name to OGG

MARKERTABLE

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

OK

MARKERSEQUENCE

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

OK

Scriptcomplete.

SQL>@ddl_setup.sql;

GoldenGateDDL Replication setup script

Verifyingthat current user has privileges to install DDL Replication...

Youwill 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 Oracle11g 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.

EnterGoldenGate schema name:ogg --输入ogg用户

Youwill be prompted for the mode of installation.

……

UsingOGG as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working,please wait ...

RECYCLEBINmust be empty.

Thisinstallation will purge RECYCLEBIN for all users.

Toproceed, enter yes. To stop installation, enter no.

Enteryes or no:yes --输入yes

DDLreplication setup script complete, running verification script...

Pleaseenter the name of a schema for the GoldenGate database objects:

Settingschema name to OGG

……

STATUSOF DDL REPLICATION

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

SUCCESSFULinstallation of DDL Replication software components

Scriptcomplete.

SQL>@role_setup.sql;

GGSRole setup script

Thisscript will drop and recreate the role GGS_GGSUSER_ROLE

Touse a different role name, quit this script and then edit the params.sql scriptto change the gg_role parameter to the preferred name. (Do not run the script.)

Youwill 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.

EnterGoldenGate schema name:ogg --输入ogg用户

Wrotefile role_setup_set.txt

PL/SQLprocedure successfully completed.

Rolesetup script complete

Grantthis role to each user assigned to the Extract, GGSCI, and Manager processes,by using the following SQL command:

GRANTGGS_GGSUSER_ROLE TO <loggedUser>

where<loggedUser> is the user assigned to the GoldenGate processes.

SQL>grant GGS_GGSUSER_ROLE to ogg;

Grantsucceeded.

SQL>@ddl_enable.sql

Triggeraltered.

5.2、配置tns

说明：若环境为多实例则需要配置tns登录创建的ogg用户,19cPDB也需要tns登录到相应的数据库.

[oracle@leo-10g-oggadmin]$ 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-oggadmin]$ 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-oggadmin]$ lsnrctl stop

[oracle@leo-10g-oggadmin]$ lsnrctl start

6、OGG配置6.1、源端6.1.1、数据准备

本次使用scott用户的dept、emp表作为测试.注意oracle19.16没有该用户.

SQL> alter user scott account unlock;

User altered.

SQL> alter user scott identified bytiger;

User altered.

SQL> conn scott/tiger

Connected.

SQL> select table_name fromuser_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 forOracle

Version 11.1.1.1OGGCORE_11.1.1_PLATFORMS_110421.2040

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

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

Successfully logged into database.

注意：此处测试表级同步，选择scott的DEPT、EMP两张表作为示例.

GGSCI (leo-10g-ogg) 2> add trandatascott.dept

Logging of supplemental redo data enabledfor table SCOTT.DEPT.

GGSCI (leo-10g-ogg) 3> add trandatascott.emp

Logging of supplemental redo data enabledfor table SCOTT.EMP.

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

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

GGSCI (leo-10g-ogg) 4> info trandatascott.dept

Logging of supplemental redo log data isenabled for table SCOTT.DEPT

GGSCI (leo-10g-ogg) 5> info trandatascott.emp

Logging of supplemental redo log data isenabled for table SCOTT.EMP

6.1.3、创建目录

注意：必须在ogg的home目录下启动ggsci，这样才能把子目录建在ogg的home目录下，如果不在相应位置建立子目录，在后面编辑参数文件时会报错.

[oracle@leo-11g-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

--创建 OGG工作目录

GGSCI(leo-10g-ogg) 1> create subdirs

Creatingsubdirectories under current directory /u01/app/ogg

Parameterfiles /u01/app/ogg/dirprm:created

Reportfiles /u01/app/ogg/dirrpt: created

Checkpointfiles /u01/app/ogg/dirchk:created

Processstatus files /u01/app/ogg/dirpcs: created

SQLscript files /u01/app/ogg/dirsql: created

Databasedefinitions files /u01/app/ogg/dirdef: created

Extractdata files /u01/app/ogg/dirdat: created

Temporaryfiles /u01/app/ogg/dirtmp:created

Veridatafiles /u01/app/ogg/dirver: created

VeridataLock files /u01/app/ogg/dirver/lock: created

VeridataOut-Of-Sync files /u01/app/ogg/dirver/oos: created

VeridataOut-Of-Sync XML files /u01/app/ogg/dirver/oosxml: created

VeridataParameter files /u01/app/ogg/dirver/params: created

VeridataReport files /u01/app/ogg/dirver/report: created

VeridataStatus files /u01/app/ogg/dirver/status: created

VeridataTrace files /u01/app/ogg/dirver/trace: created

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

添加如下内容

port7809

DYNAMICPORTLIST7810-7829

useridogg@ORCL, PASSWORD ogg

AUTOSTARTEXTRACT *

AUTORESTARTEXTRACT *

PURGEOLDEXTRACTS./dirdat/*,usecheckpoints, minkeepdays 8

LAGREPORTHOURS1

LAGINFOMINUTES30

LAGCRITICALMINUTES45

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

Managerstarted.

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-oggogg]$ pwd

/u01/app/ogg

[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> create subdirs

Creatingsubdirectories under current directory /u01/app/ogg

Parameterfile /u01/app/ogg/dirprm:created.

Reportfile /u01/app/ogg/dirrpt: created.

Checkpointfile /u01/app/ogg/dirchk:created.

Processstatus files /u01/app/ogg/dirpcs: created.

SQLscript files /u01/app/ogg/dirsql: created.

Databasedefinitions files /u01/app/ogg/dirdef: created.

Extractdata files /u01/app/ogg/dirdat:created.

Temporaryfiles /u01/app/ogg/dirtmp:created.

Credentialstore files /u01/app/ogg/dircrd:created.

Masterkeywallet files /u01/app/ogg/dirwlt:created.

Dumpfiles /u01/app/ogg/dirdmp:created.

GGSCI(leo-19c-ogg as ogg@simdb) 2> edit param mgr

添加以下：

port7809

DYNAMICPORTLIST7810-7829

useridogg@SIMDB, password ogg

--AUTOSTARTEXTRACT *

AUTORESTARTEXTRACT *,RETRIES 3,WAITMINUTES 7,RESETMINUTES 60

PURGEOLDEXTRACTS./dirdat/*,usecheckpoints, minkeepdays 8

PURGEDDLHISTORYMINKEEPDAYS 7, MAXKEEPDAYS 10

PURGEMARKERHISTORYMINKEEPDAYS 7, MAXKEEPDAYS 10

LAGREPORTHOURS1

LAGINFOMINUTES30

LAGCRITICALMINUTES45

参数说明：

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

Managerstarted.

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-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 now

2022-12-13 13:08:54 INFO OGG-01749 Successfully registered EXTRACT EXTA to start managing log retention atSCN 1050831.

EXTRACTadded.

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

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.DEPT;

tableSCOTT.EMP;

参数说明:

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

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

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

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

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

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

--启动exta抓取进程

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

SendingSTART request to MANAGER ...

EXTRACTEXTU1 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-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.DEPT;

tableSCOTT.EMP;

参数说明:

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: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-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.

6.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

DDLERROR942 IGNORE

MAPSCOTT.DEPT, TARGET SCOTT.DEPT;

MAPSCOTT.EMP, TARGET SCOTT.EMP;

特别说明：应用进程rep1在目标端完成初始化之后再启动.

参数说明:

dbOptionsIntegratedParams：设置并行度

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

Reportrollover：指定何时生成report 文件

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

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

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

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

DISCARDROLLOVERAT 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;

Pleaseselect the minimum SCN

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

2481634

7.2、源端导出数据

源端导出数据，用于目标端.

[oracle@leo-10g-oggogg]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@leo-10g-oggogg]$ exp scott/tiger@orcl file=/home/oracle/scott.dmp tables=dept,emp rows=ylog=/home/oracle/exp.log

Export:Release 10.2.0.4.0 - Production on Sun Dec 13 15:13:24 2022

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

With thePartitioning, OLAP, Data Mining and Real Application Testing options

Exportdone in AL32UTF8 character set and AL16UTF16 NCHAR character set

About toexport specified tables via Conventional Path ...

. .exporting table DEPT 4 rows exported

. .exporting table EMP 14 rows exported

Exportterminated successfully without warnings.

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

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

7.3、目标端导入

--创建用户并授予权限.

sys@SIMDB > create user scott identified by tiger;

Usercreated.

sys@SIMDB > grant connect,resource to scott;

Grantsucceeded.

sys@SIMDB > alter user scott quota unlimited on users;

Useraltered.

--导入数据

[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 13 15:30:01 2022

Version19.16.0.0.0

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

Version19.16.0.0.0

Exportfile created by EXPORT:V10.02.01 via conventional path

importdone in AL32UTF8 character set and UTF8 NCHAR character set

exportserver uses AL16UTF16 NCHAR character set (possible ncharset conversion)

.importing SCOTT's objects into SCOTT

. .importing table "DEPT" 4 rowsimported

. .importing table "EMP" 14 rowsimported

About toenable constraints...

Importterminated successfully without warnings.

--验证测试数据.

sys@SIMDB > conn scott/tiger

Connected.

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.

scott@SIMDB > select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

7.4、初始化后操作

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

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

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 2481634 --2481634为目标端初始化之前源端所查得的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

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

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

8.2、DDL测试

源端修改字段长度.

SQL>desc dept;

Name Null? Type

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

DEPTNO NOT NULLNUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

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

Tablealtered.

SQL>desc dept

Name Null? Type

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

DEPTNO NOT NULL NUMBER(5)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

--目标端查询.

scott@SIMDB > 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 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 fromuser_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 > 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

8.4、建表测试

--源端

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.

--目标端

scott@SIMDB > select table_name from user_tables;

TABLE_NAME

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

DEPT

EMP

说明：目标端除dept、emp表以外的操作均不同步.

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

[Oracle] 运用ogg实现oracle 10g到19c的单表迁移