Docker / Oracle Database / Volume Persistence / Create Table space

Question

I am building a Dev Docker environment and I have to set up an Oracle 19c database.

I have been successful... but not at 100%.

Everything is running correctly, I can create a tablespace, a user/schema, create a table, insert data, access via NodeJs the data too until I restart the container.

In all the tutorials, it is shown to mount a volume pointing to /opt/oracle/oradata

volumes:
    - ./database/OracleDB/oradata:/opt/oracle/oradata

But the tablespace are created by default in the /opt/oracle/product/19c/dbhome_1/dbs

I tried to add a volume pointing to that directory

volumes:
    - ./database/OracleDB/oradata:/opt/oracle/oradata
    - ./database/OracleDB/dbs:/opt/oracle/product/19c/dbhome_1/dbs/

But I receive the following error Error response from daemon: path /home/myusr/docker-base/database/OracleDB/dbs is mounted on / but it is not a shared mount.

Anybody has already faced this issue and found a solution?

I continue of course to search to a solution ;)

System Information

Windows 10 Professionnal with WSL2

Docker version 20.10.8, build 3967b7d

Oracle Database 19c

UPDATE 1 Based on Roberto Comments. Unfortunately, it is not working.

UPDATE 2 I tried the following

CREATE TABLESPACE tbs1_test DATAFILE '/opt/oracle/oradata/tbs1_test' SIZE 100 M AUTOEXTEND ON NEXT 100 M MAXSIZE 10 G;

and it as created the file in the desired location

I think it is telling you that this `/home/myusr/docker-base/database/OracleDB/dbs` is already mounted on `/` , therefore you can't mount it on a different volume. How did you create the tablespace ?? If you did not specify path, Oracle uses the default destination of `db_file_create_dest` . Connect to the docker and to the database and run the query `select file_name from dba_data_files order by 1;` — Roberto Hernandez, Sep 10 '21 at 07:23
Straight forward: `CREATE TABLESPACE tbs1_test DATAFILE 'tbs1_test' SIZE 100 M AUTOEXTEND ON NEXT 100 M MAXSIZE 10 G;` I know That I can add the full path to the command and add `/opt/oracle/oradata/ORCLCDB/ORCLPDB` but I am searching for a less fixed solution — Jean-Philippe M, Sep 10 '21 at 07:37
I will put an answer with steps, then we can move forward from it — Roberto Hernandez, Sep 10 '21 at 07:39

Roberto Hernandez · Accepted Answer · 2021-09-10T11:51:33.453

When you don't change the value of db_create_file_dest, Oracle will use it as default destination for datafiles. In your case, when you executed your create tablespace command, the datafile was created in the default location. That is why it does not appear on your desired directory.

1.Connect as sysdba to the database

2.Execute

SQL> alter system set db_create_file_dest = '/opt/oracle/oradata/ORCLCDB' scope=both;

3.As you have a volume already in the above directory, remove the other volume specification, as it is already shared under /

4.Remove the tablespace and create it back again ( if it is empty )

SQL> DROP TABLESPACE tbs1_test including contents and datafiles;
SQL> CREATE TABLESPACE tbs1_test DATAFILE 'tbs1_test' SIZE 100 M AUTOEXTEND ON NEXT 100 M MAXSIZE 10 G;

5.Verify that the datafile now is in the right volume

SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TBS1_TEST' ;

If you want to dig more in how to create specific volumes inside a docker image, check this post in Stackoverflow, it is one of the best IMHO

How to mount host volumes into docker containers in Dockerfile during build

You made a typo error "db_file_create_dest" should be "db_create_file_dest". And it has not worked... The file is still created in the dbhome_1/dbs directory — Jean-Philippe M, Sep 10 '21 at 08:59
sorry, you are right. it is `db_create_file_dest`. my mystake. Can you show me the value of the parameter ? and also a select over the dba_data_files ? you can edit the question and put the information there — Roberto Hernandez, Sep 10 '21 at 09:06
the value is wrong, it should contain the whole path until the datafilename. I edited the answer. — Roberto Hernandez, Sep 10 '21 at 09:38

Docker / Oracle Database / Volume Persistence / Create Table space

1 Answers1