4
Create Deployment Template

This chapter illustrates how to build a deployment template using the Replication Managment API. The following topics will be discussed:

Oracle Deployment Templates Concepts

Oracle offers Deployment Templates to allow the database administrator to package a snapshot environment for easy, custom, and secure distribution/installation. A deployment template can be as simple as containing a single snapshot with a fixed data set, or as complex as hundreds of snapshots with a dynamic data set based on one or more variables. The goal is to create the environment once and deploy the deployment template as often as necessary. Oracle deployment templates feature:

Centralized control
Capability to repeatedly deploy snapshot environment
Template parameters allow data subsetting at remote site
Authorized user list to control template instantiation and data access

To prepare a snapshot environment for deployment, the DBA creates a template at the master site. This template will store all of the information needed to deploy a snapshot environment, including the DDL to create the objects at the remote site and the target refresh group. This template also maintains links to user security information and template parameters for custom snapshot creation.

Build Deployment Template

This section contains a complete script example of how to construct a deployment template using the Replication Management API. For deployment template conceptual and architectural information, be sure to read Chapter 4 in the Oracle8i Replication manual.

Figure 4-1 Setup Deployment Template

Be sure to read the comments contained within the scripts, as they contain important and useful information about building templates with the Replication Management API.

Note:
Vertical partitioning is not supported using the Replication Management API. See "Vertical Partitioning" in the Oracle8i Replication manual for more information.

--This script creates a deployment template that contains
--4 template objects, two template parameters, a set of user
--parameter values, and an authorized user.  A template is
--built in the following order:
--
--STEP 1: Define Refresh Group Template
--STEP 2: Add template objects to DT_PERSONNEL
--STEP 3: Define Parameter Defaults and Prompt Text
--STEP 4: Define User Parameter Values
--STEP 5: Authorize Users for Private Template


CONNECT repadmin/repadmin@orc3.world

/*************************************************************************
STEP 1:
CREATE DEPLOYMENT TEMPLATE
*************************************************************************/

--Before you begin assembling the components of your deployment
--template, you need to use the CREATE_RERESH_TEMPLATE procdure 
--to define the name of your deployment template, along with
--several other template characteristics (Public/Private status,
--target refresh group, and owner).

DECLARE
   a NUMBER;
BEGIN
   a := DBMS_REPCAT_RGT.CREATE_REFRESH_TEMPLATE (
           owner => 'SCOTT',
           refresh_group_name => 'PERSONNEL',
           refresh_template_name => 'DT_PERSONNEL',
           template_comment => 'Personnel Deployment Template',
           public_template => 'N');
END;
/

/*************************************************************************
STEP 2:
ADD OBJECTS TO TEMPLATE
*************************************************************************/

--STEP 2a: Create EMP Snapshot

--You will notice that the following procedure uses the DBMS_LOB
--package. This package is required to insert values into the 
--DDL_TEXT parameter of the CREATE_TEMPLATE_OBJECT function, which
--has a CLOB datatype.  You will see the DBMS_LOB package used
--whenever a value needs to be inserted into a CLOB parameter.
--For more information about using the DBMS_LOB package and LOBs 
--in general, see the Oracle8i Application Developer's Guide - Fundamentals. 

DECLARE
   tempstring VARCHAR2(300);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'CREATE SNAPSHOT scott.snap_emp AS SELECT 
      empno, ename, job, mgr, hiredate, sal, comm, deptno 
      FROM scott.emp@&dblink WHERE deptno = &dept';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'DT_PERSONNEL',
           object_name => 'snap_emp',
           object_type => 'SNAPSHOT',
           ddl_text => templob,
           master_rollback_seg => 'RBS');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

--STEP 2b: Create DEPT Snapshot

DECLARE
   tempstring VARCHAR2(300);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'CREATE SNAPSHOT scott.snap_dept AS SELECT 
      deptno, dname, loc 
      FROM scott.dept@&dblink';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'DT_PERSONNEL',
           object_name => 'snap_dept',
           object_type => 'SNAPSHOT',
           ddl_text => templob,
           master_rollback_seg => 'RBS');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

--STEP 2c: Create SALGRADE Snapshot

DECLARE
   tempstring VARCHAR2(300);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'CREATE SNAPSHOT scott.snap_salgrade AS SELECT 
      grade, losal, hisal
      FROM scott.salgrade@&dblink';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'DT_PERSONNEL',
           object_name => 'snap_salgrade',
           object_type => 'SNAPSHOT',
           ddl_text => templob,
           master_rollback_seg => 'RBS');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

--STEP 2d: Create BONUS Snapshot

DECLARE
   tempstring VARCHAR2(300);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'CREATE SNAPSHOT scott.snap_bonus AS SELECT 
      ename, job, sal, comm
      FROM scott.bonus@&dblink';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'DT_PERSONNEL',
           object_name => 'snap_bonus',
           object_type => 'SNAPSHOT',
           ddl_text => templob,
           master_rollback_seg => 'RBS');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

/*************************************************************************
STEP 3:
DEFINE PARAMETER DEFAULTS
*************************************************************************/

--Unlike using the "CREATE" functions and procudres in the other 
--steps, you will use the ALTER_TEMPLATE_PARM procedure to define 
--a template parameter value and prompt string. You will use the  
--"ALTER" procedure because the actual parameter was created in 
--step 2; recall that you defined the &DBLINK and &DEPT parameters
--in the DDL_TEXT parameter. Oracle detects these parameters in
--the DDL and automatically creates the template parameter. Use
--the ALTER_TEMPLATE_PARM procdure to define the remainder of the
--template parameter information (i.e. default parameter value and 
--prompt string).

--STEP 3a: DEPT Parameter

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := '20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.ALTER_TEMPLATE_PARM (
      refresh_template_name => 'DT_PERSONNEL',
      parameter_name => 'DEPT',
      new_default_parm_value => templob,
      new_prompt_string => 'Enter your department number:',
      new_user_override => 'Y');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

--STEP 3b: DBLINK Parameter

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'ORC2.WORLD';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.ALTER_TEMPLATE_PARM (
      refresh_template_name => 'DT_PERSONNEL',
      parameter_name => 'DBLINK',
      new_default_parm_value => templob,
      new_prompt_string => 'Enter target database link:',
      new_user_override => 'N');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

/*************************************************************************
STEP 4:
DEFINE USER PARAMETER VALUES
*************************************************************************/

--To automate the instantiation of custom data sets at
--individual remote snapshot sites, you can define USER
--PARAMETER values that will automatically be used when
--the specified user instantiates the target template.
--The CREATE_USER_PARM_VALUE enables you to assign a
--parameter value to a specific user.

--STEP 4a: Define User Parameter Value for user SCOTT

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := '30';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_USER_PARM_VALUE (
           refresh_template_name => 'DT_PERSONNEL',
           parameter_name => 'DEPT',
           user_name => 'SCOTT',
           parm_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/
--STEP 4b: Define User Parameter Value for user SCOTT

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'ORC2.WORLD';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_USER_PARM_VALUE (
           refresh_template_name => 'DT_PERSONNEL',
           parameter_name => 'DBLINK',
           user_name => 'SCOTT',
           parm_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

/*************************************************************************
STEP 5:
AUTHORIZE USERS FOR PRIVATE TEMPLATE
*************************************************************************/

--Since this is a private template (PUBLIC_TEMPLATE => 'N'
--in the DBMS_REPCAT_RGT.CREATE_REFRESH_TEMPLATE function
--defined in STEP 1), you need to authorize users to
--instantiate the DT_PERSONNEL deployment template. Use
--the DBMS_REPCAT_RGT.CREATE_USER_AUTHORIZATION function
--to create authorized users.

DECLARE
   a NUMBER;
BEGIN
   a := DBMS_REPCAT_RGT.CREATE_USER_AUTHORIZATION (
           user_name => 'SCOTT',
           refresh_template_name => 'DT_PERSONNEL');
END;
/

COMMIT;

Package for Instantiation

After you have completed building your deployment template, you need to package the template for instantiation. This example will illustrate how to use both the online and offline instantiation procedures (you will 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 will accomplish two tasks: create the instantiation script and save the instantiation script to a file.

Create Instantiation Script

When you execute either the INSTANTIATE_ONLINE or INSTANTIATE_OFFLINE functions, Oracle populates the USER_REPCAT_TEMP_OUTPUT view with the script to create the remote snapshot environment. The difference to remember is that an offline instantiation script contains the DDL and the DML to create both the snapshot environment and populate it with the appropriate data set. An online instantiation contains only the DDL to create the snapshot environment; the environment is populated with the data when the script is executed at the remote snapshot site (this requires a connection to the master site).

Complete the steps in either the "Offline Instantiation Package" or "Online Instantiation Package" according to your needs.

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 to create the snapshot environment, this script also contains the DML to populate the snapshot environment with the appropriate data set.

Note:
If you are packaging your template at the same master site that contains the target master objects for your deployment template, you will need to create a loopback database link.

--Use the INSTANTIATE_OFFLINE function to "package" the 
--template for offline instantiation by a remote snapshot 
--site. Executing this procedure will create a script that 
--creates that snapshot environment, as well as populate 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;
/

Be sure to note the number that is returned for the DT_NUM variable. You will be required to 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.

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 will create the snapshot site according to the DDL in the script and will populate 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.

For additional requirements, be sure to read "Deploying Template" in the Oracle8i Replication manual.

--Use the INSTANTIATE_ONLINE function to "package" the 
--template for online instantiation by a remote snapshot 
--site. Executing this procedure will create a script that 
--creates that 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;
/

Save Instantiation Script to File

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

Note:
The following steps must be performed immediately after you have called either the INSTANTIATE_OFFLINE or INSTANTIATE_ONLINE functions (the contents of the USER_REPCAT_TEMP_OUTPUT view are temporary). If you have not completed the steps in the "Create Instantiation Script" section (previous section), do so now and then complete the following steps.

Type SPOOL filename.sql (where filename is the name of your script) at the SVRMGR> prompt and press <ENTER>. For example, you might enter (since you may have to generate many instantiation files, make sure you name your files with an easily recognizable name):
```
SPOOL d:\snap1_world.sql
```
Your instantiation script will be 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.
Type the following select statement at the SVRMGR> prompt and press <ENTER>:
```
SELECT DBMS_REPCAT_RGT.VC2_FROM_CLOB(text) text 
FROM user_repcat_temp_output
WHERE output_id = dt_num ORDER BY LINE;
```
dt_num is the value that was returned when you executed the INSTANTIATE_ONLINE or INSTANTIATE_OFFLINE functions (illustrated in "Create Instantiation Script").
When Server Manager has completed displaying the contents of the REPCAT$_TEMP_OUTPUT table, type the following at the SVRMGR> prompt and press <ENTER>:
```
SPOOL OFF
```
The file that you specified in step 1 will be saved in the directory specified. This file contains the script required to build the snapshot environment.

After you have created the instantiation script and saved it to a file, you will need to distribute this file to the remote snapshot sites that need to instantiate the template. You can distribute this file by posting the file on an FTP site or saving the file to a CD-ROM, floppy disk, or other distribution media.

Instantiate Deployment Template

After the instantiation script has been distributed to the remote snapshot sites, either by FTP, CD-ROM, floppy disk, etc., you are ready to instantiate the deployment template at the remote snapshot site. Be sure to read "Deploying Template" in the Oracle8i Replication manual for a list of requirements that your snapshot site must meet before instantiating your deployment template.

This example uses SQL*Plus to instantiate the deployment template. If the remote snapshot site does not have SQL*Plus, see "Deploying Template" in the Oracle8i Replication manual for additional instantiation options.

The following script demonstrates how to complete the offline instantiation process at the remote snapshot site:

/*************************************************************************
STEP 1:
CREATE SCHEMA AND DATABASE LINKS
*************************************************************************/

--Before you execute the instantiation script at the remote snapshot site,
--you must create the schema that will contain the replicated objects.

CONNECT system/manager@snap1.world

CREATE USER scott IDENTIFIED BY tiger;

GRANT CONNECT, RESOURCE TO scott;

--Before you can create the private database link, you must create a public
--database link.

CREATE PUBLIC DATABASE LINK orc3.world USING 'orc3.world';

--Connect as the target user (wcreekba) and create a private database link
--to the target master site (the target user must also exist at the master
--site).

CONNECT scott/tiger@snap1.world

CREATE DATABASE LINK orc3.world 
   CONNECT TO scott IDENTIFIED BY tiger;

/*************************************************************************
STEP 2:
EXECUTE THE OFFLINE INSTANTIATION SCRIPT
*************************************************************************/

RUN D:\snap1_world.sql

Depending on the size of the snapshot environment created and the amount of data loaded, the instantiation procedure may take a substantial amount of time.

After Instantiation

Since you have just instantiated a deployment template using the offline instantiation method, you should perform a refresh as soon as possible; complete the following:

execute DBMS_REFRESH.REFRESH('PERSONNEL');

4Create Deployment Template