17 Other Streams Management Tasks

Performing Full Database Export/Import in a Streams Environment

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.

Complete the following steps to perform a full database export/import on a database that is using Streams:

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.

7. If the export database is the source database for changes captured by any capture processes, then complete the following steps for each capture process:

   1. 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.

   2. 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.

   3. 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.

8. 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.

9. 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.

10. 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.

11. When the export is complete, transfer the export dump file to the computer running the import database.

12. 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.

13. 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.

14. Re-create downstream capture processes:

    1. 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.

    2. 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

15. 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.

16. Let users access the import database, and shut down the export database.

17. Enable any propagation jobs you disabled in Steps 1 and 9.

18. 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.

Removing a Streams Configuration

You run the REMOVE_STREAMS_CONFIGURATION procedure in the DBMS_STREAMS_ADM package to remove a Streams configuration at the local 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.