Remove ASM disk

One of the big selling points of ASM is the ability to reconfigure the storage online. I had to remove 10 disks from a +ASM test system that had to be redeployed on another server. The steps seemed easy enough until I ran into a problem;

SQL> select d.MOUNT_STATUS, d.MODE_STATUS, d.STATE, d.NAME, d.PATH
from v$asm_disk d, v$asm_diskgroup dg
where d.GROUP_NUMBER=dg.GROUP_NUMBER
and dg.name = 'PCASDGF'
order by 4
/

MOUNT_S MODE_ST STATE NAME PATH HEADER_STATUS
------- ------- -------- -------------------- --------------------------- -------------
...
CACHED ONLINE NORMAL PCASDGF_0132 /dev/oracle/fra/c17t6d7 MEMBER
CACHED ONLINE NORMAL PCASDGF_0133 /dev/oracle/fra/c17t7d0 MEMBER
CACHED ONLINE NORMAL PCASDGF_0134 /dev/oracle/fra/c17t7d1 MEMBER
CACHED ONLINE NORMAL PCASDGF_0135 /dev/oracle/fra/c17t7d2 MEMBER
CACHED ONLINE NORMAL PCASDGF_0136 /dev/oracle/fra/c17t7d3 MEMBER
CACHED ONLINE NORMAL PCASDGF_0137 /dev/oracle/fra/c17t7d4 MEMBER
CACHED ONLINE NORMAL PCASDGF_0138 /dev/oracle/fra/c17t7d5 MEMBER
CACHED ONLINE NORMAL PCASDGF_0139 /dev/oracle/fra/c17t7d6 MEMBER
CACHED ONLINE NORMAL PCASDGF_0140 /dev/oracle/fra/c17t7d7 MEMBER
CACHED ONLINE NORMAL PCASDGF_0141 /dev/oracle/fra/c17t8d5 MEMBER

SQL> alter diskgroup PCASDGF drop disk PCASDGF_0132;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0133;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0134;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0135;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0136;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0137;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0138;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0139;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0140;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0141;

You can happily drop disks in a disk group and ASM will seamlessly migrate the data to the existing disks in the disk group. The prompt returns immediatly, but the job (of migrating the data) is not yet done. In order to monitor progress use the following SQL.

SQL> select * from v$asm_operation
/

When the job is done the SQL will retun no rows. The status of the disks are also updated.

SQL> select MOUNT_STATUS, MODE_STATUS, STATE, NAME, PATH, header_status
from v$asm_disk
where name is null
/
MOUNT_S MODE_ST STATE NAME PATH HEADER_STATU
------- ------- -------- ------------------------------ ------------------------------ ------------
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t6d7 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d0 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d1 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d2 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d3 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d4 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d5 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d6 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d7 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t8d0 FORMER

BUT

# fuser /dev/oracle/fra/c17t6d7 returns/dev/oracle/fra/c17t6d7: 2924o 18184o 18175o 4129o 618o

The process details are

oracle 2924 1 0 Nov 4 ? 17:53 asm_rbal_+ASM
oracle 18184 1 0 Nov 17 ? 1:16 ora_rbal_casbcva
oracle 18175 1 0 Nov 17 ? 1:34 ora_rvwr_casbcva
oracle 4129 4128 0 10:49:06 ? 0:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 618 1 0 Nov 25 ? 37:06 oraclecasbcva (LOCAL=NO)

Is it safe to physically remove these disks? +ASM still knows about these disks and Oracle processes are still attached to these disks. I logged a SR with Oracle Support and got the following feedback:

Bug 7516653 - DROPPED ASM DISK STILL HELD BY ASM PROCESSES
Closed as duplicate for bug:
Bug 7225720 - ASM DOES NOT CLOSE OPEN DESCRIPTORS EVEN AFTER APPLYING THE Patch 4693355

Fixed in 11.2

Please perform the following workaround:
1. create dummy diskgroup using this disk:
SQL> create diskgroup test external redundancy disk ;
2. drop this diskgroup:
SQL> drop diskgroup test;
3. check if the disk still help by any process.
If the disk still held then we will have to restart the ASM instance.

So that's what I did

SQL> create diskgroup test 
external redundancy 
disk /dev/oracle/fra/c17t6d7','/dev/oracle/fra/c17t7d0',
'/dev/oracle/fra/c17t7d1','/dev/oracle/fra/c17t7d2',
'/dev/oracle/fra/c17t7d3','/dev/oracle/fra/c17t7d4',
'/dev/oracle/fra/c17t7d5','/dev/oracle/fra/c17t7d6',
'/dev/oracle/fra/c17t7d7','/dev/oracle/fra/c17t8d0'
/
SQL> drop diskgroup test
/

Fuser still showed os processes accesing the devices and I eventually had to bounce +ASM before the devices could be safely removed.

SQL workload history with DBA_HIST_SQLSTAT

Using the DBA_HIST_XXX views from the AWR (automatic workload repository), it has become easier for a DBA to track changes in workload metrics over time. Through a SR I logged with Oracle Support they have supplied me with the following sql statement to track such changes for a single SQL statement.

Just substitute XXX with your SQL_ID.

set trimspool on
set lines 220
set long 10000
set longchunk 10000
set pages 99
set longchunk 100000
set long 10000
set time on
undefine SQL_ID
undefine PLAN_HASH_VALUE
undefine SQL_TEXT

Select distinct dbid, sql_id, plan_hash_value, timestamp
from dba_hist_sql_plan where sql_id='XXX'
order by dbid, timestamp
/

undefine PLAN_HASH_VALUE
accept PLAN_HASH_VALUE prompt 'Please enter PLAN_HASH_VALUE to show Statistics for: '
col iowait_delta format 9999999.99 heading iowaitdelta(ms)
col iowait_total format 9999999.99 heading iowaittotal(ms)
col ELAPSED_TIME_TOTAL format 9999999.99 heading elapsdtimetotal(ms)
col ELAPSED_TIME_DELTA format 9999999.99 heading elapsdtimedelta(ms)
col PLAN_HASH_VALUE heading plan_hashvalue
col CONCURRENCY_WAIT_TOTAL format 9999999.99 heading concwaittotal(ms)
col CONCURRENCY_WAIT_delta format 9999999.99 heading concwaitdelta(ms)
col CLUSTER_WAIT_DELTA format 9999999.99 heading clustwaitdelta(ms)
col CLUSTER_WAIT_TOTAL format 9999999.99 heading clustwaittotal(ms)
col APWAIT_TOTAL format 9999 heading applwaittimetotal(micro)
col APWAIT_DELTA format 9999 heading applwaittimedelta(micro)
col PLSEXEC_TIME_TOTAL format 9999 heading plsqlexectimetotal(micro)
col PLSEXEC_TIME_DELTA format 9999 heading plsqlexectimedelta(micro)
col JAVAEXEC_TIME_DELTA format 9999 heading javaexectimedelta(micro)
col JAVAEXEC_TIME_TOTAL format 9999 heading javaexectimetotal(micro)
col optimizer_cost format 9999 heading optcostcol optimizer_mode format a10 heading optimmode
col kept_versions format 999 heading keptvers
col invalidations_total format 999 heading invalidtot
col invalidations_delta format 999 heading invaliddlt
col parse_calls_total format 99999 heading parsecallstotal
col parse_calls_delta format 99999 heading parsecallsdelta
col executions_total format 999999 heading exectotal
col executions_delta format 999999 heading execdelta
col fetches_total format 9999999 heading fetchestotal
col fetches_delta format 9999999 heading fetchesdelta
col end_of_fetch_count_total format 9999 heading endoffetchcalltotal
col end_of_fetch_count_delta format 9999 heading endoffetchcalldelta
col buffer_gets_total format 99999999 heading buffergetstotal
col buffer_gets_delta format 99999999 heading buffergetsdelta
col disk_reads_total format 999999 heading diskreadstotal
col disk_reads_delta format 9999999 heading diskreadsdelta
col rows_processed_total format 9999999 heading rowsprocessedtotal
col rows_processed_delta format 9999999 heading rowsprocesseddelta
col rows_ex format 999999 heading rowsexeccol snap_id format 99999 heading snapid
col ela_ex format 9999999.99 heading elapsedperexecution
col cwt_ex format 9999999.99 heading cwtperexecution
col instance_number format 99 heading inID

select sql_id, plan_hash_value,dba_hist_sqlstat.snap_id,
to_char(dba_hist_snapshot.BEGIN_INTERVAL_TIME,'dd-mm_hh24:mi') snap_beg,dba_hist_sqlstat.instance_number,invalidations_delta,
parse_calls_delta,executions_delta,fetches_delta,buffer_gets_delta,
disk_reads_delta,rows_processed_delta,elapsed_time_delta/1000 elapsed_time_delta,iowait_delta/1000 iowait_delta,clwait_delta/1000 cluster_wait_delta,ccwait_delta/1000 concurrency_wait_delta,optimizer_mode, optimizer_cost,
substr(optimizer_mode,1,3) opt,
case when executions_delta = 0 then NULL
when rows_processed_delta = 0 then NULL
else(rows_processed_delta/executions_delta)end rows_ex,
case when executions_delta = 0 then NULL
when clwait_delta = 0 then NULL
else(clwait_delta/executions_delta)/1000 end cwt_ex,
case when executions_delta = 0 then NULL
when elapsed_time_delta = 0 then NULL
else(elapsed_time_delta/executions_delta)/1000 end ela_ex
from dba_hist_sqlstat, dba_hist_snapshot
where sql_id='XXX'
and plan_hash_value='&PLAN_HASH_VALUE'
and dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id
and dba_hist_sqlstat.instance_number=dba_hist_snapshot.instance_number
order by dba_hist_sqlstat.instance_number,dba_hist_sqlstat.snap_id
/

The view DBA_HIST_SQLSTAT displays historical information about SQL statistics. Each statistic is stored in two separate columns:
metric_TOTAL for the total value of the statistic since instance startup.
metic_DELTA for the change in a statistic’s value between BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME that is stored in the DBA_HIST_SNAPSHOT view.

You can also query DBA_HIST_SQL_PLAN to compare the execution plans, if PLAN_HASH_VALUE has changed .

Delete archive logs from the standby server

I implemented the following script to delete archive logs from a standby server.
You cannot just delete the archive log files because that will leave entries in the V$ARCHIVED_LOG table.

#!/bin/ksh
#**************************************************************
# Here is where we loop through each SID in /etc/oratab . . .
#**************************************************************
cat /etc/oratab grep '^c.*:.*:[Yy]$' {
while IFS=":" read ORACLE_SID ORACLE_HOME junk
do
PATH=${ORACLE_HOME}/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH

# Now run the SQL script to generate a rm script
sqlplus -s /nolog <<EOF
connect / as sysdba
set heading off feedback off timing off
spool /usr/users/oracle/cronscripts/logs/rm_arch_${ORACLE_SID}.ksh
select 'rm '||NAME from v\$archived_log 
where REGISTRAR='RFS' 
and APPLIED='YES' 
and DELETED='NO' 
and COMPLETION_TIME < (SYSDATE-1);
spool off
exit
EOF


# Now run the generated rm script
chmod 740 /usr/users/oracle/cronscripts/logs/rm_arch_${ORACLE_SID}.ksh
/usr/users/oracle/cronscripts/logs/rm_arch_${ORACLE_SID}.ksh

# Now use RMAN to update V$ARCHIVED_LOG.DELETED
rman <<EOF
connect target /
crosscheck archivelog all;
delete noprompt expired archivelog all;
EOF

done
}

Notes:
columns V$ARCHIVED_LOG.REGISTRAR and APPLIED
If REGISTRAR='RFS' and APPLIEDis NO, then the log has arrived at the standby but has not yet been applied.
If REGISTRAR='RFS' and APPLIED is YES, the log has arrived and been applied at the standby database.

column V$ARCHIVED_LOG.DELETED
Indicates whether an RMAN DELETE command has physically deleted the archived log file from disk (YES) or not (NO)

RMAN EXPIRED
Removes only files whose status in the repository is EXPIRED.
RMAN marks backups and copies as expired when you run a CROSSCHECK command and the files are absent or inaccessible.
To determine which files are expired, run a LIST EXPIRED command.

RMAN NOPROMPT
Beginning in Oracle9i, RMAN's default behavior is to prompt for confirmation when you run DELETE EXPIRED.
In prior releases, RMAN did not prompt.

Fast Index Creation

I recently had to create a new index on a fairly large date ranged partitioned table. I tried a few permutations on test, but found the following steps to be the fastest;

01:01:03 SQL> alter session set workarea_size_policy=manual

Session altered.

01:13:44 SQL> alter session set hash_area_size=1073741824

Session altered.

01:13:44 SQL> alter session set sort_area_size=1073741824

Session altered.

01:13:44 SQL> select force_logging from v$database

FOR
---
YES

01:13:44 SQL> alter database no force logging

Database altered.

01:13:46 SQL>create index imei_prod.usage_profile_idx1 on imei_prod.usage_profile(msisdn)
01:13:46 2 nologging
01:13:46 3 compress
01:13:46 4 parallel 16
01:13:46 5 local
01:13:46 6 (
01:13:46 7 partition USAGE_199401 tablespace indx nologging compress,
01:13:46 8 partition USAGE_200106 tablespace indx nologging compress,
01:13:46 9 partition USAGE_200202 tablespace indx nologging compress,
01:13:46 10 partition USAGE_200207 tablespace indx nologging compress,
...
01:13:46 56 partition USAGE_200809 tablespace indx nologging compress,
01:13:46 57 partition USAGE_200810 tablespace indx nologging compress,
01:13:46 58 partition USAGE_200811 tablespace indx nologging compress
01:13:46 59 )
01:13:46 60 /

Index created

Elapsed: 01:58:57.99


03:12:44 SQL> SELECT st.sid, se.username, TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi')
03:12:44 2 logtime, se.program, (value/1048576) VALUE
03:12:44 3 FROM gv$sesstat st, gv$statname sn, gv$session se
03:12:44 4 WHERE username = 'MEYERM'
03:12:44 5 and sn.name = 'redo size'
03:12:44 6 AND sn.statistic# = st.statistic#
03:12:44 7 AND st.sid = se.sid
03:12:44 8 /


Redo
Generated
SID USERNAME Logon Time PROGRAM in MB
--------------------------------------------------------------
146 MEYERM 26-nov-08 01:13 sqlplus@pxxxx1 (TNS V1-V3) 116.583

03:12:46 SQL> alter database force logging

Database altered.

03:12:46 SQL> alter index imei_prod.usage_profile_idx1 logging;

Index altered.

Drop an unmounted ASM diskgroup

We are getting our hands dirty with ASM and will be moving all our databases from RAW devices to ASM in the next couple of months. My worst fear came to be when a Unix Sys Admin used a disk already allocated to ASM for a local filesystem (luckily this did not happen on a production server!).
When ASM realised this is immediatly issued a ALTER DISKGROUP PCASDG1 DISMOUNT FORCE command.
By the time the Unix Sys Admin returned the disk it was unrecognozable by ASM as the disk headers had been overwritten. The diskgroup was unrecoverable and unusable.

SQL> select GROUP_NUMBER, NAME, state from v$asm_diskgroup;
GROUP_NUMBER NAME STATE
------------ --------- ----------
0 PCASDG1 DISMOUNTED
2 PCASDGF MOUNTED
3 PCASDGR MOUNTED
3 rows selected.

SQL>alter diskgroup PCASDG1 mount;
alter diskgroup PCASDG1 mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "19" is missing

SQL>drop diskgroup PCASDG1 including contents;
drop diskgroup PCASDG1 including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "PCASDG1" does not exist or is not mounted

SQL>alter diskgroup PCASDG1 check;
alter diskgroup PCASDG1 check
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15001: diskgroup "PCASDG1" does not exist or is not mounted

I had to recreate the whole diskgroup from scratch. I followed Metalink Note 387103.1 to recreate the diskgroup.
Basically you clean up the asm disk header of all the disks in the diskgroup. This will also remove the whole diskgroup from asm.

SQL> select 'dd if=/dev/zero of='PATH' bs=8192 count=100' from v$asm_disk where GROUP_NUMBER=0;

$dd if=/dev/zero of=/dev/oracle/dg/c11t15d2 bs=8192 count=100
100+0 records in
100+0 records out

$dd if=/dev/zero of=/dev/oracle/dg/c11t7d0 bs=8192 count=100
100+0 records in
100+0 records out
etc ... 

SQL> select GROUP_NUMBER, NAME, state from v$asm_diskgroup;
GROUP_NUMBER NAME STATE
------------ --------- ----------
2 PCASDGF MOUNTED
3 PCASDGR MOUNTED
2 rows selected.

The diskgroup is now removed. I then used DBCA to recreate the ASM diskgroup, all these disks showed up as Candidate disks.

Oracle Data Guard Presentation

I had the privilege to present at the SAOUG (South African Oracle User Group) in Sun City last month. The conference was well organized, the location was great and we had close to 1000 attendees. I presented on Oracle Data Guard.

Thanks to Karen Morton (one of my favorite bloggers) for making me aware of SlideShare. To access my presentation, just click on the slideshare image on the right side and it will take you to my presentation on slideshare. Happy viewing!

Easy Streams Cookbook Example

We have Call Centres all over the country. The Call Centre’s IVR (interactive voice response, is a phone technology that allows a computer to detect voice and touch tones using a normal phone call) is used for routing calls to the correct call centre. The subscriber data is stored in 1 location and I was asked to replicate that data to 2 databases in other parts of the country. The main reasons are to have the data closer to the IVR system and also to relieve the main production system from all these queries.

Decisions: Use one-directional replication. Capture the changes at the source. Do the instantiation (first copy of data) with DataPump.

Preperation Steps

Enable Supplemental Logging (at source)
Use columns in PK constraints for Supplemental log group

ALTER TABLE "SCHEMA_ABC"."TABLE_ABC" ADD SUPPLEMENTAL LOG GROUP ivr_rep (XXMSISDN) ALWAYS;

This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

To confirm Supplemental Log Groups:

COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table' FORMAT A15
COLUMN ALWAYS HEADING 'Conditional or|Unconditional' FORMAT A14
COLUMN LOG_GROUP_TYPE HEADING 'Type of Log Group' FORMAT A20
SELECT LOG_GROUP_NAME, TABLE_NAME, DECODE(ALWAYS,'ALWAYS', 'Unconditional','CONDITIONAL', 'Conditional') ALWAYS, LOG_GROUP_TYPE
FROM DBA_LOG_GROUPS;

Create default tablespace for the Streams Administrator Account to store the queue table. Do these steps on the source and destinations.

CREATE TABLESPACE streams_tbs DATAFILE '/data/oradata/lfivrdb/streams_tbs.dbf' SIZE 7168M;

CREATE USER strmadmin IDENTIFIED BY STRMXXX 
  PROFILE VODA_COMPLIANT
  DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;
GRANT CONNECT, RESOURCE, DBA to STRMADMIN;
GRANT SELECT ANY DICTIONARY TO STRMADMIN;
GRANT SELECT_CATALOG_ROLE TO STRMADMIN;
EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

Set specific initialization parameters at the databases participating in the Streams setup

ALTER SYSTEM SET STREAMS_POOL_SIZE=200M SCOPE=BOTH;
ALTER SYSTEM SET AQ_TM_PROCESSES = 1 scope=both;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;
ALTER SYSTEM SET “_job_queue_interval”=1 scope=spfile;

(This is not a dynamic parameter and the db will need to be bounced)

Create 2 database links on the source to the 2 destinations.

CONNECT STRMADMIN/STRMXXX
CREATE DATABASE LINK LFIVRDB.VODACOM.CO.ZA CONNECT TO STRMADMIN IDENTIFIED BY STRMXXX USING ‘LFIVRDB.VODACOM.CO.ZA’;
CREATE DATABASE LINK BEIVRDB.VODACOM.CO.ZA CONNECT TO STRMADMIN IDENTIFIED BY STRMXXX USING ‘BEIVRDB.VODACOM.CO.ZA’;

STEP 1
Create Streams Queues
One Capture on the source db and one Apply on each destination:

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE STREAMS_TBS',
queue_name => ' STREAMS_CAPTURE_Q', 
queue_user => 'STRMADMIN');
END;
/

And on both dest sites:

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE STREAMS_TBS',
queue_name => ' STREAMS_APPLY_Q', 
queue_user => 'STRMADMIN');
END;
/

STEP2
Create Capture Process on Source.
This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

connect STRMADMIN/STRMXXX
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name         => '"SCHEMA_ABC"."TABLE_ABC"',
    streams_type       => 'CAPTURE',
    streams_name       => '"STREAMS_CAPTURE"',
    queue_name         => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
    include_dml        => true,
    include_ddl        => true,
    source_database    => 'DESTPRD.VODACOM.CO.ZA');
    END;
/

STEP3
Create 2 propagation process on the Source (one per destination)

connect STRMADMIN/STRMXXX
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
    streams_name        => '"STREAMS_PROP_LF"',
    source_queue_name   => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
    destination_queue_name  => '"STRMADMIN"."STREAMS_APPLY_Q"@LFIVRDB.VODACOM.CO.ZA',
    include_dml         => true,
    include_ddl         => true,
    source_database    => 'DESTPRD.VODACOM.CO.ZA',
    inclusion_rule      => true);
END;
/

And to the 2nd dest site:

connect STRMADMIN/STRMXXX
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
    streams_name        => '"STREAMS_PROP_BE"',
    source_queue_name   => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
    destination_queue_name  => '"STRMADMIN"."STREAMS_APPLY_Q"@BEIVRDB.VODACOM.CO.ZA',
    include_dml         => true,
    include_ddl         => true,
    source_database    => 'DESTPRD.VODACOM.CO.ZA',
    inclusion_rule      => true);
END;
/

STEP 4
Create 2 Apply processes. One on each destination.

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
streams_type => 'APPLY', 
streams_name => 'STREAMS_APPLY', 
queue_name => 'STRMADMIN.STREAMS_APPLY_Q',
include_dml => true, 
include_ddl => true, 
source_database    => 'DESTPRD.VODACOM.CO.ZA');
END;
/

And 2nd site

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
streams_type => 'APPLY', 
streams_name => 'STREAMS_APPLY', 
queue_name => 'STRMADMIN.STREAMS_APPLY_Q',
include_dml => true, 
include_ddl => true, 
source_database    => 'DESTPRD.VODACOM.CO.ZA');
END;
/

Instantiation. This is the initial copy of the table.

Set & Get SCN:

EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name =>  'SCHEMA_ABC.TABLE_ABC'); 

This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

To confirm the SCN number that you will need in a future step:

col scn for 9999999999999999
select * from dba_capture_prepared_tables 

Export table at source

expdp strmadmin/STRMXXX TABLES= SCHEMA_ABC.TABLE_ABC DUMPFILE=a%u.dmp parallel=8

compress and ftp dump files to the 2 destination servers

Import table at each destination
First create the schema owner of the table

create user SCHEMA_ABC identified by STRMXXX default tablespace streams_tbs;
grant connect, resource to SCHEMA_ABC
/

impdp strmadmin/STRMXXX TABLES=SCHEMA_ABC.TABLE_ABC DUMPFILE=a%u.dmp remap_tablespace=TBS_ABC:streams_tbs 

Manually instantiating the objects at each destination

connect STRMADMIN/STRMXXX
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCHEMA_ABC.TABLE_ABC',
source_database_name => 'DESTPRD.VODACOM.CO.ZA',
instantiation_scn => &iscn);
END;
/
Enter value for iscn: 

STEP5
Finally start the Process:
On source

CONNECT strmadmin/STRMXXX
exec dbms_capture_adm.start_capture(capture_name => 'STREAMS_CAPTURE');

At 1st destination:

CONNECT strmadmin/STRMXXX
exec dbms_apply_adm.start_apply(apply_name => 'STREAMS_APPLY');

At 2nd destination:

CONNECT strmadmin/STRMXXX
exec dbms_apply_adm.start_apply(apply_name => 'STREAMS_APPLY');

Post Installation steps
On Source force Stream checkpoint (see Metalink Note:342342.1)

EXEC DBMS_CAPTURE_ADM.SET_PARAMETER('STREAMS_CAPTURE','_CHECKPOINT_FORCE','Y');

On each destination

EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'STREAMS_APPLY', parameter => 'WRITE_ALERT_LOG', value => 'Y' );
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'STREAMS_APPLY',parameter => 'DISABLE_ON_ERROR',value => 'N' );