Rittman Mead - Delivered Intelligence

One of the new features in Oracle Warehouse Builder 10gR2 "Paris" is
"Transportable Modules". Transportable Modules are source modules that get their
data from remote databases, with the data being brought into your warehouse
environment using either

transportable tablespaces if your source and target databases are Oracle 9i
or higher, or

data pump, if both source and target databases are 10g or higher. Note that
both source and target database have to be the same version (different releases
within the same version are OK though.)

The idea behind transportable modules is that they allow you to rapidly
replicate data between your source databases and warehouse environment, without
having to use flat files or manually set up FTP scripts. When you transport data
from an Oracle 9i schema, Oracle Warehouse Builder 10gR2 uses transportable
tablespaces to move your data, and when you’re on 10g or higher, you can either
still use transportable tablespaces, or you can use data pump, the successor to
imp/exp. So how does this work?

In this example, we’ve got a schema in a remote database called HRDATA that
contains a number of tables. We want to transport these tables into the
warehouse environment so that we can include them in the warehouse load. We
could just export them to flat files and copy them across, or even create a
database link, but in this case we want to replicate the schema into our
warehouse database and work on it from there. As both databases are 10g (the
source database is 10.1.0.2, the target is 10.2.0.1) we’ll use data pump to move
the data across.

Going into Oracle Warehouse Builder 10gR2, the first step then is to
right-click on the Transportable Modules node and select "New…"

Next, we name the module and provide a description:

The next step is to provide OWB with details on how to connect to the remote
database. The user that we connect as has to have the EXP_FULL_DATABASE and
ALTER_TABLESPACE roles – I connected as SYSTEM which had the former by default,
but I had to explicitly grant it the latter.

Note also the FTP username and password that it requires. The Transportable
Modules feature works by moving files around in the background via FTP, or at
least it does when you choose to move your data via transportable tablespaces -
when it uses data pump I expect it could just connect directly from source to
target, therefore not needing FTP, but I’m not sure if OWB implements it using
this quicker method. Also, when I first tried this, I connected as the schema
owner that contained the data, but as you have to grant EXP_FULL_DATABASE and
ALTER TABLESPACE, I thought it safer to leave the source schema with it’s
default permissions, and connect using SYSTEM instead.

Anyway, once you’ve entered the details for the source database, you can do
the same for the target. Again, I connected as SYSTEM here, as the transportable
module routine goes and creates the schema you’re going to load into anyway,
which will have the same name as the schema you’re loading from. Also, even
though I used the SYSTEM account, I had to grant a bunch of roles to the account
before I could use it as the target location:

SQL> grant connect to system;

Grant succeeded.

SQL> grant resource to system;

Grant succeeded.

SQL> grant imp_full_database to system;

Grant succeeded.

SQL> grant execute_catalog_role to system with admin option;

Grant succeeded.

SQL> grant create materialized view to system with admin option;

Grant succeeded.

SQL> grant alter tablespace to system;

Grant succeeded.

Note that the module isn’t going to load your source tables into the SYSTEM
schema, it’s just going to connect as SYSTEM when it brings data into your
target database; when you set the configuration options later on, you can define
the specific schema in your warehouse environment that you want to load data in
to.

Once you’ve set this up, the wizard looks like this:

Now that you’ve defined the source and target connections, the next step is
to pick the objects that you want to transport. The next dialog that comes up
gives you a listing of the tablespaces in the source database, and you can then
go and pick either a whole schema, or individual tables, to bring across. The
fact that your initial listing is of tablespaces is because the primary use for
this module is to facilitate transportable tablespaces, but it works just as
well if you’re going to use datapump, as we are.

Once you’ve completed all the details, OWB then goes away and
reads the source table metadata, so that you can work with it in your project.

At this point, there’s no source data in your target warehouse,
all you’ve done is define the sources and targets and brought across the table
metadata. The next step then is to deploy the module, but before you do that,
you need to set some configuration options, by right-clicking on the module
you’ve just created.

As we’re not going to use transportable tablespaces and use data
pump instead, I untick the "Transport Tablespaces" tickbox, and change "What To
Deploy" to "Tables Only" as I only want to bring across tables of data, not all
the other objects in the schema.

The second part to the configuration options lets you define
what local schema the replicated data will go into; in my case, I want to
redirect the data into a new schema called "HRDATA_SRC" which OWB will create
for me when I deploy the module.

You can of course specify a schema that’s already created, and
if I had a general "STAGING" schema I wanted to load data into, I could specify
it here. For what I’m doing though, I want to create a separate schema to hold
the replicated data, so I give it the name, password and default tablespace, set
the degree of parallelism for data pump, and press OK.

Now that the module has been defined, you can then view the
table metadata in the Design Center, like this:

… then finally, use the Control Center Manager to deploy the
module and copy the tables across.

Now, when I connect to the warehouse environment, I can use
SQL*Plus to check that the data is there.

SQL> conn src_hrdata/password@ora10g
Connected.
SQL> select table_name
  2  from   user_tables
  3  /

TABLE_NAME
------------------------------
EMPLOYEES
JOBS
JOB_HISTORY
COUNTRIES
REGIONS
DEPARTMENTS
LOCATIONS

7 rows selected.

SQL> select *
  2  from   job_history
  3  /

EMPLOYEE_ID START_DAT END_DATE  JOB_ID                         DEPARTMENT_ID
----------- --------- --------- ------------------------------ -------------
        102 13-JAN-93 24-JUL-98 IT_PROG                                   60
        101 21-SEP-89 27-OCT-93 AC_ACCOUNT                               110
        101 28-OCT-93 15-MAR-97 AC_MGR                                   110
        201 17-FEB-96 19-DEC-99 MK_REP                                    20
        114 24-MAR-98 31-DEC-99 ST_CLERK                                  50
        122 01-JAN-99 31-DEC-99 ST_CLERK                                  50
        200 17-SEP-87 17-JUN-93 AD_ASST                                   90
        176 24-MAR-98 31-DEC-98 SA_REP                                    80
        176 01-JAN-99 31-DEC-99 SA_MAN                                    80
        200 01-JUL-94 31-DEC-98 AC_ACCOUNT                                90

10 rows selected.

Just to recap then, the Transportable Module in OWB "Paris" lets
you bring in to your warehouse database, either via transportable tablespaces or
data pump, data from a remote Oracle database. The reason you’d use this, as
opposed to connecting via a database link, is that transportable tablespaces and
data pump are far quicker at bringing across large amount of data, and the data
then sits in your warehouse database, rather than you having to keep going back
and forth to the remote database.

In a few days time, I’m going to follow this up by taking a look at setting up Change Data
Capture with OWB "Paris".

Posted in Oracle Warehouse Builder | 3 Comments »