快速用 docker 建置 oracle 環境方法

最近要看 DB 工具是否能正常連 Oracle
簡單使用 Docker 來架設環境

componhead/docker-oracle-xe-11g: My oracle xe 11g
build 上面這版docker build . -t oracle-xe-11g

帳號密碼 system,oracle
這樣就能進去了

version: '2'
services:
  oracle:
    image: oracle-xe-11g
    restart: always   #如果docker容器由于一些问题挂掉的化，docker-composer会自动把容器给启动起来
    ports:
      - 1521:1521
      - 8080:8080

docker-compose up -d 就可以執行環境，但要等一下時間

docker-compose logs -f 看到最後一段就可以使用了

oracle_1  | ls: cannot access /u01/app/oracle/oradata: No such file or directory
oracle_1  | Database not initialized. Initializing database.
oracle_1  | Setting up:
oracle_1  | processes=500
oracle_1  | sessions=555
oracle_1  | transactions=610
oracle_1  | If you want to use different parameters set processes, sessions, transactions env variables and consider this formula:
oracle_1  | processes=x
oracle_1  | sessions=x*1.1+5
oracle_1  | transactions=sessions*1.1
oracle_1  | 
oracle_1  | Oracle Database 11g Express Edition Configuration
oracle_1  | -------------------------------------------------
oracle_1  | This will configure on-boot properties of Oracle Database 11g Express 
oracle_1  | Edition.  The following questions will determine whether the database should 
oracle_1  | be starting upon system boot, the ports it will use, and the passwords that 
oracle_1  | will be used for database accounts.  Press <Enter> to accept the defaults. 
oracle_1  | Ctrl-C will abort.
oracle_1  | 
oracle_1  | Specify the HTTP port that will be used for Oracle Application Express [8080]:
oracle_1  | Specify a port that will be used for the database listener [1521]:
oracle_1  | Specify a password to be used for database accounts.  Note that the same
oracle_1  | password will be used for SYS and SYSTEM.  Oracle recommends the use of 
oracle_1  | different passwords for each database account.  This can be done after 
oracle_1  | initial configuration:
oracle_1  | Confirm the password:
oracle_1  | 
oracle_1  | Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:
oracle_1  | Starting Oracle Net Listener...Done
oracle_1  | Configuring database...Done
oracle_1  | Starting Oracle Database 11g Express Edition instance...Done
oracle_1  | Installation completed successfully.
oracle_1  | Database initialized. Please visit http://#containeer:8080/apex to proceed with configuration
oracle_1  | Oracle Database 11g Express Edition instance is already started
oracle_1  | 
oracle_1  | Starting import from '/docker-entrypoint-initdb.d':
oracle_1  | found file /docker-entrypoint-initdb.d//docker-entrypoint-initdb.d/*
oracle_1  | [IMPORT] /entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
oracle_1  | 
oracle_1  | Import finished
oracle_1  | 
oracle_1  | Database ready to use. Enjoy! ;)

其他可參考設定

docker安装wnameless/oracle-xe-11g并运行（手写超详细） - 哈喽，小枫 - 博客园
 使用 Docker Oracle | Zoctan’s Blog

acktsw/oracle-xe-11g Dockerfile - Docker Hub
這個不知道為什麼帳號密碼無法登入，不過 ssh server 設定可以當參考

docker compose安装oracle_数据库_落叶飘零z的博客-CSDN博客

新增資料表前置作業

其實上面安裝好，不能馬上建制資料表
後來查了一下，要用新增使用者帳號去做

SID 沒有找到修改的方法

XE版本裡只能有一個instance，我將SID修改了以後貌似會報錯，所以SID就不用修改了。
BASE和HOME兩個變量後儘量不要加斜線 ‘/‘ ,有可能會報錯，雖然不知到為什麼… 同時，TNS_ADMIN這個變量一定要有。
參考Linux下安裝Oracle Database 11g Express Edition_Perserve In Learning-CSDN博客

Oracle 11帳號密碼
這邊紀錄 Dbeaver 登入
user:system
pwd: oracle

normal

user: sys
pwd: oracle
dba

這邊進去不能新增資料表
要新增帳號
dbeaver 可以新增模式比較快
新增帳號還序要設定權限

1	GRANT CONNECT, RESOURCE, DBA TO miles;

設定玩資料夾就可以建制了

這邊也有發現 Oracle 12 上新增帳號設定需要特別設定

ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;
CREATE USER miles IDENTIFIED BY 123456;
GRANT CONNECT, RESOURCE, DBA TO miles;
ALTER SESSION SET "_ORACLE_SCRIPT"=FALSE;

參考: [Docker] 使用 Docker 建立 Oracle 服務 - Miles’s Journey

相關連結

docker-entrypoint-initdb.d

for f in /docker-entrypoint-initdb.d/*; do
	echo "found file /docker-entrypoint-initdb.d/$f"
	case "$f" in
		*.sh)     echo "[IMPORT] $0: running $f"; . "$f" ;;
		*.sql)    echo "[IMPORT] $0: running $f"; echo "exit" | su oracle -c "NLS_LANG=.$CHARACTER_SET $ORACLE_HOME/bin/sqlplus -S / as sysdba @$f"; echo ;;
		*.dmp)    echo "[IMPORT] $0: running $f"; impdp $f ;;
		*)        echo "[IMPORT] $0: ignoring $f" ;;
	esac
	echo
done

由於這個關係，我可以簡單設定 db schmema create 語法

init.sql 放在 /docker-entrypoint-initdb.d/
裡面放 sql 資料夾
注意，sql 資料夾並不會執行(* 只會抓到同意層資料)

init.sql

CREATE USER XXXX IDENTIFIED BY XXXX;
GRANT CONNECT, RESOURCE, DBA TO XXXX;
CREATE USER XXXXMGR IDENTIFIED BY XXXX;
alter session set current_schema=XXXXMGR;
@/docker-entrypoint-initdb.d/sql/schema/xxxx.sql

list 資料夾所有目錄方法

find pwd -type f

就可把相關 sql 用出來

docker-compose.yml 留一下

version: '2'
services:
  oracle:
    image: oracle-xe-11g
    restart: always   #如果docker容器由于一些问题挂掉的化，docker-composer会自动把容器给启动起来
    ports:
      - 1521:1521
      - 8080:8080
      - 22:22
    volumes:
    - ./docker-entrypoint-initdb.d/:/docker-entrypoint-initdb.d/
    environment: 
      - LC_ALL=C.UTF-8 
`