4 Changing the Default Oracle DataLens Server Database

This chapter describes how to prepare for and change the default configuration database that holds EDQP metadata in the supported operating system environments.

The Derby database that is installed with your Oracle DataLens Administration Server contains all of the operational information for the server, such as jobs, users, tasks, DSAs, data lenses, FTP and database connections, and other configuration information. Derby is supported only in a development environment; Derby is not supported for production.

If you plan to install the EDQP server in a production environment, you should change the Derby database to another supported database (see Section 2.3.1, "Hardware and Software Requirements"), such as an Oracle Database to create a production environment with optimal system performance and maximum system security of your Oracle DataLens Administration Server. Oracle recommends that you make this change immediately after installation if the server is going to be used in a production environment.

If you do not change the database immediately after installation, you will need to reestablish all configuration settings and assets (data lenses and DSAs) in the new database on your Oracle DataLens Administration Server. You will then need to redeploy all assets to all Oracle DataLens Transform Servers in your topology.

You must compete the following sections of this chapter to totally change the Derby database to another supported database:

• Section 4.1, "Preparing to Change the Default Oracle DataLens Server Derby Database"

• Section 4.2, "Changing the Derby Database to an Oracle DataBase or PostgreSQL Database"

• Section 4.3, "Configuring the EDQP WebLogic Server Domain to Use the PDQRespository Data Source"

• Section 4.4, "Autodeploying Data Lenses to the New Database"

4.1 Preparing to Change the Default Oracle DataLens Server Derby Database

You must ensure that the following requirements are observed:

• Ensure that one of the following supported database products is installed on your Oracle DataLens Administration Server.

  Oracle Database

  You can obtain Oracle Database from the Oracle Technology Network at

  http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

  PostgreSQL Database

  You can obtain PostgreSQL Version 9.2.x from the PostgreSQL website at

  http://www.postgresql.org/

  You must obtain the associated JAR file, postgresql-9.2-1002.jdbc4.jar, from the PostgreSQL JDBC Driver website at

  http://jdbc.postgresql.org/download.html

  Install and configure this driver as described in "Configuring JDBC Database Drivers."

  Note:

  Previous versions of PostgreSQL are not supported including the associated JAR files.

• Source your installed database environment for your administrator user.

• Ensure that your database is configured to use UTF-8 encoding if you want to store non-Latin characters.

  Caution:

  If your database is not configured as UTF-8, then some characters may not be rendered correctly due to encoding differences.

4.2 Changing the Derby Database to an Oracle DataBase or PostgreSQL Database

Use one of the following sections to change your Oracle DataLens Administration Server Derby Database to an Oracle Database or PostgreSQL Database on any of the supported operating systems:

Note:

In the sample console text and directory path names provided in this section, Linux/UNIX conventions (such as forward slashes in path names) are used, for example, /MW_HOME. When entering path names on a Windows system, be sure to use Windows conventions. For example, use backslashes in path names, such as C:\MW_HOME.

Note:

The following database schema creations only require that a DBA use a specific database administration user with sufficient privileges and run the associated data definition language (DDL) provided by EDQP to create the new schema on the targeted production database. The following sections are examples of how to create the schema on an existing database that resides on your Oracle DataLens Administration Server.

4.2.1 Creating the Oracle Database PDQRespository Schema

1. Login to your Oracle database server using the Oracle user account. Consult your database administrator if you need assistance adding and modifying user accounts.

   For Windows systems, open a command window with cmd.exe.

2. Enter sqlplus /nolog to start SQL*Plus.

3. Enter the following commands at the SQL> prompt to create the dlsadmin user and grant the necessary privileges:

   SQL> CONNECT / AS SYSDBA

   SQL> CREATE USER dlsadmin INDENTIFIED BY dlsadmin1;

   SQL> ALTER USER dlsadmin QUOTA UNLIMITED ON USERS;

   SQL> GRANT CONNECT, CREATE TABLE, CREATE SEQUENCE TO dlsadmin;

   SQL> EXIT;

4. Change directories to the /MW_HOME/user_projects/domains/dls_domain/opdq/data/repository/ddl_scripts directory.

5. Enter sqlplus dlsadmin/dlsadmin1 to log into SQL*Plus.

   Note:

   Messages similar to the following appear when the dlsadmin user cannot connect to SQL*Plus:

   Error accessing PRODUCT_USER_PROFILE
   Warning:  Product user profile information not loaded!
   You may need to run PUPBLD.SQL as SYSTEM
   

   Enter the following commands to correct this issue:

   sqlplus /nolog

   SQL> CONNECT / AS SYSDBA

   SQL> @$ORACLE_HOME/sqlplus/admin/PUPBLD.SQL

   SQL> EXIT;

   sqlplus dlsadmin/dlsadmin1

6. Enter the following commands at the SQL> prompt:

   @repository_oracle_ddl.sql

   exit;

4.2.2 Creating the PostgreSQL Database PDQRespository Schema

1. Login to your PostgreSQL database server using the database superuser (postgres) account. Consult your database administrator if you need assistance adding and modifying user accounts.

2. Start PostgreSQL using one of the following:

   On Linux and UNIX, enter /var/lib/postgres -D pgsql/data & to start PostgreSQL.

   On Windows servers, start the PostgreSQL SQL Shell (psql) by clicking Start, selecting All Programs, then selecting PostgreSQL 9.x, and then select SQL Shell (psql). Enter the server, database, and database superuser credentials.

3. Edit the pg_hba.conf file, located in the data directory of your PostgreSQL installation, to ensure that the database you will create can connect on the network:

   On Linux and UNIX, ensure that the method is set to password and the host scsdata scs line is updated with your IP address and subnet mask as in the following:

   # TYPE DATABASE USER CIDR-ADDRESS METHOD
    
   # "local" is for Unix domain socket connections only
   local all all password
   # IPv4 local connections:
   host all all 127.0.0.1/32 password
   
   #Line is added so that it gets connected to admin server;
   host scsdata scs <network_ip_address/subnet_mask> trust
   
   # IPv6 local connections:
   host all all ::1/128 password
   

   On Windows, ensure that the method is set to md5 and the host scsdata scs line is updated with your IP address and subnet mask as in the following:

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
    
   # IPv4 local connections:
   host    all             all             127.0.0.1/32            md5
   
   #Line is added so that it gets connected to admin server;
   host    scsdata          scs   <network_ip_address/subnet_mask> trust
   
   # IPv6 local connections:
   host    all             all             ::1/128                 md5
   # Allow replication connections from localhost, by a user with the
   # replication privilege.
   #host    replication     postgres        127.0.0.1/32            md5
   #host    replication     postgres        ::1/128                 md5
   

4. On Linux and UNIX, edit the postgresql.conf and ensure that the host and connection information matches your edits in the pg_hba.conf file in the previous step.

5. Restart PostgreSQL:

   On Linux and UNIX, enter /etc/init.d/postgresql restart.

   On Windows, enter C:\Windows\system32\cscript.exe //NoLogo "C:\Program Files\PostgreSQL\9.2\scripts\serverctl.vbs" reload wait.

6. Enter the following commands at the postgres=> prompt to create the scs user and grant the necessary privileges for the appropriate database:

   postgres=> CREATE USER SCS WITH PASSWORD '1REALM1';

   postgres=> CREATE DATABASE SCSDATA OWNER=SCS;

   postgres=> GRANT ALL PRIVILEGES ON DATABASE SCSDATA TO SCS;

   postgres=> \q

7. Restart PostgreSQL specifying use of the scsdata database:

   On Linux and UNIX, enter psql -d scsdata -U scs –W.

   On Windows, start the PostgreSQL SQL Shell (psql) by clicking Start, selecting All Programs, then selecting PostgreSQL 9.x, and then select SQL Shell (psql). Enter the server, scsdata as the database, scs as the user, and 1REALM1 for the password.

8. Enter one of the following commands at the scsdata=> prompt to create the required objects:

   On Linux and UNIX:

   scsdata=> \i /MW_HOME/user_projects/domains/dls_domain/opdq/data/repository/ddl_scripts/repository_postgres_ddl.sql

   On Windows:

   scsdata-> #\i '\\ML_HOME\\user_projects\\domains\\dls_domain\\opdq\\data\\repository\\ddl_scripts\\repository_postgres_ddl.sql'

   Note:

   Ensure that your ML_HOME directory path includes double backslashes as in the rest of the command. For example, C:\\oracle\\middleware.

9. Verify that you can list the tables in the database, which verifies that the server and database can be accessed:

   scsdata=> \dt

   scsdata=> \q

10. Edit the following file:

    On Linux and UNIX, edit the /WL_HOME/common/bin/commEnv.sh file and add ${CLASSPATHSEP}${WL_HOME}/server/lib/postgresql-9.1-902.jdbc4.jar to the end of the WEBLOGIC_CLASSPATH variable.

    On Windows, edit the /WL_HOME\common\bin\commEnv.cmd file and add ;%WL_HOME%\server\lib\postgresql-9.2-1002.jdbc4.jar to the end of the WEBLOGIC_CLASSPATH variable.

    For more information about configuring the database driver, see "Configuring JDBC Database Drivers."

11. Restart your WebLogic Server to implement these changes. See Oracle Fusion Middleware Oracle WebLogic Server Administration Console Online Help 11g Release 1 (10.3.6) at

    http://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/taskhelp/startstop/StartAndStopServers.html

4.3 Configuring the EDQP WebLogic Server Domain to Use the PDQRespository Data Source

The PDQRespository data source must be configured for each server in your EDQP topology so you must run the following steps for each of your Oracle DataLens Servers.

1. Ensure that your WebLogic Server is running on your Oracle DataLens Administration Server.

2. Open a supported web browser and enter the following URL:

   http://hostname:port/console

   where hostname is the DNS name or IP address of the Oracle DataLens Administration Server and port is the listen port on which the server is listening for requests (port 2229 by default).

   If you configured the Oracle DataLens Administration Server to use Secure Socket Layer (SSL) you must add s after http as follows:

   https://hostname:port/console

3. When the login page appears, enter the user name and the password you used to start the server, and then click Login.

4. Delete the PDQRepository JDBC data source. See Oracle Fusion Middleware Oracle WebLogic Server Administration Console Online Help 11g Release 1 (10.3.6) at

   http://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/taskhelp/jdbc/jdbc_datasources/DeleteDataSources.html

5. A new PDQRepository data source that configures the new database connectivity is necessary. Adding a new JDBC Generic data source is simple using the corresponding multi-page process.

   Review this process before beginning in the Oracle Fusion Middleware Oracle WebLogic Server Administration Console Online Help 11g Release 1 (10.3.6) at

   http://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/taskhelp/jdbc/jdbc_datasources/CreateDataSources.html

   Complete the Create a New JDBC Data Source pages as follows:

   No.	Screen	Perform the Following Action
   1	JDBC Data Source Properties	Enter PDQRepository in the Name and JNDI name fields. For an Oracle Database—Select Oracle from the Database Type list. For a PostgreSQL Database—Select PostgreSQL from the Database Type list. Click Next.
   2	JDBC Data Source Properties	For an Oracle Database—Select Oracle's Driver (Thin XA) for Instance connections; Versions:9.0.1 and later from the Database Driver list. For a PostgreSQL Database—Select PostgreSQL's Driver (Type 4)Versions;Any from the Database Driver list. Click Next.
   3	Transaction Properties	Click Next.
   4	Connection Properties	For an Oracle Database: Enter the name of the Oracle Database instance (SID) where the dlsadmin user account was created in the Database Name: field. Enter the host name of your Oracle Database Server in the Host Name: field. Enter port number of your Oracle Database in the Port: field. Enter dlsadmin in the Database User Name field. Enter and confirm dlsadmin1 in the password fields. Click Next. For a PostgreSQL Database: Enter the scsdata in the Database Name: field. Enter the host name of your PostgreSQL Database Server in the Host Name: field. Enter port number of your PostgreSQL Database in the Port: field. Enter scs in the Database User Name field. Enter and confirm 1REALM1 in the password fields. Click Next.
   5	Test Database Connection	Click Test Configuration to ensure that your database connection operates correctly. Make any necessary changes until the connection is successful. Click Next.
   6	Select Targets	Select the AdminServer check box to deploy the new data source on that server. Click Finish.

   
   

6. Navigate to the new PDQRepository data source. See Oracle Fusion Middleware Oracle WebLogic Server Administration Console Online Help 11g Release 1 (10.3.6) at

   http://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/taskhelp/jdbc/NavigateToJdbcSystemResource.html

7. Select the Row Prefetch Enabled check box, and click Save.

8. Stop your WebLogic Server. See Oracle Fusion Middleware Oracle WebLogic Server Administration Console Online Help 11g Release 1 (10.3.6) at

   http://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/taskhelp/startstop/StartAndStopServers.html

9. Modify the setDomainEnv file on your Oracle DataLens Administration Server only to set the Derby database parameter to false:

   Note:

   This step is not applicable to Oracle DataLens Transform Servers because the Derby database parameter is set to false during installation.

   Platform	Actions
   Linux and UNIX	Edit the /MW_HOME/user_projects/domains/dls_domain/bin/setDomainEnv.sh file. Search for the first instance of DERBY_FLAG as follows: DERBY_FLAG="true" export DERBY_FLAG enableHotswapFlag="" export enableHotswapFlag PRODUCTION_MODE="" export PRODUCTION_MOD There are several instances of this flag in the file and you must ensure that you modify only this one. Change "true" to "false": DERBY_FLAG="false" Save and close the file.
   Windows	Edit the \MW_HOME\user_projects\domains\dls_domain\bin\setDomainEnv.cmd file. Search for the first instance of DERBY_FLAG. set DERBY_FLAG=true set enableHotswapFlag= set PRODUCTION_MODE= There are several instances of this flag in the file and you must ensure that you modify only this one. Change true to false (without quotes) set DERBY_FLAG=false Save and close the file.

   
   

10. Rename the /EDQP_HOME/config/ServerProfiles.xml.bak file to /EDQP_HOME/config/ServerProfiles.xml.

11. Start your WebLogic Server. See Oracle Fusion Middleware Oracle WebLogic Server Administration Console Online Help 11g Release 1 (10.3.6) at

    http://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/taskhelp/startstop/StartAndStopServers.html

4.4 Autodeploying Data Lenses to the New Database

The EDQP Smart Glossaries delivered with EDQP are automatically deployed (autodeployed) to the standard Derby database so when you change to a new database you must autodeploy these assets for use in that database.

To autodeploy the EDQP Smart Glossaries:

1. On your Oracle DataLens Administration Server system, log in using the administrator user you established when installing the server in preparation to copy the necessary files.

   Note:

   This step does not refer to the Oracle DataLens Administration Server web page.

2. Copy the MW_HOME\edqp_template1\autodeploy\lens directory that you extracted from the product package to the WebLogic dls_domain directory on your Oracle DataLens Administration Server. By default, these directories are:

   On Linux and UNIX: MW_HOME/user_projects/domains/dls_domain/opdq/autodeploy/lens

   On Windows: MW_HOME\user_projects\domains\dls_domain\opdq\autodeploy\lens

   See Section 2.4, "Selecting Directories for Installation".

   This autodeploys the EDQP Smart Glossaries to your Oracle DataLens Server. The Oracle DataLens Administration Server polls the autodeploy directory every 10 minutes and attempts to autodeploy the data lens template files that are placed in the respective folders under the autodeploy parent folder.

3. Log out of the Oracle DataLens Administration Server system.

4. Wait 10 minutes to ensure that the server templates have been autodeployed.

5. Log into the Oracle DataLens Server Administration web page.

   For more information about these web pages, see Oracle Enterprise Data Quality for Product Data Oracle DataLens Server Administration Guide.

6. Verify that the DSAs and data lens contained in the MW_HOME\edqp_template1\autodeploy\ directory have been autodeployed.