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
