| Oracle® Fusion Middleware Database Administration Guide for Oracle WebLogic Portal 10g Release 3 (10.3.4) Part Number E14233-02 |
|
|
View PDF |
| Oracle® Fusion Middleware Database Administration Guide for Oracle WebLogic Portal 10g Release 3 (10.3.4) Part Number E14233-02 |
|
|
View PDF |
This section identifies considerations for setting up and maintaining WebLogic Portal databases.
For a discussion of database environment considerations for team development, see the Oracle Fusion Middleware Production Operations Guide for Oracle WebLogic Portal.
For additional information on vendor-specific considerations and recommendations, see the chapter in this guide pertaining to your database vendor.
This chapter includes the following sections:
Section 3.2, "Choosing Internationalization and Localization Settings"
Section 3.7, "Support for Asynchronous Proliferation Requires XA"
Section 3.8, "Note About Creating or Refreshing Database Objects"
WebLogic Portal's database creation scripts (create_db.cmd/sh) use the domain's salt file when they call JDBCDataLoader.
Database passwords in the database properties files, such as database.properties, can be either plain text or encrypted. Use weblogic.security.Encrypt to obtain the encrypted passwords. The salt file used to encrypt and decrypt passwords is the domain's DOMAIN_HOME/security/SerializedSystemIni.dat salt file.
For more about weblogic.security.Encrypt, see "Using the WebLogic Server Java Utilities" in the Oracle Fusion Middleware Command Reference for Oracle WebLogic Server.
The Localization Industry Standards Association (LISA) defines internationalization and localization as follows:
"Internationalization is the process of generalizing a product so that it can handle multiple languages and cultural conventions without the need for re-design. Internationalization takes place at the level of program design and document development."
"Localization involves taking a product and making it linguistically and culturally appropriate to the target locale (country/region and language) where it will be used and sold."
This section contains information on choosing language settings, character sets, and collation (sort) settings for your database. It includes the sections:
To use SQL Server with portal applications that are localized, you must modify the language settings in the <WLPORTAL_HOME>\portal\db\sql_server\admin\create_database.sql script before running it to create the database.
For example, to change the language setting from US English to Japanese, perform the following edits:
Change the line reading:
COLLATE SQL_Latin1_General_CP1_CS_AS
to:
COLLATE Japanese_CS_AS_KS_WS
Change the line reading:
exec sp_addlogin 'WEBLOGIC', 'WEBLOGIC', 'WEBLOGIC', 'us_english'
to:
exec sp_addlogin 'WEBLOGIC', 'WEBLOGIC', 'WEBLOGIC', 'japanese'
Run the script (after making any additional required modifications).
For more information about language support in SQL Server, refer to the vendor documentation.
If you want to localize portal applications to support a unicode language, you can edit and run the <WLPORTAL_HOME>\portal\db\sql_server\admin\alter_use_unicode_columns.sql script to alter all columns of type TEXT, CHAR, and VARCHAR to use their unicode equivalents (NTEXT, NCHAR, and NVARCHAR, respectively).
Caution:
Changing these column data types has performance implications. Each column that is modified uses twice as much storage space as the original data type. Back up the database that contains the portal schema before running this script.After running the script, you can use DBCC CHECKDB command to verify physical storage and table constraints.
A database's character set determines which languages can be represented in the database. A database's collation (sort order) determines the rules by which characters are sorted and compared.
For a globalized WebLogic Portal application, choose the database's character set and 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.
The following sections describe character set and sort order considerations for WebLogic Portal-supported databases. Refer to your vendor documentation for more information.
For Oracle, you define the character set when you create the database. In globalized Oracle 9i and 10g databases, a commonly used character set is AL32UTF8.
You can retrieve information about an Oracle database's character set and sort order by querying the SYS.NLS_DATABASE_PARAMETERS table.
For SQL Server, you can define both instance-level and database-level character sets and sort orders.
The default instance-level character set for 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 <WLPORTAL_HOME>\portal\db\sql_server\admin\create_database.sql. It defines the WebLogic Portal database with the setting COLLATE SQL_Latin1_General_CP1_CS_AS.
This specifies a case-sensitive definition. Setting the database to be case-sensitive allows content management queries to function for SQL Server as they do for other databases. If content management search queries are not written for a case-sensitive environment, you may have unexpected results.
You can use the sp_helpdb dbname stored procedure to display a SQL Server database's character set and sort order.
For MySQL, you define the character set and set collate at database creation time. A common MySQL character set and collate are CHARACTER SET latin1 and COLLATE latin1_general_cs.
MySQL restricts key lengths to 1000 bytes when a UTF8 character set is used. If UTF8 is the desired character set, the CM_NODE.FULL_PATH and CM_PROPERTY.PROPERTY_NAME columns are restricted to a maximum size of VARCHAR(255), so by default database object creation errors will be thrown if the MySQL database character set is UTF8. Therefore, do not use the UTF8 character set with MySQL at this time.
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.
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 use the DB2 CODESET configuration parameter to determine a database's codeset.
This section details factors to consider when optimizing database performance. It includes the sections:
Section 3.3.1, "Collocating the Database and WebLogic Portal"
Section 3.3.3, "Caching Database Tables Using Database-Specific Settings"
Section 3.3.5, "Improving Content Search Response Time for Oracle"
Section 3.3.6, "Using Database Statistics for Table and Index Organization"
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.
The frequency of database access varies greatly depending on what functionality is used and how it is used. By reducing the frequency of database access, caching plays a large role in configuring the portal for optimum performance.
For the core portal framework, the portal taxonomy that all users share is loaded in memory during initialization, so database access is typically infrequent after initialization. If a user has customized the portal, a database read is necessary the first time they access it, to retrieve their customizations, user preferences, and so on.
Many other features of the product also use the database, including the content management store. If content is frequently accessed, caching is a good idea where possible; otherwise, database access is necessary to retrieve the content.
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 summarizes the support provided by each database type for caching or pinning database tables. Depending on your environment, you might 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 the DBCC PINTABLE (database_id, table_id) command to pin tables to the buffer cache.
Sybase provides the ability to define multiple database caches and to selectively bind tables, indexes, and logs to those caches. Use sp_objects_stats to identify 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 the BUFFERPOOL configuration parameter to cache database data; this should be the focus of tuning efforts. DB2 allows you to assign individual tables and indexes to a buffer pool.
A Sybase instance's default locking mechanism is ALL PAGES. For concurrency, locking can also be defined for each table in a Sybase database.
The following WebLogic tables are defined with LOCK DATAROWS for Sybase. Other WebLogic Portal tables for Sybase 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 the setting LOCK DATAROWS.
Content search within WebLogic Portal typically performs best when using the indexes associated with the content repository. In some situations the Oracle optimizer, based on database statistics, chooses to perform tablespace scans instead of using indexes to access the data. Often this results in a much slower response time than if an index was used.
To improve response time, verify that the optimizer_mode Oracle initialization parameter is set to choose.
In addition, to ensure that Oracle indexes are given greater preference over tablespace scans, you can modify the following two initialization parameters:
optimizer_index_cost_adj (range 0-100) optimizer_index_caching (range 0-100)
Refer to your Oracle documentation for the more information about these settings, and the impact modifications may have on your installation.
Each DBMS has its own utility or commands for updating the database statistics used by its query optimizer. The statistics that you collect for your database often provide information on the organization of tables and indexes in your database. A DBA should schedule periodic jobs to maintain database statistics.
Data Manipulation Language (DML) operations (for example, DELETE, INSERT, UPDATE) executed in WebLogic Portal applications can affect table and index organization; this can affect database performance. Refer to your database vendor documentation for details on utilities that are available for table and index reorganization and for information on determining when a re-organization should occur.
The following sections describe special considerations, recommendations, and requirements for index placement, CLOB/BLOB/TEXT/IMAGE data storage, and behavior tracking:
For Oracle databases, use the rebuild_indexes.sql script to move indexes into their own tablespace, such as the WEBLOGIC_INDEX tablespace. Run this script manually after creating the WebLogic Portal database to move indexes into their own tablespace. For more information, see Section 7.1, "Configuring Oracle Databases."
For SQL Server and Sybase, 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. The provided sample create_database.sql scripts define WEBLOGIC_INDEX appropriately.
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 WebLogic Portal database platforms, CLOB/BLOB/TEXT/IMAGE table data should be physically stored separately from other types of data. However, to simplify deployment and to allow for flexibility in your configuration, the default WebLogic 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 configuration - 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 relevant table columns are:
CMV_VALUE.BLOB_VALUE
CM_PROPERTY.BLOB_VALUE
CM_PROPERTY_CHOICE.BLOB_VALUE
Behavior tracking configuration - If you have behavior tracking turned on, you may want to modify storage allocations for behavior tracking tables.
The relevant table column is BT_EVENT.XML_DEFINITION.
Disc controller configuration - If your environment has a separate disk controller and drive that can be dedicated to CLOB/BLOB/TEXT/IMAGE storage, you might see performance improvements using a separate controller rather than sharing a controller.
RAID storage configuration - If you are using RAID, you may see limited 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 vendor documentation for specific details on changing settings for CLOB/BLOB/TEXT/IMAGE data.
The following additional columns 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 enables you to develop, manage, and measure personalized portal applications. By recording events, you can customize portal applications and track visitor behavior.
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 other than Derby, see the chapter in this guide pertaining to your database vendor for information on creating a separate database for behavior tracking events.
This section describes the considerations for database, page, block, and data type sizing.
Sizing 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 portlets such as My Mail Portlet, My Task List Portlet, and so on?
Can end users create their own portal resource views?
Note:
WebLogic 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.
The BT_EVENT table can grow significantly if behavior tracking is enabled.
The following database tables store personalization data; monitor them for data growth.
ENTITY – This table grows as you define new users and groups for your WebLogic Portal application.
PROPERTY_VALUE – This table grows 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 Oracle Fusion Middleware User Management Guide for Oracle WebLogic Portal.
The following tables involve the Portal Framework and 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 is 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, known as the primary instance. Every time a portlet is dragged onto a page, a new instance of the portlet is created and a column is inserted into this 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," another instance is created and a row 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
The following database tables store content management (CM) data; monitor them for data growth.
CM_PROPERTY – This table stores property values. This table can grow significantly if content management is used to store large quantities of data.
CM_NODE – This table uniquely identifies a node within a WLP repository. 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 WLP repository (from the CM_NODE table) that has been versioned and is being edited within the Virtual Content 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 Virtual Content Repository. This table can grow significantly if content management versioning is enabled (the default).
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 contains cross-references.
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 (the default).
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 is likely be the largest content management table because it stores the metadata associated with each content node.
The following database tables store Community data; you should monitor them for data growth. If you are using multiple Communities or have a large numbers of users, you can expect these tables to consume more space than the basic Portal Framework customization tables.
PF_COMMUNITY_DEFINITION, PF_COMMUNITY_PROPERTY and PF_INVITATION scale in proportion to the number of Community instances.
PF_COMMUNITY_MEMBER scales in proportion to the number of WebLogic Server users involved in Communities.
PF_COMMUNITY_MEMBERSHIP, PF_MEMBERSHIP_CAPABILITY, PF_INVITEE, PF_INVITEE_PROPERTY and PF_NOTIFICATION scale in proportion to the number of Community instances multiplied by the number of Community members per Community.
For Oracle and SQL Server databases, an 8K page/block size is the default.
For DB2 databases, an 8K buffer pool 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 page size is required for several WebLogic Portal tables and indexes. For Sybase the default page size is 2K, and Sybase does not allow rows to span pages. If your Sybase instance uses 2k or 4k pages, create a new Sybase instance with an 8K page size. Sybase provides a migration utility to migrate data between servers of different page sizes.
Note:
See the chapter in this guide pertaining to your database vendor for additional details on database-specific considerations.When you create database tables for DB2 and Derby databases, the maximum BLOB/CLOB size varies by column. You can edit the appropriate database creation script to change this value.
In general, you should be aware of any size restrictions that might apply to your database. Restrictions can vary from one database version to another; refer to your vendor documentation for more information.
Use the same procedures for backup and recovery of WebLogic Portal databases as you use for other database data.
The following are general recommendations for backup and recovery:
Store your data and J2EE resources in source control, and back up the source control database.
Back up your WebLogic Portal database according to your database vendor's recommendations.
Perform periodic test restores to ensure that your backups are sound.
You use the WebLogic Portal Propagation Utility to move portal metadata from one portal environment to another. You should be aware of the usage of this utility, and back up your databases prior to propagation.
By design, when you move a portal environment, not all database data is propagated. For example, user and group data is not propagated.
For more information about the Propagation Utility, and what database data is propagated, see the Oracle Fusion Middleware Production Operations Guide for Oracle WebLogic Portal. This guide also describes database environment considerations for team development.
JMS and JMS database tables are created automatically by WebLogic Server if they do not already exist in the database to store proliferation information. The Portal Resource Proliferation Mode setting determines how updates to portal resources (pages, books, portlets) are cascaded - or proliferated - to desktops and user-customized instances. You can set the mode to Asynchronous, Synchronous, or Off in the WebLogic Portal Administration Console.
When proliferation is performed asynchronously (the default) a JMS queue is used, which requires an XA driver. In this case, the portalDataSourceAlwaysXA-xml data source is used. This means that your database environment must be configured to support XA.
To create or refresh your WebLogic Portal database:
Stop WebLogic Server.
Delete the contents of the directory DOMAIN_HOME/servers/yourServer/data/ldap.
Run the create_db.cmd (or create_db.sh) command.
|
 Copyright © 2010, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|