4
Create a Deployment Template

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

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.

See Also:

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.

See Also:

Oracle9i Replication for more conceptual information about deployment templates

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;

See Also:

The CREATE MATERIALIZED VIEW LOG statement in the Oracle9i SQL Reference for detailed information about this SQL statement

Creating a Deployment Template

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

See Also:

Oracle9i Replication for conceptual and architectural information about deployment templates

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.

Note:

You must use the Replication Management tool if you want to create materialized views with a subset of the columns their master tables. See Oracle9i Replication and the Replication Management tool online help for more information about column subsetting.

Note:

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page to the "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment.

/************************* 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.

Create countries_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.countries_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      country_id, country_name, region_id 
      FROM hr.countries@:dblink';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'hr_refg_dt',
           object_name => 'countries_mv',
           object_type => 'SNAPSHOT',
           ddl_text => tempstring,
           master_rollback_seg => 'rbs');
END;
/

/*

Whenever you create a materialized view, always specify the schema name of the table owner in the query for the materialized view. In the example above, hr is specified as the owner of the countries table.

Create departments_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.departments_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      department_id, department_name, manager_id, location_id
      FROM hr.departments@:dblink';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'hr_refg_dt',
           object_name => 'departments_mv',
           object_type => 'SNAPSHOT',
           ddl_text => tempstring,
           master_rollback_seg => 'rbs');
END;
/

/*

Create employees_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.employees_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      employee_id, first_name, last_name, email, phone_number, 
      hire_date, job_id, salary, commission_pct, manager_id, 
      department_id
      FROM hr.employees@:dblink WHERE department_id = :dept';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'hr_refg_dt',
           object_name => 'employees_mv',
           object_type => 'SNAPSHOT',
           ddl_text => tempstring,
           master_rollback_seg => 'rbs');
END;
/

/*

Create jobs_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.jobs_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      job_id, job_title, min_salary, max_salary 
      FROM hr.jobs@:dblink';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'hr_refg_dt',
           object_name => 'jobs_mv',
           object_type => 'SNAPSHOT',
           ddl_text => tempstring,
           master_rollback_seg => 'rbs');
END;
/

/*

Create job_history_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.job_history_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      employee_id, start_date, end_date, job_id, department_id 
      FROM hr.job_history@:dblink';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'hr_refg_dt',
           object_name => 'job_history_mv',
           object_type => 'SNAPSHOT',
           ddl_text => tempstring,
           master_rollback_seg => 'rbs');
 END;
/

/*

Create locations_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.locations_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      location_id, street_address, postal_code, city, 
      state_province, country_id 
      FROM hr.locations@:dblink';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'hr_refg_dt',
           object_name => 'locations_mv',
           object_type => 'SNAPSHOT',
           ddl_text => tempstring,
           master_rollback_seg => 'rbs');
END;
/

/*

Create regions_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.regions_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      region_id, region_name 
      FROM hr.regions@:dblink';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'hr_refg_dt',
           object_name => 'regions_mv',
           object_type => 'SNAPSHOT',
           ddl_text => tempstring,
           master_rollback_seg => 'rbs');
END;
/

/*

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 1 b and 1 c. 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.

Define the default value for the dept parameter.

*/

BEGIN 
   DBMS_REPCAT_RGT.ALTER_TEMPLATE_PARM ( 
      refresh_template_name => 'hr_refg_dt', 
      parameter_name => 'dept', 
      new_default_parm_value => '30', 
      new_prompt_string => 'Enter your department number:', 
      new_user_override => 'Y'); 
END; 
/ 

/*

Define the default value for the dblink parameter.

*/

BEGIN 
   DBMS_REPCAT_RGT.ALTER_TEMPLATE_PARM ( 
      refresh_template_name => 'hr_refg_dt', 
      parameter_name => 'dblink', 
      new_default_parm_value => 'orc3.world', 
      new_prompt_string => 'Enter your master site:', 
      new_user_override => 'Y'); 
END; 
/  

/*

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.

Define dept user parameter value for user hr.

*/

DECLARE
   a NUMBER;
BEGIN
   a := DBMS_REPCAT_RGT.CREATE_USER_PARM_VALUE (
           refresh_template_name => 'hr_refg_dt',
           parameter_name => 'dept',
           user_name => 'hr',
           parm_value => '20');
END;
/

/*

Define dblink user parameter value for user hr.

*/

DECLARE
   a NUMBER;
BEGIN
   a := DBMS_REPCAT_RGT.CREATE_USER_PARM_VALUE (
           refresh_template_name => 'hr_refg_dt',
           parameter_name => 'dblink',
           user_name => 'hr',
           parm_value => 'orc3.world');
END;
/

/*

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

Package and Instantiate 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.

Note:

If you need to execute either the INSTANTIATE_OFFLINE or the INSTANTIATE_ONLINE function more than once for a particular materialized view site, then run the DROP_SITE_INSTANTIATION procedure in the DBMS_REPCAT_RGT package before you attempt to repackage a template for the site. Otherwise, Oracle returns an error stating that there is a duplicate template site.

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.

Note:

If you are packaging your template at the same master site that contains the target master objects for your deployment template, then you must create a loopback database link.

--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.

See Also:

Oracle9i Replication for additional materialized view site requirements

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

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.

Note:

The following action must be performed immediately after you have called either the INSTANTIATE_OFFLINE or INSTANTIATE_ONLINE functions, because the contents of the USER_REPCAT_TEMP_OUTPUT data dictionary view are temporary. If you have not completed the steps in "Packaging a Deployment Template", then do so now and then complete the following action.

See Also:

Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the UTL_FILE package

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.

See Also:

Oracle9i Replication for materialized view site requirements that must be met before instantiating your deployment template
"Setting Up Materialized View Sites"

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.

Note:

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

/*

See Also:

Step 7 for more information about creating proxy master site users

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');

4 Create a Deployment Template

Oracle Deployment Templates Concepts

Before Creating the Deployment Template

Creating a Deployment Template

Figure 4-1 Creating a Deployment Template

Step 1 Create deployment template.

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

Step 3 Define parameter defaults.

Step 4 Define user parameter values.

Step 5 Authorize users for private template.

Packaging a Deployment Template for Instantiation

Figure 4-2 Packaging and Instantiating a Deployment Template

Packaging a Deployment Template

Packaging a Deployment Template for Offline Instantiation

Packaging a Deployment Template for Online Instantiation

Saving an Instantiation Script to File

Distributing Instantiation Files

Instantiating a Deployment Template

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

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

Step 3 Execute the instantiation script.

Refreshing a Refresh Group After Instantiation

4
Create a Deployment Template