oracle database: Unable to connect as "sys as sysdba" when oradata mapped to windows 10 volume

[tomlprog]

I have created two containers created from the same "oracle/database 12.2.0.1-se2" image. The image was built on Windows 10 OS and I am running Docker For Windows Version 17.06.0-ce-win19 (12801).

1. The first container does not have a volume mapped for the database files:

   docker run --name oracle1 -p 1521:1521 -p 5500:5500 -e ORACLE_SID=DEV -e ORACLE_PDB=TRUNK -e ORACLE_PWD=Databas3 -e ORACLE_CHARACTERSET=AL32UTF8 oracle/database:12.2.0.1-se2

and I can connect to the database running in the first container as:

E:\wfm\trunk\workspace\weblogic>docker exec -ti -u oracle oracle1 sqlplus SYS/Databas3@//localhost:1521/DEV AS SYSDBA
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 2 19:43:00 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL>

2. The second container does have a volume mapped for database files:

docker run --name oracle2 -p 1521:1521 -p 5500:5500 -e ORACLE_SID=DEV -e ORACLE_PDB=TRUNK -e ORACLE_PWD=Databas3 -e ORACLE_CHARACTERSET=AL32UTF8 -v E:\wfm\trunk\workspace\weblogic\database\oracle\oradata:/opt/oracle/oradata oracle/database:12.2.0.1-se2

and when I try to connect to the database running in the second container as I get the following error:

E:\wfm\trunk\workspace\weblogic>docker exec -ti -u oracle oracle2 sqlplus SYS/Databas3@//localhost:1521/DEV AS SYSDBA
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 2 19:19:08 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:

I also get the same error when I try to connect from my windows host:

E:\wfm\trunk\workspace\weblogic>sqlplus SYS/Databas3@//localhost:1521/DEV AS SYSDBA
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 2 19:19:08 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:

I can connect to either database locally (inside the container) using the syntax:

sqlplus / as sysdba

This isn't blocking issue for me as I can work-around this by executing sql scripts as sysdba locally inside the container, but this might be a bigger concern for others that may not be able to do the same in their environments.

[gvenzl]

Hm, this is a strange error which I cannot reproduce with the commands that you have provided:

1. Running without a Volume mapped to the container:

[root@localhost dockerfiles]# docker run --name oracle1 -p 1521:1521 -p 5500:5500 -e ORACLE_SID=DEV -e ORACLE_PDB=TRUNK -e ORACLE_PWD=Databas3 -e ORACLE_CHARACTERSET=AL32UTF8 oracle/database:12.2.0.1-se2
ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: Databas3
...
...
...
#########################
DATABASE IS READY TO USE!
#########################

Where connecting works just fine:

[root@localhost ~]# docker exec -ti -u oracle oracle1 sqlplus SYS/Databas3@//localhost:1521/DEV AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 3 22:06:29 2017

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


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

SQL> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

2. Running with a container with a volume attached to it:

[root@localhost dockerfiles]# docker run --name oracle2 -p 1521:1521 -p 5500:5500 -e ORACLE_SID=DEV -e ORACLE_PDB=TRUNK -e ORACLE_PWD=Databas3 -e ORACLE_CHARACTERSET=AL32UTF8 -v /home/oracle/oradata:/opt/oracle/oradata oracle/database:12.2.0.1-se2
ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: Databas3
...
...
#########################
DATABASE IS READY TO USE!
#########################

And also here connecting works just fine:

[root@localhost ~]# docker exec -ti -u oracle oracle2 sqlplus SYS/Databas3@//localhost:1521/DEV AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 3 22:28:38 2017

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


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

SQL> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

Are you sure that the database came up successfully in step 2 and that there weren't any typos?

[tomlprog]

I am not sure, but it looks like you ran your test with docker running on linux host instead of a windows 10 host?

I suspect this because the path to your host volume does not start with a drive letter:
/home/oracle/oradata:/opt/oracle/oradata
and mine does:
E:\wfm\trunk\workspace\weblogic\database\oracle\oradata:/opt/oracle/oradata

I have run my tests a few times already and get the same results. I have also tried a test with using the '/' char instead of '' as the directory separator in the host volume path and get the same result.

The database in step #2 come up fine and I am able to logon as another user: e.g.

C:\WINDOWS\system32>docker exec -ti -u oracle oracle2 sqlplus SYSTEM/Databas3@//localhost:1521/DEV
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 7 18:32:09 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL>

I created another container (oracle3) using the command:

docker run --name oracle3 -p 1521:1521 -p 5500:5500 -e ORACLE_SID=DEV -e ORACLE_PDB=TRUNK -e ORACLE_PWD=Databas3 -e ORACLE_CHARACTERSET=AL32UTF8 -v E:\wfm\trunk\workspace\weblogic\database\oracle\oradata3:/opt/oracle/oradata oracle/database:12.2.0.1-se2

and I have attached the log output to this ticket.

Would you be to perform these tests on a windows 10 host OS so as to be running in the same environment that I am?

-Tom

[tomlprog]

oracle3log.txt

[gvenzl]

Hi @tomlprog,

Yes, you are correct. I ran the tests on Oracle Linux 7.3, UEK4, Docker CE 17.03. Unfortunately I do not have a Windows environment available but given that it works on Linux I can only assume that it is an environment issue. On Linux I sometimes encountered problems with SELinux blocking certain commands and operations but on Windows I can unfortunately not comment on but merely suspect something similar.

Thanks for attaching the output, it looks like everything went fine inside the container.

It is very odd that only this username/password combination is failing while all the others work fine.
Do you see the same issue when connecting from outside the container, i.e. running a SQL*Plus or SQL Developer locally?

Thx,

[tomlprog]

I suspect that it has nothing to do with the SYS user because as the SYS user I can connect without the error if I do not specify any network/host connection info: e.g. docker exec -ti -u oracle oracle3 sqlplus SYS/Databas3 as sysdba
works fine,
but if i specify an @ sign followed by some connection info i do get the error: e.g.
docker exec -ti -u oracle oracle3 sqlplus SYS/Databas3@DEV as sysdba
or
docker exec -ti -u oracle oracle3 sqlplus SYS/Databas3@//localhost:1521/DEV as sysdba

I get the same results as above if I use the SYSTEM user instead of the SYS user. However, I can logon fine as SYSTEM if I do not specify "as sysdba" using any of the above connection info strings.

I get the exact same results whether I run the sqlplus inside the container or outside the container: i.e. running sqlplus (or sql developer) on windows host

Thanks,
Tom

[Philippe-Collignon]

Hello, I can confirm the exact same issue on windows 10 with docker machine in virtualbox
sqlplus SYS/welcome1Oracle@//192.168.99.100:1521/ORCLCDB as sysdba
fails with invalid username/password
sqlplus SYSTEM/welcome1Oracle@//192.168.99.100:1521/ORCLCDB
works !
and the command inside the container without the @host all works ..

It might be related to the fact that the owner of oradata on windows shared folder is not oracle user (This is the only difference I see with the linux host but I did not find a way to assign it to oracle user ) :

[oracle@e8c289b808be oracle]$ ls -al
total 80
drwxr-xr-x 18 oracle dba      4096 Aug 28 16:14 .
drwxr-xr-x  9 root   root     4096 Aug 28 15:12 ..
drwxr-x---  3 oracle oinstall 4096 Aug 28 15:12 admin
drwxr-x---  2 oracle oinstall 4096 Aug 28 15:12 audit
drwxr-x---  4 oracle oinstall 4096 Aug 28 15:16 cfgtoollogs
-rwxr-xr-x  1 oracle dba      1259 Aug 17 09:33 checkDBStatus.sh
drwxr-xr-x  2 oracle oinstall 4096 Aug 17 10:11 checkpoints
-rwxr-xr-x  1 oracle dba      2964 Aug 17 09:33 createDB.sh
-rwxr-xr-x  1 oracle dba      9406 Aug 17 09:33 dbca.rsp.tmpl
drwxrwxr-x 23 oracle oinstall 4096 Aug 28 15:12 diag
drwxrwx---  8 oracle dba      4096 Aug 17 10:12 oraInventory
drwxrwxrwx  1   1000 ftp         0 Aug 28 15:18 oradata  <==== owner is not oracle user !
drwxr-xr-x  5 oracle oinstall 4096 Aug 28 15:12 product
-rwxr-xr-x  1 oracle dba      6151 Aug 17 09:33 runOracle.sh
-rwxr-xr-x  1 oracle dba      1026 Aug 17 09:33 runUserScripts.sh
drwxr-xr-x  4 oracle dba      4096 Aug 17 10:05 scripts
-rwxr-xr-x  1 oracle dba       769 Aug 17 09:33 setPassword.sh
-rwxr-xr-x  1 oracle dba       689 Aug 17 09:33 startDB.sh

[repos-jmp]

We are facing this issue on Windows 10 too, SYS fails with invalid username/password.

[cmosguy]

I am running on windows 10 host as well and I am having exact same issue, is there a solution to this issue?

[NielsIH]

I am having the same issue. Windows 10 with a mapped volume for the database.
I can not login as sysdba.

C:\Users\niels>sqlplus sys/oracle@localhost:1521/ORCLPDB1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 1 21:25:24 2017

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

ERROR:
ORA-01017: invalid username/password; logon denied

C:\Users\niels>sqlplus system/oracle@localhost:1521/ORCLPDB1

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 1 21:27:46 2017

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

Last Successful login time: Wed Nov 01 2017 21:13:12 +01:00

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

SQL>

This is a blocking issue for me because there seems to be no way to administer the database.

[klebeer]

same problem on macOS High Sierra,

bash-4.2# ls -la
total 88

drwxrwx--- 1 oracle dba 4096 Jan 6 16:44 oraInventory
drwxr-xr-x 7 root root 224 Jan 6 18:00 oradata
drwxr-xr-x 1 oracle oinstall 4096 Jan 6 16:41 product

after a chown -Rf oracle.dba /opt/oracle/oradata/
works fine

[ByronLudwig]

I am having the same issue running docker toolbox on Windows 7. When the volume is enabled and mapped then I cannot connect to sys from outside the container. If there is no volume mapped then I can connect just fine.

I just tried and I can login with SYSTEM user but not SYS! What is the reason for this?

[babak4]

The issue seems to be caused by the fact that no pwfile is generated when we're using volumes. You can see that if you check the contents of $ORACLE_HOME/dbs.

As a workaround and until the Dockerfile is fixed, you can create the pwfile manually after you've changed the SYS password to your desired choice.

All you need to do is:
docker exec -it container_name /bin/bash
orapwd file='$ORACLE_HOME/dbs/orapw$DB_SID' asm=n format=12.2

and you're done!

[ByronLudwig]

Thanks, that does seem to be the problem, and creating the file will resolve it!

[Halahala1993]

I'm not sure why but when I use volumes the pwfile is generated but the setPassword.sh script doesn't work.

What solved it was essentially following what babak4 said. I removed orapwORCLCDB (default SID name) after navigating to $ORACLE_HOME/dbs execute rm orapwORCLCDB and created it using orapwd file=orapwORCLDB password=yourpassword format=12

[indi7]

It doesn't work for me. I had that file created. I removed and createad again and I have the same error:
invalid username/password.

[ksalih]

It doesn't work for me either! I can reset system password using this method, but not sys password!
I even reset the sys password, still not working.

[samuelvetsch]

As already stated in this thread, the problem comes from the rights owner of the $ORACLE_HOME/dbs/orapw$DB_SID file.

In the docker image, this file is a static link to /opt/oracle/oradata/dbconfig/orapw$DB_SID

On windows, if you map a volume, it will use underlying samba access your Windows host. The problem is that the FS rights are set to root:root through the Windows share with 755 rights. You cannot change those permissions and owner if on mapped windows volume.

Oracle seems to refuse to authenticate users when this file has wrong permissions and incorrect owner.

That explains why it works without volume mounted or in Unix envs. but not in Windows with win volume mounted.

ref.: https://stackoverflow.com/questions/46597290/cant-change-the-permissions-of-files-folders-on-a-volume-with-docker-windows

If you remove and recreate the orapw$DB_SID file, it will work only until next container restart, because you will replace the link by a local file which will have the correct permissions owner but will not be persisted.

[gvenzl]

Thanks a lot @samuelvetsch for adding the missing piece. If the permissions are 755 (-rwxr-xr-x) and the owner is root:root, how can the database then write to the data files?

[samuelvetsch]

Sounds strange, but is due to the specific mount inside the docker image:
Extract from /etc/mtab:

//10.0.75.1/C /opt/oracle/oradata cifs rw,relatime,vers=3.02,sec=ntlmsspi,cache=strict,username=svetsch,domain=EUROPE,uid=0,noforceuid,gid=0,noforcegid,addr=10.0.75.1,file_mode=0755,dir_mode=0755,iocharset=utf8,nounix,serverino,mapposix,nobrl,mfsymlinks,noperm,rsize=1048576,wsize=1048576,echo_interval=60,actimeo=1 0 0

Permission are forced as they cannot be handled by the Windows filesystem.

Too be more precise, the problem is more related to the owner of the file rather to the permissions. I tried the following with a local password file:

-rwxrwxrwx 1 oracle root     7680 Jun 27 06:16 orapwCLX
-> OK
-rwxrwxrwx 1 root   root     7680 Jun 27 06:16 orapwCLX
--> KO

I don't have a workaround so far appart from running as root. And knowing that DB files are remotely mounted, it may even rise some performance issues. This kind of mount shouldn't probably used on Windows platform. We should rather use volumes from other container instead even if it is less easier to manage shared files under Windows.

[gvenzl]

Thanks @samuelvetsch for the follow up! Can you do me a favor and also show me whether the data files under oradata/${ORACLE_SID} are also owned by root on Windows?
If they are owned by root but the write flag is set for others, it would explain why the database can still write to the files. Note that the above output does not match 0755 either, but 0777
I wonder whether we could work around the issue by copying the password file back into the container rather than just linking it back. Then the container can do a chown or similar to get it back to the oracle user.
However, when the password is changed, the file on the volume is out of sync, so perhaps on graceful shutdown the container can copy the file onto the volume again. Just an idea for now, certainly not an elegant solution.

[samuelvetsch]

The files are owned by root with the 755 perm:

sh-4.2$ pwd
/opt/oracle/oradata
sh-4.2$ ls -l
total 0
drwxr-xr-x 2 root root 0 Jul  2 06:14 DOCKERDB
drwxr-xr-x 2 root root 0 Jul  2 06:10 dbconfig
drwxr-xr-x 2 root root 0 Jul  2 06:12 fast_recovery_area

The 777 abobe was just my test. The server can write to those files owned by root only because of the noperm flag of the CIFS file system (see /etc/mtab dump on my previous message).

For the workaround, I also thought of doing this kind of copy on start and stop in order to keep the password file in sync between the Win and Unix file system. But I'm not fan on this ;-) The better solution would be to ask Docker team to be able to change the mount options (uid and gid) of the share in our image in order to make the shared file visible as the oracle user.

Furthermore, the fact that Oracle files are processed through a samba share (even on localhost) warns me on performance issue that may arise. But I'm not a Oracle expert, I cannot state on this.

[gvenzl]

Ah ok, that makes sense why they can be written to.

Haha, neither am I. Just sounds very dirty :)
Yeah, we can ask Docker to fix the underlying issue. Let's see what they think. Would appreciate you jumping in on that discussion tough so that they can see that there are actual users out there asking for this, too.

Well, yeah. One of the many trade-offs of running database in a containerized environment. Either lose the data on container destruction or go via an NFS (or Samba on Windows) mount.

[samuelvetsch]

After some search, it appears that the same request has already been sent to the docker team:

docker/for-win#63

But has been closed, no luck !

[gvenzl]

Seems like there could be a way:

@greut This is a limitation of CIFS/SMB and there is little we can do about this. The uid/gid are basically global for all containers. We could potentially offer a configuration option to mount with a different uid/gid. Would that help?

[lvthillo]

The following works for me on Windows 7 using Docker-machine (and mounting to Windows). I don't know if it's also a solution for Win10 (with hyperv you don't need virtualbox probably)
It's based on this blog post.

Create a folder on windows: C:\development\docker
Go to virtualbox -> click Docker-Machine -> settings -> shared folders (I added C:\development\docker).

$ docker-machine ssh default
$ mkdir -p /home/docker/data
$ sudo mount -t vboxsf -o uid=54321,gid=54321 docker /home/docker/data
$ exit
# the above is not persisted! see later

Now I start the container:

$ docker-machine ssh default
$  docker run -d --name oracle-db -e ORACLE_PWD=xxx -v /home/docker/data/:/opt/oracle/oradata -e ORACLE_PDB=xxx -p 1521:1521 -p 5500:5500 my-custom-oracle-image:12.1.0.2-se2

on docker-machine:

ls -l /home/docker/data/
total 4
drwxrwxrwx    1 54321    54321         4096 Jul 27 09:16 ORCLCDB/
drwxrwxrwx    1 54321    54321            0 Jul 27 09:16 dbconfig/
drwxrwxrwx    1 54321    54321            0 Jul 27 09:00 fast_recovery_area/

On Windows the mount is owned by my personal windows user.

ls -l C:\development\docker\
total 4
drwxr-xr-x 1 myuser 1073742337 0 Jul 27 11:16 ORCLCDB/
drwxr-xr-x 1 myuser  1073742337 0 Jul 27 11:16 dbconfig/
drwxr-xr-x 1 myuser  1073742337 0 Jul 27 11:00 fast_recovery_area/

Now when you reboot docker machine the /home/docker/data folder will be gone (read only).
To persist this you can edit:
/mnt/sda1/var/lib/boot2docker/profile

and add those lines after the proxy settings:

mkdir -p /home/docker/data
mount -t vboxsf -o uid=54321,gid=54321 docker /home/docker/data

Just before the daemon starts your data will be mounted from windows.

[Kytech]

@lvthillo, your solution worked for me. Setting the UID and GID to oracle fixed it. I'm running on Windows 10 Home, using Virtualbox. If there's a way to mount a CIFS share to a specific UID/GID, that may pose a solution for a Hyper-V setup.

Edit
Mounting the /opt/oracle/oradata directory to a docker local volume also worked as well.

[no-response]

This issue has been automatically closed because there has been no response to our request for more information from the original author. With only the information that is currently in the issue, we don't have enough information to take action. Please reach out if you have or find the answers we need so that we can investigate further.

[datocrats-org]

The issue on ownership is still a problem with the latest oracle docker images for those using docker desktop on windows especially.

Running this within the container:

ls -l $ORACLE_BASE/oradata/dbconfig/$ORACLE_SID/orapw$ORACLE_SID
-rwxr-xr-x 1 root root

There should be some scripted way for the oracle:dba user to take ownership over the mounted volume after the container is (re)started.

Running this within the mounted volume location on windows, before or after restoring the files:

wsl-user@COMPUTERNAME:/c/docker-volumes/19.3.0-ee/oradata/dbconfig/ORCLCDB$ ls -l orapwORCLCDB
-rwxrwxrwx 1 wsl-user wsl-user 6144 May 28 04:36 orapwORCLCDB

My use case involves backing up the oradata folder directly on windows, mounting it with docker run (supported only via docker daemon volume, not native windows mount versions), and/or mounting it as a named volume with docker-compose (not supported).

[arisnegro]

I tested the docker image in windows and linux hosts and the problem persists on windows.

As a workaround while It's found a better solution, the owner of the ora files can be changed moving the files into the container:

# Before remove orapwXE, check first the file is a symbolic link
rm /opt/oracle/product/18c/dbhomeXE/dbs/orapwXE;
cp /opt/oracle/oradata/dbconfig/XE/orapwXE /opt/oracle/product/18c/dbhomeXE/dbs/orapwXE;
chown oracle:oinstall /opt/oracle/product/18c/dbhomeXE/dbs/orapwXE;

# Before remove spfileXE.ora, check first the file is a symbolic link
rm /opt/oracle/product/18c/dbhomeXE/dbs/spfileXE.ora;
cp /opt/oracle/oradata/dbconfig/XE/spfileXE.ora /opt/oracle/product/18c/dbhomeXE/dbs/spfileXE.ora;
chown oracle:oinstall /opt/oracle/product/18c/dbhomeXE/dbs/spfileXE.ora;

Doing that, I finally could login with system user.

[FangJY]

I also encountered this problem. After this month I upgrade Win11, the problem disappeared.

[shiang-luong]

I am also hitting this problem (still on windows 10). Hoping arisnegro's workaround works!