Configuring and tuning Oracle

Once the database is running, the SQL*Plus tool can be used to connect to the database.

Go to the Start -> Programs -> Oracle - OraHomeX0 -> Application Development program group and run the SQL Plus application. Once SQL Plus is running, you will be prompted for 3 things: Username, password and Host String.

The default Database Administrator's account has a username of SYSTEM and a default password of MANAGER. You may have changed the SYSTEM account password in a previous step so use the new password.

Fill in the Username and password fields with the SYSTEM account and leave the Host String field blank. Since we are connecting to a local Oracle database, no Host String is required. Click on the OK button as shown below:

If the database is running and the username and password are typed correctly, SQL*Plus should log the SYSTEM user in and present the SQL> prompt as shown below:

Configuring Oracle Server

Web Application Framework needs a valid Oracle username and password during an Oracle installation in order to install and run properly.

The user should have full privileges on its schema, which can be spread across one or more tablespaces. In addition, make sure your version of Oracle is configured as follows:

• UTF-8 character set
• Minimum 8KB db blocksize
• Intermedia, if you need full-text search capabilities

Configuring Oracle Client

Although you can install the WAF with the Oracle Thin driver, there are some known issues with the way Oracle's thin driver handles LOBs. For configuring and tuning Oracle, it is highly recommended to use the OCI driver instead. Most likely, the Oracle JDBC driver will be already in place at:

This is also the default value offered by the installation program when configuring the location of the Oracle JDBC driver, assuming that at least the Oracle client library is available and that ORACLE_HOME is set accordingly.

If this file does not exist, you must install it. The OCI driver depends on native Oracle client libraries which must be properly set via LD_LIBRARY_PATH.

Make sure that the user running the servlet conatiner has read access to the Oracle JDBC driver and client libraries. To get the Thin JDBC driver to work, the target Oracle machine (for example, the one you are connecting to) must have its listener enabled for TCP connections, even if the WAF and Oracle installations are on the same host.

If the OCI driver is an option (see above), you could use the OCI driver to eliminate the need for the TCP listener.

A number of the commands for installation and day-to-day development assume that the Oracle environment has been setup for all users on the machine. This can be trivially accomplished by creating the file /etc/profile.d/Oracle.sh as follows (you will need to correct the paths to match your local installation):

Enable version control, if required

During configuring and tuning Oracle, if you require version control of repository objects, you must enable this functionality by opening the Repository Administration Utility and selecting Options -> Enable Version Support. Caution: You cannot undo this operation.

Before enabling the version control features, ensure this is really necessary for this repository and its data. If in doubt, defer this operation; you can perform it at any time.

Create a test workarea and container

I recommend the following:

• Create a test workarea and container to make sure that you can create element definitions in the repository (but note that, if you have not enabled versioning, you will not be able to create a workarea additional to the default one).
• check whether you can use the Repository Object Navigator to create objects in the repository.

Create subordinate users

If other users at your site are to have access to the repository, you will need to create subordinate users. This is the term given to repository users other than the repository owner.

Any usernames you want to use for subordinate users must already have been created as Oracle usernames via a CREATE USER statement in SQL*Plus or the Enterprise Manager.

See the Oracle Repository online help for details on configuring and tuning Oracle about how to grant repository access to an Oracle user, thus creating a subordinate user. Subordinate users must be assigned the CONNECT and RESOURCE roles in SQL*Plus or the Enterprise Manager.

To do so, connect as SYS and issue the following:

grant connect, resource to subordinate_user;

Grant privileges to users

The following table is a checklist of the system privileges that are required by a repository owner and subordinate users, together with the operation or process with which they are principally associated.

Diagnostics privileges are required if, for example, you wish to enable SQL TRACE.

Registration privileges are required for registration of Oracle schemas in the repository. See the online help for Oracle Repository.

Reconcile user privileges allow subordinate users to be enabled or disabled (synonyms created or dropped) via the Reconcile button on the Maintain Users dialog box of the Repository Administration Utility.

Set up the broadcast server

The broadcast server enables repository users to be notified immediately of changes made to repository objects in the current container.

Users can use this feature locally (changes made in one tool are immediately visible in other tools) or over the network (same as locally, and in addition changes made by one user are immediately visible to other users), or they can disable it.

The following table shows what you need to do, depending on how the feature is to be used.

Return to installing the Oracle database