Applies
to:
Oracle
Server - Enterprise Edition
Information
in this document applies to any platform.
Oracle
Server Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2
Introduction
Oracle Streams is a very powerful and
flexible data replication utility without any additional licensing cost. It is
generally easy to get Streams set up and running; but ongoing maintenance and monitoring
of a Streams environment presents great challenges.
This paper will describe steps for
implementation of oracle streams between two different databases with some of
the issues and complications that we have directly encountered with Streams;
along with some recommendations on how to resolve those issues.
Streams Functionality
– How it works?
Overview of Streams
This section will
provide a basic overview of Streams – which is needed in order to be able to
troubleshoot and resolve any Streams issues.
Streams has the ability to replicate both DML
and DDL changes to objects in another database (or to other objects within the same
database.) You decide, via “rules,” which changes are replicated. For example,
you could specify that three specific tables are replicated, or that all of the
objects in one schema are replicated, etc.
Streams
have three main processes Capture,
Propagate and Apply.
Capture -> Propagate -> Apply
Database SOURCE Database TARGET
Capture process reads changes from SOURCE,
the Propagate process copies changes from
SOURCE,
and the Apply process writes changes to TARGET.
Oracle Streams is implemented in the
following way:
- A background capture process is configured to capture changes made to tables,schemas, or the entire database. The capture process captures changes from the redo log and formats each captured change into a logical change record (LCR). The capture process uses logminer to mine the redo/archive logs to format LCRs.
- The capture process enqueues LCR events into a queue that is specified.
- This queue is scheduled to Propagate events from one queue to another in a different database.
- A background apply process dequeues the events and applies them at the destination database.
Effective Steps to
implement Oracle 10.2.0.4.0 Streams
Schema Level Replication
(Client name not
disclosed)
SOURCE
DATABASE: pmxeeu01
TARGET
DATABASE: infeu01
SOURCE
SCHEMA NAME: MAXIMO
TARGET
SCHEMA NAME: MAXIMO
STEP 0: Check streams
unsupported objects present with the schema
---------------------------------------------------------------------------------------------------------------------
Listing
the Database Objects That Are Not Compatible With Capture Processes
export
ORACLE_SID=pmxeeu01
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/strm_unsupport_obj.lst
conn
STRMADMIN/STRMADMIN
COLUMN
OWNER HEADING 'Object|Owner' FORMAT A8
COLUMN
TABLE_NAME HEADING 'Object Name' FORMAT A30
COLUMN
REASON HEADING 'Reason' FORMAT A30
COLUMN
AUTO_FILTERED HEADING 'Auto|Filtered?' FORMAT A9
SELECT
OWNER, TABLE_NAME, REASON, AUTO_FILTERED FROM DBA_STREAMS_UNSUPPORTED where
owner='MAXIMO';
spool
off
-----------------------------------------------------------------------------------------------------------------------------------------------------
STEP 1 : ADD
SUPPLEMENT LOGIN TO ALL THE TABLES WHICH ARE PART OF STREAMS REPLICATION
----------------------------------------------------------------------------------------------------------------------------------------------------
export
ORACLE_SID=pmxeeu01
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step1_pmxeeu01_supp_log_data.sql
-- Add the supplement login for all the tables
present in MAXIMO schema at the source side
CONN
SYS@pmxeeu01 AS SYSDBA
set
echo on
show
user
alter
database force logging;
---
alter database add supplemental log data;
set
heading off
set
line 400
select
'alter table MAXIMO.' || table_name ||' ADD SUPPLEMENTAL LOG DATA (ALL)
columns;' from dba_tables where owner='MAXIMO';
spool
off
---
Edit the (step1_lmxeap01_supp_log_data.sql) file and execute the script
generated by above script.
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step1_pmxeeu01_supp_drop_log_data.lst
@/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step1_pmxeeu01_supp_drop_log_data.sql
@/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step1_pmxeeu01_supp_log_data.sql
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP 2 : SETTING THE
ENV VARIABLES AT SOURCE – pmxeeu01
--------------------------------------------------------------------------------------------------------------------------------------------------------
—
The database must run in archive log mode
@
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step2_globalname.sql
set
echo on
spool /apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step2_globalname.sql.lst
CONN
SYS AS SYSDBA
SHOW
USER
select
* from global_name; –to see current global_name
alter
system set global_names=true scope=both;
spool
off
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP 3 : SETTING THE
ENV VARIABLES AT TARGET – infeu01
--------------------------------------------------------------------------------------------------------------------------------------------------------
—
the database must run in archive log mode
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step3_infeu01_globalname.sql.lst
set
echo on
CONN
SYS AS SYSDBA
SHOW
USER
select
* from global_name; –to see current global_name
alter
system set global_names=false scope=both;
spool
off
–
Restart DB
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP 4 : CREATING
STREAMS ADMINISTRATOR USER AT SOURCE – pmxeeu01
--------------------------------------------------------------------------------------------------------------------------------------------------------
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step4_sys_pmxeeu01_create_user.sql.lst
CONN
SYS AS SYSDBA
SHOW
USER
CREATE
TABLESPACE streams_tbs DATAFILE
'/apps/oracle/pmxeeu01/data01/streams_tbs.dbf'SIZE 25M REUSE AUTOEXTEND ON
MAXSIZE UNLIMITED;
create
user STRMADMIN identified by STRMADMIN
default tablespace streams_tbs
quota unlimited on streams_tbs ;
grant
connect,resource,dba to strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
spool
off
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP
5: CREATING DB LINK AT THE SOURCE - pmxeeu01
(add the relevant
entries in the tnsnames.ora file)
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/STEP5_STRMADMIN_pmxeeu01_DBLINK.SQL.lst
/*
Connected as the Streams Administrator, create the streams queue and the
database link that will be used for propagation at pmxeeu01*/
conn
STRMADMIN/STRMADMIN
show
user
--CREATE
DATABASE LINK AT SOURCE as SYS
create
public database link infeu01 using 'infeu01';
---CREATE
DATABASE LINK AT SOURCE as STRMADMIN USER
create
database link infeu01 connect to STRMADMIN identified by STRMADMIN using
'infeu01';
----CREATE
DATABASE LINK AT TARGET as SYS
create
public database link pmxeeu01 using 'pmxeeu01';
---CREATE
DATABASE LINK AT SOURCE as STRMADMIN USER
create
database link pmxeeu01 connect to STRMADMIN identified by STRMADMIN using
'pmxeeu01';
spool
off
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP 6 : CREATING
STREAMS ADMINISTRATOR USER AT TARGET –
INFEU01
--------------------------------------------------------------------------------------------------------------------------------------------------------
export
ORACLE_SID=imxeeu01
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step6_sys_
infeu01_create_user.sql.lst
set
echo on
CONN
SYS AS SYSDBA
CREATE
TABLESPACE streams_tbs DATAFILE '/apps/oracle/pmxeeu01/arch/streams_tbs.dbf'
SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
create
user STRMADMIN identified by STRMADMIN
default tablespace streams_tbs
quota unlimited on streams_tbs ;
grant
connect,resource,dba to strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
spool
off
–
IF MAXIMO schema is not present in the target please create the same.
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP 7 : CREATE QUEUE
AND QUEUE TABLE AT THE SOURCE – pmxeeu01
--------------------------------------------------------------------------------------------------------------------------------------------------------
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/STEP7_STRMADMIN_SOURCE_QUEUE.SQL.lst
/*
Connected as the Streams Administrator, create the streams queue and the
database link that will be used for propagation at DBSOURCE */
set
echo on
conn
STRMADMIN/STRMADMIN
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 – infeu01
--------------------------------------------------------------------------------------------------------------------------------------------------------
export
ORACLE_SID=infeu01
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/STEP8_STRMADMIN_infeu01_QUEUE.SQL.lst
/*
Connected as the Streams Administrator, create the streams queue and the
database link that will be used for propagation at lmxena01*/
set
echo on
conn
STRMADMIN/STRMADMIN
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 – pmxeeu01
-------------------------------------------------------------------------------------------------------------------------------------------------------
export
ORACLE_SID=pmxeeu01
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/STEP9_STRMADMIN_pmxeeu01_PROPOGATION.SQL.lst
set
echo on
conn
STRMADMIN/STRMADMIN
SHOW
USER
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'MAXIMO',
streams_name =>
'STREAM_PROPAGATE_P1',
source_queue_name => 'STRMADMIN.STREAMS_QUEUE_Q',
destination_queue_name =>
'STRMADMIN.STREAMS_QUEUE_Q@imxeeu01',
include_dml => true,
include_ddl => true,
source_database => 'pmxeeu01');
END;
/
spool
off
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP 10 : CREATE
CAPTURE PROCESS AT SOURCE – pmxeeu01
-------------------------------------------------------------------------------------------------------------------------------------------------------
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/STEP10_STRMADMIN_pmxeeu01_CAPTURE.SQL.lst
set
echo on
conn
STRMADMIN/STRMADMIN
show
user
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'MAXIMO',
streams_type => 'CAPTURE',
streams_name => 'STREAM_CAPTURE_C1',
queue_name
=>
'STRMADMIN.STREAMS_QUEUE_Q',
include_dml => true,
include_ddl => true,
source_database =>
'pmxeeu01');
END;
/
SPOOL
OFF
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP 11 : CREATE
APPLY PROCESS AT TARGET – infeu01
--------------------------------------------------------------------------------------------------------------------------------------------------------
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/STEP11_STRMADMIN_infeu01_APPLY.SQL.lst
set
echo on
show
user
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'MAXIMO',
streams_type => 'APPLY',
streams_name => 'STREAM_APPLY_A1',
queue_name => 'STRMADMIN.STREAMS_QUEUE_Q',
include_dml => true,
include_ddl => true,
source_database => 'pmxeeu01');
END;
/
SPOOL
OFF
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP 12: CREATE
NEGATIVE RULE AT SOURCE FOR UNSUPPORTED TABLES – pmxeeu01
--------------------------------------------------------------------------------------------------------------------------------------------------------
Set negative rule for all the tables which are
unsupported by streams
(
List you got from querying
DBA_STREAMS_UNSUPPORTED)
set
echo on
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step12_strmadmin_source_negative_rule.sql.lst
conn
strmadmin/strmadmin
show
user
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name =>
'MAXIMO.DR$JOBPLANDES_TIDX$K',
<<<<<<<
Similarly the same should be applied to
the all the streams unsupporeted objects which is listed in step0.
>>>>>>>>>
streams_type =>
'capture',
streams_name =>
'STREAM_CAPTURE_C1',
queue_name =>
'strmadmin.STREAMS_QUEUE_Q',
include_dml =>
true,
include_ddl =>
true,
inclusion_rule =>
false);
END;
/
Script
executed for all the unsupported objects by streams
@/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step12_strmadmin_source_negative_rule.sql
spool
off
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP 13: STREAMS
OBJECT INSTANTATION
--------------------------------------------------------------------------------------------------------------------------------------------------------
pmxeeu01
-------------
expdp
strmadmin/strmadmin DIRECTORY=backup DUMPFILE=maximo__stream_ pmxeeu01.dmp SCHEMAS=maximo exclude=STATISTICS
logfile=maximo__stream_lmxeap01.log parallel=4
infeu01
-----------
impdp
strmadmin/strmadmin DIRECTORY=backup DUMPFILE=maximo__stream_lmxeap01.dmp SCHEMAS=maximo
logfile=maximo__stream_lmxena01.log parallel=4
--------------------------------------------------------------------------------------------------------------------------------------------------------
By
Manaually instantiating the objects
--------------------------------------------------------------------------------------------------------------------------------------------------------
Get
the Instantiation SCN at the source database:
----------------------------------------------------------------------------------
connect
STRMADMIN/STRMADMIN@pmxeeu01
set
serveroutput on
DECLARE
iscn
NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn
:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE
('Instantiation SCN is: ' || iscn);
END;
/
----------------------------------------------------------------------------------
Instantiate
the objects at the destination database with the above SCN value
----------------------------------------------------------------------------------
connect
STRMADMIN/STRMADMIN@infeu01
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
SOURCE_SCHEMA_NAME
=> 'MAXIMO',
SOURCE_DATABASE_NAME
=> 'pmxeeu01',
RECURSIVE
=> TRUE,
INSTANTIATION_SCN
=> &iscn );
END;
/
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP
14: START THE APPLY PROCESS AT TARGET – infeu01
--------------------------------------------------------------------------------------------------------------------------------------------------------
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/STEP14_STRMADMIN_infeu01_START_APPLY.SQL.lst
connect
STRMADMIN/STRMADMIN
show
user
—-
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.ALTER_APPLY(
apply_name
=> 'STREAM_APPLY_A1',
apply_user
=> 'MAXIMO');
END;
/
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'STREAM_APPLY_A1',
parameter
=> 'disable_on_error',
value
=>'n');
END;
/
begin
dbms_apply_adm.alter_apply(
apply_name
=> 'STREAM_APPLY_A1',
rule_set_name
=> NULL,
remove_rule_set
=> TRUE);
end;
—
Start Apply
DECLARE
v_started
number;
BEGIN
SELECT
decode(status, 'ENABLED', 1, 0) INTO v_started
FROM
DBA_APPLY WHERE APPLY_NAME = 'STREAM_APPLY_A1';
if
(v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name
=> 'STREAM_APPLY_A1');
end
if;
END;
/
--------------------------------------------------------------------------------------------------------------------------------------------------------
STEP 15 : START THE
CAPTURE PROCESS AT SOURCE – pmxeeu01
--------------------------------------------------------------------------------------------------------------------------------------------------------
spool
/apps/oracle/pmxeeu01/admin/scripts/maximo_streams_refresh/step15_strmadmin_pmxeeu01_start_capture.lst
set
echo on
connect
STRMADMIN/STRMADMIN
show
user
BEGIN
DBMS_CAPTURE_ADM.ALTER_CAPTURE(
capture_name
=> 'STREAM_CAPTURE_C1',
checkpoint_retention_time
=> 7);
END;
/
begin
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name
=>'STREAM_CAPTURE_C1');
end;
/
Method
to STOP Streams Setup
APPLY_NAME = STREAM_APPLY_A1
CAPTURE_NAME = STREAM_CAPTURE_C1
PROPAGATION_NAME =
STREAM_PROPAGATION_P1
Execute the below
steps with streams administrator user only:
--------------------------------------------------------------------------
STEP
1. STOP THE APPLY PROCESS: [ infeu01 ]
connect
strmadmin/strmadmin
BEGIN
DBMS_APPLY_ADM.STOP_APPLY(
apply_name => 'STREAM_APPLY_A1');
END;
/
STEP
2. STOP THE PROPAGATION PROCESS: [
pmxeeu01]
(stopping
the propagation might take time if the Apply is in progress)
BEGIN
DBMS_PROPAGATION_ADM.STOP_PROPAGATION(
propagation_name =>
'STREAM_PROPAGATE_P1');
END;
/
STEP
3. STOP THE CAPTURE PROCESS : [ pmxeeu01]
BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'STREAM_CAPTURE_C1');
END;
/
Method
to START Streams Setup
APPLY_NAME
= STREAM_APPLY_A1
CAPTURE_NAME
= STREAM_CAPTURE_C1
PROPAGATION_NAME
= STREAM_PROPAGATION_P1
connect
strmadmin/strmadmin
STEP
1. START THE APPLY PROCESS:
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name
=> 'STREAM_APPLY_A1');
END;
/
STEP
2. START THE PROPAGATION PROCESS:
BEGIN
DBMS_PROPAGATION_ADM.START_PROPAGATION(
propagation_name
=> 'STREAM_PROPAGATE_P1');
END;
/
STEP
3. START THE CAPTURE PROCESS
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name
=> 'STREAM_CAPTURE_C1');
END;
/
Troubleshooting
issues handled while implementing streams setup ….
Issue
1 : ORA-600 [kwqbdrcp101] after Drop or Recreate of Propagation
Issue
2 : ORA-32593: database supplemental logging attributes in flux
Issue
3 : ORA-23603(STREAMS enqueue aborted due to low SGA)
Fixes
for above issues..
Issue 1
ORA-600 [kwqbdrcp101]
after Drop or Recreate of Propagation
ORA-600
[kwqbdrcp101] after Drop or Recreate of Propagation
------------------------------------------------------------------------------
ORA-00600:
internal error code, arguments: [kwqbdrcp101], [], [], [], [], [], [], []
Tue
Nov 29 16:19:20 2011
Errors
in file
/apps/oracle/lmxeap01/data01/inf1ap01/admin/bdump/inf1ap01_j000_5727.trc:
ORA-00600:
internal error code, arguments: [kwqbdrcp101], [], [], [], [], [], [], []
ORA-06512:
at "SYS.DBMS_AQADM_SYS", line 7236
ORA-06512:
at "SYS.DBMS_AQADM_SYS", line 7514
ORA-06512:
at "SYS.DBMS_AQADM", line 978
ORA-06512:
at line 1
Comments
------------------------------------------------------------------------------
Issue
Due to message existence in Queue table and not applying in the Target --
------------------------------------------------------------------------------
Solution
-----------
1.
Stop the Propagation.
exec
DBMS_PROPAGATION_ADM.STOP_PROPAGATION('');
2.
Drop the Propagation.
exec
DBMS_PROPAGATION_ADM.DROP_PROPAGATION('');
3.
Purge the Queue table.
------------------------------------------------------------------------------------------------------------------------------------------------------------
STEPS
FOLLOWED IN THE SOURCE - pmxeeu01
-----------------------------------------------------
1.
Stop the Propagation.
BEGIN
DBMS_PROPAGATION_ADM.STOP_PROPAGATION(
propagation_name =>
'STREAM_PROPAGATE_P1');
END;
/
2.
Drop the Propagation.
BEGIN
DBMS_PROPAGATION_ADM.DROP_PROPAGATION(
propagation_name =>
'STREAM_PROPAGATE_P1');
END;
/
3.
Purge the Queue table.
DECLARE
options
dbms_aqadm.aq$_purge_options_t;
BEGIN
options.delivery_mode:=DBMS_AQADM.BUFFERED;
DBMS_AQADM.PURGE_QUEUE_TABLE('STREAMS_QUEUE_TABLE',NULL,options);
END;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------
Remove
the Streams configuration on both pmxeeu01 and infeu01 with the below script,
execute
DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
n Proceed
with the full streams setup again.
To
check the Status of Streams
---------------------------------------
SQL>
SELECT subscriber_name, cnum_msgs, total_dequeued_msg,total_spilled_msg FROM V$BUFFERED_SUBSCRIBERS;
SUBSCRIBER_NAME CNUM_MSGS TOTAL_DEQUEUED_MSG
TOTAL_SPILLED_MSG
------------------------------
---------- ------------------ -----------------
50569 50569 0
------------
The above query shows that all the changes are applied to the Target ------
Issue 2
ORA-32593:
database supplemental logging attributes in flux.
Comments:-
Issue
occurred when we tried to DROP the SUPPLEMENTAL LOG DATA as the Source database
has taken longer to execute the ADD SUPPLEMENT LOG TO ALL THE MAXIMO TABLES.
Fix:
The
query used to drop,
SQL>
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;
ALTER
DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS
*
ERROR
at line 1:
ORA-32593:
database supplemental logging attributes in flux
To
Fix this issue we need to (SOURCE) bounce the database and then proceed,
shutdown immediate
startup
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;
startup
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;
Then proceed with
the streams setup above given steps.
Issue 3 :
ORA-23603(STREAMS
enqueue aborted due to low SGA)
Comment
:-
We
tried the streams setup and the capture
and apply process stared and started replicating at the Targer site, within a
short time for replicating large DML transactions streams ended up with this error in the Alert log.
Fix :-
We need to stop the
complete streams setup using the steps, or the messages will get spilled
Increase the SGA with the required physical memory
available and then execute the streams setup configuration. Then do the export
and import of the DDL alone to the Target database,so that streams will start
replicating to the Target database.
Views/ Queries used to Troubleshooting
Streams
Capture Process
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
References used while
streams implementation
Oracle
Metalink notes .. and supportive documents.
No comments:
Post a Comment