3

I'm quite desperate, I just created a new Oracle Database following this steps with sys and system password "oracle". Creation finished successfully but when I try to connect remotely to this database.

Via SQLDeveloper I got an error

ORA-01017 invalid username/password denied

And works fine for user scott/tiger (manually created and granted connect permission).

Newly created instance is TEST. If I connect via sqlplus / works fine 

$ ORACLE_SID=TEST
$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 5 19:32:52 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL>

When I try to connect saying username/password doesn't connect

$ ORACLE_SID=TEST
$ sqlplus sys/oracle@TEST as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 5 19:35:30 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:

But the listener apparently knows the new instance

$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 05-JUN-2020 19:37:06

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                05-JUN-2020 18:56:57
Uptime                    0 days 0 hr. 40 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/d69dcf11a559/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=d69dcf11a559)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=d69dcf11a559)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
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...
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...
The command completed successfully
bipe15
  • 111
  • 1
  • 8

3 Answers3

2

@TEST

means: lookup in a directory (tnsnames.ora,ldap, more..) and resolve my alias TEST to hostname and database SERVICE_NAME = TEST.

Configure tnsnames and hide the connection details from the connect string

cat >> $ORACLE_HOME/network/admin/tnsnames.ora <<EOF
TEST,YOU_CAN_NAME_IT_WHATERVER_SO_NOW_YOU_HAVE_TWO_ALIASES=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TEST)))
EOF

Now you can connect

sqlplus sys/oracle@TEST as sysdba
sqlplus sys/oracle@YOU_CAN_NAME_IT_WHATERVER_SO_NOW_YOU_HAVE_TWO_ALIASES as sysdba

If you are doing ad-hoc stuff and do not want to configure an alias, you don't have to

sqlplus sys/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-hostname)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TEST))) as sysdba

Another option is to use EZ_CONNECT (host:port/SERVICE_NAME)

sqlplus sys/oracle@localhost:1521/TEST as sysdba

Or make use of TWO_TASK env variable: 

export TWO_TASK=localhost:1521/TEST
sqlplus sys/oracle as sysdba

Best of luck!

Bjarte Brandt
  • 4,191
  • 2
  • 23
  • 25
1

ORA-12154 error in everyday situation would be something like this: you want to call someone on the phone, but don't know their number so you take a phonebook and search for it but - there's no number for that person.

Maybe the most frequent reason for this error is the fact that database alias (TEST in your case) can't be found in TNSNAMES.ORA file so - I'd suggest you to check that and, if that's really the case, add TEST in there and try again.

Note that - if you installed more than a single Oracle product onto the same computer - each of them has its own TNSNAMES.ORA (the phonebook, right?) file so any time you want to add a new database alias, you have to do that in each of those files. In that case, create a TNS_ADMIN environment variable which points to some directory that contains TNSNAMES.ORA file and maintain all changes only in that single file; Oracle will know where to look at.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

There were 2 errors.

  1. Missing orapwdSID
  2. TNSNAMES.ORA ( as @littlefoot and @bjarte-brandt stated)

1. Missing orapwdSID

Some of the tutorials found google miss the creation of orapwdSID, as soon I created it (following Burleson or ndesilets) I was able to connect remotely via SQLDeveloper.

orapwd FILE="$ORACLE_HOME/dbs/orapw$ORACLE_SID" PASSWORD="$ORACLE_TEMP_PASSWORD" SYS="$ORACLE_TEMP_PASSWORD"

I don't remember whether or not I restarted the database after putting the orapwdSID file.

Mind passwords must be at least 8 characters including chars, numbers and symbols. You can change it later by SQL statements

echo "alter user sys identified by ${ORACLE_SYS_PASSWORD}" | sqlplus / as sysdba
echo "alter user system identified by ${ORACLE_SYSTEM_PASSWORD}" | sqlplus / as sysdba

2. TNSNAMES.ORA

On the other hand, I found missing TNSNAMES.ORA following this post reply

$ strace sqlplus -L sys/oracle@TEST 2>&1 | grep -i 'tnsnames.ora'
access("/home/oracle/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/tnsnames.ora", F_OK)       = -1 ENOENT (No such file or directory)
access("/u01/app/oracle/product/12.2.0/SE/network/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)

As both users stated, @TEST is just a client alias. As far as I know, used by client programs to translate aliases to a way to connect to the Database.

Actually, in my case, I don't care about connecting via an alias (@TEST) since I can achieve the same by EZ_CONNECT as @bjarte-brandt stated.

sqlplus sys/oracle@localhost:1521/TEST as sysdba

Thank you both for your help.

bipe15
  • 111
  • 1
  • 8