Difference between GOLDEN GATE and STREAMS or GOLDEN GATE v/s STREAMS

SL NO	ORACLE  GOLDEN GATE	ORACLE STREAMS
1	Out of box flexible solution	Requires  API based stored DB Packages
2	High performance in high transaction environment	Problem scaling high throughput  environment
3	Broad heterogeneous support	Works with only oracle database
4	Setup is very simple, need to download and untar/unzip the dump	Need to execute the list of packages to setup streams

Oracle Golden Gate Tutorial Part 2 – STEP BY STEP INSTALLATION OF ORACLE GOLDEN GATE ONE WAY REPLICATION

STEP BY STEP INSTALLATION OF ORACLE GOLDEN GATE ONE WAY REPLICATION FOR NON RAC DATABASE( ACTIVE – PASSIVE)
STEP 0   ————— Hardware and O/S configuration STEP 1   ————— Database Pre requisites STEP 2   ————— GG Software Installation STEP 3   ————— Preparing source database for replication STEP 3a ————— GG DDL Support Replication STEP 4   ————— Create Manager process @ Source STEP 5   ————— Create Extract process @ Source STEP 6   ————— Create Checkpoint table @ Target STEP 7   ————— Create Replicate process @ Target STEP 8   ————— Initial Data load (EXP/IMP) STEP 9   ————— Start Extract STEP 10 ————— Start Replicat STEP 11————— TEST CASES
STEP	TASK
STEP 0:-   Hardware and O/S configuration	Now, let’s take a look at the Oracle database details which we will be using for our GOLDEN GATE configuration.( I am using VMWARE nodes) SOURCE Database Oracle Release: Oracle10 Release 2 – (10.2.0.5.0) Machine Name: Source.gg.com Operating System: Red Hat Linux 5 Oracle SID: DBSOURCE Replication Schema SCOTT TARGET Database Oracle Release: Oracle10 Release 2 – (10.2.0.5.0) Machine Name: Target.gg.com Operating System: Red Hat Linux 5 Oracle SID: DBTARGET Replication Schema SCOTT Download the oracle Golden Gate software from below URL  and copy to both source and target servers we need to download the required software from the Oracle E-Delivery web site
Step 1:–   Database Pre requisites	Need to be carried out at both source and target  SQL> create tablespace ggs_data     datafile ‘/oradata/<DBNAME>ggs_data01.dbf’ size 200m; SQL> create user ggs_owner identified by ggs_owner     default tablespace ggs_data     temporary tablespace temp;        grant connect, resource to ggs_owner;       grant select any dictionary, select any table to ggs_owner;       grant create table to ggs_owner;       grant flashback any table to ggs_owner;       grant execute on dbms_flashback to ggs_owner;       grant execute on utl_file to ggs_owner;       grant create any table to ggs_owner;       grant insert any table to ggs_owner;       grant update any table to ggs_owner;       grant delete any table to ggs_owner;       grant drop any table to ggs_owner;     INIT_ORA change UNDO_MANAGEMENT=AUTO UNDO_RETENTION=86400
Step 2:–   GG Software Installation	Need to be carried out at both source and targetNote: 1 binary for SOURCE         1 binary for TARGET REPLICAT SOURCE n  mkdir  /oradata/gg/ TARGET n  mkdir /oradata/gg/  set environment variables on both SOURCE and TARGET before starting the installation on all the three locations  $mkdir /oradata/gg $unzip V18157-01.zip Archive:  V18157-01.zip inflating: ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar $ tar -xf ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oradata/gg           $  . /ggsci     — YOU WILL GET GG COMMAND PROMPT                    GGSCI (hostname) 1> CREATE SUBDIRS     — Issue the following command to exit GGSCI.                       GGSCI (hostname) 1> EXIT
STEP 3:-   Preparing source database for replication	Need to be carried out at  source Switch the database to archivelog mode: shutdown immediate startup mount  alter database archivelog;  alter database open;  Enable minimal supplemental logging:  SQL> alter database add supplemental log data;  Prepare the database to support ddl replication.  SQL> alter system set recyclebin=off scope=spfile;  n  bounce the database
Step3a :-   Run scripts for creating all necessary objects for support DDL replication	Need to be carried out at  source  Note – run the scripts as SYSDBA  SQL> @/oradata/gg/marker_setup.sql   Marker setup script  You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation.  Enter GoldenGate schema name: GGS_OWNER  Marker setup table script complete, running verification script… Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGS_OWNER  MARKER TABLE ——————————- OK MARKER SEQUENCE ——————————- OK Script complete.  SQL> @/oradata/gg/ddl_setup.sql   GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication… You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: On Oracle 10g and up, system recycle bin must be disabled. NOTE: Stop all DDL replication before starting this installation.  Enter GoldenGate schema name: GGS_OWNER  You will be prompted for the mode of installation. To install or reinstall DDL replication, enter INITIALSETUP To upgrade DDL replication, enter NORMAL Enter mode of installation:INITIALSETUP  Working, please wait … Spooling to file ddl_setup_spool.txt  Using GGS_OWNER as a GoldenGate schema name, INITIALSETUP as a mode of installation.  Working, please wait …  RECYCLEBIN must be empty. This installation will purge RECYCLEBIN for all users. To proceed, enter yes. To stop installation, enter no.  Enter yes or no:yes  DDL replication setup script complete, running verification script… Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGS_OWNER  DDLORA_GETTABLESPACESIZE STATUS:  Line/pos   Error ———- —————————————————————– No errors  No errors CLEAR_TRACE STATUS: Line/pos   Error ———- —————————————————————– No errors  No errors CREATE_TRACE STATUS: Line/pos   Error ———- —————————————————————– No errors  No errors TRACE_PUT_LINE STATUS: Line/pos   Error ———- —————————————————————– No errors  No errors INITIAL_SETUP STATUS: Line/pos   Error ———- —————————————————————– No errors  No errors DDLVERSIONSPECIFIC PACKAGE STATUS: Line/pos   Error ———- —————————————————————– No errors  No errors DDLREPLICATION PACKAGE STATUS:  Line/pos   Error ———- —————————————————————– No errors  No errors  DDLREPLICATION PACKAGE BODY STATUS:  Line/pos   Error ———- —————————————————————– No errors  No errors  DDL HISTORY TABLE ———————————– OK  DDL HISTORY TABLE(1) ———————————– OK  DDL DUMP TABLES ———————————– OK  DDL DUMP COLUMNS ———————————– OK  DDL DUMP LOG GROUPS ———————————– OK  DDL DUMP PARTITIONS ———————————– OK  DDL DUMP PRIMARY KEYS ———————————– OK  DDL SEQUENCE ———————————– OK  GGS_TEMP_COLS ———————————– OK  GGS_TEMP_UK ———————————– OK  DDL TRIGGER CODE STATUS:  Line/pos   Error ———- —————————————————————– No errors  No errors  DDL TRIGGER INSTALL STATUS ———————————– OK  DDL TRIGGER RUNNING STATUS ———————————– ENABLED  STAYMETADATA IN TRIGGER ———————————– OFF  DDL TRIGGER SQL TRACING ———————————– 0  DDL TRIGGER TRACE LEVEL ———————————– 0  LOCATION OF DDL TRACE FILE ——————————————————————————– /u01/app/oracle/diag/rdbms/gavin/gavin/trace/ggs_ddl_trace.log  Analyzing installation status…  STATUS OF DDL REPLICATION ——————————————————————————– SUCCESSFUL installation of DDL Replication software components  Script complete. SQL>  SQL> @/oradata/gg/role_setup.sql   GGS Role setup script  This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)  You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation.  Enter GoldenGate schema name:GGS_OWNER Wrote file role_setup_set.txt  PL/SQL procedure successfully completed.  Role setup script complete  Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:  GRANT GGS_GGSUSER_ROLE TO  where  is the user assigned to the GoldenGate processes.  SQL> grant ggs_ggsuser_role to ggs_owner;  Grant succeeded.  SQL> @/oradata/gg/ddl_enable  Trigger altered.  SQL> @/oradata/gg/ddl_pin GGS_OWNER  PL/SQL procedure successfully completed.  PL/SQL procedure successfully completed.  PL/SQL procedure successfully completed.  Enable additional logging at the table level  Note- We had earlier enabled additional supplemental logging at the database level. Using the ADD TRANDATA command we now enable it at even the table level as this is required by Golden Gate for DDL support. GGSCI (soruce hostname) 5> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner  SQL> select ‘add trandata ‘||owner||’.'||object_name||’;’ from dba_objects where owner=’RPD_PRC’ and object_type=’TABLE’; GGSCI(source hostname) 6> add trandata <owner>.<tablename>
STEP 4:- Create and start manager on the source and the destination.	NEED TO BE CARRIED OUT AT SOURCE [oracle@DBSOURCE gg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.1.1.0.0 Build 078 Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11  Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.  GGSCI (DBSOURCE) 1> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner Successfully logged into database.  GGSCI (DBSOURCE) 44> info all  Program     Status      Group       Lag           Time Since Chkpt  MANAGER     STOPPED  GGSCI (DBSOURCE) 45> edit params mgr  PORT 7809 USERID ggs_owner, PASSWORD ggs_owner PURGEOLDEXTRACTS /oradata/gg/dirdat/ex, USECHECKPOINTS  GGSCI (DBSOURCE) 46> info all  Program     Status      Group       Lag           Time Since Chkpt  MANAGER     STOPPED  GGSCI (DBSOURCE) 47> dblogin USERID ggs_owner, PASSWORD ggs_owner Successfully logged into database.  GGSCI (DBSOURCE) 48> start manager  Manager started.
STEP 5 :-         Create the extract group	NEED TO BE CARRIED OUT AT SOURCE GGSCI (DBSOURCE) 49> add extract ext1, tranlog, begin nowEXTRACT added. GGSCI (DBSOURCE) 50> add exttrail /oradata/gg/dirdat/lt, extract ext1 EXTTRAIL added. GGSCI (DBSOURCE) 51> edit params ext1 Add the following lines to the new parameter file for our extract: –extract group–  extract ext1  –connection to database–  userid ggs_owner, password ggs_owner  –hostname and port for trail–  rmthost 172.168.10.108, mgrport 7810 –path and name for trail–  rmttrail /oradata/gg/dirdat/lt  –DDL support  DDL INCLUDE ALL ddl include mapped objname SCOTT.*;  –DML  table SCOTT.*; table SCOTT.EMP; table SCOTT.DEPT; table SCOTT.SALARY; :wq
STEP 6:-   CREATE CHECKPOINT TABLE	NEED TO BE CARRIED OUT AT TARGET  [oracle@DBTARGET gg]$ ./ggsci add checkpoint table to the destination database  GGSCI (DBTARGET) 1> edit params ./GLOBAL  and put following lines to the global parameter file:  GGSCHEMA ggs_owner  CHECKPOINTTABLE ggs_owner. Checkpoint  GGSCI (DBTARGET) 2> dblogin userid ggs_owner password password  Successfully logged into database.  GGSCI (DBTARGET) 3> add checkpointtable ggs_owner.checkpoint  Successfully created checkpoint table ggs_owner.CHECKPOINT.
STEP 7:-   CREATE REPLICAT	NEED TO BE CARRIED OUT AT TARGET GGSCI (DBTARGET) 4>add replicat rep1, exttrail /oradata/gg/dirdat/lt,checkpointtable ggs_owner.checkpointGGSCI (DBTARGET) 4> EDIT PARAMS rep1 edit params rep1 –target database login – userid ggs_owner, password ggs_owner –file for dicarded transaction – –discardfile /oradata/gg/discard/rep1_discard.txt, append, megabytes 10 –ddl support DDL INCLUDE ALL DDLERROR DEFAULT IGNORE RETRYOP –Specify table mapping — MAP SCOTT.*,       TARGET SCOTT.*; MAP SCOTT.EMP,   TARGET SCOTT.EMP; MAP SCOTT.DEPT,   TARGET SCOTT.DEPT; MAP SCOTT.SALARY,   TARGET SCOTT.SALARY;
STEP 8:-   INITIAL DATALOAD	EXPORT: @ SOURCE $ expdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_gg.log schemas=scott Scp from SOURCE and TARGET $scp –p schema_gg.dmp 172.168.10.108:/oradata  IMPORT: @ TARGET $impdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_imp_gg.log schemas=SCOTT
STEP 9:-   START EXTRACT	NEED TO BE CARRIED OUT AT  SOURCE GGSCI (DBSOURCE) 45>  start EXT1  Sending START request to MANAGER … EXTRACT EXT2 starting
STEP 10:-   START REPLICAT	NEED TO BE CARRIED OUT AT TARGET   GGSCI (DBTARGET) 10> start replicat rep1  Sending START request to MANAGER … REPLICAT REP1 starting
STEP 11:-   TEST CASES	CASE 1: CREATE TABLE IN THE SOURCE DATABASE  SOURCE:create table SCOTT.Employee( ID                 VARCHAR2(4 BYTE) , First_Name         VARCHAR2(10 BYTE), Last_Name          VARCHAR2(10 BYTE), Start_Date         DATE, End_Date           DATE, Salary             Number(8,2), City               VARCHAR2(10 BYTE), Description        VARCHAR2(15 BYTE) ); SELECT * FROM SCOTT.EMPLOYEE; NO ROWS SELECTED TARGET: SELECT * FROM SCOTT.EMPLOYEE; NO ROWS SELECTED  Case 2:CREATE TABLE AND INSERT DATA INTO IT SOURCE: Create table test_gg_1( Id             number2(10), Username varchar2(20) ); Insert into test_gg_1 values( 1, ‘USER1’); Insert into test_gg_1 values( 2,’USER2’); Insert into test_gg_1 values( 3,’USER3’); Insert into test_gg_1 values( 4,’USER4’); Insert into test_gg_1 values( 5,’USER5’); COMMIT; SELECT * FROM SCOTT.TEST_GG_1; TARGET : SELECT * FROM SCOTT.TEST_GG_1;  CASE3: UPDATE THE RECORD SOURCE: Update scott.test_gg_1 set username=’USERDBA’ where id=1; Commit; TARGET:  Select * from scott.test_gg_1 where id=1 n  you will find the updated record in the target database  CASE4: DELETE THE RECORD SOURCE : Delete from scott.test_gg_1 where id=4; Commit; TARGET: Select * from scott.test_gg_1 where id=4; n  you will not find the deleted record in the target database CASE 5: TRUNCAT THE RECORDS SOURCE: Truncate table scott.test_gg_1; Commit; Select * from scott.test_gg_1; TARGET : Select * from scott.test_gg_1; n  since the records are truncated in the source you will not find any records in the target. CASE 6: CREATE A TRIGGER SOURCE: create or replace trigger SCOTT.emp_biu BEFORE INSERT OR UPDATE of salary on employee for each row declare v_error VARCHAR2(2000); begin if :new.salary > 10000 then v_error:=:old.first_name||’ cannot have that much!’; raise_application_error(-20999,v_error); end if; end; / SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’EMP_BIU’; TARGET: SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’EMP_BIU’;  CASE 7: create table with primary Key If you create any table with constrains, Golden gate will not replicated it automatically. We need to perform below steps: stop the extract stop the replicat take the export of the table which is having constrains. Copy it to target server. Import the exported dump. Map the table in the replicat parameter file Enable HANDLECOLLUSION parameter in the replicat parameter. Start the replicat Start the extract.

ORACLE GOLDEN GATE CONFIGURATIONS

DIFFERENT TOPOLOGY

Or

 DIFFERENT WAY OF ORACLE GOLDEN GATE CONFIGURATION

RMAN-06059: expected archived log not found

RMAN-06059: expected archived log not found while taking archive log backup

 RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 06/16/2011 06:02:47

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

ORA-19625: error identifying file /arch/db/dwprod/arch_ORCL_533835433_1_145891.arc

ORA-27037: unable to obtain file status

HPUX-ia64 Error: 2: No such file or directory

Additional information: 3

The possible reason for the above error is that recovery catalog may be out of synch or that particular archivelog got deleted.

How to overcome this?

CASE1: If the recovery catalog is out of synch:

ORCL: oracle] $rman target sys/***** catalog rman/******@RCCATALOG

Recovery Manager: Release 11.2.0.2.0 – Production on Thu Jun 16 13:19:23 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to target database: ORCL (DBID=3983796265)

Connected to recovery catalog database

RMAN>SQL “ALTER SYSTEM ARCHIVE LOG CURRENT”;

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT

RMAN>RESYNC CATALOG;

starting full resync of recovery catalog

full resync complete

CASE2: IF THE ARCHIVE LOG IS DELETED:

ORCL:oracle]$rman target sys/***** catalog rman/******@RCCATALOG

RMAN>crosscheck copy of archivelog all;

Once the crosscheck command is executed successfully, we have to take the full backup of the database.

Thanks and Regards,

Satish.G.S

 

 

ORA-01403 NO DATA FOUND in ORACLE STREAMS

ORA-01403 NO DATA FOUND in ORACLE STREAMS

ORA-01403 error occurs when an apply process tries to update an existing row in the target database and the OLD_VALUES in the row LCR do not match the current values at the destination database.
Typically, one of the following conditions causes this error:
1. Supplemental logging is not specified for the columns that require supplemental logging at the source database side. This is because, LCRS from source database may not contain values for key columns.
2. There may be a problem with the primary key in the destination table. If there is no primary key mentioned for the target table or if the primary key in the target database table is different from the source database table.
3. If there is any data mismatch between source table and the target table.

TROUBLESHOOTING ORA – 01403:
STEP 1:  where you will find ORA – 01403 errors in streams?
If you query DBA_APPLY_ERROR in the Target Database or  in the database where Apply is running , you will get the list of objects or records which are out of synch or getting ORA_01403 error in the error_message column:
Run the below mentioned query:
SQL >  SELECT APPLY_NAME,
SOURCE_DATABASE,
LOCAL_TRANSACTION_ID,
ERROR_NUMBER,
ERROR_MESSAGE,
MESSAGE_COUNT
FROM DBA_APPLY_ERROR;

We have to check each and every error_message and see what is the error related to it and which command it cause this problem.
To check what the root cause of this error is, we have to use PRINT_LCR package provides by oracle: 

STEP 2 : Run below mentioned code as STRMADMIN user (streams admin user) only once
CREATE OR REPLACE PROCEDURE print_any(data IN ANYDATA) IS
tn VARCHAR2(61);
str VARCHAR2(4000);
chr VARCHAR2(1000);
num NUMBER;
dat DATE;
rw RAW(4000);
res NUMBER;
BEGIN
IF data IS NULL THEN
DBMS_OUTPUT.PUT_LINE(‘NULL value’);
RETURN;
END IF;
tn := data.GETTYPENAME();
IF tn = ‘SYS.VARCHAR2′ THEN
res := data.GETVARCHAR2(str);
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,0,253));
ELSIF tn = ‘SYS.CHAR’ then
res := data.GETCHAR(chr);
DBMS_OUTPUT.PUT_LINE(SUBSTR(chr,0,253));
ELSIF tn = ‘SYS.VARCHAR’ THEN
res := data.GETVARCHAR(chr);
DBMS_OUTPUT.PUT_LINE(chr);
ELSIF tn = ‘SYS.NUMBER’ THEN
res := data.GETNUMBER(num);
DBMS_OUTPUT.PUT_LINE(num);
ELSIF tn = ‘SYS.DATE’ THEN
res := data.GETDATE(dat);
DBMS_OUTPUT.PUT_LINE(dat);
ELSIF tn = ‘SYS.BLOB’ THEN
DBMS_OUTPUT.PUT_LINE(‘BLOB Found’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘typename is ‘ || tn);
END IF;
END print_any;
/
CREATE OR REPLACE PROCEDURE print_lcr(lcr IN SYS.ANYDATA) IS
typenm VARCHAR2(61);
ddllcr SYS.LCR$_DDL_RECORD;
proclcr SYS.LCR$_PROCEDURE_RECORD;
rowlcr SYS.LCR$_ROW_RECORD;
res NUMBER;
newlist SYS.LCR$_ROW_LIST;
oldlist SYS.LCR$_ROW_LIST;
ddl_text CLOB;
BEGIN
typenm := lcr.GETTYPENAME();
DBMS_OUTPUT.PUT_LINE(‘type name: ‘ || typenm);
IF (typenm = ‘SYS.LCR$_DDL_RECORD’) THEN
res := lcr.GETOBJECT(ddllcr);
DBMS_OUTPUT.PUT_LINE(‘source database: ‘ ||
ddllcr.GET_SOURCE_DATABASE_NAME);
DBMS_OUTPUT.PUT_LINE(‘owner: ‘ || ddllcr.GET_OBJECT_OWNER);
DBMS_OUTPUT.PUT_LINE(‘object: ‘ || ddllcr.GET_OBJECT_NAME);
DBMS_OUTPUT.PUT_LINE(‘is tag null: ‘ || ddllcr.IS_NULL_TAG);
DBMS_LOB.CREATETEMPORARY(ddl_text, TRUE);
ddllcr.GET_DDL_TEXT(ddl_text);
DBMS_OUTPUT.PUT_LINE(‘ddl: ‘ || ddl_text);
DBMS_LOB.FREETEMPORARY(ddl_text);
ELSIF (typenm = ‘SYS.LCR$_ROW_RECORD’) THEN
res := lcr.GETOBJECT(rowlcr);
DBMS_OUTPUT.PUT_LINE(‘source database: ‘ ||
rowlcr.GET_SOURCE_DATABASE_NAME);
DBMS_OUTPUT.PUT_LINE(‘owner: ‘ || rowlcr.GET_OBJECT_OWNER);
DBMS_OUTPUT.PUT_LINE(‘object: ‘ || rowlcr.GET_OBJECT_NAME);
DBMS_OUTPUT.PUT_LINE(‘is tag null: ‘ || rowlcr.IS_NULL_TAG);
DBMS_OUTPUT.PUT_LINE(‘command_type: ‘ || rowlcr.GET_COMMAND_TYPE);
oldlist := rowlcr.GET_VALUES(‘OLD’);
FOR i IN 1..oldlist.COUNT LOOP
if oldlist(i) is not null then
DBMS_OUTPUT.PUT_LINE(‘old(‘ || i || ‘): ‘ || oldlist(i).column_name);
print_any(oldlist(i).data);
END IF;
END LOOP;
newlist := rowlcr.GET_VALUES(‘NEW’);
FOR i in 1..newlist.count LOOP
IF newlist(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(‘new(‘ || i || ‘): ‘ || newlist(i).column_name);
print_any(newlist(i).data);
END IF;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE(‘Non-LCR Message with type ‘ || typenm);
END IF;
END print_lcr;
/

CREATE OR REPLACE PROCEDURE print_errors IS
CURSOR c IS
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
FROM DBA_APPLY_ERROR
ORDER BY SOURCE_DATABASE, SOURCE_COMMIT_SCN;
i NUMBER;
txnid VARCHAR2(30);
source VARCHAR2(128);
msgcnt NUMBER;
errnum NUMBER := 0;
errno NUMBER;
errmsg VARCHAR2(128);
lcr SYS.AnyData;
r NUMBER;
BEGIN
FOR r IN c LOOP
errnum := errnum + 1;
msgcnt := r.MESSAGE_COUNT;
txnid := r.LOCAL_TRANSACTION_ID;
source := r.SOURCE_DATABASE;
errmsg := r.ERROR_MESSAGE;
errno := r.ERROR_NUMBER;
DBMS_OUTPUT.PUT_LINE(‘*************************************************’);
DBMS_OUTPUT.PUT_LINE(‘—– ERROR #’ || errnum);
DBMS_OUTPUT.PUT_LINE(‘—– Local Transaction ID: ‘ || txnid);
DBMS_OUTPUT.PUT_LINE(‘—– Source Database: ‘ || source);
DBMS_OUTPUT.PUT_LINE(‘—-Error Number: ‘||errno);
DBMS_OUTPUT.PUT_LINE(‘—-Message Text: ‘||errmsg);
FOR i IN 1..msgcnt LOOP
DBMS_OUTPUT.PUT_LINE(‘–message: ‘ || i);
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid);
print_lcr(lcr);
END LOOP;
END LOOP;
END print_errors;
/
CREATE OR REPLACE PROCEDURE print_transaction(ltxnid IN VARCHAR2) IS
i NUMBER;
txnid VARCHAR2(30);
source VARCHAR2(128);
msgcnt NUMBER;
errno NUMBER;
errmsg VARCHAR2(128);
lcr SYS.ANYDATA;
BEGIN
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
INTO txnid, source, msgcnt, errno, errmsg
FROM DBA_APPLY_ERROR
WHERE LOCAL_TRANSACTION_ID = ltxnid;
DBMS_OUTPUT.PUT_LINE(‘—– Local Transaction ID: ‘ || txnid);
DBMS_OUTPUT.PUT_LINE(‘—– Source Database: ‘ || source);
DBMS_OUTPUT.PUT_LINE(‘—-Error Number: ‘||errno);
DBMS_OUTPUT.PUT_LINE(‘—-Message Text: ‘||errmsg);
FOR i IN 1..msgcnt LOOP
DBMS_OUTPUT.PUT_LINE(‘–message: ‘ || i);
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); — gets the LCR
print_lcr(lcr);
END LOOP;
END print_transaction;
/

STEP 3: Use the below mentioned code to check what is present inside the local transaction ID:

DECLARE
lcr SYS.AnyData;
BEGIN
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE
(1,’<LOCAL_TRANSACTION_ID>’);
print_lcr(lcr);
END;
/

If there are more than one table involved in out of synch problem. Then execute below mentioned code to get the list of tables which are involved in OUT OF SYNCH issue. Run the below code as STRMADMIN user:
STEP 4 : Run the below query only one time as STRMADMIN user:

CREATE OR REPLACE PROCEDURE print_objectname(lcr IN SYS.ANYDATA) IS
typenm VARCHAR2(61);
ddllcr SYS.LCR$_DDL_RECORD;
proclcr SYS.LCR$_PROCEDURE_RECORD;
rowlcr SYS.LCR$_ROW_RECORD;
res NUMBER;
newlist SYS.LCR$_ROW_LIST;
oldlist SYS.LCR$_ROW_LIST;
ddl_text CLOB;
BEGIN

typenm := lcr.GETTYPENAME();
IF (typenm = ‘SYS.LCR$_DDL_RECORD’) THEN
res := lcr.GETOBJECT(ddllcr);
DBMS_OUTPUT.PUT_LINE(‘object: ‘ || ddllcr.GET_OBJECT_NAME);

ELSIF (typenm = ‘SYS.LCR$_ROW_RECORD’) THEN
res := lcr.GETOBJECT(rowlcr);
DBMS_OUTPUT.PUT_LINE(‘object: ‘ || rowlcr.GET_OBJECT_NAME);
–DBMS_OUTPUT.PUT_LINE(‘command_type: ‘ || rowlcr.GET_COMMAND_TYPE);
END IF;
END print_objectname;
/
Run the query query when ever there is out of synch issue. Spool the output of this query and select the distinct tables inside the spool file , it will give you the list of tables which are out of synch.
set serveroutput on;

DECLARE
lcr SYS.AnyData;
type strarr is table of varchar2(100);
localtrnid strarr ;
msgcount strarr ;
BEGIN
 select local_transaction_id,message_count bulk collect into localtrnid ,msgcount  from
DBA_APPLY_ERROR where apply_name=’APPLY_NAME’;
for i in 1 .. localtrnid .count loop
 for j in 1.. to_number(msgcount(i)) loop
  lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(j,localtrnid(i) );
  print_objectname(lcr);
 end loop;
 
end loop;

–print_lcr(lcr);
END;
/

Thanks and Regards,

Satish.G.S

STEPS FOR ADDING NEW OBJECTS TO THE EXISTING ORACLE TABLE LEVEL STREAMS CONFIGURATION

Here i am adding a table by name NEW_STRM_TBL to already up and running TABLE level streams.

SOURCE :

SERVER IP     : 192.168.0.1
DATABASE NAME : SOURCEDB
SCHEMA NAME   : SCOTT
TABLE NAME    : NEW_STRM_TBL
TARGET :

SERVER IP     : 192.168.1.1
DATABASE NAME : TARGETDB
SCHEMA NAME   : SCOTT
TABLE_NAME    : NEW_STRM_TBL

STEP 1: STOP STREAMS

Stop APPLY, PROPAGATION and CAPTURE process

STEP 2: DEFINE THE RULE FOR APPLY
CONN STRMADMIN/STRMADMIN@TARGETDB

show user
–NEW_STRM_TBL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.NEW_STRM_TBL’,
streams_type => ‘APPLY’,
streams_name => ‘STREAM_APPLY_A1′,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘SOURCEDB’);
END;
/
STEP 3: DEFINE THE RULE FOR PROPAGATION

CONN STRMADMIN/STRMADMIN@SOURCEDB

–NEW_STRM_TBL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => ‘SCOTT.NEW_STRM_TBL’,
streams_name => ‘STREAM_PROPAGATE_P1′,
source_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
destination_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q@TARGETDB’,
include_dml => true,
include_ddl => true,
source_database => ‘SOURCEDB’);
END;
/
STEP 4: DEFINE THE RULE FOR CAPTURE

CONN strmadmin/STRMADMIN@SOURCEDB

show user

–NEW_STRM_TBL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.NEW_STRM_TBL’,
streams_type => ‘CAPTURE’,
streams_name => ‘STREAM_CAPTURE_C1′,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘SOURCEDB’);
END;
/
STEP5: OBJECT INSTANTATION

Take the export of table from the source database:

exp SCOTT/TIGER file=exp_tbl.dmp log=exp_tbl.log object_consistancy=y tables=SCOTT.new_strm_tbl

Transfer the dump file to target database:
Import the dump into the target database:

imp SCOTT/TIGER fromuser=SCOTT touser=SCOTT file=’<PATH>/exp_tbl.dmp’ log=’<PATH>/exp_tbl.log’ STREAMS_INSTANTIATION=Y IGNORE=Y COMMIT=Y

STEP 6: START STREAMS

Start APPLY, PROPAGATION and APPLY

Thanks and Regards,

Satish.G.S

SUPPLEMENTAL LOGGING

What is supplemental logging?

Redo log files are generally used for instance recovery and media recovery. The data required for instance recovery and media recovery is automatically recorded in the redo log files. However a redo log based application may require that the additional columns need to be logged into redo log files. The process of adding these additional columns into redo log files is called supplemental logging.

Supplemental logging is not the default behavior of oracle database. It has to be enabled manually after the database is created. You can enable the supplemental logging at two levels

1. DATABASE LEVEL
2. TABLE LEVEL

What is the user of supplemental logging in replication?

Supplemental logging at the source database side to certain columns are very much required to ensure that those changes which are happened to the columns which are supplemental logging enabled will be applied successfully at the target database. With the help of these additional columns, oracle decides the rows which need to be updated on the destination side. This is how supplement logging is more critical requirement for replication.

What is the role or use of supplemental logging in oracle streams?

In streams, capture process captures the additional information logged in to redo log file using supplemental logging and place them in the LCR (LOGGICAL CHANGE RECORD). Supplemental logging is configured at the source database side. The apply process at the target database side reads these LCR’s to properly apply DML and DDL changes that are replicated from source database side to target database.

If the table has primary key or unique key column defined, only the column which are involved in primary key or unique key column will be registered in the redo logs along with the actual column that has changed. If the table does not have any primary key or unique key defined, oracle will write all columns of the changed row data into the redo log file.

Depending on the set of additional columns logged there are two types of supplemental log groups:

1. Unconditional supplemental log group
2. Conditional supplemental log group

 1. UNCONDITIONAL SUPPLEMENTAL LOG GROUP:

 If you want the before image of the column to be logged in to the redo log file  even if there is no changes happen that column and  which have supplemental logging enabled, then we use UNCONDITIONAL SUPPLEMENTAL LOGGING. This is also call ALWAYS LOG GROUP.

 2. CONDITIONAL SUPPLEMENTAL LOG GROUP:

 The before image of all the columns are logged into the redo log file even if at least one of the columns in the supplemental log group is updated.

 DATABASE LEVEL SUPPLEMENTAL LOGGING:

 How to check supplemental logging is enabled or not?

 SQL> SELECT supplemental_log_data_min FROM v$database;

 How to enable supplemental logging at database level?

 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 How to disable supplemental logging at database level?

 SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

 TABLE LEVEL SUPPLEMENTAL LOGGING:

 TABLE LEVEL UNCONDITIONAL SUPPLEMENTAL LOGGING: 

• Primary Key columns
• All columns
• Selected columns

 To specify an unconditional supplemental log group for PRIMARY KEY column(s):

 SQL > ALTER TABLE SCOTT. EMP ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

 To specify an unconditional supplemental log group that includes ALL TABLE columns:

 SQL > ALTER TABLE SCOTT.EMP ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

 To specify an unconditional supplemental log group that includes SELECTED columns:

 SQL> ALTER TABLE SCOTT.EMP ADD SUPPLEMENTAL LOG GROUP t1_g1 (C1,C2) ALWAYS;

 TABLE LEVEL CONDITIONAL SUPPLEMENTAL LOGGING: 

• Foreign  key
• Unique
• Any Columns

To specify a conditional supplemental log group that includes all FOREIGN KEY columns:

 SQL> ALTER TABLE SCOTT.DEPT ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

 To specify a conditional supplemental log group for UNIQUE column(s) and/or BITMAP index column(s):

 SQL > ALTER TABLE SCOTT.EMP ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

 To specify a conditional supplemental log group that includes ANY columns:

 SQL>ALTER TABLE SCOTT.EMP  ADD SUPPLEMENTAL LOG GROUP t1_g1 (c1,c3);

 To drop supplemental logging:

 SQL > ALTER TABLE <TABLE NAME >DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

 SQL>ALTER TABLE <TABLE NAME >DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

 SQL> ALTER TABLE <TABLE NAME> DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

 SQL> ALTER TABLE <TABLE NAME> DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

 VIEWS

 DBA_LOG_GROUPS

 DBA_LOG_GROUP_COLUMNS

Thanks  and  Regards,

Satish.G.S

 

 

Oracle Golden Gate Tutorial part 1

ORACLE GOLDEN GATE CONCEPT AND ARCHITECTURE

 

OracleGolden Gateis a tool provided by oracle for transactional data replication among oracle databases and other RDBMS tools (SQL SERVER, DB2.Etc). Its modular architecture gives you the flexibility to easily decouple or combined to provide best solution to meet the business requirements.

 Because of this flexibility in the architecture,Golden Gatesupports numerous business requirements:

• High Availability
• Data Integration
• Zero downtime upgrade and migration
• Live reporting database

etc

 Oracle Golden Gate Architecture

 Oracle Golden Gate Architecture is composed of the following Components:

 ● Extract

 ● Data pump

 ● Replicat

 ● Trails or extract

 ● Checkpoints

 ● Manager

 ● Collector

 Below is the architecture diagram of GG:

Figure: gg.01

 Oracle Golden Gate server runs on both source and target server. OracleGolden Gateis installed as an external component to the database and it wont uses database resource, in turn it won’t effect database performance. Where as Oracle streams which uses built in packages which are provided by oracle, which uses most of the database resources and there are chances of performance slow down in both source and target databases.

 Let first have a look at architectural components of Oracle Golden Gate:

 EXTRACT:

Extract runs on the source system and it is the extraction mechanism for oracleGolden Gate( capture the changes which happens at the source database).

 The Extract process extracts the necessary data from the database transaction logs. For oracle database transaction logs are nothing both REDO log file data. Unlike streams which runs in the oracle database itself and needs the access to the database. OracleGolden Gatedoes not needs access to the oracle database and also it will extract only the committed transaction from the online redo log file.

 When ever there is a long running transaction which generates more number of redo data will force to switch the redo log file and in turn more number of archive logs will be generated. In these cases the extract process need to read the archive log files to get the data.

 Extract process captures all the changes that are made to objects that are configured for synchronization.  Multiple Extract processes can operate on different objects at the same time. For example once process could continuously extract transactional data changes and stream them to a decision support database. while another process performs batch extracts for periodic reporting or, two extract processes could extract and transmit in parallel to two replicat processes ( with two trails) to minimize target latency when the databases are large.

 DATAPUMP

 Datapump is the secondary extract process with in source oracleGolden Gateconfiguration. You can have the source oracleGolden Gateconfigured without Datapump process also, but in this case Extract process has to send the data to trail file at the target. If the Datapump is configured the primary extract process writes the data to the source trail file and Datapump will read this trail file and propagate the data over the network to target trail file. The Datapump adds the storage flexibility and it isolates the primary extract process from TCP/IP activity.

 You can configure the primary extract process and Datapump extract to extract online or extract during batch processing.

 REPLICAT

 Replicat process runs on the target system. Replicat reads extracted transactional data changes and DDL changes (IF CONFIGURED) that are specified in the Replicat configuration, and than it replicates them to the target database.

 TRAILS OR EXTRACTS

 To support the continuous extraction and replication of source database changes, Oracle Golden Gate stores the captured changes temporarily on the disk in a series of files call a TRAIL. A trail can exist on the source or target system and even it can be in a intermediate system, depending on how the configuration is done. On the local system it is known as an EXTRACT TRAIL and on the remote system it is known as REMOTE TRAIL.

 The use of a trail also allows extraction and replication activities to occur independently of each other. Since these two ( source trail and target trail) are independent you have more choices for how data is delivered.

 CHECKPOINT

 Checkpoints stores the current read and write positions of a process to disk for recovery purposes. These checkpoints ensure that data changes that are marked for synchronization are extracted by extract and replicated by replicat.

 Checkpoint work with inter process acknowledgments to prevent messages from being lost in the network. OracleGolden Gatehas a proprietary guaranteed-message delivery technology.

 Checkpoint information is maintained in checkpoint files within the dirchk sub-directory of the Oracle Golden Gate directory. Optionally, Replicat checkpoints can be maintained in a checkpoint table within the target database, apart from standard checkpoint file.

 MANAGER

 The Manager process runs on both source and target systems and it is the heart or control process of Oracle Golden Gate. Manager must be up and running before you create EXTRAT or REPLICAT process. Manager performs Monitoring, restarting oracle golden gate process, report errors, report events, maintains trail files and logs etc.

 COLLECTOR

 Collector is a process that runs in the background on the target system. Collector receives extracted database changes that are sent across the TCP/IP network and it writes them to a trail or extract file.

Thanks and Regards,

Satish.G.S

STEPS TO REMOVE STREAMS FROM THE DATABASE

     COMPLETELY REMOVE STREAMS FROM THE DATABASE

Starting from Oracle Database 10g, Oracle provides a means by which you can remove an entire Streams environment from a database.

Stop streams on both source and target and then execute the below command as sys user

SQL> conn sys@DBSOURCE as sysdba

SQL> execute DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

SQL> conn sys@DBTARGET as sysdba

SQL> execute DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

ISSUES:

In case if you get the $BIN error when you execute the above command , then purge the recycle bin and re-execute the above command.

SQL> purge dba_recyclebin;

Thanks and Regards,

Satish.G.S

STEPS TO IMPLEMENT SCHEMA LEVEL ORACLE STREAMS

 STEPS TO IMPLEMENT SCHEMA LEVEL ORACLE STREAMS

Here i am replicating all the objects of SCOTT schema from DBSOURCE database to SCOTT schema in DBTARGET database.

 SOURCE DATABASE : DBSOURCE

TARGET DATABASE : DBTARGET

SOURCE SCHEMA NAME : SCOTT

TARGET SCHEMA NAME : SCOTT

Fallow the steps in the same sequence.

STEP 0: Check streams unsupported objects present with the schema

Qurey DBA_STREAMS_UNSUPPORTED to get the list of Tables and the reason why streams wont support those  tables  in replication.

SQL > SELECT TABLE_NAME,REASON FROM DBA_STREAMS_UNSUPPORTED WHERE OWNER=’SCOTT’;

STEP 1 : ADD SUPPLEMENT LOGIN TO ALL THE TABLES WHICH ARE PART OF STREAMS REPLICATION

@STEP1_SYS_SOURCE_SUPPLEMENTAL_LOG_DATA.SQL

 Add the supplement login for all the tables present in SCOTT schema at the source side

—    CONTENTS OF .SQL FILES

spool c:\STREAMS_LOG\step1_sys_source_supplement_log_data.log

CONN SYS@DBSOURCE AS SYSDBA

set echo on

show user

alter database force logging;

alter database add supplemental log data;
alter table SCOTT.EMP  ADD SUPPLEMENTAL LOG DATA (ALL,PRIMARY KEY,UNIQUE,FOREIGN KEY) columns;                                       
alter table SCOTT.DEPT  ADD SUPPLEMENTAL LOG DATA (ALL,PRIMARY KEY,UNIQUE,FOREIGN KEY) columns;                            
alter table SCOTT.EMPLOYEES  ADD SUPPLEMENTAL LOG DATA (ALL,PRIMARY KEY,UNIQUE,FOREIGN KEY) columns;

spool off

STEP 2 : SETTING THE ENV VARIABLES AT SOURCE – DBSOURCE

— The database must run in archive log mode

@STEP2_SYS_SOURCE_GLOBALNAME.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step2_sys_source_globalname.log

CONN SYS@DBSOURCE AS SYSDBA

SHOW USER

select * from global_name; –to see current global_name

alter system set global_names=true scope=both;

– Restart DB & do the same changes on Target DB also

spool off

STEP 3 : SETTING THE ENV VARIABLES AT TARGET – DBTARGET

— the database must run in archive log mode

@STEP3_SYS_TARGET_GLOBALNAME.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step3_sys_target_globalname.log

CONN SYS@DBTARGET AS SYSDBA

SHOW USER

select * from global_name; –to see current global_name

alter system set global_names=false scope=both;

– Restart DB & do the same changes on Source DB also

spool off

STEP 4 : CREATING STREAMS ADMINISTRATOR USER AT SOURCE – DBSOURCE

—at the SOURCE:

SQL> create tablespace strepadm datafile ‘/oradata/DBSOURCE/strepadm01.dbf’ size 1000m;

@STEP4_SYS_SOURCE_CREATE_USER.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step4_sys_source_create_user.log

CONN SYS@DBSOURCE AS SYSDBA

SHOW USER

PROMPT CREATING USERS

create user STRMADMIN identified by STRMADMIN default tablespace strepadm temporary tablespace temp;

GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;

execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(‘STRMADMIN’);

spool off

STEP 5: CREATING DB LINK AT THE SOURCE -DBSOURCE

@STEP5_STRMADMIN_SOURCE_DBLINK.SQL

— CONTENTS OF .SQL FILES

/* Connected as the Streams Administrator, create the streams queue and the database link that will be used for propagation at DBSOURCE*/
/* Add the TNS ENTRY details in the tnsnames.ora file */
set echo on

spool c:\STREAMS_LOG\STEP5_strmadmin_source_dblink.log

CONN STRMADMIN@DBSOURCE AS SYSDBA

show user

create database link DBTARGET connect to STRMADMIN identified by STRMADMIN using ‘DBTARGET’;

spool off

STEP 6 : CREATING STREAMS ADMINISTRATOR USER  AT TARGET – DBTARGET

—at the TARGET:

SQL> create tablespace strepadm datafile ‘/oradata/DBTARGET/strepadm01.dbf’ size 1000m;

@STEP6_SYS_TARGET_CREATE_USER.SQL

— CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step6_sys_TARGET_create_user.log

CONN SYS@DBTARGET AS SYSDBA

show user

PROMPT CREATING USERS

create user STRMADMIN identified by STRMADMIN default tablespace strepadm temporary tablespace temp;

GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;

execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(‘STRMADMIN’);

spool off
– IF SCOTT schema is not present in the target please create the same.

STEP 7 : CREATE QUEUE AND QUEUE TABLE AT THE SOURCE – DBSOURCE

@STEP7_STRMADMIN_SOURCE_QUEUE.SQL

— CONTENTS OF .SQL FILES

/* Connected as the Streams Administrator, create the streams queue and the database link that will be used for propagation at DBSOURCE */
set echo on
spool c:\STREAMS_LOG\step7_strmadmin_source_queue.log

connect STRMADMIN@DBSOURCE

show user

BEGIN
   DBMS_STREAMS_ADM.SET_UP_QUEUE(
     queue_table => ‘STREAMS_QUEUE_TABLE’,
     queue_name  => ‘STREAMS_QUEUE_Q’,
     queue_user  => ‘STRMADMIN’);
END;
/

spool off

 STEP 8: CREATE QUEUE AND QUEUE TABLE AT THE TARGET – DBTARGET

@STEP8_STRMADMIN_TARGET_QUEUE.SQL

— CONTENTS OF .SQL FILES

/* Connected as the Streams Administrator, create the streams queue and the database link that will be used for propagation at DBTARGET */

set echo on

spool c:\STREAMS_LOG\step8_strmadmin_target_queue.log

conn STRMADMIN@DBTARGET

show user

BEGIN
   DBMS_STREAMS_ADM.SET_UP_QUEUE (
     queue_table => ‘STREAMS_QUEUE_TABLE’,
     queue_name  => ‘STREAMS_QUEUE_Q’,
     queue_user  => ‘STRMADMIN’);
END;
/

spool off

 STEP 9: CREATE PROPAGATION PROCESS AT SOURCE – DBSOURCE

@STEP9_STRMADMIN_SOURCE_PROPOGATION.SQL

— CONTENTS OF .SQL FILES

set echo on

spool C:\STREAMS_LOG\step9_strmadmin_source_propogation.log

conn strmadmin@DBSOURCE

SHOW USER

BEGIN

   DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(

     schema_name                        => ‘SCOTT’,

     streams_name                        => ‘STREAM_PROPAGATE_P1′,

     source_queue_name              => ‘STRMADMIN.STREAMS_QUEUE_Q’,

     destination_queue_name       => ‘STRMADMIN.STREAMS_QUEUE@DBTARGET’,

     include_dml                           => true,

     include_ddl                            => true,

     source_database                   => ‘DBSOURCE’);

END;

/

spool off

STEP 10 : CREATE CAPTURE PROCESS AT SOURCE – DBSOURCE

@STEP10_STRMADMIN_SOURCE_CAPTURE.SQL

— CONTENTS OF .SQL FILES

set echo on

/*Step 10 -Connected to DBSOURCE , create CAPTURE */

spool C:\STREAMS_LOG\step10_strmadmin_source_capture.log

CONNstrmadmin@DBSOURCE

show user

BEGIN

  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

    schema_name              => ‘SCOTT’,

    streams_type               => ‘CAPTURE’,

    streams_name              => ‘STREAM_CAPTURE_C1′,

    queue_name               => ‘STRMADMIN.STREAMS_QUEUE­_Q’,

    include_dml                => true,

    include_ddl                => true,

    source_database => ‘DBSOURCE’);

END;

/

SPOOL OFF

STEP 11 : CREATE APPLY PROCESS AT TARGET – DBTARGET

@STEP11_STRMADMIN_TARGET_APPLY.SQL

 — CONTENTS OF .SQL FILES

set echo on

spool c:\STREAMS_LOG\step11_strmadmin_target_apply_start.log

CONN STRMADMIN/STRMADMIN@DBTAGET

show user

BEGIN

   DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

     schema_name     => ‘SCOTT’,

     streams_type    => ‘APPLY ‘,

     streams_name    => ‘STREAM_APPLY_A1′,

     queue_name      => ‘STRMADMIN.STREAMS_QUEUE_Q’,

     include_dml     => true,

     include_ddl     => true,

     source_database => ‘DBTARGET’);

END;

/

SPOOL OFF

STEP 12: CREATE NEGATIVE RULE AT SOURCE FOR UNSUPPORTED TABLES – DBSOURCE

 Set negative rule for all the tables which are unsupported by streams ( List you got from  querying DBA_STREAMS_UNSUPPORTED)

— CONTENTS OF .SQL FILES

set echo on

spool c:\streams_source\step12_strmadmin_source_negative_rule.log

conn strmadmin@DBSOURCE

show user

BEGIN

  DBMS_STREAMS_ADM.ADD_TABLE_RULES(

    table_name      =>  ‘SCOTT.<UNSUPPORTED TABLE NAME>’,

    streams_type    =>  ‘capture’,

    streams_name    =>  ‘STREAM_CAPTURE_C1’,

    queue_name      =>  ‘strmadmin.STREAMS_QUEUE_Q’,

    include_dml     =>  true,

    include_ddl     =>  true,

    inclusion_rule  =>  false);

END;

/

SPOOL OFF

STEP 13: STREAMS OBJECT INSTANTATION

@ STEP10_EXP_IMP — Details are present in this text.

SOURCE :

$exp USERNAME/PASSWORD parfile=exp_streams.par

vi exp_streams.par

file=exp_streams.dmp
log=exp_streams.log
object_consistent=y
OWNER=SCOTT
STATISTICS=NONE

SCP THE .DMP FILE TO TARGET AND IMPORT IT:

TARGET:

imp FROMUSER=SCOTT TOUSER=SCOTT FILE=exp_streams.dmp  log=exp_streams.log STREAMS_INSTANTIATION=Y IGNORE=Y COMMIT=Y

STEP 14: START THE APPLY PROCESS AT TARGET – DBTARGET

@STEP14_STRMADMIN_TARGET_START_APPLY.SQL

— CONTENTS OF .SQL FILES

SET ECHO ON

spool c:\STREAMS_LOG\step14_STRMADMIN_TARGET_APPLY_START.log

connect STRMADMIN@DBTARGET

show user

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => ‘STREAM_APPLY_A1′);
END;
/

—- Set stop_on_error to false so apply does not abort for every error; then, start the Apply process on the destination

BEGIN

  DBMS_APPLY_ADM.SET_PARAMETER(

    apply_name => ‘STREAM_APPLY_A1′,

    parameter  => ‘disable_on_error’,

    value      => ‘n’);

END;

/

 — Start Apply

BEGIN 

DBMS_APPLY_ADM.START_APPLY( 

apply_name => ‘STREAM_APPLY_A1′); 

END; 

/ 

spool off

STEP 15 : START THE CAPTURE PROCESS AT SOURCE – DBSOURCE

@STEP15_STRMADMIN_SOURCE_START_CAPTURE.SQL

— CONTENTS OF .SQL FILES

SET ECHO ON

spool c:\STREAMS_LOG\step15_STRMADMIN_SOURCE_CAPTURE_START.log

connect STRMADMIN@DBTARGET

show user

BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name => ‘STREAM_CAPTURE_C1′);
END;
/
spool off

—YOUR COMMENTS ARE MOST WELCOME

 Thanks and Regards,

Satish.G.S