C Online Database Maintenance with Streams

Handling Modifications to Schema Objects

If you are upgrading user-created applications, then, typically, schema objects in the database change to support the upgraded applications. In Streams, row LCRs contain information about row changes that result from DML statements. A declarative rule-based transformation or DML handler can modify row LCRs captured from the source database redo log so that the row LCRs can be applied to the altered tables at the destination database.

A rule-based transformation is any modification to a message that results when a rule in a positive rule set evaluates to TRUE. Declarative rule-based transformation cover one of a common set of transformation scenarios for row LCRs. Declarative rule-based transformations are run internally without using PL/SQL. You specify such a transformation using a procedure in the DBMS_STREAMS_ADM package. A declarative rule-based transformation can modify row LCRs during capture, propagation, or apply.

A DML handler is a user procedure that processes row LCRs resulting from DML statements at a source database. A Streams apply process at a destination database can pass row LCRs to a DML handler, and the DML handler can modify the row LCRs.

The process for upgrading user-created applications using Streams can involve modifying and creating the schema objects at the destination database after instantiation. You can use one or more declarative rule-based transformations and DML handlers at the destination database to process changes from the source database so that they apply to the modified schema objects correctly. Declarative rule-based transformations and DML handlers can be used during application upgrade to account for differences between the source database and destination database.

In general, declarative rule-based transformations are easier to use than DML handlers. Therefore, when modifications to row LCRs are required, try to configure a declarative rule-based transformation first. If a declarative rule-based transformation is not sufficient, then use a DML handler. If row LCRs for tables that contain one or more LOB columns must be modified, then you should use a DML handler and LOB assembly.

Before you begin the database maintenance operation, you should complete the following tasks to prepare your declarative rule-based transformations or DML handlers:

• Learn about declarative rule-based transformations. See "Declarative Rule-Based Transformations".

• Learn about DML handlers. See "Message Processing Options for an Apply Process".

• Determine the declarative rule-based transformations and DML handlers you will need at your destination database. Your determination depends on the modifications to the schema objects required by your upgraded applications.

• Create the PL/SQL procedures that you will use for any DML handlers during the database maintenance operation. See the Oracle Streams Replication Administrator's Guide for information about creating the PL/SQL procedures.

• If row LCRs for tables that contain one or more LOB columns must be modified, then learn about using LOB assembly. See Oracle Streams Replication Administrator's Guide.

Handling Logical Dependencies

In some cases, an apply process requires additional information to detect dependencies in row LCRs that are being applied in parallel. During application upgrades, an apply process might require additional information to detect dependencies in the following situations:

• The application, rather than the database, enforces logical dependencies.

• Schema objects have been modified to support the application upgrade, and a DML handler will modify row LCRs to account for differences between the source database and destination database.

A virtual dependency definition is a description of a dependency that is used by an apply process to detect dependencies between transactions at a destination database. A virtual dependency definition is not described as a constraint in the destination database data dictionary. Instead, it is specified using procedures in the DBMS_APPLY_ADM package. Virtual dependency definitions enable an apply process to detect dependencies that it would not be able to detect by using only the constraint information in the data dictionary. After dependencies are detected, an apply process schedules LCRs and transactions in the correct order for apply.

If virtual dependency definitions are required for your application upgrade, then learn about virtual dependency definitions and plan to configure them during the application upgrade.

The Source Database Is the Capture Database

Complete the following steps to set up Streams prior to instantiation when the source database is the capture database:

1. Configure your network and Oracle Net so that the source database can communicate with the destination database. See Oracle Database Net Services Administrator's Guide for instructions.

2. Connect as the Streams administrator in SQL*Plus to the source database, and create a database link to the destination database. For example:

   CONNECT strmadmin/strmadminpw@orcl.net
   
   CREATE DATABASE LINK stms.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
      USING 'stms.net';
   
   

3. While connected as the Streams administrator in SQL*Plus to the source database, run the PRE_INSTANTIATION_SETUP procedure:

   DECLARE
     empty_tbs  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; 
   BEGIN
     DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(
       maintain_mode           => 'GLOBAL',
       tablespace_names        => empty_tbs,
       source_database         => 'orcl.net',
       destination_database    => 'stms.net',
       perform_actions         => true,
       script_name             => NULL,
       script_directory_object => NULL,
       capture_name            => 'capture_maint',
       capture_queue_table     => 'strmadmin.capture_q_table',
       capture_queue_name      => 'strmadmin.capture_q',
       propagation_name        => 'prop_maint',
       apply_name              => 'apply_maint',
       apply_queue_table       => 'strmadmin.apply_q',
       apply_queue_name        => 'strmadmin.apply_q_table',
       bi_directional          => false,
       include_ddl             => true,
       start_processes         => false,
       exclude_schemas         => '*',
       exclude_flags           => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
   END;
   /
   
   

4. Proceed to "Task 3: Instantiating the Database".

The Destination Database Is the Capture Database

Complete the following steps to set up Streams prior to instantiation when the destination database is the capture database:

1. Configure your network and Oracle Net so that the source database and destination database can communicate with each other. See Oracle Database Net Services Administrator's Guide for instructions.

2. Make sure log file shipping from the source database to the destination database is configured. See "Preparing for Downstream Capture" for instructions.

3. Connect as the Streams administrator in SQL*Plus to the destination database, and create a database link to the source database. For example:

   CONNECT strmadmin/strmadminpw@stms.net
   
   CREATE DATABASE LINK orcl.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
      USING 'orcl.net';
   
   

4. While connected as the Streams administrator in SQL*Plus to the destination database, run the PRE_INSTANTIATION_SETUP procedure:

   DECLARE
     empty_tbs  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; 
   BEGIN
     DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(
       maintain_mode           => 'GLOBAL',
       tablespace_names        => empty_tbs,
       source_database         => 'orcl.net',
       destination_database    => 'stms.net',
       perform_actions         => true,
       script_name             => NULL,
       script_directory_object => NULL,
       capture_name            => 'capture_maint',
       capture_queue_table     => 'strmadmin.streams_q_table',
       capture_queue_name      => 'strmadmin.streams_q',
       apply_name              => 'apply_maint',
       apply_queue_table       => 'strmadmin.streams_q',
       apply_queue_name        => 'strmadmin.streams_q_table',
       bi_directional          => false,
       include_ddl             => true,
       start_processes         => false,
       exclude_schemas         => '*',
       exclude_flags           => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
   END;
   /
   
   

   Notice that the propagation_name parameter is omitted because a propagation is not necessary when the destination database is the capture database and the downstream capture process and apply process use the same queue at the destination database.

   Also, notice that the capture process and apply process will share a queue named streams_q at the destination database.

5. Proceed to "Task 3: Instantiating the Database".

A Third Database Is the Capture Database

This example assumes that the global name of the third database is thrd.net. Complete the following steps to set up Streams prior to instantiation when a third database is the capture database:

1. Configure your network and Oracle Net so that the source database, destination database, and third database can communicate with each other. See Oracle Database Net Services Administrator's Guide for instructions.

2. Make sure log file shipping from the source database to the third database is configured. See "Preparing for Downstream Capture" for instructions.

3. Connect as the Streams administrator in SQL*Plus to the third database, and create a database link to the source database. For example:

   CONNECT strmadmin/strmadminpw@thrd.net
   
   CREATE DATABASE LINK orcl.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
      USING 'orcl.net';
   
   

4. While connected as the Streams administrator in SQL*Plus to the third database, create a database link to the destination database. For example:

   CREATE DATABASE LINK stms.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
      USING 'stms.net';
   
   

5. While connected as the Streams administrator in SQL*Plus to the third database, run the PRE_INSTANTIATION_SETUP procedure:

   DECLARE
     empty_tbs  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; 
   BEGIN
     DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(
       maintain_mode           => 'GLOBAL',
       tablespace_names        => empty_tbs,
       source_database         => 'orcl.net',
       destination_database    => 'stms.net',
       perform_actions         => true,
       script_name             => NULL,
       script_directory_object => NULL,
       capture_name            => 'capture_maint',
       capture_queue_table     => 'strmadmin.capture_q_table',
       capture_queue_name      => 'strmadmin.capture_q',
       propagation_name        => 'prop_maint',
       apply_name              => 'apply_maint',
       apply_queue_table       => 'strmadmin.apply_q',
       apply_queue_name        => 'strmadmin.apply_q_table',
       bi_directional          => false,
       include_ddl             => true,
       start_processes         => false,
       exclude_schemas         => '*',
       exclude_flags           => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
   END;
   /
   
   

6. Proceed to "Task 3: Instantiating the Database".

Instantiating the Database Using Export/Import

If you use Oracle Data Pump or original Export/Import to instantiate the destination database, then make sure the following parameters are set to the appropriate values:

• Set the STREAMS_CONFIGURATION import parameter to n.

• If you use original Export/Import, then set the CONSISTENT export parameter to y. This parameter does not apply to Data Pump exports.

• If you use original Export/Import, then set the STREAMS_INSTANTIATION import parameter to y. This parameter does not apply to Data Pump imports.

Complete the following steps to instantiate an entire database with Data Pump:

1. Connect in SQL*Plus to the source database as the Streams administrator, and create a directory object to hold the export dump file and export log file:

   CREATE DIRECTORY dpump_dir AS '/usr/dpump_dir';
   
   

2. While connected to the source database as the Streams administrator, determine the current system change number (SCN) of the source database:

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

   The returned SCN value is specified for the FLASHBACK_SCN Data Pump export parameter in Step 3. Specifying the FLASHBACK_SCN export parameter, or a similar export parameter, ensures that the export is consistent to a single SCN. In this example, assume that the query returned 876606.

   After you perform this query, make sure no DDL changes are made to the objects being exported until after the export is complete.

3. On a command line, use Data Pump to export the source database.

   Perform the export by connecting as an administrative user who is granted EXP_FULL_DATABASE role. This user also must have READ and WRITE privilege on the directory object created in Step 1. This example connects as the Streams administrator strmadmin.

   The following example is a Data Pump export command:

   expdp strmadmin/strmadminpw FULL DIRECTORY=DPUMP_DIR DUMPFILE=orc1.dmp FLASHBACK_SCN=876606
   
   

   See Also:

   Oracle Database Utilities for information about performing a Data Pump export

4. Connect in SQL*Plus to the destination database as the Streams administrator, and create a directory object to hold the import dump file and import log file:

   CREATE DIRECTORY dpump_dir AS '/usr/dpump_dir';
   
   

5. Transfer the Data Pump export dump file orc1.dmp to the destination database. You can use the DBMS_FILE_TRANSFER package, binary FTP, or some other method to transfer the file to the destination database. After the file transfer, the export dump file should reside in the directory that corresponds to the directory object created in Step 4.

6. On a command line at the destination database, use Data Pump to import the export dump file orc1.dmp. Make sure no changes are made to the database tables until the import is complete. Performing the import automatically sets the instantiation SCN for the destination database and all of its objects.

   Perform the import by connecting as an administrative user who is granted IMP_FULL_DATABASE role. This user also must have READ and WRITE privilege on the directory object created in Step 4. This example connects as the Streams administrator strmadmin.

   The following example is an import command:

   impdp strmadmin/strmadminpw FULL DIRECTORY=DPUMP_DIR DUMPFILE=orc1.dmp STREAMS_CONFIGURATION=n
   
   

   See Also:

   Oracle Database Utilities for information about performing a Data Pump import

Instantiating the Database Using the RMAN DUPLICATE Command

If you use the RMAN DUPLICATE command for instantiation on the same platform, then complete the following steps:

1. Create a backup of the source database if one does not exist. RMAN requires a valid backup for duplication. In this example, create a backup of orcl.net if one does not exist.

2. While connected as an administrative user in SQL*Plus to the source database, determine the until SCN for the RMAN DUPLICATE command. For example:

   CONNECT SYSTEM/MANAGER@orcl.net
   
   SET SERVEROUTPUT ON SIZE 1000000
   DECLARE
     until_scn NUMBER;
   BEGIN
     until_scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
         DBMS_OUTPUT.PUT_LINE('Until SCN: ' || until_scn);
   END;
   /
   
   

   Make a note of the until SCN value. This example assumes that the until SCN value is 748044. You will set the UNTIL SCN option to this value when you use RMAN to duplicate the database in Step 5 and as the instantiation SCN in "Task 4: Setting Up Streams After Instantiation".

3. While still connected as an administrative user in SQL*Plus to the source database, archive the current online redo log. For example:

   ALTER SYSTEM ARCHIVE LOG CURRENT;
   
   

4. Prepare your environment for database duplication, which includes preparing the destination database as an auxiliary instance for duplication. See the Oracle Database Backup and Recovery Advanced User's Guide for instructions.

5. Use the RMAN DUPLICATE command with the OPEN RESTRICTED option to instantiate the source database at the destination database. The OPEN RESTRICTED option is required. This option enables a restricted session in the duplicate database by issuing the following SQL statement: ALTER SYSTEM ENABLE RESTRICTED SESSION. RMAN issues this statement immediately before the duplicate database is opened.

   You can use the UNTIL SCN clause to specify an SCN for the duplication. Use the until SCN determined in Step 2 for this clause. Archived redo logs must be available for the until SCN specified and for higher SCN values. Therefore, Step 3 archived the redo log containing the until SCN.

   Make sure you use TO database_name in the DUPLICATE command to specify the name of the duplicate database. In this example, the duplicate database is stms.net. Therefore, the DUPLICATE command for this example includes TO stms.net.

   The following example is an RMAN DUPLICATE command:

   rman
   RMAN> CONNECT TARGET SYS/change_on_install@orcl.net
   RMAN> CONNECT AUXILIARY SYS/change_on_install@stms.net
   RMAN> RUN
         { 
           SET UNTIL SCN 748044;
           ALLOCATE AUXILIARY CHANNEL mgdb DEVICE TYPE sbt; 
           DUPLICATE TARGET DATABASE TO mgdb 
           NOFILENAMECHECK
           OPEN RESTRICTED;
         }
   
   

6. Connect to the destination database as a system administrator in SQL*Plus, and rename the global name. After an RMAN database instantiation, the destination database has the same global name as the source database, but the destination database must have its original name until the end of the maintenance operation. Rename the global name of the destination database back to its original name with the following statement:

   ALTER DATABASE RENAME GLOBAL_NAME TO stms.net;
   
   

7. Connect as the Streams administrator in SQL*Plus to the destination database, and create a database link to the source database. For example:

   CONNECT strmadmin/strmadminpw@stms.net
   
   CREATE DATABASE LINK orcl.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
      USING 'orcl.net';
   
   

   This database link is required because the POST_INSTANTIATION_SETUP procedure runs the SET_GLOBAL_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package at the destination database, and the SET_GLOBAL_INSTANTIATION_SCN procedure requires the database link.

8. If the source database and the capture database are the same database, then while still connected as the Streams administrator in SQL*Plus to the destination database, drop the database link from the source database to the destination database that was cloned from the source database:

   DROP DATABASE LINK stms.net;
   
   

Instantiating the Database Using the RMAN CONVERT DATABASE Command

If you use the RMAN CONVERT DATABASE command for instantiation to migrate the database to a different platform, then complete the following steps:

1. Create a backup of the source database if one does not exist. RMAN requires a valid backup. In this example, create a backup of orcl.net if one does not exist.

2. While still connected as an administrative user in SQL*Plus to the source database, archive the current online redo log. For example:

   ALTER SYSTEM ARCHIVE LOG CURRENT;
   
   

3. Prepare your environment for database conversion, which includes opening the source database in read-only mode. Complete the following steps:

   1. If the source database is open, then shut it down and start it in read-only mode.

   2. Run the CHECK_DB and CHECK_EXTERNAL functions in the DBMS_TDB package. Check the results to ensure that the conversion is supported by the RMAN CONVERT DATABASE command.

   See Also:

   Oracle Database Backup and Recovery Advanced User's Guide for more information about these steps

4. Determine the current SCN of the source database:

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

   Make a note of the SCN value returned. You will use this number for the instantiation SCN in "Task 4: Setting Up Streams After Instantiation". For this example, assume that the returned value is 748044.

5. Open RMAN and run the CONVERT DATABASE command.

   Make sure you use NEW DATABASE database_name in the CONVERT DATABASE command to specify the name of the destination database. In this example, the destination database name is stms. Therefore, the CONVERT DATABASE command for this example includes NEW DATABASE stms.

   The following example is an RMAN CONVERT DATABASE command for a destination database that is running on the Linux IA (64-bit) platform:

   rman
   RMAN> CONNECT TARGET SYS/change_on_install@orc1.net
   CONVERT DATABASE NEW DATABASE 'stms'
             TRANSPORT SCRIPT '/tmp/convertdb/transportscript.sql'     
             TO PLATFORM 'Linux IA (64-bit)'
             DB_FILE_NAME_CONVERT = ('/home/oracle/dbs','/tmp/convertdb');
   
   

6. Transfer the datafiles, PFILE, and SQL script produced by the RMAN CONVERT DATABASE command to the computer system that is running the destination database.

7. On the computer system that is running the destination database, modify the SQL script so that the destination database always opens with restricted session enabled.

   An example script follows with the necessary modifications in bold font:

   -- The following commands will create a new control file and use it
   -- to open the database.
   -- Data used by Recovery Manager will be lost.
   -- The contents of online logs will be lost and all backups will
   -- be invalidated. Use this only if online logs are damaged.
    
   -- After mounting the created controlfile, the following SQL
   -- statement will place the database in the appropriate
   -- protection mode:
   --  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
    
   STARTUP NOMOUNT PFILE='init_00gd2lak_1_0.ora'
   CREATE CONTROLFILE REUSE SET DATABASE "STMS" RESETLOGS  NOARCHIVELOG
       MAXLOGFILES 32
       MAXLOGMEMBERS 2
       MAXDATAFILES 32
       MAXINSTANCES 1
       MAXLOGHISTORY 226
   LOGFILE
     GROUP 1 '/tmp/convertdb/archlog1'  SIZE 25M,
     GROUP 2 '/tmp/convertdb/archlog2'  SIZE 25M
   DATAFILE
     '/tmp/convertdb/systemdf',
     '/tmp/convertdb/sysauxdf',
     '/tmp/convertdb/datafile1',
     '/tmp/convertdb/datafile2',
     '/tmp/convertdb/datafile3'
   CHARACTER SET WE8DEC
   ;
    
   -- NOTE: This ALTER SYSTEM statement is added to enable restricted session.
   
   ALTER SYSTEM ENABLE RESTRICTED SESSION;
   
   -- Database can now be opened zeroing the online logs.
   ALTER DATABASE OPEN RESETLOGS;
    
   -- No tempfile entries found to add.
   --
    
   set echo off
   prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   prompt * Your database has been created successfully!
   prompt * There are many things to think about for the new database. Here
   prompt * is a checklist to help you stay on track:
   prompt * 1. You may want to redefine the location of the directory objects.
   prompt * 2. You may want to change the internal database identifier (DBID) 
   prompt *    or the global database name for this database. Use the 
   prompt *    NEWDBID Utility (nid).
   prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
   SHUTDOWN IMMEDIATE 
   -- NOTE: This startup has the UPGRADE parameter.
   -- The startup already has restricted session enabled, so no change is needed.
   STARTUP UPGRADE PFILE='init_00gd2lak_1_0.ora'
   @@ ?/rdbms/admin/utlirp.sql 
   SHUTDOWN IMMEDIATE 
   -- NOTE: The startup below is generated without the RESTRICT clause.
   -- Add the RESTRICT clause.
   STARTUP RESTRICT PFILE='init_00gd2lak_1_0.ora'
   -- The following step will recompile all PL/SQL modules.
   -- It may take serveral hours to complete.
   @@ ?/rdbms/admin/utlrp.sql 
   set feedback 6;
   
   

   Other changes to the script might be necessary. For example, the datafile locations and PFILE location might need to be changed to point to the correct locations on the destination database computer system.

8. Connect to the destination database as a system administrator in SQL*Plus, and rename the global name. After an RMAN database instantiation, the destination database has the same global name as the source database, but the destination database must have its original name until the end of the maintenance operation. Rename the global name of the destination database back to its original name with the following statement:

   ALTER DATABASE RENAME GLOBAL_NAME TO stms.net;
   
   

9. Connect as the Streams administrator in SQL*Plus to the destination database, and create a database link to the source database. For example:

   CONNECT strmadmin/strmadminpw@stms.net
   
   CREATE DATABASE LINK orcl.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
      USING 'orcl.net';
   
   

   This database link is required because the POST_INSTANTIATION_SETUP procedure runs the SET_GLOBAL_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package at the destination database, and the SET_GLOBAL_INSTANTIATION_SCN procedure requires the database link.

10. If the source database and the capture database are the same database, then while still connected as the Streams administrator in SQL*Plus to the destination database, drop the database link from the source database to the destination database that was cloned from the source database:

    DROP DATABASE LINK stms.net;
    
    

The Source Database Is the Capture Database

Complete the following steps to set up Streams after instantiation when the source database is the capture database:

1. Connect in SQL*Plus to the source database as the Streams administrator, and run the POST_INSTANTIATION_SETUP procedure:

   CONNECT strmadmin/strmadminpw@orcl.net
   
   DECLARE
     empty_tbs  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; 
   BEGIN
     DBMS_STREAMS_ADM.POST_INSTANTIATION_SETUP(
       maintain_mode           => 'GLOBAL',
       tablespace_names        => empty_tbs,
       source_database         => 'orcl.net',
       destination_database    => 'stms.net',
       perform_actions         => true,
       script_name             => NULL,
       script_directory_object => NULL,
       capture_name            => 'capture_maint',
       capture_queue_table     => 'strmadmin.capture_q_table',
       capture_queue_name      => 'strmadmin.capture_q',
       propagation_name        => 'prop_maint',
       apply_name              => 'apply_maint',
       apply_queue_table       => 'strmadmin.apply_q',
       apply_queue_name        => 'strmadmin.apply_q_table',
       bi_directional          => false,
       include_ddl             => true,
       start_processes         => false,
       instantiation_scn       => 748044, -- NULL if Export/Import instantiation
       exclude_schemas         => '*',
       exclude_flags           => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
   END;
   /
   
   

2. Proceed to "Task 5: Finishing the Maintenance Operation and Removing Streams".

The Destination Database Is the Capture Database

Complete the following steps to set up Streams after instantiation when the destination database is the capture database:

1. Connect in SQL*Plus to the destination database as the Streams administrator, and run the POST_INSTANTIATION_SETUP procedure:

   CONNECT strmadmin/strmadminpw@stms.net
   
   DECLARE
     empty_tbs  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; 
   BEGIN
     DBMS_STREAMS_ADM.POST_INSTANTIATION_SETUP(
       maintain_mode           => 'GLOBAL',
       tablespace_names        => empty_tbs,
       source_database         => 'orcl.net',
       destination_database    => 'stms.net',
       perform_actions         => true,
       script_name             => NULL,
       script_directory_object => NULL,
       capture_name            => 'capture_maint',
       capture_queue_table     => 'strmadmin.streams_q_table',
       capture_queue_name      => 'strmadmin.streams_q',
       apply_name              => 'apply_maint',
       apply_queue_table       => 'strmadmin.streams_q',
       apply_queue_name        => 'strmadmin.streams_q_table',
       bi_directional          => false,
       include_ddl             => true,
       start_processes         => false,
       instantiation_scn       => NULL,
       exclude_schemas         => '*',
       exclude_flags           => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
   END;
   /
   
   

   Notice that the propagation_name parameter is omitted because a propagation is not necessary when the destination database is the capture database.

2. Proceed to "Task 5: Finishing the Maintenance Operation and Removing Streams".

A Third Database Is the Capture Database

This example assumes that the global name of the third database is thrd.net. Complete the following steps to set up Streams after instantiation when a third database is the capture database:

1. Connect in SQL*Plus to the third database as the Streams administrator, and run the POST_INSTANTIATION_SETUP procedure:

   CONNECT strmadmin/strmadminpw@thrd.net
   
   DECLARE
     empty_tbs  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; 
   BEGIN
     DBMS_STREAMS_ADM.POST_INSTANTIATION_SETUP(
       maintain_mode           => 'GLOBAL',
       tablespace_names        => empty_tbs,
       source_database         => 'orcl.net',
       destination_database    => 'stms.net',
       perform_actions         => true,
       script_name             => NULL,
       script_directory_object => NULL,
       capture_name            => 'capture_maint',
       capture_queue_table     => 'strmadmin.capture_q_table',
       capture_queue_name      => 'strmadmin.capture_q',
       propagation_name        => 'prop_maint',
       apply_name              => 'apply_maint',
       apply_queue_table       => 'strmadmin.apply_q',
       apply_queue_name        => 'strmadmin.apply_q_table',
       bi_directional          => false,
       include_ddl             => true,
       start_processes         => false,
       instantiation_scn       => 748044, -- NULL if Export/Import instantiation
       exclude_schemas         => '*',
       exclude_flags           => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
   END;
   /
   
   

2. Proceed to "Task 5: Finishing the Maintenance Operation and Removing Streams".