This chapter discusses troubleshooting ETL and describes the error logs in Oracle Warehouse Builder. It also discusses error handling techniques for ETL such as DML error logging.
This chapter contains the following topics:
While working with Oracle Warehouse Builder, the designers must access log files and check on different types of errors. This section outlines all the different types of error messages that are logged by Oracle Warehouse Builder and how to access them.
Oracle Warehouse Builder logs the following types of errors when you perform different operations:
This section shows you how to retrieve error logs after performing different operations in Oracle Warehouse Builder.
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.
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.
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.
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.
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.
When you encounter errors while deploying mappings, use the line number provided in the error message to determine where the error occurred. The generated code contains comments for each operator in the mapping. It enables you to determine which operator in the mapping caused the error.
The following example displays the code generated, in set-based mode, for a Filter operator. Notice that the comments enclosed between /* and */ list the operator for which a particular part of the statement is run.
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 */ ) ) ;
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:
If your Name and Address server is enabled in:
then it produces the log file
NASvrTrace.log in the same directory.
Error logging enables the processing of DML statements to continue despite errors during the statement execution. The details of the error such as the error code, and the associated error message, are stored in an error table. After the DML operation completes, you can check the error table to correct rows with errors. DML error logging is supported for SQL statements such as
MERGE, and multitable insert. It is useful in long-running, bulk DML statements.
Oracle Warehouse Builder provides error logging for the following data objects used in set-based PL/SQL mappings: tables, views, materialized views, dimensions, and cubes. DML error logging is supported only for target schemas created on Oracle Database 10g Release 2 or later versions.
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.
Oracle error number
Oracle error message text
ROWID of the row in the error (for update and delete)
Type of operation: insert (I), update (U), delete (D)
Step or detail audit ID from the run time audit data. This is the
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.
The execution of mappings that contain data objects for which DML error logging is enabled fails if any of the following conditions occur:
The number of errors generated exceeds the specified maximum number of errors for the mapping.
The default set for this value is 50. You can modify this value by setting the Maximum number of errors configuration parameter of the mapping. In the Projects Navigator, right-click the mapping and select Configure. In the Maximum number of errors configuration parameter, specify the number of errors that can generated before the mapping execution is terminated.
Errors occur due to functionality that is not supported.
You can truncate the DML error table and delete error details generated during a previous load. This helps in housekeeping of the error tables. To truncate an error table before the map is run, select the Truncate Error Table property of the operator bound to the data object that has DML error logging enabled.
The properties Roll up Errors and Select only errors from this property are not used for DML error logging.
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.
This section contains troubleshooting tips for errors that you may encounter while performing ETL.
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.
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:
jrt.log file located in
/owb/bin/admin contains output from the Agent process and audit setup errors.
The Code Template mapping execution logs are stored in the
/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.
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/jrt/applications and then start the Control Center Agent.
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.
Export the certificate used for the SSL channel from the OC4J server side. This is in the
/bin/keytool -export -storepass welcome -file server.cer -keystore
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.
Copy the exported server.cer from step 1 to the OC4J server side
Import the certificate to the java trusted certification store at the OC4J server side.
$JAVA_HOME/bin/keytool -import -v -trustcacerts -file
cacerts is the file used to store the trusted certificates,
server.cer is the file copied from step 2.
You are prompted for the
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
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.
Note:You can use a local Control Center to deploy data objects such as tables, view, dimensions, and so on to a remote target. This is because Oracle Warehouse Builder does not maintain auditing information for these objects.
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.
Drop the mappings that have been deployed to the target schema using the original Control Center.
Unregister the location from the original Control Center.
Delete the synonyms that provide the association between the target schema and its control center from the target schema.
Register the location using the new Control Center.
Deploy mappings using the new Control Center.
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;