XSTREAMS配置

数据库准备

创建用户

容器模式

创建数据库用户

CREATE TABLESPACE ZCTBS DATAFILE '数据文件路径/ZCTBS.DBF' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
CREATE USER ZCBUS_USER IDENTIFIED BY ZCBUS_PASSWD DEFAULT TABLESPACE ZCTBS QUOTA UNLIMITED ON ZCTBS;

权限

GRANT CREATE SESSION TO ZCBUS_USER;
GRANT RESOURCE TO ZCBUS_USER;
GRANT SELECT ANY TABLE TO ZCBUS_USER;
GRANT SELECT ANY DICTIONARY TO ZCBUS_USER;
GRANT SELECT_CATALOG_ROLE TO ZCBUS_USER;
注：12C的CDB模式zcbus_user要以c##开头

非容器模式

需要创建CDB用户/PDB用户两种

CDB用于分析日志

创建cdb用户(web配置页面中集群管理的db_type,db_name,host,user,password等信息添加cdb信息)
SQLPLUS SYS/PASSWORD@HOST:PORT/sid AS SYSDBA
CREATE USER c##ZCBUS_USER IDENTIFIED BY ZCBUS_USER;
ALTER USER C##ZCBUS_USER QUOTA 100M USERS;
GRANT CREATE SEESION TO C##ZCBUS_USER container=all;
GRANT RESOURCE TO C##ZCBUS_USER container=all;
GRANT SELECT ANY TABLE TO C##ZCBUS_USER container=all;
GRANT SELECT ANY DICTIONARY TO C##ZCBUS_USER container=all;
GRANT SELECT ON SYS.USER$ TO C##ZCBUS_USER container=all;
GRANT SELECT ON SYS.COL$ TO C##ZCBUS_USER container=all;
GRANT SELECT ON SYS.OBJ$ TO C##ZCBUS_USER container=all;
GRANT SELECT_CATALOG_ROLE TO C##ZCBUS_USER container=all;

PDB用户PDB中数据导出

--创建pdb用户(web配置页面中集群管理的source_db_name,source_host,source_user,source_passwd填写pdb信息)
aLTER SESSION SET CONTAINER=PDB01;
CREATE USER ZCBUS_USER IDENTIFIED BY ZCBUS_PASSWD DEFAULT TABLESPACE ZCTBS QUOTA UNLIMITED ON ZCTBS;
GRANT CREATE SESSION TO ZCBUS_USER;
GRANT RESOURCE TO ZCBUS_USER;
GRANT SELECT ANY TABLE TO ZCBUS_USER;
GRANT SELECT ANY DICTIONARY TO ZCBUS_USER;
GRANT SELECT ON SYS.USER$ TO ZCBUS_USER;
GRANT SELECT ON SYS.COL$ TO ZCBUS_USER;
GRANT SELECT ON SYS.OBJ$ TO ZCBUS_USER;
GRANT SELECT_CATALOG_ROLE TO ZCBUS_USER;
GRANT FLASHBACK ANY TABLE TO ZCBUS_USER;
GRANT LOCK ANY TABLE TO ZCBUS_USER;
GRANT SELECT_CATALOG_ROLE TO ZCBUS_USER;

发布服务配置

1. 数据库配置

SQLPLUS / AS SYSDBA
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE;
ALTER SYSTEM SET OPEN_CURSORS = 3000;
BEGIN
   DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
      GRANTEE                 => 'ZCBUS_USER',
      PRIVILEGE_TYPE          => 'CAPTURE',
      GRANT_SELECT_PRIVILEGES => TRUE);
END;
/

#ENABLE_GG_XSTREAM_FOR_STREAMS
BEGIN 
        DBMS_XSTREAM_ADM.ENABLE_GG_XSTREAM_FOR_STREAMS(TRUE);
END;

12C及以上 CDB模式配置：

BEGIN
   DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
      GRANTEE                 => 'C##ZCBUS_USER',
      PRIVILEGE_TYPE          => 'CAPTURE',
      GRANT_SELECT_PRIVILEGES => TRUE,
      CONTAINER               => 'ALL');
END;
/

b）开启补充加日志

SQLPLUS / AS SYSDBA
ALTER TABLE 数据库名.表名 ADD SUPPLEMENTAL LOG DATA(ALL) COLUMNS; 表级别
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 库级别最小日志

###根据实际情况选择添加附加日志级别
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(ALL) COLUMNS; 库级别

c）使用创建用户连接进行数据参数配置
sqlplus zcbus_user/zcbus_passwd
注：12C的CDB模式zcbus_user要以c##开头【CDB用户】
d）创建xout服务【创建服务的时候，需要所有的PDB处于OPEN状态才可以（可以视情况临时开启所有关闭的PDB）】

DECLARE
    TABLES  DBMS_UTILITY.UNCL_ARRAY;
    SCHEMAS DBMS_UTILITY.UNCL_ARRAY;
  BEGIN
TABLES(1) :='ZCBUS_USER.TAB1'; 
    SCHEMAS(1) := NULL;
    DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
    SERVER_NAME     =>  'XOUT',
    TABLE_NAMES     =>  TABLES,
    SCHEMA_NAMES    =>  SCHEMAS);
END;
/

调整优化参数

DECLARE
  CURSOR C_CAP IS SELECT CAPTURE_NAME FROM DBA_CAPTURE;
 BEGIN
  FOR C IN C_CAP LOOP
    DBMS_CAPTURE_ADM.SET_PARAMETER(CAPTURE_NAME=>C.CAPTURE_NAME,
PARAMETER=>'INLINE_LOB_OPTIMIZATION',VALUE=>'y');
DBMS_CAPTURE_ADM.ALTER_CAPTURE(CAPTURE_NAME=>C.CAPTURE_NAME,
CHECKPOINT_RETENTION_TIME=>1/24);
  END LOOP;
 END;
 /

• 至少添加一个表，用户名，表名可以不存在【保证服务正常】

ZCBUS准备

1. 添加发布节点

• 资源管理-发布节点
    添加发布节点,选择运行容器位置
    配置发布数据库配置信息【ORACLE】即可