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 ;