mysql 1054 ogg_oracle 19c ogg安装 window

tip：window要把防火墙端口 7809-7900开放 用户数据同步

1.软件安装

1.1 选择相应数据库的版本号

1.2 选中安装目录

1.3 点击安装

1.4 安装完成

1.使用ggsci创建工作目录

Microsoft Windows [版本 10.0.17763.107]

(c) 2018 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>g:

G:\>cd ogg19c

G:\ogg19c>ggsci.exe

Oracle GoldenGate Command Interpreter for Oracle

Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO

Windows x64 (optimized), Oracle 19c on Oct 19 2019 22:34:39

Operating system character set identified as GBK.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

GGSCI (WIN-GMCTQMCTAGI) 1> create subdirs

Creating subdirectories under current directory G:\ogg19c

Parameter file G:\ogg19c\dirprm: already exists.

Report file G:\ogg19c\dirrpt: already exists.

Checkpoint file G:\ogg19c\dirchk: already exists.

Process status files G:\ogg19c\dirpcs: already exists.

SQL script files G:\ogg19c\dirsql: already exists.

Database definitions files G:\ogg19c\dirdef: already exists.

Extract data files G:\ogg19c\dirdat: already exists.

Temporary files G:\ogg19c\dirtmp: already exists.

Credential store files G:\ogg19c\dircrd: already exists.

Masterkey wallet files G:\ogg19c\dirwlt: already exists.

Dump files G:\ogg19c\dirdmp: already exists.

找不到MSVCR120.dll,无法继续执行代码.重新安装程序可能会解决此问题。

请下载安装文件

2. 数据库环境准备

2.1.以超级管理员登录数据库

sqlplus sys/System123 as sysdba

2.2.    源端开启归档

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.3.    源端打开附加日志

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.

2.4.    参数

SQL> alter system set enable_goldengate_replication=true scope=both sid='*';

2.5.    tns

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

ORCL_PDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orclpdb)

)

)

*

如果碰到

1.第 1 行出现错误:

ORA-01126: 数据库必须已装载到此实例并且不在任何实例中打开

shutdown immediate

startup mount

先关闭数据库在启动

2.

第 1 行出现错误:

ORA-01109: 数据库未打开

alter database open;

3.创建ogg管理用户

分为管理用户c##ggs(c##ggs)和同步数据用户 bing(bing)

show con_name；查询当前的容器

3.1.    创建数据库用户c##ggs(源端目标端都要建立)(cdb执行)

alter session set container=CDB$ROOT;

create tablespace goldengatecdb datafile 'G:\app\oradata\ORCL\goldengatecbb.dbf' size 8M autoextend on;

create user c##ggs identified by password default tablespace goldengatecdb;

3.2.    创建数据库同步用户bing(源端目标端都要建立)(pdb执行)

alter session set container=ORCLPDB;

create tablespace bingdb datafile 'G:\app\oradata\ORCL\bingdb.dbf' size 8M autoextend on;

create user bing identified by password default tablespace bingdb;

3.3. c##ggs用户权限

--在pdb和cdb中给dba权限

SQL> alter session set container=CDB$ROOT;

SQL> grant dba to  c##ggs;

SQL>exec dbms_goldengate_auth.grant_admin_privilege('c##ggs');

SQL> alter session set container=ORCLPDB;

SQL> grant dba to  c##ggs;

SQL>exec dbms_goldengate_auth.grant_admin_privilege('c##ggs');

SQL> alter session set container=ORCLPDB;

SQL> grant create session  bing;

SQL>grant connect,resource,dba,CTXAPP,create view,sysdba to bing;

SQL>alter user c##ggs quota unlimited on goldengatecdb;

SQL>grant connect,resource,dba,CTXAPP,create view,sysdba to bing;

SQL>alter user bing quota unlimited on bingdb;

删除用户使用命令 drop user bing

删除表空间使用 drop tablespace goldengatecdb;

4.源端配置：

4.1.源端目标端GLOBAL全局参数

--param最后一行加分号，命令行最后不加分号

GGSCI (WIN-GMCTQMCTAGI) 2> edit params ./GLOBALS

GGSCI (WIN-GMCTQMCTAGI) 2>view param ./GLOBALS

GGSCHEMA c##ogg

4.2 配置mgr进程

GGSCI (WIN-GMCTQMCTAGI) 2>edit param 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

查询进程

GGSCI (WIN-GMCTQMCTAGI) 2>info all

查看status这个状态

启动mgr

GGSCI (WIN-GMCTQMCTAGI) 2>start mgr

4.3 配置抽取进程

GGSCI (WIN-JE8F5NRCVJG as c##ggs@orcl/ORCLPDB) 10> edit params ext

extract ext

userid c##ggs,password password

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 orclpdb.bing.*;

配置extract抓取进行：

GGSCI (WIN-JE8F5NRCVJG as c##ggs@orcl/ORCLPDB) 12> add extract ext,integrated tranlog,begin now

GGSCI (WIN-JE8F5NRCVJG as c##ggs@orcl/ORCLPDB) 15> dblogin userid c##ggs@orcl,password password

Successfully logged into database CDB$ROOT.

GGSCI (WIN-JE8F5NRCVJG as c##ggs@orcl/CDB$ROOT) 16> register extract ext database container(ORCLPDB)

2019-12-31 19:54:01 INFO OGG-02003 Extract EXT successfully registered with database at SCN 2116910.

GGSCI (WIN-JE8F5NRCVJG as c##ggs@orcl/CDB$ROOT) 17> add exttrail ./dirdat/ex,extract ext

启动extract进程

GGSCI (ogg1) 7> start extract ext

管理extract抓取进程命令

GGSCI > start extract ext –启动ext进程

GGSCI > stop extract ext –关闭ext进程

GGSCI > kill extract ext --强制停止ext进程

GGSCI > info ext --查看进程参数信息

GGSCI > stats ext --查看进程运行信息

GGSCI > view report ext ---查看运行报告

4.4 配置同步进程

GGSCI (WIN-JE8F5NRCVJG as c##ggs@orcl/CDB$ROOT) 18> edit params pump

extract pump

dynamicresolution

userid c##ggs,password password ---本地账号不是远程账号

rmthost 192.168.1.124,mgrport 7809,compress

rmttrail ./dirdat/dp

table orclpdb.bing.a;

提供给对方的数据为

rmthost 192.168.1.124,mgrport 7809,compress

rmttrail ./dirdat/dp

GGSCI (WIN-JE8F5NRCVJG as c##ggs@orcl/CDB$ROOT) 21> add extract pump,exttrailsource ./dirdat/ex

EXTRACT added.

GGSCI (WIN-JE8F5NRCVJG as c##ggs@orcl/CDB$ROOT) 22> add rmttrail ./dirdat/dp,extract pump

RMTTRAIL added.

管理pump投递进程

GGSCI > start extract pump –启动pump进程

GGSCI > stop extract pump –关闭pump进程

GGSCI > kill extract pump --强制停止pump进程

GGSCI > info pump --查看进程参数信息

GGSCI > stats pump --查看进程运行信息

GGSCI > view report pump ---查看运行报告

5.目标端配置：

5.1.源端目标端GLOBAL全局参数

--param最后一行加分号，命令行最后不加分号

GGSCI (WIN-GMCTQMCTAGI) 2> edit params ./GLOBALS

GGSCI (WIN-GMCTQMCTAGI) 2>view param ./GLOBALS

ggschema c##ggs

enablemonitoring

checkpointtable c##ggs.chkpt

5.2 配置mgr进程

GGSCI (WIN-GMCTQMCTAGI) 2>edit param 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

查询进程

GGSCI (WIN-GMCTQMCTAGI) 2>info all

查看status这个状态

启动mgr

GGSCI (WIN-GMCTQMCTAGI) 2>start mgr

5.3 目标端checkpoint

GGSCI (WIN-GMCTQMCTAGI) 3> dblogin userid c##ggs@ORCL_PDB,password password

Successfully logged into database ORCLPDB.

GGSCI (WIN-GMCTQMCTAGI as c##ggs@orcl/ORCLPDB) 4> add replicat rep,exttrail ./dirdat/dp,checkpointtable c##ggs.ckpt

Logon catalog name ORCLPDB will be used for table specification ORCLPDB.c##ggs.CKPT.

Successfully created checkpoint table ORCLPDB.c##ggs.CKPT.

5.4 目标端rep进程

GGSCI (18c) 6> view param rep

replicat rep

userid c##ggs@ORCL_PDB,password password

assumetargetdefs

reperror default,discard

discardfile ./dirrpt/replzl.dsc,append,megabytes 50

dynamicresolution

map orclpdb.bing.*,target orclpdb.bing.*;

GGSCI (WIN-GMCTQMCTAGI as c##ggs@orcl/ORCLPDB) 7> add replicat rep integrated,exttrail ./dirdat/dp

REPLICAT (Integrated) added.

GGSCI (18c as c##ggs@lzl1/LZL) 101> start replzl

Sending START request to MANAGER ...

REPLICAT REPLZL starting

GGSCI (WIN-GMCTQMCTAGI as c##ggs@orcl/ORCLPDB) 8> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

JAGENT STOPPED

PMSRVR STOPPED

REPLICAT STOPPED REP 00:00:00 00:00:16