9 Oracle19c打开xstream

最新推荐文章于 2024-07-24 16:58:26 发布

hryyx

最新推荐文章于 2024-07-24 16:58:26 发布

阅读量665

点赞数

分类专栏：数据库实时同步文章标签：数据库 oracle sql

本文链接：https://blog.csdn.net/hryyx/article/details/128194855

版权

数据库实时同步专栏收录该内容

16 篇文章 3 订阅

订阅专栏

本文目标

为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>