GoldenGate

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Data is Synchronizing from Source to Target "No records were replicated"

849234Apr 8 2011 — edited Apr 15 2011

After execution of extract process in source server, There are no errors are reported and also No records were replicated (in the RLOAD.rpt file).

Same configuration and parameter files are working fine when source and target DB are in same server (Source server).

Kindly let us know for any further information if required for your analysis.

Details:
Source:

Server: Centos 05, Linux 64 bit process
Oracle DB: 11 G R2

Target:

Server: Centos 05, Linux 32 bit process
Oracle DB: 11 G R2

@source:
; mgr.prm

port 7809
DYNAMICPORTLIST 8001, 8002, 9500-9502
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS
CHECKMINUTES 5

;eload.prm

EXTRACT ELOAD
RMTHOST 10.117.219.138 MGRPORT 15000
RMTTASK REPLICAT, GROUP RLOAD
SETENV NLS_LANG=AMERICAN_AMERICA.AL32UTF8
USERID apps PASSWORD apps
TABLE TEST_HZ_PARTIES;
TABLE TEST_HZ_PARTY_PREFERENCES;
TABLE TEST_HZ_PARTY_RELATIONSHIPS;
TABLE TEST_HZ_PARTY_SITES;
TABLE TEST_HZ_PARTY_SITE_USES;
TABLE TEST_HZ_PARTY_USAGES_B;
TABLE TEST_HZ_PARTY_USG_ASSIGNMENTS;

; emastr.prm

EXTRACT EMASTR

EXTTRAIL ./dirdat/em

SETENV NLS_LANG=AMERICAN_AMERICA.AL32UTF8

USERID apps, PASSWORD apps

STATOPTIONS RESETREPORTSTATS

REPORT AT 00:01

REPORTROLLOVER AT 00:01

REPORTCOUNT EVERY 60 SECONDS, RATE

TABLE TEST_HZ_PARTIES;
TABLE TEST_HZ_PARTY_PREFERENCES;
TABLE TEST_HZ_PARTY_RELATIONSHIPS;
TABLE TEST_HZ_PARTY_SITES;
TABLE TEST_HZ_PARTY_SITE_USES;
TABLE TEST_HZ_PARTY_USAGES_B;
TABLE TEST_HZ_PARTY_USG_ASSIGNMENTS;

;pmastr.prm

EXTRACT PMASTR
RMTHOST 10.117.219.138 MGRPORT 15000
RMTTRAIL ./dirdat/pm
PASSTHRU
TABLE TEST_HZ_PARTIES;
TABLE TEST_HZ_PARTY_PREFERENCES;
TABLE TEST_HZ_PARTY_RELATIONSHIPS;
TABLE TEST_HZ_PARTY_SITES;
TABLE TEST_HZ_PARTY_SITE_USES;
TABLE TEST_HZ_PARTY_USAGES_B;
TABLE TEST_HZ_PARTY_USG_ASSIGNMENTS;

@target
; mgr.prm

PORT 15000
DYNAMICPORTLIST 15010-15020
PURGEOLDEXTRACTS /home/gg/OGG/dirdat/*, USECHECKPOINTS
CHECKMINUTES 5

;rload.prm

REPLICAT RLOAD
SETENV NLS_LANG=AMERICAN_AMERICA.AL32UTF8
SOURCEDEFS /home/gg/OGG/dirdef/source.def
USERID SIEBEL, PASSWORD SIEBEL
--ASSUMETARGETDEFS
MAP TEST_HZ_PARTIES, TARGET TEST_HZ_PARTIES;
MAP TEST_HZ_PARTY_PREFERENCES, TARGET TEST_HZ_PARTY_PREFERENCES;
MAP TEST_HZ_PARTY_RELATIONSHIPS, TARGET TEST_HZ_PARTY_RELATIONSHIPS;
MAP TEST_HZ_PARTY_SITES, TARGET TEST_HZ_PARTY_SITES;
MAP TEST_HZ_PARTY_SITE_USES, TARGET TEST_HZ_PARTY_SITE_USES;
MAP TEST_HZ_PARTY_USAGES_B, TARGET TEST_HZ_PARTY_USAGES_B;
MAP TEST_HZ_PARTY_USG_ASSIGNMENTS, TARGET TEST_HZ_PARTY_USG_ASSIGNMENTS;

;rmastr.prm

REPLICAT RMASTR
HANDLECOLLISIONS
END RUNTIME
SETENV NLS_LANG=AMERICAN_AMERICA.AL32UTF8
USERID SIEBEL, PASSWORD SIEBEL
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rmastr.dsc, purge

STATOPTIONS RESETREPORTSTATS

REPORT AT 00:01

REPORTROLLOVER AT 00:01

REPORTCOUNT EVERY 60 SECONDS, RATE

MAP TEST_HZ_PARTIES, TARGET TEST_HZ_PARTIES;
MAP TEST_HZ_PARTY_PREFERENCES, TARGET TEST_HZ_PARTY_PREFERENCES;
MAP TEST_HZ_PARTY_RELATIONSHIPS, TARGET TEST_HZ_PARTY_RELATIONSHIPS;
MAP TEST_HZ_PARTY_SITES, TARGET TEST_HZ_PARTY_SITES;
MAP TEST_HZ_PARTY_SITE_USES, TARGET TEST_HZ_PARTY_SITE_USES;
MAP TEST_HZ_PARTY_USAGES_B, TARGET TEST_HZ_PARTY_USAGES_B;
MAP TEST_HZ_PARTY_USG_ASSIGNMENTS, TARGET TEST_HZ_PARTY_USG_ASSIGNMENTS;

Source Configuration after creating parameter files:

ADD EXTRACT ELOAD, SOURCEISTABLE

ADD EXTRACT EMASTR, TRANLOG, BEGIN NOW, DESCRIPTION "Capture change data from Redo"

ADD EXTTRAIL ./dirdat/em, EXTRACT EMASTR, MEGABYTES 500

ADD EXTRACT PMASTR, EXTTRAILSOURCE ./dirdat/em, DESCRIPTION "Reads from dirdat/em on SOURCE and writes to dirdat/pm on TARGET"

ADD RMTTRAIL ./dirdat/pm, EXTRACT PMASTR, MEGABYTES 500

Target Configuration after creating parameter files

ADD REPLICAT RLOAD, SPECIALRUN

ADD REPLICAT RMASTR, EXTTRAIL ./dirdat/pm, DESCRIPTION "Change Data Apply – Reads from /dirdat/pm"

Execution:

start emastr

start pmastr

status er *
EXTRACT EMASTR: RUNNING
EXTRACT PMASTR: RUNNING

start eload

Cookiemonster76

So loop over the table. Inside the loop build up a string of the text you want in the email.

After the loop check if the string is empty, if it is assign the not exists message

After that call the procedure to send the email.

user12251389

I tried but still had the same issue

Saubhik

user12251389 wrote:

I tried but still had the same issue

What is the exact problem you are facing ?

John Stegeman

You tried and it didn't work? What does "didn't work" mean? What did you try - show us?

1 person found this helpful

user12251389

I have currently 2 records in TRACK_KPI table. When i execute this procedure i am getting 2 emails one for each record. So for example if i have 100 records now in TRACK_KPI table then i will receive 100 emails for each records which i dont want. I want all this records from TRACK_KPI table send in one email

John Stegeman

Yes, because you send the email in a loop. Don't do that. Instead, build the email content in the loop (hint: store it in a CLOB or varchar2 variable, depending on the potential size) and send the email outside the loop

user12251389

Here is what i have tried :

FOR KPI_TRACK_RUNNING_ROW IN (Select KPI_NAME FROM RATOR_MONITORING.TRACK_KPI)

LOOP

sql='SEND_MAIL_SMTP(

''support@leo'', --Sender

''r.d@leo.com'', --Recipient

''PREPROD - KPI NOT RUNNING'', --Subject

''Below KPIs are not running:''' || KPI_TRACK_RUNNING_ROW.KPI_NAME --Message

);

execute immediate l_sql;

END LOOP;

Saubhik

Answer

You need something like

CREATE OR REPLACE PROCEDURE execute_send_mail_smtp AS

v_kpi varchar2(4000) :=' '; --assuming your kpi_names are not long.

begin

FOR KPI_TRACK_ROW IN (Select KPI_NAME FROM RATOR_MONITORING.TRACK_KPI)

LOOP

v_kpi :=v_kpi||KPI_TRACK_RUNNING_ROW.KPI_NAME||chr(10);

END LOOP;

SEND_MAIL_SMTP(

'support@leo.com', --Sender

'r.d@leo.com', --Recipient

'TEST- KPI NOT RUNNING', --Subject

'Below KPIs are not running:' || v_kpi --Message

);

end execute_send_mail_smtp;

NOT TESTED.

Marked as Answer by user12251389 · Sep 27 2020

user12251389

How can i build email content outside loop ? I have procedure SEND_MAIL_SMTP also which i cant take it in string ?

John Stegeman

send_mail_smtp takes a parameter. Saubhik gave you an example.

user12251389

I tried your logic it sends email without any data and when i replace

v_kpi := v_kpi||chr(10);

v_kpi :=KPI_TRACK_ROW.KPI_NAME||chr(10);

in your logic then it sends an email with only one record but there are two records exist in my table

John Stegeman

Think about it...

Why did you replace Saubhik's code, which looked correct with yours, which is obviously not?

Saubhik

I have edited that portion, but this is not a code writing service and as a professional you should take the idea and write, debug yourself.

1 person found this helpful

user12251389

Now it works thanks but just a small question can i use IF condition in this logic ? Because if there is no records exist in the TRACK_KPI table then its not sending any emai. So if there is nor record exist then i just want to send email as 'NO KPI Tracked'

John Stegeman

and just noticed... why the heck are you using execute immediate to call a stored procedure from PL/SQL? There's no need for that

John Stegeman

You can use any PL/SQL construct you like, including "IF"

user12251389

Yes it was mistake i copy paste and didn't realize

user12251389

I have tried with IF logic and it works now. Just a small question can we send email to more than one Recipient ? When i tried to enter 2 Recipient then it gaves me error as

ora-29279 smtp permanent error 550 user unknown

John Stegeman

Talk to whomever wrote send_mail_smtp, or look at the code yourself and figure it out. Of course SMTP can send mail to more than one recipient, if you do it correctly.

1 - 19

Locked Post

New comments cannot be posted to this locked post.

Locked on May 13 2011

Added on Apr 8 2011

#goldengate

7 comments

518 views

GoldenGate

Data is Synchronizing from Source to Target "No records were replicated"

Comments

Post Details