Skip Headers
Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console
11g Release 1 (11.1.1)

Part Number E14849-06
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub

A Troubleshooting DAC

This appendix describes common problems and how to resolve them.

This appendix contains the following topics:

Recovering From a Lost Encryption Key

The process of recovering from the loss of an encryption key involves clearing the encrypted data from the DAC repository, creating a new cwallet.sso file (with a new encryption key), and stamping the DAC repository with the new encryption key.

This procedure involves steps that use automation utility commands. Before you can use these commands, you need to configure the automation utility property file.

To recover from the loss of an encryption key:

  1. Remove all the encrypted data from the DAC repository by calling the automation utility command clearEncryptedData.

  2. Call one of the following automation utility commands to generate a new cwallet.sso file:

    • dbCredentials <cwallet.sso file path> <user name> -withKey

      Use this command to generate a cwallet.sso file with a encryption key that you specify.

    • dbCredentials <cwallet.sso file path> <user name> -randomKey

      Use this command to generate a cwallet.sso file with a randomly generated encryption key.

  3. Copy the new cwallet.sso file to the appropriate directory where the DAC repository can access it. The default directory is <DAC_Config_Location>\conf-shared\security\repository.

  4. Stamp the DAC repository with the new encryption key:

    1. Log in to the DAC Client.

      The following message will be displayed:

      Encryption key validation value is missing from the DAC repository. Would like to add it now?

    2. Click OK to add the new encryption key to the DAC repository.

    3. Enter the table owner name and password, and click OK.

      A message informs you whether the operation was successful.

    4. Distribute the new cwallet.sso file to all DAC installations that connect to this repository.

Restarting an Execution Plan When the DAC Server Fails

If the DAC Server crashes while an execution plan is running, some workflows associated with the execution plan may have been submitted to Informatica. When the DAC Server is restarted and the failed ETL process resumes, the workflows that DAC submitted to Informatica before the crash may have completed or failed or may still be running. You do not have to perform any manual intervention. DAC manages the tasks submitted for execution prior to the crash in the following manner:

Discarding a Failed Execution Plan

You can discard an execution plan that failed by navigating to the Current Run tab, right-clicking on the execution plan and changing its status to Mark as Completed. This will force the run status to be updated as Completed. When you submit a request for another run, the DAC Server creates another instance of it.


Perform this procedure in a development or test environment only, because it might leave the data in an inconsistent state, causing you to have to reload all of the data.

Failure of Aggregator Transformation Tasks with Sorted Input

Tasks that use Informatica Aggregator Transformation can fail when the Sorted Input option is active. The tasks SDE_DTLFORECASTFACT and SDE_COSTLIST are examples of tasks that can fail in such a situation.

To prevent such tasks from failing, in Informatica PowerCenter Designer, navigate to Mapping Designer, open the corresponding mapping, and in the Aggregator transformation, deselect the Sorted Input check box.

In Case of Abnormal Termination of the DAC Server

If the DAC Server fails during the execution of an execution plan, the status of the execution plan will remain as Running. When the DAC Server is started again, it will automatically run the execution plan if the Auto Restart ETL system property is set to True. If the same system property is set to False, when the server restarts, it will set the correct status as Failed. In order to execute the execution plan from the point of failure, submit the request to the server again.

The DAC Server will automatically terminate if it looses connection to the DAC repository.

DAC Task Failing on Non-English Operating System

DAC Server uses pmcmd to initiate the workflows on Informatica Server. In the English-based operating systems, DAC issues the commands in the non-blocking mode (asynchronously), and polls Informatica for the status of the workflow. The output of the pmcmd getWorkFlowDetails is spooled to the Domain_Home\dac\log directory, and then gets parsed to determine whether the workflow is still running, completed successfully, or failed.

However, for non-English-based operating systems, DAC issues commands in the waiting mode (synchronously). This means that when the process completes the exit code tells DAC whether or not the workflow succeeded.

The commands used by DAC to communicate with Informatica are externalized in a file called infa_commands.xml. The DAC command template does not have a place holder for specifying the wait mode. Without this wait mode configuration, on a non-English- based operating system, DAC proceeds with the execution even before the workflow completes executing. This might result in errors, such as Informatica's bulk loader failing because of indexes are present or fact tables are being loaded without foreign key references.To fix the problem, go to Domain_Home\dac\conf folder and edit the file called infa_commands.xml. Depending upon the version of informatica you are using, edit either the block called START_WORKFLOW_7 or START_WORKFLOW_8 and verify whether %WAITMODE is in the syntax. If it is not, add it as follows:

  1. For START_WORKFLOW_7 replace the following line:

    pmcmd startworkflow -u %USER -p %PASSWORD  -s %SERVER:%PORT -f %FOLDER -lpf 


    pmcmd startworkflow -u %USER -p %PASSWORD  %WAITMODE -s %SERVER:%PORT -f 
  2. For START_WORKFLOW_8 replace the following line:

    pmcmd startworkflow -sv %SERVER -d %DOMAIN -u %USER -p %PASSWORD -f %FOLDER 


    pmcmd startworkflow -sv %SERVER -d %DOMAIN -u %USER -p %PASSWORD %WAITMODE -f 
  3. Once you modify this file (the modifications should be done both on the DAC client and the server machines), restart the DAC server and client for the changes to take effect.

DAC Task Failing in a Multi-Source Environment

When you have a multi-source environment, you can use a single execution plan to extract data for a given subject area for all the sources. When you build the execution plan, DAC creates as many instances of the extract tasks as there are sources, without having the need to duplicate the workflows or DAC metadata.

At runtime, DAC creates an individual parameter file for each session, which contains the evaluated name-value pairs for all parameters. DAC stores the parameter file at the location specified by the DAC system property InformaticaParameterFileLocation. The default location is <Domain_Home>\dac\Informatica\parameters.

If a task in a multi-source environment fails and you want to restart it, you can do so from DAC or from Informatica. See the following instructions.

To restart a failed task in a multi-source environment using DAC:

  1. Go to the Execution Plans tab in the Execute view, and select the appropriate execution plan.

  2. Click the Ordered Tasks subtab.

  3. Query for the task that you want to restart.

  4. Click Unit Test in the toolbar.

    The Unit Test dialog displays the steps that DAC will carry out if the task is executed.

  5. Click Execute in the Unit Test dialog.

    Note: The DAC system property Dryrun must be set to False for the task to run.

To restart a failed task in a multi-source environment using Informatica

  1. In DAC, locate the parameter file for the failed task.

    The parameter file is stored in the location specified by the DAC system property InformaticaParameterFileLocation. The default location is <Domain_Home>\dac\Informatica\parameters.

  2. Copy the file to the location specified by the Informatica parameter $PMSourceFileDir. This is the location where Informatica looks for parameter files.

    Note: If DAC is configured correctly, the location specified by $PMSourceFileDir and the location specified by the DAC system property InformaticaParameterFileLocation should be the same. If the locations are the same, the parameter file will already be in the correct location.

  3. Change the parameter file name to match the naming convention Informatica uses for parameter files:

    <Informatica directory name>.<Informatica session name>.txt
  4. Run the workflow in Informatica.

Restoring the DAC Repository on Unicode Oracle Databases

When the DAC repository resides on a non-Unicode Oracle database, the Informatica workflow names may not fit into the corresponding DAC fields, which causes tasks to fail during ETL processes.

To work properly in multi-byte character environments, the DAC repository should be created with the Unicode option selected.

Perform the following procedure to fix an existing DAC repository with this problem. You can use the DAC Client or DAC command line utilities to perform the procedure.

  1. Connect to the existing DAC repository.

  2. Export the entire repository (logical, system, and run time categories).

  3. Stop the DAC Server and all DAC Clients except for the one you are using.

  4. Drop the current repository.

  5. If you are using a DAC Client, restart it.

  6. Create a new repository with the Unicode option selected.

  7. Import the DAC metadata that you exported in step 2.

  8. Re-enter the passwords for all Informatica services and all physical data source connections.

Handling Parameter Files with Multi-Line Parameters

Informatica workflows initiated by DAC fail with error code 17 and the error message "Parameter file does not exist" when the parameter file has multi-line parameters. See the below text for an example. Note that in the following example, an error occurs because DAC issues pmcmd with -lpf in the syntax.

getTableName() TRUNCATE PARTITION ' || '"' ||COUNTRY_REGION_NAME|| '"' ||'''' 
CONNECT BY prev = PRIOR curr START WITH curr = 1 

To prevent this issue, edit <DAC_Config_Location>\conf-shared\infa_command.xml and replace all instances of <-lpf> with <-paramfile>.

This workaround will ensure that DAC uses -paramfile in the pmcmd syntax and Informatica can recognize the multi-line parameters.

Resetting Refresh Dates at the Task Level

In DAC, you can reset refresh dates at the task level rather than the table or subject area level by using one of the following options:

Error When Using Oracle(OCI8) Connection Type

If you are using the Oracle (OCI8) connection type when defining a physical data source and receive an error such as "no ocijdbc11," consider the following points:

Tables Are Not Truncated When More Than One Task Writes to the Same Target Table

The DAC behavior for truncating tables when more than one DAC task writes to the same target table is as follows:

If you want to override this default behavior, you can create a task action and use the Preceding Action action type to specify in a custom SQL statement that the table should be truncated. For more information about configuring task actions, see "Using Actions to Optimize Indexes and Collect Statistics on Tables".

Discrepancy Between DAC Task and Informatica Workflow

Discrepancies may exist between the status of a DAC task and the status of an Informatica workflow for the following reasons:

Making the DAC Server Visible to All Clients

If the DAC Server is not visible to all DAC Clients, make sure the DAC system property "DAC Server Host" has the correct value and not "localhost." Depending on your environment, you may need to use a fully qualified name. You can also try using the IP address of the DAC Server machine. You should be able to ping the DAC Server machine from the DAC Client machine. Make sure the ping requests do not time out.

You should also inspect the network settings to make sure the port number that is registered for the DAC Server Port system property is open and available.