We use materialized views via a database link to transfer data from several source databases to a single database, which we use as data warehouse. There are materialized view logs on the source side, and the materialized views do a fast refresh every 30 minutes. There are more than 500 tables which we replicate in this way but several of them are base data tables, which change quite seldom. All databases are Oracle 19 standard edition, versions 19.26 and higher.
From time to time but several times a day, we see in the alert log of the destination database messages like
MV <schema>.<table> was not refreshed successfully.
Number of MV refresh failures: 1.
Encountered error ORA-12504.
kkzifr3g: Encountered error ORA-12504.
or
MV <schema>.<table> was not refreshed successfully.
Number of MV refresh failures: 1.
Encountered error ORA-1017.
kkzifr3g: Encountered error ORA-1017.
Each time this occurs, it affects only a few of the materialized views and not always the same ones. 30 minutes later the same materialized view gets refreshed successfully.
The associated error-codes are strange:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
ORA-1017: Invalid username/password; logon denied
as we didn’t change the TNS configuration or the database passwords in between.
I think this might have something to do with network timeouts or network buffers being too small. But I have no idea where to look more closely and how to narrow down the problem further.