数据仓库 数据库架构 dba etl工程师 CDC-Oracle 11g环境搭建

CDC-Oracle 11g环境搭建

安装Oracle-docker环境

安装容器

docker run -d -p 1524:1521 -v /opt/oracle11:/data/oracle --name oracle11_1524 registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

环境配置

# 切换到root 用户下

su root

# 密码：helowin

# 编辑profile文件配置ORACLE环境变量

vi /etc/profile

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2

export ORACLE_SID=helowin

export PATH=$ORACLE_HOME/bin:$PATH

# 保存并退出 ：wq

# 接着， 使其生效

source /etc/profile

# 创建软连接

ln -s $ORACLE_HOME/bin/sqlplus /usr/bin

数据库配置

# 切换到oracle 用户

su - oracle

配置Oracle

sqlplus /nolog

conn /as sysdba

-- 修改数据库密码的命令了

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

开启Oracle归档日志

alter system set db_recovery_file_dest_size = 5G;

alter system set db_recovery_file_dest = '/home/oracle/app/oracle/oradata/helowin/recovery_area' scope=spfile;

-- 开启Ogg，这一步测试过程中不生效

alter system set enable_goldengate_replication=true;

-- 重启Oracle

shutdown immediate

startup mount

alter database archivelog;

alter database open;

-- Should now "Database log mode: Archive Mode"

archive log list

startup

开启XStream

-- 创建表空间

CREATE TABLESPACE xstream_adm_tbs DATAFILE '/home/oracle/app/oracle/oradata/helowin/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

-- 创建用户

CREATE USER c##xstrmadmin IDENTIFIED BY xsa DEFAULT TABLESPACE xstream_adm_tbs QUOTA UNLIMITED ON xstream_adm_tbs;

-- 授权

GRANT CREATE SESSION TO c##xstrmadmin;

-- 开启xstream

BEGIN

DBMS_XSTREAM_ADM.GRANT_ADMIN_PRIVILEGE(

grantee => 'c##xstrmadmin',

privilege_type => 'CAPTURE',

grant_select_privileges => TRUE

);

END;

/

DECLARE

tables DBMS_UTILITY.UNCL_ARRAY;

schemas DBMS_UTILITY.UNCL_ARRAY;

BEGIN

tables(1) := NULL;

schemas(1) := 'debezium'; --需要处理的schema

DBMS_XSTREAM_ADM.CREATE_OUTBOUND(

server_name => 'dbzxout', --服务名称

table_names => tables,

schema_names => schemas);

END;

/

开启LogMiner

CREATE TABLESPACE logminer_tbs DATAFILE '/home/oracle/app/oracle/oradata/helowin/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATE USER debezium IDENTIFIED BY 123456 DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs;

-- 系统角色

GRANT SELECT_CATALOG_ROLE TO debezium ;

GRANT EXECUTE_CATALOG_ROLE TO debezium ;

-- 系统权限

GRANT CREATE SESSION TO debezium ;

GRANT FLASHBACK ANY TABLE TO debezium ;

GRANT SELECT ANY TABLE TO debezium ;

GRANT SELECT ANY TRANSACTION TO debezium ;

GRANT CREATE TABLE TO debezium ;

GRANT LOCK ANY TABLE TO debezium ;

GRANT ALTER ANY TABLE TO debezium ;

GRANT CREATE SEQUENCE TO debezium ;

-- 对象权限

GRANT EXECUTE ON DBMS_LOGMNR TO debezium ;

GRANT EXECUTE ON DBMS_LOGMNR_D TO debezium ;

GRANT EXECUTE ON DBMS_METADATA TO debezium ;

GRANT SELECT ON V_$LOG TO debezium ;

GRANT SELECT ON V_$LOG_HISTORY TO debezium ;

GRANT SELECT ON V_$LOGMNR_LOGS TO debezium ;

GRANT SELECT ON V_$LOGMNR_CONTENTS TO debezium ;

GRANT SELECT ON V_$LOGMNR_PARAMETERS TO debezium ;

GRANT SELECT ON V_$LOGFILE TO debezium ;

GRANT SELECT ON V_$ARCHIVED_LOG TO debezium ;

GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO debezium ;

GRANT SELECT ON V_$DATABASE to debezium ;

-- 开启补充日志

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER TABLE debezium.person ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

撤销权限脚本

REVOKE ALTER ANY TABLE FROM debezium ;

REVOKE CREATE SEQUENCE FROM debezium ;

REVOKE CREATE TABLE FROM debezium ;

精彩文章

 您阅读本篇文章共花了：