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.
See Also:
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 templatesIf 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:
TheCREATE
MATERIALIZED
VIEW
LOG
statement in the Oracle Database SQL Reference for detailed information about this SQL statementThis 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 templatesFigure 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 Replication Management tool if you want to create materialized views with a subset of the columns their master tables. See Oracle Database Advanced Replication and the Replication Management tool 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/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.
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 **********************************/
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
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 need to execute either theINSTANTIATE_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.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.
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/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.
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 theINSTANTIATE_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 theUTL_FILE
packageEnter 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/repadmin@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; /
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.
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.
See Also:
Oracle Database Advanced Replication for materialized view site requirements that must be met before instantiating your deployment template
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/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, then create database links for the 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 usersStep 3 Execute the instantiation script.
*/
CONNECT mviewadmin/mviewadmin@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 **********************************/
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');