Oracle® Fusion Middleware Database Administration Guide for Oracle WebLogic Portal 10g Release 3 (10.3.5) Part Number E14233-03 |
|
|
View PDF |
This chapter describes the steps necessary to use an Oracle database with WebLogic Portal, and includes the following sections:
Section 7.2, "Configuring the Main WebLogic Portal Database"
Section 7.3, "Manually Creating a Separate Database and Database Objects for Behavior Tracking"
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>>.
Before proceeding, read Section 2.1, "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). Any additional content management repositories require a separate database user.
Note:
Oracle configuration settings can impact Content Search performance; for more information, see Section 3.3.5, "Improving Content Search Response Time for Oracle."
To configure an Oracle database:
Be sure to back up your database before installing any new database objects. See your database documentation for details.
Review the provided sample scripts, located in the <WLPORTAL_HOME>/portal/db/oracle/admin
directory. See Table 7-1 and the comments in the scripts for additional information.
Copy and modify the sample scripts appropriately for your environment to create each of the following database schemas:
Follow the steps in Section 7.2, "Configuring the Main WebLogic Portal Database" to create the main WebLogic Portal user and database objects.
If you want to create a separate behavior tracking database, follow the steps in Section 7.3, "Manually Creating a Separate Database and Database Objects for Behavior Tracking."
To configure the main WebLogic Portal database, follow these steps:
If you want to create new tablespaces for the main WebLogic Portal schema:
Copy the create_tablespaces.sql
script and modify it appropriately for your environment. See Table 7-1 and the comments in the script for additional information.
Run the modified create_tablespaces.sql
script. For example, from SQL*Plus:
@create_tablespaces.sql
Create the main WebLogic Portal database user:
Copy the create_users.sql
script and modify it appropriately for your environment. See Table 7-1 and the comments in the script for additional information.
Run the modified create_users.sql
script. For example, from SQL*Plus:
@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 Oracle Fusion Middleware Creating Domains Using the Configuration Wizard.
Open your domain's database.properties
file for edit.
Set database=oracle
.
Update the following settings (by replacing the @ symbols and the text between the symbols with the correct values) for your main WebLogic Portal database:
oracle.user=@DB_USER@ oracle.password=@DB_PASSWORD@ oracle.url=jdbc:bea:oracle://@DB_HOST@:@DB_PORT@;SID=@DB_NAME@
Create the database objects.
Navigate to the <MW_HOME>\user_projects\domains\
myPortalDomain
directory.
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.
Note:
After running create_db script, you need to update the database administrator password. See Section 3.8, "Note About Creating or Refreshing Database Objects" for detailed information.
Replace the JDBC data sources in your domain, which point to Derby 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 Oracle supports are available in the <WLPORTAL_HOME>\portal\db\jdbc\
database_driver
directory; for example, oracle_bea
or oracle_thin
. Follow the instructions in the <WLPORTAL_HOME>\portal\db\jdbc\README.txt
file.
For improved performance, move indexes to the WEBLOGIC_INDEX
tablespace by executing rebuild_indexes.sql
.
Note:
Do this while WebLogic Server is stopped.
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 Oracle Fusion Middleware Interaction Management Guide for Oracle WebLogic Portal.
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:
Modify the bt_create_tablespaces.sql
file and the bt_create_users.sql
file for your environment. See Table 7-1 and the comments in the script for additional information.
Run the modified bt_create_tablespaces.sql
script.
Run the modified bt_create_users.sql
script.
Navigate to the appropriate database directory based on your environment: <WLPORTAL_HOME>\portal\db\oracle
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
Run the following scripts from the path <WLPORTAL_HOME>\portal\db\data\required
:
bt_insert_system_data.sql
bt9_insert_system_data.sql
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
.
Table 7-1 describes the scripts that enable you to configure the Oracle database and create WebLogic Portal objects in that database.
Table 7-1 Database Scripts and Usage Notes
Script Name | Description |
---|---|
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 Typical names for the main WebLogic Portal database are:
Note: If you decide to create an additional content management repository, you must create another schema for it. For additional information, see the Oracle Fusion Middleware Content Management Guide for Oracle WebLogic Portal. |
rebuild_indexes.sql |
Rebuilds schema user ( |
statistics.sql |
Runs |
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 path names for the |
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:
|
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 Oracle Fusion Middleware Configuring and Managing JDBC for Oracle WebLogic Server.
The following sections address the various requirements and configuration choices when using Oracle RAC with WebLogic Portal. These include:
Section 7.5.2, "Configuration Considerations for Oracle RAC"
Section 7.5.3, "Procedure for Using Oracle RAC with WebLogic Portal"
For supported Oracle RAC and JDBC driver combinations, and for all the database configurations supported by WebLogic Portal, see "Oracle Fusion Middleware Supported System Configurations."
For each JDBC data source used by WebLogic Portal, the following configuration choices need to be considered:
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. For examples of how these are implemented, see "Using WebLogic Server with Oracle RAC" in Oracle Fusion Middleware Configuring and Managing JDBC for Oracle WebLogic Server.
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.
However, there are two datasources that can not be configured with multi-data source: p13nDataSource and cgDataSource-nonXA. If using these two datasources to access RAC, the favorable choice is to use the Oracle Thin Driver capability. For an example of how this configuration is done, refer to the "Using Connect-Time Failover without Global Transactions" section in Oracle Fusion Middleware Configuring and Managing JDBC for Oracle WebLogic Server.
Here is an example of the p13nDataSource using the Oracle Thin Driver to access RAC with load balancing and failover: ( the same example can be applied to cgDataSource-nonXA)
<?xml version="1.0" encoding="UTF-8"?> <jdbc-data-source xmlns="http://www.bea.com/ns/weblogic/jdbc-data-source"> <name>p13nDataSource</name> <jdbc-driver-params> <url>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS =(PROTOCOL =TCP)(HOST=VIP1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=VIP2)(PORT=1521))(FAILOVER=on)(LOAD_BALANCE=on)(CONNECT_DATA=(SERVER=DICATED)(SERVICE_NAME=RACSERVICE)))</url> <driver-name>oracle.jdbc.OracleDriver</driver-name> <properties> <property> <name>user</name> <value>WLP</value> </property> </properties> <password-encrypted>{3DES}daaYeadukVli0=</password-encrypted> </jdbc-driver-params> <jdbc-connection-pool-params> <initial-capacity>0</initial-capacity> <max-capacity>10</max-capacity> <test-connections-on-reserve>true</test-connections-on-reserve> <test-table-name>dual</test-table-name> <seconds-to-trust-an-idle-pool-connection>60</seconds-to-trust-an-idle-pool-connection> </jdbc-connection-pool-params> <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> <jndi-name>p13n.credentialsDataSource</jndi-name> <global-transactions-protocol>None</global-transactions-protocol> </jdbc-data-source-params> </jdbc-data-source>
In the above example of the p13nDataSource-jdbc.xml
, we see the following setting for the JDBC URL:
<url> jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=VIP1)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=VIP2)(PORT=1521)) (FAILOVER=on) (LOAD_BALANCE=on) (CONNECT_DATA=(SERVER=DICATED) (SERVICE_NAME=RACSERVICE))) </url>
VIP1
and VIP2
are the Virtual IP addresses of the RAC cluster. These VIP's are setup with Oracle RAC service points. Note that these VIP's are also used in the listener.ora
and tnsnames.ora
files on each RAC node. If your WebLogic machine cannot look up these names in the DNS or in the local hosts file, it is required to use IP addresses in the JDBC URL. In addition, if you use IP address in the JDBC URL, you must also use these IP addresses in the listener.ora
and tnsnames.ora
on each RAC node. In general, using IP addresses performs better because there is no lookup of the name to the IP, however using IP addresses causes for more administrative work over using names.
Failure to follow this configuration will result in unpredictable connections to the RAC nodes. It is a good idea to test this JDBC URL connection with a tool like SQL Developer.
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" in Oracle Fusion Middleware Configuring and Managing JDBC for Oracle WebLogic Server, the data source of a JDBC Store cannot use the load-balancing algorithm.
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" in Oracle Fusion Middleware Configuring and Managing JDBC for Oracle WebLogic Server, 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.
The supported RAC configurations are summarized in Table 7-2.
Table 7-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 |
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.
The configuration steps for Oracle RAC are very similar to those described in the previous sections Section 7.2, "Configuring the Main WebLogic Portal Database" and Section 7.3, "Manually Creating a Separate Database and Database Objects for Behavior Tracking." Each of these three sections includes seven steps.
If you want to create new tablespaces for the main WebLogic Portal schema:
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.
Copy the create_tablespaces.sql
script and modify it appropriately for your environment. See Table 7-1 and the comments in the script for additional information.
Run the modified create_tablespaces.sql
script. For example, from SQL*Plus:
@create_tablespaces.sql
Create the main WebLogic Portal database user:
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.
Copy the create_users.sql
script and modify it appropriately for your environment. See Table 7-1 and the comments in the script for additional information.
Run the modified create_users.sql
script. For example, from SQL*Plus:
@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 Oracle Fusion Middleware Creating Domains Using the Configuration Wizard.
Open your domain's database.properties
file for edit.
Set database=oracle
.
Update the following settings (by replacing the @ symbols and the text between the symbols with the correct values) for your main WebLogic Portal database:
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.
Create the database objects.
Navigate to the <MW_HOME>\user_projects\domains\
myPortalDomain
directory.
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.
Note:
After running create_db script, you need to update the database administrator password. See Section 3.8, "Note About Creating or Refreshing Database Objects" for detailed information.
Replace the JDBC data sources in your domain with multi-data sources pointing to your Oracle RAC.
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.
Alternatively, you could create them by copying and updating the sample jdbc.xml
files in the <WLPORTAL_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.
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.
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 Example 7-1. 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>
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 the WebLogic Portal 9.2 document Appendix B, Sample WLST Scripts at http://download.oracle.com/docs/cd/E13218_01/wlp/docs92/db/appx_oracle_rac_scripts.html
. To use the sample, follow the steps below:
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
.
Note:
In the following examples, the initial capacity is set to 0. This is done so that if one of the nodes is not available, you will not get error messages printed to the logs that the data source is not available. If you desire this information to the logs, set the value of the initial capacity to more than 0.
Example 7-1 portalDataSource Multi-Data Source
<?xml version='1.0' encoding='UTF-8'?> <jdbc-data-source xmlns="http://www.bea.com/ns/weblogic/jdbc-data-source"> <name>portalDataSource-RAC</name> <jdbc-data-source-params> <jndi-name>contentDataSource</jndi-name> <jndi-name>contentVersioningDataSource</jndi-name> <jndi-name>weblogic.jdbc.jts.commercePool</jndi-name> <jndi-name>portalFrameworkPool</jndi-name> <jndi-name>jdbc.portalFrameworkPool</jndi-name> <global-transactions-protocol>OnePhaseCommit</global-transactions-protocol> <algorithm-type>Load-Balancing</algorithm-type> <data-source-list>portalDataSource-1,portalDataSource-2</data-source-list> </jdbc-data-source-params> </jdbc-data-source>
Example 7-2 portalDataSource-1 Data Source
<?xml version='1.0' encoding='UTF-8'?> <jdbc-data-source xmlns="http://www.bea.com/ns/weblogic/jdbc-data-source"> <name>portalDataSource-1</name> <jdbc-driver-params> <url>jdbc:oracle:thin:@RACNODE1:1521:WLP1</url> <driver-name>oracle.jdbc.OracleDriver</driver-name> <properties> <property> <name>user</name> <value>WLP </value> </property> </properties> <password-encrypted>{3DES}daaYeadukVli0=</password-encrypted> </jdbc-driver-params> <jdbc-connection-pool-params> <initial-capacity>0</initial-capacity> <max-capacity>30</max-capacity> <test-connections-on-reserve>true</test-connections-on-reserve> <test-table-name>dual</test-table-name> <seconds-to-trust-an-idle-pool-connection>60</seconds-to-trust-an-idle-pool-connection> </jdbc-connection-pool-params> <jdbc-data-source-params> <jndi-name>portalDataSource-1</jndi-name> <global-transactions-protocol>OnePhaseCommit</global-transactions-protocol> </jdbc-data-source-params> </jdbc-data-source>
Example 7-3 portalDataSource-2 Data Source
<?xml version='1.0' encoding='UTF-8'?> <jdbc-data-source xmlns="http://www.bea.com/ns/weblogic/jdbc-data-source"> <name>portalDataSource-1</name> <jdbc-driver-params> <url>jdbc:oracle:thin:@RACNODE1:1521:WLP1</url> <driver-name>oracle.jdbc.OracleDriver</driver-name> <properties> <property> <name>user</name> <value>WLP </value> </property> </properties> <password-encrypted>{3DES}daaYeadukVli0=</password-encrypted> </jdbc-driver-params> <jdbc-connection-pool-params> <initial-capacity>0</initial-capacity> <max-capacity>30</max-capacity> <test-connections-on-reserve>true</test-connections-on-reserve> <test-table-name>dual</test-table-name> <seconds-to-trust-an-idle-pool-connection>60</seconds-to-trust-an-idle-pool-connection> </jdbc-connection-pool-params> <jdbc-data-source-params> <jndi-name>portalDataSource-2</jndi-name> <global-transactions-protocol>OnePhaseCommit</global-transactions-protocol> </jdbc-data-source-params> </jdbc-data-source>