昨天突然接到消息说有一个线上的ogg出现了问题，看是否能修复，由于ogg以前玩的少，所以就加急搞了个测试环境，练习了一把
环境
db1,db2(单实例)
ip: 1*,1*
sid: orcl,ogg1
os: centos 6.5,centos 6.5
oracle version: 11.2.0.4,11.2.0.4
1 --可以创建ogg用户，也可以使用oracle用户
useradd -u 2100 -g oinstall -G dba ogg
passwd ogg
[ogg@DSI ~]$ vim .bash_profile export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib export ORACLE_SID=orcl export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/jdk/bin:$PATH [ogg@DSI ~]$ source .bash_profile
2 下载安装ogg
[root@DSI ~]# mkdir -p /u01/app/ogg [root@DSI ~]# chown -R oracle:oinstall /u01/app/ogg [root@DSI opt]# unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip Archive: ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf inflating: Oracle GoldenGate 11.2.1.0.1 README.txt inflating: Oracle GoldenGate 11.2.1.0.1 README.doc [root@DSI opt]# tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/app/ogg/ [root@DSI ogg]# chown -R oracle:oinstall /u01/app/ogg
解压成功之后，登录OGG测试
[oracle@DSI ogg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (DSI) 1> ##注意：如果LD_LIBRARY_PATH变量设置正确，会像上面演示一样正确进入命令行，如果提示找不到so文件，就需要查看LD_LIBRARY_PATH变量是否配置正确
3 源端创建OGG表空间和OGG用户---两个服务器都要做
SYS@ orcl > create tablespace ogg datafile '/u01/app/oracle/oradata/orcl/ogg01.dbf' size 50m autoextend on; Tablespace created. SYS@ orcl > create user ogg identified by ogg default tablespace ogg; User created SYS@ orcl >grant CONNECT, RESOURCE to ogg; Grant succeeded. SYS@ orcl >grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg; Grant succeeded. SYS@ orcl >grant ALTER ANY TABLE to ogg; Grant succeeded. SYS@ orcl >grant FLASHBACK ANY TABLE to ogg; Grant succeeded. SYS@ orcl >grant EXECUTE on DBMS_FLASHBACK to ogg; Grant succeeded. SYS@ orcl >grant insert any table to ogg; Grant succeeded. SYS@ orcl >grant update any table to ogg; Grant succeeded. SYS@ orcl >grant delete any table to ogg; Grant succeeded. 或者 grant resource,connect,dba to ogg; 打开数据库的附加日志和 force log SYS@ orcl >select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database; NAME OPEN_MODE FOR SUPPLEME --------- -------------------- --- -------- ORCL READ WRITE NO YES SYS@ orcl >alter database force logging; Database altered. SYS@ orcl >alter database add SUPPLEMENTAL log data; Database altered. SYS@ orcl >select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database; NAME OPEN_MODE FOR SUPPLEME --------- -------------------- --- -------- ORCL READ WRITE YES YES SYS@ orcl >alter system switch logfile; ---运行 OGG 支持 DDL 脚本 如果要让 OGG 支持 DDL 操作，还需要额外运行几个脚本，这些脚本是 OGG 带的而不是 ORACLE 带的，源端与目标端都需要 ---SYS@ orcl >@/u01/app/ogg/marker_setup.sql ---SYS@ orcl >@/u01/app/ogg/ddl_setup.sql ---SYS@ orcl >@/u01/app/ogg/role_setup.sql ---SYS@ orcl >@/u01/app/ogg/ddl_enable.sql
ogg实例是刚搭建的新环境
实例ogg//开启归档，ogg方式同上 SYS@ ogg >archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 71 Current log sequence 73 SYS@ ogg >shutdown immediate; SYS@ ogg >startup mount; SYS@ ogg >alter database archivelog; SYS@ ogg >alter database open; SYS@ ogg >archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 71 Next log sequence to archive 73 Current log sequence 73 export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib export ORACLE_SID=ogg export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/jdk/bin:$PATH # source /home/ogg/.bash_profile create tablespace ogg datafile '/u01/app/oracle/oradata/ogg/ogg01.dbf' size 50m autoextend on; alias ggsci="/u01/app/ogg/ggsci"
4 开始进行搭建
源 orcl
1 创建 OGG 的管理目录
GGSCI (DSI) 1> create subdirs Creating subdirectories under current directory /u01/app/ogg Parameter files /u01/app/ogg/dirprm: already exists 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 Stdout files /u01/app/ogg/dirout: created
2 建立测试表 SYS@ ogg >@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlsampl.sql --实例orcl SYS@ orcl> conn scott/* Connected. SCOTT@ orcl >create table emp_ogg as select * from emp; Table created. SCOTT@ orcl >create table dept_ogg as select * from dept; Table created. SCOTT@ orcl >alter table EMP_OGG add constraint PK_EMPNO_OGG primary key (EMPNO); Table altered. SCOTT@ orcl >alter table DEPT_OGG add constraint PK_DEPTNO_OGG primary key (DEPTNO); Table altered. --实例ogg SYS@ ogg >conn scott/* Connected. SCOTT@ ogg > create table emp_ogg as select * from emp where 1=2; Table created. SCOTT@ ogg >create table dept_ogg as select * from dept where 1=2; Table created. SCOTT@ ogg >alter table EMP_OGG add constraint PK_EMPNO_OGG primary key (EMPNO); Table altered. SCOTT@ ogg >alter table DEPT_OGG add constraint PK_DEPTNO_OGG primary key (DEPTNO); Table altered.
3 源端添加表级 TRANDATA
GGSCI (DSI) 2> DBLOGIN USERID ogg, PASSWORD ogg Successfully logged into database. GGSCI (DSI) 3> add trandata scott.emp_ogg Logging of supplemental redo log data is already enabled for table SCOTT.EMP_OGG. GGSCI (DSI) 4> add trandata scott.dept_ogg Logging of supplemental redo log data is already enabled for table SCOTT.DEPT_OGG. GGSCI (DSI) 5> INFO TRANDATA scott.* Logging of supplemental redo log data is disabled for table SCOTT.BONUS. Logging of supplemental redo log data is disabled for table SCOTT.DEPT. Logging of supplemental redo log data is enabled for table SCOTT.DEPT_OGG. Columns supplementally logged for table SCOTT.DEPT_OGG: DEPTNO. Logging of supplemental redo log data is disabled for table SCOTT.EMP. Logging of supplemental redo log data is enabled for table SCOTT.EMP_OGG. Columns supplementally logged for table SCOTT.EMP_OGG: EMPNO. Logging of supplemental redo log data is disabled for table SCOTT.SALGRADE.
4 配置 MGR 管理进程
GGSCI (DSI) 6> edit params mgr PORT 7809 PURGEOLDEXTRACTS /u01/app/ogg/dirdat, USECHECKPOINTS 参数说明： PORT 7809： OGG 管理进程监控端口。 PURGEOLDEXTRACTS：清除不需要的 trail 文件。 /u01/app/ogg/dirdat： trail 文件存放位置。 USECHECKPOINTS：使用检查点队列 GGSCI (DSI) 7> start mgr Manager started. GGSCI (DSI) 8> info mgr Manager is running (IP port DSI.7809).
5 配置初始化数据进程
由于在创建测试表的时候，源端的测试表有数据，而目标端的测试表只有结构，没有数据，所以需要初始
化目标端的数据，所谓初始化，就是让目标端的数据和源端的数据在这个时间点是一模一样的， 所以初始化工
作并不需要一定使用 OGG，也可以使用 EXP、 EXPDP、 SQLLOAD 等其他工具
##使用 OGG 进行数据初始化， 下面在源端配置捕获进程 EINI_1
GGSCI (DSI) 9> ADD EXTRACT EINI_1, SOURCEISTABLE EXTRACT added. GGSCI (DSI) 10> INFO EXTRACT *, TASKS EXTRACT EINI_1 Initialized 2019-06-19 14:00 Status STOPPED Checkpoint Lag Not Available Log Read Checkpoint Not Available First Record Record 0 Task SOURCEISTABLE GGSCI (DSI) 11> EDIT PARAMS EINI_1 EXTRACT EINI_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg, PASSWORD ogg RMTHOST 1*, MGRPORT 7809 RMTTASK REPLICAT, GROUP RINI_1 TABLE scott.EMP_OGG; TABLE scott.DEPT_OGG; 参数介绍： EXTRACT EINI_1：说明这是 EXTRACT 进程，名字是 EINI_1 SETENV：环境变量，一定要设置和数据库字符集一样，否则可能会乱码 USERID：数据库 OGG 用户 PASSWORD：数据库用户 OGG 的密码 RMTHOST：目标端地址，如果在/etc/hosts 文件里已经设置解析，可以写主机名 MGRPORT：目标端 MGR 管理进程监听的端口 RMTTASK REPLICAT：目标端 REPLICAT 应用进程的组和名字 TABLE：源端要初始化数据的表的名字
编辑好捕获进程 EINI_1 后， 还需要在目标端配置 REPLICAT 应用进程，名字要和源端的捕获进程 EINI_1 里面 RMTTASK REPLICAT 参数配置的一样，
也就是还需要在目标端配置 RMTTASK REPLICAT RINI_1
目 ogg [oracle@ogg ~]$ alias ggsci="/u01/app/ogg/ggsci" [oracle@ogg ~]$ ggsci GGSCI (ogg) 1> create subdirs Creating subdirectories under current directory /u01/app/ogg Parameter files /u01/app/ogg/dirprm: already exists 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 Stdout files /u01/app/ogg/dirout: created 目标端 GGSCI (ogg) 2> edit params mgr PORT 7809 PURGEOLDEXTRACTS /u01/app/ogg/dirdat, USECHECKPOINTS GGSCI (ogg) 3> start mgr Manager started. GGSCI (ogg) 4> info mgr Manager is running (IP port ogg.7809).
7 配置目标端 REPLICAT 进程
GGSCI (ogg) 5> EDIT PARAMS RINI_1 REPLICAT RINI_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) ASSUMETARGETDEFS USERID ogg, PASSWORD ogg DISCARDFILE /u01/app/ogg/dirrpt/RINIaa.dsc, PURGE MAP scott.*, TARGET scott.*; 参数介绍： REPLICAT RINI_1：说明这是 REPLICAT 应用进程，名字叫 RINI_1 SETENV：语言变量，同捕获进程 EINI_1 ASSUMETARGETDEFS：告诉 OGG 目标端和源端需要同步的表的结构完全一致，不需要 OGG 去检查表的结构，包括 表名、字段名、字段类型、字段长度等，如果目标端和源端同步的表的结构不一样，需要使用 SOURCEDEFS 参 数，详见 OGG 官方文档。 USERID、 PASSWORD：同捕获进程 EINI_1 参数介绍 DISCARDFILE： 错误信息存放位置及命名规则 MAP：源端捕获的表的名字 TARGET：目标端同步的表的名字，可以不在同一 SCHEMA。
8 初始化数据
配置好目标端的应用进程 RINI_1 后， 就可以启动源端的捕获进程进行捕获数据了，而目标端的应用进程 RINI_1 不需要手动去启动，也就是说目标端 RINI_1 进程不需要管
GGSCI (DSI) 12> START EXTRACT EINI_1 Sending START request to MANAGER ... EXTRACT EINI_1 starting GGSCI (DSI) 16> VIEW REPORT EINI_1 2019-06-19 14:09:12 WARNING OGG-01194 EXTRACT task RINI_1 abended : Could not open checkpoint file /u01/app/ogg/dirchk/RINI_1.cpr, mode 1 (error 2, No such file --目标端错误日志 [root@ogg ogg]# more ggserr.log | grep ERROR 2019-06-19 14:09:15 ERROR OGG-00446 Oracle GoldenGate Delivery for Oracle, rini_1.prm: Could not open checkpoint file /u01/app/ogg/dirchk/RINI_1.cpr, mode 1 (error 2, No such file or directory). ###在目标端执行 GGSCI (ogg) 9> add replicat RINI_1,specialrun REPLICAT added. ##在执行 GGSCI (DSI) 19> START EXTRACT EINI_1 Sending START request to MANAGER ... EXTRACT EINI_1 starting GGSCI (DSI) 22> VIEW REPORT EINI_1 Report at 2019-06-19 14:18:35 (activity since 2019-06-19 14:18:28) Output to RINI_1: From Table SCOTT.EMP_OGG: # inserts: 14 # updates: 0 # deletes: 0 # discards: 0 REDO Log Statistics Bytes parsed 0 Bytes output 2616
9 验证初始化数据
###目标库已经有了数据 SCOTT@ ogg >set linesize 1000 SCOTT@ ogg >select * from emp_ogg; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10
初始化之后，上文提到的初始化进程（EINI_1、 RINI_1）自动停止， 因为通常情况下初始化数据工作只会做一次。 可以通过 INFO 命令查看进程的状态
源端： GGSCI (DSI) 23> INFO EXTRACT EINI_1 EXTRACT EINI_1 Last Started 2019-06-19 14:18 Status STOPPED Checkpoint Lag Not Available Log Read Checkpoint Table SCOTT.EMP_OGG 2019-06-19 14:18:34 Record 14 Task SOURCEISTABLE GGSCI (ogg) 10> INFO REPLICAT RINI_1 REPLICAT RINI_1 Initialized 2019-06-19 14:18 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:04:23 ago) Log Read Checkpoint Not Available Task SPECIALRUN
10 源端和目标端配置 OGG 的检查点
此步骤不是必须的，但是为了让 OGG 网络中断、服务器宕机、掉电等在突发情况也能正确断点续传， ORACLE 建议配置 OGG 的检查点队列。
源端和目标端都需配置
GGSCI (DSI) 24> EDIT PARAMS ./GLOBALS CHECKPOINTTABLE ogg.ggschkpt GGSCI (DSI) 25> ADD CHECKPOINTTABLE ERROR: Missing checkpoint table specification. GGSCI (DSI) 26> exit [oracle@DSI ogg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (DSI) 1> dblogin userid ogg,password ogg Successfully logged into database. GGSCI (DSI) 2> ADD CHECKPOINTTABLE No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)... Successfully created checkpoint table ogg.ggschkpt. SYS@ orcl >conn ogg/ogg Connected. OGG@ orcl >select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BIN$i6WDddFkJD/gUxkHDwrYjw==$0 TABLE BIN$i6WDddFsJD/gUxkHDwrYjw==$0 TABLE GGSCHKPT TABLE GGSCHKPT_LOX TABLE
11 源端配置捕获进程
GGSCI (DSI) 3> EDIT PARAMS EORA_1 EXTRACT EORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg, PASSWORD ogg EXTTRAIL /u01/app/ogg/dirdat/aa TABLE scott.EMP_OGG; TABLE scott.DEPT_OGG; ##EXTTRAIL 参数是 TRAIL 队列文件存放的路径和命名格式， TRAIL 文件可以理解为存放捕获进程捕获的日志文件。 此时还需要将捕获进程 EORA_1 添加到 OGG GGSCI (DSI) 4> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW EXTRACT added. GGSCI (DSI) 5> ADD EXTTRAIL /u01/app/ogg/dirdat/aa, EXTRACT EORA_1, MEGABYTES 5 EXTTRAIL added. ##上面的两个命名告诉 OGG，捕获进程从启动起开始捕获，捕获数据保存到 TRAIL 文件，及 TRAIL 文件的路径、命名格式，单个 TRAIL 文件最大大小 GGSCI (DSI) 6> START EXTRACT EORA_1 Sending START request to MANAGER ... EXTRACT EORA_1 starting GGSCI (DSI) 7> INFO EXTRACT EORA_1 EXTRACT EORA_1 Last Started 2019-06-19 14:57 Status RUNNING Checkpoint Lag 00:01:37 (updated 00:00:06 ago) Log Read Checkpoint Oracle Redo Logs 2019-06-19 14:56:08 Seqno 23, RBA 728592 SCN 0.0 (0)
12 源端配置 PUMP 传输进程
##此步骤也是非必须的，如果不配置传输进程， OGG 会通过 EXTRACT 进程传输 TRAIL 队列文件，但是和检查点队列一样，为了保证断点续传 ORACLE 建议配置 PUMP 传输进程
GGSCI (DSI) 8> EDIT PARAMS PORA_1 EXTRACT PORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA,AL32UTF8) PASSTHRU RMTHOST *, MGRPORT 7809 RMTTRAIL /u01/app/ogg/dirdat/pa TABLE scott.EMP_OGG; TABLE scott.DEPT_OGG; ##添加 PUMP 进程 PORA_1 到 OGG，并指定本地的 TRAIL 文件。 GGSCI (DSI) 9> ADD EXTRACT PORA_1, EXTTRAILSOURCE /u01/app/ogg/dirdat/aa EXTRACT added. GGSCI (DSI) 10> INFO EXTRACT PORA_1 EXTRACT PORA_1 Initialized 2019-06-19 15:00 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:00:17 ago) Log Read Checkpoint File /u01/app/ogg/dirdat/aa000000 First Record RBA 0 ##为 PUMP 进程 PORA_1 指定将本地 TRAIL 文件传输到目标端后保存成目标端 TRAIL 文件的名字。 GGSCI (DSI) 11> ADD RMTTRAIL /u01/app/ogg/dirdat/pa, EXTRACT PORA_1, MEGABYTES 5 RMTTRAIL added. GGSCI (DSI) 12> START EXTRACT PORA_1 Sending START request to MANAGER ... EXTRACT PORA_1 starting GGSCI (DSI) 13> INFO EXTRACT PORA_1 EXTRACT PORA_1 Last Started 2019-06-19 15:02 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:01 ago) Log Read Checkpoint File /u01/app/ogg/dirdat/aa000000 First Record RBA 0 GGSCI (DSI) 14> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EORA_1 00:00:00 00:00:08 EXTRACT RUNNING PORA_1 00:00:00 00:00:07 此时在目标端/u01/app/ogg/dirdat/目录下，就会看到由源端 PUMP 进程 PORA_1 传输过来的 TRAIL 文件 [root@ogg ogg]# cd /u01/app/ogg/dirdat/ [root@ogg dirdat]# ll /u01/app/ogg/dirdat/ total 0 -rw-rw-rw- 1 oracle oinstall 0 Jun 19 15:02 pa000000
13 目标端配置同步进程 RORA_1
GGSCI (ogg) 3> ADD REPLICAT RORA_1,SPECIALRUN REPLICAT added. GGSCI (ogg) 4> EDIT PARAMS RORA_1 REPLICAT RORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg, PASSWORD ogg HANDLECOLLISIONS ASSUMETARGETDEFS DISCARDFILE /u01/app/ogg/dirrpt/RORA_aa.DSC, PURGE MAP scott.emp_ogg, TARGET scott.emp_ogg; MAP scott.dept_ogg, TARGET scott.dept_ogg; GGSCI (ogg) 5> START REPLICAT RORA_1 Sending START request to MANAGER ... REPLICAT RORA_1 starting GGSCI (ogg) 6> INFO REPLICAT RORA_1 REPLICAT RORA_1 Initialized 2019-06-19 15:04 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:01:54 ago) Log Read Checkpoint Not Available Task SPECIALRUN GGSCI (ogg) 8> view report RORA_1 2019-06-19 15:06:07 ERROR OGG-00446 Invalid data source -1 in checkpoint file /u01/app/ogg/dirchk/RORA_1.cpr. ##源库错误日志 [root@ogg dirdat]# more ../ggserr.log | grep ERROR 2019-06-19 15:12:52 ERROR OGG-00446 Oracle GoldenGate Delivery for Oracle, rora_1.prm: Invalid data source -1 in checkpoint file /u01/app/ogg/dirchk/RORA_1.cpr. 2019-06-19 15:12:52 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rora_1.prm: PROCESS ABENDING. ##修改目标端RORA_1 GGSCI (ogg) 33> view param RORA_1 REPLICAT RORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg, PASSWORD ogg HANDLECOLLISIONS ASSUMETARGETDEFS DISCARDFILE /u01/app/ogg/dirrpt/RORA_1.DSC, APPEND, MEGABYTES 1000 MAP scott.emp_ogg, TARGET scott.emp_ogg; MAP scott.dept_ogg, TARGET scott.dept_ogg; GGSCI (ogg) 28> delete RORA_1 Deleted REPLICAT RORA_1. GGSCI (ogg) 29> add REPLICAT RORA_1,exttrail /u01/app/ogg/dirdat/pa,checkpointtable ogg.ggschkpt REPLICAT added. GGSCI (ogg) 30> start RORA_1 Sending START request to MANAGER ... REPLICAT RORA_1 starting GGSCI (ogg) 32> info RORA_1 REPLICAT RORA_1 Last Started 2019-06-19 15:42 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:06 ago) Log Read Checkpoint File /u01/app/ogg/dirdat/pa000000 First Record RBA 0
14 插入数据测试
OGG@ orcl >conn scott/tiger Connected. SCOTT@ orcl >insert into dept_ogg select * from dept; 4 rows created. SCOTT@ orcl >commit; Commit complete. 源端 GGSCI (DSI) 17> stats PORA_1 Sending STATS request to EXTRACT PORA_1 ... Start of Statistics at 2019-06-19 15:46:37. Output to /u01/app/ogg/dirdat/pa: Extracting from SCOTT.DEPT_OGG to SCOTT.DEPT_OGG: *** Total statistics since 2019-06-19 15:45:59 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 4.00 *** Daily statistics since 2019-06-19 15:45:59 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 4.00 *** Hourly statistics since 2019-06-19 15:45:59 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 4.00 *** Latest statistics since 2019-06-19 15:45:59 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 4.00 End of Statistics. 目标端 GGSCI (ogg) 34> stats RORA_1 Sending STATS request to REPLICAT RORA_1 ... Start of Statistics at 2019-06-19 15:47:26. Replicating from SCOTT.DEPT_OGG to SCOTT.DEPT_OGG: *** Total statistics since 2019-06-19 15:46:07 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 4.00 *** Daily statistics since 2019-06-19 15:46:07 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 4.00 *** Hourly statistics since 2019-06-19 15:46:07 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 4.00 *** Latest statistics since 2019-06-19 15:46:07 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 4.00 End of Statistics. 数据查询 OGG@ ogg >conn scott/tiger Connected. SCOTT@ ogg >select * from dept_ogg; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
更新测试
SYS@ orcl >conn scott/* Connected. SCOTT@ orcl >update emp_ogg set ename='hongquan' where empno=7698; 1 row updated. SCOTT@ orcl >commit; Commit complete. SCOTT@ ogg >select * from emp_ogg where empno=7698; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7698 hongquan MANAGER 7839 01-MAY-81 2850 30
至此 简单的单实例单向同步已经完成
注明：此文档跟网上有些文档类似，因为临时的原因，在我以前下载的资料里面找的pdf进行测试，发现是一个很早的pdf，找不到原文链接---
这里的实验测试完全真实有效~~
后面会测试：修改表的同步，新增表的同步，双向同步，以及切换等
其他mgr，等一些命令参数，官网或者网上有大部分的资料，这里不做列举，因为还没研究这些（~~~）