sqlplus can not connect to oracle using information in tnsnames.ora

4.5k views Asked by At

I can login by

sqlplus / as sysdba

then I start the intance using startup, I also can start PDB using

startup pluggable database pdborcl 

In the terminal, echo $ORACLE_SID gets ora12cr1

But I cannot login by using

sqlplus  /@ora12cr1 as sysdba

I got ORA-01017: invalid username/password; logon denied. Below is my tnsnames.ora:

ORA12CR1=
  (DESCRIPTION=
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA=
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.cn.oracle.com)
    )
  )

PDBORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
     (CONNECT_DATA =
       (SERVICE_NAME = pdborcl.cn.oracle.com)
     )
 )

The oracle 12cr1 is install in my local machine.

lsnrctl status gets:

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 11-SEP-2013 23:02:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                11-SEP-2013 22:40:09
Uptime                    0 days 0 hr. 22 min. 48 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost.localdomain)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl.cn.oracle.com" has 1 instance(s).
  Instance "ora12cr1", status READY, has 1 handler(s) for this service...
Service "orclXDB.cn.oracle.com" has 1 instance(s).
  Instance "ora12cr1", status READY, has 1 handler(s) for this service...
Service "pdborcl.cn.oracle.com" has 1 instance(s).
  Instance "ora12cr1", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     3 PDBORCL            READ WRITE NO

and

alter session set container = pdborcl

also works.

3

There are 3 answers

0
Ajax Zhang On

using / AS SYSDBA will work as it did in previous releases at the container database level (CDB). but It's NOT able to connect to the pluggable database (PDB) without password by sqlplus.

The simplest way to achieve this is to continue to connect using "/ as SYSDBA", but to set the specific container in your script using the ALTER SESSION SET CONTAINER command.

sqlplus / as sysdba <<EOF

ALTER SESSION SET CONTAINER = pdb1;

-- Perform actions as before...
SHOW CON_NAME;

EXIT;
EOF

you also can refer this article for more information Multitenant : Running Scripts Against Container Databases (CDBs) and Pluggable Databases (PDBs) in Oracle Database 12c Release 1 (12.1)

0
bebbo On

I am using

sqlplus sys@pdborcl as sysdba

and entering the password.

show con_name;

yields the value

CON_NAME
------------------------------
PDBORCL
1
adona9 On

Try

sqlplus /@localhost.localdomain/orcl.cn.oracle.com as sysdba

if that's the service you want to log on to (not sure if I understand your question entirely).