Home
Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups
General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement |
| General PL/SQL Utilities Library |
Consider the following scenario. You have 8,200 retail stores each of which has an small in-store
Oracle database application the store uses to manage its local inventory. You want to have this application talking to the
corporate office real-time but also be able to operate if, due to network issues, the communications link is temporarily broken.
You are going to have to produce more than 32,800 files. And, just to make the job a little larger, you want to deploy properly by
first testing in dev, promoting to QA, and finally rolling out in the production environment. This is probably not the moment when
you are thinking "Thank goodness for Python and shell scripting."
The following code demonstrates a simple way to do this using a parameter configuration utility written in PL/SQL to generate the
files from table data.
First let's create the database user for the corporate office that will perform the parameter file creation process. |
CREATE TABLESPACE ggdata
DATAFILE 'c:\app\oracle\oradata\orabase\pdbdev\ggdata01.dbf' SIZE 50M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;
CREATE PROFILE gg_profile LIMIT
PASSWORD_LIFE_TIME 30
PASSWORD_GRACE_TIME 7
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 10
PASSWORD_REUSE_TIME 9999
PASSWORD_REUSE_MAX 9;
CREATE USER ggadmin
IDENTIFIED BY ggadmin
DEFAULT TABLESPACE ggdata
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON ggdata
PROFILE gg_profile
ENABLE EDITIONS;
GRANT create session TO ggadmin;
GRANT alter session TO ggadmin;
GRANT create table TO ggadmin;
GRANT create procedure TO ggadmin;
GRANT create any directory TO ggadmin;
GRANT select any dictionary TO ggadmin;
GRANT execute ON dbms_flashback TO ggadmin;
GRANT execute ON dbms_goldengate_auth TO ggadmin;
GRANT execute ON utl_file TO ggadmin; |
| Then create the configuration directory and tables with appropriate constraints and indexes. |
conn sys@pdbdev as sysdba
CREATE OR REPLACE DIRECTORY gg_pfiles AS 'c:\stage';
GRANT write ON DIRECTORY gg_pfiles TO ggadmin;
CREATE TABLE globals_params (
parameter_name VARCHAR2(40),
parameter_desc VARCHAR2(200))
COMPRESS;
ALTER TABLE globals_params
ADD CONSTRAINT pk_globals_params
PRIMARY KEY (parameter_name);
CREATE TABLE globals (
location VARCHAR2(20),
parameter_name VARCHAR2(40),
parameter_value VARCHAR2(30),
include_flag VARCHAR2(1))
COMPRESS;
ALTER TABLE globals
ADD CONSTRAINT pk_globals
PRIMARY KEY (location, parameter_name);
ALTER TABLE globals
ADD CONSTRAINT fk_globals_globals_params
FOREIGN KEY (parameter_name)
REFERENCES globals_params(parameter_name);
ALTER TABLE globals
ADD CONSTRAINT cc_globlas_include_flag
CHECK (include_flag IN ('N', 'Y'));
CREATE TABLE ddl_params (
parameter_name VARCHAR2(40),
parameter_desc VARCHAR2(200))
COMPRESS;
ALTER TABLE ddl_params
ADD CONSTRAINT pk_ddl_params
PRIMARY KEY (parameter_name);
CREATE TABLE ddls (
location VARCHAR2(20),
parameter_name VARCHAR2(40),
parameter_value VARCHAR2(512),
include_flag VARCHAR2(1))
COMPRESS;
ALTER TABLE ddls
ADD CONSTRAINT pk_ddls
PRIMARY KEY (location, parameter_name, parameter_value);
ALTER TABLE ddls
ADD CONSTRAINT fk_ddls_ddls_params
FOREIGN KEY (parameter_name)
REFERENCES ddl_params(parameter_name);
ALTER TABLE ddls
ADD CONSTRAINT cc_ddls_include_flag
CHECK (include_flag IN ('N', 'Y')); |
| Then load the tables with configuration metadata. |
BEGIN
INSERT INTO globals_params VALUES ('CHARMAP', 'Specifies that the character mapping file overrides the character code point mapping');
INSERT INTO globals_params VALUES ('CHARSET', 'Specifies a multibyte character set for the process to use instead of the operating
system default when reading the parameter file');
INSERT INTO globals_params VALUES ('CHECKPOINTTABLE', 'Specifies a default checkpoint table');
INSERT INTO globals_params VALUES ('CREDENTIALSTORELOCATION', 'Specifies the location of the GoldenGate credential store that stores login credentials');
INSERT INTO globals_params VALUES ('DDLTABLE', 'Specifies a non-default name for the DDL history table that supports DDL synchronization for Oracle');
INSERT INTO globals_params VALUES ('ENABLECATALOGNAMES', 'Enables support for three-part names for SQL/MX databases');
INSERT INTO globals_params VALUES ('ENABLEMONITORING', 'Enables Oracle GoldenGate Monitor to view and monitor Oracle GoldenGate instances');
INSERT INTO globals_params VALUES ('EXCLUDEWILDCARDOBJECTSONLY', 'Includes non-wildcarded source tables when a TABLEEXCLUDE, SCHEMAEXCLUDE, or CATALOGEXCLUDE parameter contains a wildcard');
INSERT INTO globals_params VALUES ('GGSCHEMA', 'Specifies the name of the schema that contains the database objects that support DDL synchronization for Oracle');
INSERT INTO globals_params VALUES ('MARKERTABLE', 'Specifies a non-default name for the DDL marker table that supports DDL synchronization for Oracle');
INSERT INTO globals_params VALUES ('MAXGROUPS', 'Specifies the maximum number of process groups that can run in an instance of Oracle GoldenGate');
INSERT INTO globals_params VALUES ('MGRSERVNAME', 'Specifies the name of the Manager process when it is installed as a Windows service');
INSERT INTO globals_params VALUES ('NAMECCSID', 'Specifies a DB2 for i CCSID if the object names in the SQL catalog are of a different CCSID than the system');
INSERT INTO globals_params VALUES ('NODUPMSGSUPPRESSION', 'Prevents the automatic suppression of duplicate informational and warning messages');
INSERT INTO globals_params VALUES ('OUTPUTFILEUMASK', 'Specifies a umask that can be used by GoldenGate processes to create trail files and discard files');
INSERT INTO globals_params VALUES ('USEANSISQLQUOTES', 'Enables SQL-92 rules for quoted object names and literals');
INSERT INTO globals_params VALUES ('NOUSEANSISQLQUOTES', 'Disables SQL-92 rules for quoted object names and literals');
INSERT INTO globals_params VALUES ('SYSLOG', 'Filters the types of Oracle GoldenGate messages that are written to the system logs');
INSERT INTO globals_params VALUES ('TRAILBYTEORDER', 'Specifies the byte order (endianness) of a file created with the EXTFILE, RMTFILE, EXTTRAIL, or RMTTRAIL parameter');
INSERT INTO globals_params VALUES ('TRAILCHARSET', 'Specifies the character set of the source data when the trail is of an older version that does not store the source character set, or to override the character set that is stored in the trail');
INSERT INTO globals_params VALUES ('USEIPV4', 'Forces Oracle GoldenGate to use IPv4 for TCP/IP connections');
INSERT INTO globals_params VALUES ('USEIPV6', 'Forces Oracle GoldenGate to use IPv6 for TCP/IP connections');
INSERT INTO globals_params VALUES ('WALLETLOCATION', 'Specifies the location of the master key wallet');
COMMIT;
END;
/
BEGIN
INSERT INTO globals VALUES ('HR', 'CHARSET', 'UTF-8', 'Y');
INSERT INTO globals VALUES ('ALL', 'CHECKPOINTTABLE', 'GGS_CHECKPOINT', 'Y');
INSERT INTO globals VALUES ('ALL', 'CREDENTIALSTORELOCATION', '/home/ogg/credentials', 'Y');
INSERT INTO globals VALUES ('ALL', 'DDLTABLE', 'GGS_DDL_HIST', 'N'); -- explicitly use default
INSERT INTO globals VALUES ('ALL', 'ENABLEMONITORING', '', 'Y');
INSERT INTO globals VALUES ('ALL', 'EXCLUDEWILDCARDOBJECTSONLY', '', 'Y');
INSERT INTO globals VALUES ('ALL', 'GGSCHEMA', 'GGADMIN', 'Y');
INSERT INTO globals VALUES ('ALL', 'MARKERTABLE', 'GGS_MARKER', 'Y'); -- explicitly use default
INSERT INTO globals VALUES ('ALL', 'MAXGROUPS', '1000', 'N'); -- explicitly use default
INSERT INTO globals VALUES ('CS', 'MGRSERVNAME', 'GG_CS_MGR', 'Y'); -- explicitly use default
INSERT INTO globals VALUES ('HR', 'MGRSERVNAME', 'GG_HR_MGR', 'Y'); -- explicitly use default
INSERT INTO globals VALUES ('ALL', 'NODUPMSGSUPPRESSION', '', 'Y');
INSERT INTO globals VALUES ('ALL', 'OUTPUTFILEUMASK', '022', 'Y');
INSERT INTO globals VALUES ('ALL', 'USEANSISQLQUOTES', '', 'Y'); -- explicitly use default
INSERT INTO globals VALUES ('ALL', 'SYSLOG', 'ALL', 'Y'); -- explicitly use default
INSERT INTO globals VALUES ('ALL', 'TRAILCHARSET', 'AL32UTF8, REPLACEBADCHAR', 'Y');
INSERT INTO globals VALUES ('ALL', 'USEIPV4', '', 'Y'); -- explicitly use default
COMMIT;
END;
/
BEGIN
INSERT INTO ddl_params VALUES ('DDL', 'Enables DDL support and filters DDL');
INSERT INTO ddl_params VALUES ('DDLERROR', 'Handles errors that occur during DDL replication');
INSERT INTO ddl_params VALUES ('DDLOPTIONS', 'Configures aspects of DDL replication other than filtering and string substitution');
INSERT INTO ddl_params VALUES ('DDLSUBST', 'Enables the substitution of strings in DDL operations');
INSERT INTO ddl_params VALUES ('DDLTABLE', 'Specifies an alternate name for the DDL history table');
INSERT INTO ddl_params VALUES ('GGSCHEMA', 'Specifies the name of the schema that contains the objects that support DDL replication');
INSERT INTO ddl_params VALUES ('PURGEDDLHISTORY', 'Controls the size of the DDL history table');
INSERT INTO ddl_params VALUES ('PURGEDDLHISTORYALT', 'Controls the size of the DDL history table');
INSERT INTO ddl_params VALUES ('PURGEMARKERHISTORY', 'Controls the size of the DDL marker table');
COMMIT;
END;
/
BEGIN
INSERT INTO ddls VALUES ('ALL', 'DDL', 'INCLUDE OBJNAME fin.* EXCLUDE OBJNAME fin.ss', 'Y');
INSERT INTO ddls VALUES ('ALL', 'DDL', 'DDL INCLUDE OBJTYPE ''INDEX''', 'Y');
INSERT INTO ddls VALUES ('ALL', 'DDL', 'DDL INCLUDE OBJTYPE ''SNAPSHOT''', 'Y');
INSERT INTO ddls VALUES ('ALL', 'DDLERROR', '1234 IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 10 & INCLUDE ALL OBJTYPE TABLE OBJNAME tab* EXCLUDE OBJNAME tab1*', 'Y');
INSERT INTO ddls VALUES ('ALL', 'DDLOPTIONS', 'DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10', 'Y');
INSERT INTO ddls VALUES ('ALL', 'DDLSUBST', 'DDLSUBST ''/file1/location1'' WITH ''/file2/location2'' INCLUDE INSTR ''logfile''', 'Y');
INSERT INTO ddls VALUES ('ALL', 'DDLTABLE', 'GGS_DDL_HIST', 'Y');
INSERT INTO ddls VALUES ('ALL', 'GGSCHEMA', 'GGADMIN', 'N');
INSERT INTO ddls VALUES ('ALL', 'PURGEDDLHISTORY', 'PURGEDDLHISTORY MINKEEPDAYS 8, MAXKEEPDAYS 15, FREQUENCYMINUTES 30', 'Y');
INSERT INTO ddls VALUES ('ALL', 'PURGEDDLHISTORYALT', '???', 'N');
INSERT INTO ddls VALUES ('ALL', 'PURGEMARKERHISTORY', 'PURGEMARKERHISTORY MINKEEPDAYS 8, MAXKEEPDAYS 32, FREQUENCYMINUTES 30', 'Y');
COMMIT;
END;
/ |
| PL/SQL Package |
CREATE OR REPLACE EDITIONABLE PACKAGE dbms_gg_deploy AUTHID CURRENT_USER IS
PROCEDURE build_global(pLoc IN globals.location%TYPE);
PROCEDURE build_manager(pLoc IN globals.location%TYPE);
PROCEDURE build_extract(pLoc IN globals.location%TYPE);
PROCEDURE build_pumpfile(pLoc IN globals.location%TYPE);
PROCEDURE build_replicat(pLoc IN globals.location%TYPE);
PROCEDURE build_ddl(pLoc IN globals.location%TYPE);
END dbms_gg_deploy;
/
CREATE OR REPLACE EDITIONABLE PACKAGE BODY "GG"."DBMS_GG_DEPLOY" IS
-------------------------------------------------------------------
PROCEDURE build_global(pLoc IN globals.location%TYPE) IS
pFile utl_file.file_type;
dirLoc VARCHAR2(30) := 'gg_pfiles';
CURSOR gcur IS
SELECT * FROM globals g
WHERE (UPPER(g.location) = UPPER(pLoc))
OR (UPPER(g.location) = 'ALL')
AND include_flag = 'Y'
ORDER BY parameter_name;
BEGIN
pFile := sys.utl_file.fopen('GG_PFILES', pLoc || '_' || 'globals', 'W');
FOR grec IN gcur LOOP
utl_file.putf(pFile, UPPER(grec.parameter_name) || ' ' || LOWER(grec.parameter_value));
utl_file.putf(pFile, '%s\n');
END LOOP;
utl_file.fclose(pFile);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Ouch!');
END build_global;
-------------------------------------------------------------------
PROCEDURE build_manager(pLoc IN globals.location%TYPE) IS
BEGIN
NULL;
END build_manager;
-------------------------------------------------------------------
PROCEDURE build_extract(pLoc IN globals.location%TYPE) IS
BEGIN
NULL;
END build_extract;
-------------------------------------------------------------------
PROCEDURE build_pumpfile(pLoc IN globals.location%TYPE)IS
BEGIN
NULL;
END build_pumpfile;
-------------------------------------------------------------------
PROCEDURE build_replicat(pLoc IN globals.location%TYPE) IS
BEGIN
NULL;
END build_replicat;
-------------------------------------------------------------------
PROCEDURE build_ddl(pLoc IN globals.location%TYPE) IS
pFile utl_file.file_type;
dirLoc VARCHAR2(30) := 'gg_pfiles';
CURSOR gcur IS
SELECT * FROM ddls g
WHERE (UPPER(g.location) = UPPER(pLoc))
OR (UPPER(g.location) = 'ALL')
AND include_flag = 'Y'
ORDER BY parameter_name;
BEGIN
pFile := sys.utl_file.fopen('GG_PFILES', pLoc || '_' || 'ddls', 'W');
FOR grec IN gcur LOOP
utl_file.putf(pFile, UPPER(grec.parameter_name) || ' ' || LOWER(grec.parameter_value));
utl_file.putf(pFile, '%s\n');
END LOOP;
utl_file.fclose(pFile);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Ouch!');
END build_ddl;
-------------------------------------------------------------------
END dbms_gg_deploy;
/
exec dbms_gg_deploy.build_global('HR');
exec dbms_gg_deploy.build_ddl('HR'); |
| ... |
|
