Start several container with same data VOLUME #349

Closed
konfri opened this Issue Apr 12, 2017 · 4 comments

Comments

4 participants
@konfri

konfri commented Apr 12, 2017

Hey,

I do have many database tables that are filled with many tuples - it is huge ;) As this tables won't be changed (read-only) I tried to use a data volume.

docker volume create --name oracle_data

docker run -v oracle_data:/opt/oracle/oradata --name oracle_db1 -p 1521:1521 -p 5500:5500 -e ORACLE_SID=db1 oracle/database:12.1.0.2-ee

I've created an ordinary user and imported the tables. Now I would like to start a second container reusing this tables from data volume.

docker run -v oracle_data:/opt/oracle/oradata --name oracle_db2 -p 1521:1521 -p 5500:5500 -e ORACLE_SID=db2 oracle/database:12.1.0.2-ee

I didn't find any information about my issue .. just the way around #205 (distributing a baseline within a container with no data volume).

Thank you :)

@konfri konfri changed the title from Start several container with same data volumen to Start several container with same data VOLUME Apr 12, 2017

@brunoborges

This comment has been minimized.

Show comment
Hide comment
@brunoborges

brunoborges Apr 12, 2017

Contributor

Maybe you should use an Oracle Database feature for this: DBLINK. AFAIK, you can't run two Oracle Database instances associated to the same oradata folder.

@gvenzl can you confirm?

Contributor

brunoborges commented Apr 12, 2017

Maybe you should use an Oracle Database feature for this: DBLINK. AFAIK, you can't run two Oracle Database instances associated to the same oradata folder.

@gvenzl can you confirm?

@clemSeveillac

This comment has been minimized.

Show comment
Hide comment
@clemSeveillac

clemSeveillac Apr 13, 2017

Contributor

What about having the oradata inside the image (i.e. a pre-built DB), with as many read-only tablespaces as possible? Indeed, everything that is not modified at runtime is not copied thanks to copy-on-write storage drivers; therefore only the SYSTEM etc.-like tablespaces would actually be copied and diverge from the image ones...

Contributor

clemSeveillac commented Apr 13, 2017

What about having the oradata inside the image (i.e. a pre-built DB), with as many read-only tablespaces as possible? Indeed, everything that is not modified at runtime is not copied thanks to copy-on-write storage drivers; therefore only the SYSTEM etc.-like tablespaces would actually be copied and diverge from the image ones...

@gvenzl gvenzl self-assigned this Apr 13, 2017

@gvenzl

This comment has been minimized.

Show comment
Hide comment
@gvenzl

gvenzl Apr 13, 2017

Member

As long as the data is READ-ONLY you have some ways to expose that data to multiple databases.

One way is to use TRANSPORTABLE TABLESPACES, introduced in Oracle 8i. You can find an example in this blog post: Sharing READ ONLY Tablespaces between Databases
If you have many tables that you want to share, this is probably the way to go.

Oracle also has a feature called EXTERNAL TABLE since the days of Oracle 9i. Guess what it does? Exactly, it let's you represent data inside the database which is actually externally managed and stored. Or in simple English: You can expose data in files outside the database as a table and query them! Such external tables are also by definition Read-Only given that the database can't guarantee any consistency for the data but it simply just gives you a table interface for it.

So, what you could do is have that data exported into CSV files (SQL Developer is your friend for that) and then just put those files into the data volume and create an external table on all those databases.

The whole thing would look like this:

Put your data file into the volume, in this case I just put it straight into the root folder but you may or may not want to create subdirectories accordingly:

[oracle@localhost ~]$ ls -al /home/oracle/oradata
total 28
drwxrwxrwx.  8 oracle oracle 4096 Apr 13 11:54 .
drwx------. 20 oracle oracle 4096 Apr 13 10:56 ..
drwxrwxrwx.  6 oracle dba      57 Apr 13 10:55 dbconfig
drwxr-x---.  5    500    500   48 Apr 13 11:06 fast_recovery_area
drwxr-x---.  4    500    500 4096 Mar 20 06:08 ORCLCDB
drwxr-x---.  4    500    500 4096 Mar 20 06:24 ORCLSE2
-rw-rw-r--.  1 oracle oracle   96 Apr 13 10:56 test.csv
drwxr-x---.  4    500    500 4096 Apr 13 11:08 TESTEXT
drwxrwxrwx.  2 oracle dba    4096 Mar 20 06:03 XE

Note, the test.csv file there:

[oracle@localhost ~]$ cat /home/oracle/oradata/test.csv
Gerald,Venzl,Oracle,Redwood Shores
Mark,Wheeler,Oracle,Redwood Shores
Klaus,Baker,Oracle,Berlin

And now create a DIRECTORY and the EXTERNAL TABLE in any database that you want to access the file:

sql sys/LetsDocker@//localhost:1521/ORCLPDB1 as sysdba

SQLcl: Release 4.2.0.16.175.1027 RC on Thu Apr 13 12:32:18 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> CREATE DIRECTORY DATA_DIR AS '/opt/oracle/oradata';

Directory DATA_DIR created.

SQL>
SQL> CREATE TABLE test       -- Your table name
  2    (
  3       first_name varchar2(255),   -- the column definition of the table
  4       last_name varchar2(255),
  5       company varchar2(255),
  6       location varchar2(255)
  7    )
  8    ORGANIZATION EXTERNAL        -- This tells the database that this is not a regular table but an external one. What follows are the parameters for the external table
  9      (
 10         TYPE ORACLE_LOADER      -- Which access driver should be used. ORACLE_LOADER is good for flat files but if you want to just access your dump file use ORACLE_DATAPUMP
 11         DEFAULT DIRECTORY DATA_DIR  -- This is the directory that should be used. Note "DATA_DIR" is the name we specified for the directory above
 12         ACCESS PARAMETERS           -- Access parameters follow (How are records delimited, fields terminated, missing fields handled, etc.
 13         (
 14            RECORDS DELIMITED BY NEWLINE
 15            BADFILE 'test%a_%p.bad'
 16            LOGFILE 'test%a_%p.log'
 17            FIELDS TERMINATED BY ','
 18            MISSING FIELD VALUES ARE NULL
 19               (first_name, last_name, company, location)
 20         )
 21         LOCATION ('test.csv')  -- The location of your file containing all the data
 22       )
 23      PARALLEL                  -- Enable parallel scanning of the data
 24      REJECT LIMIT UNLIMITED;   -- How many conversion errors can occur before aborting the query --> unlimited

Table TEST created.

SQL> desc test;
Name       Null? Type
---------- ----- -------------
FIRST_NAME       VARCHAR2(255)
LAST_NAME        VARCHAR2(255)
COMPANY          VARCHAR2(255)
LOCATION         VARCHAR2(255)
SQL> select first_name, location from test where last_name = 'Venzl';

FIRST_NAME  LOCATION 
-------------  ---------------
Gerald             Redwood Shores

And now do the same thing in the other database (note the different connection string):

sql sys/Yeah@//localhost:1522/SEPP as sysdba

SQLcl: Release 4.2.0.16.175.1027 RC on Thu Apr 13 12:36:23 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> CREATE DIRECTORY DATA_DIR AS '/opt/oracle/oradata';

Directory DATA_DIR created.

SQL>
SQL> CREATE TABLE test       -- Your table name
  2    (
  3       first_name varchar2(255),   -- the column definition of the table
  4       last_name varchar2(255),
  5       company varchar2(255),
  6       location varchar2(255)
  7    )
  8    ORGANIZATION EXTERNAL        -- This tells the database that this is not a regular table but an external one. What follows are the parameters for the external table
  9      (
 10         TYPE ORACLE_LOADER      -- Which access driver should be used. ORACLE_LOADER is good for flat files but if you want to just access your dump file use ORACLE_DATAPUMP
 11         DEFAULT DIRECTORY DATA_DIR  -- This is the directory that should be used. Note "DATA_DIR" is the name we specified for the directory above
 12         ACCESS PARAMETERS           -- Access parameters follow (How are records delimited, fields terminated, missing fields handled, etc.
 13         (
 14            RECORDS DELIMITED BY NEWLINE
 15            BADFILE 'test%a_%p.bad'
 16            LOGFILE 'test%a_%p.log'
 17            FIELDS TERMINATED BY ','
 18            MISSING FIELD VALUES ARE NULL
 19               (first_name, last_name, company, location)
 20         )
 21         LOCATION ('test.csv')  -- The location of your file containing all the data
 22       )
 23      PARALLEL                  -- Enable parallel scanning of the data
 24      REJECT LIMIT UNLIMITED;   -- How many conversion errors can occur before aborting the query --> unlimited

Table TEST created.

SQL> desc test;
Name       Null? Type
---------- ----- -------------
FIRST_NAME       VARCHAR2(255)
LAST_NAME        VARCHAR2(255)
COMPANY          VARCHAR2(255)
LOCATION         VARCHAR2(255)
SQL> select first_name, location from test where last_name = 'Venzl';

FIRST_NAME LOCATION
------------- -----------
Gerald            Redwood Shores

Now there are a couple of more things:

  1. I did create the directory and table as SYS. Of course creating tables as SYS is highly discouraged and you should create the table within a regular schema. But for that you will have to ensure that the schema has read access to the directory. So after you created the directory you will have to do a GRANT READ ON DIRECTORY DATA_DIR TO <your user>;
  2. If you have your data available as a Datapump export file, you can and probably should use TYPE ORACLE_DATAPUMP
  3. If you do not have an export file but a csv file and that happens to be huge, you can compress it and use a PREPROCESSOR. Both gzip and zip are within the image.
  4. I should highlight that within the container database architecture we now have APPLICATION CONTAINERS in 12.2. That allows you to store such data and even define whole schemas on an application root container level and then expose that to multiple database containers, i.e PDBs. The huge benefit there is that you do not have any trade-off in consistency, etc and of course you can also propagate data model changes across many PDBs. This is a revolutionary concept in databases that today only Oracle can offer. Check out Overview of Applications in an Application Container to learn more about this awesome and unique feature.
  5. Databases manage data within data files, i.e. you can run multiple databases within the same directory and as a matter of fact that's exactly what we are doing with our volume, they are all running against the same oradata. However, obviously it is highly recommended to separate the datafiles of different databases into dedicated locations on the file system and with the appropriate permissions. Just to avoid things like, you know, deleting the wrong database. Seems to happen quite often these days...
  6. I know this has been quite a lot of info to absorb, sorry for that. So if you need any further help or information, etc. please just reach out to me. Happy to take you through it all quickly.
  7. Of course the real source for more information should always be the documentation: External tables, Transportable tablespaces
Member

gvenzl commented Apr 13, 2017

As long as the data is READ-ONLY you have some ways to expose that data to multiple databases.

One way is to use TRANSPORTABLE TABLESPACES, introduced in Oracle 8i. You can find an example in this blog post: Sharing READ ONLY Tablespaces between Databases
If you have many tables that you want to share, this is probably the way to go.

Oracle also has a feature called EXTERNAL TABLE since the days of Oracle 9i. Guess what it does? Exactly, it let's you represent data inside the database which is actually externally managed and stored. Or in simple English: You can expose data in files outside the database as a table and query them! Such external tables are also by definition Read-Only given that the database can't guarantee any consistency for the data but it simply just gives you a table interface for it.

So, what you could do is have that data exported into CSV files (SQL Developer is your friend for that) and then just put those files into the data volume and create an external table on all those databases.

The whole thing would look like this:

Put your data file into the volume, in this case I just put it straight into the root folder but you may or may not want to create subdirectories accordingly:

[oracle@localhost ~]$ ls -al /home/oracle/oradata
total 28
drwxrwxrwx.  8 oracle oracle 4096 Apr 13 11:54 .
drwx------. 20 oracle oracle 4096 Apr 13 10:56 ..
drwxrwxrwx.  6 oracle dba      57 Apr 13 10:55 dbconfig
drwxr-x---.  5    500    500   48 Apr 13 11:06 fast_recovery_area
drwxr-x---.  4    500    500 4096 Mar 20 06:08 ORCLCDB
drwxr-x---.  4    500    500 4096 Mar 20 06:24 ORCLSE2
-rw-rw-r--.  1 oracle oracle   96 Apr 13 10:56 test.csv
drwxr-x---.  4    500    500 4096 Apr 13 11:08 TESTEXT
drwxrwxrwx.  2 oracle dba    4096 Mar 20 06:03 XE

Note, the test.csv file there:

[oracle@localhost ~]$ cat /home/oracle/oradata/test.csv
Gerald,Venzl,Oracle,Redwood Shores
Mark,Wheeler,Oracle,Redwood Shores
Klaus,Baker,Oracle,Berlin

And now create a DIRECTORY and the EXTERNAL TABLE in any database that you want to access the file:

sql sys/LetsDocker@//localhost:1521/ORCLPDB1 as sysdba

SQLcl: Release 4.2.0.16.175.1027 RC on Thu Apr 13 12:32:18 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> CREATE DIRECTORY DATA_DIR AS '/opt/oracle/oradata';

Directory DATA_DIR created.

SQL>
SQL> CREATE TABLE test       -- Your table name
  2    (
  3       first_name varchar2(255),   -- the column definition of the table
  4       last_name varchar2(255),
  5       company varchar2(255),
  6       location varchar2(255)
  7    )
  8    ORGANIZATION EXTERNAL        -- This tells the database that this is not a regular table but an external one. What follows are the parameters for the external table
  9      (
 10         TYPE ORACLE_LOADER      -- Which access driver should be used. ORACLE_LOADER is good for flat files but if you want to just access your dump file use ORACLE_DATAPUMP
 11         DEFAULT DIRECTORY DATA_DIR  -- This is the directory that should be used. Note "DATA_DIR" is the name we specified for the directory above
 12         ACCESS PARAMETERS           -- Access parameters follow (How are records delimited, fields terminated, missing fields handled, etc.
 13         (
 14            RECORDS DELIMITED BY NEWLINE
 15            BADFILE 'test%a_%p.bad'
 16            LOGFILE 'test%a_%p.log'
 17            FIELDS TERMINATED BY ','
 18            MISSING FIELD VALUES ARE NULL
 19               (first_name, last_name, company, location)
 20         )
 21         LOCATION ('test.csv')  -- The location of your file containing all the data
 22       )
 23      PARALLEL                  -- Enable parallel scanning of the data
 24      REJECT LIMIT UNLIMITED;   -- How many conversion errors can occur before aborting the query --> unlimited

Table TEST created.

SQL> desc test;
Name       Null? Type
---------- ----- -------------
FIRST_NAME       VARCHAR2(255)
LAST_NAME        VARCHAR2(255)
COMPANY          VARCHAR2(255)
LOCATION         VARCHAR2(255)
SQL> select first_name, location from test where last_name = 'Venzl';

FIRST_NAME  LOCATION 
-------------  ---------------
Gerald             Redwood Shores

And now do the same thing in the other database (note the different connection string):

sql sys/Yeah@//localhost:1522/SEPP as sysdba

SQLcl: Release 4.2.0.16.175.1027 RC on Thu Apr 13 12:36:23 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> CREATE DIRECTORY DATA_DIR AS '/opt/oracle/oradata';

Directory DATA_DIR created.

SQL>
SQL> CREATE TABLE test       -- Your table name
  2    (
  3       first_name varchar2(255),   -- the column definition of the table
  4       last_name varchar2(255),
  5       company varchar2(255),
  6       location varchar2(255)
  7    )
  8    ORGANIZATION EXTERNAL        -- This tells the database that this is not a regular table but an external one. What follows are the parameters for the external table
  9      (
 10         TYPE ORACLE_LOADER      -- Which access driver should be used. ORACLE_LOADER is good for flat files but if you want to just access your dump file use ORACLE_DATAPUMP
 11         DEFAULT DIRECTORY DATA_DIR  -- This is the directory that should be used. Note "DATA_DIR" is the name we specified for the directory above
 12         ACCESS PARAMETERS           -- Access parameters follow (How are records delimited, fields terminated, missing fields handled, etc.
 13         (
 14            RECORDS DELIMITED BY NEWLINE
 15            BADFILE 'test%a_%p.bad'
 16            LOGFILE 'test%a_%p.log'
 17            FIELDS TERMINATED BY ','
 18            MISSING FIELD VALUES ARE NULL
 19               (first_name, last_name, company, location)
 20         )
 21         LOCATION ('test.csv')  -- The location of your file containing all the data
 22       )
 23      PARALLEL                  -- Enable parallel scanning of the data
 24      REJECT LIMIT UNLIMITED;   -- How many conversion errors can occur before aborting the query --> unlimited

Table TEST created.

SQL> desc test;
Name       Null? Type
---------- ----- -------------
FIRST_NAME       VARCHAR2(255)
LAST_NAME        VARCHAR2(255)
COMPANY          VARCHAR2(255)
LOCATION         VARCHAR2(255)
SQL> select first_name, location from test where last_name = 'Venzl';

FIRST_NAME LOCATION
------------- -----------
Gerald            Redwood Shores

Now there are a couple of more things:

  1. I did create the directory and table as SYS. Of course creating tables as SYS is highly discouraged and you should create the table within a regular schema. But for that you will have to ensure that the schema has read access to the directory. So after you created the directory you will have to do a GRANT READ ON DIRECTORY DATA_DIR TO <your user>;
  2. If you have your data available as a Datapump export file, you can and probably should use TYPE ORACLE_DATAPUMP
  3. If you do not have an export file but a csv file and that happens to be huge, you can compress it and use a PREPROCESSOR. Both gzip and zip are within the image.
  4. I should highlight that within the container database architecture we now have APPLICATION CONTAINERS in 12.2. That allows you to store such data and even define whole schemas on an application root container level and then expose that to multiple database containers, i.e PDBs. The huge benefit there is that you do not have any trade-off in consistency, etc and of course you can also propagate data model changes across many PDBs. This is a revolutionary concept in databases that today only Oracle can offer. Check out Overview of Applications in an Application Container to learn more about this awesome and unique feature.
  5. Databases manage data within data files, i.e. you can run multiple databases within the same directory and as a matter of fact that's exactly what we are doing with our volume, they are all running against the same oradata. However, obviously it is highly recommended to separate the datafiles of different databases into dedicated locations on the file system and with the appropriate permissions. Just to avoid things like, you know, deleting the wrong database. Seems to happen quite often these days...
  6. I know this has been quite a lot of info to absorb, sorry for that. So if you need any further help or information, etc. please just reach out to me. Happy to take you through it all quickly.
  7. Of course the real source for more information should always be the documentation: External tables, Transportable tablespaces
@konfri

This comment has been minimized.

Show comment
Hide comment
@konfri

konfri Apr 18, 2017

Thank your for the mentioned options. As my question has been answered, I will close this :)

konfri commented Apr 18, 2017

Thank your for the mentioned options. As my question has been answered, I will close this :)

@konfri konfri closed this Apr 18, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment