Oracle XStream Troubleshooting

Here following we have collected many commands and snippets you can use to check if your setup is healthy or to troubleshoot for errors.

Status checks

New to Xstream? Not receiveing live data changes to Gluesync after having made a commit into your Oracle?

We have collected three useful commands you can run to check if the setup reflects your goals or for any outstanding error that is halting the process.

Capture process status

If you need to check if the setup has been done properly, Oracle gives 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.

If none are running (meaning empty list) or you can’t see the one you’ve just setup it might be something that has to do with the Xstream create capture command or issues with privileges, please checkout again the steps done during the setup.

If the capture is in PAUSED FOR FLOW CONTROL please check the following section Troubleshooting.

Checking if tables are properly under capture

Not able to proceed with the Gluesync setup wizard? Can’t see your tables in the list while navigating with the entity browser?

One of the root reason is that these tables are not under capture and so Gluesync will be unable to hook on change feed belonging to them, since it just doesn’t exist, yet.

To check for that please run the following query:

--- replace "?" with your SCHEMA NAME
SELECT * FROM DBA_CAPTURE_PREPARED_TABLES WHERE TABLE_OWNER = ?

From the list you’ll get you should be able to tell if the table you’re looking for is present or not. If not you should go back to the Xstream setup steps and make sure you add it to the capture. Check also for permissions for Xstream user (not admin) to be able to read it.

Checking for errors

To check if there is any relevant alert instead, you can run this query:

SELECT * FROM DBA_OUTSTANDING_ALERTS;

Xstream administration commands

Add new tables to the capture process

In case you need to add a new table/schema to the capture process you can do it lively just by editing the following snippet.

DECLARE
  tables  DBMS_UTILITY.UNCL_ARRAY;
  schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
  tables(1)  := 'SCHEMA1.ARTICLES';
  schemas(1) := '';
  DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
    server_name     => 'GSXOUT',
    table_names     => tables,
    schema_names    => schemas,
    add             => TRUE
  );
END;

Setting the add field to FALSE will remove the tables passed in the table_names field (in this case the SCHEMA1.ARTICLES table is added)

Consider setting the schema to an empty list (schemas(1) := '';) otherwise all the tables of the declared schema will be added.

Optimizing by Skip/add columns via applying rules

To limit the amount of resources consumed by the capture process, reducing latencies and avoiding records being spilled to disk, consider applying filtering rules to your capture process to discard unwanted table columns to become part of the replication process even if those are then discarded by Gluesync later in the pipeline.

DECLARE
   cols DBMS_UTILITY.LNAME_ARRAY;
BEGIN
     cols(1) := 'COLUMN1';
     cols(2) := 'COLUMN2';
   DBMS_XSTREAM_ADM.ADD_SUBSET_OUTBOUND_RULES(
     server_name => 'GSXOUT',
     table_name => 'SCHEMA1.ARTICLES',
     keep => FALSE,
     column_list => cols);
END;

Also in this case the keep field is used to define what to keep and what not.

For further details, I refer you to the official documentation which you can find here: Oracle DBMS_STREAM_ADM documentation.

Troubleshooting

Here is the list of common errors.

ORA-01031: Insufficient privileges

This means that you’ve executed the DBMS_XSTREAM_ADM.CREATE_OUTBOUND steps 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

This issue means that you are explicitly set a value in either a cdbName or pdbName inside oracle object in your config.json. Since you’re running an Oracle single-tenant instance you have to remove them from the config.

Capture state falls into PAUSED FOR FLOW CONTROL

LCR records are hitting the capture process way too much than what can be sustained by the current resource allocation and records are then spilled to disk instead of being directly processed. One of the most common reason for this is due to potentially having setup all tables present in a schema and the system is not provisioned for such heavy load causing the process to pause for flow control. Sometimes this can be easily resolved by just adding to the capture the tables that are required and/or filtering out non-needed columns from tables. If that’s not your case we suggest for this and other common Xstream-related errors please consider taking a look at this link: Oracle streams errors and this link for self troubleshooting with your DBA team Solutions for PAUSED FOR FLOW CONTROL.

Automatically start Xstream after DB shutdown

If you had to restart your Oracle instance or just put it to maintenance and found that all the capture processes were offline, you can use/adapt the following snippet we wrote for your convenience:

CREATE OR REPLACE TRIGGER xstream_run_at_startup
  AFTER STARTUP ON DATABASE
  BEGIN
    DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
      server_name  => 'gsxout',
      connect_user => 'c##xstrm'
    );

      DBMS_XSTREAM_ADM.START_OUTBOUND(
        server_name => 'gsxout'
      );
  END;

This snippet will allow the Xstream component to hook up on the system startup event and add back the whole capture process so you won’t miss it the next time a restart happens.

Advancing capture processing to specific SCN / timestamp

If you require your Capture process to start from a specific SCN or a timestamp there are two commands that come to help:

DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
   server_name    IN VARCHAR2,
   capture_user   IN VARCHAR2   DEFAULT NULL,
   start_scn      IN NUMBER     DEFAULT NULL,
   start_time     IN TIMESTAMP  DEFAULT NULL
);

Use either the SCN value or the time value to alter the capture and move its cursor to seek it to the desired point.