Database Administration Guide

Database Setup and Maintenance Overview

This section identifies considerations that the database administrator may want to be aware of in setting up and maintaining WebLogic Portal databases.

For a discussion of database environment considerations for team development, see the section "Creating a Shared Portal Domain" in the Production Operations Guide at http://download.oracle.com/docs/cd/E13218_01/wlp/docs81/prodOps/index.html.

For additional details on database-specific considerations and recommendations, see the individual database sections in this document.

Information included in this section includes:

Character Sets and Sort Orders

A database character set determines which languages can be represented in the database. A database's sort order, or collation, determines the rules by which characters are sorted and compared.

For a globalized WebLogic Portal application, you should plan a database's character set and/or sort order before you set up the database. Changing an existing database's character set and sort order can be a time- and resource-intensive task. Typically a change can be made only if the target character set is a subset of the source or original.

The following sections describe character set and sort order considerations for Portal-supported databases.

Oracle

For Oracle, you define the character set when you create the database.

For Oracle 9i and 10g databases against which a globalized WebLogic Portal application will run, a commonly used character set is AL32UTF8.
For Oracle 8.1.7 databases, the UTF8 character set is a common selection.

You can retrieve information about an Oracle database's character set and sort order by querying the SYS.NLS_DATABASE_PARAMETERS table.

SQL Server

For SQL Server, you can define both an instance level and a database level character set and sort order. The default instance level character set for a SQL Server is determined by the locale setting for the Windows operating system. The following sample output from the sp_helpsort stored procedure shows a common instance level sort order:

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data.

The sample database creation script is located at WL_HOME\portal\db\sql_server\2000\admin\create_database.sql defines the WebLogic Portal database with the settings COLLATE SQL_Latin1_General_CP1_CS_AS. Note that this specifies a case-sensitive definition. Setting the database to case-sensitive allows Content Management queries to function the same for SQL Server as they do for other databases. If Content Management search queries are not written for a case-sensitive environment, you may obtain unexpected results.

You can use the sp_helpdb <dbname> stored procedure to display a SQL Server database's character set and sort order.

Sybase

For Sybase, you define the character set and sort order when you create the Sybase instance. A commonly used Sybase character set and sort order definition is:

Character Set = 2, cp850 Code Page 850 (Multilingual)
Sort Order = 50, bin_cp850 Binary ordering, for use with Code Page 850 (cp850).

You can use the sp_helpsort stored procedure to display information about an instance's character set and sort order.

DB2

For DB2 databases, you can set character encoding for each database individually. The default character encoding is determined by the operating system.

A commonly used DB2 character encoding is UTF-8.

You can examine the DB2 CODESET configuration parameter to determine a database's codeset.

Disk and Data Placement

The following sections describe special considerations, recommendations, and requirements for index placement, CLOB/BLOB/TEXT/IMAGE data storage, and behavior tracking.

Placement of Indexes

For Oracle databases, use the rebuild_indexes.sql script to place indexes into a WEBLOGIC_INDEX tablespace. Run this script manually after creating the WebLogic Portal database to move indexes into their own tablespace.

For SQL Server and Sybase, as of 8.1 SP4 the Data Definition Language (DDL) for non-clustered indexes is defined with ON WEBLOGIC_INDEX to place indexes into their own file group or segment. You must define the WEBLOGIC_INDEX file group or segment before running WebLogic Portal database creation or upgrade scripts. For detailed information about SP4 database changes, see the Upgrade Guide at http://download.oracle.com/docs/cd/E13218_01/wlp/docs81/upgrade/index.html.

Data Storage for Tables with CLOB/BLOB/TEXT/IMAGE Data

This section describes recommendations and special considerations for tables that contain CLOB/BLOB (Oracle/DB2) or TEXT/IMAGE (Sybase/SQL Server) data.

As a general rule, for optimal performance with any of the supported Portal database platforms, CLOB/BLOB/TEXT/IMAGE table data should be physically stored separately from non-CLOB/BLOB/TEXT/IMAGE data. However, to simplify deployment and to allow for flexibility in your configuration, the default Portal database schema is not deployed with separated data. To determine if your specific application is a candidate for separating CLOB/BLOB/TEXT/IMAGE storage, assess the following considerations in your environment:

Content management usage - If you are using the out-of-the-box content management system heavily and you are storing and retrieving many large documents, you may want to modify your storage allocations for the tables that have the CM and CMV prefixes.

The corresponding tables and columns for this change are:

CMV_VALUE.BLOB_VALUE
CM_PROPERTY.BLOB_VALUE
CM_PROPERTY_CHOICE.BLOB_VALUE

Behavior tracking - If you have behavior tracking turned on, you may want to modify storage allocations for behavior tracking tables.

The corresponding table and column for this change is BT_EVENT.XML_DEFINITION.

Physical database storage - If your environment has a separate disk controller and drive that can be dedicated to CLOB/BLOB/TEXT/IMAGE storage, you might see greater performance impacts than if you share a controller.
RAID storage - If you are using RAID, depending on the configuration you may see minimal if any improvement by changing storage allocations. Follow your database vendor recommendations based on your specific configuration.

If you want to change your storage allocations, see your database documentation for specific details on changing CLOB/BLOB/TEXT/IMAGE settings.

The following additional tables contain CLOB/BLOB or TEXT/IMAGE data types; you can experiment with storage changes to determine the effect on performance.

AD_BUCKET.AD_QUERY
CATALOG_PROPERTY_VALUE.BLOB_VALUE
DATA_SYNC_ITEM.XML_DEFINITION
DISCOUNT.DISCOUNT_RULE
MAIL_MESSAGE.MESSAGE_TEXT
P13N_ANONYMOUS_PROPERTY.PROPERTY_VALUE
P13N_DELEGATED_HIERARCHY.ENTITLEMENT_DOCUMENT
PF_CONSUMER_REGISTRY.REGISTRATION_STATE
PF_PROXY_PORTLET_INSTANCE.PORTLET_STATE
PLACEHOLDER_PREVIEW.XML_DEFINITION
PROPERTY_VALUE.BLOB_VALUE

Behavior Tracking

Behavior tracking is typically used to track visitor behavior by recording events.

Due to the large number of rows that can be written to the BT_EVENT table when behavior tracking is enabled, you might want to use a separate database (or tablespace and schema, depending upon your DBMS) to store behavior tracking data.

For each database type other than PointBase, the following sections include instructions for creating a separate database for behavior tracking events:

Reporting on Behavior Tracking data

Some third-party behavior tracking reporting tools extract data from the BT_EVENT table data and place it into another set of database tables. For more information on reporting and analytics tools, see the Portal Solutions Catalog at http://dev2dev.bea.com/products/wlportal/psc/Reporting_Analytics_BI.jsp.

Database Table Caching

Some databases provide the ability to cache or "pin" database tables into memory. Choose a database caching implementation based on the WebLogic Portal components that are deployed for your application and how you deploy them.

The following text summarizes the support provided by each database type for caching or pinning database tables. Depending on your environment, focus your caching strategy on small tables and frequently referenced tables.

Oracle's touch-count algorithm makes it unnecessary to pin specific tables to buffers or database caches. You can adjust Oracle instance parameters to influence buffer behavior.
SQL Server provides DBCC PINTABLE (database_id, table_id) to pin tables to the buffer cache.
Sybase provides the ability to define multiple database cache and to selectively bind tables, indexes and logs to those caches. Use sp_objects_stats to identify hot objects that may benefit from their own cache. Use a Sybase monitoring tool or sp_sysmon to determine if cache hit ratios are acceptable.
DB2 uses BUFFERPOOLs to cache database data and describes configuring bufferpools as the single most important tuning area. DB2 allows you to assign individual tables and indexes to a BUFFERPOOL.

Page and Block Size

Note: See the individual database chapters for additional details on database-specific considerations.

For Oracle and SQL Server databases, an 8K page/block size is the default.

For DB2 an 8K bufferpool is defined for the WebLogic Portal tables and indexes that require this larger pool size (higher than the 4K default).

For Sybase databases an 8K pagesize is required for several WebLogic Portal tables and indexes.

Sybase Locking

A Sybase instance's default locking mechanism is "ALL PAGES". For concurrency, locking can also be defined for each table in a Sybase database.

In 8.1 SP4 the following WebLogic tables are defined with LOCK DATAROWS for Sybase. Other WebLogic Portal tables for Sybase (excluding those defined in collaboration_create_tables.sql) are defined with LOCK DATAPAGES.

CATALOG_ENTITY
ENTITY
SEQUENCER
L10N_INTERSECTION
PF_DESKTOP_INSTANCE
PF_PLACEMENT

Based on your usage of WebLogic Portal components with a Sybase database, you may decide to modify additional tables to LOCK DATAROWS.

Database Sizing

The size required for your WebLogic Portal database(s) depends on many factors, including the following:

The components of WebLogic Portal that are deployed for your application and the method you use to deploy them. By default, all WebLogic Portal database objects are created for each domain. If a component of WebLogic Portal is not deployed, its database objects will exist, but will not contain any data rows.
The number of WebLogic Portal application users in your environment.
The degree to which customization or personalization is allowed for your end users' WebLogic Portal resources. For example, you might ask these questions about user personalization:

Can end users create their own collaboration portlets such as My Mail Portlet, My Task List Portlet, and so on?
Can end users create their own portal resource views?

Note: Portal resource view data is stored in the Portal Framework tables named PF_<resource_type>_INSTANCE.

The following sections describe some WebLogic Portal database tables that you should monitor closely for growth due to increasing data volumes.

Behavior Tracking

The BT_EVENT table can grow significantly if Behavior Tracking is enabled.

Personalization

The following database tables store personalization data; monitor them for data growth.

ENTITY — This table will grow as you define new users and groups for your WebLogic Portal application.
PROPERTY_VALUE — This table will grow as property values are added to a user profile. This table can become quite large when a large number of users exist with a large number of property values per user. For a discussion of user and group profile values, see the Administration Portal online help at http://download.oracle.com/docs/cd/E13218_01/wlp/docs81/adminportal/help/UG_UserProfile_Edit.html.

Portal Framework and WSRP

The following tables involve the Portal Framework and/or Web Services for Remote Portlets (WSRP) and can potentially be high volume tables; monitor them for data growth.

PF_BOOK_INSTANCE — This table identifies instances of the BOOK_DEFINITION. There is always at least one book instance, namely the primary instance; all other instances represent customization by administrators or end users. This table can grow significantly if extensive user customization occurs.
PF_BOOK_GROUP — This table represents child pages or book placements on the parent book. A single record in the table represents one placement on a book. This table can grow significantly if extensive user customization occurs.
PF_PLACEMENT — This table tracks the portlets on a specific portal page for each desktop. Any time a visitor modifies the position of portlets on a page, a row will be inserted for each portlet that exists on the portal page for the user's custom desktop. This table can grow significantly because of portal customization; this growth can be expressed using the equation number of users * number of pages * number of portlets = number of rows.
PF_PORTLET_PREFERENCE_VALUE — This table identifies preference values for the portlet instance. This table can grow significantly if extensive user customization occurs.
PF_PORTLET_INSTANCE — A portlet definition has at least one portlet instance that is known as the primary instance. Every time a portlet is dragged onto a page, a new instance of the portlet is being created and a column is inserted into the table. If, for example, an administrator drags a portlet onto a page and then a user modifies the portlet by setting the default to "minimized," then another instance is created and a column inserted. (Subsequent changes to the portlet instance do not create a new instance/row.) This growth can be expressed using the equation number of portlet definitions * number of instances = number of rows.
PF_DESKTOP_INSTANCE — This table identifies a customized or localized instance of a desktop. This table can grow significantly; a row is added for each entitled desktop, and possibly a row for each unique visitor who customizes the site. This growth can be expressed using the equation number of portals * number of desktops + number of visitors who customize = number of rows.

Content Management & Virtual Content Management (Versioning)

The following database tables store content management (CM) and versioning (CMV) data; monitor them for data growth.

CM_PROPERTY_DEFINITION — This table defines the shape of a property; it describes the property type (BLOB, Boolean, Varchar, Float, Date, Number), whether it is required, whether it is editable, the default value, and restricted values, if applicable. This table can grow significantly if content management is used to store large quantities of data.
CMV_NODE — This table uniquely identifies a content-managed node from a BEA repository (from the CM_NODE table) that has been versioned and is being edited within the Content Management Virtual Repository. This table can grow significantly if content management is used to store large quantities of data.
CMV_NODE_VERSION — This table uniquely identifies all the versions of a mode within the Content Management Virtual Repository. This table can grow significantly if Content Management versioning is enabled.
CMV_NODE_VERSION_PROPERTY — This table uniquely identifies a relationship between a CMV_NODE_VERSION and CMV_PROPERTY. This table will likely have the largest number of rows within Content Management if versioning is enabled. It may not be the largest table because it is a cross-reference table.
CMV_PROPERTY — This table uniquely identifies a property that can be associated with a node version. For example, some properties of a book might be author, title, and subject. This table can grow significantly if Content Management versioning is enabled.
CMV_VALUE — This table uniquely identifies a value for a given property. For example, a property SUBJECT for a BOOK might have a value of FINANCE. This table can grow significantly if Content Management is used to store large quantities of data. This table will likely be the largest Content Management table because it stores the actual content associated with each Content Node.

Content Search (Oracle)

Content search within WebLogic Portal typically performs best when accessing database indexes associated with the content repository. In some situations the Oracle optimizer, based on database statistics, will choose to perform tablespace scans instead of using indexes to access the data. Most often this will result in a much slower response time than if an index was used.

To improve response time, verify that the following Oracle initialization parameter reads:

optimizer_mode=choose.

In addition to ensure that Oracle indexes are given greater preference over tablespace scans, the following two initialization parameters can be altered:

optimizer_index_cost_adj (range 0-100)

optimizer_index_caching (range 0-100)

Refer to your Oracle documentation for the impact of each of these settings to make sure it is right for your installation.

Updating Database Statistics

Each DBMS has its own utility or commands for updating the database statistics used by its query optimizer. A DBA should schedule periodic jobs to maintain database statistics.

Database Reorganizations

The statistics that you collect for your database often provide information on the organization of tables and indexes in your database.

Normal DML operations (for example, DELETE, INSERT, UPDATE) that the WebLogic Portal application performs can affect table and index organization; this can affect database performance. Refer to your database vendor product documentation for details on utilities that are available for table and index reorganization and for information on determining when a re-organization should occur.

Database Backup and Recovery

Use the same procedures for backup and recovery of WebLogic Portal databases as you use for other data. The following text lists some general recommendations:

Store your E-Business Control Center data and J2EE resources in source control, and back up the source control database.
Back up your WebLogic Portal database according to your DBMS vendor's recommendations.

Perform periodic test restores to ensure that your backups are sound.

Commerce Functionality in an XA Domain

If you are using the optional commerce functionality in a Portal domain configured for XA, then you must move the weblogic.jdbc.jts.commercePool JNDI name from the portalFrameworkPool to the cgDataSource-nonXA JDBC Tx Data Source. For information about using commerce functionality, see Adding Commerce Services to an Application in the WebLogic Workshop Help.

WebLogic Portal Propagation Utility

The Propagation Utility guides you through the process of propagating the configuration contents, including portal framework, datasync, and security data, of one portal domain environment to another. For example, the Propagation Utility can play a role whenever you move a portal application from a staging environment to the production environment.

You should be aware of the usage of this utility, and you may need to back up databases prior to propagation. For more information about this utility, contact BEA customer support.

Collocation of Database and WebLogic Portal

To prevent network latency issues, you should locate your database and your WebLogic Portal instances in the same data center. This recommendation is especially important when you use the Propagation Utility, to ensure that a large propagation process can complete successfully.