Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console 11g Release 1 (11.1.1) Part Number E14849-05 |
|
|
PDF · Mobi · ePub |
This chapter provides instructions for performing common DAC tasks.
This chapter contains the following topics:
Pointing Multiple Informatica Integration Services to a Single Informatica Repository
Viewing DAC Metrics Using Fusion Middleware Control MBean Browser
You can access the DAC Server through a command line to start and stop execution plans and to get status information for servers, databases, and execution plans. This feature enables you to access the DAC Server using third-party administration or management tools, without using the DAC Client. Refer to the file dacCmdLine.bat/.sh for usage.
This section includes the following topics:
The Command Line utility enables you to invoke commands on a DAC Server running on a remote or local machine.
To set up command line access to the DAC Server
Make sure you have installed the supported version of the Java SDK.
Copy the following files from the <Domain_Home>\dac directory to a local directory:
DAWSystem.jar
dac.properties
dacCmdLine.bat or dacCmdLine.sh
In the dacCmdLine.bat file, do the following:
Edit the JAVA_HOME variable to point to the directory where the Java SDK is installed.
Make sure there are no spaces in the path reference.
Edit the DAC_HOME variable to point to the directory where the DAC is installed.
In the dac.properties file, edit the following parameter values.
Parameter | Value |
---|---|
ServerHost= |
Host name of the DAC Server. |
ServerPort= |
Port of the DAC Server. The default is 3141. |
RepositoryStampVal= |
Repository stamp that appears in the DAC Client Login Details screen. To find this value, in the DAC Client navigate to Help, then select Login Details. |
The dac.properties file should look similar to the following:
ServerHost=<host name> ServerPort=3141 RepositoryStampVal=851E0677D5E1F6335242B49FCCd6519
Follow this procedure to use the command line to access the DAC Server.
To use the command line to access the DAC Server
At the command prompt, enter the following:
dacCmdLine <method name> <optional execution plan name>
where method name
is one of the following:
Method Name | Description |
---|---|
StartServer |
Starts the DAC Server (Web mode only). |
StopServer |
Stops the DAC Server (Web mode only). |
StartETL |
Starts an execution plan. You must specify an execution plan name. -wait option lets you start the execution plan in synchronous mode. |
StopETL |
Stops the operation of an execution plan. You must specify an execution plan name. |
ETLStatus |
If you do not specify an execution plan name, the status of the execution plan that last ran is returned. If you specify an execution plan name, the status of the specified execution plan is returned. |
DatabaseStatus |
Verifies whether the DAC Server can connect to all active database connections. You do not need to specify an execution plan name. |
InformaticaStatus |
Verifies whether the DAC Server is able to ping all active Informatica PowerCenter Services machines. |
Note:
The method names are case insensitive. Execution plan names are case sensitive. Also, if the execution plan name contains spaces, place beginning and ending double quotes around the name.
For example:
Command Line | Description |
---|---|
dacCmdLine EtlStatus |
Returns the status of the execution plan that last ran. |
dacCmdLine EtlStatus Forecast |
Returns the status of the last instance of the Forecast execution plan. |
dacCmdLine StopEtl Forecast |
If the execution plan currently running is Forecast, the operation will be terminated. Otherwise, the request is ignored. |
dacCmdLine databasestatus |
Returns the health status of all the database connections as defined in the DAC repository from the DAC Server. |
dacCmdLine InformaticaStatus |
Returns the health status of all the Informatica PowerCenter Services connections as defined in the DAC Client on the Informatica Services tab. |
The command line feature enables you to get the following status information:
Summary of the requested execution plan. If there are multiple instances of the same execution plan, a summary of the instance that last ran is returned. Below is an example of the information contained in the summary.
(c) 2009 Oracle Oracle DAC Server comprising the etl execution-management, scheduler, logger, and network server. ETL details for the last run: ETL Process Id : 255 ETL Name : Complete ETL Run Name : DRY RUN OF Complete ETL: ETL Run - 2004-06-17 18:30:13.201 DAC Server : (<host name>) DAC Port : 3141 Status: Stopped Log File Name: Complete_ETL.255.log Database Connection(s) Used : OLTP jdbc:microsoft:sqlserver://<host name>:1433;DatabaseName=OLTP Data Warehouse jdbc:microsoft:sqlserver://<host name>:1433;DatabaseName=olap Informatica Server(s) Used : InformaticaServer4-<host name>:(4) InformaticaServer2-<host name>:(2) InformaticaServer3-<host name>:(3) InformaticaServer1-<host name>:(10) Start Time: 2004-06-17 19:00:06.885 Message: ETL was interrupted Actual Start Time: 2004-06-17 18:30:13.357 End Time: 2004-06-17 19:05:56.781 Total Time Taken: 35 Minutes Start Time For This Run: 2004-06-17 19:00:06.885 Total Time Taken For This Run: 5 Minutes Total steps: 212 Running steps: 0 Complete steps: 142 Failed/Stopped steps:70
Summary of connection status to all active databases and Informatica servers.
This section describes the DAC repository command line properties that are exposed by the AutomationUtils.bat file, which is located in the <Domain_Home>\dac directory.
Note: Before you can use the command line properties, you must first configure DAC repository database and authentication properties in the automationUtils.properties file, which is located in the <Domain_Home>\dac directory. Alternatively, you can create your own connection file that contains the repository database and authentication properties. Many command line properties discussed in this section require the path to the connection file (either automationUtils.properties or a user-defined connection file). The path to the connection file must include the file name.
The following DAC repository command line parameters are available in the AutomationUtils.bat file:
The analyzeDACSchema property analyzes the DAC repository tables.
Syntax:
<path to connection file> analyzeDACSchema
The applyDistributedDevPatch property applies a patch to the DAC repository.
Syntax:
<path to connection file> applyDistributedDevPatch <file name>
where:
Parameter | Description |
---|---|
|
Name of the XML file. |
The assembleSubjectArea property assembles a subject areas.
Syntax:
<path to connection file> assembleSubjectArea <container name> <subject area name>
The buildExecutionPlan property builds an execution plan.
Syntax:
<path to connection file> buildExecutionPlan <execution plan name>
The changeEncryptionKey property changes the encryption key in the credentials file and re-encrypts all encrypted information in the DAC repository.
Syntax to change encryption key with a randomly generated key:
<path to connection file> changeEncryptionKey -randomkey
Syntax to change encryption key by explicitly specifying the key from command line input:
<path to connection file> changeEncryptionKey
The clearEncryptedData property removes all encrypted data from the DAC repository.
Syntax:
<path to connection file> clearEncryptedData <schema owner>
Note: An authentication file is not required for this operation. The repository password will be taken from command line input.
Caution:
This operation will clear all database, Informatica, and external executor passwords as well as all DAC user data. Use this command only when the authentication file with the encryption key was lost and cannot be recovered. After removing the encrypted data from the DAc repository, generate the authentication file with a new encryption key, and log into the DAC repository using the DAC Client. When prompted to add the encryption key, update all passwords in the DAC repository and distribute the new authentication file to all required DAC users.
The cmdCredentials parameter stores credentials for the DAC command line utility for both the Fusion Middleware and DAC standalone authentication modes.
Syntax:
cmdCredentials <path to cwallet.sso file> <user name>
Note: The password will be taken from command line input. Also, make sure to include the cwallet.sso file name in the path.
The createDACSchema property creates the schema of a new DAC repository.
Syntax:
<path to connection file> createDACSchema <unicode> <tablespace>
where:
Parameter | Description |
---|---|
|
Specifies whether the schema is created as unicode. Possible values are |
|
The name of the default tablespace in which the schema is created. Surround the name with double quotation marks. Note: If the value for the AuthenticationType property in the connection file is |
The createDACUser property creates a DAC schema user.
Syntax for FMW authentication:
<path to connection file> createDACUser <user name> [role1] [role2] ...
Syntax for database authentication:
<path to connection file> createDACUser <user name> [role1] [role2] ... [-dbVerification <schema owner>]
Note: The user password will be taken from command line input.
The createDWSchema property creates the data warehouse schema using the schema definition file.
Syntax:
<path to connection file> createDWSchema <dwSchemaDefinitionFileName> <unicode> <tablespace>
where:
Parameter | Description |
---|---|
|
Specifies the data warehouse schema definition file location and name. This file is generated by the generateDWSchemaDefinitionFile command line parameter. |
|
The name of the default tablespace in which the schema is created. Surround the name with double quotation marks. |
unicode |
Specifies whether the schema is created as unicode. Possible values are |
Note: The file create.sql is generated and stored in the <Domain_Home>/dac/conf/sqlgen/sql/oracle directory of an enterprise installation, and the dac/conf/sqlgen/sql/oracle directory in a standalone or client installation. User authentication is not required for this command.
The createPatchOfObjectsBetweenTimeStamps property creates a DAC repository metadata patch containing all eligible objects that are new or changed between two timestamps.
Syntax:
<path to connection file> createPatchOfObjectsBetweenTimeStamps <patch name> <from timestamp> <to timestamp>
where:
Parameter | Description |
---|---|
|
The beginning timestamp for the timestamp range in format yyyymmdd-hh:mm:ss:ss. The hh value must be between 00-23. |
|
The ending timestamp for the timestamp range. The format is the same as that of the "from timestamp." |
-now |
A special string to specify the current timestamp. |
The dbCredentials property generates an authentication file (cwallet.sso) with the DAC repository credentials and encryption key.
Syntax for explicit key and credentials:
dbCredentials <path to cwallet.sso file> <user name> -withKey
Syntax for randomly generated encryption key and credentials:
dbCredentials <path to cwallet.sso file> <user name> -randomKey
Syntax to update credentials in existing file without modifying the encryption key:
dbCredentials <path to cwallet.sso file> <user name> -noKey
Note: The user password will be taken from command line input.
The deleteObjects ExecutionPlan property deletes executions plan from the DAC repository.
Syntax:
<path to connection file> deleteObjects ExecutionPlan <name 1> [name 2] ...
The deleteObjects SubjectArea property deletes subject areas from the DAC repository.
Syntax:
<path to connection file> deleteObjects SubjectArea <container name> <subject area name 1> [subject area name 2] ...
The dropDACSchema property drops the schema of the DAC repository.
Syntax:
<path to connection file> dropDACSchema
The dropDWSchemaFromSchemaDefinitionFile property drops the data warehouse schema using the schema definition file.
Syntax:
<path to connection file> dropDWSchemaFromSchemaDefinitionFile <dwSchemaDefinitionFileName> <execute>
where:
Parameter | Description |
---|---|
|
Specifies the data warehouse schema definition file location and name. This file is generated by the generateDWSchemaDefinitionFile command line parameter. |
execute |
Possible values are |
Note: The file drop.sql is generated and stored in the <Domain_Home>/dac/conf/sqlgen/sql/oracle directory of an enterprise installation, and the dac/conf/sqlgen/sql/oracle directory in a standalone or client installation. User authentication is not required for this command.
The Export property exports DAC metadata from the DAC repository for specified source system containers.
Syntax:
<path to connection file> export <folder name> <container name 1> <container name 2> ...
where:
Parameter | Description |
---|---|
|
Full path to the location of the export file structure. |
|
(Optional) Name of the source system container for which you want to export DAC metadata. If no container is named, all containers that are found in the file structure will be exported. |
The exportCategory property exports DAC metadata from the DAC repository based on the Logical, Run Time, or System categories.
Syntax:
<path to connection file> exportCategory <folder name> <category>
where:
Parameter | Description |
---|---|
|
Full path to the root of the export file structure. |
|
Possible values are the following:
|
The exportPatch property exports a DAC metadata repository patch in XML format.
Syntax:
<path to connection file> exportPatch <file name> <patch name>
where:
Parameter | Description |
---|---|
|
Name of the XML file. |
The jksPassword parameter stores the password for the Java key store.
Syntax:
jksPassword <path to cwallet.sso file>
Note: The password will be taken from command line input. Also, make sure to include the cwallet.sso file name in the path.
The generateDWSchemaDefinitionFile generates a data warehouse schema definition file.
Syntax:
<path to connection file> generateDWSchemaDefinitionfile <dwSchemaDefinitionFileName> <container 1> <container 2> ...
Note: The definition file will be generated for the specified containers. If you do not specify a container, the file will be generated for all containers.
The Import property imports DAC metadata into the DAC repository for specified source system containers.
Syntax:
<path to connection file> import <folder name> [-noTruncate] [-noUpdate] <container name 1> <container name 2> ...
where:
Parameter | Description |
---|---|
|
Full path to the root of the import file structure. |
|
(Optional) Name of the source system container for which you want to import DAC metadata. If no container is named, all containers that are found in the file structure will be imported. |
|
By default the repository will be truncated upon import. Use the -noTruncate option for incremental import. |
|
By default existing records will be updated during incremental import. Use the -noUpdate option if you only want to insert new records. |
The importCategory property imports DAC metadata from the DAC repository based on the Logical, Run Time, or System categories.
Syntax:
<path to connection file> exportCategory <folder name> <category>
where:
Parameter | Description |
---|---|
|
Full path to the root of the import file structure. |
|
Possible values are the following:
|
|
By default the repository will be truncated upon import. Use the -noTruncate option for incremental import. |
|
By default existing records will be updated during incremental import. Use the -noUpdate option if you only want to insert new records. |
The repositoryXMLSnapshot property exports a snapshot of the DAC repository in XML format.
Syntax:
<path to connection file> repositoryXMLSnapshot <file name>
where:
Parameter | Description |
---|---|
|
Name of the XML file. |
The serverSetup property sets up the DAC Server connection to the DAC repository.
Syntax:
<path to connection file> serverSetup <database type> <database name/instance name/tns> <host> <port>
Note: If Fusion Middleware authentication is specified in the connection file, the DAC Server URL should be specified in the properties file and the DAC Server will be set to where the URL points (location can be a remote server). If DAC standalone authentication is specified in the connection file, you must configure the repository connection properties in the properties file. The DAC Server will be set up in the local installation. The user name, password, and encryption key will be taken from the database credentials (cwallet.sso) file.
The setPassword parameter sets the password for the Informatica Integration Service, Informatica Repository Service, and physical data sources in the DAC repository.
Syntax:
<path to connection file> setPassword <type> <logical name>
where:
Parameter | Description |
---|---|
|
Possible values are the following:
|
|
Logical name of the server or physical data source record in DAC. |
Note: The password will be taken from command line input. If the logical name or password contains spaces, surround the entry in double quotes.
The upgradeDACSchema parameter upgrades the DAC repository.
Syntax:
<path to connection file> upgradeDACSchema
The upgradeDWSchemaFromSchemaDefinitionFile property upgrades the data warehouse schema using the schema definition file.
Syntax:
<path to connection file> upgradeDWSchemaFromSchemaDefinitionFile <dwSchemaDefinitionFileName> <unicode> <execute>
where:
Parameter | Description |
---|---|
|
Specifies the data warehouse schema definition file location and name. This file is generated by the generateDWSchemaDefinitionFile command line parameter. |
unicode |
Specifies whether the schema is created as unicode. Possible values are |
execute |
Possible values are |
Note: User authentication is not required for this command.
Follow this procedure to set up the DAC Server to be run automatically when your machine reboots.
Note: This procedure applies to the DAC Server in standalone mode only.
To set up the DAC Server to run automatically upon rebooting the machine
From the Start menu, select Programs, Accessories, System Tools, Scheduled Tasks.
Double-click Add Scheduled Task.
In the Scheduled Task Wizard, browse to startserver.bat, and click Open.
Select the option When my computer starts, and click Next.
Enter the domain user account to start the DAC Server and a password, and click Finish.
The startserver task is displayed in the Scheduled Task window.
Right-click the task and select Properties.
In the Settings tab, deselect the Stop the task if it runs for 72 hours check box.
To start the DAC Server as a scheduled task
From the Programs menu, select Accessories, System Tools, Scheduled Tasks.
Right-click startserver, and then click Run.
To stop the DAC Server as a scheduled task
From the Programs menu, select Accessories, System Tools, Scheduled Tasks.
Right-click startserver, and then click End Task.
To check if the DAC Server is running
From the Programs menu, select Accessories, System Tools, Scheduled Tasks.
Select the startserver task.
In the Windows menu bar, select View, Details.
In the DAC standalone mode, you can run two DAC Servers on the same machine as long as they are listening on different ports and pointing to two different repositories.
Note: This procedure does not apply when the DAC Server is running as a service of the DACServer application on WebLogic Server.
To run two DAC Servers on the same machine
Copy the <Domain_Home>\dac directory to a different directory on the same machine.
For example, you might copy the C:\<Domain_Home>\dac directory to C:\<Domain_Home>\DAC_SERVER2\dac.
Edit the config.bat file to set the DAC_HOME variable appropriately for each instance.
For example if you copy the C:\<Domain_Home>\dac directory to C:\<Domain_Home>\DAC_SERVER2\dac, make sure that the C:\<Domain_Home>\DAC_SERVER2\dac config.bat file is configured correctly.
Launch each of the DAC Clients by navigating to the DAC directories and double-clicking the startclient.bat file.
For each instance, configure the DAC Server connection.
Navigate to Tools, DAC Server Management, DAC Server Setup.
A message dialog states this operation should be performed on the machine running the DAC Server. It asks whether you want to continue.
Click Yes.
In the Repository Connection Information tab, enter the appropriate information for each instance. The Database Host should be the same for each instance, and the Database Port should be different.
Click Save.
For each instance, configure the DAC repository connection.
Navigate to Tools, DAC Server Management, Repository Configuration.
In the Repository Configuration dialog, enter the following information:
Field | Description |
---|---|
Mode |
Select Standalone. |
Standalone Mode Configuration: |
If you are using standalone mode configuration, enter the following information: |
Host |
Name of the host machine where the DAC Server resides. |
Alternative Hosts |
(Optional) Names of additional machines hosting DAC Servers that can be used to connect to the DAC repository if the main DAC Server host machine fails. Separate multiple hosts with commas. |
Port |
DAC Server port. |
Start each DAC Server from its directory.
You can use one DAC Client to access multiple DAC Servers. The DAC Servers can reside on the same machine, or they can each reside on a different machine. Each DAC Server must listen on its own port and point to a single DAC repository.
You can install multiple Informatica Integration Services and point them to a single Informatica repository. You need to register each instance of the Integration Service in DAC and specify a unique machine name and service name. For instructions on registering Integration Services, see "Registering Informatica Services in DAC".
When you reset a data warehouse all tables are truncated and refresh dates are deleted. This allows you to perform a full load of data into the data warehouse.
To reset the data warehouse
On the Tools menu, select ETL Management, and then select Reset Data Warehouse.
The Reset Data Warehouse dialog is displayed and requests you to confirm you want to reset the data warehouse.
Enter the confirmation text in the text box.
Click Yes.
Fusion Middleware Control MBean Browser is an Oracle Web application (based on JMX MBean containers) that you can use to view information about running, failed and queued execution plans and the status of the DAC Server.
Information about execution plans is available through the MBean Browser only if the DAC Server is running.
To display DAC metrics using Fusion Middleware Control MBean Browser
Display Fusion Middleware Control.
In the Navigator window, expand the WebLogic Domain folder and the bifoundation_domain node, and select the AdminServer node.
Display the WebLogic Server menu and select System MBean Browser from the menu to display the Fusion Middleware Control System MBean Browser.
Display the DACMetrics MBean by selecting the following: ApplicationDefinedMBeans, com.oracle.dac, Server:<server name>, Application: DACServer, EMIntegration, and DACMetrics MBean.
The attributes and operations that are available to be invoked are displayed.
The DAC Server runs as a service of the DACServer application on WebLogic Server. The WebLogic Server Administration Console enables you to monitor the status of the DAC Server.
Note: The DAC Server can also run in standalone mode. For information about the differences between the standalone and WebLogic Server modes, see "About DAC Authentication Modes".
To monitor the DAC Server using the WebLogic Server Administration Console
Display the WebLogic Server Administration Console in one of two ways:
On the Overview page in Fusion Middleware Control, expand the WebLogic Domain node and select the bifoundation_domain. Locate the WebLogic Server Administration Console link in the Summary region.
Enter the following URL into a Web browser:
http://<host>:<port>/console
where host is the DNS name or IP address of the DAC Server and port is the listen port on which the WebLogic Server is listening for requests. The default port is 7001.
Enter the system administrator user name and password and click Login.
This system wide administration user name and password was specified during the installation process, and you can use it to log in to WebLogic Server Administration Console and Fusion Middleware Control. Alternatively, enter a user name that belongs to one of the following security groups:
Administrators
Operators
Deployers
Monitors
Click on the Deployments link in the navigation tree on the left to display the list of deployed applications.
Select the DACServer application.
The DAC Server status is displayed.