Error in connection for foreign table "XYZ" cannot be authenticated

[friendarora]

We are facing an issue on the AWS cloud environment
Error in connection for foreign table "XYZ" cannot be authenticated

This issue is not permanent but occurs sometimes and then suddenly everything is working properly
Since it is intermittent it is difficult to diagnose and find its root cause

Can you please guide us as to how to fix this issue

[laurenz]

This is an Oracle problem, so that's where you have to look.
What is the detail message that comes with that error message??

[friendarora]

mentioned below is the issue i got the oracle server log

WARNING: too many parse errors, count=101 SQL hash=0xb8ae938c
SQL: SELECT * FROM "schema1"."Table1"
PARSE ERROR: ospid=12112, error=4063:
Additional information: hd=00007FF92134D840 phd=00007FF9211E8BC8 flg=0x28 cisid=265 sid=265 ciuid=265 uid=265 sqlid=61y07xqwax4wc
...Current username=schema1
...Application: postgres Action: 1228183
2024-11-24T14:23:51.321007-05:00
WARNING: too many parse errors, count=101 SQL hash=0x8d3a209b
SQL: SELECT * FROM "Schema2"."Table1"
PARSE ERROR: ospid=11024, error=4063:
Additional information: hd=00007FF92134E9E8 phd=00007FF9213F6D60 flg=0x28 cisid=266 sid=266 ciuid=266 uid=266 sqlid=gcxgs1k6mn84v
...Current username=Schema2
...Application: postgres Action: 1228214
2024-11-24T15:00:44.382919-05:00
Thread 1 advanced to log sequence 3410 (LGWR switch), current SCN: 12717557849771
Current log# 2 seq# 3410 mem# 0: D:\ORACLE\ORADATA\ARGUSDEV\REDO2.LOG
2024-11-24T15:50:22.524918-05:00

[friendarora]

this issue comes intermittently like once/twice a day
but for the rest of the day everything is working correctly

[laurenz]

I am not sure if that warning from the Oracle log is related.
What I wanted to know is the full error message you get in PostgreSQL. That should come with a detail message that contains the Oracle error.

[friendarora]

there are no errors "oracle error" or related to authentication failure
Do we need to enable something to get this log

[laurenz]

That depends on the client API you are using.
Normally, PostgreSQL errors are also logged in the PostgreSQL log. Can you see anything there?

[friendarora]

No oracle/authentication related error message captured in the log
Is there any other way to get this information

[laurenz]

Perhaps your client does not show the detail messages?
Can you reproduce the problem with psql?
Anyway, without the detail message that contains the Oracle error message, there is not much I can do.

[laurenz]

Do you have any new information? If not, I'll close the issue, since I don't see a way forward with the information we have.