程式狂想筆記

一個攻城師奮鬥史

0%

快速用 docker 建置 oracle 環境方法

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

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

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

1
2
3
4
5
6
7
8
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 看到最後一段就可以使用了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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 上新增帳號設定需要特別設定

1
2
3
4
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

1
2
3
4
5
6
7
8
9
10
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

1
2
3
4
5
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 留一下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
`