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.57k stars 5.43k forks source link

Unable to log in as sys when volume parameter specified #441

Closed ja6a closed 4 years ago

ja6a commented 7 years ago

When running an image specifying the -e ORACLE_SID and the -v (volume parameter) parameters it is not possible to log in as Sys via JDBC or SQL Developer. I can log in as System via JDBC or SQL Developer and I can log in as Sys via sqlplus using docker exec. Upon investigation I noticed the file tnsnames.ora did not look right. It appeared to conjoin the default SID with the custom SID.

In my case - if this is relevant - I am using Docker Toolbox - which uses a virtual box image. I am also using a Windows host.

Command: docker run --name myDB \ -p 1521:1521 -p 5500:5500 \ -e ORACLE_SID=mysid \ -v ~/my/myDb:/opt/oracle/oradata \ oracle/database:12.2.0.1-ee

I also noticed the password parameter -e ORACLE_PWD does not work properly for sys either - but that is a separate issue...

ja6a commented 7 years ago

Confirmed that the -v parameter is causing the issue.

gvenzl commented 7 years ago

Hi @ja6a,

Can you please elaborate more on the issue what doesn't work, error message, etc? It works fine for me:

[oracle@localhost ~]$ sudo docker run --name myDB -p 1521:1521 -p 5500:5500 -e ORACLE_SID=mysid -v ~/my/myDb:/opt/oracle/oradata oracle/database:12.2.0.1-ee
[sudo] password for oracle:
ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: fJ5f9CWukEs=1

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 05-JUL-2017 16:41:23

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

Starting /opt/oracle/product/12.2.0.1/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/76565c408f1d/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                05-JUL-2017 16:41:23
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/76565c408f1d/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully
[WARNING] [DBT-10102] The listener configuration is not selected for the database. EM DB Express URL will not be accessible.
   CAUSE: The database should be registered with a listener in order to access the EM DB Express URL.
   ACTION: Select a listener to be registered or created with the database.
Copying database files
1% complete
13% complete
25% complete
Creating and starting Oracle instance
26% complete
30% complete
31% complete
35% complete
38% complete
39% complete
41% complete
Completing Database Creation
42% complete
43% complete
44% complete
46% complete
47% complete
50% complete
Creating Pluggable Databases
55% complete
75% complete
Executing Post Configuration Actions
100% complete
Look at the log file "/opt/oracle/cfgtoollogs/dbca/mysid/mysid.log" for further details.

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 5 16:48:08 2017

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

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

SQL>
System altered.

SQL>
Pluggable database altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
Completed: alter pluggable database ORCLPDB1 open
2017-07-05T16:48:07.743592+00:00
ORCLPDB1(3):CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/opt/oracle/oradata/mysid/ORCLPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO
ORCLPDB1(3):Completed: CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/opt/oracle/oradata/mysid/ORCLPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO
ORCLPDB1(3):ALTER DATABASE DEFAULT TABLESPACE "USERS"
ORCLPDB1(3):Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
2017-07-05T16:48:08.575862+00:00
ALTER SYSTEM SET control_files='/opt/oracle/oradata/mysid/control01.ctl' SCOPE=SPFILE;
   ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE
Completed:    ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE

And logging in with SQLcl which uses JDBC and the same code as SQLDeveloper:

[oracle@localhost ~]$ sql sys/fJ5f9CWukEs=1@//localhost:1521/mysid as sysdba

SQLcl: Release 4.2.0.16.175.1027 RC on Wed Jul 05 12:53:31 2017

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

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

SQL> exit;

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost ~]$
gvenzl commented 7 years ago

Closing due to no response.

ja6a commented 7 years ago

Sorry for the lack of response. I don't have anytime to devote to this. I have had problems when the images reference volumes. I eventually settled on one without volumes.

IngbertPalm commented 7 years ago

I can confirm this. When I create a container with -v :/opt/oralce/oradata, I'm not able to connect as SYSDBA. I'm always getting a login denied message. Creating an additional container without using the -v option works. Here it's possible to connect as SYSDBA.

gvenzl commented 7 years ago

Hi @IngbertPalm, can you please elaborate a bit more on your issue and share the actual output. The path you have specified above has a typo. You wrote "oralce" while it should be "oracle", so your volume will have no impact on the image whatsoever as that path doesn't exist.

IngbertPalm commented 7 years ago

Hi @gvenzl, as you mentioned, "oralce" is just a typo in my comment.

Here the command I'm using to create the docker container: docker run --name oracletest -p 1521:1521 -p 5500:5500 -e ORACLE_SID=LODB -e ORACLE_PWD=mypwd -v D:\oradata:/opt/oracle/oradata oracle/database:12.1.0.2-se2

After the creation of the container, I can connect to the database with: docker exec oracletest sqlplus system/mypwd@//localhost:1521/LODB

However, trying to connect as SYSDBA always fails with an error message that the username/password are invalid and logon is denied. Here the command I use to do this:

docker exec oracletest sqlplus sys/mypwd@//localhost:1521/LODB as sysdba

Attached you will find the container log: oracletest.txt

Kind regards,

Ingbert

gvenzl commented 7 years ago

Hi @IngbertPalm,

I just tried to run the very same commands as you did and cannot reproduce this:

[root@localhost oracle]# docker run --name oracletest -p 1521:1521 -p 5500:5500 -e ORACLE_SID=LODB -e ORACLE_PWD=mypwd -v /home/oracle/oradata:/opt/oracle/oradata oracle/database:12.1.0.2-se2
ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: mypwd

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 26-JUL-2017 15:54:45

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

Starting /opt/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /opt/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/0f59cc782ed3/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                26-JUL-2017 15:54:45
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/0f59cc782ed3/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully
Copying database files
1% complete
2% complete
27% complete
Creating and starting Oracle instance
29% complete
32% complete
33% complete
34% complete
38% complete
42% complete
43% complete
45% complete
Completing Database Creation
48% complete
51% complete
53% complete
62% complete
64% complete
72% complete
Creating Pluggable Databases
78% complete
100% complete
Look at the log file "/opt/oracle/cfgtoollogs/dbca/LODB/LODB.log" for further details.

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 26 16:04:23 2017

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

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

SQL>
System altered.

SQL>
Pluggable database altered.

SQL> Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
Completed: alter pluggable database ORCLPDB1 open
Wed Jul 26 16:04:22 2017
CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/opt/oracle/oradata/LODB/ORCLPDB1/ORCLPDB1_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO
Completed: CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/opt/oracle/oradata/LODB/ORCLPDB1/ORCLPDB1_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO
ALTER DATABASE DEFAULT TABLESPACE "USERS"
Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
Wed Jul 26 16:04:23 2017
ALTER SYSTEM SET control_files='/opt/oracle/oradata/LODB/control01.ctl' SCOPE=SPFILE;
   ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE
Completed:    ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE
[root@localhost ~]# docker exec oracletest sqlplus sys/mypwd@//localhost:1521/LODB as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 26 16:14:44 2017

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

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

SQL>

Given that you are receiving an TNS-12535: TNS:operation timed out, can you make sure you don't have a firewall or anything alike (SELinux, etc) blocking this request?

IngbertPalm commented 7 years ago

AFAIK, a firewall can't be the issue. docker exec commands run inside the container so there shouldn't be any communication going out of the container. You've changed the outside path to /home/oracle/oradata. Are you running the test on a Non-Windows environment? I'm running my tests on a Windows 10 system.

I've also recreated a new image with your latest files. The issue is still there. Very strange.

Kind regards,

Ingbert

gvenzl commented 7 years ago

Yes, I'm running this on an OL 7.3 environment hence the location change to /home/oracle/oradata. But the volume should have little to do with the fact that you can't login to the database with username/password authentication. Are there any other security measures in place on Windows that would prevent the logon from happening. Can you test with another user than SYS, e.g. create a user and try to logon with that one.

Thx,

gvenzl commented 7 years ago

I mark this as a duplicate of #525.

gvenzl commented 4 years ago

Housekeeping, closed as duplicate #525