Materialized view refresh - ORA-02019: connection description for remote database not found.

Please could you help me with an Oracle 9i materialized view issue.

I have materialized views setup on a laptop and a refresh group.
The database link is setup and working (tested via SQLPLUS select sysdate from dual@dblink)

However, the refresh hangs when run. I can see the session on the oracle server and it's displaying an error when running the statement for the first MV: -

ORA-02019: connection description for remote database not found.

Sorry if I haven't posted enough information - I am new to materialized view's!

We have another laptop which is setup the same way (as far as I can see) which refreshes fine!

Any assistance gratefully appreciated.
Col_GAsked:
Who is Participating?
 
Col_GConnect With a Mentor Author Commented:
Thank you sdstuber for your help, this is now resolved.

In the end I created a test database C05TEST on the laptop and ran a full import from another working database C03 which created all the MV's, DB Links etc.
I then truncated the tables and imported the table data from the old problem database C05 via a db link. Tested the refresh on C05TEST - all OK!
I then dropped the old problem database C05,  recreated it and ran a full import from the Test database.
Tested the refresh which worked OK but when we actually inputted new data via the app and ran another refresh the new data was overwritten, so it was not being passed to the main server before the refresh (we had this option selected on the refresh).
Turned out it didn't have a propagator, so registered MVADMIN as the propagator - EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR(username => 'MVADMIN')
This enabled me to create the Materialized View group which was also missing.
Re-tested - all OK.

A bit of a ‘round the houses’ approach and would like to have known what was up with the original database but a least it's working and the laptop is back out in the field.

Thanks again for your assistance.

Col
0
 
sdstuberCommented:
the owner of the materialized view needs to own the database link or the link needs to be public.
0
 
Col_GAuthor Commented:
Hi,
Thank you for your reply, I have checked the link & it's a public link.

All the posts I found seem to point to the link but I have checked & double checked this & appears correct. Is there anything else i can try?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sdstuberCommented:
there must be something else that isn't being seen

can you post the exact query you used  and the results of

select * from dba_db_links
0
 
Chakravarthi AyyalaDatabase AdministratorCommented:
In the materialized view creation, are you using FORCE or FAST?
0
 
Col_GAuthor Commented:
We are using FAST.

The results of SELECT * FROM DBA_DB_LINKS are in the attached file DBLinks.txt.

The MV's and Refresh Group are owned by user CAL.

As there are 3 DB Links will it default to the one owned by CAL or use the Public view?

When I tested the links I logged in as both SYS and MVADMIN and ran: -
SELECT SYSDATE FROM DUAL@SDIR.GEMCO.LOCAL
Both returned the date

Thank you for your assistance.
DBLinks.txt
0
 
sdstuberCommented:
FAST vs COMPLETE vs FORCE is irrelevant for the error you are getting


It will use the one owned by CA.  What happens when you test using CAL?

What is the MV query?
0
 
Col_GAuthor Commented:
The laptop is temp unavailable so I'll test the CAL link tomorrow but the MV Query is as follows: -

SELECT "CONFIRMED", "DATE_BOOKED", "DATE_PROPOSED", "DATE_COMPLETE", "PRINTED", "CANCELED", "CANCEL_REASON", "CONFIRMED_WITH", "CONFIRMED_DATE", "CREATE_USER", "CREATE_DATE_TIME", "EQUIPMENT_DUE_FROM", "EQUIPMENT_DUE_TO", "SCHEDULE_ID", "NUMBER_OF_ITEMS", "CANCELLED_BY", "CANCELLED_DATE", "CUSTOMER_ORDER_NUMBER", "COMMENTS_TEMP", "COMMENTS", "CALL_ID", "JOB_SHEET", "ENGINEER_ID", "DATE_DUE", "CALL_TYPE", "CUSTOMER_ID", "COMPLETE" FROM "CAL"."CALLS"@SDIR.GEMCO.LOCAL

When I ran the refresh and checked the sessions on the Oracle server I could see the session which was trying to execute this statement. It was this sessions that displayed the ORA-02019 error.
The session was showing the following as the current SQL...
SELECT /*+ */ "A2"."CONFIRMED","A2"."DATE_BOOKED","A2"."DATE_PROPOSED","A2"."DATE_COMPLETE","A2"."PRINTED","A2"."CANCELED","A2"."CANCEL_REASON","A2"."CONFIRMED_WITH","A2"."CONFIRMED_DATE","A2"."CREATE_USER","A2"."CREATE_DATE_TIME","A2"."EQUIPMENT_DUE_FROM","A2"."EQUIPMENT_DUE_TO","A2"."SCHEDULE_ID","A2"."NUMBER_OF_ITEMS","A2"."CANCELLED_BY","A2"."CANCELLED_DATE","A2"."CUSTOMER_ORDER_NUMBER","A2"."COMMENTS","A2"."CALL_ID","A2"."JOB_SHEET","A2"."ENGINEER_ID","A2"."DATE_DUE","A2"."CALL_TYPE","A2"."CUSTOMER_ID","A2"."COMPLETE","A1"."CHANGE_VECTOR$$" FROM "CAL"."CALLS" "A2", ( (SELECT DISTINCT "A5"."CALL_ID" "CALL_ID","A5"."CHANGE_VECTOR$$" "CHANGE_VECTOR$$" FROM "CAL"."MLOG$_CALLS" "A5" WHERE "A5"."SNAPTIME$$">:1 AND "A5"."DMLTYPE$$"<>'D')UNION (SELECT "A4"."CALL_ID" "CALL_ID","A4"."CHANGE_VECTOR$$" "CHANGE_VECTOR$$" FROM "CAL"."USLOG$_CALLS"@! "A4" WHERE "A4"."DMLTYPE$$"<>'I')) "A1" WHERE "A2"."CALL_ID"="A1"."CALL_ID" ORDER BY "A2"."CALL_ID"

Hope this helps
0
 
Col_GAuthor Commented:
Hi,

I have logged into SQLPlus as user CAL and run: -

select sysdate from dual@sdir.gemco.local which returned the SYSDATE OK.

I then removed the public link and re-ran the above which returned ORA-02019: connection description for remote database not found.

I dropped the CAL DB Link and recreated with username & passsword for CAL and the correct service_name. I re-ran the select script which returned the SYSDATE.

 
I then ran the Refresh group (still logged in as CAL) exec dbms_refresh.refresh('CAL_UPDATE') but it appears to hang and it creates no session on the main oracle server.

Any more ideas?

Regards,
Col
0
 
sdstuberCommented:
what is the wait event for the hung session?
0
 
Col_GAuthor Commented:
Provided my own work around to the problem.
Root cause of the initials issue was never found.
0
All Courses

From novice to tech pro — start learning today.