Database Administration Guide
Using a Sybase Database
This section describes the steps necessary to use a Sybase database with WebLogic Portal 8.1, and includes information on the following subjects:
Review this entire chapter and any release notes before proceeding. The steps in this chapter should be performed by a database administrator
Configuring a Sybase Database
Before proceeding, be sure you have read Overview of Database Configuration for WebLogic Portal on page 1-1.
Note the following when defining your Sybase instance.
- Be sure that you are using a supported version, see http://download.oracle.com/docs/cd/E13196_01/platform/docs81/support/supp_plat.html#1085671.
- Define a pagesize of at least 8K to support WebLogic Portal's use of wide tables, wide columns, and larger indexes.
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. A technical white paper on the Sybase migration process can be found at: http://www.sybase.com/detail/printthis/1,6907,1021203,00.html.
- For WebLogic Portal 7.0 users who are upgrading to version 8.1, ensure that the following WebLogic Portal 7.0 script has been run:
bea\weblogic700\portal\db\sybase\125\migrate\migrate_to_125.sql
- Install the Sybase client software on the WebLogic Platform host and do the following:
- Configure it connect to the target Sybase instance.
- Verify that you can connect to the target instance via isql. For example,
isql -Usa -Ppassword -SMySybase
- Prepare the Sybase database. The database creation scripts install domain-specific tables. You should work with your database administrator to adjust the
SAMPLE
scripts, and to create the database schema owner users and tablespaces needed for your environment.
Notes: Multiple databases are required if you have multiple domains, or to run multiple environments using the same Sybase instance (for example, if you want to run development and system test from a single Sybase installation).
Be sure to back up your database(s) before installing any new database objects. See your database documentation for details.
- Review and modify the provided sample scripts to suit your environment. These scripts are provided in WL_HOME/portal/db/sybase/125/admin.
Script Name
|
Description
|
create_devices.sql
|
Create database devices.
Usage Notes: Database devices must be created by a user with system administrator privileges (normally the 'sa' user). "D:\DATAFILE" and "E:\LOGFILE" specifications in this script must be changed to reflect valid disk locations for your environment. Optimally, data and log devices would be placed on separate physical disks, which reside on separate controllers. Edits are required to change file sizes and device names.
The following default names are used:
|
create_database.sql
|
Create the database and login. An alias is added to the dbo (database owner user) of the database. The devices created by create_devices.sql are used.
Usage Notes: Edits are required to reflect name or size changes from create_devices.sql. Edits are required to change the default database name and/or database owner user.
The following defaults are used:
If the database you are creating is a development database your database administrator may want to uncomment and set the "truncate log on checkpoint" database option.
If your application will use WebLogic Workshop page flows or RowSet controls uncomment and set the 'DDL in transaction' to true option to true to allow database table create commands to work properly.
|
statistics_build.sql
|
Builds statistics.sql to update table and index statistics for the database optimizer. Statistics should be should be updated whenever any significant changes in database data occurs. Your database administrator should schedule update statistics to run periodically in your environment.
|
install_report_build.sql
install_report_static.sql
|
Builds an informational installation report about the database objects created by the WEBLOGIC user.
|
bt_create_devices.sql
|
Creates behavior tracking database devices.
Usage Notes: Database devices must be created by a user with system administrator privileges (normally the 'sa' user). "D:\DATAFILE" and "E:\LOGFILE" specifications in this script must be changed to reflect valid disk locations for your environment. Optimally, data and log devices would be placed on separate physical disks, which reside on separate controllers. Edits are required to change file sizes and device names.
The following default names are used:
|
bt_create_database.sql
|
Create the WEBLOGIC_EVENT database and WEBLOGIC_EVENT database owner user login. An alias is created to make WEBLOGIC_EVENT dbo (database owner user) in the database.
Usage Notes: Edits are required to change database names, database owner user and password. Edits are required to reflect valid disk locations for DATA and the LOG devices, or to adjust file sizes. DATA and LOG files should be placed on separate physical disks and away from any system database files.
The following defaults are used:
|
- Run create_devices.sql as a user with system administrator privileges. For example,
isql -Usa -SMYSYBASE -e -icreate_devices.sql -ocreate_devices.log
- Run create_database.sql via isql as a user with System Administrator privileges (i.e. the sa user).
isql -Usa -SMYSYBASE -e -icreate_database.sql -ocreate_database.log
Output from the above is written to the file specified after the "-o" parameter. The log file is stored in the same directory that the script resides. Verify that each log file contains no errors for database object creation.
- Statistics and install report scripts will be run automatically by the create_db.cmd/.sh scripts. Ensure that your database administrator schedules update statistics to be run periodically for your WebLogic Portal database.
Manually Creating Database Objects
You can either manually create database objects or use the Configuration Wizard, see Manually Creating Database Objects and JDBC Settings on page 1-2 for more information.
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:
- Verify that you can connect to the target database. Use the following command syntax to verify that you can connect to the target database server using the default schema owner user created by running
create_database.sql
.
isql -UWEBLOGIC -SMYSYBASE
- Open your domain's
db_settings.properties
file for edit and comment out the database setting for PointBase.
- Uncomment the database settings for your new target database and update the following settings for your database:
server=
dblogin=
password=
- Initialize the database with the new settings.
- Navigate to the
<BEA_HOME>\user_projects\domains\portalDomain
directory, and double-click on the create_db.cmd
file.
- 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
.
Manually Configuring Your Domain's JDBC Driver Settings
You can either manually configure your domains JDBC driver settings using the WebLogic Server Console, or use the Configuration Wizard, see Manually Creating Database Objects and JDBC Settings on page 1-2 for more information.
To manually configure your JDBC Driver settings using WebLogic Server Console,
- Start the WebLogic Server for your domain.
- Log on to the WebLogic Server Console.
- Configure your new connection pools.
- Go to Services -> JDBC -> Connection Pools.
- Click Configure a new Connection Pool.
Note: For an XA configuration, see "Creating XA Domains Using Configuration Templates" in the "Creating WebLogic Configurations Using the Configuration Wizard documentation, http://download.oracle.com/docs/cd/E13196_01/platform/docs81/confgwiz/index.html .
- Choose a name for the new Connection Pool (For example: cgPoolN) and fill in the blanks for your vendor database. Click Continue.
- Test your connection to verify that you can successfully connect to your database.
- Create and deploy your new Connection Pool.
Note: A one-to-one mapping of JDBCTxDataSource to JDBC Connection Pool needs to be maintained in the domain's configuration (as managed in the config.xml file). Create one new JDBC Connection Pool for each JDBCTxDataSource and another JDBC Connection Pool for the domain's JDBCDataSources.
- Update your data sources.
- From Services -> JDBC -> Data Sources, click on each data source and switch each to the newly created connection pool. Be sure to apply each change.
- 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.
- From Services -> JMS -> Stores -> cgJMSStore, switch cgJMSStore to use the new Connection Pool.
- Stop your domain's WebLogic Server, then restart it.
- In the WebLogic Server Console, delete the original connection pools.
- Go to Services -> JDBC -> Connection Pools.
- Right-click each connection pool and select Delete.
Creating a Database for Behavior Tracking Events
You may want to store behavior tracking events in a different location than other WebLogic Portal database objects for increased performance. 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 only need to following these steps if you are configuring a separate database for behavior tracking events.
- Verify that you can connect to the target Sybase instance, see step 1. in .
- Run
bt_create_devices.sql
via isql as a user with system administrator privileges. For example,
isql -Usa -SMYSYBASE -e -ibt_create_devices.sql -obt_create_devices.log
- Run
bt_create_database.sql
via isql as a user with system administrator privileges (i.e. the sa user).
isql -Usa -SMYSYBASE -e -ibt_create_database.sql -obt_create_database.log
- Open your domain's
db_settings.properties
file for edit.
- Within the
db_settings.properties
file, uncomment the database settings for Sybase and update the following settings:
server=<SERVER_NAME>
dblogin=WEBLOGIC_EVENT
password=WEBLOGIC_EVENT
- Within the
db_settings.properties
file, find the p13n_modules, portal_modules and netuix_modules lines at the top of the file.
- Copy these 3 lines and comment out the original settings by adding a # sign.
- Replace the original settings as follows:
- Replace
p13n_modules=p13n au bt ds
with p13n_modules=bt
- Replace
portal_modules=cm wlcs wps collaboration sample_cm
with portal_modules=
- Replace
netuix_modules=pf
with netuix_modules=
When you are finished, the section should look like this:
#p13n_modules=p13n au bt ds
#portal_modules=cm wlcs wps collaboration sample_cm
#netuix_modules=pf
p13n_modules=bt
portal_modules=
netuix_modules=
- Save the changes to the
db_settings.properties
file.
- Initialize the database with the new settings.
- Navigate to the
\\<BEA_HOME>\user_projects\domains\portalDomain
directory, and double-click on the create_db.cmd file.
- Verify the results in the db.log file.
Note: If you are using the sample domain, run the create_db.cmd file from the following directory: \\<WL_HOME>\samples\domains\portal
.