Using Diagnosis and Debugging Techniques

1. Using Diagnosis and Debugging Techniques

2. Objectives • After completing this lesson, you should be able to do the following: • Collect information needed by Oracle Support • Use SQL scripts to troubleshoot problems • Activate logging and tracing • Test the availability of the OWB Control Center Service • Troubleshoot the Control Center Agents • Use public views and run-time utilities • Find online Oracle resources for diagnosing and debugging OWB problems

3. Mandatory Reading • Oracle Warehouse Builder Installation and Administration Guide • Appendix A entitled “Troubleshooting the Installation and Setup.” • This chapter should be mandatory reading for anyone in your workplace who is involved in supporting Warehouse Builder.

4. Collecting Information Before Contacting Oracle Support • To help provide information to assist Oracle Support in resolving your issue: • Search My Oracle Support (formerly known as Metalink) for “Data Collection Script for Oracle Warehouse Builder” • Download and run the attached script and provide the output when logging a service request. • Additional information about your repository can be provided: • Search for “Health Check of the Oracle Warehouse Builder” • Download and run the script and upload the output with the service request.

5. Collecting Information Before Contacting Oracle Support • If an OWB repository installation fails, determine if the database was created manually or by using the Database Configuration Assistant. • 99% of repository installation failures occur in manually created databases. • XDB must be in the database, so that the XML toolkit installs to the XDB schema instead of the SYS schema. • The operating system owner name for the Oracle database installation must be the same as the operating system owner of the OWB installation. • If it is an execution issue, upload the run-time log on the server’s \owbhome\owb\log directory.

6. Sequence Used by Oracle Support Representatives to Process Calls 1. Provide a clear definition of the environment. 2. Does the problem involve: • The client tool during design or deployment? • The server during execution? 3. If the problem involves the client tool, you willbe asked to perform the following tasks: a. Open the OWB client in debug mode. Replicate the problem. b. Close OWB. c. Collect the log files and upload them with your service request. 4. If the problem involves server execution, you may be asked to provide the run-time and alert logs and the trace files for the Oracle database.

7. Timing Your Service Request • Try to avoid opening a service request at the end of your workday. • Try to work with support representatives in your time zone. • Be prepared to remain available for at least one hour for the support representative to contact you and start the process.

8. Activating Debugging and Logging for Full Java Debug Trace • Warehouse Builder is written in Java. • In Windows, Java returns some error messages: • To the command prompt (in the window in which you are operating) • To the application (you can configure the application to make messages appear) • On Linux machines, most error messages just flash rapidly across the screen. • Piping messages to files is the most efficientway to capture the error information.

9. Activating Debugging and Tracing • You can enable debugging and tracing in the OWB client to help determine the cause of a problem. 1. Modify the following parameters in the file: [ORACLE_HOME]/owb/bin/admin/DebugUtility.properties • Set Debug=true. Set LogDebug=true. 2. To activate tracing, edit the file: [ORACLE_HOME]/owb/bin/admin/Preference.properties • Add a line: TRACE=MEMORY. • By default, the log files are created in the [ORACLE_HOME]/owb/bin/admin directory: • Log.<index>.log • OWBTrace.log<xxx>

10. Activating Debugging and Logging DebugUtility.properties: Set Debug and LogDebug to true.

11. Activating Tracing Preference.properties: Add a line: TRACE=MEMORY The trace log file is created in: [ORACLE_HOME]/ owb/bin/admin/OWBTrace.log<xxx>

12. Using the service_doctor.sql Script

13. Using SQL*Plus Scripts to Test Availability of the Control Center Service Show service > current availability Stop service > not available Start service > available

14. Killing a Job If a Mapping Hangs • If a mapping hangs, you need to: 1. Kill the job. 2. Redeploy or reexecute. • Use three scripts in the [ORACLE_HOME]/owb/rtp/sql/ folder, in the following order, to kill the job: 1. list_requests.sql • To obtain information such as audit_id to deactivate 2. Abort_exec_request.sql • To abort execution 3. Deactivate_execution.sql at SQL*Plus command line: • Execute wb_rt_execution.deactivate_execution([audit_id], 1); • Use this only if abort_exec_request.sql does not work.

15. Troubleshooting Heterogeneous Sources and Targets • First test that Heterogeneous Services connections work outside of Warehouse Builder. • If Heterogeneous Services enables connection from SQL*Plus, you can reference the heterogeneous source or target as you would reference any Oracle source or target. • My Oracle Support has notes listing the steps for setting up Heterogeneous Services on Windows, Linux, and other platforms. WarehouseBuilder IBM DB2, Microsoft SQL Server, Sybase, Informix, Mainframes, and so on (via Oracle Connectivity options)

16. Troubleshooting and Diagnosing Errors in Control Center Agent (CCA) 1. Inspect the log files for CCA • ODL Job Log Files • Control Center Agent Runtime Log File 2. Modify the Control Center Agent settings • Modify the jrt.xml file • Modify via JRTAdministratorMBean • Use the cca_admin utility

17. Encouraging Designers to Use the Mapping Debugger Debugger toolbar Debug Start button You use the debug mode to step through this simple mapping with test data.

18. Run-Time Views and Utilities • Warehouse Builder has many useful utilities and views to manage your run-time environment. • Use the views to tailor reports for administrators. • Valid/Invalid report • Throughput report • Use the views to get a general status of: • Deployed objects • Object owners • Object locations • Use the utilities to do common tasks. • Purging audit information • Setting the repository password

19. Public Run-Time Views • Select view_name from user_views where view_name like 'ALL_RT%';

20. Public Run-Time Utilities • Non-customized reporting • print_exec_details • print_running_exec_details • list_requests • rtrepos_report • Deleting and purging • delete_warehouse_object • purge_audit_template • Resetting deployment or execution jobs • abort_exec_request • deactivate_deployment • deactivate_execution • expedite_exec_request

21. Public Run-Time Utilities • Control Center Service utilities (already discussed in previous slides) • start_service, stop_service, show_service • service_doctor • Run-time platform utilities • reset_repository • set_platform_property, display_platform_property (set run-time property such as max_number_logfiles, logfile_max_size, recovery and so on.) • Password and privileges utilities • set_repository_password • grant_upgrade_privileges

22. Online Warehouse Builder Resources • OWB on Oracle Technology Network (OTN): • http://www.oracle.com/technology/products/warehouse/index.html • Warehouse Builder product discussion forum: • http://forums.oracle.com/forums/forum.jspa?forumID=57 • Warehouse Builder Utility Exchange: • http://www.oracle.com/technology/products/warehouse/htdocs/OWBexchange.html • Advice for finding OWB patches on My Oracle Support: • http://www.oracle.com/technology/products/warehouse/htdocs/OTN_Patches.htm • Sample code: • http://www.oracle.com/technology/sample_code/products/warehouse/index.html

23. Summary • In this lesson, you should have learned how to: • Collect information needed by Oracle Support • Use SQL scripts to troubleshoot problems • Activate logging and tracing • Test the availability of the OWB Control Center Service • Troubleshoot the Control Center Agents • Use public views and run-time utilities • Find online Oracle resources for diagnosing and debugging OWB problems