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.See Also:
Oracle Streams Replication Administrator's Guide for more information about export/import parameters that are relevant to Streams
Oracle Database Utilities for more information about performing a full database export/import
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 DBMS_PROPAGATION_ADM
package.
Make the necessary changes to your network configuration so that the database links used by the propagation jobs you disabled in Step 1 point to the computer running the import database.
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_NUMBER
function in the DBMS_FLASHBACK
package. 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 7000000
.
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 message_limit
and 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.
If the enqueue message number of each capture process is less than or equal to the SCN determined in Step 4, then proceed to Step 9.
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 any downstream capture processes captured changes that originated at the export database, then drop these downstream capture processes. You will re-create them in Step 14a.
If the export database has any propagations that are propagating user-enqueued messages, then stop these propagations using the STOP_PROPAGATION
procedure in the DBMS_PROPAGATION
package.
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.
When the export is complete, transfer the export dump file to the computer running the import database.
Perform the full database import. Make sure that the STREAMS_CONFIGURATION
and 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 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.
See Also:
"Creating a Capture Process" for information about creating a downstream capture processIf 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 message_limit
or 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:
EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
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 theREMOVE_STREAMS_CONFIGURATION
procedure