Oracle Warehouse Builder OWB 11g New Features Part 6!

This is a continuation from part 5. In part 6, we will discuss a bit more about the installation of OWB 11g, and the install options. Again, thanks to Oracle for providing some of this information to make my posting easier!

Server Install Provides Full Control Center Functionality

The Oracle home of the Oracle Database 11g installation includes the components needed to perform the following tasks for OWB:

• Deployment and execution of all standard objects
  (tables, dimensions, cubes, maps, and so on)
• Deployment of process flows
• Warehouse upgrade
• Run-time scripting
• Installation of Oracle Workflow

The OWB server installation includes a small set of files needed to get the Control Center running in the database Oracle home. This allows a “fully functioning” OWB install directly with the database install.

The directory structure looks like this:

• Oracle Home/owb
• Oracle Home/owb/wf

Discoverer deployment is the only task not supported by the OWB server install. For Discoverer deployment, you must perform the stand-alone installation described later.

Stand-Alone..When Do You Need Stand-alone Installation?

The OWB 11g stand-alone installation is required only if you must:

• Deploy to an Oracle Database 10g, release 2 target
• Perform Discoverer deployment

The stand-alone DVD is actually bundled with the 11g database pack.

For full functionality, OWB 11g stand-alone must be installed on the database server machine (10g R2 or 11g) into a new Oracle home (separate from the database home).

Using OWB 11g with DB 10g R2

OWB 11g is architected on the assumption that it will be used with Oracle Database 11g. This tight integration includes pre-seeding of the OWBSYS schema in the default database and placing OWB in the same Oracle home as the database.

To use OWB 11g with Oracle Database 10g R2, you must perform a few simple steps:

1. Run SQL script to create OWBSYS.

2. Run SQL script to identify OWB home to Oracle Database 10g R2.

3. Unlock OWBSYS.

4. Enable access to workspaces.

1. Run SQL script to create OWBSYS.

When using Oracle Database 10g R2, you must run a SQL script to create the OWBSYS repository schema that is needed by OWB 11g. (OWBSYS is automatically created during Oracle Database 11g installation, so this step is not needed when using OWB 11g with Database 11g.)

The script /OWB/UnifiedRepos/cat_owb.sql installs the OWBSYS database user that is otherwise automatically created when installing Database 11g.

Note: The method by which you invoke SQL*Plus is important. Do not invoke SQL*Plus from the Start > Programs > Oracle database home folder or the Warehouse Builder folder. Instead, invoke SQL*Plus from Start > Run > cmd.exe. Type the Path command and press Enter.
Verify that Warehouse Builder is near the front of the path statement so that the SQL*Plus session invokes from the Warehouse Builder installation. If not, you may need to temporarily set the path (such as Path=\bin). Then invoke SQL*Plus as SYS with SYSDBA privileges. For example, at the command line you can type the following:
sqlplus sys/ as sysdba

Connect to SQL*Plus as SYSDBA and issue the following command to create OWBSYS:

@/owb/UnifiedRepos/cat_owb.sql;

Press Enter. You are prompted for a tablespace for the OWBSYS user. Type a table such as users and press Enter.

When this command finishes successfully, you are prompted: “If you are NOT using an OWB installed in the Oracle database home, please now run reset_owbcc_home”.

2. Run SQL script to identify OWB home to Oracle Database 10g R2.

Earlier, you were instructed to install Warehouse Builder in an Oracle home other than the database Oracle home if you are using OWB 11g with Oracle Database 10g R2. To ensure access to the Control Center on the Oracle Database 10g R2 database, run the reset_owbcc_home.sql script and pass in your Oracle home for Warehouse Builder.
Run the script as a system privileged user such as SYS or SYSTEM. For example, type the following command at the command prompt:

SQL> @c:\oracle\OWB_home_11g\owb\UnifiedRepos\reset_owbcc_home;

Press Enter. You are prompted for the full path of the Oracle home for the OWB Control Center install. First read the following note.

Note: In the following example, observe the required use of the forward slashes (/) even when using a Windows machine. Note also that your response with the full path must be case sensitive. Be sure to enter the drive letter in uppercase (such as C:) and follow the case sensitivity of the folder names in the path of the Oracle home for OWB. Your response should be similar to the following example:

C:/oracle/OWB_home_11g

Press Enter.

Important note: If you specify the path to the OWB Oracle home incorrectly (using backward slashes or incorrect case sensitivity), the reset_owbcc_home.sql script finishes without apparent errors. However, when you later use the Repository Assistant to create a workspace and users, the process fails.

3. Unlock OWBSYS.

After creating OWBSYS, you must unlock the OWBSYS account and reinstate its password if the strong password option is enabled on the database. Proceed with the SQL*Plus commands to unlock the OWBSYS account and assign a password (also named OWBSYS):

alter user OWBSYS account unlock;

alter user OWBSYS identified by OWBSYS;

The requirements explained above are also mentioned in the OWB 11g Installation Guide documentation, in the section titled “Hosting the Repository on Oracle Database 10g Release 2.”

For each Warehouse Builder client installation, enable access to the workspaces hosted on your Oracle Database 10g R2 database.

4. Enable Access to Workspaces

By default, Warehouse Builder 11g, release 1 clients are set to connect to workspaces hosted on an Oracle 11g database. That is, the Warehouse Builder repository is assumed to be hosted on an Oracle 11g database.

To enable access to an Oracle Database 10g R2 repository and its workspaces, alter the file /owb/bin/admin/Preference.properties. If the file does not exist, you can create it based on the example file available in the same directory, Preference.properties.tmp.

In the preference properties file, add the REPOS_DB_VERSION_ALLOWED property and set its value to Oracle 10g, Oracle 11g. (This property may already be present.)

And that's all for part 6. Hopefully, this allows users to get OWB 11g installed in their preferred format! Thanks again to Oracle for providing some of the info contained above. If you have any questions, feel free to drop me a line. Part 7 will cover Repository Assistant Workspace Changes, and how logging in, managing users and targets, and other features have changed!