OGG:11G RAC - 19C 单实例搭建同步实况
11g rac -- 19c 单机 ogg软件下载地址
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
官方指导手册：
https://docs.oracle.com/en/middleware/goldengate/core/19.1/index.html
https://docs.oracle.com/en/middleware/goldengate/core/19.1/oracle-db/index.html
参数说明：
https://docs.oracle.com/en/middleware/goldengate/core/19.1/reference/index.html
创建 ogg 安装目录,一般会使用 OS 用户 Oracle 作为 ogg 系统安装用户。
环境简介
IP 系统 Oracle版本 OGG版本
源端 192.167.100.150/151 RHEL6.10 oracle11204 191004
目标端 192.167.100.99 RHEL7.4 oracle19300 191004 |
一、源端 11g 安装（ rac 装在一个节点即可）
1 、安装 ogg 19.1
创建 ogg 安装目录 , 一般会使用 OS 用户 Oracle 作为 ogg 系统安装用户。
使用 Oracle 用户创建软件安装目录
mkdir -p /ogg
chown -R oracle:oinstall /ogg |
上传软件到 /ogg 并解压
191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@rac1 ogg]$ unzip 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@rac1 ogg]$ ll
total 543548
-rwxr-xr-x 1 root root 556240981 Aug 5 22:31 191004_fbo_ggs_Linux_x64_shiphome.zip
drwxr-xr-x 3 oracle oinstall 4096 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 、配置环境变量
[oracle@rac1 ogg]$ vi ~/.bash_profile
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/oracle/app
export ORACLE_HOME=/oracle/app/product/11.2.0/db_1
export OGG_HOME=/ogg
export ORACLE_SID=zmhtxdy1
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$OGG_HOME:/lib/usr/lib:/usr/
local
/lib
[oracle@rac1 ogg]$ source ~/.bash_profile |
3 、静默安装
ogg 自 12c 以后可以图形化安装，也可以命令行安装。下面修改响应文件采用命令行静默安装。
vi /ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp |
只修改如下两个地方即可。
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/ogg |
使用如下命令静默安装：
[oracle@rac1 ogg]$ /ogg/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller -silent -responseFile /ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer...
Checking
Temp
space
: must be greater than 120 MB. Actual 30915 MB Passed
Checking swap
space
: must be greater than 150 MB. Actual 3205 MB Passed
Preparing
to
launch Oracle Universal Installer
from
/tmp/OraInstall2022-08-05_10-53-31PM. Please wait ...[oracle@rac1 ogg]$ [WARNING] [INS-75003] The specified directory /ogg
is
not
empty.
CAUSE: The directory specified /ogg
contains
files.
ACTION
: Clean up the specified directory
or
enter a new directory location.
Successfully Setup Software.
The installation
of
Oracle GoldenGate Core was successful.
Please
check
'/oracle/oraInventory/logs/silentInstall2022-08-05_10-52-15PM.log'
for
more details.
[oracle@rac1 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 11g
on
Oct 17 2019 23:13:12
Operating system
character
set
identified
as
UTF-8.
Copyright (C) 1995, 2019, Oracle
and
/
or
its affiliates.
All
rights reserved.
GGSCI (rac1) 1> |
安装成功。
二、目标端 19c 下安装
1 、环境变量编辑
[oracle@19c ogg]$ vi ~/.bash_profile
PATH=$PATH:$HOME/.
local
/bin:$HOME/bin
export PATH
export ORACLE_BASE=/oracle/app
export ORACLE_HOME=/oracle/app/product/19.3.0/db_1
export OGG_HOME=/ogg
export ORACLE_SID=zmhtxdy
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$OGG_HOME:/lib/usr/lib:/usr/
local
/lib
[oracle@19c ogg]$ source ~/.bash_profile |
静默安装
[oracle@19c ogg]$ unzip 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@19c ogg]$ vi /ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
INSTALL_OPTION=ORA19c
SOFTWARE_LOCATION=/ogg
[oracle@19c ogg]$ /ogg/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller -silent -responseFile /ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer...
Checking
Temp
space
: must be greater than 120 MB. Actual 28826 MB Passed
Checking swap
space
: must be greater than 150 MB. Actual 1614 MB Passed
Preparing
to
launch Oracle Universal Installer
from
/tmp/OraInstall2024-01-23_04-40-23PM. Please wait ...[oracle@19c ogg]$ [WARNING] [INS-75003] The specified directory /ogg
is
not
empty.
CAUSE: The directory specified /ogg
contains
files.
ACTION
: Clean up the specified directory
or
enter a new directory location.
You can find the log
of
this install session
at
:
/oracle/oraInventory/logs/installActions2024-01-23_04-40-23PM.log
WARNING:OUI-10030:You have specified a non-empty directory
to
install this product. It
is
recommended
to
specify either an empty
or
a non-existent directory. You may, however, choose
to
ignore
this message if the directory
contains
Operating System generated files
or
subdirectories
like
lost+found.
Do you want
to
proceed
with
installation
in
this Oracle Home?
Successfully Setup Software.
The installation
of
Oracle GoldenGate Core was successful.
Please
check
'/oracle/oraInventory/logs/silentInstall2024-01-23_04-40-23PM.log'
for
more details.
[oracle@19c 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 (19c) 1> |
安装成功。
三、数据库源端（11g）相关设置
0. 归档开启，步骤省略
1. 设置 enable_ogg_replication 为 true
SQL> show parameter enable_goldengate_replication
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean
FALSE
SQL>
alter
system
set
enable_goldengate_replication=
true
scope=both sid=
'*'
;
System altered.
SQL> show parameter enable_goldengate_replication
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean
TRUE |
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
------------ --- -------- --- ---
ARCHIVELOG
NO
NO
NO
NO
SQL>
alter
database
add
supplemental log data;
SQL>
alter
database
force
logging;
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
------------ --- -------- --- ---
ARCHIVELOG YES YES
NO
NO |
3. 创建 ogg 表空间、用户并授权
create
tablespace ogg_tbs datafile
'+DATA'
size
1G autoextend
on
maxsize 1G;
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
dba
to
ogg; |
4.配置 DDL 同步
切记 @marker_setup.sql 一定要在 cd $OGG_HOME 目录下执行否则会卡主。执行脚本时要输入 ogg 管理用户，本实例是 ogg 。
cd /ogg
sqlplus /
as
sysdba
grant
execute
on
utl_file
to
ogg;
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
grant
GGS_GGSUSER_ROLE
to
ogg;
@ddl_enable.sql
@ddl_pin ogg |
5.两端配置 tns和监听文件
注意要是多实例需要配置 tns 登录创建的 ogg 用户 ,19c PDB 也需要 tns 登录到相应的数据库。
[oracle@rac1 admin]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
OGG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.167.100.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
+ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.167.100.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
)
)
[oracle@rac1 ogg]$ more /oracle/grid/crs_1/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(
KEY
=LISTENER)))) # line added
by
Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(
KEY
=LISTENER_SCAN1)))) # line added
by
Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=
ON
# line added
by
Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=
ON
# line added
by
Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = +ASM)
(ORACLE_HOME=/oracle/grid/crs_1)
(SID_NAME = +ASM1)
)
) |
--reload 监听，使配置生效
[grid@rac1 ~]$ lsnrctl reload
[oracle@rac1 ogg]$ lsnrctl stat
LSNRCTL
for
Linux: Version 11.2.0.4.0 - Production
on
13-APR-2023 12:46:08
Copyright (c) 1991, 2013, Oracle.
All
rights reserved.
Connecting
to
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS
of
the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR
for
Linux: Version 11.2.0.4.0 - Production
Start
Date
13-APR-2023 10:58:40
Uptime 0 days 1 hr. 47
min
. 28 sec
Trace
Level
off
Security
ON
:
Local
OS Authentication
SNMP
OFF
Listener Parameter File /oracle/grid/crs_1/network/admin/listener.ora
Listener Log File /oracle/gridbase/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(
KEY
=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.167.100.150)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.167.100.152)(PORT=1521)))
Services Summary...
Service
"+ASM"
has 2 instance(s).
Instance
"+ASM1"
, status UNKNOWN, has 1 handler(s)
for
this service...
Instance
"+ASM1"
, status READY, has 1 handler(s)
for
this service...
Service
"orcl"
has 1 instance(s).
Instance
"orcl1"
, status READY, has 1 handler(s)
for
this service...
Service
"orclXDB"
has 1 instance(s).
Instance
"orcl1"
, status READY, has 1 handler(s)
for
this service...
The command completed successfully |
--源端创建测试用户和表数据
create
user
test identified
by
test;
grant
connect
,resource
to
test;
conn test/test
Connected.
create
table
test (id number(10)
primary
key
,
name
varchar
(8));
insert
into
test
values
(1,
'zhangsan'
);
insert
into
test
values
(2,
'lisi'
);
commit
; |
6.打开表级附加日志
[oracle@rac1 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 11g
on
Oct 17 2019 23:13:12
Operating system
character
set
identified
as
UTF-8.
Copyright (C) 1995, 2019, Oracle
and
/
or
its affiliates.
All
rights reserved.
GGSCI (rac1) 1> dblogin userid ogg@OGG,
password
ogg
--注意服务器多实例需要配置 tns 登录创建的 ogg 用户
Successfully logged
into
database
.
GGSCI (rac1
as
ogg@orcl1) 2> info trandata test.test
Logging
of
supplemental redo log data
is
disabled
for
table
TEST.TEST.
GGSCI (rac1
as
ogg@orcl1) 3>
add
trandata test.*
2023-04-13 12:04:26 INFO OGG-15132 Logging
of
supplemental redo data enabled
for
table
TEST.TEST.
2023-04-13 12:04:26 INFO OGG-15133 TRANDATA
for
scheduling columns has been added
on
table
TEST.TEST.
2023-04-13 12:04:26 INFO OGG-15135 TRANDATA
for
instantiation CSN has been added
on
table
TEST.TEST. |
-- 查看附加日志是否添加成功
GGSCI (rac1
as
ogg@orcl1) 4> info trandata test.test
Logging
of
supplemental redo log data
is
enabled
for
table
TEST.TEST.
Columns supplementally logged
for
table
TEST.TEST:
"ID"
.
Prepared CSN
for
table
TEST.TEST: 1004051 |
7.配置 MGR
GGSCI (rac1
as
ogg@orcl1) 5>
create
subdirs
Creating subdirectories under
current
directory /ogg
Parameter file /ogg/dirprm: created.
Report file /ogg/dirrpt: created.
Checkpoint
file /ogg/dirchk: created.
Process status files /ogg/dirpcs: created.
SQL script files /ogg/dirsql: created.
Database
definitions files /ogg/dirdef: created.
Extract data files /ogg/dirdat: created.
Temporary
files /ogg/dirtmp: created.
Credential store files /ogg/dircrd: created.
Masterkey wallet files /ogg/dirwlt: created.
Dump files /ogg/dirdmp: created. |
7.1 编辑 mgr 并启动
GGSCI (rac1
as
ogg@orcl1) 6> edit param mgr
port 7809
DYNAMICPORTLIST 7810-7829
userid ogg@OGG,
PASSWORD
ogg
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS /ogg/dirdat/*,usecheckpoints, minkeepdays 8
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
ACCESSRULE, PROG *, IPADDR 192.*.*.*, PRI 1, ALLOW
以下是参数的意义
PORT: 指定 Manager 使用的端口。
Dynamicportliist:配置了捕获和复制进程使用的端口范围。
AUTORESTART: 参数使抽取/复制进程失败后自动重启。
PURGEOLDEXTRACTS 参数指定：当根据
checkpoint
发现已经完成抽取和复制的 trail 文件将被自动删除，但保留最近 10 个。
Purgeddlhistory 和 purgemarkerhistory 分别删除历史 DDL 历史表和 marker 表中的过期数据，以控制他们不会过于庞大。 |
GGSCI (rac1
as
ogg@orcl1) 7> info
all
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
MANAGER STOPPED
GGSCI (rac1
as
ogg@orcl1) 8> start mgr
Manager started.
GGSCI (rac1
as
ogg@orcl1) 9> info
all
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
MANAGER RUNNING |
8.配置 extract 抽取进程 ext1
SQL>
select
distinct
thread#
from
v$log;
THREAD#
----------
1
2
SQL>
SELECT
TO_CHAR(
MIN
(start_scn) )
AS
"Please select the minimumSCN"
FROM
v$
transaction
UNION
ALL
SELECT
TO_CHAR(current_scn)
FROM
v$
database
;
Please
select
the minimumSCN
----------------------------------------
1942119
SQL>
select
to_char(scn_to_timestamp(1942119),
'yyyy-mm-dd hh24:mi:ss'
) scndate
from
dual;
SCNDATE
-------------------
2022-08-05 23:54:01 |
单机配置：
add
extract ext1, TRANLOG,
BEGIN
NOW |
RAC
配置：
add
extract ext1, TRANLOG,
BEGIN
NOW, THREADS 2
--（主库有两个threads） |
或者
add
extract ext1, TRANLOG,
BEGIN
2022-08-05 23:54:01, THREADS 2 |
GGSCI (rac1
as
ogg@orcl1) 11> info
all
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:03:49
GGSCI (rac1
as
ogg@orcl1) 12>
add
exttrail /ogg/dirdat/ss, extract ext1, megabytes 1024
EXTTRAIL added.
GGSCI (rac1
as
ogg@orcl1) 13> edit param ext1
EXTRACT ext1
SETENV (NLS_LANG=
"AMERICAN_AMERICA.ZHS16GBK"
)
setenv (ORACLE_SID=
"orcl1"
)
setenv (ORACLE_HOME=/oracle/app/product/11.2.0/db_1)
setenv (TNS_ADMIN=/oracle/app/product/11.2.0/db_1/network/admin)
USERID ogg@OGG,
PASSWORD
ogg
TRANLOGOPTIONS ASMUSER sys@+ASM,ASMPASSWORD oracle
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL /ogg/dirdat/ss
DDL INCLUDE
ALL
TABLE
test.*; |
启动 ext1 捕获进程
GGSCI (rac1
as
ogg@orcl1) 51> start ext1
Sending START request
to
MANAGER ...
EXTRACT EXT1 starting
GGSCI (rac1
as
ogg@orcl1) 52> info
all
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:05 |
9.配置pump传输进程
GGSCI (rac1
as
ogg@orcl1) 54>
add
extract PUMP1,exttrailsource /ogg/dirdat/ss,
begin
now
EXTRACT added.
GGSCI (rac1
as
ogg@orcl1) 55>
add
rmttrail /ogg/dirdat/ss,extract PUMP1
RMTTRAIL added.
GGSCI (rac1
as
ogg@orcl1) 56> edit params pump1
EXTRACT pump1
userid ogg@OGG,
password
ogg
RMTHOST 192.167.100.99, MGRPORT 7809
--其中rmthost用来指定目标数据库的IP
RMTTRAIL /ogg/dirdat/ss
--rmttrail用来指定投递到目标端的trail文件
passthru
TABLE
test.*;
--table参数后面就是你要复制投递的表，注意结束了一定要用‘;’ |
四、数据库目标端（19c）相关设置
1 、目标端创建相关管理用户、表空间等
create
tablespace ogg_tbs datafile
'/oracle/app/oradata/ZMHTXDY/testogg/ogg01.dbf'
size
1G autoextend
on
maxsize 1G;
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
dba
to
ogg; |
2、TNS文件配置(我的是PDB的配置)：
TESTOGG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.167.100.99)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTOGG)
)
) |
3、添加GLOBALS参数文件，创新检查点表
GGSCI (19c) 1> edit params ./GLOBALS
GGSCI (19c) 2>
view
params ./GLOBALS
GGSCHEMA ogg
checkpointtable ogg.
checkpoint
GGSCI (19c) 4> dblogin userid ogg@TESTOGG,
password
ogg
Successfully logged
into
database
TESTOGG.
GGSCI (19c
as
ogg@zmhtxdy/TESTOGG) 5>
add
checkpointtable ogg.
checkpoint
Logon catalog
name
TESTOGG will be used
for
table
specification TESTOGG.ogg.
checkpoint
.
Successfully created
checkpoint
table
TESTOGG.ogg.
checkpoint
. |
4、配置mgr进程
GGSCI (19c) 1>
create
subdirs
Creating subdirectories under
current
directory /ogg
Parameter file /ogg/dirprm: created.
Report file /ogg/dirrpt: created.
Checkpoint
file /ogg/dirchk: created.
Process status files /ogg/dirpcs: created.
SQL script files /ogg/dirsql: created.
Database
definitions files /ogg/dirdef: created.
Extract data files /ogg/dirdat: created.
Temporary
files /ogg/dirtmp: created.
Credential store files /ogg/dircrd: created.
Masterkey wallet files /ogg/dirwlt: created.
Dump files /ogg/dirdmp: created.
GGSCI (19c) 2> dblogin userid ogg@TESTOGG,
password
ogg
Successfully logged
into
database
TESTOGG.
GGSCI (19c
as
ogg@zmhtxdy/TESTOGG) 3> edit params mgr
GGSCI (19c
as
ogg@zmhtxdy/TESTOGG) 4>
view
params mgr
port 7809
GGSCI (19c
as
ogg@zmhtxdy/TESTOGG) 5> start mgr
Manager started.
GGSCI (19c
as
ogg@zmhtxdy/TESTOGG) 6> info
all
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
MANAGER RUNNING
GGSCI (19c
as
ogg@zmhtxdy/TESTOGG) 7> sh netstat -ntpl |grep 7809
(
Not
all
processes could be identified, non-owned process info
will
not
be shown, you would have
to
be root
to
see it
all
.)
tcp6 0 0 :::7809 :::* LISTEN 33669/./mgr
GGSCI (19c
as
ogg@zmhtxdy/TESTOGG) 8> sh ps -ef|grep mgr
postfix 1296 1291 0 08:42 ? 00:00:00 qmgr -l -t unix -u
oracle 33669 33551 0 17:48 ? 00:00:00 ./mgr PARAMFILE /ogg/dirprm/mgr.prm REPORTFILE /ogg/dirrpt/MGR.rpt PROCESSID MGR
oracle 33704 33551 0 17:49 pts/0 00:00:00 sh -c ps -ef|grep mgr
oracle 33706 33704 0 17:49 pts/0 00:00:00 grep mgr |
5、配置replicat复制进程
GGSCI (19c
as
ogg@zmhtxdy/TESTOGG) 9>
add
replicat rep1, exttrail /ogg/dirdat/ss, checkpointtable ogg.
checkpoint
REPLICAT added.
GGSCI (19c
as
ogg@zmhtxdy/TESTOGG) 10> edit params rep1
REPLICAT rep1
setenv (ORACLE_SID=zmhtxdy)
SETENV (ORACLE_HOME =
"/oracle/app/product/19.3.0/db_1"
)
USERID ogg@TESTOGG,
PASSWORD
ogg
ASSUMETARGETDEFS
----使用ASSUMETARGETDEFS参数时，用MAP语句中指定的生产库源表和灾备端目标表具有相同的列结构。它指示的Oracle GoldenGate不在生产端查找源表的结构定义。
HANDLECOLLISIONS
----不要用！存在操作不一致性(详情https://blog.csdn.net/zhuxiaoliao/article/details/42234661)
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
--当DDL复制报错时，则需要用到此处的ddlerror参数预处理一些常见的报错信息。Ddlerror对于抽取、复制进程均有效，默认为abend。
REPERROR (
DEFAULT
, DISCARD)
DDLERROR
DEFAULT
IGNORE
RETRYOP
--DDL语句出错默认
DISCARDFILE /u01/app/oracle/ogg/dircrd/rep1_discard.txt,append,megabytes 10
--将执行失败的记录保存在discard file中，文件中已经包含记录的话，再后面继续追加，不删除之前的记录。
--如当DDL复制报ORA-1430错误，传递了重复的alter语句导致，则可以用ddlerror (1430, discard)将错误信息扔到discard文件里。
DISCARDROLLOVER
AT
02:00
--为了防止discard file被写满，每天2：00做一次文件过期设定
MAP test.*, TARGET test.*; |
配置完先不启动，后面再说。
五、初始化数据
1 、启动源端的抽取进程和投递进程，和两端的管理进程
GGSCI (rac1
as
ogg@orcl1) 63> start ext1
Sending START request
to
MANAGER ...
EXTRACT EXT1 starting
GGSCI (rac1
as
ogg@orcl1) 64> start pump1
Sending START request
to
MANAGER ...
EXTRACT PUMP1 starting
GGSCI (rac1
as
ogg@orcl1) 65> info
all
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:04
EXTRACT RUNNING PUMP1 00:00:00 00:00:00 |
2、查看数据库中所有事务的开始时间
查看数据库中所有事务的开始时间，直到其大于抽取进程的启动时间再开始备份数据库，因为 GoldenGate 的只获取在 Extract 启动以后的交易变化，在 Extract 启动之前开始而在 Extract 启动以后才完成的交易 GoldenGate 将会忽略这些交易，这些被忽略的交易数据就会丢失。 所以需要等数据库中所以的交易都在 Extract 启动之后开始的才能开始备份数据库。
通过 vtransaction 视图来查看数据库中的交易：
SQL>
select
*
from
v$
transaction
;
no
rows
selected |
这里是测试环境没有事物，可以进行后面的备份了。
3、EXPDP 备份源端数据库
当所有在 Extract 启动之前的开始的交易都完成后，我们就可以使用 expdp 备份生产端的数据库了。备份数据库的过程中一定要密切监控 Extract 进程的状态，保证其一直正常运行
3.1源端查询当前数据库SCN
[root@rac1 ~]# mkdir /backup
[root@rac1 ~]# chown -R oracle:oinstall /backup
[root@rac1 ~]# su - oracle
SQL>
create
or
replace
directory dump_dir
as
'/backup'
;
Directory created.
SQL>
grant
read
,write
on
directory dump_dir
to
system;
Grant
succeeded.
SQL>
select
current_scn
from
v$
database
;
CURRENT_SCN
-----------
1019439 |
3.2备份源端数据库
expdp
system/oracle directory=dump_dir dumpfile=sender1_%U.dmp logfile=
user
.log schemas=test parallel=2
flashback_scn= 1019439 cluster=N |
3.3传递备份片到目标端
目标端创建备份目录
[oracle@19c ogg]$ mkdir -p /home/oracle/backup |
源端数据库传输到目标端
[oracle@cndba backup]$ scp *.dmp oracle@192.167.100.99:/home/oracle/backup |
在目标端给备份文件授权
[oracle@19c backup]# chown -R oracle:oinstall *.dmp
[oracle@19c backup]# chmod 777 *.dmp |
4、IMPDP 初始化数据
4.1Impdp 数据
[oracle@19c backup]$ sqlplus sys@TESTOGG
as
sysdba
SQL>
create
or
replace
directory dump_dir
as
'/home/oracle/backup'
;
Directory created.
[oracle@19c backup]$ impdp system/oracle@TESTOGG directory=dump_dir dumpfile= sender1_%U.dmp logfile=
user
.log parallel=2 table_exists_action=
replace
SCHEMAS=test |
4.2用SCN 启动Replicat
GGSCI (19c) 1> start rep1, aftercsn 1019439
Sending START request
to
MANAGER ...
REPLICAT REP1 starting
OGG-02091 Oracle GoldenGate Delivery
for
Oracle, rep1.prm: Operation
not
supported because enable_goldengate_replication
is
not
set
to
true
.
SQL>
alter
system
set
enable_goldengate_replication=
true
scope=both;
System altered. |
参数修改后重启Replicat正常：
GGSCI (19c) 1> start rep1, aftercsn 1019439
GGSCI (19c) 2> info
all
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:04 |
六、DML测试
-- 检查目标端数据是否正常
GGSCI (19c) 4> info
all
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06
SQL>
select
*
from
test.test;
ID
NAME
---------- --------
1 zhangsan
2 lisi |
-- 源端表中添加数据
insert
into
test.test
values
(3,
'wanger'
);
commit
;
select
*
from
test.test;
ID
NAME
---------- --------
1 zhangsan
2 lisi
3 wanger |
-- 目标端查看
SQL>
select
*
from
test;
ID
NAME
---------- --------
1 zhangsan
2 lisi
3 wanger |
可以看到可以同步过来的。
七、DDL测试
1 ./GLOBALS 设置 ( 之前设置过可忽略 )
-- 源端
GGSCI (rac1) 3> edit params ./GLOBALS
GGSCI (rac1) 4>
view
params ./GLOBALS
GGSCHEMA ogg |
-- 目标端
GGSCI (19c) 2> edit params ./GLOBALS
GGSCI (19c) 3>
view
params ./GLOBALS
GGSCHEMA ogg
checkpointtable ogg.
checkpoint |
2.在源端和目标端执行与DDL同步相关的SQL脚本
经测试目标端不执行 DDL 同步相关的 SQL 脚本也能同步 DDL 数据
切记 @marker_setup.sql 一定要在 cd $OGG_HOME 目录下执行否则会卡主。执行脚本时要输入 ogg 管理用户，
本实例是 ogg 。
cd /ogg
sqlplus /
as
sysdba
grant
execute
on
utl_file
to
ogg;
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
grant
GGS_GGSUSER_ROLE
to
ogg;
@ddl_enable.sql
@ddl_pin ogg |
3.源端extract 配置
GGSCI (rac1) 11> edit params ext1
GGSCI (rac1) 12>
view
params ext1
EXTRACT ext1
SETENV (NLS_LANG=
"AMERICAN_AMERICA.ZHS16GBK"
)
USERID ogg,
PASSWORD
ogg
TRANLOGOPTIONS ASMUSER sys@+ASM,ASMPASSWORD oracle
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL /ogg/dirdat/ss
DDL INCLUDE
ALL
--主要是更新这条，前面设置过可忽略
TABLE
test.*; |
重启 extract 进程
GGSCI (rac1) 13> stop ext1
Sending STOP request
to
EXTRACT EXT1 ...
Request processed.
GGSCI (rac1) 14> start ext1
Sending START request
to
MANAGER ...
EXTRACT EXT1 starting
GGSCI (rac1) 15> info
all
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:07
EXTRACT RUNNING PUMP1 00:00:00 00:00:01 |
4.目标端replicat 配置
GGSCI (19c) 3> edit params rep1
GGSCI (19c) 4>
view
params rep1
REPLICAT rep1
setenv (ORACLE_SID=zmhtxdy)
SETENV (ORACLE_HOME =
"/oracle/app/product/19.3.0/db_1"
)
USERID ogg@TESTOGG,
PASSWORD
ogg
ASSUMETARGETDEFS
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
REPERROR (
DEFAULT
, DISCARD)
DDLERROR
DEFAULT
IGNORE
RETRYOP
DISCARDFILE /ogg/dircrd/rep1_discard.txt,append,megabytes 10
DISCARDROLLOVER
AT
02:00
MAP test.*, TARGET test.*;
--更新下面两个参数
ddl include
all
ddlerror
default
ignore
retryop maxretries 3 retrydelay 5
GGSCI (19c) 5> stop rep1
Sending STOP request
to
REPLICAT REP1 ...
Request processed.
GGSCI (19c) 6> start rep1
Sending START request
to
MANAGER ...
REPLICAT REP1 starting
GGSCI (19c) 7> info
all
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:05 |
5.DDL测试
-- 源端：
SQL> conn test/test
Connected.
SQL>
create
table
test2(id number(10)
primary
key
,
name
varchar
(8));
Table
created.
SQL>
insert
into
test2
values
(1,
'zhangsan'
);
1 row created.
SQL>
commit
;
Commit
complete. |
--目标端：
[oracle@19c ogg]$ sqlplus test/test@testogg
SQL>
desc
test2
Name
Null
? Type
----------------------------------------- -------- ----------------------------
ID
NOT
NULL
NUMBER(10)
NAME
VARCHAR2(8)
SQL>
select
*
from
test2
ID
NAME
---------- --------
1 zhangsan |
测试成功。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70013625/viewspace-3093165/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Dg broker如何配置
下一篇：
如何主库进行闪回，而备库不需要重新搭建？
请登录后发表评论
登录
全部评论
