9 Managing the Control Center and Repository

Users must manage Oracle Warehouse Builder Control Center service and repository. You can use the utilities in the OWB_HOME/owb/rtp/sql directory by accessing them at the command prompt. This section describes the available resources and tools, and the necessary management tasks.

This section contains the following topics:

Overview of Oracle Warehouse Builder Control Center and Repository Management

Oracle Warehouse Builder Control Center, Control Center service, and Oracle Warehouse Builder repository occasionally require administration tasks that go beyond Oracle Warehouse Builder Control Center Manager GUI.

These tasks can be divided into the following groups:

Details about each group of tasks and the associated commands are provided in the following sections.

See also:

Starting and Stopping the Control Center Service

A group of commands starts and stops the control center service, displays its current availability, and troubleshoots a range of issues.

This group includes the following commands:

  • "service_doctor" displays a variety of Oracle Warehouse Builder repository and Control Center service diagnostics.

  • "show_service" displays the status of the Control Center service as either Available or Not Available.

  • "start_service" starts a Control Center service.

  • "stop_service" stops a Control Center service.

Displaying and Managing Control Center Configuration

Another group of commands displays and modifies the properties of the Control Center, thereby modifying its behavior.

This group includes the following commands:

Managing Control Center Deployment and Execution Jobs

The third group of commands overrides default handling of an Oracle Warehouse Builder deployment or execution requests by the Control Center, deactivates or aborts a job, or expedites handling of a specific request.

This group includes the following commands:

Oracle Warehouse Builder Repository and Location Administration Utilities

Oracle Warehouse Builder repository and locations sometimes require direct manipulation. Also, run-time audit data accumulates in Oracle Warehouse Builder repository, and some users may want to purge historical run-time audit data, to improve performance.

This fourth group includes the following commands:

Oracle Warehouse Builder Administrative Utilities

The following information is an alphabetical list of Oracle Warehouse Builder administrative utilities.

abort_exec_request

The abort_exec_request.sql script terminates a run that is currently busy.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/abort_exec_request.sql auditId workspace;

  • For an auditId that represents a running job, this script aborts the job cleanly. The auditId may be obtained by running the list_requests.sql script; see "list_requests".

  • The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

abort_unit_request

The abort_unit_request.sql script terminates a deployment job at the unit level. A deployment unit is a collection of objects that are being deployed to the same location with the same deployment action.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/abort_unit_request.sql auditId workspace;

  • For an auditId that represents a deployment unit job, this script aborts the job cleanly. The auditId may be obtained by running the list_requests.sql script; see "list_requests".

  • The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

deactivate_all

The deactivate_all.sql script deactivates all incomplete deployment and execution jobs.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/deactivate_all.sql workspace;

  • The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

deactivate_deployment

The deactivate_deployment.sql script deactivates a deployment job.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/deactivate_deployment.sql auditId workspace;

  • For an auditId that represents a deployment job, this script deactivates the job and all its unit jobs cleanly. The auditId may be obtained by running the list_requests.sql script; see "list_requests".

  • The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

deactivate_execution

The deactivate_execution.sql script deactivates an execution job.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/deactivate_execution.sql auditId workspace;

  • For an auditId that represents an execution job, this script deactivates the job cleanly. The auditId may be obtained by running the list_requests.sql script; see "list_requests".

  • The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

delete_warehouse_object

The delete_warehouse_object.sql script deletes a warehouse object.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/delete_warehouse_object.sql object location workspace;

  • The named object is deleted.

  • The location is where the object may be found.

  • The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

display_platform_property

The display_platform_property.sql script displays the value of a platform property. These properties control the behavior of the control center service.

Connect as workspaceOwner or workspaceUser with administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/display_platform_property.sql property;

  • The script assumes that at most one property with the given name exists. To set the property, see "set_platform_property".

expedite_exec_request

The expedite_exec_request.sql script expedites a running job by moving it to the top of the list of pending jobs.

Connect as workspaceOwner or workspaceUser with execute, deploy, and administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/expedite_exec_request.sql authId returnNumber returnCode workspace;

  • For an auditId that represents a deployment job, this script expedites the job cleanly. The auditId may be obtained by running the list_requests.sql script; see "list_requests".

  • The returnNumber and returnCode are the result values assigned to the job, and have the following values:

    • 0 = OK

    • 1 = OK_WITH_WARNINGS

    • 3 = FAILURE

  • The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

grant_error_table_privileges

The grant_error_table_privileges.sql script enables the Repository Browser to view and purge error tables. It grants SELECT and DELETE privileges on the specified table to the OWBSYS user.

Connect as the user who owns the error table. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/grant_error_table_privileges.sql tableName;

  • In this script, tableName is the name of the error table, such as TABLE1_ERR.

grant_upgrade_privileges

The grant_upgrade_privileges.sql script grants the required roles and privileges for upgrading deployment actions to a specific user.

Connect as workspaceOwner or workspaceUser with execute, deploy, and administrative privileges, such as SYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/grant_upgrade_privileges.sql userName;

  • In this script, userName is the name of the schema or user who has the new upgrade privileges.

list_requests

The list_requests.sql script lists the details of any active deployment or execution requests. A deployment is a collection of deployment units, which are collections of objects deployed by the same deployment action to the same location. Each deployment or execution is uniquely identified by an audit ID that may be used as a parameter to other SQL scripts, such as "deactivate_deployment", "deactivate_execution", and so on.

Connect as workspaceOwner or workspaceUser with execute, deploy, and administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/list_requests.sql workspace;

  • The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

print_exec_details

The print_exec_details.sql script prints the audit execution hierarchy and details about the various executions associated with an auditId to a file called exec_details.txt.

Connect as workspaceOwner or workspaceUser with execute, deploy, and administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/print_exec_details.sql auditId workspace;

  • The auditId represents an execution job about which this script print the job details. The auditId may be obtained by running the script list_requests.sql; see "list_requests".

  • The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

print_running_exec_details

The print_running_exec_details.sql script prints the audit execution hierarchy and details about the various incomplete executions that were started after the specified date associated with an auditId to a file called exec_details.txt.

Connect as workspaceOwner or workspaceUser with execute, deploy, and administrative privileges. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/print_running_exec_details.sql auditId workspace;

  • The auditId represents an execution job about which this script print the job details. The auditId may be obtained by running the script list_requests.sql; see "list_requests".

  • The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

purge_audit_template

The purge_audit_template.sql script purges either deployment audit data or execution audit data.

Connect as workspaceOwner or workspaceUser with administrative privileges. The script is of the form:

SQL> @drive:OWB_HOME/owb/rtp/sql/purge_audit_template.sql 
   workspace
   {DEPLOYMENT | EXECUTION}
   {sql_predicate | ALL | DATE_RANGE} [start_date end_date];

  • The workspace is the workspace where the job is running, and should be declared as workspaceOwner.workspaceName. If using only the workspaceName, workspaceOwner defaults to current user.

  • The audit_type is one of:

    • DEPLOYMENT for deployment audit data; see columns in ALL_RT_AUDIT_DEPLOYMENTS view

    • EXECUTION for execution audit data; see columns in ALL_RT_AUDIT_EXECUTIONS view

  • The condition can be one of:

    • sql_predicate, such as "number_script_run_errors > 0"; this must follow with SQL syntax rules

    • ALL, for purging all deployment or execution data

    • DATE_RANGE, for purging data within a specified data range

  • If DATE_RANGE is used, start_date is the start of date range (such as TO_DATE('01-Jan-08') and end_date is the end of the date range (such as SYSDATE).

There are several ways to invoke this script, as demonstrated in these examples:

SQL> @drive:OWB_HOME/owb/rtp/sqlpurge_audit_template.sql 
     workspace DEPLOYMENT ALL null null

SQL> @drive:OWB_HOME/owb/rtp/sqlpurge_audit_template.sql
     workspace EXECUTION "number_task_errors > 0" null null

SQL> @drive:OWB_HOME/owb/rtp/sqlpurge_audit_template.sql
     workspace EXECUTION DATE_RANGE TO_DATE('01-Jan-2008') SYSDATE

reset_repository

The reset_repository.sql script resets the registration details for an Oracle Warehouse Builder repository. It also stores the specified password as the password for the repository; see "set_repository_password". After the registration details are reset, you must register the passwords for all previously registered locations.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/reset_repository.sql password

rtrepos_report

The rtrepos_report.sql script displays the details of the Control Center and its registered locations. It provides information before using the location_util.bat script to alter some stored credentials.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/rtrepos_report.sql

service_doctor

The service_doctor.sql script displays diagnostics about Oracle Warehouse Builder repository and its Control Center Service.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/service_doctor.sql

set_ccs_home

The set_ccs_home.sql script sets the properties associated with a Control Center Service to recognize the location of the where to pick up the Instance Number, Version and Server Side Home. These parameters are not included in an Oracle Warehouse Builder Installation when the "Allow Local Control Center Service" option is set.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/set_ccs_home.sql instance version server_home

  • The instance is the service node instance that you are updating

  • The version is Oracle Warehouse Builder version that you are using

  • The server_home is Oracle Warehouse Builder Home on the server computer, where Oracle Warehouse Builder install components are located.

set_platform_property

The set_platform_property.sql script sets the value of a platform property. These properties controls the behavior of the Control Center Service.

The script assumes that the specified property must exist and its value is mutable.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/set_platform_property.sql 
     property property_value

  • The property is the name of the property that is changed

  • The property_value is the name of the new value of the property

  • To return a list of the properties, execute the following query:

    select property_path from wb_rt_platform_properties where is_mutable <> 0

set_platform_property_from_file

The set_platform_property_from_file.sql script sets the value of a platform property to the contents of the file_name found in the directory. These properties controls the behavior of the Control Center Service.

The script assumes that the specified property must exist and its value is mutable.

Connect as Control Center owner. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/set_platform_property_from_file.sql property directory file_name

  • The property is the name of the property that is changed.

  • The directory is the path where the new value for the property can be found.

  • The file_name is the name of the file that contains the new value of the property.

set_repository_password

The set_repository_password.sql script sets the password for Oracle Warehouse Builder repository. This password is used by the Control Center Service at startup time.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/set_repository_password.sql password

  • The password is the new password value.

show_service

The show_service.sql script displays the status of the Control Center Service, which provides access to the deployment and execution features of an Oracle Warehouse Builder repository. The valid return values for the status of the Control Service Center are Available or Not Available. To change the status of the Control Center Service, use the SQL scripts start_service and stop_service as appropriate.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/show_service.sql

sqlplus_exec_background_template

The sqlplus_exec_background_template.sql script runs the specified object in the background, not blocking the caller. To get the task status and return result, check public views, of the type ALL_RT_*. Use the script "sqlplus_exec_template" to run the object in the foreground, which blocks the caller until the completion of the task. In its unchanged form the script takes the three keys required to identify the executable task. The task runs in the background with the default parameters configured before deployment. The custom_params (defined on the object in Oracle Warehouse Builder Designer) and system_params values override the default input parameters of the task. The script returns the following values: 1 if task reports SUCCESS, 2 if WARNING, 3 if ERROR.

Connect as workspaceOwner or workspaceUser with execute privilege. The script is of the form:

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
   workspace
   location_name 
   {PLSQLMAP | SQLLOADERCONTROLFILE | PROCESSFLOW | ABAPFILE |
      DATAAUDITOR | SCHEDULEDJOB} 
   [parent/]task_name
   system_params
   custom_params

  • The workspace is nominated in the parameters, and should be declared as workspaceOwner.workspaceName; if using only workspaceName, workspaceOwner defaults to user.

  • The location_name is the physical name of the location to which the task is deployed.

  • The task_type has the following options:

    • PLSQLMAP is used for Oracle Warehouse Builder PL/SQL mapping.

    • SQLLOADERCONTROLFILE is used for Oracle Warehouse Builder SQL*Loader mapping.

    • PROCESSFLOW is used for Oracle Warehouse Builder ProcessFlow.

    • ABAPFILE is used for Oracle Warehouse Builder SAP mapping.

    • DATAAUDITOR is used for Oracle Warehouse Builder DataAuditor mapping.

    • SCHEDULEDJOB is used for an Oracle Warehouse Builder scheduled job.

    • CTMAPPING is used for Oracle Warehouse Builder Code Template mapping.

  • The task_name is the physical name of the deployed object; it can be optionally named of the deployed parent, like the ProcessFlow package name. A module name cannot be used because it is not a deployable object.

  • The custom_params (defined on the object in Oracle Warehouse Builder Designer) and system_params values override the default input parameters of the task

If you encounter problems accessing public views, then connect to SQL*Plus using your repository user or owner credentials, and issue:

Set role OWB_USER

Run @start_access_workspace.sql workspaceName workspaceOwner. You can then access the public views.

There are several ways to invoke this script; note that system_params and custom_params are not used in these examples:

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
     workspace warehouse PLSQL mapping "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
     workspace platform_schema SQL_LOADER load "," ","


SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
     workspace workflow PROCESS process_1 "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
     workspace platform_schema SAP sap_1 "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_background_template.sql 
     workspace warehouse DATA_AUDITOR data_auditor_1 "," ","

Note: The comma character can be escaped using the backslash character (\,); likewise the backslash character can be escaped by using two backlash characters (\\). A single-quote character must be replaced by a sequence of four single-quote characters ('''').

sqlplus_exec_template

The sqlplus_exec_template.sql script runs the specified object and blocks the caller until the task is completed. To get the task status and return result, check public views of the type ALL_RT_*. Use the script "sqlplus_exec_background_template" to run the object in the background, without blocking the caller throughout the completion of the task. The script takes the three keys required to identify the executable task. The task runs with the default parameters configured before deployment. The custom_params (defined on the object in the Oracle Warehouse Builder Designer) and system_params values override the default input parameters of the task. The script returns the following values: 1 if task reports SUCCESS, 2 if WARNING, 3 if ERROR.

Connect as workspaceOwner or workspaceUser with execute privilege. The script is of the form:

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
   workspace
   location_name 
   {PLSQLMAP | SQLLOADERCONTROLFILE | PROCESSFLOW | ABAPFILE |
      DATAAUDITOR | SCHEDULEDJOB} 
   [parent/]task_name 
   system_params 
   custom_params

  • The workspace is nominated in the parameters, and should be declared as workspaceOwner.workspaceName; if using only workspaceName, workspaceOwner defaults to user.

  • The location_name is the physical name of the location to which the task is deployed.

  • The task_type has the following options:

    • PLSQLMAP is used for Oracle Warehouse Builder PL/SQL mapping.

    • SQLLOADERCONTROLFILE is used for Oracle Warehouse Builder SQL*Loader mapping.

    • PROCESSFLOW is used for Oracle Warehouse Builder Process Flow.

    • ABAPFILE is used for Oracle Warehouse Builder SAP mapping.

    • DATAAUDITOR is used for Oracle Warehouse Builder Data Auditor mapping.

    • SCHEDULEDJOB is used for an Oracle Warehouse Builder scheduled job.

    • CTMAPPING is used for Oracle Warehouse Builder Code Template mapping.

  • The task_name is the physical name of the deployed object; it can be optionally qualified named of the deployed parent, like the ProcessFlow package name. A module name cannot be used because it is not a deployable object.

  • The custom_params (defined on the object in Oracle Warehouse Builder Designer) and system_params values override the default input parameters of the task

There are several ways to invoke this script; note that system_params and custom_params are not used in these examples:

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
     workspace warehouse PLSQL mapping "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
     workspace platform_schema SQL_LOADER load "," ","


SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
     workspace workflow PROCESS process_1 "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
     workspace platform_schema SAP sap_1 "," ","

SQL> @drive:OWB_HOME/owb/rtp/sql/sqlplus_exec_template.sql 
     workspace warehouse DATA_AUDITOR data_auditor_1 "," ","

Note: The comma character can be escaped using the backslash character (\,); likewise the backslash character can be escaped by using two backlash characters (\\). A single-quote character must be replaced by a sequence of four single-quote characters ('''').

start_service

The script start_service.sql starts a Control Center Service that is managed by the database. A Control Center Service, when available, provides access to the deployment and execution features of an Oracle Warehouse Builder repository. To determine if the Control Center Service is available, use the show_service script; the return values are either Available or Not Available. To stop the service, use the stop_service script. When the service is invoked using this script, a monitoring job is created; it restarts the service if it fails, or if the database is brought back online. Alternatively, a Control Center Service can be started in a different OWB_HOME, on another host; the service is available only when that host is available and provided the service has been started and not stopped.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/start_service.sql

stop_service

The script stop_service.sql stops a Control Center Service that is managed by the database. A Control Center Service, when available, provides access to the deployment and execution features of an Oracle Warehouse Builder repository. To determine if the Control Center Service is available, use the show_service script; the return values are either Available or Not Available. To start the service, use the start_service script. When the service is stopped using this script, the monitoring job is removed; the service remains disabled until it is re-started by the start_service script.

Connect as repository owner, OWBSYS. Run the script:

SQL> @drive:OWB_HOME/owb/rtp/sql/stop_service.sql

UpdateControlCenter

This SQL script sets the host:port:service parameters for a control center in the Oracle Warehouse Builder Client Repository. The connection type must be host:port:service.

UpdateLocation

This SQL script sets the host:port:service parameters for a location in Oracle Warehouse Builder Client Repository. The connection type must be host:port:service. The location may be registered or unregistered.

Overview of Oracle Warehouse Builder Repository Browser

The Repository Browser is a browser-based tool that generates reports from data stored in Oracle Warehouse Builder repositories. When you install Oracle Warehouse Builder from Oracle Universal Installer, the Repository Browser is also installed and runs in all the languages that you select during installation.

Starting the Repository Browser

The Repository Browser can be started if the Repository Browser Listener is in a running mode. You can start the Repository Browser in any of the following ways:

  • On a Windows platform, from the Start menu, select Programs. Select the Warehouse Builder folder, and then Warehouse Builder Repository Browser.

  • From the Tools menu of the Design Center, select Repository Browser.

  • In the web browser, type the location of the Repository Connection page. For example, if the Repository Browser Listener is running on a computer named owbserver, then type the following address to start the Repository Browser.

    https://owbserver/:8999/owbb_RABLogin.uix?mode=runtime
OR
https://owbserver/:8999/owbb_RABLogin.uix?mode=design

    It starts the Repository Browser.

    After the Repository Browser is started, the browser opens the Repository connection page that helps us to log in to the Repository Browser.

How to Start the Repository Browser Listener

Before you can open the Repository Browser, you must start the Repository Browser Listener.

Starting the Repository Browser Listener in Windows

Open a command prompt window and run startOwbbInst.bat located in the OWB_HOME\owb\bin\win32 directory. You are prompted to set the password for the OC4J administrator. Enter a password and press the Enter key. When you are prompted to confirm the password, enter the same password again.

Starting the Repository Browser Listener in UNIX/LINUX

Run ./startOwbbInst.sh located in the OWB_HOME/owb/bin/unix directory. You are prompted to set the password for the OC4J administrator. Enter a password and press the Enter key. When you are prompted to confirm the password, enter the same password again.

How to Stop the Repository Browser Listener

To stop the Repository Browser Listener, do the following.

Stopping the Repository Browser Listener in Windows

Open a command prompt window and run stopOwbbInst.bat located in the OWB_HOME\owb\bin\win32 directory. You are prompted to provide the OC4J administrator password that you set while starting the Repository Browser Listener. Type the password and press the Enter key.

Stopping the Repository Browser Listener in UNIX and Linux

Run ./stopOWBBInst.sh located in the OWB_HOME/owb/bin/unix directory. You are prompted to provide the OC4J administrator password that you set while starting the Repository Browser Listener. Type the password and press the Enter key.