search

oracle / docker-images

Official source of container configurations, images, and examples for Oracle products and projects
https://developer.oracle.com/use-cases/#containers
Universal Permissive License v1.0
6.58k stars 5.43k forks source link

ORA-12541: TNS:no listener #2343

Closed jaymku closed 2 years ago

jaymku commented 2 years ago

Getting "ORA-12541: TNS:no listener" while connecting to DB using connection string (PHP), but the same is working if connect to oracle db container from sqldeveloper.

$conn = oci_connect("sys", "Oradoc_db1", "localhost:1521/ORCLCDB");

docker-compose.yml:


  oracledb:
     image: store/oracle/database-enterprise:12.2.0.1-slim
     ports:
      - 1521:1521
     volumes:
     - ./oradata:/opt/oracle/oradata

container startup logs: oracledb_1 | Setup Oracle Database oracledb_1 | Oracle Database 12.2.0.1 Setup oracledb_1 | Mon Apr 18 06:35:51 UTC 2022 oracledb_1 | oracledb_1 | Check parameters ...... oracledb_1 | log file is : /home/oracle/setup/log/paramChk.log oracledb_1 | paramChk.sh is done at 0 sec oracledb_1 | oracledb_1 | untar DB bits ...... oracledb_1 | log file is : /home/oracle/setup/log/untarDB.log oracledb_1 | untarDB.sh is done at 19 sec oracledb_1 | oracledb_1 | config DB ...... oracledb_1 | log file is : /home/oracle/setup/log/configDB.log oracledb_1 | Mon Apr 18 06:36:10 UTC 2022 oracledb_1 | Start Docker DB configuration oracledb_1 | Call configDBora.sh to configure database oracledb_1 | Mon Apr 18 06:36:11 UTC 2022 oracledb_1 | Configure DB as oracle user oracledb_1 | Setup Database directories ... oracledb_1 | oracledb_1 | SQLPlus: Release 12.2.0.1.0 Production on Mon Apr 18 06:36:11 2022 oracledb_1 | oracledb_1 | Copyright (c) 1982, 2016, Oracle. All rights reserved. oracledb_1 | oracledb_1 | Connected to an idle instance. oracledb_1 | oracledb_1 | SQL> oracledb_1 | File created. oracledb_1 | oracledb_1 | SQL> ORACLE instance started. oracledb_1 | oracledb_1 | Total System Global Area 1342177280 bytes oracledb_1 | Fixed Size 8792536 bytes oracledb_1 | Variable Size 369100328 bytes oracledb_1 | Database Buffers 956301312 bytes oracledb_1 | Redo Buffers 7983104 bytes oracledb_1 | Database mounted. oracledb_1 | Database opened. oracledb_1 | SQL> oracledb_1 | Database altered. oracledb_1 | oracledb_1 | SQL> oracledb_1 | NAME TYPE VALUE oracledb_1 | ------------------------------------ ----------- ------------------------------ oracledb_1 | spfile string /u01/app/oracle/product/12.2.0 oracledb_1 | /dbhome_1/dbs/spfileORCLCDB.or oracledb_1 | a oracledb_1 | SQL> oracledb_1 | NAME TYPE VALUE oracledb_1 | ------------------------------------ ----------- ------------------------------ oracledb_1 | encrypt_new_tablespaces string CLOUD_ONLY oracledb_1 | SQL> oracledb_1 | User altered. oracledb_1 | oracledb_1 | SQL> oracledb_1 | User altered. oracledb_1 | oracledb_1 | SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production oracledb_1 | update password oracledb_1 | oracledb_1 | Enter password for SYS: oracledb_1 | create pdb : ORCLPDB1 oracledb_1 | oracledb_1 | SQLPlus: Release 12.2.0.1.0 Production on Mon Apr 18 06:36:49 2022 oracledb_1 | oracledb_1 | Copyright (c) 1982, 2016, Oracle. All rights reserved. oracledb_1 | oracledb_1 | oracledb_1 | Connected to: oracledb_1 | Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production oracledb_1 | oracledb_1 | SQL> 2 3 4 5
oracledb_1 | Pluggable database created. oracledb_1 | oracledb_1 | SQL> oracledb_1 | Pluggable database altered. oracledb_1 | oracledb_1 | SQL> oracledb_1 | Pluggable database altered. oracledb_1 | oracledb_1 | SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production oracledb_1 | Reset Database parameters oracledb_1 | oracledb_1 | SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 18 06:36:57 2022 oracledb_1 | oracledb_1 | Copyright (c) 1982, 2016, Oracle. All rights reserved. oracledb_1 | oracledb_1 | oracledb_1 | Connected to: oracledb_1 | Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production oracledb_1 | oracledb_1 | SQL> oracledb_1 | System altered. oracledb_1 | oracledb_1 | SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production oracledb_1 | oracledb_1 | LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 18-APR-2022 06:36:57 oracledb_1 | oracledb_1 | Copyright (c) 1991, 2016, Oracle. All rights reserved. oracledb_1 | oracledb_1 | Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait... oracledb_1 | oracledb_1 | TNSLSNR for Linux: Version 12.2.0.1.0 - Production oracledb_1 | System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/admin/ORCLCDB/listener.ora oracledb_1 | Log messages written to /u01/app/oracle/diag/tnslsnr/bf30a5ad8300/listener/alert/log.xml oracledb_1 | Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))) oracledb_1 | Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) oracledb_1 | oracledb_1 | Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))) oracledb_1 | STATUS of the LISTENER oracledb_1 | ------------------------ oracledb_1 | Alias LISTENER oracledb_1 | Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production oracledb_1 | Start Date 18-APR-2022 06:36:57 oracledb_1 | Uptime 0 days 0 hr. 0 min. 0 sec oracledb_1 | Trace Level off oracledb_1 | Security ON: Local OS Authentication oracledb_1 | SNMP OFF oracledb_1 | Listener Parameter File /u01/app/oracle/product/12.2.0/dbhome_1/admin/ORCLCDB/listener.ora oracledb_1 | Listener Log File /u01/app/oracle/diag/tnslsnr/bf30a5ad8300/listener/alert/log.xml oracledb_1 | Listening Endpoints Summary... oracledb_1 | (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))) oracledb_1 | (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) oracledb_1 | The listener supports no services oracledb_1 | The command completed successfully oracledb_1 | oracledb_1 | DONE! oracledb_1 | Remove password info oracledb_1 | Docker DB configuration is complete ! oracledb_1 | configDB.sh is done at 66 sec oracledb_1 | oracledb_1 | Done ! The database is ready for use . oracledb_1 | # ===========================================================================
oracledb_1 | # == Add below entries to your tnsnames.ora to access this database server ==
oracledb_1 | # ====================== from external host =================================
oracledb_1 | ORCLCDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)) oracledb_1 | (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLCDB.localdomain)))
oracledb_1 | ORCLPDB1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)) oracledb_1 | (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLPDB1.localdomain)))
oracledb_1 | #
oracledb_1 | #ip-address : IP address of the host where the container is running.
oracledb_1 | #port : Host Port that is mapped to the port 1521 of the container.
oracledb_1 | #
oracledb_1 | # The mapped port can be obtained from running "docker port "
oracledb_1 | # ===========================================================================
oracledb_1 | ORCLPDB1(3):Database Characterset for ORCLPDB1 is WE8DEC oracledb_1 | ORCLPDB1(3):Opatch validation is skipped for PDB ORCLPDB1 (con_id=0) oracledb_1 | 2022-04-18T06:36:56.838870+00:00 oracledb_1 | ORCLPDB1(3):Opening pdb with no Resource Manager plan active oracledb_1 | Pluggable database ORCLPDB1 opened read write oracledb_1 | Completed: alter pluggable database ORCLPDB1 open oracledb_1 | alter pluggable database all save state oracledb_1 | Completed: alter pluggable database all save state oracledb_1 | 2022-04-18T06:36:57.063437+00:00 oracledb_1 | ALTER SYSTEM SET encrypt_new_tablespaces='DDL' SCOPE=BOTH; oracledb_1 | 2022-04-18T06:37:22.495912+00:00 oracledb_1 | TABLE SYS.WRP$_REPORTS: ADDED INTERVAL PARTITION SYS_P1427 (4491) VALUES LESS THAN (TO_DATE(' 2022-04-19 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) oracledb_1 | TABLE SYS.WRP$_REPORTS_DETAILS: ADDED INTERVAL PARTITION SYS_P1428 (4491) VALUES LESS THAN (TO_DATE(' 2022-04-19 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) oracledb_1 | TABLE SYS.WRP$_REPORTS_TIME_BANDS: ADDED INTERVAL PARTITION SYS_P1431 (4490) VALUES LESS THAN (TO_DATE(' 2022-04-18 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

cjbj commented 2 years ago

Where is PHP running? Where is SQL Developer running?

jaymku commented 2 years ago

Php & oracle are running on two separate containers. While sqldveloper is installed on host machine (Mac), where I am trying to check connections with oracle db. Here is the complete docker-compose file. 

version: '2'

services:
  php7-with-oci8:
    depends_on:
      - oracledb
    build: .
    links:
     - oracledb
    volumes:
     - ./www:/var/www/html
    ports:
     - "80:80"
  oracledb:
     image: store/oracle/database-enterprise:12.2.0.1-slim
     ports:
      - 1521:1521
     volumes:
     - ./oradata:/opt/oracle/oradata

volumes:
    www:

The connection string from sqldeveloper is sys/Oradoc_db1@ORCLCDB as sysdba (localhost:1521), if the same I use for the app written in PHP, its throws an error on the webpage - "ORA-12541: TNS:no listener"

cjbj commented 2 years ago

If you use a connection string localhost:1521/ORCLCDB inside the PHP container then I expect 'localhost' resolves to the container where PHP is running, not the container where the DB is running.

Try adding a bridge, shown in Part 2: Docker for Oracle Database Applications in Node.js and Python.

Your connection string might then look like: $conn = oci_connect("sys", "Oradoc_db1", "oracledb:1521/ORCLCDB");

Googling shows answers like this that may help you set up your docker-compose file.

jaymku commented 2 years ago

Yes, the connection string "localhost:1521/ORCLCDB" was the problem here. Now it worked fine with "oracledb:1521/ORCLCDB". Thanks.

I have wrapped everything & summed up here to make things easier for people, testing oracle container for the first time.