MaksymBilenko / docker-oracle-12c

:whale: Docker image with Oracle Database 12c on board
Apache License 2.0
685 stars 288 forks source link

Cannot connect with sqlplus #57

Closed KidA001 closed 7 years ago

KidA001 commented 7 years ago

I get ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptoreverytime I try to connect via sqlplus

On my machine I run docker run --shm-size=1024MB -d -p 8080:8080 -p 1521:1521 sath89/oracle-12c

Logs show:

File Validations Successful.
Copying database files
DBCA_PROGRESS : 1%
DBCA_PROGRESS : 3%
DBCA_PROGRESS : 11%
DBCA_PROGRESS : 18%
DBCA_PROGRESS : 26%
DBCA_PROGRESS : 37%
Creating and starting Oracle instance
DBCA_PROGRESS : 40%
DBCA_PROGRESS : 45%
DBCA_PROGRESS : 50%
DBCA_PROGRESS : 55%
DBCA_PROGRESS : 56%
DBCA_PROGRESS : 60%
DBCA_PROGRESS : 62%
Completing Database Creation
DBCA_PROGRESS : 66%
DBCA_PROGRESS : 70%
DBCA_PROGRESS : 73%
DBCA_PROGRESS : 85%
DBCA_PROGRESS : 96%
DBCA_PROGRESS : 100%
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/xe.
Database Information:
Global Database Name:xe
System Identifier(SID):xe

When I log onto the docker machine through docker exec -i -t <container_id> bash I can successfully run sqlplus system/oracle@localhost:1521/xe

When I'm on my local machine and try and run that, it cannot connect: sqlplus system/oracle@//192.168.99.100:1521/xe.oracle.docker or sqlplus system/oracle@//192.168.99.100:1521/xe or sqlplus system/oracle@192.168.99.100:1521/xe all give me the same ORA-12162: TNS:net service name is incorrectly specified error. 192.168.99.100 is my docker-machine ip. Same happens if I try localhost

I can successfully connect to the web portal at http://192.168.99.100:8080/em and login

Any help appreciated

anilsamuel commented 7 years ago

I had to modify $ORACLE_HOME/network/admin/*.ora files to listen to 0.0.0.0 instead of the hostname. Does that make sense ?

KidA001 commented 7 years ago

@anilsamuel this is my tnsnames.ora

ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.100)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
   )
 )

Generally localhost/0.0.0.0 doesn't work for me, are you suggesting to change 192.168.99.100 to 0.0.0.0 ?

anilsamuel commented 7 years ago

yes, change 192.168.99.100 to 0.0.0.0.

anilsamuel commented 7 years ago

I would commit my changes once I got it working; else you would loose on docker container shutdown

KidA001 commented 7 years ago

Interesting...I started getting a different error (ORA-21561: OID generation failed) when I used: sqlplus system/oracle@//192.168.99.100:1521/xe ... note xe at the end instead of xe.oracle.docker

I looked up the ORA-21561: OID generation failed error and found this post on stackoverflow. I updated my /etc/hosts file entry for localhost from 127.0.0.1 localhost to 127.0.0.1 localhost MacBook-Pro-2.local

Now I'm able to connect with sqlplus system/oracle@//192.168.99.100:1521/xe @anilsamuel

Earthson commented 7 years ago

@KidA001 Where is the tnsnames.ora you put? I can not find a folder like network/admin. Which is the $ORACLE_HOME? mounted on /u01/app/oracle?