This chapter provides instructions for performing full database export/import in an Oracle Streams environment. This chapter also provides instructions for removing an Oracle Streams configuration.
The following topics describe Oracle Streams management tasks:
Each task described in this chapter should be completed by an Oracle Streams administrator that has been granted the appropriate privileges, unless specified otherwise.
Oracle Streams Replication Administrator's Guide for information about creating an Oracle Streams administrator
21.1 Performing Full Database Export/Import in an Oracle Streams Environment
This section describes how to perform a full database export/import on a database that is running one or more Oracle Streams capture processes, propagations, or apply processes. These instructions pertain to a full database export/import where the import database and export database are running on different computers, and the import database replaces the export database. The global name of the import database and the global name of the export database must match. These instructions assume that both databases already exist.
If you want to add a database to an existing Oracle Streams environment, then do not use the instructions in this section. Instead, see Oracle Streams Replication Administrator's Guide.
Complete the following steps to perform a full database export/import on a database that is using Oracle Streams:
If the export database contains any destination queues for propagations from other databases, then stop each propagation that propagates messages to the export database. You can stop a propagation using the
STOP_PROPAGATIONprocedure in the
To complete this step, you might need to re-create the database links used by these propagation jobs or modify your Oracle networking files at the databases that contain the source queues.
Notify all users to stop making data manipulation language (DML) and data definition language (DDL) changes to the export database, and wait until these changes have stopped.
Make a note of the current export database system change number (SCN). You can determine the current SCN using the
GET_SYSTEM_CHANGE_NUMBERfunction in the
DBMS_FLASHBACKpackage. For example:
SET SERVEROUTPUT ON SIZE 1000000 DECLARE current_scn NUMBER; BEGIN current_scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; DBMS_OUTPUT.PUT_LINE('Current SCN: ' || current_scn); END; /
In this example, assume that current SCN returned is
After completing this step, do not stop any capture process running on the export database. A later step in this procedure instructs you to use the
V$STREAMS_CAPTUREdynamic performance view to ensure that no DML or DDL changes were made to the database after Step 3. The information about a capture process in this view is reset if the capture process is stopped and restarted.
For the check in the later step to be valid, this information should not be reset for any capture process. To prevent a capture process from stopping automatically, you might need to set the
time_limitcapture process parameters to
INFINITEif these parameters are set to another value for any capture process.
If any downstream capture processes are capturing changes that originated at the export database, then ensure that the log file containing the SCN determined in Step 4 has been transferred to the downstream database and added to the capture process session. See "Displaying the Registered Redo Log Files for Each Capture Process" for queries that can determine this information.
If the export database is not running any apply processes, and is not propagating messages, then start the full database export now. Ensure that the
FULLexport parameter is set to
yso that the required Oracle Streams metadata is exported.
If the export database is running one or more apply processes or is propagating messages, then do not start the export and proceed to the next step.
If the export database is the source database for changes captured by any capture processes, then complete the following steps for each capture process:
Wait until the capture process has scanned past the redo record that corresponds to the SCN determined in Step 4. You can view the SCN of the redo record last scanned by a capture process by querying the
CAPTURE_MESSAGE_NUMBERcolumn in the
V$STREAMS_CAPTUREdynamic performance view. Ensure that the value of
CAPTURE_MESSAGE_NUMBERis greater than or equal to the SCN determined in Step 4 before you continue.
In SQL*Plus, connect to the database as the Oracle Streams administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Monitor the Oracle Streams environment until the apply process at the destination database has applied all of the changes from the capture database. For example, if the name of the capture process is
capture, the name of the apply process is
apply, the global name of the destination database is
dest.example.com, and the SCN value returned in Step 4 is
7000000, then run the following query at the capture database:
SELECT cap.ENQUEUE_MESSAGE_NUMBER FROM V$STREAMS_CAPTURE cap WHERE cap.CAPTURE_NAME = 'CAPTURE' AND cap.ENQUEUE_MESSAGE_NUMBER IN ( SELECT DEQUEUED_MESSAGE_NUMBER FROM V$STREAMS_APPLY_READER@dest.example.com reader, V$STREAMS_APPLY_COORDINATOR@dest.example.com coord WHERE reader.APPLY_NAME = 'APPLY' AND reader.DEQUEUED_MESSAGE_NUMBER = reader.OLDEST_SCN_NUM AND coord.APPLY_NAME = 'APPLY' AND coord.LWM_MESSAGE_NUMBER = coord.HWM_MESSAGE_NUMBER AND coord.APPLY# = reader.APPLY#) AND cap.CAPTURE_MESSAGE_NUMBER >= 7000000;
When this query returns a row, all of the changes from the capture database have been applied at the destination database, and you can move on to the next step.
If this query returns no results for an inordinately long time, then ensure that the Oracle Streams clients in the environment are enabled by querying the
STATUScolumn in the
DBA_CAPTUREview at the source database and the
DBA_APPLYview at the destination database. You can check the status of the propagation by running the query in "Displaying Information About the Schedules for Propagation Jobs".
If an Oracle Streams client is disabled, then try restarting it. If an Oracle Streams client will not restart, then troubleshoot the environment using the information in Identifying Problems in an Oracle Streams Environment.
The query in this step assumes that a database link accessible to the Oracle Streams administrator exists between the capture database and the destination database. If such a database link does not exist, then you can perform two separate queries at the capture database and destination database.
Verify that the enqueue message number of each capture process is less than or equal to the SCN determined in Step 4. You can view the enqueue message number for each capture process by querying the
ENQUEUE_MESSAGE_NUMBERcolumn in the
V$STREAMS_CAPTUREdynamic performance view.
However, if the enqueue message number of any capture process is higher than the SCN determined in Step 4, then one or more DML or DDL changes were made after the SCN determined in Step 4, and these changes were captured and enqueued by a capture process. In this case, perform all of the steps in this section again, starting with Step 1.
For this verification to be valid, each capture process must have been running uninterrupted since Step 4.
If any downstream capture processes captured changes that originated at the export database, then drop these downstream capture processes. You will re-create them in a step later in this procedure.
If the export database has any propagations that are propagating messages, then stop these propagations using the
STOP_PROPAGATIONprocedure in the
If the export database is running one or more apply processes, or is propagating messages, then start the full database export now. Ensure that the
FULLexport parameter is set to
yso that the required Oracle Streams metadata is exported. If you already started the export in Step 6, then proceed to Step 11.
When the export is complete, transfer the export dump file to the computer running the import database.
Perform the full database import. Ensure that the
FULLimport parameters are both set to
yso that the required Oracle Streams metadata is imported. The default setting is
STREAMS_CONFIGURATIONimport parameter. Also, ensure that no DML or DDL changes are made to the import database during the import.
If any downstream capture processes are capturing changes that originated at the database, then make the necessary changes so that log files are transferred from the import database to the downstream database. See Oracle Streams Replication Administrator's Guide for more information.
Re-create downstream capture processes:
Re-create any downstream capture processes that you dropped in Step 8, if necessary. These dropped downstream capture processes were capturing changes that originated at the export database. Configure the re-created downstream capture processes to capture changes that originate at the import database.
Re-create in the import database any downstream capture processes that were running in the export database, if necessary. If the export database had any downstream capture processes, then those downstream capture processes were not exported.
Oracle Streams Replication Administrator's Guide for information about configuring a capture process
If any local or downstream capture processes will capture changes that originate at the database, then, at the import database, prepare the database objects whose changes will be captured for instantiation. See Oracle Streams Replication Administrator's Guide for information about preparing database objects for instantiation.
Let users access the import database, and shut down the export database.
If you reset the value of a
time_limitcapture process parameter in Step 4, then, at the import database, reset these parameters to their original settings.
21.2 Removing an Oracle Streams Configuration
You run the
REMOVE_STREAMS_CONFIGURATION procedure in the
DBMS_STREAMS_ADM package to remove an Oracle Streams configuration at the local database.
Running this procedure is dangerous. You should run this procedure only if you are sure you want to remove the entire Oracle Streams configuration at a database.
To remove the Oracle Streams configuration at the local database, run the following procedure while connected to the database as the Oracle Streams administrator:
After running this procedure, drop the Oracle Streams administrator at the database, if possible.
Oracle Database PL/SQL Packages and Types Reference for detailed information about the actions performed by the