This chapter provides instructions for performing full database export/import in a Streams environment. This chapter also provides instructions for removing a Streams configuration.
This chapter contains these topics:
Each task described in this chapter should be completed by a Streams administrator that has been granted the appropriate privileges, unless specified otherwise.
See Also:"Configuring a Streams Administrator"
This section describes how to perform a full database export/import on a database that is running one or more 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. The export/import described in this section can be performed using Data Pump Export/Import utilities or the original Export/Import utilities.
Note:If you want to add a database to an existing 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 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_PROPAGATION procedure 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.
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. Step 7c instructs you to use the
V$STREAMS_CAPTURE dynamic 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 Step 7c 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_limit capture process parameters to
infinite if 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 make sure 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 user-enqueued messages, then start the full database export now. Make sure that the
FULL export parameter is set to
y so that the required Streams metadata is exported.
If the export database is running one or more apply processes or is propagating user-enqueued 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_NUMBER column in the
V$STREAMS_CAPTURE dynamic performance view. Make sure the value of
CAPTURE_MESSAGE_NUMBER is greater than or equal to the SCN determined in Step 4 before you continue.
Monitor the 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.net, and the SCN value returned in Step 4 is
7000000, then run the following query at the capture database:
CONNECT strmadmin/strmadminpw 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.net reader, V$STREAMS_APPLY_COORDINATOR@dest.net 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 make sure the Streams clients in the environment are enabled by querying the
STATUS column in the
DBA_CAPTURE view at the source database and the
DBA_APPLY view at the destination database. You can check the status of the propagation by running the query in "Displaying the Schedule for a Propagation Job".
If a Streams client is disabled, then try restarting it. If a Streams client will not restart, then troubleshoot the environment using the information in Chapter 18, "Troubleshooting a Streams Environment".
The query in this step assumes that a database link accessible to the 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_NUMBER column in the
V$STREAMS_CAPTURE dynamic 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.
Note:For this verification to be valid, each capture process must have been running uninterrupted since Step 4.
If the export database is running one or more apply processes, or is propagating user-enqueued messages, then start the full database export now. Make sure that the
FULL export parameter is set to
y so that the required Streams metadata is exported. If you already started the export in Step 6, then proceed to Step 11.
Perform the full database import. Make sure that the
FULL import parameters are both set to
y so that the required Streams metadata is imported. The default setting is
y for the
STREAMS_CONFIGURATION import parameter. Also, make sure 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 "Preparing to Transmit Redo Data to a Downstream Database" for instructions.
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.
See Also:"Creating a Capture Process" for information about creating a downstream 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_limit capture process parameter in Step 4, then, at the import database, reset these parameters to their original settings.
You run the
REMOVE_STREAMS_CONFIGURATION procedure in the
DBMS_STREAMS_ADM package to remove a Streams configuration at the local database.
Attention:Running this procedure is dangerous. You should run this procedure only if you are sure you want to remove the entire Streams configuration at a database.
To remove the Streams configuration at the local database, run the following procedure:
After running this procedure, drop the Streams administrator at the database, if possible.
See Also:Oracle Database PL/SQL Packages and Types Reference for detailed information about the actions performed by the