This section describes some troubleshooting tasks you must perform during installation and setup of Oracle Warehouse Builder.
This section includes the following topics:
Take the following steps to troubleshoot errors in Oracle Warehouse Builder:
Review this section for a possible solution to the problem.
If Oracle Warehouse Builder displays an error message during the installation process, then refer to "Error Messages Related to Installation". If you did not note the error number, you can review the "Log Files for Installation Errors".
In the absence of an error message, refer to "Troubleshooting Other Installation Problems".
Check for additional information about the problem by "Inspecting Log Files in Oracle Warehouse Builder".
If the problem remains unresolved, search for a possible solution at My Oracle Support at https://support.oracle.com/.
From the Repository Assistant, use the Check for Updates Wizard to see if there is a patch or information that addresses the problem. See "About the Check for Updates Wizard for Oracle Warehouse Builder".
Review the Oracle Warehouse Builder Release Notes for installation notes or known issues.
If you are unable to resolve the problem in the previous steps, contact Oracle Support.
Oracle Support may ask you to complete the steps in "Using Oracle Warehouse Builder Client Logging to Control Logging and Trace Problems".
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:
Log Files for Installation Errors
When you run Oracle Universal Installer to install Oracle Warehouse Builder, the installation error logs are automatically stored in:
C:\ProgramFiles\Oracle\Inventory\logs\installActionstimestamp.log
When you run the Oracle Warehouse Builder Repository Assistant, the workspace installation error logs are stored in:
OWB_HOME\UnifiedRepos\log_timestamp.log
See "Error Messages Related to Installation" for suggested actions for commonly encountered errors during installation.
Log Files for Metadata Import and Export Errors
Metadata Import: When you import a project or specific objects into your workspace using the Metadata Import Utility, Oracle Warehouse Builder records details of the import process in a log file. You can specify the name and location of this log file from the Metadata Import dialog box.
Metadata Export: When you export an Oracle Warehouse Builder project or specific objects using the Metadata Export Utility, Oracle Warehouse Builder records the details of the export in a log file. You can specify the name and location of this log file from the Metadata Export dialog box.
Log File for Validation Errors
In Oracle Warehouse Builder, you can validate all objects by selecting the objects from the console tree and then selecting Validate from the Object menu. After the validation is complete, the validation messages are displayed in the Validation Results 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.
On the Validation tab of the Validation Results window, double-click an object name in the Object column to display the editor for that object. You can fix errors in the editor. Double-click a message in the Message column to display the detailed error message in a message editor window. To save the message to your local system, select Code in the menu bar, then select Save as File.
Oracle Warehouse Builder saves the last validation messages for each previously validated objects. You can access these messages at any time by selecting the object from the console tree in the Project Navigator, select View from the menu bar, and then click Validation Messages. The messages are displayed in the Validation Results window.
Log File for Generation Errors
After you generate scripts for Oracle Warehouse Builder objects, the Generation Results window displays the generation results and errors. Double-click an error under the Messages column on the Validation tab to display a message editor that enables you to save the errors to your local system.
After you generate scripts for Oracle Warehouse Builder objects, the Generation Results window displays the generation results and errors. Double-click an error under the Messages column on the Validation tab to display a message editor that enables you to save the errors to your local system.
Log Files for 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 Project Navigator, select the project. Then from the Tools menu, select Preferences. In the Preferences dialog box, click the Logging option in the object tree to the left. In the list box on the right, you can set the log file path, file name and maximum file size. You can also select the types of logs you want to store.
You can view this log of deployment and error messages from the Oracle Warehouse Builder console by selecting View from the menu bar, and then Messages Log. This Message Log dialog box is read-only.
Errors related to the Control Center Service are stored at the following path:
OWB_HOME\log\Repository_Name\log.xx on Oracle Database server.
Errors related to transforming or loading data are stored in the Control Center audit tables. You can access these error reports using the Repository Browser. The Browser provides detailed information about past deployments and executions. Click the Execution tab in the Execution reports to view error messages and audit details.
Log File for Name and Address Server Errors
If you are using the Name and Address cleansing service provided by Oracle Warehouse Builder, you can encounter related errors.
Name and address server start and execution errors can be located at:
OWB_HOME\owb\bin\admin\NASver.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.
This section includes the following topics:
"No fonts were found in 'drive:\Program Files\ Qarbon\viewlet Builder3jre\lib\fonts'"
"RTC-5301: The Control Center Service is not currently available."
"Regional Name and Address Data Libraries Are Not Available."
"Lineage and impact analysis reports: Extensive tablespace requirements for materialized views."
"INS0009: Unable to connect to the database. Verify the connect information."
"ORA-12514: TNS: listener could not resolve SERVICE_NAME given in connect descriptor."
"PL/SQL: ORA-04052: Error occurred when looking up remote object"
"ORA-04088: error during execution of trigger 'DVSYS.DV_BEFORE_DDL_TRG'"
"DPF-0029: Source Table_Name must have less than 165 attributes"
OWBSYS is not granted access to OWB_HOME/owb/bin/admin/rtrepos.properties: Please run UnifiedRepos/reset_owbcc_home.sql, specifiying the path of Oracle home from which the Control Center Service is being run. reset_owbcc_home.sql and prompted for the OWB_HOME, you typed an invalid path for OWB_HOME.On all platforms, including both Windows and UNIX, the path you enter must use forward slashes, and is case-sensitive. The case of the path entered here must match the case of the path for the Oracle Warehouse Builder home as known by the operating system.
On UNIX, the correct path to enter is the path for the OWB_HOME directory. On Windows, to determine the correct path for the OWB_HOME directory, and examine the path displayed as part of the default Windows command prompt.
SYSDBA privileges. SYS user has SYSDBA credentials and REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE. You can verify the credentials by issuing the following connect statement:
SQL> CONNECT sys@tns_name_of_db AS SYSDBA;
Enter password: sys_password
If your database is configured with REMOTE_LOGIN_PASSWORDFILE=NONE, then the statement fails.
Reconfigure your database with
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE and create a password file if none exists.
If the preceding is not an option, reconfigure your database with
O7_DICTIONARY_ACCESSIBILITY=TRUE.
start_service.sql.
If it is not possible to run the service on the server host, then start the Control Center Service on the local computer using the script local_service_login.sh or local_service_login.bat as appropriate. Use this script as follows:
local_service_login.sh [-startup | -closedown] OWB_HOME
In this mode, the Control Center Service runs on the local computer and is available only when that computer is available and can connect to the Control Center.
Use the script show_service.sql to determine the status of the service.
NAMESPACESERVICEIMPL may be invalid. This occurs after a database export or import from the Oracle Warehouse Builder repository schema if the repository owner has no SELECT privilege on SYS.V_$SESSION. You can diagnose the cause as follows:
In SQL*Plus, connect to the Oracle Warehouse Builder repository schema.
Enter the following command at the SQL prompt:
ALTER PACKAGE NAMESPACESERVICEIMPL compile body;
If Warning: Package body altered with compilation errors appears, enter the following command at the SQL prompt:
show errors;
The following errors mean that the Oracle Warehouse Builder repository owner has no SELECT privilege on SYS.V_$SESSION.
PL/SQL: SQL statement ignored PLS-00201: Identifier 'SYS.V_$SESSION' must be declared
In SQL*Plus, connect as the SYS user.
At the SQL prompt, enter the following command:
grant SELECT on V_$SESSION to Oracle Warehouse Builder_Repository_Owner;
Connect to the Repository_Owner.
Enter the following command at the SQL prompt:
alter package NAMESPACESERVICEIMPL compile;
SYS user has SYSDBA credentials. You can verify this from SQL*Plus by issuing the following connect statement:
connect sys/sys_password@TNS_NAME_OF_DB as sysdba;
In a standard database installation, the preceding connect statement works because REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE and the default password file is created by the installation process.
If your database is configured with
REMOTE_LOGIN_PASSWORDFILE=NONE, then the following statement fails:
connect sys/sys_password@TNS_NAME_OF_DB as sysdba;
In this case, you have two options.
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE and create a password file if none exists.
O7_DICTIONARY_ACCESSIBILITY=TRUE. With this setting, the statement
connect sys/sys_password@TNS_NAME_OF_DB enables the Oracle Warehouse Builder Assistants to connect to SYS user.
NAS_DATA directory.
From the OWB_HOME, start the Name and Address Server:
On Windows, run owb\bin\win32\NAStart.bat.
On Linux, Run owb/bin/unix/NASTART.sh.
Open the log file: owb\bin\admin\NASvr.log.
The log contains a list of installed countries.
If there is no such list, then verify that you have extracted the regional library data to the correct location. If you have extracted the data to the wrong location, then you can either reinstall the data, or modify the owb\bin\admin\NameAddr.properties file to indicate the correct file path. If you modify the NameAddr.properties file, then stop and restart the Name and Address Server as follows:
On Windows, start the server by running owb\bin\win32\NAStart.bat. Stop the server by running owb\bin\win32\NAStop.bat.
On UNIX, start the server by running owb/bin/unix/NAStart.sh. Stop the server by running owb/bin/unix/NAStop.sh.
After you have verified the installation, you can stop the Name and Address Server if you want, because it is automatically started at the execution of any mapping that employs the Name and Address operator.
Autoextend On.
Note:
To grant permission to an Oracle Warehouse Builder repository user to use Enterprise Manager for performing tasks, enter the following command in SQL*Plus:GRANT SELECT any dictionary to "&OWB repository user";-mx parameter in the owbclient.bat file. The -Dlimit parameter in the owbclient.bat file specifies the memory threshold (80% of Dlimit) at which Oracle Warehouse Builder memory manager begins to assist Java garbage collection. If you change the -mx parameter value, set the -Dlimit parameter to the same value, or at least to 90% of the value. Setting the -Dlimit to a low value can have a negative impact on the performance of Oracle Warehouse Builder.-Dlimit parameter in Oracle Warehouse Builder as follows:
Exit Oracle Warehouse Builder.
Open this file in a text editor:
On Windows, open the $OWB_HOME\bin\win32\ombplus.bat.
On UNIX, open the $OWB_HOME\bin\win32\owbclient.sh.
Change the -Dlimit parameter to 334.
Save and close the file.
Restart Oracle Warehouse Builder.
MAX_ENABLED_ROLES parameter, this error occurs.MAX_ENABLED_ROLES parameter in the init.ora file. When you uninstall a repository or a target schema, delete the associated roles as well.From SQL*Plus, connect to a SYS user.
Create user test_lj identified by test_lj; Grant connect, resource to test_lj;
Create OWB_HOME/owb/bin/unix/test.sh with the following contents:
../unix/loadjava -thin -verbose -order -resolve -user 'test_lj/test_lj@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hpdgpa3)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=dgpadw)))' ../../lib/int/rtpserver.jar
Change directory to OWB_HOME/owb/bin/unix/.
Run test.sh.
OWB_HOME being used to run the control center service. To ensure access, run the Net Configuration Assistant from the OWB_HOME and then restart the control center service.
To set up a TNS name for use by database links, the TNS name must be accessible from the database server home. To ensure access, run the Net Configuration Assistant from the database server home.
SERVICE_NAME is added to the TNSNAMES.ORA as a subclause to the CONNECT_DATA section in the Net Service Name entry. This replaces the (SID=SIDname) subclause in previous releases of the database, for example, Oracle Database8i (8.1.x).TNSNAMES.ORA file as follows:
Use the GLOBAL_DBNAME parameter in the LISTENER.ORA for each SID to identify as a separate service. Use the value of this parameter as the value of the SERVICE_NAME parameter. You must activate any changes that you make to LISTENER.ORA for this purpose by stopping and restarting the listener process.
Use the values of the parameters that exist in the INIT.ORA, namely SERVICE_NAMES and DB_DOMAIN, to determine the value of the SERVICE_NAME that you must use in TNSNAMES.ORA. The valid construction of this value is SERVICE_NAMES.DB_DOMAIN with the period separating the two INIT.ORA values. If your SERVICE_NAMES is BIKES and your DB_DOMAIN is COM, then your SERVICE_NAME is BIKES.COM.
If there is no DB_DOMAIN parameter set in your INIT.ORA, or if there is no GLOBAL_DBNAME in the LISTENER.ORA, then you can use the SERVICE_NAMES from the INIT.ORA in your TNSNAMES.ORA for the SERVICE_NAME parameter.
For example, if INIT.ORA contains SERVICE_NAMES = "TEST817" and db_domain is not set, then the TNSNAMES.ORA entry is: CONNECT_DATA =(SERVICE_NAME = "TEST817")).
If you have multiple values specified in the SERVICE_NAMES parameter in the init.ora, then you can use one of them. If SERVICE_NAMES is not set, then you can use DB_NAME.DB_DOMAIN parameters from the INIT.ORA file.
If SERVICE_NAMES and DB_DOMAIN is not set in the INIT.ORA and there is no GLOBAL_DBNAME in the LISTENER.ORA, then your SERVICE_NAME in TNSNAMES.ORA file is DB_NAME.
SYS user and entering the following command: SELECT * FROM GLOBAL_NAME; If the Global Name of the old database does not match that of the new database, then a domain mismatch is causing this error.ALTER DATABASE RENAME GLOBAL_NAME TO xxx10G.US.ORACLE.COM; or redeploy your connectors.
Refer to Oracle Warehouse Builder User's Guide for information on deploying connectors.
select_catalog_role privilege. If you have the same dimension object defined in multiple Oracle Warehouse Builder target schemas, then Oracle Export creates duplicates in the export file, and this error occurs when you import.SYS user to the existing version of the Oracle Database from which you exported the target schemas. Enter the following statement in
SQL*Plus: revoke select_catalog_role from OLD_Target_Schema;
Export the target schema into an Oracle .DMP file again, and then import the file into Oracle Database.
Ensure that OWB_HOME and PATH are set correctly. Your Oracle home directory must to point to the OWB_HOME. Set your PATH variable to include the OWB_HOME\bin directory before any other Oracle products.
Ensure that the TNSNames.ora file is configured correctly:
On Windows, from Oracle Database program group, start Net Configuration Assistant and select Local Net Service Name Configuration to configure TNSNames.ora.
On UNIX, set OWB_HOME and PATH to the OWB_HOME for Oracle Warehouse Builder 11g Release 2 (11.2) then run OWB_HOME/bin/netca to start Net Configuration Assistant. Select Local Net Service Name Configuration to configure TNSNames.ora.
sys.dbms_aq.SYS user and run a query to identify which user sessions are pinning the Advanced Queue packages, using the following query as an example:
column s.sid format a5;
column s.serial# format a8;
column s.username format a10;
column objectname format a10;
select distinct
s.sid,
s.serial#,
s.username,
x.kglnaobj as objectname
from
dba_kgllock l,
v$session s,
x$kgllk x
where
l.kgllktype = 'Pin' and
s.saddr = l.kgllkuse and
s.saddr = x.kgllkuse and
x.kglnaobj in ('DBMS_AQ', 'DBMS_AQADM');
The following is an example of the output you may receive:
SID SERIAL# USERNAME OBJECTNAME --- ------- -------- ---------- 9 29623 RTU_4942 DBMS_AQ
Noting the SID and Serial Number, issue the following command to stop the user sessions:
ALTER SYSTEM KILL SESSION 'SIDNoted, SerialNumberNoted';
For example, enter the following command to stop the session listed in the sample output for this error:
ALTER SYSTEM KILL SESSION '9,29623';
CURSOR_SHARING to EXACT.This section includes causes and actions for the following installation problems:
If the client is installed on Windows and you started the client from the Start menu, you may not see any error messages.
OWB_HOME\owb\owbclient.bat. You are likely to encounter an error message such as "No fonts were found in 'drive:\Program Files\ Qarbon\viewlet Builder3jre\lib\fonts'".OWB_HOME\bin is listed correctly in the Environmental Variables.At the DOS command prompt, enter:
cd OWB_HOME\owb\bin\win32\
Run owbclient.bat.
When the program hangs, press Ctrl+Break keys.
This produces the thread-dump. Contact Oracle Support and provide them with this information to help identify the problem.
To check, verify, or reinstall the Java Virtual Machine (JVM) server in the database, refer to My Oracle Support:
In your Web browser, go to the following URL: https://support.oracle.com.
Log in to My Oracle Support, or register as a new user.
Enter the following terms into the Search field, separating each term by semicolons):
INITJVM.SQL; INSTALL; JAVAVM; JVM; VERIFY; SERVER; INSTALL; CLEANUP
Press Enter.
This search returns the support notes for the JVM. The number of available documents frequently changes because My Oracle Support creates, merges, and deletes various support notes. This string of search words returns the most current and pertinent documents.
If Oracle Warehouse Builder is producing errors or exhibiting other unexpected results, additional error logging can help you and Oracle Support identify the cause. You can create a temporary file for logging by using owbclient.logging.properties in Oracle Warehouse Builder user home directory to get trace information from the client. This should only be used for specific troubleshooting as this generates excessive debugging information if left on the system. The following example writes a logging file owbclient.log to /tmp. Using Java logging properties, enter:
console.messageFormat={1,time}: {2}
file.messageFormat=[{3} ({4})] {1,time}: <{0}> {2}
handlers=java.util.logging.FileHandler
java.util.logging.FileHandler.pattern=/tmp/owbclient.log
level = DEBUG_ALL
java.util.logging.FileHandler.level=ALL
java.util.logging.ConsoleHandler.level=DEBUG_ALL
Stop and restart the client for this to take effect. Remember to remove this after you have your log, and then stop and restart the client again.
You must ensure that Oracle Universal Installer added the bin directory of the new Oracle Warehouse Builder 11.2 installation to the system path ahead of other Oracle product bin directories. This is easily checked using the command prompt.
To verify the system path for Oracle Warehouse Builder on Windows:
In the Command Prompt window, enter path.
C:\> path
The system returns the value of the path variable; verify that C:\OWB112 precedes other Oracle products.
PATH=C:\OWB112\bin;C:\oracle\product\11.2.0\db_1\bin; ... \
To verify the system path for Oracle Warehouse Builder on Linux:
Echo the PATH variable in your shell.
When installing Oracle Warehouse Builder on an Oracle RAC cluster, the Oracle RAC service names must be unique and match the node that they're running on. This ensures that the Control Center Service uses a database service on the correct node, which in turn ensures access to the correct file system.
For example, the wb_rt_service_nodes should be similar to those in Table A-1.