【oracle教程

The process of deploying is where database objects are actually created and PL/ SQL code is actually loaded and compiled in the target database. Up until this point, no objects exist in our target schema, acme_dwh, in our Oracle database.

The process of deploying is where database objects are actually created and PL/ SQL code is actually loaded and compiled in the target database. Up until this point, no objects exist in our target schema, acme_dwh, in our Oracle database. Everything we’ve talked about so far about importing metadata for tables, defining objects, mappings, and so on has referred totally to the Warehouse Builder repository, where it keeps a record of everything we’ve defined so far in metadata. Not a single actual database object has been created yet. Everything we’ve done until now has been done entirely in the OWB Design Center client. But to perform the process of deployment, now we’re going to have to communicate to the target database. For that we need to be introduced to the Control Center Service, which must be running for the deployments to function.

The Control Center Service

If we briefly look all the way back at topic 1, we talked about the architecture of the Warehouse Builder and looked at a diagram that laid out the main components of the Warehouse Builder software and where they were located—either on the client or on the server. The Control Center Service is a process that runs on the server and provides the interface to our target database for controlling the deployment process. It is also possible to run the Control Center Service on another remote computer to implement a remote runtime. If it’s running in this configuration, it doesn’t start automatically by default. So we would need to manually start it. It is available from the Windows Start menu as shown next:

We will not need to run it because we’re running locally on the same machine as the database is running, and will be interfacing with the Control Center Service that is running locally in the database. If we were to implement a remote runtime and had to run this Start Control Center Service menu entry, it would start up a command window with the window title Start Control Center Service and would pop-up a dialog box asking us for connection information for the OWBSYS schema in our database. We would enter the password, host name, and service name for connecting to that schema.

The local Control Center Manager on the database server is controlled using scripts, which are run in the database while connected as the OWBSYS user. The scripts are located in the ORACLE_HOME\owb\rtp\sql\ folder. They can be run using the SQL*Plus command-line utility for executing SQL commands and scripts. Open a command-prompt window and enter the following command to run it and connect to the OWBSYS schema:

Substitute your actual oracle_home location in the previous command. A few of the other scripts available in the previous folder are as follows:

? start_service.sql: Starts the Control Center Service

? stop_service.sql: Stops the Control Center Service

? service_doctor.sql: Analyzes the state of the service and reports the status

The Control Center Service normally starts when the database starts up. So if we are running the database server locally, we don’t need to bother with running any of the scripts. However, it is good to be informed should there be any problem in the future involving connections to the service. Let’s give the Control Center Service some work to do now by doing an actual deployment from the Design Center.

Deploying in the Design Center and Data Object Editor

Enter the password for OWBSYS when prompted, and then enter the following command at the SQL*Plus command prompt to display the status of the service:

As with validation and generation, we can deploy objects and mappings from the Design Center. Let’s deploy our pos_trans_stage table from the Design Center. We’ll right-click on it and select Deploy from the pop-up menu. If the Control Center Service is not running for some reason, we’ll be presented with an error dialog box as shown next:

If we get this pop-up window when we click on the OK button, we’ll get another pop-up window prompting for connection information for the Control Center Service where we can provide connection information. That dialog box looks like the following:

The only items we can modify here are the User Name and Password to use. These items default to the repository workspace owner that we’ve been using all along to connect in the Design Center, and we should not change them. Mostly, this dialog box appears because the Control Center Service is not running, and not because of incorrect connection information. Let’s take a quick look at where that connection information is specified. We’ll press the Cancel button in the dialog box to close it if it appears.

The Control Center Service connection information is set in the Design Center, which is in the Locations Navigator window under the Control Centers entry. If we expand it, we can see that a default Control Center Service was created for us called DEFAULT_CONTROL_CENTER. We did not have to create it separately. If we double-click on this, we get a dialog box that shows the connection information. However, we’re not able to edit anything in it. When this entry was created it was specified, and it can’t be changed. Therefore, it is unlikely that incorrect connection information caused the Control Center Service error dialog box to appear. We could have more than one control center defined. But the default will work fine for us, so we will not modify it.

If our Control Center Service is running, which will usually be the case, we won’t get the previous dialog boxes. However, we may get a dialog box telling us that our location is not registered.

The act of registering a location is to associate all the previous information with a location defined in the Warehouse Builder so that the Control Center Service knows how to find the location. The connection details in the dialog box are what it uses to connect to the location. Simply provide the password for the target schema and ensure that the rest of the information is correct, and then click on OK. The location will be registered and the object will deploy. We can register and un-register locations at any time by using the Control Center Manager. We’ll be looking at the Control Center Manager very soon.

So, we have deployed our pos_trans_stage table in the Design Center. Assuming the Control Center Service is running and we don’t get any of the previous dialog boxes, we will actually not get any dialog box when we deploy an object, unless we have set a certain option to tell the Warehouse Builder to display a dialog box upon completion of the deployment. If this option is not set (which is the default), it will not tell us whether the deployment succeeded or failed. If we want to see a completion message, we have to set this option in the preferences for the Design Center and tell it to show this message to us when the deployment is completed. We can set this option under the Tools menu entry by selecting the Preferences… menu entry. The resulting dialog box will look similar to the following, which has been scrolled down so that the needed entry is visible and the column has been expanded so that the whole description is visible. We need to check the box beside the option for displaying the completion status as follows:

It will show us the completion message after this option has been checked, which will look similar to the following:

This shows us that the deployment processed successfully with no errors or warnings. But what if the count of errors or warnings was not zero? There would be nothing but a count that would display with this dialog box, so we need a feature to see what these warnings and error messages are. If we look at the Log window, we’ll see that it has created a tab with some results of our deployment. The tab name will be named for the object we’re deploying and it will look very similar to the results we viewed previously for validating and generating but with the addition of a new entry for deployment results. It will look similar to the following:

The number you will see beside the object name in your log window will most likely be different since that’s a randomly assigned ID number the warehouse builder gives to deployment jobs. We’ll see that in a moment. There must be some way to give us more control over the deployment process as the Design Center only shows us design information and these messages. This feature of the Warehouse Builder is the Control Center Manager.

The Control Center Manager

The Control Center Manager is the interface the Warehouse Builder provides for interacting with the target schema. This is where the deployment of objects and subsequent execution of generated code takes place. The Design Center is for manipulating metadata only on the repository. Deployment and execution take place in the target schema through the Control Center Service. The Control Center Manager is our interface into the process where we can deploy objects and mappings, check on the status of previous deployments, and execute the generated code in the target schema.

We launch the Control Center Manager from the Tools menu of the Design Center main menu. We click on the very first menu entry, which says Control Center Manager. This will open up a new window to run the Control Center Manager, which will look similar to the following:

The Control Center Manager window overview

The Control Center Manager interface is organized in a similar manner to the Design Center with multiple windows appearing in the main window. But only two windows are available: the Object Details window and the Control Center Jobs window. The subwindow on the left that displays the tree hierarchy for our project and the locations defined within it is a permanent part of the interface, and so it does not have a separate window title like the other two.

Beginning with the left subwindow, we see our project name displayed there with a list of the locations that have been defined within our project. The primary location of concern for deployment and execution will be acme_dwh_location. This is the location we have defined for our target database and selected as default.

This is where we can control the registering and un-registering of a location. If we right-click on a location, we see a Register… menu entry. When it is selected, it pops up the same dialog box as we have seen previously. If the location has not been previously registered it’s connection details will all be editable. But if the location was previously registered successfully, only the password can be set. The Unregister… menu entry will remove the connection details for the location.

All the objects defined for this location can be found by clicking on the plus sign to the left of the location name to expand the tree, and then clicking on the plus sign next to the ACME_DWH module to display for us a hierarchy of the object types that can be deployed. An example is shown next:

Clicking on the plus sign beside any of the subcategories, such as Mappings or Tables, will show us the list of the objects of that type defined within our project. If we click on an entry in the hierarchy, the Object Details window will update to display the associated objects. In the previous image of the entire Control Center Manager window, we can see that the Object Details window contains the entire set of objects defined in our project for the main location because that is what is selected in the tree view on the left. Now as we click on the subcategories, the Object Details window updates to display just the objects within that subcategory. If we further expand the tree view on the left to view the objects in a subcategory, we can click on an individual item and the Object Details window will display the details for just that item.

If this looks familiar, it is because the module view for the ACME_DWH module in the tree is very similar to the view we get in the Design Center when viewing that module. All the objects represented here can be deployed and correspond exactly to the same item in the Design Center.

The Object Details window

Let’s click on the acme_dwh_location again in the left window and look at the complete list of objects for our project. The statuses will vary depending on whether we’ve done any deployments or not, when we did them, and whether there are any warnings or failures due to errors that occurred. If you’re following along exactly with the topic, the only deployment we’ve done so far is the pos_trans_stage table and the previous image of the complete Control Center manager interface shows it as the only one that has been deployed successfully. The remainder all have a deploy status of Not Deployed.

The columns displayed in the Object Details window are as follows:

? Object: The name of the object

? Design Status: The status of the design of the object in relation to whether it has been deployed yet or not

° New: The object has been created in the Design Center, but has not been deployed yet

° Unchanged: The Object has been created in the Design Center and deployed previously, and has not been changed since its last deployment

° Changed: The Object has been created and deployed, and has subsequently undergone changes in the Design Center since its last deployment

? Deploy Action: What action will be taken upon the next deployment of this object in the Control Center Manager

° Create: Create the object; if an object with the same name already exists, this can generate an error upon deployment

° Upgrade: Upgrade the object in place, preserving data (only on objects able to be upgraded)

° Drop: Delete the object

° Replace: Delete and recreate the object; this option does not preserve data

? Deployed: Date and time of the last deployment

? Deploy Status: Results of the last deployment

° Not Deployed: The object has not been deployed yet

° Success: The last deployment was successful, without any errors or warnings

° Warning: The last deployment had warnings

° Failed: The last deployment failed due to errors

? Location: The location defined for the object, which is where it will be deployed

? Module: The module where the object is defined

Some of the previous columns will allow us to perform an action associated with the column by double-clicking or single-clicking in the column. The following is a list of the columns that have actions available, and how to access them:

? Object: Double-click on the object name to launch the appropriate editor on the object.

? Deploy Action: Click on the deploy action to change the deploy action for the next deployment of the object via a drop-down menu. The list of available actions that can be taken will be displayed. Not all the previously listed actions are available for every object. For instance, upgrade is not available for some objects and will not be an option for a mapping. The deploy action is what determines the scripts that get generated for an object. The Create option will generate only a script to create the object. The Replace option, in addition to generating the create script, will cause a drop script to be generated. The Drop option will cause only a drop script to be generated. The Upgrade option, if available, will generate neither a drop nor a create option but will generate a script with the appropriate upgrade SQL code.

An important note about the upgrade option is that the target user must have certain extra privileges granted to him or her in the database to be able to perform an upgrade. If these privileges haven’t been granted, an error will occur when trying to do a deployment with an Upgrade option: RPE-02257: The following Oracle Roles have not been Granted to the Target User: ‘SELECT_CATALOG_ROLE’ RPE-02258: The following Oracle Privileges have not been Granted to the Target User: ‘EXECUTE ANY PROCEDURE’ ‘EXECUTE ANY TYPE’ ‘SELECT ANY TABLE’ ‘SELECT ANY DICTIONARY’ RPE-02259: Please run script /owb/rtp/sq]/grant_ upgrade_privileges.sql

Simply run the script mentioned in the message as the system user to grant the privileges.

There are two buttons available in the Object Details window, Default Actions and Reset Actions. Every object created in the Design Center has a default deployment action associated with it, which is determined by the current design and deployment status. For example, a mapping that has not been deployed yet has a default status of Create. A table that was previously deployed but just changed will have a default action of Upgrade. The Default Actions button will change the displayed Deploy Action to show the default action for that object based on a comparison of its design status with its deploy status.

Now let’s click on the Default Actions button and we’ll notice that all the actions are updated to their default action. In our case, with just the pos_trans_stage table deployed and all others not deployed, all the objects except for pos_trans_stage have the Deploy Action changed to Create from None.

The other window in the Control Center Manager is the Control Center Jobs window. This is where we can monitor the status of any deployments and executions we’ve performed.