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;
/

 

 



DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.