9 Oracle19c打开xstream
本文目标
为Oracle19c打开xstream，为了让Debezium能够读取日志变化
参考文章
https://support.huaweicloud.com/usermanual-roma/fdi-ug-190624013.html
打开归档
修改管理员密码
[oracle@hostyyx ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 5 16:18:04 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter user system identified by manager;
User altered.
SQL> alter user sys identified by manager;
User altered.
用sys登录数据库修改配置
[oracle@hostyyx ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 5 16:23:11 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> connect sys/manager@192.168.31.224/orclcdb as sysdba;
Connected.
SQL> alter system set enable_goldengate_replication=true;
System altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
SQL> alter system set db_recovery_file_dest_size = 10G;
System altered.
SQL> alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE.
SQL> exit
启动数据库
[oracle@hostyyx ~]$ sqlplus / as sysdba;
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 5 16:25:12 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1593832664 bytes
Fixed Size 9135320 bytes
Variable Size 1006632960 bytes
Database Buffers 570425344 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter pluggable database ORCLPDB1 OPEN;
Pluggable database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL>
至此成功打开归档日志
打开XStream
创建xstream管理员及赋权
CREATE TABLESPACE xstream_adm_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
alter session set container = ORCLPDB1;
CREATE TABLESPACE xstream_adm_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
alter session set container = CDB$ROOT;
CREATE USER c##xstrmadmin IDENTIFIED BY xstrmadmin DEFAULT TABLESPACE xstream_adm_tbs QUOTA UNLIMITED ON xstream_adm_tbs CONTAINER=ALL;
GRANT CREATE SESSION, SET CONTAINER TO c##xstrmadmin CONTAINER=ALL;
BEGIN
DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'c##xstrmadmin',
privilege_type => 'CAPTURE',
grant_select_privileges => TRUE,
container => 'ALL'
);
END;
/
-- 注意最后是/执行多行
创建xstream账号及赋权
[oracle@hostyyx ~]$ sqlplus sys/manager@192.168.31.224:1521/ORCLCDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 5 16:46:40 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> CREATE TABLESPACE xstream_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Tablespace created.
SQL> alter session set container = ORCLPDB1;
Session altered.
SQL> CREATE TABLESPACE xstream_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Tablespace created.
SQL> alter session set container = CDB$ROOT;
Session altered.
SQL> CREATE USER c##xstrm IDENTIFIED BY xstrm DEFAULT TABLESPACE xstream_tbs QUOTA UNLIMITED ON xstream_tbs CONTAINER=ALL;
User created.
SQL> GRANT CREATE SESSION TO c##xstrm CONTAINER=ALL;
Grant succeeded.
SQL> GRANT SET CONTAINER TO c##xstrm CONTAINER=ALL;
Grant succeeded.
SQL> GRANT SELECT ON V_$DATABASE to c##xstrm CONTAINER=ALL;
Grant succeeded.
SQL> GRANT FLASHBACK ANY TABLE TO c##xstrm CONTAINER=ALL;
Grant succeeded.
SQL> GRANT SELECT ANY TABLE to c##xstrm CONTAINER=ALL;
Grant succeeded.
SQL> GRANT LOCK ANY TABLE TO c##xstrm CONTAINER=ALL;
Grant succeeded.
SQL> grant select_catalog_role to c##xstrm CONTAINER=ALL;
Grant succeeded.
SQL> alter database add supplemental log data (all) columns;
Database altered.
SQL> quit;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@hostyyx ~]$
创建XStream出站服务器
[oracle@hostyyx ~]$ sqlplus c##xstrmadmin/xstrmadmin@192.168.31.224/orclcdb
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 5 16:52:26 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
DECLARE
tables DBMS_UTILITY.UNCL_ARRAY;
schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
tables(1) := NULL;
schemas(1) := 'YINYX';
DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
server_name => 'xstrmout',
table_names => tables,
schema_names => schemas);
END;
/
为出站服务器赋权
[oracle@hostyyx ~]$ sqlplus sys/manager@192.168.31.224:1521/ORCLCDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 5 17:18:04 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> BEGIN
DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
server_name => 'xstrmout',
connect_user => 'c##xstrm'
);
END;
/
2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL>
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· 单线程的Redis速度为什么快？
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 展开说说关于C#中ORM框架的用法！