docker部署oracle ( 服务端 )

一. 部署oracle

镜像可选：

1
2
3
# 镜像
filemon/oracle_11g
registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

docker-compose.yml

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
version: '3.0'
services:
  oracle11g:
    image: registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
           # filemon/oracle_11g
    container_name: oracle11g
    restart: unless-stopped
    ports:
      - "3022:22"
      - "1521:1521"
    volumes:
      - oracle:/var/lib/oracle
      - ./profile:/etc/profile
    environment:
      DBCA_TOTAL_MEMORY: 16192
      TZ: Asia/Shanghai
      #ORACLE_HOME: /home/oracle/app/oracle/product/11.2.0/dbhome_2  
      #ORACLE_SID: helowin 
      #PATH: $ORACLE_HOME/bin:$PATH
    networks:
      - oracle11g-network
volumes:
  oracle:
    #external: true
networks:
  oracle11g-network:
    driver: 'bridge'

profile文件:

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
# ----------------------------分割线------------------------------
# /etc/profile
# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc
# It's NOT a good idea to change this file unless you know what you
# are doing. It's much better to create a custom.sh shell script in
# /etc/profile.d/ to make custom changes to your environment, as this
# will prevent the need for merging in future updates.
pathmunge () {
    case ":${PATH}:" in
        *:"$1":*)
            ;;
        *)
            if [ "$2" = "after" ] ; then
                PATH=$PATH:$1
            else
                PATH=$1:$PATH
            fi
    esac
}

if [ -x /usr/bin/id ]; then
    if [ -z "$EUID" ]; then
        # ksh workaround
        EUID=`id -u`
        UID=`id -ru`
    fi
    USER="`id -un`"
    LOGNAME=$USER
    MAIL="/var/spool/mail/$USER"
fi
# Path manipulation
if [ "$EUID" = "0" ]; then
    pathmunge /sbin
    pathmunge /usr/sbin
    pathmunge /usr/local/sbin
else
    pathmunge /usr/local/sbin after
    pathmunge /usr/sbin after
    pathmunge /sbin after
fi
HOSTNAME=`/bin/hostname 2>/dev/null`
HISTSIZE=1000
if [ "$HISTCONTROL" = "ignorespace" ] ; then
    export HISTCONTROL=ignoreboth
else
    export HISTCONTROL=ignoredups
fi
export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL
# By default, we want umask to get set. This sets it for login shell
# Current threshold for system reserved uid/gids is 200
# You could check uidgid reservation validity in
# /usr/share/doc/setup-*/uidgid file
if [ $UID -gt 199 ] && [ "`id -gn`" = "`id -un`" ]; then
    umask 002
else
    umask 022
fi
for i in /etc/profile.d/*.sh ; do
    if [ -r "$i" ]; then
        if [ "${-#*i}" != "$-" ]; then
            . "$i"
        else
            . "$i" >/dev/null 2>&1
        fi
    fi
done
unset i
unset -f pathmunge
# oracle home目录
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2   
# oracle 服务名或者 SID名，要记住这个名字helowin，它是naivcat登录的重要选项
export ORACLE_SID=helowin     
# oracle环境变量
export PATH=$ORACLE_HOME/bin:$PATH
# ----------------------------分割线------------------------------

执行部署

1
2
# 部署
docker-compose up -d

image-20230527143935735

二. oracle初始化配置

https://www.cnblogs.com/hyq0002013/p/5623326.html

刷新环境变量: source /etc/profile

1.测试登录oracle

1
2
3
4
# 切换oracle用户su - oracle注意“-”的前后都一定要有空格 进入oracle (密码为oracle)
sqlplus / as sysdba 
# 查看oracle数据库sid
select instance_name from V$instance;

2.修改oracle默认超级管理员用户的登录密码并设置永不过期

1
2
3
4
5
# 此处的oracle就是设置的登录密码
SQL> alter user system identified by oracle; 
User altered.
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.

到这一步，linux上的docker里的oracle就安装完毕了，如果只是通过linux内访问现在已经可以登入了，下面是重点配置使oracle支持外部连接访问 (其他linux服务器需要安装oracle客户端 此处略…)

windows远程连接

image-20230526233246089

3.oracle常规操作

①.查看oracle数据库sid
select instance_name from V$instance;

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
# -----------------------------分割线------------------------------------------
# 查看Oracle数据库中的所有用户名
select * from dba_users;  查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
select * from all_users;  查看你能管理的所有用户！
select * from user_users; 查看当前用户信息 ！
# 查询你 当前用户下,有哪些表
SELECT * FROM user_tables
# 查询你 当前用户下, 可以访问哪些表 [也就是访问自己 和 其他用户的]
SELECT * FROM all_tables
# 查询当前数据库所有的表， 需要你有 DBA 的权限
SELECT * FROM dba_tables
# 使用例子：
1、创建新用户：jkw
# 查询数据库表空间
select distinct TABLESPACE_NAME from tabs;
# 查看你能管理的所有用户
select * from all_users;
# 删除用户
--drop user JKW cascade;
# 第3步：创建用户并指定表空间 
create user jkw identified by jkw123
default tablespace RMDB
--temporary tablespace rmdb_temp;
# 第4步：给用户授予权限 
grant connect,resource,dba to jkw;
# 导入数据：
imp jkw/jkw123@ip/rmdb file=C:\Users\Administrator\Desktop\mhc.dmp full=y
# -----------------------------分割线------------------------------------------

三. 若需要监控oracle则oracle数据库需要开放以下权限

1
2
3
4
5
6
7
8
9
# 登入oracle
# 切换oracle用户su - oracle注意“-”的前后都一定要有空格
sqlplus / as sysdba  # 进入oracle

1. su - oracle
2. sqlplus / as sysdba 
3. create user putong identified by oracle;         #  创建一个普通用户
4. grant create session to putong;                  #  给putong登录权限
5. grant SELECT_CATALOG_ROLE to putong;             #  给putong SELECT_CATALOG_ROLE权限

监控oracle此处需要用python脚本比较复杂 略

image-20230527153003468

结语祝好！