Working with Mappings Groups

4 Working with Mappings Groups

Learn how to use the local and global mappings groups to create, configure, copy, and manage your replication options by using Oracle GoldenGate Studio.

Topics:

4.1 Understanding Mapping Groups

In Oracle GoldenGate Studio, the term mapping group is used to explain a collection of source and target table mappings and its associated replication logic such as filtering and conflict detection rules. First, you must drag and drop or add the tables by using the keyboard shortcuts from the Resources Navigator to the Mapping Editor. Then you can automatically generate between Source and Target table mapping associations by using Automap option or by drag-and-drop operation or by using keyboard shortcuts between Source to Target and between Target to Source. Additionally, you can use wildcard characters (*) in the mappings at the schema and table level. If you drag and drop the schema name it automatically use a wildcard, indicating all tables. For more on wildcard logic see the Reference for Oracle GoldenGate

For Oracle multitenant support you must edit the schema name manually to include the pluggable database name, only the metadata from the database connections are used. This means mapping groups are logical constructs, which can be assigned to multiple replication paths. Mapping groups local to a solution can be associated with any replication path in that solution, while global mapping groups located in the global resource library, can be used for any replication path for any solution across all projects. When a mapping group is modified that change is reflected in all replication paths that references that mapping group. More than one mapping group can be assigned to a replication path.

For example, if you have 100 stores with the same application schema reporting back to a central data warehouse, you only have to define that mapping group once and you can apply it to all 100 replication paths. Any changes to that mapping group only needs to be made once and it is reflected in all 100 replication paths.

4.1.1 Schema and Table Mapping

Schemas are not directly mapped to other schemas. Tables in the schemas are mapped to other tables. To map all tables in a schema you can either highlight of the tables in the global resource library and drag and drop them into the mapping editor or you can drag and drop the schema name, which then uses a wildcard (*) for all tables. If replicating DDL and want to capture new tables then, always use a wildcard. You can use a wildcard with schema names. However, when you use a wildcard for schema names, some system schemas are automatically excluded. To see schema names that are implicitly excluded when you use wildcards, see https://www.oracle.com/pls/topic/lookup?ctx=en/middleware/goldengate/studio/12.2.1.4/ggsug&id=GWUAD-GUID-46CAAFC3-DCA5-4A20-9C60-8939EBD87E83 in Administering Oracle GoldenGate. When you use a wildcard for the schema name you can override the implicitly excluded schemas by explicitly listing the schema names.

To map tables or wildcards you can click the Automap button and any unmapped target table is mapped to a source table if the table names are the same regardless if they are in different schemas. Alternatively, in the mapping editor you can drag and drop source and target table names on each other to create the association.

Once tables are mapped you see the Source table name in the Target column Mapped From. Only the Source table names are listed in this column but a tool tip provides the source schema name. Double-click the Target table name or Mapped From table name takes you to the column mapping editor.

To use a pluggable database, first specify pluggable database name and then schema name in the following format <PDB>.<SCHEMA>. To use the replicat process from one pluggable database to another, you have to edit the sourcecatalog parameter in the Properties Inspector for the extract and replicat process. To use the replicat process for multiple pluggable databases, you have to edit the mapping editor schema to add the plugabble database name for the replication process.

Oracle Data Pump replication are usually done through databases, tablespaces, schema and table types. Currently, only table type is supported. It requires both, the schema name from a source and a target as well as the table name.

In the schema and table mapping editor you can define properties and logic for the entire mapping, source tables, and target tables. The following figure and the table shows the properties that can be edited based on where you click in the mapping editor.

Note:

Wildcard mapping are not supported with Oracle Data Pump. No errors are shown during deployment but the data on target side are not loaded.

Figure 4-1 Schema and Table Mapping

ID	UI Element	Properties
1	Map Breadcrumbs	Name and description, DDL replication, global column matching, DML conversions, and global replication error responses.
2	Source schema name	Database name, schema name, and exclude schema from source capture. The exclude schema is used when you have used a wildcard for all schemas but want to explicitly exclude certain schemas that would otherwise be included with the wildcard.
3	Source table name	Table name, exclude table from source capture, filter, Where clause, custom SQL execution, event actions, and additional custom properties. The table name cannot exceed 128 characters.
4	Target schema name	Target schema name and exclude schema from target apply.
5	Target table name and Mapped From	Target schema name and exclude table from target apply, exception handling, filter, DML conversion, Where clause, table specific replication error responses, custom SQL execution, coordinated apply thread (if applicable), coordinated apply thread range (if applicable), event actions, more settings, and additional custom properties. The table name cannot exceed 128 characters.

When you exclude table on source side a tableexclude parameter is written on source side but corresponding map statements are not be written to target side. Similarly, when you exclude table on target side a mapexclude parameter is written on target side , but corresponding table statements are not written to source side.

Note:

When you have more than one table mapped you need to drill into column mapping and select the table name to edit all the properties except exclude property. This allows you to apply different logic to different tables.

4.1.2 Column Mapping

You can navigate to the column mapping from the table mapping editor. Double-click the Target table name or the Mapped From column, or right-click anywhere on the target row that contains the table name and chose Edit Column Mapping from the context menu.

The Oracle GoldenGate runtime engine automatically maps matching column names so, column mapping is not required. Column mapping works in the same way as table mapping and you can use Automap and drag-and-drop between Source and Target.

In the column mapping editor, you can define the properties and logic for the Source and Target tables and columns. The following figure and the table show the properties that can be edited based on where you click in the mapping editor. It also shows how to select individual table mappings when more than one table is mapped to a target table and how to navigate back to the schema and table mapping editor.

Note:

Selecting the table name in the column mapping editor provides the same options as clicking the table name in the schema and table mapping editor when there is only one source table mapped to the target table.

Figure 4-2 Column Mapping

ID	UI Elements	Properties
1	Map Breadcrumbs showing mapping group name	Select here to return to the schema and table mapping editor.
2	Map Breadcrumbs showing table mapping name	If there is more than one source table mapped to this target table, selecting here provides a drop-down list of those other mappings and you can navigate to them.
3	Source table name	Table name, exclude table from source capture, filter, Where clause, custom SQL execution, event actions, and additional custom properties.
4	Source column name	Column name, exclude column from source capture, force column to be used as (part of) the primary key.
5	Target table name	Target table name, exclude table mapping from target apply, force specific columns to be used as the key, exception handling, conflict detection, conflict resolution, filter, DML conversion, Where clause, table specific replication error responses, custom SQL execution, coordinated apply thread (if applicable), coordinated apply thread range (if applicable), event actions, more settings, and additional custom properties.
6	Target column name and Mapped From	Column name, mapped from (which can contain source column names or custom logic), force column to be used as (part of) the primary key, enable this column for update conflict detection, and enable this column for delete conflict detection.

Note:

Primary key column overrides, conflict detection, and conflict resolution are only present in the column mapping editor.

4.1.3 Automap

The Automap feature automatically maps similarly named tables. Automapping assignments are created from the source to target and not the reverse. Automapping is not possible if the table structures are identical and the table names are different.

The mapping window looks as follows:

Figure 4-3 Automap

4.2 Creating a Mapping Group

To create a new local or global mapping group:

To create a mapping group that is local to the solution, select the Mapping Groups under that solution and right-click to select New. To create a global mapping group, right-click and select Global Mapping Group in the Resources Navigator. If the Resources Navigator has no global mapping groups then click the plus icon + in the Resources Navigator, select Global Resource Library, and then select Global Mapping Group.

The Create New Mapping Group or New Global Mapping Group window opens.
Provide a name and optional description for the Mapping Group, and click OK.
If the mapping editor window does not open automatically, double-click the new mapping group in the Projects Navigator or Resources Navigator.
Add tables from the Global Resource Library by dragging and dropping them from the library into the source and target panes of the mapping editor.

To select a list of tables you can use Shift-click on the first and last table in the set, use Control-click to select multiple individual tables, or use the + button in the source and target mapping editor panes to manually add schema and table names. In the column mapping editor the equivalent + button allows you to add columns manually.
Drag and drop the table or schema names from either the Source or Target pane to the other pane to create a source or target table mapping. If you drag the tables to a white area it does an exact 1:1 mapping. If you drag a table name from one pane to the other and drop it directly on the other table name, it creates an explicit mapping.

For example, if table EMP1 in Source is to be mapped with EMP_BACK1 in Target, then drag EMP1 from Source and drop it on EMP_BACK1 at Target. Typically, the Mapped From field shows the Source table that is mapped with the target table. In this case the Mapped From field has EMP1 value against the target table EMP_BACK1.

You can also use the keyboard to perform the same action. When a source or target schema does not have a reference to any specific Resources Navigator schema and the name matches a particular database name in the Resources Navigator, a confirmation dialog is displayed to associate the resource. You can select Yes to complete the mapping.

If you select a table as target in the Mapping Editor, which is not mapped, the Properties Inspector displays a Mapped From text box. You can enter the source table name which is validated against a valid source table references and can be mapped to target table if the source table is valid.
You can also use the AutoMap button to automatically create mappings for table names that are the same between Source and Target. Source tables can be mapped to more than one Target table and more than one Source table can be mapped to a single Target table.

4.3 Assigning Mapping Groups to a Replication Path

Mapping group is assigned in the solution editor. To assign a local or global mapping group to a replication path, drag the mapping group from the Projects Navigator or Global Resource Library and drop on the replication path or data server in the solution editor. If dropped onto a data server, the mapping group is automatically associated with every replication path connecting that data server. If dropped on the replication path arrow, the association is only for that path. You can drag the same mapping group and drop on multiple paths and data servers. These associations are by reference and not copies. Any change to the mapping group is automatically reflected in every replication patch associated with that mapping group.

To remove a mapping group from a replication path, select the replication path in the solution editor and in the Properties Inspector you see a list of all replication path mapping associations. Select the desired mapping group name and click the red X icon to delete. A confirmation dialog appears.

In the Properties Inspector of the replication path, there is a green + icon, which provides an alternative way to add an association between a mapping group and replication path. When you select the + icon, through a dialog box, you can add the association for local and global mappings to this replication path.

4.4 Copying And Sharing Mapping Groups

When you right-click a local solution mapping group you can see options to copy it to the Global Resource Library, make a copy (duplicate) of the selection within the same solution. You can right-click the global mapping group and export to an XML file that can be imported by other Oracle GoldenGate users outside of your shared environment. The export map dialog supports character set and encryption settings.

You can import both local and global exported mapping groups by using the right-click option and select the appropriate Mapping Groups node in the Projects Navigator and selecting Import.

The different Import Types are:

Duplication: This mode creates a new object (with a new internal ID).
Synonym Mode INSERT: This mode tries to insert the same object (with the same internal ID).
Synonym Mode UPDATE: This mode tries to modify the same object (with the same internal ID).
Synonym Mode INSERT_UPDATE: If no object exists in the target Repository with an identical ID, this import mode will create a new object with the content of the export file. Already existing objects (with an identical ID) will be updated.