StarQuest Technical Documents

SQDR Plus: Oracle XStream - Recreate Outbound Server

Last Update:30 Augst 2022
Product: SQDR & SQDR Plus
Version: 5.30 and later
Article ID: SQV00PL073

It is sometimes necesssary to recreate and Outbound Server. Usually it is because of missing archivelogs. There are 3 steps to take to recreate the Outbound Sever.

Step 1: Gather information to restore later

Log into the database (root container CDB$ROOT if PDB) as the XSTRMADMIN or C##XSTRMADMIN (if PDB) user.

Get the name of the outbound server and source database:

SELECT SERVER_NAME, SOURCE_DATABASE FROM ALL_XSTREAM_OUTBOUND_PROGRESS;

Get any non-default values that had been set for the capture and apply processes:

SELECT * FROM DBA_CAPTURE_PARAMETERS WHERE SET_BY_USER = 'YES';

SELECT * FROM DBA_APPLY_PARAMETERS WHERE SET_BY_USER = 'YES';

Step 2: Stop and drop the Outbound Server

BEGIN
DBMS_XSTREAM_ADM.STOP_OUTBOUND(server_name => '&SERVER', force => true);
END;
/

BEGIN
DBMS_XSTREAM_ADM.DROP_OUTBOUND(server_name => '&SERVER');
END;
/

BEGIN
DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
server_name => '&SERVER',
source_database=>'&SOURCEDATABASE',
include_ddl => TRUE);
END;
/

Step 3: Add back in non-default parameters

Now, take data captured from Step 1 above and restore any non-default parameters

To restore a capture parameter:

Example: use_rac_service for Oracle RAC databases

Use the result from this query to enter your capture_name in the code below.

SELECT CAPTURE_NAME FROM DBA_CAPTURE;

ALTER SESSION set container=CDB$ROOT;

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

To restore an apply parameter:

Example: set max_sga_size to 4gb, use the SERVER_NAME output from the query in Step 1 against ALL_XSTREAM_OUTBOUND_PROGRESS

BEGIN
DBMS_XSTREAM_ADM.SET_PARAMETER(
streams_name => '&SERVER_NAME',
streams_type => 'apply',
parameter => 'max_sga_size',
value => '4096');
END;
/