15 Troubleshooting and Error Handling for ETL Designs

Troubleshooting Validation Errors

In Oracle Warehouse Builder, you can validate all objects by selecting the objects from the Projects Navigator and then selecting Validate from the File menu. After the validation is complete, the validation messages are displayed in the Log window.

Figure 15-1 displays the validation messages in a new tab of the Message Log window.

Figure 15-1 Validation Error Messages

Description of "Figure 15-1 Validation Error Messages"

You can also validate mappings from the Mapping Editor by selecting Mapping, then Validate. The validation messages and errors are displayed in the Validation Results window.

In the validation results, expand the node displaying the object name and then the Validation node. The validation errors, if any are displayed. Double-click a validation message to display the detailed error message in a message editor window.

Oracle Warehouse Builder saves the last validation messages for each previously validated object. You can access these messages at any time by selecting the object from the console tree in the Projects Navigator, selecting View from the menu bar, and then clicking Validation Messages. The messages are displayed in the Validation Results window.

Troubleshooting Generation Errors

After you generate scripts for Oracle Warehouse Builder objects, the Log window displays the generation results and errors. Double-click an error message in the Log window to display a message editor that enables you to save the errors to your local system.

Figure 15-2 displays the Generation Results window.

Figure 15-2 Generation Results Window

Description of "Figure 15-2 Generation Results Window"

Troubleshooting Deployment and Execution Errors

You can store execution or deployment error and warning message logs on your local system by specifying a location for them. In the Projects Navigator, select the project. Then from the Tools menu, select Preferences. In the object tree on the left of the Preferences dialog box, expand the Oracle Warehouse Builder node, and click the Logging option. Use the properties listed on the right to set the log file path, file name, and maximum file size. You can also select the types of logs to store.

You can view this log of deployment and error messages from Oracle Warehouse Builder console by selecting View from the menu bar, and then Log. This Message Log panel is read-only.

Runtime Audit Browser

If an error occurs while transforming or loading data, the audit routines report the errors into the run time tables. You can easily access these error reports using the Run time Audit Browser. The Run time Audit Browser provides detailed information about past deployments and executions. These reports are generated from data stored in the run time repositories. Click the Execution tab in the Execution reports to view error messages and audit details.

INSERT INTO "FLTR_TGT"
      ("CHANNEL_ID", "CHANNEL_DESC")
      (SELECT
/*+ NO_MERGE */
/* CHANNELS.INOUTGRP1, FILTER.INOUTGRP1 */
  "CHANNELS"."CHANNEL_ID" "CHANNEL_ID",
  "CHANNELS"."CHANNEL_DESC" "CHANNEL_DESC"
FROM
  "SH"."CHANNELS"@"ORA11@SH_SRC_LOCATION"  "CHANNELS"
  WHERE 
  ( "CHANNELS"."CHANNEL_ID"  < 5/* OPERATOR FILTER: FILTER CONDITION */ ) 
      )
    ;

Troubleshooting Name and Address Server Errors

If you are using the Name and Address cleansing service provided by Oracle Warehouse Builder, you may encounter related errors.

Name and Address server start and execution errors can be located at:

OWB_HOME/owb/bin/admin/NASvr.log

If your Name and Address server is enabled in:

OWB_HOME/owb/bin/admin/NameAddr.properties:TraceLevel=1,

then it produces the log file NASvrTrace.log in the same directory.

About DML Error Tables

DML error tables store details of errors encountered while performing DML operations using a mapping. You can define error tables for tables, views, and materialized views only.

Use the DML Error Table Name property to log DML errors for a particular data object. In the mapping that uses the data object as a target, set the DML Error Table Name property of the operator that is bound to the target object to the name of the DML error table that stores DML errors.

You can create your own tables to store DML errors or enable Oracle Warehouse Builder to generate the DML error table. While deploying a mapping in which the DML Error Table Name property is set for target operators, if a table with the name specified by the DML Error Table property does not exist in the target schema, it is created.

When DML error tables are created along with the mapping, dropping the mapping causes the DML error tables to be dropped, too.

In addition to the source target object columns, DML error tables contain the columns listed in Table 15-1. If you use your own tables to log DML errors, then ensure that your table contains these columns.

Enabling DML Error Logging

DML error logging is generated for set-based PL/SQL mappings if the following conditions are satisfied:

• In the mapping that loads the table, view, materialized view, dimension, or cube, the DML Error Table Name property is set for the operator representing the target object.

• The PL/SQL Generation Mode configuration parameter of the module that contains the mapping is set to Oracle 10g, Oracle Database 11g, or Default.

  If the value is set to Default, ensure that location associated with this module has the Version property set to 10.2, 11.1, or 11.2.

DML Error Logging Limitations

• DML error logging is not supported for nonscalar data types.

• Each DML statement has specific limitations, which are listed in the documentation related to that statement.

  See Also:

  Oracle Database SQL Language Reference for limitations on DML error logging for each DML statement.

• If you have a DML error table defined for a data object, you cannot upgrade the data object using the Upgrade option in the Control Center Manager.

• Depending on your error logging needs, you can configure the Table operator in a mapping to use the APPEND or NOAPPEND hint. For example, direct-path INSERT does not support error logging for unique key violations. To log unique key violations, use the NOAPPEND hint.

ORA-04063 While Running Hybrid Maps

While executing a hybrid mapping that contains a PL/SQL Oracle Target CT, you may encounter the following error:

ORA-04063: package body "DEMO.ORACLE_SQL_POWER_MTI" has errors

This indicates that there are compilation errors in ORACLE_SQL_POWER_MTI, the PL/SQL package generated to implement the mapping.

To determine the cause of this error, start SQL*Plus, connect as the target user, and run the following commands:

ALTER PACKAGE ORACLE_SQL_POWER_MTI COMPILE BODY;
SHOW ERRORS;

For example, a table not found error may occur if permissions on source tables or views are not granted to the target user. Resolve any errors, recompile the package, and then run the hybrid mapping.

Agent Log Files

The agent log files enable you to debug deployment and execution errors in Code Template mappings. On Windows, the agent logs are displayed in the Design Center console.

The following are the agent logs on UNIX:

• The jrt.log file located in OWB_HOME/owb/bin/admin contains output from the Agent process and audit setup errors.

• The Code Template mapping execution logs are stored in the OWB_HOME/owb/jrt/log/owb folder. Each job execution is represented by a separate directory that contains XML log files with the audit trail of the job execution.

Error Starting the Control Center Agent (CCA)

While starting the Control Center Agent, you may encounter the following error:

Error initializing server: Application: system is in failed state as initialization failed.

To resolve this error, delete all the subdirectories in the folder OWB_HOME/owb/jrt/applications and then start the Control Center Agent.

Error Executing Web Services from the Secure Web Site

Sometimes you may encounter the following error when you run a Web service from a secure Web site:

SSL Error: unable to find valid certification path to requested target.

Use the following steps to overcome this error.

1. Export the certificate used for the SSL channel from the OC4J server side. This is in the $J2EE_HOME/config directory.

   $JAVA_HOME/bin/keytool -export -storepass welcome -file server.cer -keystore OWB_HOME.owb/jrt/config/serverkeystore.jks

   server.cer is the file to which the certificate is exported, serverkeystore.jks is the key store used in the OC4J server embedded in Oracle Warehouse Builder. If you use an OC4J instance other than the one embedded in Oracle Warehouse Builder, serverkeystore.jks is the key store file you created when you setup the SSL with OC4J.

2. Copy the exported server.cer from step 1 to the OC4J server side $JAVA_HOME/jre/lib/security directory.

3. Import the certificate to the java trusted certification store at the OC4J server side.

   $JAVA_HOME/bin/keytool -import -v -trustcacerts -file

   $JAVA_HOME/jre/lib/security/server.cer -keystore

   $JAVA_HOME/jre/lib/security/cacerts

   where cacerts is the file used to store the trusted certificates, server.cer is the file copied from step 2.

   You are prompted for the cacerts password.

REP-01012 While Deploying Mappings to a Target Schema

When you deploy mappings to a target schema, you may encounter the following error:

REP-01012: Cannot deploy PL/SQL maps to the target schema because it is not owned by the Control Center

Cause 

This error occurs when you attempt to deploy mappings to a target schema into which objects were previously deployed using a different Control Center.

A target schema can be associated with only one Control Center. Audit data regarding deployments to this target schema is written to audit tables in the Control Center repository. The Control Center creates various objects (primarily synonyms) in the target schema that provide information about the Control Center to which audit data should be written. When you attempt to use a different Control Center to deploy a mapping to the same target schema, you encounter the REP-01012 error.

However, Oracle Warehouse Builder enables you to deploy mappings to a target schema.

Typically, when you use a Control Center for deployments, you deploy mappings to a target schema by using the Control Center installed on the database containing the target schema. You can deploy mappings to a target schema by using a Control Center installed on a different database than the one that contains the target schema. In the Locations Navigator, create a Control Center that points to the Control Center installed on the remote host containing the target schema. Thus you can deploy mappings to the remote control center.

Solution 

1. Drop the mappings that have been deployed to the target schema using the original Control Center.

2. Unregister the location from the original Control Center.

3. Delete the synonyms that provide the association between the target schema and its control center from the target schema.

4. Register the location using the new Control Center.

5. Deploy mappings using the new Control Center.

Unable to Delete a Location

Before you delete a location, do the following:

• Reconfigure any modules that use the location to use a different location

  Edit the modules and remove the location from the set of possible data locations for the module.

• Unregister the location.

Log in as the OWBSYS user and run the following query to determine if the location is still associated with a Control Center.

SELECT s.name owner, r.name referenced, c.name connector, 
       c.REGISTERED, c.STRONGTYPENAME
FROM cmplogicalconnector_v c, cmplocation_v s, cmplocation_v r
WHERE c.owninglocation = s.elementid AND c.referencedlocation = r.elementid;