flink 同步oracle11g数据表到pg库

1. 关闭防火墙和selinux

systemctl stop firewalld
systemctl disable firewalld
systemctl status firewalldvi /etc/selinux/config
修改为disabled

在这里插入图片描述

2.安装java8

yum list java-1.8* yum install java-1.8.0-openjdk* -yjava -version

在这里插入图片描述

3.下载和部署postgresql

看需求安装pg库版本

下载地址：http://www.postgresql.org/ftp/source/

在这里插入图片描述

上传到服务器解压

tar -zxvf postgresql-16.2.tar.gz

./configure

如果出现以下报错解决方法：

yum -y install gcc

在这里插入图片描述

如果出现以下错误解决方法：

yum -y install libicu libicu-devel libunwind readline-devel zlib-devel

在这里插入图片描述

make
make installgroupadd postgres
useradd -g postgres postgres
id postgrescd /usr/local/pgsql/
mkdir data
chown postgres:postgres datacd /home/postgres/
ll -al

在这里插入图片描述

vi .bash_profile
添加
export PGHOME=/usr/local/pgsql/
export PGDATA=/usr/local/pgsql/data
PATH=$PATH:$HOME/bin:$PGHOME/bin

在这里插入图片描述

source .bash_profile
su – postgres
initdb

在这里插入图片描述

cd /usr/local/pgsql/data/
vi postgresql.conf
修改为listen_addresses = '*'

在这里插入图片描述

启动pg库

service postgresql start
su – postgres
psql

在这里插入图片描述

4.下载和部署oracle

Oracle下载地址：

下载好上传两个文件到系统
mkdir /oracle
cd /oracle

在这里插入图片描述

yum install unzip -y
unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip
yum -y install wget
yum install yum install gcc make binutils gcc-c++ compat-libstdc++-33 elfutils-libelf-devel elfutils-libelf-devel-static ksh libaio libaio-devel numactl-devel sysstat unixODBC unixODBC-devel pcre-devel -y 
添加安装用户和用户组
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle创建安装目录和设置文件权限
mkdir -p /u01/app/oracle/product/11.2.0
mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/inventory
mkdir /u01/app/oracle/fast_recovery_area
chown -R oracle:oinstall /u01/
chmod -R 775 /u01/app/oracle
chown -R oracle:oinstall /oracle/

设置oracle用户环境变量

su – oracle	
vi .bash_profileexport PATH
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0
ORACLE_SID=orcl
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8source .bash_profilecp -R /oracle/database/response/ .
cd response/

vi db_install.rsp

oracle.install.responseFileVersion=oracleinstallrspfmt_dbinstall_response_schema_v11_2_0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=flink
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oracle/inventory
SELECTED_LANGUAGES=
ORACLE_HOME=u01/app/oracle/product/11.2.0
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=
oracle.install.db.isCustomInstall=false
oracle.install.db.customComponents=oracle.server11.2.0.1.0,oracle.sysman.ccr10.2.7.0.0,oracle.xdk11.2.0.1.0,oracle.rdbms.oci11.2.0.1.0,oracle.network11.2.0.1.0,oracle.network.listener11.2.0.1.0,oracle.rdbms11.2.0.1.0,oracle.options11.2.0.1.0,oracle.rdbms.partitioning11.2.0.1.0,oracle.oraolap11.2.0.1.0,oracle.rdbms.dm11.2.0.1.0,oracle.rdbms.dv11.2.0.1.0,orcle.rdbms.lbac11.2.0.1.0,oracle.rdbms.rat11.2.0.1.0
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
oracle.install.db.CLUSTER_NODES=
oracle.install.db.config.starterdb.type=
oracle.install.db.config.starterdb.globalDBName=oracle.sunyard
oracle.install.db.config.starterdb.SID=orcl
oracle.install.db.config.starterdb.characterSet=AL32UTF8
oracle.install.db.config.starterdb.memoryOption=true
oracle.install.db.config.starterdb.memoryLimit=512
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.enableSecuritySettings=true
oracle.install.db.config.starterdb.password.ALL=
oracle.install.db.config.starterdb.password.SYS=
oracle.install.db.config.starterdb.password.SYSTEM=
oracle.install.db.config.starterdb.password.SYSMAN=
oracle.install.db.config.starterdb.password.DBSNMP=
oracle.install.db.config.starterdb.control=DB_CONTROL
oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=
oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false
oracle.install.db.config.starterdb.dbcontrol.emailAddress=
oracle.install.db.config.starterdb.dbcontrol.SMTPServer=
oracle.install.db.config.starterdb.automatedBackup.enable=false
oracle.install.db.config.starterdb.automatedBackup.osuid=
oracle.install.db.config.starterdb.automatedBackup.ospwd=
oracle.install.db.config.starterdb.storageType=
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=LED_ASM_SYSTEM
MYORACLESUPPORT_USERNAME=
MYORACLESUPPORT_PASSWORD=
SECURITY_UPDATES_VIA_MYORACLESUPPORT=
DECLINE_SECURITY_UPDATES=true
PROXY_HOST=
PROXY_PORT=
PROXY_USER=
PROXY_PWD=

在这里插入图片描述

cd /oracle/database/
./runInstaller -silent -responseFile /home/oracle/response/db_install.rsp -ignorePrereq

在这里插入图片描述

报错原因：swap分区关闭了打开即可

在这里插入图片描述

/u01/app/oracle/inventory/orainstRoot.sh
/u01/app/oracle/product/11.2.0/root.sh
yum -y install net-tools

vi /home/oracle/response/dbca.rsp

修改参数
GDBNAME = "orcl"
SID = "orcl"
SYSPASSWORD = "oracle"
SYSTEMPASSWORD = "oracle"
SYSMANPASSWORD = "oracle"
DBSNMPPASSWORD = "oracle"
DATAFILEDESTINATION =/u01/app/oracle/oradata
RECOVERYAREADESTINATION=/u01/app/oracle/fast_recovery_area
CHARACTERSET = "ZHS16GBK"
TOTALMEMORY = "1638"

dbca -silent -responseFile /home/oracle/response/dbca.rsp

在这里插入图片描述

实例检查
ps -ef | grep ora_ | grep -v grep
在这里插入图片描述

启动监听
lsnrctl start
在这里插入图片描述

查看监听状态
在这里插入图片描述

sqlplus / as sysdba

startup报错
在这里插入图片描述

解决方法

cp /u01/app/oracle/admin/orcl/pfile/init.ora.21520241602 /u01/app/oracle/product/11.2.0/dbs/
cd /u01/app/oracle/product/11.2.0/dbs/
mv init.ora.21520241602 initoral.ora

在这里插入图片描述

5.下载部署flink

flink下载地址：https://www.apache.org/dyn/closer.lua/flink/flink-1.18.1/flink-1.18.1-bin-scala_2.12.tgz
下载flink sql所需驱动
1.	flink-connector-jdbc-3.1.1-1.17.jar 下载地址：https://repo1.maven.org/maven2/org/apache/flink/flink-connector-jdbc/3.1.1-1.17/  
2.	flink-sql-connector-oracle-cdc-3.0.1.jar 下载地址：https://mvnrepository.com/artifact/com.ververica/flink-sql-connector-oracle-cdc/3.0.1
3.	flink-sql-connector-postgres-cdc-3.0.1.jar 下载地址：https://mvnrepository.com/artifact/com.ververica/flink-sql-connector-postgres-cdc/3.0.1

下载完之后上传flink压缩包并解压

tar -zxvf flink-1.18.1-bin-scala_2.12.tgz
进入flink的lib目录上传三个依赖
cd flink-1.18.1/lib/

在这里插入图片描述

需要修改一下flink配置文件
不然会出现以下情况
在这里插入图片描述

vi flink-1.18.1/conf/flink-conf.yaml

原本是localhost修改为ip

在这里插入图片描述

./flink-1.18.1/bin/start-cluster.sh

访问 192.168.207.193:8081 （默认是8081端口可在配置文件里修改）

在这里插入图片描述

6.实时同步oracle数据到postgresql

数据库先创建一个库,在库里创建表再添加数据

sqlplus / as sysdba
create user ljq identified by linux123;
grant create session,resource to ljq;
sqlplus ljq/linux123@192.168.207.195/orclcreate table players (player_id INT NOT NULL,team_id INT,player_name VARCHAR(255),height FLOAT(53),
PRIMARY KEY (player_id)
);

insert into players (player_id,team_id,player_name,height) values (1001,1001,'韦德','1.93');
insert into players (player_id,team_id,player_name,height) values (1002,1002,'雷吉','1.91');
insert into players (player_id,team_id,player_name,height) values (1003,1003,'安德烈','2.11');
insert into players (player_id,team_id,player_name,height) values (1004,1004,'索恩','2.16');
insert into players (player_id,team_id,player_name,height) values (1005,1005,'兰斯顿','1.88');
insert into players (player_id,team_id,player_name,height) values (1006,1006,'格伦','1.98');
insert into players (player_id,team_id,player_name,height) values (1007,1007,'伊斯梅尔','1.83');
insert into players (player_id,team_id,player_name,height) values (1008,1008,'扎扎','2.11');
insert into players (player_id,team_id,player_name,height) values (1009,1009,'乔恩','2.08');

select * from players;

在这里插入图片描述

格式很乱

解决方法：
set line 320
col player_name for a20

在这里插入图片描述

在pg库创建一个库,在库里创建一个表不插入数据

create database ljq;
\c ljqCREATE TABLE players3 (
player_id INT NOT NULL,
team_id INT,
player_name VARCHAR(255),
height FLOAT(53),
PRIMARY KEY (player_id)
);

启动flink-sql

./flink-1.18.1/bin/sql-client.sh embedded
根据需要同步的数据创建源表
CREATE TABLE nbaplayers (
PLAYER_ID INT,
TEAM_ID INT,
PLAYER_NAME VARCHAR,
HEIGHT FLOAT,
PRIMARY KEY (PLAYER_ID) NOT ENFORCED
) WITH (
'connector' = 'oracle-cdc',
'hostname' = '192.168.207.192',
'port' = '1521',
'username' = 'ljq',
'password' = 'ljq',
'database-name' = 'test',
'schema-name' = 'LJQ',
'table-name' = 'PLAYERS'
);

select * from nbaplayers;

在这里插入图片描述

创建结果表

CREATE TABLE nba (
player_id INT,
team_id INT,
player_name VARCHAR,
height NUMERIC(3,2),
PRIMARY KEY (player_id) NOT ENFORCED
) WITH (
'connector' = 'jdbc',
'url' = 'jdbc:postgresql://192.168.207.193:5432/ljq??currentSchema=public&reWriteBatchedInserts=true',
'username' = 'postgres',
'password' = 'linux123',
'table-name' = 'players3'
);

在这里插入图片描述

执行从源表插入结果表操作，生成同步作业

INSERT INTO nba
SELECT
player_id,
team_id,
player_name,
height
FROM nbaplayers;

在这里插入图片描述

Web端查看
在这里插入图片描述

查看是否同步数据到pg库的players3表
在这里插入图片描述