This topic includes the following:
When capturing, transforming, and delivering data across disparate systems and databases, you must understand both the source and target layouts. Understanding column names and data types is instrumental to the data synchronization functions of Oracle GoldenGate.
The DEFGEN utility produces a file defining the source files and tables' layouts. These definitions are used by the Collector and by Replicat. In some cases, Extract also uses a definition file containing the target layouts when transformation operations are required on the source system.
The output definitions are written and saved to a text file and transferred to all target systems in text format. When they start, Replicat and the Collector read the definitions to interpret the data formats read from Oracle GoldenGate trails.
Note:
Do not modify the text file that is output from DEFGEN.
Once you have generated your definitions, you must specify their location in your process' parameter file. Replicat uses the SOURCEDEFS parameter to indicate which source definition file to use. Collector uses the –d argument at startup to specify which source definition file to use.
Run DEFGEN interactively or using a batch obey script.
TACL> RUN DEFGEN [/IN command_file/] [EXCLUDESYSTEM] [EXPANDDDL options] [RECORDNAMEPROMPTING]
For more information, see DEFGEN Arguments.
Parent topic: Using Oracle GoldenGate Utilities
Definition for table $DATA1.GGSSOU.TCUSTMER Record length: 198 Syskey: 0 Columns: 13 TS 134 8 0 0 0 0 1 8 8 0 0 0 0 1 0 1 RECNUM 132 4 8 0 0 0 1 4 4 4 0 0 0 0 1 0 1 SYSNAME 1 8 12 0 0 0 0 8 8 8 0 0 0 0 1 0 0 TEXT 0 64 20 0 0 0 0 64 64 64 0 0 0 0 1 0 0 VAL1 134 8 84 0 0 0 1 8 8 8 0 0 0 0 1 0 0 VAL2 134 8 92 0 0 0 1 8 8 8 0 0 0 0 1 0 0 COL_COMPUTE 134 8 100 0 0 0 1 8 8 8 0 0 0 0 1 0 0 I16 130 2 108 0 0 0 1 2 2 2 0 0 0 0 1 0 0 I32 132 4 110 0 0 0 1 4 4 4 0 0 0 0 1 0 0 I64 134 8 114 0 0 0 1 8 8 8 0 0 0 0 1 0 0 I32_TOTAL 132 4 122 0 0 0 1 4 4 4 0 0 0 0 1 0 0 JTS 134 8 126 0 0 0 1 8 8 8 0 0 0 0 1 0 0 JTS_TEXT 0 64 134 0 0 0 0 64 64 64 0 0 0 0 1 0 0 End of definition
Parent topic: Generating Data Definitions with DEFGEN
Multiple tables that have the same structure (identical columns, column order, and data types) can use the same definition. To run DEFGEN for these tables, use the RECORDNAMEPROMPTING argument.
In response to the prompts, enter information similar to the following example:
| For the prompt: | Enter: |
|---|---|
|
Enter definitions file name (or Exit): |
$DATA1.GGSDEF.CUSTDEF |
|
File/Table to create definition for (or Exit): |
$DATA1.GGSSOU.ECUSTMER |
|
Use record name for definition file (Y/N)? |
Y |
|
Record or definition name to be used: |
CUSTOMER-DEF |
|
File/Table to create definition for (or Exit) |
EXIT |
Parent topic: Generating Data Definitions with DEFGEN
DDLGEN generates table definitions for target databases based on existing Enscribe and NonStop SQL definitions. It can also use the output from the DEFGEN utility (see above). DDLGEN reduces the work necessary to create databases on platforms such as UNIX or Windows, and enables the creation of NonStop SQL databases based on Enscribe definitions. Target templates are provided for NonStop SQL, Oracle, SQL Server, DB2, and Sybase.
DDLGEN is run either interactively by supplying responses to user prompts, or in batch mode by supplying an input file.When running interactively, the user is supplied several prompts. For batch execution, the answers to the prompts are supplied in the obey file. It is recommended that the user runs the process in interactive mode to better understand the replies to specify in the obey file.
The result of running DDLGEN is a text file containing the create table statements. Transfer this file, as a text file, to your target system.
For more information, see DDLGEN Arguments in the Reference for Oracle GoldenGate on HP NonStop Guardian.
The DDLGEN syntax is:
TACL> RUN DDLGEN [/IN command_file/] [-d DEFGEN_output]
| Argument | Description |
|---|---|
IN command_file
|
If you have created and saved a file of responses using the NonStop editor, enter the name of that file. See the information on configuring |
-d DEFGEN_output
|
Instructs |
Parent topic: Using Oracle GoldenGate Utilities
Enscribe record definitions often contain the following items that do not map directly to SQL environments:
OCCURS items
SQL columns cannot have multiple occurrences, while Enscribe fields can.
Group level items
There is no grouping hierarchy in SQL, while an Enscribe record definition can contain fields that are redundant until qualified at the group level. This means that redundant column names can be created when mapping Enscribe definitions to SQL. For example, an Enscribe record might contain the field YEAR twice, once within the BEGIN-DATE group and once within the END-DATE group.
To get around these conditions, you can run the DEFGEN utility with various EXPANDDDL options set, as described in Reference for Oracle GoldenGate on HP NonStop Guardian. Use the -d parameter to specify the definitions file created by DEFGEN as input to DDLGEN.
Parent topic: Creating Target Database DDL
Templates are provided with each version of Oracle GoldenGate. A template file specifies how to generate the target definitions based on the source definitions. Each template file contains the following items:
Literal text to output for each table definition.
Source to target data type conversion specifications.
Column name substitution specifications.
Miscellaneous run-time parameters.
Section headers.
Comments, which begin with a pound sign (#).
Session parameters, which are resolved at run-time by user prompts and applied during the entire DDLGEN session. Session parameters begin with a question mark (?).
Per-table parameters, which are input by the user for each table definition generated. Per-table parameters begin with a percent sign (%).
Calculated parameters, which include information determined by DDLGEN. Calculated parameters include the following:
| Calculated Parameter | Description |
|---|---|
?TABLE |
The file name portion of the source table or file. |
?COLUMNS |
A list containing each column, its target data type, precision and scale (if any), and null/not null syntax. |
?KEYCOLUMNS |
A list containing each column in the primary key. |
?MAXPAGES ?MAXMEGS ?CURPAGES ?CURMEGS |
The maximum and current number of 2048-byte pages and megabytes in the source table. |
Parent topic: Creating Target Database DDL
This sample (TMPLORA), is a template file for converting HP NonStop DDL to Oracle DDL.
The sections are:
Table creation section specifying operations for creating and managing tables. Note that in this section ?TABLE, ?COLUMNS and ?KEYCOLUMNS are resolved by DDLGEN. ?TABLE_SPACE is prompted for once and will apply to every table, while %NEXT_SIZE is prompted for on a per-table basis.
Column name mapping section containing source NonStop and target Oracle column names. This section maps the source column names to the target names. In this example, any occurrences of ROWID in the NonStop database will be changed to ROWID_ in the Oracle definition. If Oracle keywords appear in your NonStop database definitions, add entries to this list.
Miscellaneous parameters section. Specify instructions for column formatting.
Column type mapping section. Determines how NonStop types are defined in Oracle. Precision and Scale definitions can be YES, NO or a constant, positive value.
Note in this example, there are two entries for both CHAR and VARCHAR. Because Oracle allows a maximum of 255 characters in a VARCHAR2, we specify that all instances of CHAR and VARCHAR with length greater than 255 should become LONGS.
# Table Creation Section # DROP TABLE ?TABLE; CREATE TABLE ?TABLE ( ?COLUMNS ,CONSTRAINT PK_?TABLE PRIMARY KEY ( ?KEYCOLUMNS ) USING INDEX TABLESPACE ?TABLE_SPACE ) TABLESPACE ?TABLE_SPACE STORAGE (INITIAL 50K NEXT %NEXT_SIZE); # # Column Name Mapping Section # # NonStop column name Oracle target name # ROWID ROWID_ SYSDATE SYSDATE_ # # Miscellaneous Parameters Section # INCLUDENULL # # Column Type Mapping Section # Max Max #NonStop Type Target DB Type Precision Scale Prec. Scale # CHAR VARCHAR2 Y N 255 N CHAR LONG Y N N N VARCHAR VARCHAR2 Y N 255 N VARCHAR LONG N N N N REAL NUMBER N N N N DOUBLE NUMBER N N N N NUMERIC NUMBER Y Y N N SMALLINT NUMBER Y N N N INTEGER NUMBER Y N N N LARGEINT NUMBER Y N N N DECIMAL NUMBER Y Y N N DATE DATE N N N N TIME DATE N N N N TIMESTAMP DATE N N N N DATETIME VARCHAR2 Y N N N INTERVAL VARCHAR2 Y N N N
Parent topic: Understanding the Template File
An example for creating Oracle table DDL is shown below.
CREATE TABLE TCUSTORD
(
CUST_CODE CHAR (4) NOT NULL,
ORDER_DATE DATETIME YEAR TO SECOND NOT NULL,
PRODUCT_CODE CHAR (8) NOT NULL,
ORDER_ID NUMERIC (18) NOT NULL,
PRODUCT_PRICE DECIMAL (8,2),
PRODUCT_AMOUNT DECIMAL (6,0),
TRANSACTION_ID NUMERIC (18) NOT NULL,
DESCRIPTION CHAR (400),
PRIMARY KEY (CUST_CODE, ORDER_DATE, PRODUCT_CODE, ORDER_ID)
);
Parent topic: Understanding the Template File
In this example, you can make three modifications:
DATE_MODIFIED column to each table and to the primary key.NEXT value based on the table's current size. Note that any parameter value that evaluates to a numeric value can be multiplied or divided.ORDER_NUM for instances of ORDER_ID.The following is the template file, with modifications shown in bold.
# # Table Creation Section # DROP TABLE ?TABLE; CREATE TABLE ?TABLE ( ?COLUMNS , DATE_MODIFIEDDATE NOT NULL , CONSTRAINT PK_?TABLE PRIMARY KEY ( ?KEYCOLUMNS , DATE_MODIFIED ) USING INDEX TABLESPACE ?TABLE_SPACE ) TABLESPACE ?TABLE_SPACE STORAGE (INITIAL 50K NEXT ?CURPAGES/5K); # # Column Name Mapping Section # # NonStop column name Oracle target name # ROWID ROWID_ SYSDATE SYSDATE_ ORDER_ID ORDER_NUM
Parent topic: Understanding the Template File
The following example generates an Oracle definition:
1> RUN DDLGEN Output file for table DDL (or Exit): ORADDL DDL template file name (or Exit): TMPLORA Value for param TABLE_SPACE: USERS Source File/Table (or Exit): $DATA1.SAMPLE.TCUSTORD Source File/Table (or Exit): EXIT
The following is an example of the contents of ORADDL after DDLGEN is run.
DROP TABLE TCUSTORD; CREATE TABLE TCUSTORD ( CUST_CODE VARCHAR2(4) NOT NULL , ORDER_DATE DATE NOT NULL , PRODUCT_CODE VARCHAR2(8) NOT NULL , ORDER_NUM NUMBER(18) NOT NULL , PRODUCT_PRICE NUMBER(8,2) NULL , PRODUCT_AMOUNT NUMBER(6,0) NULL , TRANSACTION_ID NUMBER(18) NULL , DATE_MODIFIED DATE NOT NULL , CONSTRAINT PK_TCUSTORD PRIMARY KEY ( CUST_CODE , ORDER_DATE , PRODUCT_CODE , ORDER_NUM , DATE_MODIFIED ) USING INDEX TABLESPACE USERS ) TABLESPACE USERS STORAGE (INITIAL 50K NEXT 470K);
Parent topic: Understanding the Template File
You can run Oracle GoldenGate for selective rollback processing when synchronizing data from TMF applications. Oracle GoldenGate rollback uses before-images from the TMF audit trail to undo database changes for user-defined tables and files, rows and records, and time periods. Two valuable applications of rollback are:
Reversing errors caused by damaged data or accidental deletions. For example, if you issue a SQL UPDATE or DELETE command without the WHERE clause, rollback reverses the errors caused by the erroneous operations.
Maintaining large test databases. Use rollback to restore the test database to its state before a test run, enabling test cycles to run quickly. Since the rollback process only reverses changes, a database can be restored much more efficiently than a complete database restore.
The Reverse program prepares extracted data for rollback by:
Reversing the ordering of database operations in the file so Replicat processes them in reverse order, guaranteeing that records with the same key are properly applied.
Changing the before or after-image indicator in each record.
Changing delete operations to inserts, and insert operations to deletes.
Reversing the BEGIN and END transaction indicators to delimit each transaction.
Syntax
RUN REVERSE source_extract_trail target_extract_trail
| Argument | Description |
|---|---|
source_extract_trail
|
The |
target_extract_trail
|
The |
Note:
source_extract_trail and target_extract_trail must be different.
Parent topic: Using Oracle GoldenGate Utilities
Before applying rollback, you may want to review your pending changes. To review, run Extract with the FORMATSQL parameter. The Extract file contains SQL syntax describing each of the statements to be reversed.
Parent topic: Oracle GoldenGate Rollback
If rollback produces unexpected or undesired results, you can reapply the original changes to the database. Use the source Extract file (source_extract_trail) specified in the REVERSE command as the EXTFILE entry in your Replicat parameter file and run Replicat again.
Parent topic: Oracle GoldenGate Rollback
Rollback does not work in the following circumstances:
Because entry-sequenced does not support deletes, rollback does not work when inserts are reversed for an entry-sequenced table or file.
When a table is organized with a cluster key (a non-unique primary key), reversing deletes results in SYSKEY values that are different from the original rows. The delete's before-image is applied as an insert into the table. Replicat cannot control the SYSKEY value, so it becomes the timestamp when the records are reversed and relationships depending on the original SYSKEY are lost. In addition, if both deletes and inserts are reversed, results will likely be incorrect. If a set of columns can be defined that uniquely identifies the record, you can use the KEYCOLS option of the Replicat MAP statement.
Parent topic: Oracle GoldenGate Rollback
Syncfile manages non-database file duplication. For example, you may want to replicate configuration files that are small and change infrequently. This is a common requirement for maintaining a secondary system that has frequent database changes, but infrequent configuration file changes.
Syncfile can copy almost any type of file, making it suitable for other scenarios that require only infrequent, off-hours copying. By default, Syncfile uses the NonStop FUP DUP utility to perform file duplication; however, it can also run user-written TACL scripts to perform more specialized file duplication, such as FTP over TCP/IP.
You implement Syncfile by defining its parameters. The two main parameters include a file list to duplicate, and one to many schedules. A file set can be a file name, a wildcarded file name, or a file exclude list. The schedules are events which can be as frequent as you want, such as every day, every hour, or every ten minutes.
Syncfile options control the following:
The files to duplicate
The schedule for determining when files should be duplicated
The method for duplication (FUP, FTP, etc.)
Whether files should be always duplicated, or only when modified
A Syncfile parameter file can contain multiple schedules and file sets. In addition, you can create multiple Syncfile processes to support duplication for different applications or other requirements.
Syncfile processes are persistent. If a Syncfile process goes down unexpectedly, Manager automatically restarts it.
Parent topic: Using Oracle GoldenGate Utilities
See GGSCI Commands for details about the GGSCI Syncfile commands and the Syncfile parameters.
Parent topic: Using Syncfile