If you plan to use Data Pump to instantiate your target sites, you will need to create work directories in which Data Pump will create and access the resulting dump file for export and import. The naming convention you use is up to you. If you used the Database Creation Assistant to create your database, you may already have a DATA_PUMP_DIR (destination C:Oracle11gR2product11.2.0db_1 dbmslog). You can determine this by querying the DBA_DIRECTORIES view.

At the Source site, create a work directory for the Data Pump export, setting the destination to what suits your needs best (it doesn't have to be the default {oracle_home}/rdbms/log/). Best practice; specify the full path explicitly.

Windows:

CREATE DIRECTORY 'DATA_PUMP_DIR' AS 'c:oracledpumpexport';

Unix:

CREATE DIRECTORY 'DATA_PUMP_DIR' AS '/u01/oracle/dpump/export/';

Note: This can also be done through the EM Console: Schema | Database Objects | Directory Objects.

As mentioned in Chapter 1, you can use the DBMS_CAPTURE_ADM. PREPARE_*_INSTANTIATION, DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER and DBMS_APPLY_ADM SET_*_INSTANTIATION_SCN packages to manually set the Instantiation SCN between a capture and an Apply process.

The steps for setting the instantiation SCN are as follows:

The following provides examples using all three packages from the Source side and Destination side. The procedures below must be run as the Streams Administrator and uses a database link owned by the Streams Administrator user and connects to the Streams Administrator at the remote database.

The database links used for examples are as follows:

The example below is run on the Source side (HQ.US.APGTECH.COM). The object(s) are prepared for instantiation at the source. The SCN is captured on the source side once the table is instantiated. That SCN is then set for the same table at the Destination (BRANCH.US.APGTECH.COM) across a database link.

DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
table_name => 'product.inventory',
supplemental_logging => 'keys' --default
);
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
[email protected](
source_object_name => 'product.inventory',
source_database_name => 'HQ.US.APGTECH.COM',
instantiation_scn => iscn);
END;
/

This pattern of capturing the Instantiation SCN on Source side and applying it to the Destination side continues in the examples below for SCHEMA and GLOBAL level.

For SCHEMA level:

DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
table_name => 'product.inventory',
supplemental_logging => 'keys' --default
);
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@BRANCH.US.APGTECH.COM(
source_schema_name => 'product',
source_database_name => 'HQ.US.APGTECH.COM',
instantiation_scn => iscn
recursive => TRUE);
END;
/

The recursive parameter provides instructions to apply the proper Instantiation SCN to each table in the schema.

For GLOBAL level:

BEGIN
DBMS_CAPTURE_ADM.PREPARE_GLOBAL_INSTANTIATION(
supplemental_logging => 'keys' --default
);
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_GLOBAL_INSTANTIATION_SCN@BRANCH.US.APGTECH.COM(
source_database_name => 'HQ.US.APGTECH.COM',
instantiation_scn => iscn
recursive => TRUE);
END;
/

The recursive parameter provides instructions to apply the proper Instantiation SCN to each of the tables in all schemas.

In most situations we find the use of either DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN or DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN to be the most appropriate.

The examples above have shown the "pushing" of the instantiation SCN from the Source side to the Destination side. The reverse can also be accomplished essentially "pulling" the instantiation SCN to the Destination from the Source side. This can be accomplished by simply running the same code above and changing the location and entry of the database link mentioned above from BRANCH.US.APGTECH.COM to HQ.US.APGTECH.COM. The example at the table level for clarity is shown as follows. This is run on the Destination side (BRANCH.US.APGTECH.COM).

DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
[email protected](
table_name => 'product.inventory',
supplemental_logging => 'keys' --default
);
iscn := [email protected]();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'product.inventory',
source_database_name => 'HQ.US.APGTECH.COM',
instantiation_scn => iscn);
END;
/

The code above "pulls" the instantiation SCN from the Source Side (HQ.US.APGTECH.COM) and applies it the Destination Side (BRANCH.US.APGTECH.COM).

One important point that needs to be mentioned is that we include preparing objects for instantiation manually. If you used the DBMS_STREAMS_ADM package to build the Capture process, the call to DBMS_CAPTURE_ADM.PREPARE_*_INSTANTIATION is done automatically.

To check that instantiation was done properly use the following queries:

-- Run on the Source Side
COLUMN TABLE_OWNER HEADING 'Table Owner' FORMAT A15
COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A15
COLUMN SCN HEADING 'Prepare SCN' FORMAT 99999999999
COLUMN TIMESTAMP HEADING 'Time Ready for|Instantiation'
SELECT TABLE_OWNER,
TABLE_NAME,
SCN,
TO_CHAR(TIMESTAMP, 'HH24:MI:SS MM/DD/YY') TIMESTAMP
FROM DBA_CAPTURE_PREPARED_TABLES;
-- Run on the Destination Side
COLUMN SOURCE_DATABASE HEADING 'Source Database' FORMAT A20
COLUMN SOURCE_OBJECT_OWNER HEADING 'Object Owner' FORMAT A15
COLUMN SOURCE_OBJECT_NAME HEADING 'Object Name' FORMAT A15
COLUMN INSTANTIATION_SCN HEADING 'Instantiation SCN' FORMAT 99999999999
SELECT SOURCE_DATABASE,
SOURCE_OBJECT_OWNER,
SOURCE_OBJECT_NAME,
INSTANTIATION_SCN
FROM DBA_APPLY_INSTANTIATED_OBJECTS
WHERE APPLY_DATABASE_LINK IS NULL;

There are occasions where you may need to remove an instantiation SCN. This is usually only done when an attempt at instantiation has failed or gone wrong. This being the case, you can clear the apply Instantiation SCN with DBMS_APPLY_ADM.SET_*_INSTANTIATION_SCN procedure and setting the Instantiation SCN to NULL. This removes the entry from the associated dictionary view. Where * is one of the following levels: TABLE, SCHEMA, or GLOBAL. You would then prepare the object for instantiation at the source and use that SCN to retry setting the Instantiation SCN at the apply site.