4 Creating a Deployment Template

This chapter illustrates how to build a deployment template using the replication management 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.

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:

Oracle Database Advanced 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 must 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@orc3.example.com
Enter password: password

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 Oracle Database SQL Language 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:

Oracle Database Advanced Replication for conceptual and architectural information about deployment templates

Figure 4-1 Creating a Deployment Template

Description of Figure 4-1 follows
Description of "Figure 4-1 Creating a 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:

  • You must use the Advanced Replication interface in Oracle Enterprise Manager if you want to create materialized views with a subset of the columns their master tables. See Oracle Database Advanced Replication and the Advanced Replication interface online Help for more information about column subsetting.

  • If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note 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 the 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@orc3.example.com

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.

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 => 'MATERIALIZED VIEW',
           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 previously, 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 => 'MATERIALIZED VIEW',
           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 => 'MATERIALIZED VIEW',
           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 => 'MATERIALIZED VIEW',
           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 => 'MATERIALIZED VIEW',
           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 => 'MATERIALIZED VIEW',
           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 => 'MATERIALIZED VIEW',
           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. 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 tasks 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.example.com', 
      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 tasks 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.example.com');
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 1), you must 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 must 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

Description of Figure 4-2 follows
Description of "Figure 4-2 Packaging and Instantiating a Deployment Template"

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.

Note:

If you must 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@orc3.example.com
Enter password: password

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.example.com',
               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:

Oracle Database Advanced 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@orc3.example.com
Enter password: password

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.example.com',
               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.

These contents are saved to a directory that corresponds to a directory object. To create a directory object, the CREATE ANY DIRECTORY privilege is required. If the replication administrator does not have this privilege, then connect as an administrative user who can grant privileges. For example:

GRANT CREATE ANY DIRECTORY TO repadmin;

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:

Oracle Database 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 name of a directory object that represents the directory where you want to save the template script.

  • 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@orc3.example.com
Enter password: password

CREATE DIRECTORY GFILES AS '/home/gen_files';

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 ('GFILES', '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. You can also transfer the file using the DBMS_FILE_TRANSFER package.

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

See Also:

Note:

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note 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 ******************************

Step 1   If it does not exist, then create the schema at 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 might already exist in your database. In this case, the schema might need additional privileges, such as CREATE MATERIALIZED VIEW, ALTER ANY MATERIALIZED VIEW, and CREATE DATABASE LINK.

*/

SET ECHO ON

SPOOL instant_mv.out

CONNECT system@mv4.example.com

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;
ACCEPT password PROMPT 'Enter password for user: ' HIDE

CREATE USER hr IDENTIFIED BY &password;

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, then create database links for the schema.

Before instantiating the deployment template, you must ensure 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.example.com USING 'orc3.example.com';

CONNECT hr@mv4.example.com

CREATE DATABASE LINK orc3.example.com 
   CONNECT TO proxy_refresher IDENTIFIED BY &password;

/*

See Also:

Step 7 for more information about creating proxy master site users

Step 3   Execute the instantiation script.

*/
CONNECT mviewadmin@mv4.example.com

@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 might 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@mv4.example.com
Enter password: password

EXECUTE DBMS_REFRESH.REFRESH ('hr_refg');