> Database Administration Guide > Using Oracle

Database Administration Guide

     Previous  Next    Open TOC in new window  Open Index in new window  View as PDF - New Window  Get Adobe Reader - New Window
Content starts here

Using Oracle

This chapter describes the steps necessary to use an Oracle database with WebLogic Portal, and includes the following sections:

Review this entire chapter and any release notes before proceeding. The tasks described in this chapter should be performed by a database administrator.

Note: A WebLogic Portal Oracle database user that has DBA privileges causes database create errors and upgrade issues. Before you create database objects or perform an upgrade, you should revoke the Oracle user's DBA privileges and replace them with the following privileges: GRANT CREATE TABLE, CREATE VIEW, CREATE TRIGGER, CREATE SEQUENCE, CREATE SESSION, UNLIMITED TABLESPACE TO <<WEBLOGIC>>.

 

Configuring Oracle Databases

Before proceeding, read Overview of Enterprise-Quality Database Configuration for WebLogic Portal.

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

Multiple database users 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). GroupSpace requires a separate database user, as do any additional content management repositories.

Note: Oracle configuration settings can impact Content Search performance; for more information, see Improving Content Search Response Time for Oracle.

To configure an Oracle database:

  1. Be sure to back up your database before installing any new database objects. See your database documentation for details.
  2. Review the provided sample scripts, located in the WL_HOME/portal/db/oracle/admin directory. See Table 6-1, Database Scripts and Usage Notes, on page 6-7 and the comments in the scripts for additional information.
  3. Copy and modify the sample scripts appropriately for your environment to create each of the following database schemas:
    1. Follow the steps in Configuring the Main WebLogic Portal Database to create the main WebLogic Portal user and database objects.
    2. If you want to use GroupSpace, follow the steps in Configuring the GroupSpace Database to create the GroupSpace user and database objects.
    3. If you want to create a separate behavior tracking database, follow the steps in Manually Creating a Separate Database and Database Objects for Behavior Tracking.

 

Configuring the Main WebLogic Portal Database

To configure the main WebLogic Portal database, follow these steps:

  1. If you want to create new tablespaces for the main WebLogic Portal schema:
    1. Copy the create_tablespaces.sql script and modify it appropriately for your environment. See Table 6-1, Database Scripts and Usage Notes, on page 6-7 and the comments in the script for additional information.
    2. Run the modified create_tablespaces.sql script. For example, from SQL*Plus:
      3. @create_tablespaces.sql
  2. Create the main WebLogic Portal database user:
    1. Copy the create_users.sql script and modify it appropriately for your environment. See Table 6-1, Database Scripts and Usage Notes, on page 6-7 and the comments in the script for additional information.
    2. Run the modified create_users.sql script. For example, from SQL*Plus:
      3. @create_users.sql

Follow the remaining steps only if you want to create database objects manually rather than using the Configuration Wizard. To perform the remaining steps using the Configuration Wizard, see Creating WebLogic Configurations Using the Configuration Wizard.

  1. Open your domain's database.properties file for edit.
  2. Set database=oracle.
  3. Update the following settings (by replacing the @ symbols and the text between the symbols with the correct values) for your main WebLogic Portal database:
    4. oracle.user=@DB_USER@
    oracle.password=@DB_PASSWORD@
    oracle.url=jdbc:bea:oracle://@DB_HOST@:@DB_PORT@;SID=@DB_NAME@
  4. Create the database objects.
    1. Navigate to the BEA_HOME\user_projects\domains\myPortalDomain directory.
    2. Enter one of the commands:
      • On Windows, double-click create_db.cmd.
      • On UNIX, run create_db.sh.

        • If any error messages are displayed, check the create_db.log file for additional information.

  5. Replace the JDBC data sources in your domain (except for appsGroupSpaceDataSource), which point to PointBase by default, with data sources that point to Oracle. You can configure them using the WebLogic Server Administration Console or choose from the samples provided and update them for your database environment. Sample jdbc.xml definition files for each database and driver that BEA supports are available in the WL_HOME\portal\db\jdbc\database_driver directory; for example, oracle_bea or oracle_thin. Follow the instructions in the WL_HOME\portal\db\jdbc\README.txt file.
  6. For improved performance, move indexes to the WEBLOGIC_INDEX tablespace by executing rebuild_indexes.sql.
    7. Note: Do this while WebLogic Server is stopped.

 

Configuring the GroupSpace Database

To configure the GroupSpace database, follow these steps:

  1. If you want to create new tablespaces for the GroupSpace schema:
    1. Copy the create_tablespaces.sql script and modify it appropriately for your environment. See Table 6-1, Database Scripts and Usage Notes, on page 6-7 and the comments in the script for additional information.
    2. Run the modified create_tablespaces.sql script. For example, from SQL*Plus:
      3. @create_tablespaces.sql
  2. Create the GroupSpace user:
    1. Copy the create_users.sql script and modify it appropriately for your environment and the GroupSpace settings. See Table 6-1, Database Scripts and Usage Notes, on page 6-7 and the comments in the script for additional information.
    2. Run the modified create_users.sql script. For example, from SQL*Plus:
      3. @create_users.sql

Follow the remaining steps only if you want to create database objects manually rather than using the Configuration Wizard. To perform the remaining steps using the Configuration Wizard, see Creating WebLogic Configurations Using the Configuration Wizard.

  1. Open your domain's groupspace_database.properties file for edit.
  2. Set database=oracle.
  3. Update the following settings (by replacing the @ symbols and the text between the symbols with the correct values) for your GroupSpace database:
    4. oracle.user=@DB_USER@
    oracle.password=@DB_PASSWORD@
    oracle.url=jdbc:bea:oracle://@DB_HOST@:@DB_PORT@;SID=@DB_NAME@
  4. Create the database objects.
    1. Navigate to the BEA_HOME\user_projects\domains\myPortalDomain directory.
    2. Enter one of the commands:
      • On Windows:
        • create_db.cmd -database.properties=groupspace_database.properties
      • On UNIX:
        • create_db.sh -database.properties=groupspace_database.properties

        If any error messages are displayed, check the create_db_groupspace.log file for additional information.

  5. Replace the appsGroupSpaceDataSource JDBC data source (which points to PointBase by default) with a data source that points to Oracle. Use the WebLogic Server Administration Console or update the sample jdbc.xml definition file provided for each database and driver that BEA supports, in the WL_HOME\portal\db\jdbc\database_driver directory; for example oracle_bea or oracle_thin. Follow the instructions in the WL_HOME\portal\db\jdbc\README.txt file.

 

Manually Creating a Separate Database and Database Objects for Behavior Tracking

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 Setting Up Events and Behavior Tracking in the Interaction Management Guide.

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.

To create a separate database for behavior tracking:

  1. Modify the bt_create_tablespaces.sql file and the bt_create_users.sql file for your environment. See Table 6-1, Database Scripts and Usage Notes, on page 6-7 and the comments in the script for additional information.
  2. Run the modified bt_create_tablespaces.sql script.
  3. Run the modified bt_create_users.sql script.
  4. Navigate to the appropriate database directory based on your environment: WL_HOME\portal\db\oracle
  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_triggers.sql
  6. Run the following scripts from the path WL_HOME\portal\db\data\required:
    • bt_insert_system_data.sql
    • bt9_insert_system_data.sql
  7. Use the WebLogic Server Administration Console to configure a non-XA JDBC data source to access your behavior tracking database. Associate the JNDI name p13n.trackingDataSource with that data source and then remove p13n.trackingDataSource from p13nDataSource.

 

Database Scripts and Usage Notes

Table 6-1 describes the scripts that enable you to configure the Oracle database and create WebLogic Portal objects in that database.

Table 6-1 Database Scripts and Usage Notes 
Script Name
Description
create_tablespaces.sql
Creates table and index tablespaces. If you want to use existing tablespaces, you do not need to run this script. You can use new or existing tablespaces for the main WebLogic Portal database, the GroupSpace database, and any additional content management repositories.

Note: Make a copy of this script and edit it to replace <<WEBLOGIC>> with the appropriate data and index tablespaces. You must also edit the script to reflect valid disk locations for the DATA and INDEX datafiles.

Typical names for the main WebLogic Portal database are:
  • Table data: WEBLOGIC_DATA
  • Indexes: WEBLOGIC_INDEX

Note: Indexes are placed in the table tablespace by default. Rebuild indexes by running rebuild_indexes.sql to move them to the index tablespace after initial database object creation.
create_users.sql
Creates a user and password, and sets default and temporary tablespaces. Grants privileges to that user.

Note: Make a copy of this script and edit it to replace <<WEBLOGIC>> with the user name, password and tablespace names.

Typical names for the main WebLogic Portal database are:
  • Database schema user = WEBLOGIC
  • Database password = WEBLOGIC
  • Default tablespace = WEBLOGIC_DATA
  • Temporary tablespace = TEMP

Note: To use GroupSpace, you must create an additional user.

Typical names for the GroupSpace repository database are:
  • Database schema user: WEBLOGIC_GROUPSPACE
  • Password: WEBLOGIC_GROUPSPACE
  • Default tablespace = WEBLOGIC_GROUPSPACE_DATA
  • Temporary tablespace = TEMP

Note: If you decide to create an additional content management repository, you must create another schema for it. For additional information, see the Content Management Guide.
rebuild_indexes.sql
Rebuilds schema user (WEBLOGIC) indexes to move them from the table (WEBLOGIC_DATA) tablespace to the (WEBLOGIC_INDEX) tablespace.
statistics.sql
Runs dbms_utility.analyze_schema to compute database statistics needed for the Oracle optimizer. Update statistics periodically and whenever any significant changes in database data occur.
install_report.sql
Builds an informational installation report about the database objects created in the schema.
dbsize.sql
Builds a report showing free space in database tablespaces.
bt_create_tablespaces.sql
Creates the tablespace for behavior event tracking.

Note: You must edit this script to modify the pathnames for the EVT_DATA_PATHNAME and DATA_FILENAME variables to reflect valid disk locations.
bt_create_users.sql
Creates a behavior event tracking schema user and password, and sets default and temporary tablespaces. Grants privileges to that user.

Note: You can edit this script to change the schema user name, password and tablespace names.

The following defaults are used:
  • Database user: WEBLOGIC_EVENT
  • Password: WEBLOGIC_EVENT
  • Default tablespace = WEBLOGIC_EVENT_DATA
  • Temporary tablespace = TEMP

 

Using WebLogic Portal with Oracle RAC

This section describes the steps necessary to use Oracle Real Application Cluster (RAC) 10g with WebLogic Portal.

Oracle Real Application Cluster is Oracle's database cluster technology. RAC offers scalability, high-availability and load-balancing at the database tier of a WebLogic Portal deployment. For more information on Oracle RAC please refer to the section Using WebLogic Server with Oracle RAC in the WebLogic Server document Configuring and Managing WebLogic JDBC.

The following sections address the various requirements and configuration choices when using Oracle RAC with WebLogic Portal. These include:

Supported Configuration

WebLogic Portal 9.2 supports the following Oracle RAC and JDBC driver combinations:

Database Type
JDBC Driver
Notes
Oracle 10g R2 RAC (for Oracle 10.2.0.1 and later patch sets of 10.2.x)
  • Oracle Thin Driver 10g
  • Oracle Thin/XA Driver 10g
Only WebLogic Portal 9.2 is supported with Oracle RAC 10g R2

For all the database configurations supported by WebLogic Platform 9.2, refer to WebLogic Platform Support for Databases.

Configuration Considerations for Oracle RAC

For each JDBC data source used by WebLogic Portal, the following configuration choices need to be considered:

Multi-Data Source vs. Oracle Thin Driver

There are two ways to implement JDBC fail-over and load-balancing: through the WebLogic multi-data source, or through the cluster-aware capabilities built into the Oracle Thin Driver. Using WebLogic Server with Oracle RAC includes examples of how these are implemented.

As pointed out in Using WebLogic Server with Oracle RAC, there are some limitations if you are using the Oracle Thin Driver alone to access Oracle RAC. Specifically, you cannot use load-balancing together with XA support.

On the other hand, WebLogic multi-data source fully supports XA with load-balancing. Multi-data source also offers benefits such as faster fail-over and automatic fail-back. These make multi-data source the favorable choice to access RAC. The rest of the document is based on using multi-data source.

Fail-over vs. Load-Balancing

WebLogic Portal data sources support both fail-over and load-balancing with the exception of the portalDataSourceNeverXA data source. The portalDataSourceNeverXA is used as JDBC Store. As specified in Using WebLogic Server with Oracle RAC, the data source of a JDBC Store cannot use the load-balancing algorithm.

XA Considerations

WebLogic Portal data sources can use either the XA or the non-XA JDBC driver, except p13nDataSource and portalDataSourceNeverXA. The p13nDataSource is used in local JDBC transactions only. The portalDataSourceNeverXA is used as JDBC Store. As specified in Using WebLogic Server with Oracle RAC, the data source of a JDBC Store cannot use an XA-compliant JDBC driver.

When the multi-data source is used, it is important to make sure that the multi-data source's value of the global-transaction-protocol attribute is identical to those of all its underlying physical data sources. For a multi-data source, the value of global-transaction-protocol can be configured by either manually editing 

${DOMAIN_HOME}/config/jdbc/${DATASOURCE_NAME}-jdbc.xml

or programmatically through WLST scripts. The WebLogic Administration Console does not provide a way to configure this attribute for a multi-data source.

Supported Configurations

In light of the above considerations, the supported RAC configurations are summarized in Table 6-2.

Table 6-2 WebLogic Portal Data Sources When Using Oracle RAC
Data Source Name
Multi-Data Source
MD-Protocol
XA-Driver
XA-Protocol
p13nDataSource
No
 
False
None
cgDataSource
Yes
Load-Balance
True
TwoPhaseCommit
cgDataSource-NonXA
No
 
False
None
portalDataSource
Yes
Load-Balance
True
TwoPhaseCommit
portalDataSourceAlwaysXA
Yes
Load-Balance
True
TwoPhaseCommit
portalDataSourceNeverXA
Yes
Failover
False
OnePhaseCommit
appsGroupSpaceDataSource
Yes
Load-Balance
True
TwoPhaseCommit
sampleDataSource
Yes
Load-Balance
True
TwoPhaseCommit

Note: The values in the above table are consistent with the JDBC data source configuration files packaged with WebLogic Portal, with the addition of the Multi-Data Source column.

Procedure for Using Oracle RAC with WebLogic Portal

The configuration steps for Oracle RAC are very similar to those described in the previous sections Configuring the Main WebLogic Portal Database, Configuring the GroupSpace Database, and Manually Creating a Separate Database and Database Objects for Behavior Tracking. Each of these three sections includes seven steps.

  1. If you want to create new tablespaces for the main WebLogic Portal schema:
    2. Note: To run the SQL script in this step, log on to any one node of the RAC. The specific node is not important since all Oracle instances in the RAC share the same disk storage.
    1. Copy the create_tablespaces.sql script and modify it appropriately for your environment. See Table 6-1, Database Scripts and Usage Notes, on page 6-7 and the comments in the script for additional information.
    2. Run the modified create_tablespaces.sql script. For example, from SQL*Plus:
      3. @create_tablespaces.sql
  2. Create the main WebLogic Portal database user:
    3. Note: To run the SQL script in this step, log on to any one node of the RAC. The specific node is not important since all Oracle instances in the RAC share the same disk storage.
    1. Copy the create_users.sql script and modify it appropriately for your environment. See Table 6-1, Database Scripts and Usage Notes, on page 6-7 and the comments in the script for additional information.
    2. Run the modified create_users.sql script. For example, from SQL*Plus:
      3. @create_users.sql

      Follow the remaining steps only if you want to create database objects manually rather than using the Configuration Wizard. To perform the remaining steps using the Configuration Wizard, see Creating WebLogic Configurations Using the Configuration Wizard.

  3. Open your domain's database.properties file for edit.
  4. Set database=oracle.
  5. Update the following settings (by replacing the @ symbols and the text between the symbols with the correct values) for your main WebLogic Portal database:
    6. oracle.user=@DB_USER@
    oracle.password=@DB_PASSWORD@
    oracle.url=jdbc:bea:oracle://@DB_HOST@:@DB_PORT@;SID=@DB_NAME@
    Note: In the oracle.url string, replace @DB_HOST@ and @DB_NAME@ with the hostname and SID of any one node in the RAC. Since all Oracle instances in the RAC share the same disk storage, the database objects created in step 6 will be visible from all other nodes in the RAC.
  6. Create the database objects.
    1. Navigate to the BEA_HOME\user_projects\domains\myPortalDomain directory.
    2. Enter one of the commands:
      • On Windows, double-click create_db.cmd.
      • On UNIX, run create_db.sh.

        • If any error messages are displayed, check the create_db.log file for additional information.

  7. Replace the JDBC data sources in your domain (except appsGroupSpaceDataSource) with multi-data sources pointing to your Oracle RAC.
    1. For each JDBC data source, create one physical data source pointing to each node in the RAC. For example, for p13nDataSource, create physical data sources p13nDataSource-1 to p13nDataSource-N, each pointing to one RAC node. Use the WebLogic Server Administration Console to create these data sources.

      2. Alternatively, you could create them by copying and updating the sample jdbc.xml files in the WL_HOME/portal/db/jdbc/oracle_thin directory following the steps below:

      1) Copy the dataSourceName-jdbc.xml file to DOMAIN_HOME/config/jdbc directory, and name it dataSourceName-n-jdbc.xml, where n is the sequence number of the n-th RAC node.

      2) Edit the file dataSourceName-n-jdbc.xml. Replace @DB_USER@, @DB_USER_PASSWD@, @DB_HOST@, @DB_PORT@ and @DB_NAME@. For @DB_HOST@ and @DB_NAME@, use the hostname and SID of the n-th RAC node.

      3) Edit config.xml to add this newly created JDBC data source as a JDBC system resource.

    2. Create a multi-data source for each of the JDBC data sources, which encompasses the physical data sources created above. For example, create a p13nDataSource multi-data source which includes p13nDataSource-1 to p13nDataSource-N created above.

      3. If you use the WebLogic Server Administration Console, you would need to first remove the existing data source with the same name, and then create the multi-data source. Also, since the WebLogic Server Administration Console does not provide a way to configure the value of global-transaction-protocol attribute for a multi-data source, you need to manually add the attribute into the corresponding jdbc.xml later.

      Alternatively, you could manually edit the corresponding jdbc.xml following the steps below:

      1) Open the corresponding jdbc.xml file, delete the jdbc-driver-params element and the jdbc-connection-pool-params element, including all their children elements:

      2) Between the jndi-name and the global-transactions-protocol elements, add the following two elements. Determine the value of algorithm-type from Table . The value of data-source-list is a comma-separated list of all the underlying physical data sources.

      <algorithm-type>Load-Balancing</algorithm-type>

      or

      <algorithm-type>Failover</algorithm-type>

      <data-source-list>portalDataSourceAlwaysXA-1,
      portalDataSourceAlwaysXA-2 </data-source-list>

    3. An alternative to the manual procedure described above in steps 1 and 2 is WLST scripting. The sample build.xml, oraracconf.py and oraracconf.py.properties provide an example of how to configure an existing WebLogic Portal domain to point to an Oracle RAC database. These files are listed in Sample WLST Scripts. To use the sample, follow the steps below:

      4. 1) Download these files into your DOMAIN_HOME directory.

      2) Open the file oraracconf.py.properties. Edit the values in the "Database usernames and passwords" and the "ORACLE RAC configuration" sections to reflect your Oracle RAC environment.

      3) Open a command prompt, change current directory to DOMAIN_HOME/bin. Execute the following script:

      . ./setDomainEnv.sh (or setDomainEnv.cmd)

      4) Execute ant.

Sample Configuration Files

Listing 6-1 p13nDataSource Multi-Data Source 
<?xml version="1.0" encoding="UTF-8"?>
<jdbc-data-source xmlns="http://www.bea.com/ns/weblogic/90">
  <name>p13nDataSource</name>
  <jdbc-data-source-params>
    <jndi-name>p13n.trackingDataSource</jndi-name>
    <jndi-name>p13n.sequencerDataSource</jndi-name>
    <jndi-name>cm.sequencerDataSource</jndi-name>
    <jndi-name>p13n.leasemanager</jndi-name>
    <jndi-name>p13n.dataSyncDataSource</jndi-name>
    <jndi-name>p13n.entitlementsDataSource</jndi-name>
    <jndi-name>p13n.quiescenceDataSource</jndi-name>
    <algorithm-type>Load-Balancing</algorithm-type>
    <data-source-list>p13nDataSource-1,p13nDataSource-2</data-source-list>
    <global-transactions-protocol>None</global-transactions-protocol>
  </jdbc-data-source-params>
</jdbc-data-source>
Listing 6-2 p13nDataSource-1 Data Source 
<?xml version="1.0" encoding="UTF-8"?>
<jdbc-data-source xmlns="http://www.bea.com/ns/weblogic/90">
  <name>p13nDataSource-1</name>
  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@rnhp380-c11-23-vip:1521:DBSRAC101</url>
    <driver-name>oracle.jdbc.OracleDriver</driver-name>
    <properties>
      <property>
        <name>user</name>
        <value>WEBLOGIC_8</value>
      </property>
    </properties>
    <password-encrypted>{3DES}cIUMOgs5Divb+UWlIFgSoA==</password-encrypted>
  </jdbc-driver-params>
  <jdbc-connection-pool-params>
    <initial-capacity>5</initial-capacity>
    <max-capacity>20</max-capacity>
    <capacity-increment>1</capacity-increment>
    <test-connections-on-reserve>true</test-connections-on-reserve>
    <test-table-name>SQL SELECT 1 FROM DUAL</test-table-name>
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>p13nDataSource-1</jndi-name>
    <global-transactions-protocol>None</global-transactions-protocol>
  </jdbc-data-source-params>
</jdbc-data-source>
Listing 6-3 p13nDataSource-2 Data Source 
<?xml version="1.0" encoding="UTF-8"?>
<jdbc-data-source xmlns="http://www.bea.com/ns/weblogic/90">
  <name>p13nDataSource-2</name>
  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@rnhp380-c11-25-vip:1521:DBSRAC102</url>
    <driver-name>oracle.jdbc.OracleDriver</driver-name>
    <properties>
      <property>
        <name>user</name>
        <value>WEBLOGIC_8</value>
      </property>
    </properties>
    <password-encrypted>{3DES}cIUMOgs5Divb+UWlIFgSoA==</password-encrypted>
  </jdbc-driver-params>
  <jdbc-connection-pool-params>
    <initial-capacity>5</initial-capacity>
    <max-capacity>20</max-capacity>
    <capacity-increment>1</capacity-increment>
    <test-connections-on-reserve>true</test-connections-on-reserve>
    <test-table-name>SQL SELECT 1 FROM DUAL</test-table-name>
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>p13nDataSource-2</jndi-name>
    <global-transactions-protocol>None</global-transactions-protocol>
  </jdbc-data-source-params>
</jdbc-data-source>

  Back to Top       Previous  Next