Database upgrade from 9i to 10g on Linux ( Oracle 11i)

Linux Box Preparation Steps
O/s level settings for oracle

Modify number of open file descriptors

Open /etc/security/limits.conf and change the existing values for "hard" and "soft" parameters as follows:
oratest soft nofile 8192
oratest hard nofile 63536
oratest soft nproc 4096
oratest hard nproc 16384
oratest soft memlock 15728640
oratest hard memlock 15728640

Modify port range value
Open /etc/sysctl.conf and change the port range value as follows. Reboot the system after you make this change.
net.ipv4.ip_local_port_range = 1024 65000
If you don’t change the same, there will be error during software installation pre-check, which needs to be ignored.

Check if Asyn I/o is enabled at Linux Database box
output with async io enabled.
$ cat /proc/slabinfo grep kio
kioctx 3060 3060 128 102 102 1 : 4032 1008
kiocb 83648 89820 128 2864 2994 1 : 4032 1008
kiobuf 36444 38460 128 1280 1282 1 : 4032 1008

output with async io disabled.
$ cat /proc/slabinfo grep kio
kioctx 0 0 128 0 0 1 : 252 126
kiocb 0 0 96 0 0 1 : 252 126
kiobuf 0 0 64 0 0 1 : 252 126

If the same is not enabled, please enable the same.

Check if hangcheck timer is set
/sbin/lsmod grep hangcheck

Check Network Requirements
Configure all nodes with two network adapters with TCP/IP support: one for the public network interface and one for the private network interface (interconnect).

In RAC Environment interconnect (Private Network) must support the UDP.

Each public network interface should be assigned an IP address and associated host name, registered in DNS. One virtual IP address (VIP) and an associated virtual host name registered in DNS, or resolved in the hostfile, or both. Each Private interface must be assigned a private IP address.

You can configure database to use either the virtual host name or virtual IP address.

Check Kernel Parameters and Linux Packages

Check for O/S Version in DB node.
$cat /etc/issue Red Hat Enterprise Linux
or
$cat /etc/*release*
AS release 3 (Taroon Update 6 Kernel Version – uname –r 2.4.21-37.ELsmp

Verify that the cat /etc/sysconfig/network file contains an entry that is similar to the following: HOSTNAME=.. The same needs to be run on DB node as root user or sudo.

NETWORKING=yes
HOSTNAME=testoracledb02.net
GATEWAY=10.36.130.10

Check the current O/s packages installed in DB
use following command.


rpm -q glib openmotif setarch
rpm -qa grep compat

The following patches (Minimum) must be installed:
compat-db-4.0.14.5
compat-gcc-7.3-2.96.122
compat-gcc-c++-7.3.2.96.122
compat-libstdc++-devel-7.3-2.96.122
openmotif21-2.1.30-8.i386.rpm
setarch-1.3-1

Ensure to Install Red Hat packages and apply patches rpm -qa --queryformat "%{NAME}-%{VERSION}.%{RELEASE} (%{ARCH})\n" grep libaio
+ compat-db-4.1.25-9
+ compat-gcc-32-3.2.3-47.3
+ compat-gcc-32-c++-3.2.3-47.3
+ compat-oracle-rhel4-1.0-3
+ compat-libcwait-2.0-1
+ compat-libgcc-296-2.96-132.7.2
+ compat-libstdc++-296-2.96-132.7.2
+ compat-libstdc++-33-3.2.3-47.3
+ xorg-x11-deprecated-libs-devel-6.8.1-23.EL
+ xorg-x11-deprecated-libs-6.8.1-23.EL

In addition, install the following rpm's (available on Disk3 of the RHEL4.0 media)
+ openmotif21-2.1.30-11
+ libaio-0.3.102-1
+ libaio-devel-0.3.102-1

Check following Kernel parameters
kernel.sem=250 32000 500 150
kernel.shmmax=34359738368
kernel.shmmni=4096
kernel.shmall=3279547
fs.file-max=23278304
kernel.msgmni=2878
vm.nr_hugepages=65
net.core.rmem_default=1048576
net.core.wmem_default=262144
net.core.rmem_max=1048576
net.core.wmem_max=262144

Cluster Verification Utility (CVU)

Install cvuqdisk Package for Linux using following command

rpm -iv cvuqdisk-1.0.1-1.rpm

This cvuqdisk RPM package is located under clusterware/rpm directoryon the installation media.

Check rsh, ssh, rcp, scp and host equivalence

a. Verify that you have rsh (remote shell) package installed on all your hosts, by executing the command: rpm -qa grep -i rsh


b. Check host has Public and private rac entries.

$cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
10.36.130.108 testoracledb01.net testoracledb01
10.36.130.245 testoracledb01.net testoracledb02
192.168.0.10 testoracledb01rac
192.168.0.11 testoracledb02rac


c. Check ssh is enabled between both Cluster nodes doing scp, from one node a test file to another node. If it does not ask for password then ssh is enabled. If it asks for password, then its not enabled.

d. Configure Secure Shell (SSH) on all cluster nodes as follows: Log in as the oracle user.
$ cd .ssh ( if not present make directory .ssh under /home/oracle/ )
$ chmod 755 ~/.ssh
e. Generate a RSA key using the following commands
$ /usr/bin/ssh-keygen -t rsa (Accept the default for all the prompt)
f. Generate a DSA key for the SSH protocol:
$ /usr/bin/ssh-keygen -t dsa (Accept the default location for the key file at the prompt )
g. SCP the id_rsa.pub and id_dsa_pub generated to other node.
h. Copy the contents of the id_rsa.pub and id_dsa.pub files to the /.ssh/authorized_keys file on this node, and to the same file on all other cluster nodes.
$ cat home/oracle/.ssh/id_rsa.pub >> /home/orac/.ssh/authorized_keys
i. Change the permissions on the home/oracle/.ssh/authorized_keys file on all cluster nodes: $ chmod 644 ~/.ssh/authorized_keys
j. After all done very again, Connect at ssh to each other node, it should not ask for password. From testoracledb01
$ssh testoracledb02
From testoracledb02
$ssh testoracledb01
It should not ask for password.

k. Use the following syntax to determine pre-installation steps status

//clusterware/cluvfy/runcluvfy.sh stage -pre crsinst -n testoracledb01, testoracledb02 nodes in your cluster must be separated by commas. for network

//clusterware/cluvfy/runcluvfy.sh comp nodecon -n testoracledb01, testoracledb02 -verbose for operating system requirement using CUV

//clusterware/cluvfy/runcluvfy.sh comp sys -n testoracledb01, testoracledb02 -p {crsdatabase}

example
$. ./runcluvfy.sh comp sys -n testoracledb01, testoracledb02 -p crs

Pre Upgrade Patches – (On your current 9i Database)

You may need to apply some Application interoperability patches before you upgrade your database from 9i to 10g.

Note: - If the LINUX BOX is already having CRS Installed perform the below mentioned steps:

1). Clean the .bash_profile on both the nodes
2). Move /home/oracle/oraInventory to /home/oracle/oraInventory.old
3). Move /var/opt/oracle to /var/opt/oracle.old
4). Execute the following:
rm /etc/oracle/*
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -f /etc/inittab.crs
cp /etc/inittab /etc/inittab.orig

Install Oracle Clusterware 10.2.0.1

Start runInstaller from the Oracle Clusterware 10.2.0.1 staging area. Ensure we create directory structure for CRS OH /u01/ora/product/10.2.0 Ensure you have correct Java version installaed in the system.

[oracle@testoracledb01 bin]$ /u01/ora/product/jdk142/jdk/bin/java -version
java version "1.4.2_11" Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_11-b06) Java HotSpot(TM) 64-Bit Server VM (build 1.4.2_11-b06, mixed mode)

Start installation from staging area

$./runInstaller -jreLoc /u01/ora/product/jdk142/jdk
In the Specify Home details Window, enter the name and path of CRS ORACLE_HOME and click Next.

In the Cluster Configuration Window, enter the name of the Cluster Configuration. Specify public node, corresponding private node names for the public nodes and corresponding virtual host names for the public host names.

In next screen specify network interface with the interface type
Enter the location for Oracle Cluster Registry (OCR) and click Next.

In case of upgrade of RAC DB, this OCS screen does not appear. Oracle 9i server manager file identified in /var/opt/oracle/srvConfig.loc (in our case, srvm.dbf) that exists on shared storage will become the new OCR file required for 10g.
Enter the location and the name for Voting Disk (e.g. /ocfs01_msa/ora10g/voting.dbf). Click Next.
Please create a directory for the voting disk in shared file system beforehand.

NOTE: The OCR and Voting Disks must be located on a shared file system that is accessible by all nodes.

Verify the installation Summary Window and Click Install.
Exexcute root.sh from all nodes at the end of the installation. Execute root.sh from the CRS ORACLE_HOME specified after logging in as root in a different terminal window. Root.sh will also start CRS services on both the cluster nodes.

Before Running root.sh please create softlink for JDK in CRS_ORACLE_HOME to avoid following error

# # An unexpected error has been detected by HotSpot Virtual Machine:
# # SIGILL (0x4) at pc=0x2000000003aa4070, pid=12563, tid=16384
# # Java VM: Java HotSpot(TM) 64-Bit Server VM (1.4.2_10-b03 mixed mode)
# Problematic frame: # /u01/ora/product/10.2.0/crs/install/rootconfig: line 1046: 12563 Segmentation fault $CH/bin/vipca -silent -nodelist

$CRS_NODELIST -nodevips $CRS_NODEVIPS
$ sudo ./root.sh
WARNING: directory '/u01/ora/product/10.2.0' is not owned by root
WARNING: directory '/u01/ora/product' is not owned by root
WARNING: directory '/u01/ora' is not owned by root
Checking to see if Oracle CRS stack is already configured /etc/oracle does not exist. Creating it now.
Setting the permissions on OCR backup directory
Setting up NS directories Oracle Cluster Registry configuration upgraded successfully WARNING: directory '/u01/ora/product/10.2.0' is not owned by root.
WARNING: directory '/u01/ora/product' is not owned by root
WARNING: directory '/u01/ora' is not owned by root clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node : node 1: testoracledb01 testoracledb01rac testoracledb01
node 2: testoracledb02 testoracledb02rac testoracledb02
clscfg: Arguments check out successfully.
NO KEYS WERE WRITTEN. Supply -force parameter to override. -force is destructive and will destroy any previous cluster configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
testoracledb01
testoracledb02
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps

#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
# SIGILL (0x4) at pc=0x2000000003aa4070, pid=12563, tid=16384
#
# Java VM: Java HotSpot(TM) 64-Bit Server VM (1.4.2_10-b03 mixed mode)
# Problematic frame:
# /u01/ora/product/10.2.0/crs/install/rootconfig: line 1046: 12563 Segmentation fault $CH/bin/vipca -silent -nodelist $CRS_NODELIST -nodevips $CRS_NODEVIPS

As discussed earlier, root.sh in the last node will throw error like:
Oracle CRS stack installed and running under init(1M) Running vipca(silent) for configuring nodeapps. The given interface(s), "eth0" is not public. Public interfaces should be used to configure virtual IPs.

Solution for this is to run vipca manually after completing the installation Run VIPCA manually from CRS_ORACLE_HOME/Bin as root user. It will open another window to do VIP configuration

$ sudo ./vipca

Install Oracle Software 10.2.0.1

From the staging area, where u have downloade 10.2.0.1 software and unzipped the same. Go to that same location and run its installer.

$./runInstaller -jreLoc /u01/ora/product/jdk142/jdk

A GUI first screen comes, the same ask for Select Installation Method. Check the button Advanced Installation option and Click Next.

The next screen asks for Select Installation Type. Check the button Oracle Enterprise Edition 10.2.0.1 from the list of options, and click Next.

The next screen asks for Specify Home Details. Check under Path option the Oracle Home it is showing is correct one and is pointing to new 10g location. Else change the same to the new 10g Oracle Home location then Click on Next Button

In Cluster Nodes Window, verify the cluster nodes shown for the installation. Select all nodes for installation, and click Next.

Then comes the Product specific pre-requisites checks. The same will be performed and it will pass all. Then click Next. If you see any error or warning need to correct and start the same again.

Then the Screen come Upgrade an Existing Database. Select NO on the same, and click Next.

The Next screen asks for Select Configuration Options. Select the checkbox Install Database Software Only option, and then click on Next Button.

On the database creation Window, click Install Database Software only, and click Next.

Next comes the Summary Screen. Check all is correct and then Click on Install Button.

Then the Installation starts and you will see the progress of it.
During the course of the installation, the Installer will prompt for executing root.sh. Log on to another terminal of same Database box and go to ORACLE_HOME location. Either you log on as root or your own login and do sudo to run as root.

$export $ORACLE_HOME=/u01/ora/product/oracle10g
$cd $ORACLE_HOME
$sudo ./root.sh

During running ./root.sh it is promt for various overwrite of files. Type y for each time it asks for.Once the same is done, return to Graphical Interface Installation screen and Click on OK button for the promt.
Final screen comes End of Installation.
Click on Exit Button once its done.

Install: “Oracle DB 10g (10.2.0.1)Products” from Companion CD

Go to the Staging home location and execute the installer as below.
$ ./runInstaller –jreLoc /u01/ora/product/jdk142/jdk
First screen comes Select the Product Install. Check on box to select Oracle Database 10g Products 10.2.0.1.0 and then click on Next button.

Next comes sceen Specify Oracle Home Details. Check and ensure its showing correct 10g Oracle Home location. Click on Next button.

Next screen is Product Specific pre-requisites check. Will check all pre req and give report of Success at the end. After which click on Install Button.

Then comes the Summary Screen. Crosscheck if all are correct and then press on Install Button. Installation starts with window showing its progress.

Final screen is End of Installation, showing the url details. Click on Exit button on the same. You are done.

Apply 10.2.0.3.0 or 10.2.0.4.0 patch to cluster and oracle home

Set the ORACLE_HOME variable to the 10.2.0.1 CRS ORACLE_HOME installed earlier in CRS 10.2.0.1 install.
Shut down all the CRS services on all the nodes in the cluster.
$10g CRS ORACLE_HOME/bin/crsctl stop crs

Execute runInstaller from the 10.2.0.2 staging area and upgrade the CRS software to 10.2.0.3.0 At the end of the installation, the Installer will prompt for executing root.sh from all the nodes. Log in as root in a different terminal window, and execute root.sh.

$ export ORACLE_HOME=/u01/ora/product/10.2.0/crs
$ ./runInstaller -jreLoc /u01/oracle/OraHome_1/jdk/

Unset the existing ORACLE_HOME, and set the same to point to 10.2.0.1 ORACLE_HOME. Execute Installer, under 10.2.0.3 patchset staging location.

$./runInstaller -jreLoc /u01/oracle/OraHome_1/jdk/

Screen gives you the summary on Installation, Click on Install Button.
Installation starts and you see its progress. Then comes End of Installation screen, here you will click on Exit button. You are done with software upgrade work. This will upgrade the 10g Software from 10.2.0.1 to 10.2.0.3 (Binaries)

Apply RDBMS Patch or CPU

Once binary upgrade is done you can apply any RDBMS patch or CPU patch to CRS or ORACLE_HOME.

Pre – Database Upgrade 9i to 10g

Run the pre-upgrade information tool using following steps. (DB1) Open another terminal session connecting to Database as oracle user.

$echo $ORACLE_HOME (This should point to 9i ORACLE HOME) $cp /u01/ora/product/oracle10g/rdbms/admin/utlu102i.sql .
SQL>spool instance_preinfo102.log
mailto:SQL@utlu102i.sql

Check for the deprecated CONNECT Role (DB1) After upgrading to 10gR2, the CONNECT role will only have the CREATE SESSION privilege. The other privileges granted to the CONNECT role in earlier releases will be revoked during the upgrade. To identify which users and roles in your database are granted. In Oracle 9i Database session SQLPLUS run the following query.

$echo $ORACLE_HOME (check it should point to 9i OH)
$sqlplus Username: / as sysdba
SQL>spool connect_dep.log
SQL> SELECT grantee FROM dba_role_privs WHERE granted_role = 'CONNECT' and grantee NOT IN ( 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT'
SQL> exit

In Oracle 10.2 the CONNECT role only includes CREATE SESSION privilege.

$ sqlplus '/as sysdba'
SQL> create role connect_sub;
SQL> grant CREATE VIEW ,CREATE TABLE,ALTER SESSION,CREATE CLUSTER,CREATE SESSION,CREATE SYNONYM,CREATE SEQUENCE,CREATE DATABASE LINK to connect_sub;
SQL> Spool assign_role.sql
SELECT 'grant connect_sub to ' grantee ';' FROM dba_role_privs WHERE granted_role = 'CONNECT' and grantee NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS','ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY','WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS','WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER','OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA','SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

Startup the 10g database to upgrade.

$echo $ORACLE_HOME (should point to 10g OH)
$sqlplus "/ as sysdba"
SQL>STARTUP UPGRADE

Create Mandatory SYSAUX tablespace.

SQL> CREATE TABLESPACE sysaux DATAFILE '/u03_iscsi/ora/testdata/ocfsd19/sysaux01.dbf' SIZE 2000M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;

Run the upgrade script. Spool the output so you can take a look at possible errors after the upgrade:

SQL> spool upgrade.log
SQL> @/u01/ora/product/oracle10g/rdbms/admin/catupgrd.sql
SQL> spool off

This takes a long time in my case took 1 and ½ hours.

Verify the upgrade, Run utlu102s.sql, specifying the TEXT option:

$echo $ORACLE_HOME (ensure its 10g home)
$cd /home/oracle/10g_upgrade
$sqlplus "/ as sysdba"
SQL>spool verify_upg.log
SQL>@ /u01/ora/product/oracle10g/rdbms/admin/utlu102s.sql TEXT
SQL>spool off;

Check the output if all product has been successfully updated.
Restart the database. (10g Database)

$sqlplus "/ as sysdba"
SQL>SHUTDOWN IMMEDIATE; (DO NOT USE SHUTDOWN ABORT!!!!!!!!!)
SQL>STARTUP

Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

$sqlplus "/ as sysdba"
SQL>@ /u01/ora/product/oracle10g/rdbms/admin/utlrp.sql
This will take long time a hour or more.

Post Database Upgrade 10.2.0.3

Update Environment Variables after the Upgrade. Make sure that the following environment variables point to the new release 10.2 directories: (To be done on both Database node.) ORACLE_HOME, PATH , ORA_NLS10 , LD_LIBRARY_PATH.

Gather sys schema statistics.
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node.
Use SQL*Plus to connect to the database as SYSDBA and restart the database in restricted mode, run adstats.sql, and restart the database in normal mode.

Execute assign_role.sql which we generated in earlier step.

Unlock schema and table statistics using unlock_statistics.sql

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

execute dbms_stats.unlock_schema_stats('APPLSYS');
execute dbms_stats.unlock_schema_stats('XNP');
execute dbms_stats.unlock_schema_stats('XXHL');
execute dbms_stats.unlock_schema_stats('AR');
execute dbms_stats.unlock_schema_stats('IEO');
execute dbms_stats.unlock_schema_stats('CCT');
execute dbms_stats.unlock_schema_stats('SYSTEM');
execute dbms_stats.unlock_schema_stats('AMV');
execute dbms_stats.unlock_schema_stats('ASO');
execute dbms_stats.unlock_schema_stats('IEM');
execute dbms_stats.unlock_schema_stats('XDP');
execute dbms_stats.unlock_schema_stats('APPS');
execute dbms_stats.unlock_schema_stats('IBU');
execute dbms_stats.unlock_schema_stats('SYS');
execute dbms_stats.unlock_schema_stats('JTF');
execute dbms_stats.unlock_schema_stats('OKC');

TUNING STEPS for 10g NEW FEATURE

SGA memory automatic tuning, new parameter SGA_TARGET and other needs to be calculated and set.
sga_target = 1G
shared_pool_size = 400M
shared_pool_reserved_size = 40M
log_buffer = 10M
Remove or comment the following entry from init.ora file –
db_cache_size
sga_max_size
java_pool_size
large_pool_size

The following parameters will be auto tuned new database feature, on spfile. Change STATISTICS_LEVEL=TYPICAL for AWR to collect statistics.

Enter 10g specific entry in init parameter files.
olap_page_pool_size = 4194304
plsql_optimize_level = 2 #MP
plsql_code_type = native #MP
plsql_native_library_dir = /prod11i/plsqlnativelib
plsql_native_library_subdir_count = 149
_b_tree_bitmap_plans = FALSE #MP
_kks_use_mutex_pin=TRUE
optimizer_secure_view_merging = FALSE
optimizer_dynamic_sampling = 2
disk_asynch_io = TRUE
_optimizer_cost_based_transformation=off ##put only for 11.5.9

How to Configure Oracle Streams in Oracle environment

In this post i am going to discuss steps to configure Oracle Streams in Oracle environment. We wouldn't be discussing about streams in details but steps only. If you need more details please refer the oracle documentation.

1. Streams Configuration Setup – Source System

• Add following parameters to source and destination instances
  log_archive_format='ORCLPRODA_%t_%s_%r.arc'
  log_archive_dest_1='LOCATION=/ocfsd106/ora/arch/ORCLPRODA'
  STREAMS_POOL_SIZE =500M
  _job_queue_interval=1
• Create tablespace to hold stream data Source
  CREATE TABLESPACE STREAMS_DATA DATAFILE '/ocfsd105/ora/ORCLPRODdata/ocfsd106/streamd01.dbf' SIZE 1000 M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT
  SPACE MANAGEMENT AUTO;  
• Create/Setup Stream User to hold stream data
  
  CREATE USER strmadmin
  IDENTIFIED BY strmadmin
  DEFAULT TABLESPACE STREAMS_DATA
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON STREAMS_DATA;
  
  GRANT CONNECT, RESOURCE, DBA TO strmadmin;
  GRANT SELECT ANY DICTIONARY TO STRMADMIN;
  GRANT AQ_ADMINISTRATOR_ROLE to STRMADMIN;
  GRANT SELECT ANY DICTIONARY TO STRMADMIN;
  GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
  GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
  GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;
  GRANT SELECT_CATALOG_ROLE TO strmadmin;
  
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'ENQUEUE_ANY',
  grantee => 'STRMADMIN',
  admin_option => FALSE);
  END;
  /
  
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'DEQUEUE_ANY',
  grantee => 'STRMADMIN',
  admin_option => FALSE);
  END;
  /
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'MANAGE_ANY',
  grantee => 'STRMADMIN',
  admin_option => TRUE);
  END;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  END;
  /
  
  BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  END;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  END;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,
  object_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
  grantee => 'STRMADMIN',
  grant_option => FALSE );
  END;
  
  /
• Create database Link from Source to Target
  update global_name set global_name='ORCLPROD.WORLD';
  
  CREATE DATABASE LINK "ORCLREP.WORLD" CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING 'ORCLREP';
• Create Streams queue for each schema
  
  sqlplus strmadmin/strmadmin
  
  BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
  queue_name => 'STRMADMIN.STREAMS_SQUEUE',
  queue_table =>' STRMADMIN.STREAMS_SQUEUE_TABLE',
  queue_user => 'STRMADMIN');
  END;
  
  It's a good idea to create sepereta queue for each schema you are planning to replicate.
  
  
• Create capture process and Rules for the tables at the source database
  BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES (
  table_name => 'APPLSYS.FND_TERRITORIES_TL',
  streams_type => 'CAPTURE',
  streams_name => 'STRMADMIN_CAPTURE_APPLSYS',    queue_name => 'STRMADMIN.STREAMS_APPSQUEUE',  include_dml => true,
  include_ddl => true,
  source_database => 'ORCLPROD');
  END;
  /
  
  Add propagation rules for the table at the source database. This step will also create a propagation job to the destination database.
  
  Example:
  
  BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
  table_name => 'APPLSYS.FND_TERRITORIES_TL',
  streams_name => 'STRMADMIN_PROPAGATE',
  source_queue_name => 'STRMADMIN.STREAMS_SQUEUE',
  destination_queue_name => 'STRMADMIN.STREAMS_TQUEUE@ORCLREP',
  include_dml => true,
  include_ddl => true,
  source_database => 'ORCLPROD');
  END;
  

2. Streams Configuration Setup – Target System

• Create tablespace to hold stream data
  
  CREATE TABLESPACE STREAMS_DATA DATAFILE '/ocfsr101/ora/ORCLREPdata/streamd01.dbf' SIZE 1000 M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT
  SPACE MANAGEMENT AUTO;
  
• Create/Setup Stream User to hold stream data
  
  CREATE USER strmadmin
  IDENTIFIED BY strmadmin
  DEFAULT TABLESPACE STREAMS_DATA
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON STREAMS_DATA;
  
  GRANT CONNECT, RESOURCE, DBA TO strmadmin;
  GRANT SELECT ANY DICTIONARY TO STRMADMIN;
  GRANT AQ_ADMINISTRATOR_ROLE to STRMADMIN;
  GRANT SELECT ANY DICTIONARY TO STRMADMIN;
  GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
  GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
  GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;
  GRANT SELECT_CATALOG_ROLE TO strmadmin;
  
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'ENQUEUE_ANY',
  grantee => 'STRMADMIN',
  admin_option => FALSE);
  END;
  /
  
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'DEQUEUE_ANY',
  grantee => 'STRMADMIN',
  admin_option => FALSE);
  END;
  /
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'MANAGE_ANY',
  grantee => 'STRMADMIN',
  admin_option => TRUE);
  END;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  END;
  /
  
  BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  end;
  /
  
  begin
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  END;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  END;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,
  object_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
  grantee => 'STRMADMIN',
  grant_option => FALSE );
  END;
  
  /
• Create Tablespaces and Users at Destination database
  
  CREATE TABLESPACE ONTD DATAFILE '/ocfsr101/ora/ORCLREPdata/ontd01.dbf' SIZE 8000 M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
  
  CREATE USER ONT
  IDENTIFIED BY ONT
  DEFAULT TABLESPACE ONTD
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON ONTD;
  
  GRANT CONNECT, RESOURCE TO ONT;
  
  alter user ONT QUOTA UNLIMITED ON ONTX;
• Export tables from source database to target database
  
  Source
  
  exp ont/ont tables=(OE_ORDER_LINES_ALL) file=('ONT_ORCLPROD01.dmp') log=ONT_ORCLPROD.log FILESIZE=4G DIRECT=YES
  
  Target
  
  imp ont/ont file=('ONT_ORCLPROD01.dmp') log=ONT_ORCLREP.log FULL=Y
• Create database Link from Target to Source
  update global_name set global_name='ORCLREP.WORLD';
  
  CREATE DATABASE LINK "ORCLPROD.WORLD"
  CONNECT TO STRMADMIN
  IDENTIFIED BY STRMADMIN
  USING 'ORCLPROD';
• Create Streams queue for each schema
  
  sqlplus strmadmin/strmadmin
  
  BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
  queue_name => 'STRMADMIN.STREAMS_TQUEUE',
  queue_table =>' STRMADMIN.STREAMS_TQUEUE_TABLE',
  queue_user => 'STRMADMIN');
  END;
• Create Apply process and Rules for the tables at the destination database
  
  Example:
  
  BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'APPLSYS.FND_TERRITORIES_TL',
  streams_type => 'APPLY',
  streams_name => 'STRMADMIN_APPLY',
  queue_name => 'STRMADMIN.STREAMS_TQUEUE',
  include_dml => true,
  include_ddl => true,
  source_database => 'ORCLPROD');
  END;
  /
  
  
• Grant Full permission on all objects to stream admin user
  
  Example:
  
  grant all on APPLSYS.FND_TERRITORIES_TL to STRMADMIN;
• Activate stream admin user to apply all DML and DDL changes
  
  The user specified in the APPLY_USER parameter must have the necessary privileges to perform DML and DDL changes on the apply objects.
  
  BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
  apply_name => 'STRMADMIN_APPLY',
  apply_user => 'STRMADMIN');
  END;

3. Instantiate Table level Replication

• Find the SCN number in the source
  
  set serveroutput on
  DECLARE
  iscn NUMBER;
  BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_OUTPUT.PUT_LINE (iscn);
  END;
  /
  
  DECLARE
  source_scn NUMBER;
  BEGIN
  source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
  source_object_name => 'APPLSYS.FND_TERRITORIES_TL',
  source_database_name => 'ORCLPROD',
  instantiation_scn => source_scn);
  END;
  /
  
  Instantiate the objects in the target. The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table are to be applied by the apply process. If the commit SCN of an LCR from the source database is less than or equal to this instantiation SCN, then the apply process discards the LCR else, the apply process applies the LCR.
  
  BEGIN
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
  source_object_name => 'APPLSYS.FND_TERRITORIES_TL',
  source_database_name => 'ORCLPROD',
  instantiation_scn => 8221381383942);
  END;
  DECLARE source_scn NUMBER; BEGIN
  source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@ORCLREP( source_object_name => 'APPLSYS.FND_TERRITORIES_TL', source_database_name => 'ORCLPROD, instantiation_scn => source_scn); END;/
  
  
• Set performance tuning parameters in source and target database
  begin
  dbms_capture_adm.set_parameter('STRMADMIN_CAPTURE','_checkpoint_frequency','400');
  end;
  
  begin
  dbms_capture_adm.set_parameter(capture_name=>'STRMADMIN_CAPTURE',checkpoint_retention_time=>2); end; Begin dbms_capture_adm.set_parameter('CAPTURE_NAME','_CHECKPOINT_FORCE','Y'); END;
  
  begin
  dbms_aqadm.alter_propagation_schedule('STRMADMIN.STREAMS_SQUEUE','ORCLREP',latency=>1);
  end;
• Start the capture process in the source
  
  BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STRMADMIN_CAPTURE');
  END;
  /
• Start the apply process in the target
  
  BEGIN
  DBMS_APPLY_ADM.START_APPLY(apply_name => 'STRMADMIN_APPLY');
  END;
  /
  
• Set parameters for Apply Process at target site
  
  begin
  dbms_apply_adm.set_parameter('STRMADMIN_APPLY','disable_on_error','n');
  end;
  
  Begin
  dbms_apply_adm.set_parameter('STRMADMIN_APPLY','_TXN_BUFFER_SIZE',2);
  end;
  Begin
  dbms_apply_adm.set_parameter('STRMADMIN_APPLY','_DYNAMIC_STMTS','Y');
  end;
• Monitor the apply process in the target
  
  SELECT (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
  TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
  "Event Creation",
  TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
  HWM_MESSAGE_NUMBER "Applied Message Number"
  FROM V$STREAMS_APPLY_COORDINATOR
  WHERE APPLY_NAME = 'STRMADMIN_APPLY';
  
  SELECT
  (APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
  TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
  "Event Creation",
  TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
  APPLIED_MESSAGE_NUMBER "Applied Message Number"
  FROM DBA_APPLY_PROGRESS
  WHERE APPLY_NAME like 'STRMADMIN_APPLY';
  
  SELECT DECODE(ap.APPLY_CAPTURED,
  'YES','Captured LCRS',
  'NO','User-enqueued messages','UNKNOWN') APPLY_CAPTURED,
  SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,
  r.STATE,
  r.TOTAL_ASSIGNED,
  r.TOTAL_MESSAGES_APPLIED
  FROM V$STREAMS_APPLY_SERVER R, V$SESSION S, DBA_APPLY AP
  WHERE r.APPLY_NAME like 'STRMADMIN_APPLY' AND
  r.SID = s.SID AND
  r.SERIAL# = s.SERIAL# AND
  r.APPLY_NAME = ap.APPLY_NAME
  ORDER BY r.SERVER_ID;
  
  SELECT q.owner, q.name, t.queue_table, t.owner_instance
  FROM DBA_QUEUES q, DBA_QUEUE_TABLES t
  WHERE t.object_type = 'SYS.ANYDATA' AND
  q.queue_table = t.queue_table AND
  q.owner = t.owner;
  
• Flow Control Query COLUMN queue_schema FORMAT A12 COLUMN queue_name FORMAT A20 SELECT queue_schema, queue_name, unbrowsed_msgs, overspilled_msgs, memory_usage, publisher_stateFROM V$BUFFERED_PUBLISHERS;
  
  SELECT
  subscriber_name, cnum_msgs, total_dequeued_msg, total_spilled_msg
  FROM V$BUFFERED_SUBSCRIBERS;
• Dynamic Streams views V$STREAMS_CAPTURE GV$STREAMS_CAPTURE V$STREAMS_APPLY_COORDINATOR GV$STREAMS_APPLY_COORDINATOR V$STREAMS_APPLY_READER GV$STREAMS_APPLY_READER V$STREAMS_APPLY_SERVER GV$STREAMS_APPLY_SERVER V$STREAMS_POOL_ADVICE GV$STREAMS_POOL_ADVICE V$STREAMS_TRANSACTION GV$STREAMS_TRANSACTION V$BUFFERED_PUBLISHERS GV$BUFFERED_PUBLISHERS V$BUFFERED_QUEUES GV$BUFFERED_QUEUES V$BUFFERED_SUBSCRIBERS GV$BUFFERED_SUBSCRIBERS V$PROPAGATION_RECEIVER GV$PROPAGATION_RECEIVER V$PROPAGATION_SENDER GV$PROPAGATION_SENDER V$RULE GV$RULE V$RULE_SET GV$RULE_SET V$RULE_SET_AGGREGATE_STATS GV$RULE_SET_AGGREGATE_STATS
• Static Streams Views Capture Views DBA_CAPTURE DBA_CAPTURE_EXTRA_ATTRIBUTES DBA_CAPTURE_PARAMETERS DBA_CAPTURE_PREPARED_DATABASE DBA_CAPTURE_PREPARED_SCHEMAS DBA_CAPTURE_PREPARED_TABLES
• Apply Views DBA_APPLY DBA_APPLY_CONFLICT_COLUMNS DBA_APPLY_DML_HANDLERS DBA_APPLY_ENQUEUE DBA_APPLY_ERROR DBA_APPLY_EXECUTE DBA_APPLY_INSTANTIATED_GLOBAL DBA_APPLY_INSTANTIATED_OBJECTS DBA_APPLY_INSTANTIATED_SCHEMAS DBA_APPLY_KEY_COLUMNS DBA_APPLY_OBJECT_DEPENDENCIES DBA_APPLY_PARAMETERS DBA_APPLY_PROGRESS DBA_APPLY_SPILL_TXN DBA_APPLY_TABLE_COLUMNS DBA_APPLY_VALUE_DEPENDENCIES
• Propagation & Queue Views Streams Views DBA_PROPAGATION DBA_QUEUE_SCHEDULES DBA_QUEUE_SUBSCRIBERS DBA_QUEUE_TABLES DBA_QUEUES
• Streams views DBA_REGISTERED_ARCHIVED_LOG DBA_RECOVERABLE_SCRIPT DBA_RECOVERABLE_SCRIPT_BLOCKS DBA_RECOVERABLE_SCRIPT_ERRORS DBA_RECOVERABLE_SCRIPT_PARAMS DBA_STREAMS_ADD_COLUMN DBA_STREAMS_ADMINISTRATOR DBA_STREAMS_DELETE_COLUMN DBA_STREAMS_GLOBAL_RULES DBA_STREAMS_MESSAGE_CONSUMERS DBA_STREAMS_MESSAGE_RULES DBA_STREAMS_NEWLY_SUPPORTED DBA_STREAMS_RENAME_COLUMN DBA_STREAMS_RENAME_SCHEMA DBA_STREAMS_RENAME_TABLE DBA_STREAMS_RULES DBA_STREAMS_SCHEMA_RULES DBA_STREAMS_TABLE_RULES DBA_STREAMS_TRANSFORM_FUNCTION DBA_STREAMS_TRANSFORMATIONS DBA_STREAMS_UNSUPPORTED DBA_RULE_SET_RULES DBA_RULE_SETS DBA_RULES DBA_HIST_BUFFERED_QUEUES DBA_HIST_BUFFERED_SUBSCRIBERS DBA_HIST_RULE_SET DBA_HIST_STREAMS_APPLY_SUM DBA_HIST_STREAMS_CAPTURE DBA_HIST_STREAMS_POOL_ADVICE
  
• Apply process parameter _HASH_TABLE_SIZE: set this relatively high (such as 10000000), to minimize “wait dependency” bottlenecks.
• Apply process commit frequency
• Propagate
  • Set the propagate parameter LATENCY to 0
  • Set the propagate parameter QUEUE_TO_QUEUE to TRUE in case of RAC(10.2 only).
• Capture Process
  
  streams$_capture_process: lists all defined capture processes
  dba_capture: basic status, error info
  v$streams_capture: detailed status info
  dba_capture_parameters: configuration information
  
• Propagate Process
  
  streams$_propagation_process: Lists all defined propagate processes
  dba_propagation: Basic status, error info
  v$propagation_sender: Detailed status
  v$propagation_receiver: Detailed status
  
• Apply Process
  
  streams$_apply_process: Lists all defined apply processes
  dba_apply: Basic status, error info
  v$streams_apply_reader: Status of the apply reader
  v$streams_apply_server: Status of apply server(s)
  v$streams_apply_coordinator: Overall status, latency info
  dba_apply_parameters: Configuration information
  
• “Miscellaneous” Tables and Views
  
  v$buffered_queues: View that displays the current and cumulative number of messages enqueued and spilled, for each buffered queue.
  sys.streams$_apply_spill_msgs_part: Table that the apply process uses, to “spill” messages from large transactions to disk.
  system.logmnr_restart_ckpt$: Table that holds capture process “checkpoint” information.
  
  

In the Next post i will discuss Downstream Oracle Streams Configuration.