最近要看 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
|
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
|