Hello,
everyone, I have some troubles with dblinks these days. on my laptop, I have 2 sample database ORCL and TEST. both Enterprise edition 11.2.0.1. OS windows 10 (as far as I know this is not supported but I don't see any reason for error that I got).
simply, I created a db link from ORCL to TEST db using CURRENT_USER option but when I try to run a statement I got invalid username password error. My OS username is "musta" and my hostname is Mustylatop
both databases has a user called MUSTAFA and password is also "mustafa". on both databases mustafa user has granted DBA role.
here is my tnsnames entries:
TEST1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MustyLaptop)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = test)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mustylaptop)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ORCL)
)
)
here is my listener status (this is done via powershell not cmd, with cmd I got: TNS-01190: The user is not authorized to execute the requested listener command error):
PS C:\WINDOWS\system32> lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 25-JAN-2018 20:38:39
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MustyLaptop)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 25-JAN-2018 20:37:45
Uptime 0 days 0 hr. 0 min. 57 sec
Trace Level admin
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\musta\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\musta\diag\tnslsnr\MustyLaptop\listener\alert\log.xml
Listener Trace File c:\app\musta\diag\tnslsnr\MustyLaptop\listener\trace\ora_9204_2456.trc
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MustyLaptop)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
my databases are known by Listener.
here are my connections:
TEST1 DB:
C:\Users\musta>sqlplus mustafa/mustafa@test1
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 25 20:49:09 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select name from v$database;
NAME
---------
TEST
so I can connect to TEST database via listener from cmd.
ORCL:
C:\Users\musta>sqlplus mustafa/mustafa@orcl1
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 25 20:59:42 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select name from v$database;
NAME
---------
ORCL
SQL>
I can connect to ORCL db via Listener either.
as also you can see both DB has MUSTAFA user with password "mustafa".
now, if I create a fixed user db link on ORCL, it is working!
C:\Users\musta>sqlplus mustafa/mustafa@orcl1
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 25 20:59:42 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select name from v$database;
NAME
---------
ORCL
SQL> select user from dual;
USER
------------------------------
MUSTAFA
SQL> create database link testlink connect to mustafa identified by mustafa using 'TEST1';
Database link created.
SQL> select name from v$database@testlink;
NAME
---------
TEST
SQL>
so I am on ORCL db(with username MUSTAFA) and I used mustafa/mustafa as credentials in dblink and db link is working. I was able to query v$database from TEST db.
now I am creating same dblink using CURRENT_USER option (I am continuing with same session above):
SQL> drop database link testlink;
Database link dropped.
SQL> create database link testlink connect to current_user using 'TEST1';
Database link created.
SQL> select name from v$database@testlink;
select name from v$database@testlink
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
SQL>
now I am confused here, I can connect to TEST db with username password "mustafa/mustafa" (all lowercase), also I can connect with fixed user database link but I can not connect with current_user option!
I checked listener.log, when I tried to connect with FIXED USER db link (which is working) her is log records:
Thu Jan 25 20:39:03 2018
25-JAN-2018 20:39:03 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=test)(CID=(PROGRAM=c:\app\musta\product\11.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=MUSTYLAPTOP)(USER=musta))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=59840)) * establish * test * 0
25-JAN-2018 20:39:04 * service_update * test * 0
When I tried to connect with CURRENT_USER (which is not working), here is the log:
Thu Jan 25 21:12:37 2018
25-JAN-2018 21:12:37 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=test)(CID=(PROGRAM=c:\app\musta\product\11.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=MUSTYLAPTOP)(USER=musta))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=60099)) * establish * test * 0
25-JAN-2018 21:12:37 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=test)(CID=(PROGRAM=c:\app\musta\product\11.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=MUSTYLAPTOP)(USER=musta))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=60100)) * establish * test * 0
I don't see any difference beside PORT which is normal.
also I checked DBA_AUDIT_TRAIL on TEST db for connection request (whether there is a request or not) but there were no audit records. that means there is no connection request to TEST db, then how can I get "invalid username/password" error?
my sqlnet.ora file:
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
what could be the reason? what can I check further?
thanks.