StarQuest Technical Documents

SQDR Plus: Oracle XStream Support

Last Update: 3 October 2022
Product: SQDR Plus
Version: 5.30 and later
Article ID: SQV00PL062

Abstract

SQDR Plus obtains change data from Oracle databases using either of the following methods:

• LogMiner
  
• XStream Out

The use of XStream offers better performance, but also involves additional setup on the Oracle system and licensing considerations. Contact StarQuest for licensing details; you will need to provide information about the Oracle host system (CPU count and processor type) by following the instructions in Oracle XStream Licensing.

Solution

StarQuest provides XStream setup scripts to be used by an Oracle system administrator to prepare an Oracle system to use the XStream support of SQDR Plus 5.30 & later.

This procedure does not take the place of the Oracle Host Preparation Scripts that are optionally used to create agent SQDR Plus users and objects in advance of creating the agent; these instructions assume that either those scripts will also be used, or that the agent will be created with the typical method of supplying SYS AS SYSDBA credentials in the Add Agent Wizard as described in the Quick Start Guide. The Xstream scripts should be run before creating the agent, but if you choose to use both set of scripts (Xstream preparation and the optional scripts for creating agent SQDR Plus users and objects), the scripts can be run in any order.

Instructions for each script are contained in comments at the start of the script.

There are 2 subdirectories:

• nonPDB - scripts to be run in a non-PDB environment
• PDB - scripts to be run in a PDB environment. The XSTRMADMIN user is a common user (e.g. C##XSTRMADMIN)
  The supplied example assumes that there is only one PDB of interest. If you wish to create agents for additional PDBs, use the "step1a.sql" script to repeat the PDB-specific steps in additional PDB's.
  
  
• Overview
• Apply applicable Oracle patches
• Procedure
  
  • On the Oracle source system
    
  • On the SQDR Plus system, using SQDR Control Center
    
  • Converting an existing Agent (using LogMiner) to use Xstream
    
• Performance
  
• SQDR Plus Reference
  
• RAC Environment
  
• Troubleshooting
  • Diagnostic Information from Oracle Source
  • Insufficient Privileges error
    
• Oracle documentation

Overview

1. Verify the system parameters enable_goldengate_replication and streams_pool_size.
2. Edit & run script step1.sql as SYS AS SYSDBA to create the XSTRMADMIN user and xstream_tbs tablespace and perform various grants. In the case of a PDB scenario, the user is a common user (C##XSTRMADMIN) and grants are performed in either the root or the PDB, as documented in the script.
3. Edit & run script step2.sql as the XSTRMADMIN user to create and start the OUTBOUND server.
4. After the OUTBOUND server is started, use step3.sql to revoke DBA privileges from the XSTRMADMIN user.
5. Optional: run the Oracle Host Preparation Scripts as SYS AS SYSDBA to create agent SQDR Plus users and objects in advance of creating the agent. For ease of setup, you can instead allow the Add Agent Wizard to create the users and objects.
6. Create a new Oracle agent using the Add Agent Wizard of SQDR Control Center, supplying the XStream properties.
7. Start the Agent and verify that XStream support is functioning.
8. Optional: edit and run the script tuning.sql as the XSTRMADMIN user to adjust performance-related parameters.

Oracle Patches

For Oracle 19.12, 19.13, 19.14, 19.15, 21.4 or 21.5

apply the patch for

Bug 34010877 - ORA-4031 on streams pool area with multiple subpools (Doc ID 34010877.8)

For Oracle 19.12, 19.14, 19.15 on Linux86-64 or Oracle 19.16 on IBM AIX on Power Systems (64-bit)

apply the patch for

Bug 34470389 XSTREAM CLIENT STOPS RETRIEVING CHANGES WITH PROPAGATION RECEIVER WAITING FOR MEMORY

See Locating Oracle Patches for details.

Procedure

On the Oracle source system:

1. Verify that the system parameter enable_goldengate_replication is set to TRUE using either

SQL> show parameter enable_goldengate_replication;
or
SQL> select NAME, VALUE from V$PARAMETER where NAME = 'enable_goldengate_replication';

If necessary, set it true:
SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;

It is not necessary to restart Oracle.

2. Verify that streams_pool_size system setting is at least 6GB; increase it if necessary:
   
   SQL> Show parameter streams_pool_size
   SQL> alter system set streams_pool_size=6G scope=both

It is not necessary to restart Oracle.

3. Determine if the XSTRMADMIN user and XSTREAM_TBS tablespace exist. This might be the case if you have already created an SQDR Plus agent, and are now creating additional agents to handle more than one PDB's, or if you have more than one SQDR system connecting to your Oracle system.

SELECT USERNAME, ACCOUNT_STATUS FROM dba_users WHERE USERNAME like '%XSTRMADMIN%';

SELECT TABLESPACE_NAME, STATUS FROM dba_tablespaces WHERE UPPER(TABLESPACE_NAME) like '%XSTREAM_TBS%';

If the user & tablespace already exist:

• For a non-PDB scenario, skip step1.sql, and use step2.sql to configure a new OUTBOUND server for your new agent.
• For a PDB scenario, skip step1.sql, use the step1a.sql script to create the tablespace and issue the various grants in the PDB, then use step2.sql to configure a new OUTBOUND server.
  

4. Edit each script as directed.
5. Start sqlplus or SQL Developer and log on as a user with sufficient authority to perform the operations e.g. SYS AS SYSDBA.
6. Run the first script, examining the output for errors. This script needs to be run only once per system.

SQL> @step1

7. Next log on to sqlplus as the XSTRMADMIN user created in step1.sql, then run step2.sql to create and start the OUTBOUND server.
   VERY IMPORTANT: If you are configuring multiple agents, modify and run this script multiple times to create a unique OUTBOUND server for each agent.

SQL> @step2

8. After the OUTBOUND server is created and started, log on to sqlplus as SYS AS SYSDBA and run step3.sql to revoke DBA rights from the XSTRMADMIN user.

SQL> @step3

On the SQDR Plus system, using SQDR Control Center:

1. Create a new Oracle agent using the Add Agent Wizard.
2. On the 2nd panel, select the checkbox for "Use Xstream-Out".
3. Supply:
   XStream-Out Name - this is the SERVER name specified in step2.sql
   XStream User - this is the user created in step1.sql
   XStream Password
4. Start the agent.
5. Verify that XStream support is functioning:

• Summary will show Common as running with a Log Position.
• Subscriptions will show the internal subscription for SQ_BASELINES.
• The Diagnostics log will contain entries that reference XStreamsReader.

To convert an existing Agent (using LogMiner) to use Xstream:

1. Choose a quiet time when there are no changes occurring on the source system (or be prepared to run new baselines).
2. Use the XStream setup scripts to prepare the host
3. Pause any I/R groups in Data Replicator Manager
4. In SQDR Control Center, select the Configuration panel of the Agent and edit (adding if necessary) the following properties:
   xstream - set to true
   xstreamUser
   xstreamPassword (the password will be encrypted when the configuration is saved)
   xstreamServer
   xstreamFilter - set to true
   
5. Save the configuration. This will restart the Agent.
6. Examine diagnostics and verify that XStreamsReader is now being referenced.
7. Resume the paused I/R groups

Moving from LogMiner to XStream is similar to doing a Warm Start; there is no guarantee that the log position will be maintained. We recommend running TargetChecker, and consider running new baselines if there is any concern that changes may have been missed.

PERFORMANCE

A script tuning.sql is provided to modify performance-related parameters. The contents are suggested values that we have found useful. To adjust these parameters, edit this script and run it as the XSTRMADMIN user created in step1.sql. This will restart the OUTBOUND server.

SQL> @tuning

REFERENCE

These are the XStream-related properties in the Agent configuration. See the Reference and Troubleshooting/Configuration page in the SQDR Plus Control Center Help for details.

xstream (true/false)
xstreamUser
xstreamPassword
xstreamServer
xstreamFilter - set to true
xstreamUrl - only use if XStream out should be read from backup system

 

RAC ENVIRONMENT

If you are using a RAC environment, set the use_rac_service parameter:

BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => '&CAPTURENAME',
parameter => 'use_rac_service',
value => 'Y');
END;
/

TROUBLESHOOTING

Diagnostic Information from Oracle Source

See SQDR Plus: Oracle XStream Diagnostics for a script to run to gather diagnostic details.

Insufficient Privileges error

Symptom: The following error appeared in the agent diagnostics:

XStreamsReader.initialize: Could not start XStream from SCN: 344537582.
oracle.streams.StreamsException: ORA-26827: Insufficient privileges to attach to XStream outbound server "SRV123".

Action: Query DBA_XSTREAM_OUTBOUND view to get the connect_user value for the specified outbound server.

SQL> SELECT SERVER_NAME,CONNECT_USER FROM DBA_XSTREAM_OUTBOUND;

If the value is SYS rather than the expected XSTRMADMIN, it is likely that step2.sql was run as user SYS rather than the XSTRMADMIN user. Run cleanup0.sql (as SYS) to restore DBA rights for the XSTRMADMIN user, run cleanup1.sql (as the XSTRMADMIN user) to stop and drop the outbound server, and run step2.sql (as the XSTRMADMIN user) to recreate and start it.

Multiple agents using the same XStream OUTBOUND server affects Oracle Database

Symptom: Remote connection requests to the Oracle database failed with the error Connection Dead. After restarting Oracle, the system worked for a while but the symptom re-appeared after a few hours.

Cause: Multiple SQDR Plus agents were configured to use the same XStream OUTBOUND server. This not only caused replication failures, but it created many inactive (zombie) processes. After a while, the Oracle DBMS system ran out of processes and the alert log showed ORA-00020: maximum number of processes (320) exceeded, and the listener was unable to establish new connections.

Solution: Configure a unique XStream OUTBOUND server for each agent.

Diagnostic Scripts

Use the following scripts to gather information about streams pool and Xstream configuration; send the output to StarQuest support.

config_xstream.sql
xstream_streams_pool.sql

ORA-600 error with argument knacpft_ProcessFetchedTxns250

If you see this error in the Oracle alert log:

XStream APPLY AP01 for XSTRMAGENT with pid=736, OS id=541310 stopped
2022-06-07T08:36:10.781780-07:00
Errors in file /u01/app/oracle/diag/rdbms/jdepd/jdepd1/trace/jdepd1_ap01_541310.trc:
ORA-00600: internal error code, arguments: [knacpft_ProcessFetchedTxns250], [], [], [], [], [], [], [], [], [], [], []

And you are running Oracle Database 19.15.0.0.0 DBRU, apply the patch for bug 32338220:

XF21.3SEC_OLS2 - TRC - KNACPFT_PROCESSFETCHEDTXNS - ORA-600 [KNACPFT_PROCESSFETCHEDTXNS250](Patch 32338220)
Linux x86-64 for Oracle Database 19.15.0.0.0 DBRU

This patch will also be part of DB 19 DBRU (October 2022) and is also in DB 21.3.

Oracle documentation

Monitoring XStream Out (Oracle 11.2)
Monitoring XStream Out (Oracle 12.2)
Monitoring XStream Out (Oracle 19.1)