Oracle Database

Oracle Streams -11g features

2009-10-23T16:22:00.000-07:00

Database 11g Contains numerous Streams Performance improvements, such as,

1.The reader process in the capture component mines from the in-memory redo log buffer whenever possible to minimize disk I/O and reduce latency of capture.

2.The capture process directly communicate with the apply component to improve LCR transaction throughput and reduce end-to-end replication latency. This optimization, know as Combined Capture and Apply, is automatically enabled for single capture/single apply configuration.

3.The applier process employs an internal mechanism to execute change records and thus reduces CPU consumption. In addition, extensive caching minimized latch contention adn other wait events.

Adding Partition Table to Streams

2009-09-20T19:24:00.000-07:00

Steps for adding a Partition table to an existing single source Streams replication environment.

Source database (S): ORCL.ORACLEDB1.KRISH.COM
Target database (T): MYDEV.ORACLEDB2.KRISH.COM
Source & Target Schema: SCOTT
Table: SALES_PART

Note: Ensure that you have setup the streams for replication for the first time and created a streams administrator before executing following steps.

Steps for adding a Partitioned table:

1. Stop the capture process(S)
2. Setup logging & supplemental logging for the table (S)
3. Add capture rules on the table (S)
4. Add propagation rules on the table(S)
5. Set Instantiation of table from Source(S)
6. Export, Import of table(Data) from Source(S) to Target (T)
7. Add apply rules to the table(T)
8. Start the capture process(S)

1 Stop the capture process(S)

connect strmadmin/strmORCL@ORCL

SQL> BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'STREAMS_CAPTURE');
END;
/
2 Configure logging & supp. logging for the table (S)

connect strmadmin/strmORCL@ORCL

--Create Partition table if does not exist on the source database.

SQL> create table SCOTT.SALES_PART
(INVOICE_NO NUMBER,
SALE_YEAR INT NOT NULL,
SALE_MONTH INT NOT NULL,
SALE_DAY INT NOT NULL)
partition by range (SALE_YEAR,SALE_MONTH,SALE_DAY)
(PARTITION SALES_Q1 VALUES LESS THAN (1994, 04, 01) TABLESPACE TBS_USERDATA,
PARTITION SALES_Q2 VALUES LESS THAN (1994,07,01) TABLESPACE TBS_USERDATA)
/

alter table SCOTT.SALES_PART logging;
alter table SCOTT.SALES_PART add supplemental log data (primary key,unique,foreign key,all) columns;

select partition_name, logging from dba_tab_partitions where table_name='SALES_PART' and TABLE_OWNER='SCOTT';

3 Add capture rules on the table (S)
Source(ORCL):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmORCL@ORCL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.SALES_PART',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

4 Add propagation rules on the table(S)

Source(ORCL):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmORCL@ORCL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.SALES_PART',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL',
inclusion_rule => true);
END;
/

5 Set Instantiation of table from Source (S)

--This script needs to be run as "strmadmin".

Source(ORCL):

connect strmadmin/strmORCL@ORCL

BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.SALES_PART');
END;
/

6 Export, import of table (Data) from Source(S) to Target (T)

Source(ORCL):

$exp parfile=exp_sales_part.par

#exp_sales_part.par
USERID=system@ORCL
FILE=exp_SALES_PART.dmp
LOG=exp_SALES_PART.log
ROWS=Y
GRANTS=Y
INDEXES=Y
TRIGGERS=N
CONSTRAINTS=Y
CONSISTENT=Y
STATISTICS=NONE
TABLES= SCOTT.SALES_PART

ftp the exp_SALES_PART.dmp file to target site

Target(MYDEV):

$imp parfile=imp_MYDEV_sales_part.par

#imp_MYDEV_ sales_part.par
USERID=system@MYDEV
FILE=exp_SALES_PART.dmp
LOG=imp_SALES_PART.log
ROWS=Y
IGNORE=Y
resumable=y
resumable_timeout=43200
commit=y
statistics=NONE
buffer=50000000
RECORDLENGTH=65535
STREAMS_INSTANTIATION=Y
FULL=Y

make sure that target table instantiated

select SOURCE_DATABASE,SOURCE_OBJECT_OWNER,SOURCE_OBJECT_NAME,
INSTANTIATION_SCN from dba_apply_instantiated_objects where SOURCE_OBJECT_NAME='SALES_PART';

7 Add apply rules to the table(T)

Target(MYDEV):

connect strmadmin/strmMYDEV@MYDEV

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.SALES_PART',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name =>
'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL',
inclusion_rule => true);
END;
/

-- grant all permissions on SALES_PART to streams administrator
conn / as sysdba

GRANT ALL ON SCOTT.SALES_PART to strmadmin;

8 Start the capture process(S)

connect strmadmin/strmORCL@ORCL

SQL> BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STREAMS_CAPTURE');
END;
/

Verification:

1. Add a partition

SQL> alter table SCOTT.SALES_PART add partition SALES_Q3
VALUES LESS THAN(1994,10,01) TABLESPACE TBS_USERDATA;

Check on the source and target databases.
-----------------------------------------
select TABLE_NAME,partition_name from dba_tab_partitions where table_name='SALES_PART' and TABLE_OWNER='SCOTT';

2. Drop partition
Alter table SCOTT.SALES_PART drop partition SALES_Q3;

3. Move partition to different tablespace
Alter table SCOTT.SALES_PART MOVE PARTITION SALES_Q2 TABLESPACE STREAMS01;

select TABLE_NAME,partition_name,tablespace_name from dba_tab_partitions where table_name='SALES_PART' and TABLE_OWNER='SCOTT';

4.Perform DML:
Insert into SCOTT.SALES_PART values (1,1994,02,05);
Insert into SCOTT.SALES_PART values (2,1994,05,10);
Commit;

DELETE FROM SCOTT.SALES_PART;
COMMIT;

5.Truncate Partition table:

Alter table SCOTT.SALES_PART TRUNCATE PARTITION SALES_Q1;
Alter table SCOTT.SALES_PART TRUNCATE PARTITION SALES_Q2;

6. SPLIT Table Partitions

Alter table SCOTT.SALES_PART SPLIT PARTITION SALES_Q2 AT(1994,06,01)
INTO (PARTITION SALES_Q2, PARTITION SALES_Q3);

select TABLE_NAME,partition_name,tablespace_name from dba_tab_partitions where table_name='SALES_PART' and TABLE_OWNER='SCOTT';

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ --------------------
SALES_PART SALES_Q1 TBS_USERDATA
SALES_PART SALES_Q2 STREAMS01
SALES_PART SALES_Q3 STREAMS01

7. Merge partition

Alter table SCOTT.SALES_PART MERGE PARTITIONS SALES_Q2, SALES_Q3 INTO PARTITION SALES_Q23;

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ -------------------
SALES_PART SALES_Q1 TBS_USERDATA
SALES_PART SALES_Q23 TBS_USERDATA

8. Rename Partition

Alter table SCOTT.SALES_PART RENAME PARTITION SALES_Q23 TO SALES_Q2;

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ --------------------
SALES_PART SALES_Q1 TBS_USERDATA
SALES_PART SALES_Q2 TBS_USERDATA

9. Create LOCAL index on SALES_PART

CREATE INDEX SCOTT.SALES_PART_IDX ON SCOTT.SALES_PART(INVOICE_NO,SALE_MONTH)
LOCAL
(PARTITION SALES_Q1 TABLESPACE TBS_USERIDX,
PARTITION SALES_Q2 TABLESPACE TBS_USERIDX);
select TABLE_NAME,INDEX_NAME,PARTITIONING_TYPE,LOCALITY,ALIGNMENT from dba_part_indexes where TABLE_NAME like 'SALES_PART';

TABLE_NAME INDEX_NAME PARTITI LOCALI ALIGNMENT
---------- ------------------------------ ------- ------ ------------
SALES_PART SALES_PART_IDX RANGE LOCAL NON_PREFIXED

select INDEX_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,STATUS,USER_STATS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME LIKE 'SALES_PART_IDX';

INDEX_NAME COM PARTITION_NAME HIGH_VALUE PARTITION_POSITION STATUS USE
------------------------------ --- ------------------------------ --------------- ------------------ -------- ---
SALES_PART_IDX NO SALES_Q1 1994, 04, 01 1 USABLE NO
SALES_PART_IDX NO SALES_Q2 1994, 07, 01 2 USABLE NO

Bi directional Streams setup

2009-09-02T07:52:00.000-07:00

Bi-directional streams

Streams Database Move

2009-08-07T16:40:00.000-07:00

Streams source database Migration:
Below steps I followed to migrate streams source database. Streams configured at table level using local capture.
1. Stopped streams capture & propagation process prior to export on source db.
2. Checked for any errors on the streams target database in dba_apply_error.
3. Exported Full database using Datapump (set parameter Flashback_time or flashback_scn).
4. Created new source database at new destination & enabled Archived Log mode.
5. Imported full database.
6. Ran utlrp.sql.
7. Disabled the propagation process.
8. Configured listener and tnsnames for new source db.
9. Ran Instantiation for the replication objects.
10. Started propagation process then capture process on the new source db.

Adding a table to a single source Streams environment

2009-03-18T19:26:00.000-07:00

Adding a table to an existing single source Streams replication environment while the table is active.

Source database (S): ORCL.ORACLEDB1.KRISH.COM

Target database (T): MYDEV.ORACLEDB2.KRISH.COM

Source & Target Schema: SCOTT
Adding Table: DEPT

Steps to add table “DEPT” which exists at ORCL.ORACLEDB1.KRISH.COM to the Streams environment without stopping the existing apply/propagation/capture:

Note: Ensure that you have setup the streams for replication for the first time and created a streams administrator before executing following scripts.

1. Add apply rules on the table(T)
2. Add propagation rules on the table(S)
3. Configure supplemental logging for the table (S)
4. Add capture rules on the table (S)
5. Set Instantiation of table from Source(S)
6. Export, Import of table(Data) from Source(S) to Target (T)
7. Grant privileges to strmadmin user(T)

1 Add apply rules on the table(T)

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect strmadmin/strmpwd@MYDEV.ORACLEDB2.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV,
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/

2 Add propagation rules on the table(S)

Source(ORCL.ORACLEDB1.KRISH.COM):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmpwd@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV.ORACLEDB2.KRISH.COM',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/

3 Configure supplemental logging for the table (S)

connect strmadmin/strmpwd@ORCL.ORACLEDB1.KRISH.COM

alter table SCOTT.DEPT add supplemental log data (primary key, unique, foreign key, all) columns;

4 Add capture rules on the table (S)

Source(ORCL.ORACLEDB1.KRISH.COM):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmpwd@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

5 Set Instantiation of the table from Source (S)

--This script needs to be run as "strmadmin".

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmpwd@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.DEPT');
END;
/

6 Export, import of table (Data) from Source(S) to Target (T)

Source(ORCL.ORACLEDB1.KRISH.COM):

$exp parfile=DEPT.par

DEPT.par
USERID=system/pwd@ORCL.ORACLEDB1.KRISH.COM
FILE=DEPT.dmp
LOG= DEPT.log
ROWS=Y
GRANTS=Y
INDEXES=Y
TRIGGERS=N
CONSTRAINTS=Y
CONSISTENT=Y
STATISTICS=NONE
TABLES= SCOTT.DEPT

ftp the DEPT.dmp file to target site

Target(MYDEV.ORACLEDB2.KRISH.COM):

$imp parfile=imp_DEPT.par

imp_DEPT.par
USERID=system/pwd@MYDEV.ORACLEDB2.KRISH.COM
FILE=DEPT.dmp
LOG= DEPT.log
ROWS=Y
IGNORE=Y
STREAMS_INSTANTIATION=Y
FULL=Y

7 Grant privileges to strmadmin user(T)

Target(MYDEV.ORACLEDB2.KRISH.COM):
-- grant all permissions on DEPT to streams administrator
conn SCOTT/tiger@MYDEV.ORACLEDB2.KRISH.COM

GRANT ALL ON SCOTT.DEPT to strmadmin;

Hub and Spoke Streams Replication Setup between 3 Databases

2009-03-16T21:07:00.000-07:00

Hub-n-Spoke replication has typically a single Hub database to which all the other Spoke databases will be replicating. The changes (DML & DDL) originated on the Hub will be replicated to all the Spoke databases.

Publishing the document with example soon.

Oracle Streams Overview

2009-02-07T16:27:00.000-08:00

Oracle Streams enables information sharing. Oracle Streams can share database changes and other information in a stream, which can propagate events within a database or from one database to another. The specified information is routed to specified destinations. The result is a feature that provides greater functionality and flexibility than traditional solutions for capturing and managing information, and sharing the information with other databases and applications.

- A capture process is an Oracle background process that scans the database redo log to capture DML and DDL changes made to database objects. It formats these changes into events called logical change records (LCRs) and enqueues them into a queue.

- Propagation process send events from one queue to another, and these queues can be in the same database or in different databases.

- An apply process is an Oracle background process that dequeues events from a queue and applies each event directly to a database object or sends events to apply handlers for custom processing.

Re-Synchronize/Refresh a Table in Streams Environment

2009-01-10T19:10:00.000-08:00

Purpose:

The purpose of this article is to outline the steps for Re-Synchronize/Refresh a single table if it is out of sync with source site in one-way replication between two ORACLE databases in Streams environment.

Example:
Schema:SCOTT
Table:EMP

Steps for Refreshing a Table

If one of the tables on the Target site is out of Sync with Source site then follow below steps to re-synchronize both:

1. Stop Capture Process on Source(S)
2. Stop Propagation Process on Source(S)
3. Stop Apply Process on Target(T)
4. Delete the apply errors on Target(T)
5. Truncate the problem table on Target(T)
6. Re-instantiate the problem table on Source(S)
7. Synchronize the problem table through exp/imp
8. Start the apply Process on Target(T)
9. Start the propagation Process on the Source(S)
10.Start the Capture Process on the Source(S)

1. Stop Capture Process on Source(S)

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

SQL> BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'STREAMS_CAPTURE');
END;
/

2. Stop Propagation Process on Source(S)

Make sure all the changes are propagated to the Target site, then stop the propagation process on source using following sql

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

SQL>BEGIN
DBMS_PROPAGATION_ADM.STOP_PROPAGATION(
'STREAMS_PROPAGATE',FORCE=>TRUE);
END;
/

3. Stop Apply Process on Target(T)

Make sure all changes are applied on the target, then stop apply process on target using following sql

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

SQL> BEGIN
DBMS_APPLY_ADM.STOP_APPLY(
apply_name => 'STREAMS_APPLY');
END;
/

4. Delete the apply errors on Target

As we will re-synchronize, we can delete the errors that are in the error_queue of the apply process. To do so, we can execute either DBMS_APPLY_ADM.DELETE_ERROR or DBMS_APPLY_ADM.DELETE_ALL_ERRORS at the apply site.

You need to make sure that you are not deleting any needed transaction, specially if you are not synchronizing all the replicated for that apply process.

Target(MYDEV.ORACLEDB2.KRISH.COM):

Check the errors in the error queue:

SQL> select apply_name, LOCAL_TRANSACTION_ID, ERROR_MESSAGE from dba_apply_error;

To delete specific transaction:

SQL> exec DBMS_APPLY_ADM.DELETE_ERROR('transaction_id');

To delete all errors:

SQL> exec DBMS_APPLY_ADM.DELETE_ALL_ERRORS('apply_name');

5. Truncate the problem table on Target

Truncate the problem table if you are using traditional export/import for copying the data.

If you are using datapump exp/imp then ignore step 5 and go to step 7(use parameter TABLE_EXISTS_ACTION=truncate)

Target(MYDEV.ORACLEDB2.KRISH.COM):

Connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

SQL> Truncate table SCOTT.EMP;

6. Re-instantiate the problem table on Source(S)

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

BEGIN

DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.EMP');

END;

DECLARE iscn NUMBER; BEGIN iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@MYDEV.ORACLEDB2.KRISH.COM ( source_object_name => 'SCOTT.EMP', source_database_name => 'ORCL.ORACLEDB1.KRISH.COM', instantiation_scn => iscn);

END;

7. Synchronize the problem table through datapump exp/imp

Now synchronize the problem table on source and target so they are identical, Here we are copying the data through DP exp/imp from source to target.

6a) Export the source Table:

For example we are replicating table EMP of SCOTT, so we should do the following:

Source(ORCL.ORACLEDB1.KRISH.COM):

expdp parfile=expdp.par

expdp.par

userid=system/pwd@ORCL.ORACLEDB1.KRISH.COM
tables=SCOTT.EMP
DUMPFILE=dpump:expdpScott.dmp
LOGFILE= dpump:expdpScott.log
JOB_NAME=exp_job
CONTENT=DATA_ONLY

6b) Import the source table to target:

Target(MYDEV.ORACLEDB2.KRISH.COM):

impdp parfile=impdp.par

impdp.par

userid=system/pwd@MYDEV.ORACLEDB2.KISH.COM
tables=SCOTT.EMP
DUMPFILE=dpump:expdpScott.dmp
LOGFILE= dpump:impdpScott.log
JOB_NAME=imp_job
TABLE_EXISTS_ACTION=truncate

8. Start the Apply Process on Target(T)

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

SQL> BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'STREAMS_APPLY');
END;
/
Make sure apply process is started

SQL> select apply_name, status from dba_apply;

9. Start the propagation Process on the Source(S)

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

SQL> BEGIN
DBMS_PROPAGATION_ADM.START_PROPAGATION('STREAMS_PROPAGATE');
END;
/

10. Start the Capture Process on the Source(S)

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

SQL> BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STREAMS_CAPTURE');
END;
/

Make sure capture process is started

SQL> select capture_name, status from dba_capture;

Conclusion:

The Target database is not up to date till the refresh is completed.

Streams 10gR2 Implementation -Table Level

2008-12-21T18:35:00.000-08:00

Oracle Streams 10gR2 Step by Step

Purpose:

Oracle Streams enables the sharing of data and events in a data stream, either within a database or from one database to another.

The purpose of this article is to outline the steps for setting up one-way replication between two ORACLE databases using streams at table level.

Source database (S): ORCL.ORACLEDB1.KRISH.COM 192.162.2.138
Target database (T): MYDEV.ORACLEDB2.KRISH.COM 192.162.2.139
Source Schema: SCOTT
Target Schema: SCOTT
Replication Tables:
EMP,
DEPT,
SALGRADE,
BONUS

Streams Setup Steps:

Following sequence of steps are required in order to implement streams in online.

1. Set parameters Relevant to Streams (S & T)
2. Set up ARCHIVELOG mode (S & T)
3. Create separate Tablespace for stream admin user (S & T)
4. Setup streams admin user (S & T)
5. Create a database link on source (S)
6. Setup Streams queues on both Source(S) and Target (T)
7. Setup supplemental logging on Source(S)
8. Configure capture process (S)
9. Configure propagation process (S)
10.Create destination tables (Metadata-only export/import) on Target (T)
11.Set Instantiation of tables from Source(S)
12.Export, Import of tables(Data) from Source(S) to Target (T)
13.Grant object privileges to stream admin user (T)
14.Configure apply process on Target(T)
15.Start the apply process (T) and Capture process(S)

1 Set parameters Relevant to Streams (S & T)

1a) Initialization parameters

Set below Init.ora parameters on both Source (S) and Target (T) databases.

db_name =
db_domain = ORACLEDB1.KRISH.COM
global_names = TRUE
compatible = 10.2.0
job_queue_processes =4
timed_statistics =TRUE
statistics_level =TYPICAL
sga_target=>0 or streams_pool_size >=200m
open_links =4
logmnr_max_persistent_sessions =1(>= no. of capture processes)
parallel_max_servers =2 or more(current value + (3 * capture processes) + (3
* apply processes)

1b) Streams Process Parameters

Capture: (DBMS_CAPTURE package)
Set capture parameters on source database

1. Set retention time for capture checkpoints as needed
Alter_capture(‘captureName’, checkpoint_retention_time=>7)

2. Reduce the capture checkpoint frequency parameter
Set_parameter(‘captureName’,’_checkpoint_frequency’,’1000’)
Note: Recommended “_checkpoint_frequency=1000” If redo logs are over 300Mb in size.

Apply: (DBMS_APPLY package)
Set Apply parameters on Target database

1. Set_parameter(‘applyName’,’parallelism’,’4’)
2. Set_parameter(‘applyName’,’disable_on_error’,’N’)

2 Set up ARCHIVELOG mode (S & T)

Set below parameters in init.ora and turn on “ARCHIVELOG” mode

log_archive_dest=‘/u002/oradata/ORCL/arch’
log_archive_format=‘ARCH%t_%s_%r.LOG’

Issue below command in mount state

SQL> alter database archivelog;
SQL> alter database open;

3 Create separate Tablespace for stream admin user (S & T)

Create default tablespace (streams01) for streams administrator account which stores the queue table. Minimum tablespace size should be 200m.

Source(ORCL.ORACLEDB1.KRISH.COM):

connect system/<@ORCL.ORACLEDB1.KRISH.COM

Create tablespace streams01 datafile ‘/a002/oradata/ORCL/STREAMS0101.dbf’ size 200m autoextend on extent management local uniform size 1m;

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect system/pwd@MYDEV.ORACLEDB2.KRISH.COM

Create tablespace streams01 datafile ‘/a002/oradata/MYDEV/STREAMS0101.dbf’ size 200m autoextend on extent management local uniform size 1m;

4 Setup streams admin user (S & T)

Source(ORCL.ORACLEDB1.KRISH.COM):

Script creates strmadmin user at source and grants set of privileges.
This script needs to be run as "sys"

connect /as sysdba

CREATE USER strmadmin
IDENTIFIED BY strmadmin
DEFAULT TABLESPACE STREAMS01
TEMPORARY TABLESPACE TEMP01
QUOTA UNLIMITED ON STREAMS01;

--Now grant the CONNECT, RESOURCE, and DBA roles to the Streams administrator:

GRANT CONNECT, RESOURCE, DBA TO strmadmin;
GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/

Target(MYDEV.ORACLEDB2.KRISH.COM):

Script creates strmadmin user at Target and grants set of privileges.
This script needs to be run as "sys"

connect /as sysdba

CREATE USER strmadmin
IDENTIFIED BY strmadmin
DEFAULT TABLESPACE STREAMS01
TEMPORARY TABLESPACE TEMP01
QUOTA UNLIMITED ON STREAMS01;

--Now grant the CONNECT, RESOURCE, and DBA roles to the Streams administrator:

GRANT CONNECT, RESOURCE, DBA TO strmadmin;
GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/

5 Create a database link on source (S)

Create a private database link on source site. Do not change the password for strmadmin user after creating the database link, if password changed then the propagation process will fail to propagate the changes to the target database.

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

drop database link MYDEV.ORACLEDB2.KRISH.COM;

create database link MYDEV.ORACLEDB2.KRISH.COM
CONNECT TO strmadmin
IDENTIFIED BY strmadmin USING 'MYDEV.ORACLEDB2.KRISH.COM'
/

6 Setup Streams queues on both Source(S) and Target(T)

Source and Target queue name similar as STREAMS_QUEUE__

Source(ORCL.ORACLEDB1.KRISH.COM):

-- Create the source queue
-- This script needs to be run as "strmadmin"

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

begin
dbms_streams_adm.set_up_queue(
queue_name => 'STREAMS_QUEUE_ORCL_MYDEV');
end;
/

Target(MYDEV.ORACLEDB2.KRISH.COM):

-- Create the destination queue
-- This script needs to be run as "strmadmin"

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

begin
dbms_streams_adm.set_up_queue(
queue_name => 'STREAMS_QUEUE_ORCL_MYDEV');
end;
/

7 Setup supplemental logging (S)

The tables which are participating in replication should have primary key exist. If no PK, then unique key columns can be used.

Source(ORCL.ORACLEDB1.KRISH.COM):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

alter table SCOTT.EMP add supplemental log data(primary key,unique,foriegn key,all) columns;
alter table SCOTT.DEPT add supplemental log data(primary key,unique,foriegn key,all) columns;
alter table SCOTT.SALGRADE add supplemental log data(primary key,unique,foriegn key,all) columns;
alter table SCOTT.BONUS add supplemental log data(primary key,unique,foriegn key,all) columns;

8 Configure capture process (S)

Source(ORCL.ORACLEDB1.KRISH.COM):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.EMP',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.SALGRADE',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.BONUS',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

--By default streams retention time is 64 days, set this to a realistic value 7 days.

begin
dbms_capture_adm.alter_capture(
capture_name => 'STREAMS_CAPTURE',
checkpoint_retention_time => 7);
end;
/
--set checkpoint frequency to 1000

begin
DBMS_CAPTURE_ADM.SET_PARAMETER
('STREAMS_CAPTURE', '_checkpoint_frequency','1000');
end;
/

9 Configure propagation process (S)

Source(ORCL.ORACLEDB1.KRISH.COM):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.EMP',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.SALGRADE',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.BONUS',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/

10 Create destination tables (Metadata-only exp/imp) on Target (T)

Export the objects metadata only from source and import on target database.

Source(ORCL.ORACLEDB1.KRISH.COM):

expdp parfile=expdp.par

expdp.par
userid=system/@ORCL.ORACLEDB1.KRISH.COM
tables=SCOTT.EMP,SCOTT.DEPT,SCOTT.SALGRADE,SCOTT.BONUS,
DUMPFILE=dpump:expdpScott.dmp
LOGFILE= dpump:expdpScott.log
JOB_NAME=exp_job
CONTENT=METADATA_ONLY

Target(MYDEV.ORACLEDB2.KRISH.COM):

impdp parfile=impdp.par

impdp.par
userid=system/@MYDEV.ORACLEDB2.KRISH.COM
tables=SCOTT.EMP,SCOTT.DEPT,SCOTT.SALGRADE,SCOTT.BONUS
DUMPFILE=dpump:expdpScott.dmp
LOGFILE= dpump:impdpScott.log
JOB_NAME=expfull
TABLE_EXISTS_ACTION=replace

11 Set Instantiation of tables from Source (S)

Make sure SCN # is same for all the tables which are participating in streams.

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.EMP');
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.DEPT');
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.SALGRADE');
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.BONUS');
END;
/
DECLARE
iscn NUMBER;
BEGIN
iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@MYDEV.ORACLEDB2.KRISH.COM (
source_object_name => 'SCOTT.EMP',
source_database_name => 'ORCL.ORACLEDB1.KRISH.COM',
instantiation_scn => iscn);
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@MYDEV.ORACLEDB2.KRISH.COM (
source_object_name => 'SCOTT.DEPT',
source_database_name => 'ORCL.ORACLEDB1.KRISH.COM',
instantiation_scn => iscn);
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@MYDEV.ORACLEDB2.KRISH.COM (
source_object_name => 'SCOTT.SALGRADE',
source_database_name => 'ORCL.ORACLEDB1.KRISH.COM',
instantiation_scn => 'iscn');
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@MYDEV.ORACLEDB2.KRISH.COM (
source_object_name => 'SCOTT.BONUS',
source_database_name => 'ORCL.ORACLEDB1.KRISH.COM',
instantiation_scn => 'iscn');
END;
/

12 Export, import of tables (Data) from Source(S) to Target (T)

Source(ORCL.ORACLEDB1.KRISH.COM):

expdp parfile=expdp.par

expdp.par
userid=system/@ORCL.ORACLEDB1.KRISH.COM
Tables=SCOTT.EMP,SCOTT.DEPT,SCOTT.SALGRADE,SCOTT.BONUS,
DUMPFILE=dpump:exp_Scott_data.dmp
LOGFILE= dpump:exp_Scott_data.log
JOB_NAME=exp_job

Target(MYDEV.ORACLEDB2.KRISH.COM):

impdp parfile=impdp.par

impdp.par
userid=system/@MYDEV.ORACLEDB2.KRISH.COM
Tables=SCOTT.EMP,SCOTT.DEPT,SCOTT.SALGRADE,SCOTT.BONUS,
DUMPFILE=dpump:exp_Scott_data.dmp
LOGFILE= dpump:imp_soctt_data.log
JOB_NAME=imp_job
TABLE_EXISTS_ACTION=APPEND

13 Grant object privileges to stream admin user (T)

The apply user must have all grants/permissions to perform DDL and DML operations on the objects.

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect SCOTT/tiger@MYDEV.ORACLEDB2.KRISH.COM

GRANT ALL ON SCOTT.EMP to strmadmin;
GRANT ALL ON SCOTT.DEPT to strmadmin;
GRANT ALL ON SCOTT.SALGRADE to strmadmin;
GRANT ALL ON SCOTT.BONUS to strmadmin;

14 Configure apply process on Target (T)

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.EMP',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name =>
'strmadmin.STREAMS_QUEUE_ORCL_MYDEV,
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name =>
'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.SALGRADE',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name =>
'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.BONUS',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name =>
'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/

--Set parameter disable on error & parallelism on Target database

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'streams_apply',
parameter => 'disable_on_error',
value => 'n');
END;
/

begin
dbms_apply_adm.set_parameter(‘streams_apply’,’parallelism’,’4’);
end;
/

15 Start the apply process on Target (T) & Capture process on source(S)

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'streams_apply');
END;
/

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name =>
'STREAMS_CAPTURE');
END;
/

Implementing Streams Heartbeat Table

2008-12-15T08:32:00.001-08:00

Heartbeat table in a Streams replication is especially useful for databases that have a low activity rate because you can make sure the replication environment is working properly even if there are not many replicated changes. It ensures that changes are being replicated in a Streams replication environment.

Prerequisites:

Create a table at the source site that includes a date or timestamp column and the global name of the database.

Add a rule to capture changes to this table and propagate the changes to each target destination.

Make sure that the target destination will apply changes to this table as well.

Set up an automated job to update this table at the source site periodically, for example every minute.

Following is an example configuration we could use as a heartbeat table.

--Create a table at the source database.
--Enable Supplemental logging for the table.
--Instantiate the table.
--Add this table to capture,apply,propagation rules.
--Configure an automated job to update the heartbeat.

create table SCOTT.HEARTBEAT(a number primary key, b date);
create sequence HEART_SEQ start with 1;

-- TO SUBMIT A JOB THAT RUN AN INSERT EVERY 60 SECONDS
variable jobno number;
begin
dbms_job.submit(:jobno,'insert into SCOTT.HEARTBEAT values (HEART_SEQ.nextval, sysdate);',
sysdate, 'sysdate+60/(60*60*24)');
commit;
end;
/

-- Add this table to the streams configuration