Today I faced a strange issue. Support team needed to extract some data from PeopleSoft database (184.108.40.206) via oracle ERP database (220.127.116.11). A database links already exists between the 2 databases but when SELECT query is fired, we were getting below error.
SQL>SELECT * FROM <a href="mailto:apps.PS_ORACLE_DATA_AL@PEOPLESOFT.WORLD">PS_ORACLE_DATA_AL</a>; SELECT * FROM <a href="mailto:apps.PS_ORACLE_DATA_AL@PEOPLESOFT.WORLD">PS_ORACLE_DATA_AL</a> * ERROR at line 1: ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from PEOPLESOFT.WORLD Elapsed: 00:00:01.62
and I was pretty sure that the login credential for the remote database is perfectly correct. Even I was able to connect remote DB using the same credential.
SQL>conn <a href="mailto:EMP_EXTR@Remote_DB">EMP_EXTR@Remote_DB</a> Enter password: ********* Connected. and ses.sid=(select sid from v$mystat where rownum=1) * ERROR at line 7: ORA-00942: table or view does not exist Elapsed: 00:00:00.57 SQL>show user USER is "EMP_EXTR" SQL>
I tried dropping and then recreating the DB link but still got the same error. I searched the error in Metalink got one direct HIT – Note ID 473716.1.
This asks to create the database link with ‘double quotes’ around the password (IDENTIFIED by clause). I dropped and recreated the database link as advised. This is due to the fact that user passwords are case-sensitive by default from 11gR1 onwards.
This resolved the error.
SQL>select sysdate from <a href="mailto:dual@PEOPLESOFT.WORLD">dual@PEOPLESOFT.WORLD</a>; SYSDATE --------- 25-JUL-13 Elapsed: 00:00:00.73 SQL>