Setup Xstream into Oracle database single-tenant
Oracle database version 220.127.116.11 (11.2g) and above configured as single-tenant
If you’re looking for support on a multi-tenant installation please have a look here at this link.
sys into your database. Then execute the following commands:
CREATE TABLESPACE xstream_adm_tbs DATAFILE '/u01/app/oracle/oradata/orcl/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Then, again as
sys into your database, we’re going to create
xstream admin and grant permissions, execute:
CREATE USER xstreamadmin IDENTIFIED BY xsa DEFAULT TABLESPACE xstream_adm_tbs QUOTA UNLIMITED ON xstream_adm_tbs; GRANT CREATE SESSION TO xstreamadmin; BEGIN DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE( grantee => 'xstreamadmin', privilege_type => 'CAPTURE', grant_select_privileges => TRUE ); END;
again, this time we’re going to create
xstream user and grant permissions, execute:
CREATE TABLESPACE xstream_tbs DATAFILE '/u01/app/oracle/oradata/orcl/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; CREATE USER xstream IDENTIFIED BY xs DEFAULT TABLESPACE xstream_tbs QUOTA UNLIMITED ON xstream_tbs; GRANT CREATE SESSION TO xstream; GRANT CREATE TABLE TO xstream; GRANT SELECT ON V_$DATABASE to xstream; GRANT FLASHBACK ANY TABLE TO xstream;
Now it’s the turn to add logging capabilities to the tables involved in your replication process.
We’re going to alter all tables involved and grand permissions to
xstream user, again as
-- replace me with your tables list ALTER TABLE SCHEMA1.MYFIRSTTABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA1.MYFIRSTTABLE TO xstream; ALTER TABLE SCHEMA1.MYSECONDTABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA1.MYSECONDTABLE TO xstream; ALTER TABLE SCHEMA2.MYTHIRDTABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA2.MYTHIRDTABLE TO xstream; ALTER TABLE SCHEMA2.MYFOURTHTABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA2.MYFOURTHTABLE TO xstream;
This final step covers the setup of Xstream outbound stream. Please keep in mind that:
tables: are the list of tables that you are considering to enable the CDC into, keeping
NULLas per the example means that the CDC will be enabled in all the tables present in the schema specified there after. You won’t keep that for any production workload/environment;
schemas: the schemas where the tables are belonging to;
server_name: the name of your Xstream server, for a GlueSync standard setup we are going to
capture_name: the name of your data capture output, for a GlueSync standard setup we are going to
To proceed with the following query statements please make sure you connect as
xstream admin user run the following query making the changes accordingly to your needs (tables and schemas per your requirements):
DECLARE tables DBMS_UTILITY.UNCL_ARRAY; schemas DBMS_UTILITY.UNCL_ARRAY; BEGIN -- replace me with your tables list tables(1) := 'SCHEMA1.MYFIRSTTABLE'; tables(2) := 'SCHEMA1.MYSECONDTABLE'; tables(3) := 'SCHEMA2.MYTHIRDTABLE'; tables(4) := 'SCHEMA2.MYFOURTHTABLE'; -- replace me with your schemas list schemas(1) := 'SCHEMA1'; schemas(2) := 'SCHEMA1'; schemas(3) := 'SCHEMA2'; schemas(4) := 'SCHEMA2'; DBMS_XSTREAM_ADM.CREATE_OUTBOUND( server_name => 'gsxout', capture_name => 'gluesync_capture', table_names => tables, schema_names => schemas ); END; BEGIN DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE( capture_name => 'gluesync_capture', attribute_name => 'username' ); END; BEGIN DBMS_XSTREAM_ADM.ALTER_OUTBOUND( server_name => 'gsxout', connect_user => 'xstream' ); END;
That’s it! Happy CDC :-)
If you need to check if the setup has been done properly, Oracle give us a snippet to see if Xstream is up & running. To perform the check you need to run this query:
SELECT CAPTURE_NAME, STATE FROM V_$XSTREAM_CAPTURE;
this query will output the list of outbound server(s) running with their current queried status.
To check if there is any relevant alert instead, you can run this query:
SELECT * FROM DBA_OUTSTANDING_ALERTS;
Here following the list of common errors.
ORA-01031: Insufficient privileges: means that you’ve executed the
DBMS_XSTREAM_ADM.CREATE_OUTBOUNDsteps with the wrong user (maybe when still logged in as
sys as sysdba). Delete it at redo the steps following the instructions above;
ORA-02248: Invalid option for ALTER SESSION: means that you are explicy set a value in either a
oracleobject in your config.json. Since you’re running an Oracle single-tenant instance you have to remove them from the config.