Home » Server Options » Streams & AQ » streams not functional, but no errors in capture/propagate/apply views (10.2.0.3)
streams not functional, but no errors in capture/propagate/apply views [message #297453] Thu, 31 January 2008 13:36 Go to next message
pras2213
Messages: 1
Registered: January 2008
Location: San Francisco, CA
Junior Member
Hi,

I'm unable to resolve an issue with streams, local capture, uni-directional. I've set up streams on source and destination with no errors showing in the capture, propagation and apply views. The views show 'enabled' in all three areas and publishing messages/capturing LCRS/enabled notifications and the queues are both created and queue-to-queue is set to true. The database links have been tested and the 2 database listeners are configured correctly.

However, the STRMMON shows no actual propagation happening and there are basically no messages being applied, even though both of these processes are enabled with no errors. I followed the following metalink doc very closely in terms of setting init params and also applied 8 of the most recent patches for streams. The patching has eliminated issues like excessive flow control and various ORA-errors, but the problem seems to be with queuing and/or propagation. I've referenced the Oracle Streams Concepts & Admin Guide as well as the Streams Replication Guide, ran the Metalink Health Checks and there are no errors from over 30 queries I've run.

10.2 Streams Recommendations Overview of Oracle Streams 418755.1 17-DEC-2007

The queues and propagation colums are 0 and the DDL and DML
changes I'm making at source aren't being shipped to the destination. I'm
attaching a snapshot of the STRRMON and the health checks from both
source/destination. I have configured the parameters, but would really
appreciate some specific advice on how to get the Streams process enabled and
active.

Although capture shows enabled with no errors, somehow the enqueue/dequeue processes aren't propagating the changes made. I originally used impdp to import the exact same objects into identical schema, created newly, in both environments, so there should be no issue with SCN mismatches. Is there some schema/table level instantiation (recursive=>all?) cmd that I need to give? and if so what is the syntax? or is there a need for supplemental logging (i've forced logging at the database level, but I'm trying to do schema-level replication)... Please keep in mind that I've seen all of the oracle/metalink docs and parsed through quite a few of the forums strings, but still no luck. Hopefully I'm just missing one piece of the puzzle that someone can help me identify based on their experience.

Thanks,

p

this is the protocol I followed


APPLIED 8 PATCHES FROM METALINK...

init params...
GLOBAL_NAMES ALTER SYSTEM SET global_names=TRUE scope=both;
OPEN_LINKS 4 set at 4
PROCESSES 5000 alter system set processes=5000 scope=spfile; GIDQUEST – 150
REMOTE_ARCHIVE_ENABLE TRUE (only needed for downstreams capture; at both source/destination)
UNDO_RETENTION >900 900

*.db_cache = 1,610,612,736
*.streams_pool_size=500M
*.shared_pool_size=1000M
*.sga_target= //enables automatic shared memory management
*.sga_max_size=3500M SGA_MAX_SIZE=3,758,096,384
*.db_recovery_file_dest='/oraback/GIDTLSD1/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.log_archive_dest_1='LOCATION=/oraback/GIDTLSD1/oraarch MANDATORY'
*.log_archive_format='GIDTLSD1_%t_%s_%r.arc'
Configure init.ora to contain this hidden parameter at both source/destination:
*._job_queue_interval=1 Scan rate interval (seconds) of job queue

alter database flashback on; ON
select log_mode from v$database; ON
alter database archivelog
ALTER DATABASE FORCE LOGGING; ON

Source setup...

schema/tablespace creation, setup queue, database link, test link, add schema rules, add propagation rules

CREATE TABLESPACE streams_tbs..
CREATE USER strmadmin...
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;

execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

conn strmadmin/pw

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
QUEUE_NAME=>'PPCAPR_QUEUE',
QUEUE_TABLE=>'PPCAPR_QUEUE_TABLE',
QUEUE_USER=>'STRMADMIN',
STORAGE_CLAUSE=>'TABLESPACE STREAMS_TBS');
END;

select * from all_queues
select schema,qname,destination,schedule_disabled,failures,last_error_msg
from dba_queue_schedules;

Destination schema setup...

schema/tablespace creation, setup queue, database link, test link, apply schema apply rules
CREATE TABLESPACE streams_tbs..
CREATE USER strmadmin...
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;

execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

conn strmadmin/pw

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
QUEUE_NAME=>'PPCAPR_QUEUE',
QUEUE_TABLE=>'PPCAPR_QUEUE_TABLE',
QUEUE_USER=>'STRMADMIN',
STORAGE_CLAUSE=>'TABLESPACE STREAMS_TBS');
END;

select * from all_queues

DATAPUMP EXPORT from Source and Import into Destination to Instantiate

-SOURCE
expdp system/manager PARFILE=expdp.par

SCHEMAS=PPCAPR
DUMPFILE=ppcapr_expdp%U.dmp
DIRECTORY=ppcapr_expdp
LOGFILE=ppcapr_expdp.log
STATUS=120
PARALLEL=3
TRANSPORT_FULL_CHECK=Y
VERSION=10.2.0.3
CONTENT=ALL
JOB_NAME=ppcapr_expdp

--A parameter comparable to OBJECT_CONSISTENT is not needed.
--Use FLASHBACK_SCN and FLASHBACK_TIME for this functionality.
scp –p ppcapr_expdp* oracle@10.8.169.78:/oraback/ppcapr_expdp/


--DESTINATION
impdp system/manager PARFILE=impdp.par
SCHEMAS=PPCAPR
DUMPFILE=ppcapr_expdp%U.dmp
DIRECTORY=ppcapr_impdp
LOGFILE=ppcapr_impdp.log
STATUS=120
PARALLEL=3
VERSION=10.2.0.3
CONTENT=ALL
JOB_NAME=ppcapr_impdp
STREAMS_CONFIGURATION=Y

DESTINATION
begin
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'PPCAPR',
streams_type => 'APPLY',
streams_name=>'PPCAPR_APPLY',
queue_name=>'STRMADMIN.PPCAPR_QUEUE',
include_dml=> TRUE,
include_ddl=> TRUE,
source_database=>'GIDTLSD1');
END;

begin
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name=>'PPCAPR_APPLY',
apply_user=>'STRMADMIN');
end;

DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = 'PPCAPR_APPLY';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'PPCAPR_APPLY');
end if;
END;

CREATE DATABASE LINK GIDTLSD1 CONNECT TO STRMADMIN IDENTIFIED BY
STRMADMIN USING 'GIDTLSD1';

SOURCE DATABASE
BEGIN
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
schema_name => 'integrity',
supplemental_logging => 'all');
END;

CREATE DATABASE LINK PPCAPR_GIDTRXD1 CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING 'PPCAPR_GIDTRXD1';

SELECT DB_LINK FROM USER_DB_LINKS;
select sysdate from dual@GIDTRXD1

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME =>'PPCAPR',
STREAMS_TYPE =>'CAPTURE',
STREAMS_NAME=>'PPCAPR_CAPTURE',
QUEUE_NAME=>'STRMADMIN.PPCAPR_QUEUE',
INCLUDE_DML=>TRUE,
INCLUDE_DDL=>TRUE);
END;

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (
SCHEMA_NAME=>'PPCAPR',
STREAMS_NAME=>'PPCAPR_PROPAGATE',
SOURCE_QUEUE_NAME=>'STRMADMIN.PPCAPR_QUEUE',
DESTINATION_QUEUE_NAME=> 'STRMADMIN.PPCAPR_QUEUE@GIDTRXD1',
SOURCE_DATABASE=>'GIDTLSD1',
INCLUDE_DML=>TRUE,
INCLUDE_DDL=>TRUE);
END;

Part II
SOURCE DATABASE

begin
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name=>'PPCAPR_CAPTURE');
end;


-- Specify parallelism (auto restarts capture process)
BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'strm01_capture',
parameter => 'parallelism',
value => '2');
END;

DESTINATION

-- Specify parallelism (auto restarts apply process)
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'strm01_apply',
parameter => 'parallelism',
value => '2');
END;

-----------------------------------------troubleshooting attempts
BEGIN
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION('PPCAPR');
END;

- check propagation scheduling, disable/enable to restart

schema instantiation at capture
start queue at capture
start queue at apply

unschedule propagation
schedule

Stop the propagation.

Make sure the source queue is empty.

Make sure that the destination queue is empty and has no unapplied, spilled messages before you drop the propagation.

Re-create the propagation with the queue_to_queue parameter set to TRUE in the creation procedure.

schedule propagation

stop capture
start capture

prepare schema instantiation at apply

start queue again

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'PPCAPR_APPLY',
parameter => 'parallelism',
value => '2');
END;

--reverse direction link
CREATE DATABASE LINK GIDQUEST CONNECT TO STRMADMIN IDENTIFIED BY
STRMADMIN USING 'GIDQUEST';

STOP, unschedule, drop propagation,
add propagation rules, schedule, start

BEGIN
DBMS_PROPAGATION_ADM.START_PROPAGATION('PPCAPR_PROPAGATE');
END;

BEGIN
DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(
queue_name => 'PPCAPR_QUEUE',
destination => 'GIDQSTD1.GAP.COM',
latency => 1,
destination_queue => 'STRMADMIN.PPCAPR_QUEUE');
END;
/

BEGIN
DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(
queue_name => 'PPCAPR_QUEUE',
destination => 'GIDQSTD1.GAP.COM',
latency => 5,
destination_queue => 'STRMADMIN.PPCAPR_QUEUE');
END;

alter system switch logfile @SOURCE

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (
SCHEMA_NAME=>'PPCAPR',
STREAMS_NAME=>'PPCAPR_PROPAGATE',
SOURCE_QUEUE_NAME=>'STRMADMIN.PPCAPR_QUEUE',
DESTINATION_QUEUE_NAME=> 'STRMADMIN.PPCAPR_QUEUE@GIDQSTD1',
SOURCE_DATABASE=>'GIDQUEST',
INCLUDE_DML=>TRUE,
INCLUDE_DDL=>TRUE,
INCLUSION_RULE=>TRUE,
QUEUE_TO_QUEUE=>TRUE);
END;

BEGIN
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
schema_name => 'PPCAPR',
supplemental_logging => 'ALL');
END;
/

remove queue/setup queue/start queue (capture, apply)..but nothing seems to be propagating inspite of running DDL and DML cmds at source database..please help!

  • Attachment: GIDQUEST.html
    (Size: 333.65KB, Downloaded 4997 times)
Re: streams not functional, but no errors in capture/propagate/apply views [message #297719 is a reply to message #297453] Sat, 02 February 2008 22:38 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
CREATE DATABASE LINK GIDTLSD1 CONNECT TO STRMADMIN IDENTIFIED BY
STRMADMIN USING 'GIDTLSD1';


Seems you have done wrong here. As Database link name must be same exactly as the name of database global name. According your attachment global name is not GIDTLSD1.
Previous Topic: Database link on Oracle Streams
Next Topic: streams Configuration
Goto Forum:
  


Current Time: Thu Mar 28 15:18:23 CDT 2024