Home » RDBMS Server » Networking and Gateways » Why I get ORA-01017 error with this database link?
icon5.gif  Why I get ORA-01017 error with this database link? [message #439137] Thu, 14 January 2010 11:13 Go to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Dear all,


I have a question about DATABASE LINK creation and I would appreciate if you could kindly give me a hand. Here is the situation:

There are two schemas: schemaA and schemaB There are two users: user1 on schemaA and user2 on schemaB

I usually work as user1 on "schemaA", but sometimes I need to have access to the user2 tables on schemaB without needing to change the connection. As a result I created the following database link on schemaA:

CREATE DATABASE LINK dblink01 CONNECT TO user2 IDENTIFIED BY password
	USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = 
		(COMMUNITY = TCP)(PROTOCOL = TCP)(HOST = 10.113.35.150)
			(PORT = 1521)))(CONNECT_DATA =(SID = db01)))';



Apparently the database is created without any error message. But whenever I want to access any of the tables via the link I get the following error:

SQL> SELECT * FROM employees@mydblink01;
SELECT * FROM employees@mydblink01
                        *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from MYDBLINK01



I don't really understand why I get this error, because I'm sure about the user ID and password. Also the TNS parameters used during the database link creation are those that I use whenever I want to connect to schemaB directly from SQL*Plus shell and it works without any problem.


Any idea?

Thanks in advance,

Kind regards,
Dariyoosh

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439139 is a reply to message #439137] Thu, 14 January 2010 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
There are two schemas: schemaA and schemaB There are two users: user1 on schemaA and user2 on schemaB

You mean databases not schemas. User and schema are synonymous in Oracle (most of the time).

Quote:
Apparently the database is created without any error message.

The creation of the database link does not verify the accessibility just the syntax (and even not the syntax inside the USING string).

Quote:
I don't really understand why I get this error, because I'm sure about the user ID and password.

I tend to trust Oracle in its error message, prove us it is wrong and use the connection parameters in the database link definition to connect using connect SQL*Plus command.

Regards
Michel

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439142 is a reply to message #439137] Thu, 14 January 2010 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>There are two schemas: schemaA and schemaB There are two users: user1 on schemaA and user2 on schemaB

In Oracle user & schema are synonymous.

Do your two users/schemas reside within the same database/instance?

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439375 is a reply to message #439142] Sat, 16 January 2010 00:10 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
 
SQL> conn raghav/raghav;
Connected.
SQL> set line 80
SQL> desc sriram
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> desc sriram_dept;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> set line 1000
SQL> select * from sriram;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> select * from sriram_dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> conn scott/bhanutej
Connected.
SQL> set line 80
SQL> desc sriram
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SRI                                       NOT NULL VARCHAR2(20)

SQL> desc sriram_dept;
ERROR:
ORA-04043: object sriram_dept does not exist


SQL> sho user
USER is "SCOTT"
SQL> CREATE DATABASE LINK dblink01 CONNECT TO raghav IDENTIFIED BY raghav USING 'satya';

Database link created.

SQL> desc sriram_dept@dblink01;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> desc sriram@dblink01;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> set line 1000
SQL> select * from sriram_dept@dblink01;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from sriram@dblink01;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> select * from sriram@mydblink01;
select * from sriram@mydblink01
                     *
ERROR at line 1:
ORA-02019: connection description for remote database not found


sriram Smile

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439380 is a reply to message #439375] Sat, 16 January 2010 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't know for OP but for myself I don't understand whar this is intended to show and in what it is related to OP's problem.
I don't even know what part is meaningless and what part is just padding.
Maybe a little explaination or comments inside the code should be welcome.

Regards
Michel

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439458 is a reply to message #439139] Sun, 17 January 2010 04:23 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Michel Cadot wrote on Thu, 14 January 2010 18:21

...
I tend to trust Oracle in its error message, prove us it is wrong and use the connection parameters in the database link definition to connect using connect SQL*Plus command.

Regards
Michel




Hello dear Michel

Thanks for your answer. As I said I already did some test directly with sqlplus in order to verify the correctness of the TNS parameters used for the USING clause during my database link creation. I can confirm that the parameters are just fine. The code in the USING clause is exactly related to the corresponding entry in my tnsnames.ora file. I can establish a connection within sqlplus shell to the specified server by the same schema = user

Yet, there is something strange that I cannot understand. I tried to create a link from serverA to serverB (schemaA is on the physical machine named serverA and schemaB on the physical machine named serverB), well it didn't work and it gave me the above mentioned ORA-01017 error message.

Later, just for see how it works, I tried to do the same thing in the reverse order, that is, to create a database link but this time from serverB to serverA. Surprisingly this time it worked pretty well.

I wonder whether this is related to some lack of privileges that causes error message when there is a link from serverA to serverB? why it works in the reverse order? I don't really understand, there is no firewall or any blocking process on non of the servers and the fact that I can create a database link shows that I have already been granted the required oracle privileges for database links.


Strange! Confused


Kind Regards,
Dariyoosh

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439459 is a reply to message #439142] Sun, 17 January 2010 04:27 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
BlackSwan wrote on Thu, 14 January 2010 18:32


...

Do your two users/schemas reside within the same database/instance?



Hello there,


Thanks for your attention to my problem. There are two oracle instances on two distinct physical machines.

As about my above mentioned schemas (= users) the schemaA resides on the oracle instance installed on the physical machine serverA and schemaB resides on the oracle instance installed on physical machine serverB.


Kind Regards,
Dariyoosh

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439460 is a reply to message #439459] Sun, 17 January 2010 04:37 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
For me dblink01 is different from mydblink01? are n`t they ?

sriram Smile

[Updated on: Sun, 17 January 2010 04:38]

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439461 is a reply to message #439458] Sun, 17 January 2010 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
As I said I already did some test directly with sqlplus in order to verify the correctness of the TNS parameters used for the USING clause during my database link creation. I can confirm that the parameters are just fine.

Did you do it on the server that owns the database link with the OS user that started the instance from the same environment that the one that is used when you started the instance?

Quote:
The code in the USING clause is exactly related to the corresponding entry in my tnsnames.ora file. I can establish a connection within sqlplus shell to the specified server by the same schema = user

Do not tell us, show us: copy and paste the SQL*Plus session using the connection string that is in the database link, do not use the tnsnames entry.

Quote:
I wonder whether this is related to some lack of privileges that causes error message when there is a link from serverA to serverB? why it works in the reverse order?

As you don't show us what you did and your environment we can't say, the only things we have is what you tell us and as you don't understand what happens you can't explain us what happens and most likely does not provide us the information that can allow to understand.

Regards
Michel

Report message to a moderator

icon10.gif  Re: Why I get ORA-01017 error with this database link? [message #439464 is a reply to message #439460] Sun, 17 January 2010 05:28 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
ramoradba wrote on Sun, 17 January 2010 11:37
For me dblink01 is different from mydblink01? are n`t they ?

sriram Smile


Oh I'm sorry, that was just a typesetting error introduced while I was creating this thread, the database link is: mydblink01

Sorry about that! Razz

Dariyoosh

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439465 is a reply to message #439461] Sun, 17 January 2010 05:33 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Michel Cadot wrote on Sun, 17 January 2010 11:38

...
As you don't show us what you did and your environment we can't say, the only things we have is what you tell us and as you don't understand what happens you can't explain us what happens and most likely does not provide us the information that can allow to understand.

Regards
Michel


Ok, as today is sunday I don't have the information stored on my PC which is in the enterprise Laughing , tomorrow I will write here all the parameters and information used to create the database link in order to illustrate clearly how I proceeded.

Thank you very much and have a nice day! Bonne dimanche!

Kind Regards,
Dariyoosh
Smile

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439466 is a reply to message #439464] Sun, 17 January 2010 05:39 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
As you got succeed in reverse method.....
show us What you tried on both normal and reverse methods.
In my previous reply i used my connect string to
create dblink ....so in the same way show us so we can help you...


sriram Smile

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439728 is a reply to message #439137] Tue, 19 January 2010 04:58 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Hello again,

As promised here are the detailed connection parameters used during the database link creation.

Just a remark, Actually for your ease of reading I had previously named my schemas as schemaA and schemaB, in fact the real names are db01 and A971DEVT. Any way, as you can see my tnsnames.ora file contains the necessary parameters for connection. 


A971DEVT.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = TCP)(PROTOCOL = TCP)
		(HOST = 10.161.129.4)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = A971DEVT)
    )
  )
  

ccsaplinux0002_db01 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = TCP)(PROTOCOL = TCP)
		(HOST = 10.113.35.150)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = db01)
    )
  )


Therefore within a sqlplus shell, in order to connect to A971DEVT I run
 $ sqlplus  user1/password@A971DEVT.WORLD


Likewise in order to connect to db01 I run
 $ sqlplus  user2/password@ccsaplinux0002_db01


In both cases the connection is established without any problem. Now here is exactly what I did in order to create the database link from A971DEVT to db01.

While I was connected to A971DEVT I run the following: 
 $ sqlplus  user1/password@A971DEVT.WORLD
 SQL> CREATE DATABASE LINK mydblink01 CONNECT TO user2 IDENTIFIED BY password
	USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = 
		(COMMUNITY = TCP)(PROTOCOL = TCP)(HOST = 10.113.35.150)
			(PORT = 1521)))(CONNECT_DATA =(SID = db01)))';


Apparently the database link, was created without error or at least, without any syntax error (as Michel remarked)

But when I want to query any table I get the above mentioned error, for example:
SELECT * FROM employees@mydblink01
                        *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from MYDBLINK01





But as I said, if I create a database link in the reverse order, that is, from db01 to A971DEVT, this works pretty well. First of all, here is what I have in tnsnames.ora file of the server where resides db01

ccsaplinux0002_db01 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = TCP)(PROTOCOL = TCP)
		(HOST = 10.113.35.150)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = db01)
    )
  )


A971DEVT.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = TCP)(PROTOCOL = TCP)
		(HOST = 10.161.129.4)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = A971DEVT)
    )
  )




So as you can see the A971DEVT.WORLD entry on ccsaplinux0002 server (which contains the schema db01)is exactly the same that I use for connecting to A971DEVT. And here is how I created the database link

sqlplus user2/password@ccsaplinux0002_db01
CREATE DATABASE LINK mydblink02 CONNECT TO user1 IDENTIFIED BY password 
	USING '(DESCRIPTION = (ADDRESS_LIST = 
		(ADDRESS = (COMMUNITY = TCP)(PROTOCOL = TCP) (HOST = 10.161.129.4)
			(PORT = 1521))) (CONNECT_DATA = (SID = A971DEVT)))';



Then I queries with success several tables of A971DEVT by using mydblink02.



So:

- creating database link from A971DEVT to db01 doesn't work (login denied)

- creating database link from db01 to A971DEVT works pretty well.



Any idea?


Kind Regards,
Dariyooh

[Updated on: Tue, 19 January 2010 05:07]

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439736 is a reply to message #439728] Tue, 19 January 2010 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Do not tell us, show us: copy and paste the SQL*Plus session using the connection string that is in the database link, do not use the tnsnames entry.

Regards
Michel

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439975 is a reply to message #439736] Wed, 20 January 2010 07:37 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Michel Cadot wrote on Tue, 19 January 2010 12:11
Quote:
Do not tell us, show us: copy and paste the SQL*Plus session using the connection string that is in the database link, do not use the tnsnames entry.

Regards
Michel



Hello dear Michel,

Thanks for your answer. Excuse me but I'm a little confused, when you say
Quote:

...
the connection string that is in the database link, do not use the tnsnames entry
...

Well, I provided the connection string in the database link
...
USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = 
		(COMMUNITY = TCP)(PROTOCOL = TCP)(HOST = 10.113.35.150)
			(PORT = 1521)))(CONNECT_DATA =(SID = db01)))';

I didn't use tns entry which is A971DEVT.WORLD, I just supplied directly as indicated above the connection parameters directly (host, SID, etc.)

Thanks again

Kind Regards,
Dariyoosh

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439978 is a reply to message #439975] Wed, 20 January 2010 07:45 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
 
SQL> select '&_connect_identifier' from dual;

execute this it will give you the connect string..create the link using that.
And show us..

sriram Smile

Report message to a moderator

Re: Why I get ORA-01017 error with this database link? [message #439979 is a reply to message #439975] Wed, 20 January 2010 07:48 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Hello dear Michel,

Thanks for your answer. Excuse me but I'm a little confused, when you say

In "connect .../...@..." or "sqlplus .../...@..." do not use tnsnames entry but the same connection string as in database link.

Regards
Michel

Report message to a moderator

Previous Topic: i m not seeing ant listener.ora
Next Topic: Dispatcher Process constantly taking 98% CPU
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 17 20:26:36 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.