4
Create a Deployment Template

This chapter illustrates how to build a deployment template using the replication managment API. This chapter contains these topics:

• Oracle Deployment Templates Concepts
• Before Creating the Deployment Template
• Creating a Deployment Template
• Packaging a Deployment Template for Instantiation
• Instantiating a Deployment Template

Before you build materialized view environments, you must set up your master site, create a master group, and set up your intended materialized view sites. Also, if conflicts are possible at the master site due to activity at the materialized view sites you are creating, then configure conflict resolution for the master tables of the materialized views before you create the materialized view group.

Oracle Deployment Templates Concepts

Oracle offers deployment templates to allow the database administrator to package a materialized view environment for easy, custom, and secure distribution and installation. A deployment template can be simple (for example, it can contain a single materialized view with a fixed data set), or complex (for example, it can contain hundreds of materialized views with a dynamic data set based on one or more variables). The goal is to define the environment once and deploy the deployment template as often as necessary. Oracle deployment templates feature:

• Central control
• Repeated deployment of a materialized view environment
• Data subsetting at remote sites using template parameters
• Authorized user list to control template instantiation and data access

To prepare a materialized view environment for deployment, the DBA creates a deployment template at the master site. This template stores all of the information needed to deploy a materialized view 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 materialized view creation.

You cannot use deployment templates to instantiate the following types of objects:

• User-defined types
• User-defined type bodies
• User-defined operators
• Indextypes

Nor can you use deployment templates to instantiate any objects based on these types of objects.

Before Creating the Deployment Template

If you want one of your master sites to support a materialized views that can be fast refreshed, then you need to create materialized view logs for each master table that is replicated to a materialized view.

The example in this chapter uses the hr sample schema. Enter the following to create materialized view logs for the tables in the hr schema:

CONNECT hr/hr@orc3.world

CREATE MATERIALIZED VIEW LOG ON hr.countries;
CREATE MATERIALIZED VIEW LOG ON hr.departments;
CREATE MATERIALIZED VIEW LOG ON hr.employees;
CREATE MATERIALIZED VIEW LOG ON hr.jobs;
CREATE MATERIALIZED VIEW LOG ON hr.job_history;
CREATE MATERIALIZED VIEW LOG ON hr.locations;
CREATE MATERIALIZED VIEW LOG ON hr.regions;

Creating a Deployment Template

This section contains a complete script example of how to construct a deployment template using the replication management API.

Figure 4-1 Creating a Deployment Template

Text description of the illustration rardta.gif

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.

/************************* BEGINNING OF SCRIPT ******************************

This script creates a private deployment template that contains four template objects, two template parameters, a set of user parameter values, and an authorized user. Complete the following steps to build a template:

Step 1 Create deployment template.

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

*/

SET ECHO ON

SPOOL create_dt.out

CONNECT repadmin/repadmin@orc3.world

DECLARE
   a NUMBER;
BEGIN
   a := DBMS_REPCAT_RGT.CREATE_REFRESH_TEMPLATE (
           owner => 'hr',
           refresh_group_name => 'hr_refg',
           refresh_template_name => 'hr_refg_dt',
           template_comment => 'Human Resources Deployment Template',
           public_template => 'N');
END;
/

/*

Step 2 Add objects to template by completing the following steps.

Step 3 Define parameter defaults.

Rather than using the CREATE_* functions and procedures as in the other steps, use the ALTER_TEMPLATE_PARM procedure to define a template parameter value and prompt string. You use the ALTER_* procedure because the actual parameter was created in Step 1b and 1c. Recall that you defined the :dblink and :dept template parameters in the ddl_text parameter. Oracle detects these parameters in the DDL and automatically creates the template parameter. Use the ALTER_TEMPLATE_PARM procedure to define the remainder of the template parameter information (that is, default parameter value and prompt string).

Complete the following steps to define parameter defaults.

Step 4 Define user parameter values.

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

Complete the following steps to define user parameter values.

Step 5 Authorize users for private template.

Because this is a private template (public_template => 'n' in the DBMS_REPCAT_RGT.CREATE_REFRESH_TEMPLATE function defined in Step ), you need to authorize users to instantiate the dt_personnel deployment template. Use the CREATE_USER_AUTHORIZATION function in the DBMS_REPCAT_RGT package to create authorized users.

*/

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

COMMIT;

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT **********************************/

Packaging a Deployment Template for Instantiation

After 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 describes two tasks: create the instantiation script and save the instantiation script to a file.

Figure 4-2 Packaging and Instantiating a Deployment Template

Text description of the illustration rardt2.gif

Packaging a Deployment Template

When you execute either the INSTANTIATE_OFFLINE or the INSTANTIATE_ONLINE function, Oracle populates the USER_REPCAT_TEMP_OUTPUT data dictionary view with the script to create the remote materialized view 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 materialized view site connects to the master site to download the data.

Complete the steps in either the "Packaging a Deployment Template for Offline Instantiation" or "Packaging a Deployment Template for Online Instantiation" according to your needs. These sections only apply to packaging templates for materialized view sites running Oracle Enterprise Edition, Oracle Standard Edition, or Oracle Personal Edition.

Packaging a Deployment Template for Offline Instantiation

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

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

CONNECT repadmin/repadmin@orc3.world

SET SERVEROUTPUT ON
DECLARE
   dt_num NUMBER;
BEGIN
   dt_num := DBMS_REPCAT_RGT.INSTANTIATE_OFFLINE(
               refresh_template_name => 'hr_refg_dt',
               user_name => 'hr',
               site_name => 'mv4.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 data dictionary view to retrieve the generated script. Be sure that you complete the steps in "Saving an Instantiation Script to File" after you complete this section. This script is unique to an individual materialized view site and cannot be used for other materialized view sites.

Packaging a Deployment Template for Online Instantiation

The INSTANTIATE_ONLINE function creates a script that creates the materialized view environment according to the contents of a specified deployment template. When this script is executed at the remote materialized view site, Oracle creates the materialized view 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 materialized view site has a "live" connection to the master site.

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

CONNECT repadmin/repadmin@orc3.world

SET SERVEROUTPUT ON
DECLARE
   dt_num NUMBER;
BEGIN
   dt_num := DBMS_REPCAT_RGT.INSTANTIATE_ONLINE(
               refresh_template_name => 'hr_refg_dt',
               user_name => 'hr',
               site_name => 'mv4.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 data dictionary view to retrieve the generated script. Be sure that you complete the steps in "Saving an Instantiation Script to File" after you complete this task.

Saving an Instantiation Script to File

The best way to save the contents of the USER_REPCAT_TEMP_OUTPUT data dictionary view is to use the UTL_FILE package to save the contents of the TEXT column in the USER_REPCAT_TEMP_OUTPUT view to a file.

Enter the following to save the deployment template script to a file.

DECLARE 
  fh UTL_FILE.FILE_TYPE; 
  CURSOR ddlcursor(myid NUMBER) IS 
  SELECT TEXT FROM USER_REPCAT_TEMP_OUTPUT WHERE OUTPUT_ID = myid ORDER BY LINE; 
BEGIN 
  fh := UTL_FILE.FOPEN ('file_location', 'file_name', 'w'); 
  UTL_FILE.PUT_LINE (fh, 'SET ECHO OFF;'); 
  FOR myrec IN ddlcursor(template_id) LOOP
    UTL_FILE.PUT_LINE(fh, myrec.text); 
  END LOOP; 
  UTL_FILE.PUT_LINE (fh, 'SET ECHO ON;'); 
  UTL_FILE.FFLUSH(fh); 
  UTL_FILE.FCLOSE(fh); 
END; 
/ 

Notice that file_location, file_name, and template_id are placeholders. Substitute the correct values for your environment:

• Replace the file_location placeholder with the full directory path where you want to save the template script.

  Note: The location you specify for the template script must be a location listed in the UTL_FILE_DIR initialization parameter. If you specify a location that is not listed in the UTL_FILE_DIR initialization parameter, then Oracle returns errors when you try to save the template script to a file. See Oracle9i Replication for more information about UTL_FILE_DIR.

• Replace the file_name placeholder with name you want to use for the template script.
• Replace the template_id placeholder with the number returned by the INSTANTIATE_OFFLINE or INSTANTIATE_ONLINE function when you packaged the template previously.

For example, suppose you have the following values:

Placeholder	Value
file_location	/home/gen_files/
file_name	sf.sql
template_id	18

Given these values, connect to the master site as the replication administrator and run the following procedure to save the template script to a file:

CONNECT repadmin/repadmin@orc3.world

DECLARE 
  fh UTL_FILE.FILE_TYPE; 
  CURSOR ddlcursor(myid NUMBER) IS 
  SELECT TEXT FROM USER_REPCAT_TEMP_OUTPUT WHERE OUTPUT_ID = myid 
  ORDER BY LINE; 
BEGIN 
  fh := UTL_FILE.FOPEN ('/home/gen_files/', 'sf.sql', 'w'); 
  UTL_FILE.PUT_LINE (fh, 'SET ECHO OFF;'); 
  FOR myrec IN ddlcursor(18) LOOP
    UTL_FILE.PUT_LINE(fh, myrec.text); 
  END LOOP; 
  UTL_FILE.PUT_LINE (fh, 'SET ECHO ON;'); 
  UTL_FILE.FFLUSH(fh); 
  UTL_FILE.FCLOSE(fh); 
END; 
/ 

Distributing Instantiation Files

After creating the instantiation script and saving it to a file, you must distribute this file to the remote materialized view 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 medium.

Instantiating a Deployment Template

After the instantiation script has been distributed to the remote materialized view sites, you are ready to instantiate the deployment template at the remote materialized view site. Make sure you have set up the materialized view site before you instantiate the deployment template.

The following script demonstrates how to complete the instantiation process at a remote materialized view site with Oracle Enterprise Edition, Oracle Standard Edition, or Oracle Personal Edition installed.

/************************* BEGINNING OF SCRIPT ******************************

Step 1 If it does not already exist, create the schema at the materialized view site.

Before executing the instantiation script at the remote materialized view site, you must create the schema that contains the replicated objects.

The following illustrates creating the hr schema. This schema may already exist in your database.

*/

SET ECHO ON

SPOOL instant_mv.out

CONNECT system/manager@mv4.world

CREATE TABLESPACE demo_mv
 DATAFILE 'demo_mv.dbf' SIZE 10M AUTOEXTEND ON
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE TEMPORARY TABLESPACE temp_mv
 TEMPFILE 'temp_mv.dbf' SIZE 5M AUTOEXTEND ON;

CREATE USER hr IDENTIFIED BY hr;

ALTER USER hr DEFAULT TABLESPACE demo_mv
              QUOTA UNLIMITED ON demo_mv;

ALTER USER hr TEMPORARY TABLESPACE temp_mv;

GRANT 
  CREATE SESSION, 
  CREATE TABLE, 
  CREATE PROCEDURE, 
  CREATE SEQUENCE, 
  CREATE TRIGGER, 
  CREATE VIEW, 
  CREATE SYNONYM, 
  ALTER SESSION,
  CREATE MATERIALIZED VIEW,
  ALTER ANY MATERIALIZED VIEW,
  CREATE DATABASE LINK
 TO hr;

/*

Step 2 If they do not already exist, create the database links for the replicated schema.

Before instantiating the deployment template, you must make sure that the necessary database links exist for the replicated schema. The owner of the materialized views needs a database link pointing to the proxy_refresher that was created when the master site was set up.

*/

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

CONNECT hr/hr@mv4.world

CREATE DATABASE LINK orc3.world 
   CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher;

/*

Step 3 Execute the instantiation script.

*/

@d:\sf.sql

SET ECHO OFF

SPOOL OFF

/*

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

************************** END OF SCRIPT **********************************/

Refreshing a Refresh Group After Instantiation

If you have just instantiated a deployment template using the offline instantiation method, then you should perform a refresh of the refresh group as soon as possible by issuing the following execute statement:

CONNECT hr/hr@mv4.world

EXECUTE DBMS_REFRESH.REFRESH ('hr_refg');