4 Creating a Deployment Template

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/user-password@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

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.

/************************* 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/user-password@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.

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.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 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.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 1), 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

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

--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/user-password@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;
/

--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/user-password@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;
/

GRANT CREATE ANY DIRECTORY TO repadmin;

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

CONNECT repadmin/user-password@orc3.world

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.

/************************* 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/user-password@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 user-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.world USING 'orc3.world';

CONNECT hr/user-password@mv4.world

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

/*

Step 3   Execute the instantiation script.

*/

CONNECT mviewadmin/user-password@mv4.world

@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/user-password@mv4.world

EXECUTE DBMS_REFRESH.REFRESH ('hr_refg');