Skip navigation.

eDocs Home > BEA WebLogic Portal 8.1 Documentation > Database Administration Guide > Using an Oracle Database

Database Administration Guide

  Previous Next vertical dots separating previous/next from contents/index/pdf Contents View as PDF   Get Adobe Reader

Using an Oracle Database

This section describes the steps necessary to use an Oracle database with WebLogic Portal 8.1, and includes information on the following subjects:

Review this entire chapter and any release notes before proceeding. Typically, the steps described in this chapter should be performed by an Oracle system administrator or a database administrator.

Notes:

 

Configuring an Oracle Database

Before proceeding, be sure that you have read Overview of Database Configuration for WebLogic Portal.

Note the following considerations when defining your Oracle instance and databases:

  1. Install the Oracle client software on the WebLogic Platform host.

Note: If you plan to use the Configuration Wizard to create the database objects for a new domain, you do not need to install the Oracle Client.

    1. Configure a Local Net Service to access the target Oracle instance.
    2. Be sure that Oracle environment variables are defined, and that the Oracle bin directory is included in the $PATH variable.
    3. Verify that you can connect to the target Oracle database schema using SQLPlus.
  1. Prepare the Oracle database and schema. The database creation scripts install domain-specific tables for each. It is recommended that you work with a database administrator to adjust the SAMPLE scripts, and to create the database schema owner users and tablespaces needed for your environment.

Notes: Multiple database schemas are required if you have multiple domains, or to run multiple environments using the same Oracle instance (for example, if you want to run development and system test from a single Oracle installation).

Be sure to back up your database before installing any new database objects. See your database documentation for details.

    1. Edit the sample scripts provided in: WL_HOME/portal/db/oracle/817/admin to suit your environment.

      2. The database creation scripts install domain-specific tables for each. It is recommended that you work with a database administrator to adjust the sample scripts, and to create the database schema owner users and tablespaces needed for your environment.

    2. Review the Description and Usage Notes for each script.

      Script Name

      Description

      create_tablespaces.sql

      Creates data and index tablespaces.

      Usage Notes: Edits are required to modify the pathnames for the DATA_PATHNAME and INDEX_PATHNAME variables to match your local directory path structures. For example, on a UNIX system, if two disks are mounted as /usr1 and /usr2 and the Oracle SID is PROD, use the following pathnames:

      DEFINE DATA_PATHNAME=/usr1/oradata/PROD

      DEFINE INDEX_PATHNAME=/usr2/oradata/PROD

      Edits are also required if you want to change the tablespace names. The following defaults are used:

      • WEBLOGIC_DATA: tables for WebLogic Portal and/or WebLogic Platform

      • WEBLOGIC_INDEX: indexes for WebLogic Portal and/or WebLogic Platform

      create_users.sql

      Creates a WEBLOGIC schema owner user, establishes the users password, default and temporary tablespaces and grants privileges to that user.

      Usage Notes: Edits are required to change the schema owner user name, password and tablespace names. The following defaults are used:

      • database user = WEBLOGIC

      • database password = WEBLOIGIC

      • default tablespace = WEBLOGIC_DATA

      • temporary tablespace = TEMP

      rebuild_indexes.sql

      Rebuilds WEBLOGIC (schema user) indexes to move them from the WEBLOGIC_DATA tablespace to the WEBLOGIC_INDEX tablespace.

      statistics.sql

      Runs analyze_schema to compute database statistics needed for the Oracle optimizer. Run analyze_schema whenever any significant changes in database data occur. Your database administrator typically schedules analyze_schema to run periodically in your environment.

      install_report.sql

      Builds an informational installation report about the database objects created in the schema.

      db_size.sql

      Builds a report showing free space in database tablespaces.

      bt_create_tablespaces.sql

      Creates the tablespace for behavior event tracking.

      Usage Notes: Edits are required to modify the pathnames for the EVT_DATA_PATHNAME and INDEX_PATHNAME variables to match your local directory path structures.

      • WEBLOGIC_DATA: tables for WebLogic Portal and/or WebLogic Platform

      • WEBLOGIC_INDEX: indexes for WebLogic Portal and/or WebLogic Platform

      bt_create_users.sql

      Creates a behavior event tracking user; establishes the user's password, and default and temporary tablespaces; and grants privileges to that user.

      Usage Notes: Edits are required to change the schema owner user name, password and tablespace names. Edits are required to change file sizes and device names.

      The following defaults are used:

      • database user: WEBLOGIC_EVENT

      • password: WEBLOGIC_EVENT

    3. To run these scripts from a shell, change directories to:

      4. WL_HOME/portal/db/oracle/817/admin

    4. Start SQL*Plus as the system user. For example:

      5. sqlplus system/manager@MYDB

    5. From SQL*Plus, execute the create_tablespaces.sql script. using the @ sign. For example:

      6. @create_tablespaces.sql

    6. From SQL*Plus, execute the create_users.sql script using the @ sign. For example,

      7. @create_users.sql

  1. Follow the steps in Manually Creating Database Objects.

 

Manually Creating Database Objects

You can either manually create database objects or use the Configuration Wizard; for more information, see Overview of Database Configuration for WebLogic Portal.

Note: If you choose to use the WebLogic Configuration Wizard to configure and connect to the database that you will use to support WebLogic Portal, see http://download.oracle.com/docs/cd/E13196_01/platform/docs81/confgwiz/index.html.

To manually create WebLogic Portal database objects, use the following steps:

  1. Use the following command to verify that you can connect to the target database server with a valid user ID and password:
    2. sqlplus user_ID/password@DB_SID
  2. Open your domain's db_settings.properties file for edit, and comment out the database settings for PointBase.
  3. Uncomment the database settings for Oracle and update the following settings for your database:
    • server=
    • dblogin=
    • password=
  4. Initialize the database with the new settings.
    1. For Windows, navigate to the BEA_HOME\user_projects\domains\portalDomain directory, and double-click the create_db.cmd file.
    2. For UNIX, navigate to the BEA_HOME\user_projects\domains\portalDomain directory and run create_db.sh.
    3. Verify the results in the create_db.log file.

Note: If you are using the sample domain, run the create_db.cmd/sh file from the following directory: WL_HOME\samples\domains\portal.

  1. Follow the steps in Manually Configuring Your Domain's JDBC Driver Settings.

 

Manually Configuring Your Domain's JDBC Driver Settings

You can either manually configure your domain's JDBC driver settings using the WebLogic Server Console, or use the Configuration Wizard; for more information, see Overview of Database Configuration for WebLogic Portal.

To manually configure your JDBC driver settings using WebLogic Server Console:

  1. Start the WebLogic Server for your domain.
  2. Login to the WebLogic Server Console.
  3. Configure your new connection pools.
    1. Go to Services —> JDBC —> Connection Pools.
    2. Click Configure a New Connection Pool.
    3. Select the appropriate database type and non-XA database driver from the drop-down lists and click Continue. For more information, see the Supported Configuration documentation for JDBC drivers supported by WebLogic Portal located at http://download.oracle.com/docs/cd/E13196_01/platform/docs81/support/supp_plat.html.

      4. For an XA configuration, see "Creating XA Domains Using Configuration Templates" in the "Creating WebLogic Configurations Using the Configuration Wizard documentation located at http://download.oracle.com/docs/cd/E13196_01/platform/docs81/confgwiz/index.html.

    4. Choose a name for the new connection pool (for example, cgPoolN) and fill in the blanks for your vendor database. Click Continue.
    5. Test your connection to verify that you can successfully connect to your database.
    6. Create and deploy your new connection pool.

      7. Note: You must maintain a one-to-one mapping of JDBCTxDataSource to JDBC connection pool in the domain's config.xml file. Create one new JDBC connection pool for each JDBCTxDataSource and another JDBC connection pool for the domain's JDBCDataSources.

  4. Update your data sources.
    1. From Services —> JDBC —> Data Sources, click each data source and switch each to the newly created connection pool. Make sure that you apply each change.
    2. Verify that each data source is changed by clicking on Data Sources and then verifying that Pool Name has been set to the new connection pool for each.
  5. From Services —> JMS —> Stores —> cgJMSStore, switch cgJMSStore to use the new connection pool.
  6. Stop your domain's WebLogic Server, then restart it.
  7. In the WebLogic Server Console, delete the original connection pools.
    1. Go to Services —> JDBC —> Connection Pools.
    2. Right-click each connection pool and select Delete.
  8. This step is recommended for improved performance. Move indexes to the WEBLOGIC_INDEX tablespace by executing rebuild_indexes.sql from SQLPLUS. Do this while WebLogic Server is not running.

 

Creating a Separate Database for Behavior Tracking Events

For improved performance, you might want to store behavior tracking events in a different location from other WebLogic Portal database objects. For more information about behavior tracking, see http://download.oracle.com/docs/cd/E13218_01/wlp/docs81/adminportal/help/SA_BehavTrackServ.html.

Note: By default, behavior tracking database objects are created in the same database as other WebLogic Portal database objects. You need to perform these steps only if you are configuring a separate database for behavior tracking events.

  1. Edit the bt_create_tablespaces.sql file and the bt_create_users.sql file for your environment, as indicated in the instructions contained in the files. .
  2. From SQL Plus, run the bt_create_tablespaces.sql script.
  3. From SQL Plus, run the bt_create_users.sql script.
  4. Navigate to the appropriate database directory based on your environment: WL_HOME\portal\db\oracle\817
  5. Connect as the user WEBLOGIC_EVENT and run the following scripts:
    • bt_create_tables.sql
    • bt_create_fkeys.sql
    • bt_create_indexes.sql
    • bt_create_views.sql
    • bt_create_triggers.sql
  6. Run the following script from the path WL_HOME\portal\db\data\required:
    • bt_insert_system_data.sql
  7. Configure a connection pool to access your behavior tracking database and associate the p13n_tracking JDBC data source with that connection pool. Follow the steps in Manually Configuring Your Domain's JDBC Driver Settings.

 

WebLogic Platform Support for Oracle9i RAC

WebLogic Platform 8.1 SP4 now provides a patch that enables WebLogic Platform 8.1 SP4 support for Oracle9i Real Application Clusters (RAC). For more information about this patch, see the WebLogic Platform Release Notes at the following URL:

http://download.oracle.com/docs/cd/E13196_01/platform/docs81/relnotes/relnotes.html

 

Oracle 10g R2 Database Requires Manual Privilege Setup

As described in Oracle Note 317258.1, Oracle changed their use of predefined database roles in the 10g R2 version to provide more security. The CONNECT role now provides only CREATE SESSION privileges; the other associated privileges have been removed.

You must now explicitly grant the CREATE VIEW privilege for Oracle 10g R2 databases.

 

Skip navigation bar  Back to Top Previous Next