18c ogg oracle-oracle安装手册
目 录
第一章 概述
1.1 实验内容
第二章 实验内容
2.1 前言
2.2 软件安装
2.2.1 软件下载
2.2.2 解压介质
2.3 数据库准备
2.3.1 数据库环境准备
2.3.2 创建ogg管理用户
2.4 ogg配置
2.4.1 附加日志
2.4.2 源端目标端GLOBAL全局参数
2.4.3 源端目标端mgr进程
2.4.4 源端Extract进程
2.4.5 源端Pump投递进程
2.4.6 数据初始化
2.4.7 目标端checkpoint
2.4.8 目标端rep进程
2.5 同步验证
2.6 同步失败解决
2.7 问题
2.7.1 q1：12c dblogin和pdb问题
2.7.2 q2：能不能一个rep进程应用多次表变更
第三章 参考文档
第一章 概述
ogg安装和同步测试。
1.1 实验目标
1.安装ogg。
2.实现源端到目标端的表同步
第二章 实验内容
2.1 前言
版本信息：
实验环境 软件版本
操作系统 Rhel 6.5
数据库 18c
ogg 18.1.0.0.0
实验目标为将源端的表同步到目标端，当前源端和目标端是同一个环境，也就是本地同步到本地，但是为了理解ogg的同步过程，下面的信息中还是以源端、目标端来表述。
2.2 软件安装
2.2.1 软件下载
2.2.2 解压介质
1. 上传介质
2. 更改属组、解压
mkdir -p /ogg/ogg
cd /u01/software
chown oracle:oinstall 181000_fbo_ggs_Linux_x64_shiphome.zip
chmod 755 181000_fbo_ggs_Linux_x64_shiphome.zip
unzip 181000_fbo_ggs_Linux_x64_shiphome.zip
3. 添加环境变量
export OGG_HOME=/ogg/ogg
LD_LIBRARY_PATH= $ORACLE_HOME/lib:$ORACLE_HOME/lib32:$OGG_HOME:$ORA_CRS_HOME/lib32:/lib/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
4. runInstaller
[[email protected] Disk1]$ pwd
/u01/software/fbo_ggs_Linux_x64_shiphome/Disk1
[[email protected] Disk1]$ ./runInstaller
5. 使用ggsci创建工作目录
cd /ogg/ogg
./ggsci
create subdirs
2.3 数据库准备
2.3.1 数据库环境准备
1. 源端开启归档
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/soft/dbs/arch
Oldest online log sequence 14
Current log sequence 16
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 859829344 bytes
Fixed Size 8901728 bytes
Variable Size 679477248 bytes
Database Buffers 167772160 bytes
Redo Buffers 3678208 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
2. 源端打开附加日志
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
SQL> alter database force logging;
Database altered.
SQL> alter database add supplemental log data;
Database altered.
3. 参数
alter system set enable_goldengate_replication=true;
4. tns
lzlpdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.100.177 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lzl)
)
)
lzlcdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.100.177 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lzl1)
)
)
2.3.2 创建ogg管理用户
1. 创建数据库用户ggs（源端目标端都要建立）(cdb执行)
create tablespace goldengate datafile size 8M autoextend on;
create user c##ggs identified by "oracle" default tablespace goldengate;
2. ggs用户权限
--在pdb中给dba权限（我cdb和pdb都给了）
SQL> grant dba to c##ggs;
--exec dbms_goldengate_auth.grant_admin_privilege('c##ggs');
2.4 ogg配置
2.4.1 附加日志
GGSCI (18c as c##[email protected]/LZL) 73> dblogin userid c##[email protected],password oracle
Successfully logged into database LZL.
GGSCI (18c as c##[email protected]/LZL) 75> add trandata test.tab1
2019-02-24 22:57:06 WARNING OGG-06439 No unique key is defined for table TAB1. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2019-02-24 22:57:06 INFO OGG-15130 No key found for table LZL.TEST.TAB1. All viable columns will be logged.
2019-02-24 22:57:06 INFO OGG-15132 Logging of supplemental redo data enabled for table LZL.TEST.TAB1.
2019-02-24 22:57:07 INFO OGG-15133 TRANDATA for scheduling columns has been added on table LZL.TEST.TAB1.
2019-02-24 22:57:07 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table LZL.TEST.TAB1.
2019-02-24 22:57:08 INFO OGG-10471 ***** Oracle Goldengate support information on table TEST.TAB1 *****
Oracle Goldengate support native capture on table TEST.TAB1.
Oracle Goldengate marked following column as key columns on table TEST.TAB1: COL1
No unique key is defined for table TEST.TAB1.
2.4.2 源端目标端GLOBAL全局参数
--param最后一行加分号，命令行最后不加分号
GGSCI (18c) 5> edit param ./GLOBALS
GGSCI (18c) 6> view param ./GLOBALS
GGSCHEMA c##ggs
2.4.3 源端目标端mgr进程
源端mgr
PORT 7809
DYNAMICPORTLIST 7850-7908
purgeoldextracts ./dirdat/*, usecheckpoints, MINKEEPDAYS 4
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 4
AUTORESTART EXTRACT *, WAITMINUTES 2, RETRIES 20, RESETMINUTES 60
目标端mgr
PORT 7809
DYNAMICPORTLIST 7850-7908
purgeoldextracts ./dirdat/*, usecheckpoints, MINKEEPDAYS 4
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 4
AUTORESTART REPLICAT *, WAITMINUTES 2, RETRIES 20, RESETMINUTES 60
因为是一个端，mgr配置如下
PORT 7809
DYNAMICPORTLIST 7850-7908
purgeoldextracts ./dirdat/*, usecheckpoints, MINKEEPDAYS 4
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 4
AUTORESTART EXTRACT *, WAITMINUTES 2, RETRIES 20, RESETMINUTES 60
AUTORESTART REPLICAT *, WAITMINUTES 2, RETRIES 20, RESETMINUTES 60
启动mgr
GGSCI (18c) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (18c) 8> start mgr
Manager started.
GGSCI (18c) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
2.4.4 源端Extract进程
GGSCI (18c) 11> view params extlzl
extract extlzl
dynamicresolution
userid c##[email protected],password oracle
setenv(ORACLE_SID=lzl1)
exttrail ./dirdat/ex
SOURCECATALOG lzlpdb
table test.tab1
-- 必须连到cdb，否则报错OGG-06206 The database connection must be to the root level database for user c##ggs.
--添加extract进程
GGSCI (18c) 13> add extract extlzl,integrated tranlog,begin now
EXTRACT (Integrated) added.
--pdbmode数据库必须是集成模式，OGG-06220 Classic Extract does not support multitenant container databases.
--注册pdb信息
GGSCI (18c) 16> dblogin userid c##[email protected],password oracle
Successfully logged into database CDB$ROOT.
GGSCI (18c as c##[email protected]/CDB$ROOT) 17> register extract extlzl database container (lzl)
2019-02-24 21:49:55 INFO OGG-02003 Extract EXTLZL successfully registered with database at SCN 2803139.
--创建本地trail文件
GGSCI (18c) 14> add exttrail ./dirdat/ex,extract extlzl
EXTTRAIL added.
Start extlzl
2.4.5 源端Pump投递进程
GGSCI (18c) 18> view params dplzl
extract dplzl
dynamicresolution
userid ggs,password oracle
rmthost 172.17.100.177,mgrport 7809,compress
rmttrail ./dirdat/dp
SOURCECATALOG lzlpdb
table test.tab1;
GGSCI (18c) 19> add extract dplzl,exttrailsource ./dirdat/ex
EXTRACT added.
GGSCI (18c) 22> add rmttrail ./dirdat/dp,extract dplzl
RMTTRAIL added.
GGSCI (18c as c##[email protected]/LZL) 78> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPLZL 00:00:00 00:00:00
EXTRACT RUNNING EXTLZL 00:00:00 00:00:02
2.4.6 数据初始化
--基于scn号的数据泵初始化
--查询scn
SELECT
TO_CHAR(MIN(start_scn) ) AS "Please select the minimum SCN"
FROM
v$transaction
UNION ALL
SELECT
TO_CHAR(current_scn)
FROM
v$database;
Please select the minimum SCN
----------------------------------------
2848351
2 rows selected.
--导出
expdp system/[email protected] directory=dir1 parallel=1 cluster=n compression=ALL FLASHBACK_SCN=2848351 dumpfile=ogg_test_tab1.dmp logfile=ogg_test_tab1.log tables=test.tab1
--导入
impdp test2/[email protected] directory=dir1 dumpfile=ogg_test_tab1.dmp logfile=ogg_test_tab1_imp.log remap_schema=test:test2
2.4.7 目标端checkpoint
GGSCI (18c as c##[email protected]/CDB$ROOT) 70> dblogin userid c##[email protected],password oracle
Successfully logged into database LZL.
GGSCI (18c as c##[email protected]/LZL) 71> add checkpointtable c##ggs.CKPT
Logon catalog name LZL will be used for table specification LZL.c##ggs.CKPT.
Successfully created checkpoint table LZL.c##ggs.CKPT.
2.4.8 目标端rep进程
GGSCI (18c) 6> view param replzl
replicat replzl
setenv(ORACLE_SID=lzl1)
userid c##[email protected],password oracle
assumetargetdefs
reperror default,discard
discardfile ./dirrpt/replzl.dsc,append,megabytes 50
dynamicresolution
SOURCECATALOG lzlpdb
map test.tab1,target test2.tab1;
map test.tab1,target test.tab2;
-- rep不能登录cdb，否则报错OGG-02661 Replicat is not allowed to log on to the root level database.
GGSCI (18c) 5> add replicat replzl integrated,exttrail ./dirdat/dp
REPLICAT (Integrated) added.
add replicat replzl,exttrail ./dirdat/dp,checkpointtable c##ggs.ckpt
GGSCI (18c as c##[email protected]/LZL) 101> start replzl
Sending START request to MANAGER ...
REPLICAT REPLZL starting
GGSCI (18c as c##[email protected]/LZL) 106> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPLZL 00:00:00 00:00:04
EXTRACT RUNNING EXTLZL 00:00:00 00:00:02
REPLICAT RUNNING REPLZL 00:00:00 00:00:07
2.5 同步验证
SQL> insert into test.tab1 values(1);
1 row created.
Elapsed: 00:00:00.04
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> select * from test.tab1;
COL1
----------
1
1 row selected.
Elapsed: 00:00:00.01
SQL> select * from test.tab2;
no rows selected
Elapsed: 00:00:00.01
SQL> select * from test2.tab1;
no rows selected
--info all
GGSCI (18c as c##[email protected]/LZL) 138> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPLZL 00:00:00 00:00:04
EXTRACT RUNNING EXTLZL 00:00:00 00:00:01
REPLICAT RUNNING REPLZL 00:00:00 00:00:09
--extlzl状态
GGSCI (18c as c##[email protected]/LZL) 121> stats extlzl table test.tab1
Sending STATS request to EXTRACT EXTLZL ...
No active extraction maps.
--trandata 信息
GGSCI (18c as c##[email protected]/LZL) 12> info trandata test.tab1
2019-02-25 09:25:27 INFO OGG-10471 ***** Oracle Goldengate support information on table TEST.TAB1 *****
Oracle Goldengate support native capture on table TEST.TAB1.
Oracle Goldengate marked following column as key columns on table TEST.TAB1: COL1
No unique key is defined for table TEST.TAB1.
Logging of supplemental redo log data is enabled for table LZL.TEST.TAB1.
Columns supplementally logged for table LZL.TEST.TAB1: "COL1".
Prepared CSN for table LZL.TEST.TAB1: 2886444
2.6 同步失败解决
extract:
extract extlzl
userid c##ggs,password oracle
GETUPDATEBEFORES
GETTRUNCATES
BR BRINTERVAL 2H
CACHEMGR CACHESIZE 500MB
WARNLONGTRANS 2H,CHECKINTERVAL 5M
NUMFILES 4000
EOFDELAYCSECS 10
LOGALLSUPCOLS
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 2)
discardfile ./dirrpt/jcms.dsc,append, megabytes 200
exttrail ./dirdat/ex
table lzl.test.tab1;
datapump:
extract dplzl
dynamicresolution
userid c##ggs,password oracle
rmthost 172.17.100.177,mgrport 7809,compress
rmttrail ./dirdat/dp
table lzl.test.tab1;
replicat:
replicat replzl
userid c##[email protected],password oracle
assumetargetdefs
reperror default,discard
discardfile ./dirrpt/replzl.dsc,append,megabytes 50
map lzl.test.tab1,target lzl.test2.tab1;
同步验证：(info all,stats <>都ok)
GGSCI (18c as c##[email protected]/LZL) 113> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPLZL 00:00:00 00:00:00
EXTRACT RUNNING EXTLZL 00:00:00 00:00:01
REPLICAT RUNNING REPLZL 00:00:00 00:00:01
GGSCI (18c as c##[email protected]/LZL) 114> stats extlzl
Sending STATS request to EXTRACT EXTLZL ...
s
Start of Statistics at 2019-02-25 11:04:34.
Output to ./dirdat/ex:
Extracting from LZL.TEST.TAB1 to LZL.TEST.TAB1:
*** Total statistics since 2019-02-25 10:43:33 ***
Total inserts 3.00
Total updates 4.00
Total befores 4.00
Total deletes 0.00
Total discards 0.00
Total operations 7.00
*** Daily statistics since 2019-02-25 10:43:33 ***
Total inserts 3.00
Total updates 4.00
Total befores 4.00
Total deletes 0.00
Total discards 0.00
Total operations 7.00
*** Hourly statistics since 2019-02-25 10:43:33 ***
Total inserts 3.00
Total updates 4.00
Total befores 4.00
Total deletes 0.00
Total discards 0.00
Total operations 7.00
*** Latest statistics since 2019-02-25 10:43:33 ***
Total inserts 3.00
Total updates 4.00
Total befores 4.00
Total deletes 0.00
Total discards 0.00
Total operations 7.00
End of Statistics.
GGSCI (18c as c##[email protected]/LZL) 115>stats dplzl
Sending STATS request to EXTRACT DPLZL ...
Start of Statistics at 2019-02-25 11:04:44.
Output to ./dirdat/dp:
Extracting from LZL.TEST.TAB1 to LZL.TEST.TAB1:
*** Total statistics since 2019-02-25 10:46:23 ***
Total inserts 2.00
Total updates 4.00
Total befores 4.00
Total deletes 0.00
Total discards 0.00
Total operations 6.00
*** Daily statistics since 2019-02-25 10:46:23 ***
Total inserts 2.00
Total updates 4.00
Total befores 4.00
Total deletes 0.00
Total discards 0.00
Total operations 6.00
*** Hourly statistics since 2019-02-25 10:46:23 ***
Total inserts 2.00
Total updates 4.00
Total befores 4.00
Total deletes 0.00
Total discards 0.00
Total operations 6.00
*** Latest statistics since 2019-02-25 10:46:23 ***
Total inserts 2.00
Total updates 4.00
Total befores 4.00
Total deletes 0.00
Total discards 0.00
Total operations 6.00
End of Statistics.
GGSCI (18c as c##[email protected]/LZL) 116> stats replzl
Sending STATS request to REPLICAT REPLZL ...
Start of Statistics at 2019-02-25 11:04:59.
Integrated Replicat Statistics:
Total transactions 2.00
Redirected 0.00
Replicated procedures 0.00
DDL operations 0.00
Stored procedures 0.00
Datatype functionality 0.00
Event actions 0.00
Direct transactions ratio 0.00%
Replicating from LZL.TEST.TAB1 to LZL.TEST2.TAB1:
*** Total statistics since 2019-02-25 10:52:36 ***
Total inserts 1.00
Total updates 1.00
Total deletes 0.00
Total discards 3.00
Total operations 2.00
*** Daily statistics since 2019-02-25 10:52:36 ***
Total inserts 1.00
Total updates 1.00
Total deletes 0.00
Total discards 3.00
Total operations 2.00
*** Hourly statistics since 2019-02-25 11:00:00 ***
No database operations have been performed.
*** Latest statistics since 2019-02-25 10:52:36 ***
Total inserts 1.00
Total updates 1.00
Total deletes 0.00
Total discards 3.00
Total operations 2.00
End of Statistics.
注意：stats不一定代表有问题，比如
上面可以代表ext进程在工作了，但是下面的
ext没有工作，可能有问题，也可能是因为没有dml变更，所以还是要看数据是否传过去了
Dml验证
SQL> update test.tab1 set COL1=111 where col1=888;
4 rows updated.
SQL> commit;
Commit complete.
SQL> select * from TEST2.TAB1;
COL1
----------
888
SQL> select * from TEST2.TAB1;
COL1
----------
111
2.7 问题
2.7.1 q1：12c dblogin和pdb问题
dblogin登陆cdb可以
userid c##ggs,password oracle
或者
userid c##[email protected],password oracle
指定pdb可以用sourcecatalog
sourcecatalog lzlpdb
或者不用courcecatalog参数，在table中指定
table lzl.test.tab1;
讲道理，两种写法都可以，但是我上面的问题就是第一种写法没有成功，换了种写法就可以。
测试：
添加ext进程
extract extlzl1
userid c##[email protected],password oracle
GETUPDATEBEFORES
GETTRUNCATES
BR BRINTERVAL 2H
CACHEMGR CACHESIZE 500MB
WARNLONGTRANS 2H,CHECKINTERVAL 5M
NUMFILES 4000
EOFDELAYCSECS 10
LOGALLSUPCOLS
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 2)
Sourcecatalog lzlpdb
discardfile ./dirrpt/disl.dsc,append, megabytes 200
exttrail ./dirdat/xe
table test.tab2;
add extract extlzl1,integrated tranlog,begin now
register extract extlzl1 database container (lzl)
add exttrail ./dirdat/xe,extract extlzl1
dblogin userid c##[email protected],password oracle
add trandata test.tab2
状态
GGSCI (18c as c##[email protected]/LZL) 188> stats extlzl1
Sending STATS request to EXTRACT EXTLZL1 ...
2019-02-25 14:30:43 ERROR OGG-15163 There was a problem sending a message to EXTRACT EXTLZL1 (Connection reset by peer).
GGSCI (18c as c##[email protected]/LZL) 189> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPLZL 00:00:00 00:00:05
EXTRACT RUNNING EXTLZL 00:00:00 00:00:06
EXTRACT ABENDED EXTLZL1 00:02:36 00:00:45
REPLICAT RUNNING REPLZL 00:00:00 00:00:04
还是不行
2.7.2 q2：能不能一个rep进程应用多次表变更
比如rep进程map写法如下
map lzl.test.tab1,target lzl.test2.tab1;
map lzl.test.tab1,target lzl.test.tab2;
tab2表初始化
impdp test/[email protected] directory=dir1 dumpfile=ogg_test_tab1.dmp TABLE_EXISTS_ACTION=replace logfile=ogg_test_tab1_imp.log remap_table=tab1:tab2
[[email protected] trace]$ impdp test/[email protected] directory=dir1 dumpfile=ogg_test_tab1.dmp TABLE_EXISTS_ACTION=replace logfile=ogg_test_tab1_imp.log remap_table=tab1:tab2
Import: Release 18.0.0.0.0 - Production on Mon Feb 25 15:21:19 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "TEST"."SYS_IMPORT_FULL_04" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_04": test/********@lzlpdb directory=dir1 dumpfile=ogg_test_tab1.dmp TABLE_EXISTS_ACTION=replace logfile=ogg_test_tab1_imp.log remap_table=tab1:tab2
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."TAB2" 5.039 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT:"TEST"."GGS_74580" failed to create with error:
ORA-30567: name already used by an existing log group
Failing sql is:
ALTER TABLE "TEST"."TAB2" ADD SUPPLEMENTAL LOG GROUP "GGS_74580" ("COL1") ALWAYS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST"."SYS_IMPORT_FULL_04" completed with 1 error(s) at Mon Feb 25 15:21:56 2019 elapsed 0 00:00:22
因为是同一个库，所以存在同名的情况（附加日志），上面导入是成功了，但是可能是附加日志没有生成（maybe）(dba_constraints查不到该约束)
stop replzl
start replzl
replzl hang住，kill杀掉replzl
应该是可以的，没做出来，目前没查出原因
第三章 参考文档
GoldenGate (OGG) Master Note References to Documentation, Tutorials and Procedures(Doc ID 1313280.1）