Oracle Stream Setup in 11GR2

Dear All,

Oracle Streams configuration with 11gr2

OS: Windows 2003

Oracle: 11GR2

Source Database: DB01

Target Database: DB02

SCHEMAS used in stream: scott

Type of replication environment : bi - directional replication in a two database replication 

environment

Step:

Note: 

1. Enable archivelog mode on source/target database

2. unlock scott schema (by default it is locked)

1. configure an oracle streams administrator on all databases

1.1 create tablespace (optional)

1.2 create user

1.3 grant privileges

2. configure network connectivity and database links

2.1 edit TNSNAMES.ORA file for target and source database

2.2 create database links on both databases ( source/ target)

3. setting init parameter relevant to oracle streams

3.1 set global_names= true parameter

4. specifying supplemental logging

4.1 add supplemental log on all tables

5. configure two-database schema replication with local capture

This example configures an oracle streams replication environment that replicates DML changes to 

all of the tables in the scott schema. this example configures a two-database replications 

environment with local captuer processes to capture changes.

5.1 creating the required directory objects

5.2 connect to db01 and run MAINTAIN_SCHEMAS procedure

6. configure latest time conflict resolution (cover in next post )

C:\>rem enable archive log mode on target/source db

C:\>set oracle_sid=db01

C:\>sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 15 13:07:29 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1384752 bytes

Variable Size             348131024 bytes

Database Buffers          180355072 bytes

Redo Buffers                5791744 bytes

Database mounted.

SQL> alter database archivelog ;

Database altered.

SQL> alter database open;

Database altered.

SQL> --unlock scott schema

SQL> alter user scott account unlock;

User altered.

SQL> conn scott/tiger

ERROR:

ORA-28001: the password has expired

Changing password for scott

New password:

Retype new password:

Password changed

Connected.

SQL>

################## repeat the above steps on DB02 ###################

SQL> conn sys/oracle@db01 as sysdba

Connected.

SQL> --create new tablespace

SQL> create tablespace STREAM

  2  datafile 'c:\stream01.dbf' size 25m

  3  autoextend on;

Tablespace created.

SQL> --create user for stream

SQL> create user STRMADMIN

  2  identified by strmadmin;

User created.

SQL> --grant previleges to stream administrator user

SQL> grant DBA to strmadmin;

Grant succeeded.

SQL> begin

  2  dbms_streams_auth.grant_admin_privilege (

  3  grantee =>'strmadmin',

  4  grant_privileges => TRUE);

  5  end;

  6  /

PL/SQL procedure successfully completed.

################## repeat the above same step on DB02 ###################

db02 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.104.23)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = db02)

    )

  )

db01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.104.23)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = db01)

    )

  )

Add above lines to TNSNAMES.ORA file ( replace the HOST and SERIVCE_NAME as per your 

environment)

########################################################################

SQL> --create db link on target db i.e. DB01

SQL> conn strmadmin/strmadmin@db01

Connected.

SQL> create database link db02

  2  connect to strmadmin

  3  identified by strmadmin

  4  using 'db02';

Database link created.

SQL> --create db link on target db i.e. DB02

SQL> conn  strmadmin/strmadmine@db02 

Connected.

SQL> create database link db01

  2  connect to strmadmin

  3  identified by strmadmin

  4  using 'db01';

Database link created.

########################################################################

SQL> conn strmadmin/strmadmin@db01

Connected.

SQL> --setup init parameter

SQL> alter system set global_names=true;

System altered.

SQL> conn strmadmin/strmadmin@db02

Connected.

SQL> alter system set global_names=true;

System altered.

#######################################################################

SQL> conn strmadmin/strmadmin@db01

Connected.

SQL> --specifying supplemental logging

SQL> --scott schema has FOUR table i.e. emp/dept/bouns/salgrade

SQL> alter table scott.emp add supplemental log data (all) columns;

Table altered.

SQL> alter table scott.dept add supplemental log data (all) columns;

Table altered.

SQL> alter table scott.bonus add supplemental log data (all) columns;

Table altered.

SQL> alter table scott.salgrade add supplemental log data (all) columns;

Table altered.

################## repeat the above same step on DB02 ###################

SQL> conn strmadmin/strmadmin@db01

Connected.

SQL> create directory dir_db01 as 'c:\tmp\db01';

Directory created.

SQL> --create destination direcotory on db02(target db)

SQL> conn strmadmin/strmadmin@db02

connected.

SQL> create directory dir_db02 as 'c:\tmp\db02';

Directory created.

################## ############################# ###################

connect with stream administrator and run maintain_schemas procedure on source db (db01)

SQL> --create to the source database (db01) with stream administrator user

SQL> --execute the following procedure

SQL> conn strmadmin/strmadmin@db01

Connected.

SQL> BEGIN

  2    DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(

  3      schema_names                 => 'scott',

  4      source_directory_object      => 'dir_db01',

  5      destination_directory_object => 'dir_db02',

  6      source_database              => 'db01',

  7      destination_database         => 'db02',

  8      include_ddl                     => TRUE,

  9      instantiation           => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA,

 10      bi_directional               => TRUE); -- Set to TRUE for bi-directiona

l

 11  END;

 12  /

PL/SQL procedure successfully completed.

################## ############################# ###################

let check the stream is configured properly or not.

add new employee in the scott.emp at db01 database;

SQL> conn scott/tiger@db01

Connected.

SQL> select count(*) from emp;

  COUNT(*)

----------

        14

SQL> --let add new employee on source db (db01)

SQL> insert into emp

  2  values (007,'TEST','testing',10,sysdate,100,0,20);

1 row created.

SQL> --let check on target db (db02)

SQL> conn scott/tiger@db02

Connected.

SQL> select empno,ename,job,mgr,hiredate from emp where empno=007;

     EMPNO ENAME      JOB              MGR HIREDATE

---------- ---------- --------- ---------- ---------

         7 TEST       testing           10 17-AUG-11

SQL> --DML operation are working perfectly.

SQL> --let check DDL operations

SQL> --add new column in the emp table in target db (db02)

SQL> alter table emp

  2  add (ID NUMBER DEFAULT 0);

Table altered.

SQL> --check on source db (db01)

SQL> conn scott/tiger@db01

Connected.

SQL> desc emp

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

 ID                                                 NUMBER

SQL> --DDL operation are also working :)

SQL> --and this is vice versa stream configuration.

Important considerations for the configuration procedures

1. Local or downstream capture for the source database

The database on which the procedure is run is configured as the capture database for changes made to the source database. Therefore, to configure local capture at the source database, run the procedure at the source database. 

procedure : dbms_streams_adm.maintain_schemas

2. Perform configuration actions directly or with a script

To configure an Oracle Streams replication environment directly when you run one of these procedures, set the perform_actions parameter to TRUE. The default value for this parameter is TRUE.

Direct option configure stream environment immediately.

3. Oracle Streams Components configured by these procedures

The capture_name and capture_queue_name parameters must be set to NULL when both of the following conditions are met:

The destination database is the capture database.

The bi_directional parameter is set to TRUE.

4. One-way or Bi-Directional replication

If you set the bi_directional parameter to TRUE when you run one of these procedures, then do not allow data manipulation language (DML) or data definition language (DDL) changes to the shared database objects at the destination database while the procedure, or the script generated by the procedure, is running. This restriction does not apply if a procedure is configuring a single-source replication environment.

5. DDL changes

To configure an Oracle Streams replication environment that does not maintain DDL changes, set the include_ddl parameter to FALSE when you run one of these procedures. The default value for this parameter is FALSE.

Set include_DDL = TRUE (in this example)

6. Instantiaion

To specify this instantiation option, set the instantiation parameter to  the following values:

DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA if you run the MAINTAIN_SCHEMAS procedure

write me email in case of any queries at askdbataj