My Journey to the cloud…

Today I faced a strange issue. Support team needed to extract some data from PeopleSoft database (11.2.0.1) via oracle ERP database (9.2.0.6). 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>

-Anand