Setup Oracle Streams - Datapump Instantiation (Part 1) - Step by Step

Today,  I will continue from my previous post Instantiation - Oracle Streams Basics which talked about basics of instantiation and its role in replication using Streams. This post will cover details on setting up environment for replication between two databases, one being source containing schemas to be replicated, second one being blank destination database.
 

Prerequisites:

1. Source database: We already have it, an Oracle 11gR2 Enterprise Edition instance. Lets call it PROD.EXAMPLE.COM

2. Destination database: Install an Oracle 11gR2 Standard Edition software at destination server and setup a database with default tablespaces (SYSTEM, SYSAUX, UNDO etc). Lets call it REPL.EXAMPLE.COM

3. Oracle Net: Configure Oracle Net (listener.ora and tnsnames.ora) between two databases. Test connectivity by connecting as SYS or any database user. I configured TNS alias PROD for PROD.EXAMPLE.COM and REPL for REPL.EXAMPLE.COM

4. Update Instance Parameter File: Update instance pfile/spfile with below parameters set to recommended values:

compatible           : Should be same as database version (highest possible)

global_names         : Must be true

open_links           : 4 or higher

processes            : 100 or higher

sessions             : (1.5 x processes) + 22

Setting global_names to true is mandatory as Oracle Streams will use global names of source and destination databases respectively for identification.

5. Archivelog Mode: Both source and destination databases must be in ARCHIVELOG mode

$ sqlplus / as sysdba
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
-- If output shows "ARCHIVELOG" then move on to step 5, otherwise follow below steps
Set log_archive_dest and log_archive_format parameters
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
-- Archivelog should be now enabled

6. Schemas Excluded: Remember SYS, SYSTEM and CTXSYS and other SYS related schemas are excluded by Streams, so make sure not to create any normal user related objects under these.

7. Unsupported Objects: Streams would not capture changes from specific data types / columns / change types etc. To know of such objects in database use below query:

SQL> SELECT * FROM DBA_STREAMS_UNSUPPORTED;

Note: Get specific details on Oracle Streams Restrictions from Oracle.

Setup Environment

Streams environment consists of a dedicated Streams Administrator user. This user will be configured to run capture process and propagation on source and apply process on destination. The user is specifically provided its own default tablespace to keep any relevant objects, this also makes cleanup job easier if Streams are to be removed from database. The user is also assigned set of privileges along with DBA and Streams Admin privilege

 

Connect to Source Database

Prepare directory for keeping datapump export from source database
$ mkdir /tmp/src_dp
$ sqlplus / as sysdba
SQL> CREATE DIRECTORY STREAM_SRC AS '/tmp/src_dp';

Create Tablespace for STREAMADM user - This user will act as Streams Administrator
SQL> CREATE TABLESPACE STREAM_TBS DATAFILE '/<location>/stream_tbs01.dbf' SIZE 25M AUTOEXTEND ON;

Create STREAMADM user
SQL> CREATE USER STREAMADM IDENTIFIED BY STREAMADM DEFAULT TABLESPACE STREAM_TBS QUOTA UNLIMITED ON STREAM_TBS;
SQL> GRANT CONNECT, RESOURCE, DBA, SELECT_CATALOG_ROLE TO STREAMADM;
SQL> BEGIN
     DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
                  GRANTEE=> 'STREAMADM',
                  GRANT_PRIVILEGES => TRUE);
     END;
     /
SQL> GRANT ALL ON DIRECTORY STREAM_SRC TO STREAMADM;

Create Database Link
SQL> CONNECT STREAMADM/STREAMADM
SQL> CREATE DATABASE LINK REPL.EXAMPLE.COM CONNECT TO STREAMADM IDENTIFIED BY STREAMADM USING 'REPL';

Connect to Destination Database

Prepare directory for keeping datapump export from source database
$ mkdir /tmp/dest_dp
$ sqlplus / as sysdba
SQL> CREATE DIRECTORY STREAM_DEST AS '/tmp/dest_dp';

Create Tablespace for STREAMADM user - This user will act as Streams Administrator
SQL> CREATE TABLESPACE STREAM_TBS DATAFILE '/<location>/stream_tbs01.dbf' SIZE 25M AUTOEXTEND ON;

Create STREAMADM user

SQL> CREATE USER STREAMADM IDENTIFIED BY STREAMADM DEFAULT TABLESPACE STREAM_TBS QUOTA UNLIMITED ON STREAM_TBS;
SQL> GRANT CONNECT, RESOURCE, DBA, SELECT_CATALOG_ROLE TO STREAMADM;
SQL> BEGIN
      DBMS_STREAMS_AUTH.GRANAT_ADMIN_PRIVILEGE(
           GRANTEE=> 'STREAMADM',
           GRANT_PRIVILEGES=> 'TRUE');
      END;
      /
SQL> GRANT ALL ON DIRECTORY STREAM_SRC TO STREAMADM;

Create Database Link

SQL> CONNECT STREAMADM/STREAMADM
SQL> CREATE DATABASE LINK PROD.EXAMPLE.COM CONNECT TO STREAMADM IDENTIFIED BY STREAMADM USING 'PROD';
This completes setting up of environment for Oracle Streams. In Part 2, I will post details on setting-up replication between these two databases.