Package for Instantiation

After you have completed building your deployment template, you need to package the template for instantiation. This example illustrates how to use both the online and offline instantiation procedures. Notice that the instantiation procedures are very similar: you simply use either the INSTANTIATE_ONLINE function or INSTANTIATE_OFFLINE function according to your needs. This section accomplishes two tasks: create the instantiation script and save the instantiation script to a file.

Figure 4-2 Package and Instantiate Deployment Template

Text description of the illustration rardta.gif.

Package Template

When you execute either the INSTANTIATE_OFFLINE or the INSTANTIATE_ONLINE function, Oracle populates the USER_REPCAT_TEMP_OUTPUT view with the script to create the remote snapshot environment. Both online and offline scripts contain the SQL statements to create the objects specified in the deployment template. The difference is that an offline instantiation script also contains the data to populate the objects. The online instantiation script does not contain the data. Rather, during online instantiation, the snapshot site connects to the master site to download the data.

Complete the steps in either the "Offline Instantiation Package" or "Online Instantiation Package" according to your needs. These sections only apply to packaging templates for snapshot sites with Oracle8i Enterprise Edition, Oracle8i Standard Edition, or Oracle8i Personal Edition installed. These instructions do not apply to snapshot sites with Oracle8i Lite installed.

Offline Instantiation Package

The INSTANTIATE_OFFLINE function creates a script that creates the snapshot environment according to the contents of a specified deployment template. In addition to containing the DDL (CREATE statements) to create the snapshot environment, this script also contains the DML (INSERT statements) to populate the snapshot environment with the appropriate data set.

--Use the INSTANTIATE_OFFLINE function to package the 
--template for offline instantiation by a remote snapshot 
--site. Executing this procedure both creates a script that 
--creates that snapshot environment and populates the
--environment with the proper data set. This script is stored
--in the temporary USER_REPCAT_TEMP_OUTPUT view.

SET SERVEROUTPUT ON
DECLARE
   dt_num NUMBER;
BEGIN
   dt_num := DBMS_REPCAT_RGT.INSTANTIATE_OFFLINE(
               refresh_template_name => 'DT_PERSONNEL',
               user_name => 'SCOTT',
               site_name => 'LA_REGIONAL',
               next_date => sysdate,
               interval => 'sysdate + (1/144)');
   DBMS_OUTPUT.PUT_LINE('Template ID = ' || dt_num);
END;
/
COMMIT;
/

Make a note of the number that is returned for the DT_NUM variable. You must use this number when you select from the USER_REPCAT_TEMP_OUTPUT view to retrieve the generated script. Be sure that you complete the steps in "Save Instantiation Script to File" after you complete this section. This script is unique to an individual snapshot site and cannot be used for other snapshot sites.

Online Instantiation Package

The INSTANTIATE_ONLINE function creates a script that creates the snapshot environment according to the contents of a specified deployment template. When this script is executed at the remote snapshot site, Oracle creates the snapshot site according to the DDL (CREATE statements) in the script and populates the environment with the appropriate data set from the master site. This requires that the remote snapshot site has a "live" connection to the master site.

--Use the INSTANTIATE_ONLINE function to "package" the 
--template for online instantiation by a remote snapshot 
--site. Executing this procedure creates a script which can
--then be used to create a snapshot environment. This script 
--is stored in the temporary USER_REPCAT_TEMP_OUTPUT view.

SET SERVEROUTPUT ON
DECLARE
   dt_num NUMBER;
BEGIN
   dt_num := DBMS_REPCAT_RGT.INSTANTIATE_ONLINE(
               refresh_template_name => 'DT_PERSONNEL',
               user_name => 'SCOTT',
               site_name => 'snap1.world',
               next_date => sysdate,
               interval => 'sysdate + (1/144)');
   DBMS_OUTPUT.PUT_LINE('Template ID = ' || dt_num);
END;
/
COMMIT;
/

Make a note of the number that is returned for the DT_NUM variable. You must use this number when you select from the USER_REPCAT_TEMP_OUTPUT view to retrieve the generated script. Be sure that you complete the steps in "Save Instantiation Script to File" after you complete this section.

Save Instantiation Script to File

The easiest way to save the contents of the USER_REPCAT_TEMP_OUTPUT view is to use the SQL*Plus spool feature to save the results of a SELECT statement. Complete the following steps to save your deployment template script to a file:

1. Enter SPOOL filename.sql, where filename is the name of your script. Because you may have to generate many instantiation files, make sure you name your files with an easily recognizable name. For example, you might enter:

   SQL> SPOOL d:\snap1_world.sql
   
   

   Your instantiation script is saved as snap1_world.sql in the Oracle home directory, unless otherwise specified, as in the example above. If necessary, precede the filename with a fully qualified path to save the script to a different directory.

2. Issue the following select statement:

   SQL> SELECT DBMS_REPCAT_RGT.VC2_FROM_CLOB(text) text 
          FROM user_repcat_temp_output
          WHERE output_id = dt_num ORDER BY LINE;
   
   

Here, dt_num is the value that was returned when you executed the INSTANTIATE_ONLINE or INSTANTIATE_OFFLINE functions (illustrated in "Package Template").

SQL> SPOOL OFF