自学内容网 自学内容网
自学内容网 > 正文

Oracle19C PDB 环境下 OGG 搭建（PDB to PDB）

🕗 发布于 2025-01-16 10:21 oracle  数据库  

1. Euler21.10 安装oracle19C OGG （PDB to PDB）

1.1. 环境介绍

源端（RAC）

目标端（FS）

IP

192.168.40.90/91

192.168.40.96

数据库版本

Oracle 19.22.0

Oracle 19.22.0

主机名

hfdb90/hfdb91

hfogg

操作系统

Euler21.10

Euler21.10

数据库实例

hfdb1/hfdb2

hfogg

同步用户

pdb.hefei

pdb.hefei

同步表

hefei

hefei

OGG版本

19.1.0.0.4

19.1.0.0.4

1.2. OGG 安装目录创建（源端和目标端都要做）

#在两台ogg设备上都创建目录
mkdir /ggs
chown -R oracle:dba /ggs
chmod -R 777 /ggs

1.3. OGG 环境变量配置（源端和目标端都要做）


源端：vim .bash_profile
##原有配置不变，增加 ogg 相关路径
export OGG_HOME=/ggs
export PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

目标端：vim .bash_profile
##原有配置不变，增加 ogg 相关路径
export OGG_HOME=/ggs
export PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

使环境变量生效
source .bash_profile

1.4. 上传安装包并解压（源端和目标端都要做）

下载地址：https://edelivery.oracle.com/

源端和目标端都需上传

su - root
cd /soft
unzip V983658-01.zip
chown -R oracle:oinstall /soft/*

1.5. 安装 OGG

1.5.1. 图形化安装

1.5.1.1. 安装vncserver
su - oracle
[oracle@hfdb30:/backup]$vncserver  #源端和目标端都要执行
[oracle@hfdb30:/backup]$cd /backup/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@hfdb30:/backup]$./runInstaller
1.5.1.2. 或者使用display
su - oracle
cd /backup/fbo_ggs_Linux_x64_shiphome/Disk1
export DISPLAY=192.168.40.90:0.0
xhost +
./runInstaller

在 Oracle 11g 及之前的版本中，安装完 OGG，需要使用 ggsci 工具执行 create subdirs 命令手动创建 OGG 对应的目录，在 12c 及以后版本中，目录自动创建，无需再手动执行 create subdirs 命令。

1.5.2. 静默安装

1.5.2.1. 编辑配置文件
#vim ~/oggcore.rsp
INSTALL_OPTION=ORA19c
SOFTWARE_LOCATION=/ggs
START_MANAGER=true
MANAGER_PORT=7809
DATABASE_LOCATION=/oracle/app/oracle/product/19c/db_1
INVENTORY_LOCATION=/oracle/app/oraInventory
UNIX_GROUP_NAME=oinstall
1.5.2.2. 开始安装
cd /u01/soft/fbo_ggs_Linux_x64_shiphome/Disk1/
./runInstaller -silent -nowait -responseFile ~/oggcore.rsp

2. ogg 配置运行环境准备

01.源端一定要打开归档，目标端一般不需要。

02.源数据库打开补充日志

03.源端开启 force_logging

04.源端关闭回收站功能(下次重启生效，10g 要关，11g 手册说不用关了)

05.修改数据库参数（源端、目标端）

alter system set enable_goldengate_replication=true;

06.源和目标网络通讯正常。

07.创建专用的 goldengate 用户用来同步数据（源端、目标端）。

2.1. 配置归档

源端一定要打开归档，目标端一般不需要。

创建一个归档目录并授权

[oracle@hfdb30:/home/oracle]$mkdir /oracle/archive
[oracle@hfdb30:/home/oracle]$chown -R oracle:dba /oracle/archive
[oracle@hfdb30:/home/oracle]$chmod -R 777 /oracle/archive
SQL> show parameter recovery
SQL> alter system set db_recovery_file_dest_size=100g;
SQL> alter system set db_recovery_file_dest='/oracle/archive';
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
SQL> alter system switch logfile;  --【alter system archive log current; 】

2.2. 源数据库打开补充日志

SQL> select supplemental_log_data_min from v$database;  --【no 就是没有打开】
SQL> alter database add supplemental log data;
SQL> select supplemental_log_data_min from v$database; --【YES】
#检查下归档日志，归档日志正常切换说明就成功了
SQL> alter system switch logfile;

2.3. 源端开启 force_logging 【强制日志】

SQL> select force_logging from v$database; --【no】
SQL> alter database force logging; 
SQL> select force_logging from v$database; --【YES】
SQL> alter system switch logfile;

2.4. 启用 OGG（源端、目标端）

su - oracle
sqlplus / as sysdba
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;  --#源端和目标端都要做

2.5. 源端关闭回收站功能(下次重启生效)

SQL> show parameter recyclebin
SQL> alter system set recyclebin=off scope=spfile;
SQL> shutdown immediate
SQL> startup

--
查看配置结果
select log_mode,supplemental_log_data_min,force_logging from v$database;  #archivelog  yes  yes

2.6. 配置tnsname

2.6.1. 源端 tns 配置

[oracle@hfdb90:/oracle/app/oracle/product/19c/db_1/network/admin]$vi tnsnames. ora

# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

HFDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hfdbscan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hfdb)
    )
  )
dghfdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.95)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dghfdb)
    )
  )
HFOGG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.96)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hfogg)
    )
  )
target_pdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.96)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
  )

source_pdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.92)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
  )
~

2.6.2. 目标端 TNS 配置

[oracle@hfogg:/oracle/app/oracle/product/19c/db_1/network/admin]$vi tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_HFOGG =
  (ADDRESS = (PROTOCOL = TCP)(HOST = hfdb96)(PORT = 1521))


HFOGG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hfdb96)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hfogg)
    )
  )

HFDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.92)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hfdb)
    )
  )
target_pdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.96)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
  )

source_pdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.92)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
  )

2.7. 创建专用的 goldengate 用户用来同步数据

2.7.1. 源端配置用户

CDB执行：
CREATE tablespace GGUSER_TBS datafile '+DGDATA' size 200M autoextend on;
CREATE USER C##GGUSER IDENTIFIED BY goldengate;
GRANT ALTER SESSION TO C##GGUSER;
GRANT CREATE SESSION TO C##GGUSER;
GRANT CONNECT TO C##GGUSER;
GRANT RESOURCE TO C##GGUSER;
GRANT SELECT ANY DICTIONARY TO C##GGUSER;
GRANT SELECT ANY TABLE TO C##GGUSER;
GRANT FLASHBACK ANY TABLE TO C##GGUSER;
GRANT INSERT ANY TABLE TO C##GGUSER;
GRANT UPDATE ANY TABLE TO C##GGUSER;
GRANT DELETE ANY TABLE TO C##GGUSER;
alter user C##GGUSER quota unlimited on GGUSER_TBS;
exec dbms_goldengate_auth.grant_admin_privilege('C##GGUSER');
exec dbms_goldengate_auth.grant_admin_privilege('C##GGUSER',container=>'ALL');

每个PDB执行：
CREATE tablespace GGUSER_TBS datafile '+DGDATA' size 200M autoextend on;
GRANT ALTER SESSION TO C##GGUSER;
GRANT CREATE SESSION TO C##GGUSER;
GRANT CONNECT TO C##GGUSER;
GRANT RESOURCE TO C##GGUSER;
GRANT SELECT ANY DICTIONARY TO C##GGUSER;
GRANT SELECT ANY TABLE TO C##GGUSER;
GRANT FLASHBACK ANY TABLE TO C##GGUSER;
GRANT INSERT ANY TABLE TO C##GGUSER;
GRANT UPDATE ANY TABLE TO C##GGUSER;
GRANT DELETE ANY TABLE TO C##GGUSER;
alter user C##GGUSER quota unlimited on GGUSER_TBS;

再次退回到CDB执行：
alter user C##GGUSER default tablespace GGUSER_TBS;

2.7.2. 目标端配置用户

CDB执行：
CREATE tablespace GGUSER_TBS datafile '/oracle/app/oracle/oradata/HFOGG/ogg.dbf' size 200M autoextend on;
CREATE USER C##GGUSER IDENTIFIED BY goldengate;
GRANT ALTER SESSION TO C##GGUSER;
GRANT CREATE SESSION TO C##GGUSER;
GRANT CONNECT TO C##GGUSER;
GRANT RESOURCE TO C##GGUSER;
GRANT SELECT ANY DICTIONARY TO C##GGUSER;
GRANT SELECT ANY TABLE TO C##GGUSER;
GRANT FLASHBACK ANY TABLE TO C##GGUSER;
GRANT INSERT ANY TABLE TO C##GGUSER;
GRANT UPDATE ANY TABLE TO C##GGUSER;
GRANT DELETE ANY TABLE TO C##GGUSER;
alter user C##GGUSER quota unlimited on GGUSER_TBS;
exec dbms_goldengate_auth.grant_admin_privilege('C##GGUSER');
exec dbms_goldengate_auth.grant_admin_privilege('C##GGUSER',container=>'ALL');

每个PDB执行：
CREATE tablespace GGUSER_TBS datafile '/oracle/app/oracle/oradata/HFOGG/pdb/ogg.dbf' size 200M autoextend on;
GRANT ALTER SESSION TO C##GGUSER;
GRANT CREATE SESSION TO C##GGUSER;
GRANT CONNECT TO C##GGUSER;
GRANT RESOURCE TO C##GGUSER;
GRANT SELECT ANY DICTIONARY TO C##GGUSER;
GRANT SELECT ANY TABLE TO C##GGUSER;
GRANT FLASHBACK ANY TABLE TO C##GGUSER;
GRANT INSERT ANY TABLE TO C##GGUSER;
GRANT UPDATE ANY TABLE TO C##GGUSER;
GRANT DELETE ANY TABLE TO C##GGUSER;
alter user C##GGUSER quota unlimited on GGUSER_TBS;

再次退回到CDB执行：
alter user C##GGUSER default tablespace GGUSER_TBS;

2.7.3. 测试连接正常

[oracle@hfdb96:/oracle/app/oracle/product/19c/db_1/network/admin]$sqlplus c##GGUSER/goldengate@source_pdb

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 14 20:40:24 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

C##GGUSER@source_pdb> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
[oracle@hfdb96:/oracle/app/oracle/product/19c/db_1/network/admin]$sqlplus c##GGUSER/goldengate@target_pdb

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 14 20:40:44 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue Jan 14 2025 20:16:15 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

[oracle@hfdb90:/home/oracle]$sqlplus c##GGUSER/goldengate@source_pdb

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 14 20:41:52 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue Jan 14 2025 20:40:43 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

C##GGUSER@source_pdb> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
[oracle@hfdb90:/home/oracle]$sqlplus c##GGUSER/goldengate@target_pdb

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 14 20:42:01 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue Jan 14 2025 20:40:24 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

3. ogg源端配置过程

3.1.1. 配置 pdb 用户

[oracle@hfdb90:/home/oracle]$sqlplus / as sysdba
alter session set container=pdb;
create tablespace hefei_tbs datafile '+dgdata' size 50m;
create user hefei identified by hefei123 default tablespace hefei_tbs;
grant dba to hefei;


[oracle@hfdb90:/home/oracle]$sqlplus hefei/hefei123@source_pdb;    --测试

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 08:57:52 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

3.1.2. 配置管理进程 mgr

配置 mgr 进程后启动 mgr 进程。

[oracle@hfogg:/ggs]$ggsci
ggsci> CREATE SUBDIRS --【12c以后不用执行】

--配置管理进程 mgr 
ggsci> edit params mgr 
PORT 7809 
DYNAMICPORTLIST 7840-7850   
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

ggsci>start mgr
GGSCI (hfdb90) 4> info all

*/

其他可加的参数含义

##########################

/*

DYNAMICPORTLIST 7810-7820

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

ACCESSRULE,PROG *,IPADDR 192.168.*.*,ALLOW

----参数含义

PORT：默认监听端口

DYNAMICPORTLIST：动态端口列表的范围，当指定端口被占用或者出现通 信故障，管理进程将会从列表中选择下一个端口尝试连接，避免通信端口的单点故障。

AUTORESTART：当提取进程中断后尝试自动重启，每隔 3 分钟尝试启动一次，尝试 15 次。

PURGEOLDEXTRACTS：定期清理 dirdat 路径下的本地队列（local trail）。保留期限 7 天，过期后自动删除。

LAGREPORTHOURS：每隔一小时检查一次传输延迟情况

LAGINFOMINUTES：传输延时超过 30 分钟将写入错误日志

LAGCRITICALMINUTES：传输延时超过 45 分钟将写入警告日志

--报错：

[oracle@hfdb96:/ggs]$ggsci

ggsci: error while loading shared libraries: libnnz19.so: cannot open shared object file: No such file or directory

解决：

ln -s /oracle/app/oracle/product/19c/db_1/lib/libnnz19.so /ggs/libnnz19.so

ln -s /oracle/app/oracle/product/19c/db_1/lib/libclntsh.so.19.1 /ggs/libclntsh.so.19.1

ln -s /oracle/app/oracle/product/19c/db_1/lib/libclntshcore.so.19.1 /ggs/libclntshcore.so.19.1

3.1.3. 配置GLOBAL参数

./ggsci
dblogin userid C##GGUSER,password goldengate
edit params ./GLOBALS
添加一行：
GGSCHEMA C##GGUSER

3.1.4. 源端配置抽取进程 extract

添加抽取进程
./ggsci
dblogin userid C##GGUSER,password goldengate
add trandata pdb.hefei.hefei                     #PDB的表，命名格式为pdb_name.use_rname.table_name,必须全部小写
add extract ext01,integrated tranlog,begin now    #添加抽取进程(集成模式)
add exttrail /ggs/dirdat/et, extract ext01        #添加trail文件路径
register extract ext01 database container(pdb)   #把抽取进程注册到PDB

编辑抽取进程的配置文件
edit param ext01

extract ext01
USERID C##GGUSER, PASSWORD goldengate
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE /ggs/dirrpt/ext01.dsc,APPEND,MEGABYTES 1024
WARNLONGTRANS 2h,CHECKINTERVAL 5m
EXTTRAIL  /ggs/dirdat/et
TABLE pdb.hefei.hefei;


------------------日志


GGSCI (hfdb90) 1> dblogin userid C##GGUSER,password goldengate
Successfully logged into database CDB$ROOT.

GGSCI (hfdb90 as C##GGUSER@hfdb1/CDB$ROOT) 2> add trandata pdb.hefei.hefei

2025-01-15 09:05:41  WARNING OGG-06439  No unique key is defined for table HEFEI. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2025-01-15 09:05:41  INFO    OGG-15130  No key found for table PDB.HEFEI.HEFEI.  All viable columns will be logged.

2025-01-15 09:05:41  INFO    OGG-15132  Logging of supplemental redo data enabled for table PDB.HEFEI.HEFEI.

2025-01-15 09:05:41  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table PDB.HEFEI.HEFEI.

2025-01-15 09:05:42  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table PDB.HEFEI.HEFEI.

2025-01-15 09:05:43  INFO    OGG-10471  ***** Oracle Goldengate support information on table HEFEI.HEFEI *****
Oracle Goldengate support native capture on table HEFEI.HEFEI.
Oracle Goldengate marked following column as key columns on table HEFEI.HEFEI: ID
No unique key is defined for table HEFEI.HEFEI.

GGSCI (hfdb90 as C##GGUSER@hfdb1/CDB$ROOT) 3> add extract ext01,integrated tranlog,begin now
EXTRACT (Integrated) added.


GGSCI (hfdb90 as C##GGUSER@hfdb1/CDB$ROOT) 4> add exttrail /ggs/dirdat/et, extract ext01
EXTTRAIL added.

GGSCI (hfdb90 as C##GGUSER@hfdb1/CDB$ROOT) 5> register extract ext01 database container(pdb)

2025-01-15 09:10:21  ERROR   OGG-02060  The Oracle mining database is not configured properly to support integrated capture. The following configuration error must be fixed:  Operation not supported because enable_goldengate_replication is not set to true.


GGSCI (hfdb90 as C##GGUSER@hfdb1/CDB$ROOT) 6> register extract ext01 database container(pdb)

2025-01-15 09:13:09  INFO    OGG-02003  Extract EXT01 successfully registered with database at SCN 5195801.

3.1.5. 源端配置投递进程 pump

添加投递进程
add extract pump01, exttrailsource /ggs/dirdat/et, begin now   #添加投递进程
add rmttrail /ggs/dirdat/rt, extract pump01                    #添加目标端的trail文件路径

编辑投递进程的配置文件
edit param pump01
extract pump01
USERID C##GGUSER, PASSWORD goldengate
RMTHOST 192.168.40.96, MGRPORT 7809
rmttrail /ggs/dirdat/rt
TABLE pdb.hefei.hefei

3.1. ogg 目标端配置的过程

3.1.1. 目标端添加 pdb 用户

sqlplus / as sysdba
alter session set container=pdb;
create tablespace hefei_tbs datafile '/oracle/app/oracle/oradata/HFOGG/pdb/hefei_tbs1.dbf' size 50m;
create user hefei identified by hefei123 default tablespace hefei_tbs;
grant dba to hefei;

[oracle@hfdb96:/home/oracle]$sqlplus hefei/hefei123@target_pdb  --测试

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 10:33:03 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Wed Jan 15 2025 10:00:58 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

3.1.2. 配置目标端 mgr 管理

cd /ggs
./ggsci
ggsci> edit param mgr
写入以下参数：
PORT 7809 #主端口
DYNAMICPORTLIST 7840-7850   #备用端口（可以指定单个，也可以指定范围）
autostart replicat *
autorestart replicat *,retries 3,waitminutes 3

启动MGR进程
ggsci> start mgr
ggsci> info mgr

3.1.3. 配置复制进程 replicat (目标端)

./ggsci
dblogin userid C##GGUSER@target_pdb,password goldengate
edit params ./GLOBALS
添加2行：
GGSCHEMA C##GGUSER
CHECKPOINTTABLE C##GGUSER.checkpointtable

添加检查点表
add checkpointtable C##GGUSER.checkpointtable 
添加复制进程
add replicat rep01, exttrail /ggs/dirdat/rt, begin now, checkpointtable C##GGUSER.checkpointtable

编辑复制进程的配置文件
edit params rep01 
replicat rep01 
userid C##GGUSER@target_pdb,password goldengate
DISCARDFILE /ggs/dirrpt/rep01.dsc,append,megabytes 1024
map pdb.hefei.hefei,target pdb.hefei.hefei;

4. expdp-impdp初始化

测试数据

CREATE TABLE emp_ceshi (
id INT  PRIMARY KEY,
name VARCHAR(100) NOT NULL,
salary DECIMAL(10, 2)
);
insert into emp_ceshi values(1,'hefei1',5000);
insert into emp_ceshi values(2,'hefei2',6000);
insert into emp_ceshi values(3,'hefei3',6030);
insert into emp_ceshi values(4,'hefei4',6040);
insert into emp_ceshi values(5,'hefei5',6050);
insert into emp_ceshi values(6,'hefei6',6060);
insert into emp_ceshi values(7,'hefei7',6070);
commit;

--目标端导入后，再在源端
insert into emp_ceshi values(8,'hefei8',6080);
commit;

4.1. 源端启动 extract 进程

源和目标的 mgr 进程一定要先启动。

ggsci> start mgr

--edit params ext01  （导出数据之前就要启动）

extract ext01
USERID C##GGUSER, PASSWORD goldengate
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE /ggs/dirrpt/ext01.dsc,APPEND,MEGABYTES 1024
WARNLONGTRANS 2h,CHECKINTERVAL 5m
EXTTRAIL  /ggs/dirdat/et
TABLE pdb.hefei.hefei;
TABLE pdb.hefei.emp_ceshi;  --添加需要同步的表


start ext01 
info all
view report ext01

--edit params pump01（目标端导入的时候在开启）
extract pump01
USERID C##GGUSER, PASSWORD goldengate
RMTHOST 192.168.40.96, MGRPORT 7809
rmttrail /ggs/dirdat/rt
TABLE pdb.hefei.hefei;
TABLE pdb.hefei.emp_ceshi;--添加需要同步的表


--目标端
--edit params rep01
replicat rep01
userid C##GGUSER@target_pdb,password goldengate
DISCARDFILE /ggs/dirrpt/rep01.dsc,append,megabytes 1024
map pdb.hefei.hefei,target hefei.hefei;
map pdb.hefei.emp_ceshi,target hefei.emp_ceshi; --添加需要同步的表



---报错
---------------------

2025-01-15 09:34:01  ERROR   OGG-00396  Command 'TABLE' not terminated by semi-colon.

2025-01-15 09:34:01  ERROR   OGG-01668  PROCESS ABENDING.

--edit param ext01 ，在最后一行表后面加;号。


2025-01-15 09:37:53  ERROR   OGG-00041  Data source not specified.

2025-01-15 09:37:53  ERROR   OGG-01668  PROCESS ABENDING.
-- edit param ext01 ，第一行没有添加extract ext01


--------------报错

2025-01-15 10:26:17  ERROR   OGG-00446  OCI Error ORA-01950: no privileges on tablespace 'USERS'
 (status = 1950). Inserting into checkpoint table C##GGUSER.checkpointtable, group REP01, SQL <INSERT INTO C##GGUSER.checkpointtable (group_name, group_
key, current_dir, create_ts, last_update_ts, seqno, rba, audit_ts, version) VALUES (:group_name, :group_key, :current_dir, :create_ts, sysdate, :seqno,
:rba, :audit_ts, :version)>.

alter user C##GGUSER quota unlimited on USERS;

4.2. 备份源端数据库（expdp）

注意一定要在抽取进程启动后再进行数据库备份 （启动之前把需要同步的表要加到抽取进程中去，启动后再进行备份）

sql>select current_scn from v$database;
5410823
create directory hfbak as '/home/oracle';   
grant read,write on directory hfbak to system; 
grant create any directory to system; 
expdp system/oracle@source_pdb directory=hfbak dumpfile=expdp_hffs_m.dmp logfile=expdp_hffs_m.log  flashback_scn=5410823 tables=hefei.emp_ceshi;




#查看导出目录
SELECT  * from dba_directories; 


-------------------------日志
[oracle@hfdb90:/oracle/app/oracle/product/19c/db_1/network/admin]$expdp system/oracle@source_pdb directory=hfbak dumpfile=expdp_hffs_m.dmp logfile=expdp_hffs_m.log  flashback_scn=5410823 tables=hefei.emp_ceshi;

Export: Release 19.0.0.0.0 - Production on Wed Jan 15 15:28:22 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@source_pdb directory=hfbak dumpfile=expdp_hffs_m.dmp logfile=expdp_hffs_m.log flashback_scn=5410823 tables=hefei.emp_ceshi
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "HEFEI"."EMP_CESHI"                         6.078 KB       7 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/expdp_hffs_m.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 15 15:29:05 2025 elapsed 0 00:00:41

4.3. 把备份文件拷贝到目标库

scp 命令拷贝

scp expdp_hffs_m.dmp 192.168.40.96:/home/oracle

4.4. 在目标库恢复数据库（impdp）

create directory hfbak as '/home/oracle'; 
grant read,write on directory hfbak to system; 
grant create any directory to system; 

impdp system/oracle@target_pdb directory=hfbak dumpfile=expdp_hffs_m.dmp logfile=expdp_out_m.log  full=y table_exists_action=truncate





---------------------------日志
[oracle@hfdb96:/home/oracle]$impdp system/oracle@target_pdb directory=hfbak dumpfile=expdp_hffs_m.dmp logfile=expdp_out_m.log  full=y table_exists_action=truncate

Import: Release 19.0.0.0.0 - Production on Wed Jan 15 15:34:34 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@target_pdb directory=hfbak dumpfile=expdp_hffs_m.dmp logfile=expdp_out_m.log full=y table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HEFEI"."EMP_CESHI"                         6.078 KB       7 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Jan 15 15:35:17 2025 elapsed 0 00:00:35

4.5. 源端开启投递进程：

start pump01
info all
view report pump01



---------------------日志
GGSCI (hfdb90) 10> view report pump01


***********************************************************************
                 Oracle GoldenGate Capture 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:58

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

                    Starting at 2025-01-15 15:41:56
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Tue Jul 18 10:06:28 CST 2023, Release 4.19.90-2107.6.0.0208.16.oe1.bclinux.x86_64
Node: hfdb90
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 828225

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2025-01-15 15:41:56  INFO    OGG-03059  Operating system character set identified as US-ASCII.

2025-01-15 15:41:56  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.

2025-01-15 15:41:56  INFO    OGG-01360  EXTRACT is running in Data Pump mode.

2025-01-15 15:41:56  INFO    OGG-01889  Flush size (max message size) is set to 27,985.
extract pump01
USERID C##GGUSER, PASSWORD ***
RMTHOST 192.168.40.96, MGRPORT 7809
rmttrail /ggs/dirdat/rt
TABLE pdb.hefei.hefei;
TABLE pdb.hefei.emp;
TABLE pdb.hefei.emp_ceshi;

2025-01-15 15:41:56  INFO    OGG-01851  filecaching started: thread ID: 140502607324928.

2025-01-15 15:41:56  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /ggs/dirtmp.

2025-01-15 15:41:57  INFO    OGG-25340
Database Version:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production.

2025-01-15 15:41:57  INFO    OGG-25341
Database Language and Character Set:
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK".

2025-01-15 15:42:02  INFO    OGG-01888  TCP network is configured as

                  OS DEFAULT          SPECIFIED           ACTUAL VALUE
         IP_DSCP    0                   N/A                 0
          IP_TOS    0                   N/A                 0
     TCP_NODELAY    0                   N/A                 0
    TCP_QUICKACK    1                   N/A                 1
        TCP_CORK    0                   N/A                 0
       SO_SNDBUF    8192                N/A                 8192
       SO_RCVBUF    65536               N/A                 65536.

2025-01-15 15:42:02  INFO    OGG-01055  Recovery initialization completed for target file /ggs/dirdat/rt000000001, at RBA 2362.

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************


2025-01-15 15:42:02  INFO    OGG-02243  Opened trail file /ggs/dirdat/et000000001 at 2025-01-15 15:42:02.652097.

2025-01-15 15:42:02  INFO    OGG-01478  Output file /ggs/dirdat/rt is using format RELEASE 19.1.

2025-01-15 15:42:02  INFO    OGG-01026  Rolling over remote file /ggs/dirdat/rt000000002.

2025-01-15 15:42:02  INFO    OGG-01053  Recovery completed for target file /ggs/dirdat/rt000000002, at RBA 1367.

2025-01-15 15:42:02  INFO    OGG-01057  Recovery completed for all targets.

2025-01-15 15:42:02  INFO    OGG-02232  Switching to next trail file /ggs/dirdat/et000000002 at 2025-01-15 15:42:02.710017 due to EOF. with current RBA
2,268.

2025-01-15 15:42:02  INFO    OGG-02263  Passthru MAP (TABLE) resolved (entry pdb.hefei.emp_ceshi): TABLE "PDB"."HEFEI"."EMP_CESHI".

4.6. 目标端启动 replicate 进程

start rep01,aftercsn 1341822
info all 
view report rep01


-----------------------日志

GGSCI (hfdb96) 34> view report rep01


***********************************************************************
                 Oracle GoldenGate Delivery 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:17:25

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

                    Starting at 2025-01-15 15:42:49
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Tue Jul 18 10:06:28 CST 2023, Release 4.19.90-2107.6.0.0208.16.oe1.bclinux.x86_64
Node: hfdb96
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 280003

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2025-01-15 15:42:49  INFO    OGG-03059  Operating system character set identified as UTF-8.

2025-01-15 15:42:49  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.
replicat rep01
userid C##GGUSER@target_pdb,password ***

2025-01-15 15:42:51  INFO    OGG-02679  The Replicat process logged on to database PDB and can only apply to that database.
DISCARDFILE /ggs/dirrpt/rep01.dsc,append,megabytes 1024
map pdb.hefei.hefei,target hefei.hefei;

2025-01-15 15:42:51  INFO    OGG-02669  Default catalog name PDB will be used for target table name hefei.hefei mapping.
map pdb.hefei.emp,target hefei.emp;

2025-01-15 15:42:51  INFO    OGG-02669  Default catalog name PDB will be used for target table name hefei.emp mapping.
map pdb.hefei.emp_ceshi,target hefei.emp_ceshi;

2025-01-15 15:42:51  INFO    OGG-02669  Default catalog name PDB will be used for target table name hefei.emp_ceshi mapping.

2025-01-15 15:42:51  INFO    OGG-06451  Triggers will be suppressed by default.

2025-01-15 15:42:51  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /ggs/dirtmp.

2025-01-15 15:42:51  INFO    OGG-25340
Database Version:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production.

2025-01-15 15:42:51  INFO    OGG-25341
Database Language and Character Set:
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "AL32UTF8".

2025-01-15 15:42:51  INFO    OGG-06604  Database PDB CPU info: CPU Count 4, CPU Core Count 4, CPU Socket Count 1.

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************


2025-01-15 15:42:51  INFO    OGG-02243  Opened trail file /ggs/dirdat/rt000000001 at 2025-01-15 15:42:51.355108.

2025-01-15 15:42:51  INFO    OGG-03506  The source database character set, as determined from the trail file, is zhs16gbk.

2025-01-15 15:42:51  INFO    OGG-02232  Switching to next trail file /ggs/dirdat/rt000000002 at 2025-01-15 15:42:51.357866 due to EOF. with current RBA
2,362.

2025-01-15 15:42:51  INFO    OGG-04048  Processed graceful restart record at seq 2 rba 1,306.

2025-01-15 15:42:51  INFO    OGG-04048  Processed graceful restart record at seq 2 rba 1,367.

2025-01-15 15:42:51  INFO    OGG-01373  User requested start after CSN 5410823.

2025-01-15 15:42:51  INFO    OGG-01374  Transaction delivery commencing at position Seqno 2, RBA 1933, Transaction ID 2484173665.7.11.968, CSN 5429197,
0 transaction(s) skipped.

2025-01-15 15:42:51  INFO    OGG-06505  MAP resolved (entry pdb.hefei.emp_ceshi): map "PDB"."HEFEI"."EMP_CESHI",target "PDB".hefei.emp_ceshi.

2025-01-15 15:43:01  INFO    OGG-02756  The definition for table PDB.HEFEI.EMP_CESHI is obtained from the trail file.

2025-01-15 15:43:01  INFO    OGG-06511  Using following columns in default map by name: ID, NAME, SALARY.

2025-01-15 15:43:01  INFO    OGG-06510  Using the following key columns for target table PDB.HEFEI.EMP_CESHI: ID.


2025-01-15 15:43:02  INFO    OGG-03010  Performing implicit conversion of column data from character set zhs16gbk to UTF-8.

4.7. 数据库查询确认

[oracle@hfdb90:/home/oracle]$sqlplus hefei/hefei123@source_pdb;

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 15:37:43 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Wed Jan 15 2025 15:24:07 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
HEFEI@source_pdb> select * from emp_ceshi;

        ID NAME                     SALARY
---------- -------------------- ----------
         1 hefei1                     5000
         2 hefei2                     6000
         3 hefei3                     6030
         4 hefei4                     6040
         5 hefei5                     6050
         6 hefei6                     6060
         7 hefei7                     6070
         8 hefei8                     6080    --导出后新增的一条数据

8 rows selected.

[oracle@hfdb96:/home/oracle]$sqlplus hefei/hefei123@target_pdb

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 15:36:47 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Wed Jan 15 2025 14:39:10 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

HEFEI@target_pdb> select * from emp_ceshi;

        ID NAME                     SALARY
---------- -------------------- ----------
         1 hefei1                     5000
         2 hefei2                     6000
         3 hefei3                     6030
         4 hefei4                     6040
         5 hefei5                     6050
         6 hefei6                     6060
         7 hefei7                     6070
         8 hefei8                     6080   --导出后新增的一条数据

8 rows selected.

原文地址：https://blog.csdn.net/hf191850699/article/details/145167798

免责声明：本站文章内容转载自网络资源，如侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2025