1. Preparation

We assume that on the 64-bit Windows host is already installed an Oracle Database 11g R2 (11.2.0.1.0) with SID= ORCL, a dedicated LISTENER that listens on the port 1521 and an ORACLE_HOME = E:\app\oracle\product\11.2.0\dbhome_1\

You will need the OWB Client Stand Alone version 11.2.0.3 for Windows 64 bit (13365470). Even though you may have a 64 bit database, the OWB client bundled there is not 64 bit.

You need to download this standalone client in order to run OWB 11.2 Design Center client when using a database hosted on a platform other than 32-bit Linux x86 or Microsoft Windows 32-bit client platforms. (Design Center is present in Oracle Database 11g Release 2 installations on other platforms, but is not functional and is not supported.)

2. Installation of OWB standalone client for Windows 64 bit

1. Copy the Installation file (OWB_11.2.0.3_Windows_x86-64.zip) under E:/Install and unzip it
2. Run the installer.exe and provide another Oracle Home.

E.g. Name: owbclient and Path: E:\app\oracle\product\11.2.0\owbclient\

Now under the Oracle Base: E:\app\oracle\product\11.2.0 We have 2 Oracle Homes:

• dbhome_1 -> our data warehouse Database
• owbclient -> our standalone owb client

Re-installation of the OWBSYS user

To avoid the OWB error MMM 1034:PROPERTY ENCRYPT DOES NOT EXIST by trying to insert source table metadata, you need to re-install the OWBSYS schema in order the repository version and client version to be the same.

1. Drop the old users who were assigned the role 'OWB_USER'
   • select * from dba_role_privs where granted_role like 'OWB_USER'; all these users except for sys and system will lose the role but they will not be dropped. (it is a good practice to drop them if you want to reuse them)
   • DROP USER XYZ CASCADE;
2. Start Menu -> r.click Computer -> Properties -> System Protection -> Advanced -> Environment Variables Edit the Path to show to the Oracle DB home bin directory: E:\app\oracle\product\11.2.0\dbhome_1\BIN (!!!without deleting the non-oracle entries)
3. Go to the UnifiedRepos location of the new installed home of the OWB client version (E.g. E:\app\oracle\product\11.2.0\owbclient\owb\UnifiedRepos directory) and execute these scripts connected via sqlplus as owbsys:

• @E:\app\oracle\product\11.2.0\dbhome_1\owb\rtp\sql\stop_service.sql (or go to Start -> Oracle Database home -> Warehouse Builder -> Administration -> Stop Control Center Service)
• @clean_owbsys
• @cat_owb > USERS
• @reset_owbcc_home > set the oracle home path of the new stand alone OWB client with opposite slices. E.g. E:/app/oracle/product/11.2.0/owbclient/

4. As sysadm unlock and change the passwords of owbsys and owbsys_audit

alter user owbsys identified by "*******" account unlock;
alter user owbsys_audit identified by "******" account unlock;

3. Setup a new Workspace

1. Open the Repository Assistant (Start menu -> Oracle Home (of the new OWB client) -> Warehouse Builder -> Administration -> Repository Assistant)
2. Provide the Database connection information (E.g. for the Instance ORCL)
3. Create a new Warehouse Builder Workspace (Select: Manage Warehouse Builder Workspaces -> CREATE A NEW WORKSPACE) 3.1 Create a WS with a New User as WS owner 3.2 Create a WS with an existing User as WS owner

To perform the first option, we will need to specify a database user who has DBA privileges that are required to be able to create a new user in the database. The second option is to specify an existing database user to become the OWNER OF THE WORKSPACE (= OWB REPOSITORY). This user must have the OWB_USER role assigned to be able to successfully designate it as a workspace owner. That is a database role required of any user who is to use the Warehouse Builder. The user specified here, whether new or existing, will become a deployment target for the Warehouse Builder. (login to the Design Center and Control Manager)

Taking option 1:

• Provide login credentials of system user
• Deselect all the optional Features in case you have license for Standard Edition
• WS Owner: Your new User. E.g. OWB_REPO
• Workspace name: Your new repository name. E.g. COMPANY_WS
• Provide OWBSYS credentials and set Tablespaces for the OWBSYS schema (for initializing the repository) (one-time process)(good practice is to create and assign everywhere different tablespaces [Data, Indexes, Temp, Snaphots (same as data)]) *Select Base Language (unchangeable) and Display Languages [display languages allow users to assign a business name to physical objects in their own language.] *Define Workspace Users (for each Developer)

4. Run again the Repository Assistant and Select: Manage Warehouse Builder workspace users
5. Login as the Workspace Owner (E.g. OWB_REPO) and select Register Warehouse Builder workspace users.
6. Create or select an existing user and click next and Finish

4. Solve the incompatibility issues if using the greater version of Client than the db.

1. In Oracle Home of the Client, under owb/bin/admin/ edit the Compatibily.Properties file to look like that:

ClientVersion = 11.2.0.1.0
MinReposVersion = 11.2.0.1.0
Features = 4335987675
MinRuntimeVersion = 11.2.0.1.0
MinRuntimePlatformVersion = 11.2.0.1.0
ADElabel = OWB_11.2.0.3.0.X64_GENERIC_111122.2145
OverrideRepositoryVersionCheck=true
OverrideRuntimeVersionCheck=true

2. Also edit the Preference.Properties by making sure you set OverrideRuntimeVersionCheck=true and OverrideRepositoryVersionCheck=true Under "#Locally Cached Preferences": For REPOS_DB_VERSION_ALLOWED property, the valid values are: Oracle 11g, Oracle 10g. You can list both to allow OWB connect to Repository on Oracle 10g and 11g. e.g. REPOS_DB_VERSION_ALLOWED=Oracle 11g, Oracle 10g

OverrideRuntimeVersionCheck=true
RECENT_LOGON_STRING_ARRAY1=[hr@localhost\:1521\:MZVDW]hr\:***\:localhost\:1521\:MZVDW\:\:hps
REPOS_DB_VERSION_ALLOWED=Oracle 10g, Oracle 11g
OverrideRepositoryVersionCheck=true

3. For the Control Center incompatibility Error you need to add the following line in Runtime.Properties

 property.RuntimePlatform.0.skip_version_check = true

5. Setup the Design Center on the Server side

1. Open the design center from the new standalone owb client home

IF you want to display your client in English: Edit the file: $ORACLE_HOME/ide/bin/ide.conf By adding the following line: AddVMOption -Duser.language=en

2. Logon to the Workspace with the Workspace owner (or the user) e.g. MZV_ETL

Open the basic Windows:

• View -> Projects Navigator (metadata on design owb objects, sources and targets)
• View -> Locations Navigator (define connections for sources and targets - for the entire workspace and not just the project)
• View -> Global Navigator (manages objects that are common to all projects in a workspace. e.g. Public Transformations or Public Data Rules)

3. Rename the default Project MY_PROJECT e.g. to MY_ETL

4. Create a Module [data source or Warehouse target] necessary to create a module to hold our source metadata. It is an object in the Design Center that acts as a storage location for the various definitions and help us logically group them. (e.g. File modules or Database modules (oracle and non-oracle))

5. Import Source Metadata (Tables) In the Project Explorer, r.click the source Module -> Import give the password for the connection, test and the Import Metadata Wizard will start

   • Select level to check for FK dependencies
   • The already imported tables will be displayed in bold but we can reimport them to pick up any changes

6. Create target user and module lets say the target schema is MZV_ETL Check in Global Explorer -> Security -> users (you see the users granted the role OWB_USER) Create a new db Module Name: Staging Edit the Location (MZV_ETL) etc...

---

Useful scripts

sripts to start/stop service and service doctor are: owbclient_home\owb\rtp\sql

select service_name from WB_RT_SERVICE_NODES; select * from WB_RT_SERVICE_JOB_LOGS;

select OWNER, OBJECT_NAME, OBJECT_TYPE from ALL_OBJECTS where status='INVALID' ;

EXEC DBMS_UTILITY.compile_schema(schema => 'OWBSYS');

---

Deploying Mappings Issues

If the source module db is the same as the target, then in the genaration of the mapping scripts, owb does not use a database link and therefore you must grant select privilleges on the Target schema for the source tables.

e.g. grant select on hr.employees to MZV_ETL;