oracle - 自定义 Oracle XE 数据目录位置，用于跨 docker 容器重新启动检索我的表数据

Question

使用 Docker Oracle XE 映像https://hub.docker.com/r/wnameless/oracle-xe-11g/，我创建了一个docker-compose文件：

version: "3"
services:
  oracle-xe:
    image: wnameless/oracle-xe-11g:16.04
    ports:
      - "1521:1521"
      - "8084:8080"
    networks:
      - oracle
    environment:
      - ORACLE_ALLOW_REMOTE=true
      - ORACLE_DISABLE_ASYNCH_IO=true
      - ORACLE_ENABLE_XDB=true
    volumes:
      - "~/dev/docker/projects/oracle/volumes/oracle-xe/config/oratab:/etc/oratab"
      -    version: "3"
services:
  oracle-xe:
    image: wnameless/oracle-xe-11g:16.04
    ports:
      - "1521:1521"
      - "8084:8080"
    networks:
      - oracle
    environment:
      - ORACLE_ALLOW_REMOTE=true
      - ORACLE_DISABLE_ASYNCH_IO=true
      - ORACLE_ENABLE_XDB=true
    volumes:
      - "~/dev/docker/projects/oracle/volumes/oracle-xe/config/oratab:/etc/oratab"
      - "~/dev/docker/projects/oracle/volumes/oracle-xe/config/tnsnames.ora:/u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora"
      - "~/dev/docker/projects/oracle/volumes/oracle-xe/data:/u01/app/oracle/product/11.2.0/xe/dbs/data"
networks:
  oracle: "~/dev/docker/projects/oracle/volumes/oracle-xe/config/tnsnames.ora:/u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora"
      - "~/dev/docker/projects/oracle/volumes/oracle-xe/data:/u01/app/oracle/product/11.2.0/xe/dbs/data"
networks:
  oracle:

我可以使用以下命令启动容器：

docker stack deploy --compose-file docker-compose-swarm-dev.yml oracle

并使用以下命令停止它：

docker stack rm oracle

然后我可以从 Docker 主机连接：

sqlplus system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=127.0.0.1)(Port=1521))(CONNECT_DATA=(SID=XE)))

登录后，我可以使用他的授权创建表空间和用户：

create tablespace useraccounttest datafile '/u01/app/oracle/product/11.2.0/xe/dbs/data/useraccounttest.dbf' size 10M autoextend on;
create temporary tablespace useraccounttest_temp tempfile '/u01/app/oracle/product/11.2.0/xe/dbs/data/useraccounttest_temp.dbf' size 5M autoextend on;
create user useraccounttest identified by mypassword default tablespace useraccounttest temporary tablespace useraccounttest_temp;
grant resource to useraccounttest;
grant connect to useraccounttest;
grant create view to useraccounttest;
grant create session to useraccounttest;
grant create sequence to useraccounttest;
grant create table to useraccounttest;
grant unlimited tablespace to useraccounttest;
revoke unlimited tablespace from useraccounttest;

由于映射卷，我可以看到在我的主机上创建的数据文件：

$ ll ~/dev/docker/projects/oracle/volumes/oracle-xe/data/
total 12M
-rw-r----- 1 stephane  11M juil.  2 14:35 useraccounttest.dbf
-rw-r----- 1 stephane 5,1M juil.  2 14:35 useraccounttest_temp.dbf

创建用户后，我可以使用这个新用户进行连接：

$ sqlplus useraccounttest/mypassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=127.0.0.1)(Port=1521))(CONNECT_DATA=(SID=XE)))

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 2 14:57:21 2018

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> 

但是如果我现在停止容器，然后启动容器，并尝试使用上述命令连接，我会收到一条消息，指出用户未知：

ORA-01017: invalid username/password; logon denied

现在，如果我以系统用户身份登录，我尝试以下命令，感觉无法创建表空间，因为它已经存在，但同时又无法使用：

SQL> create user useraccounttest identified by mypassword default tablespace useraccounttest temporary tablespace useraccounttest_temp;
create user useraccounttest identified by mypassword default tablespace useraccounttest temporary tablespace useraccounttest_temp
*
ERROR at line 1:
ORA-00959: tablespace 'USERACCOUNTTEST' does not exist


SQL> create tablespace useraccounttest datafile '/u01/app/oracle/product/11.2.0/xe/dbs/data/useraccounttest.dbf' size 10M autoextend on;
create tablespace useraccounttest datafile '/u01/app/oracle/product/11.2.0/xe/dbs/data/useraccounttest.dbf' size 10M autoextend on
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u01/app/oracle/product/11.2.0/xe/dbs/data/useraccounttest.dbf'
ORA-27038: created file already exists
Additional information: 1


SQL> 
SQL> create temporary tablespace useraccounttest_temp tempfile '/u01/app/oracle/product/11.2.0/xe/dbs/data/useraccounttest_temp.dbf' size 5M autoextend on;
create user useraccounttest identified by mypassword default tablespace useraccounttest temporary tablespace useraccounttest_temp;create temporary tablespace useraccounttest_temp tempfile '/u01/app/oracle/product/11.2.0/xe/dbs/data/useraccounttest_temp.dbf' size 5M autoextend on
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u01/app/oracle/product/11.2.0/xe/dbs/data/useraccounttest_temp.dbf'
ORA-27038: created file already exists
Additional information: 1

标签： oracledockerdocker-composeoracle-xe