Docker Oracle

1、从Docker镜像仓库拉取Oracle镜像

以下仓库镜像选择一个即可-一般选择第一个

  1. docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
  2. docker pull wnameless/oracle-xe-11g

image.png
image.png

2、创建Oracle容器的数据目录

  1. [root@fcant-hadoop001 ~]# cd ..
  2. [root@fcant-hadoop001 /]# mkdir /my/oracle/data -pv
  3. mkdir: created directory ‘/my’
  4. mkdir: created directory ‘/my/oracle’
  5. mkdir: created directory ‘/my/oracle/data’

image.png

3、运行一个Oracle实例

  1. [root@fcant-hadoop001 ~]# docker images
  2. REPOSITORY                                             TAG                 IMAGE ID            CREATED             SIZE
  3. docker.io/busybox                                      latest              db8ee88ad75f        5 weeks ago         1.22 MB
  4. docker.io/mysql                                        latest              de764ad211de        5 weeks ago         443 MB
  5. docker.io/redis                                        latest              598a6f110d01        6 weeks ago         118 MB
  6. docker.io/alpine                                       latest              b7b28af77ffe        6 weeks ago         5.58 MB
  7. registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g   latest              3fa112fd3642        3 years ago         6.85 GB
  8. [root@fcant-hadoop001 ~]# docker run --name oracle -d -p 8080:8080 -p 1521:1521 -v /my/oracle/data:/u01/app/oracle 3fa112fd3642
  10. 597ad81f4f3a2a500c8e95c52d09b53a376a10556c96f235ce524dba9366241c
  11. [root@fcant-hadoop001 ~]# docker ps -a
  12. CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                   PORTS                                            NAMES
  13. 597ad81f4f3a        3fa112fd3642        "/bin/sh -c '/home..."   55 seconds ago      Up 31 seconds            0.0.0.0:1521->1521/tcp, 0.0.0.0:8080->8080/tcp   oracle
  14. 3b2fdbba0cce        alpine              "echo 'hello world'"     4 weeks ago         Exited (0) 4 weeks ago                                                    zealous_wescoff
  15. 3778b0440a6b        docker.io/mysql     "docker-entrypoint..."   5 weeks ago         Up 10 hours              0.0.0.0:3306->3306/tcp, 33060/tcp                mysql
  16. [root@fcant-hadoop001 ~]#

image.png

4、通过查看容器启动日志查看容器路径

  1. [root@fcant-hadoop001 home]# docker logs -f oracle
  2. /home/oracle/app/oracle/product/11.2.0/dbhome_2
  3. Processing Database instance "helowin": log file /home/oracle/app/oracle/product/11.2.0/dbhome_2/startup.log
  4. tail: unrecognized file system type 0x794c7630 for `/home/oracle/app/oracle/product/11.2.0/dbhome_2/startup.log'. Reverting to polling.
  5. Fixed Size            2213776 bytes
  6. Variable Size          402655344 bytes
  7. Database Buffers     1191182336 bytes
  8. Redo Buffers            7360512 bytes
  9. Database mounted.
  10. Database opened.
  11. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
  12. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  14. /home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/dbstart: Database instance "helowin" warm started.

image.png

5、系统环境变量配置Oracle容器的路径

A、编辑配置文件

  1. vim /etc/profile

B、添加Oracle容器的环境变量

  1. # set oracle environment
  2. export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
  3. export ORACLE_SID=helowin
  4. export PATH=$ORACLE_HOME/bin:$PATH

image.png

6、通过SQLPlus进入Oracle

A、进入sqlplus的步骤分析

通过sqlplus进入数据库操作的步骤：

  1. 执行**docker exec -it oracle bash**进入容器的Bash操作
  2. 此时进入的是oracle用户，没有sqlplus命令的执行权限
  3. 切换到root用户，默认密码是helowin
  4. 执行**sqlplus /nolog**
  5. 再切换到oracle用户，默认密码是oracle
  6. 执行**sqlplus /nolog**可以运行
  7. 使用sysdba身份进入数据库

    B、执行步骤

    ```sql

    在oracle用户下找不到sqlplus命令

    [oracle@dbcef03180fe /]$ sqlplus /nolog bash: sqlplus: command not found

切换到root用户-默认密码是helowin

[oracle@dbcef03180fe /]$ su - root Password:

root用户下可以执行sqlplus，此时需要退出sqlplus切换到oracle用户对数据库进行操作

[root@dbcef03180fe ~]# sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 28 08:10:22 2019

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

退出sqlplus

SQL> exit

切换到oracle用户

[root@dbcef03180fe ~]# su oracle

再次执行就可以找到sqlplus命令了

[oracle@dbcef03180fe root]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 28 08:14:36 2019

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

使用sysdba权限连接数据库

SQL> conn / as sysdba Connected. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@dbcef03180fe root]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 28 08:15:19 2019

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

SQL> 

  1. ![image.png](https://cdn.nlark.com/yuque/0/2019/png/396745/1566953447717-0a61c4c7-68ab-4ba4-bdb9-6d6e528a9332.png#crop=0&crop=0&crop=1&crop=1&height=578&id=X0nZx&name=image.png&originHeight=636&originWidth=2484&originalType=binary&ratio=1&rotation=0&showTitle=false&size=139775&status=done&style=none&title=&width=2258.1817692370464)<br />![image.png](https://cdn.nlark.com/yuque/0/2019/png/396745/1566953658174-4bfde114-e877-47bd-b640-4bab1591494a.png#crop=0&crop=0&crop=1&crop=1&height=1173&id=YuJiD&name=image.png&originHeight=1290&originWidth=2867&originalType=binary&ratio=1&rotation=0&showTitle=false&size=277152&status=done&style=none&title=&width=2606.363579872227)<br />**进入容器，切换到root用户添加软链接，否则没有权限-如果以前添加过SQLPlus的软链接，进入/usr/bin目录下删除重新添加**
  2. ```bash
  3. [root@fcant-hadoop001 ~]# docker exec -it oracle bash
  4. [oracle@597ad81f4f3a /]$ sqlplus /nolog
  5. bash: sqlplus: command not found
  6. [oracle@597ad81f4f3a /]$ su oracle
  7. Password: 
  8. [oracle@597ad81f4f3a /]$ sqlplus / as sysdba
  9. bash: sqlplus: command not found
  10. [oracle@597ad81f4f3a /]$ ln -s $ORACLE_HOME/bin/sqlplus /usr/bin
  11. ln: creating symbolic link `/usr/bin/sqlplus': Permission denied
  12. [oracle@597ad81f4f3a /]$ su root
  13. Password: 
  14. su: incorrect password
  15. [oracle@597ad81f4f3a /]$ su root
  16. Password: 
  17. [root@597ad81f4f3a /]# ln -s $ORACLE_HOME/bin/sqlplus /usr/bin
  18. [root@597ad81f4f3a /]# su - oracle
  19. [oracle@597ad81f4f3a ~]$ sqlplus /nolog
  21. SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 27 12:36:53 2019
  23. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
  25. SQL> conn / as sysdba
  26. Connected.
  27. SQL> startup
  28. ORA-01081: cannot start already-running ORACLE - shut it down first
  29. SQL>

image.png

7、修改sys和system的密码并修改密码的有效时间为无限

  1. SQL> alter user system identified by oracle;
  2. User altered.
  3. SQL> alter user sys identified by oracle;
  4. User altered.
  5. SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
  6. Profile altered.
  7. SQL>

image.png

8、查看数据库用户和权限

  1. SQL> show parameter password
  3. NAME                     TYPE     VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. remote_login_passwordfile         string     EXCLUSIVE
  6. SQL> select * from v$pwfile_users;
  8. USERNAME               SYSDB SYSOP SYSAS
  9. ------------------------------ ----- ----- -----
  10. SYS                   TRUE  TRUE  FALSE
  12. SQL>

9、为sys用户添加sysdba权限

  1. SQL> grant sysdba to sys;
  3. Grant succeeded.
  5. SQL>

image.png

10、使用Navicat连接数据库

A、配置连接的角色

image.png

B、配置连接信息

image.png

11、连接参数

IP：本机 ip
端口：1521
SID：helowin
账号：system
密码：oracle

12、常见的问题解决

A、设置Oracle数据库用户

数据库用户一般在建立数据库表空间时，创建用户和该表空间进行绑定，创建的用户进行授权后可以对该表空间进登录管理
1.Oracle创建数据库(数据库表空间)、表和用户 · 语雀

B、数据库编码问题

1.查看数据库编码

  1. SQL> select userenv('language') from dual;
  3. USERENV('LANGUAGE')
  4. ----------------------------------------------------
  5. AMERICAN_AMERICA.AL32UTF8
  7. SQL>

image.png

2.编码更改和介绍

2.Oracle字符集编码

C、bash: sqlplus: command not found

1.为root用户添加SQLPlus软链接

进入容器，切换到root用户添加软链接，否则没有权限-如果以前添加过SQLPlus的软链接，进入/usr/bin目录下删除重新添加

  1. [oracle@597ad81f4f3a /]$ sqlplus / as sysdba
  2. bash: sqlplus: command not found
  3. [oracle@597ad81f4f3a /]$ ln -s $ORACLE_HOME/bin/sqlplus /usr/bin
  4. ln: creating symbolic link `/usr/bin/sqlplus': Permission denied
  5. [oracle@597ad81f4f3a /]$ su root
  6. Password: 
  7. su: incorrect password
  8. [oracle@597ad81f4f3a /]$ su root
  9. Password: 
  10. [root@597ad81f4f3a /]# ln -s $ORACLE_HOME/bin/sqlplus /usr/bin
  11. [root@597ad81f4f3a /]# su - oracle
  12. [oracle@597ad81f4f3a ~]$ sqlplus /nolog
  14. SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 27 12:36:53 2019
  16. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
  18. SQL> conn / as sysdba
  19. Connected.
  20. SQL> startup
  21. ORA-01081: cannot start already-running ORACLE - shut it down first
  22. SQL>

image.png

2.因为权限而无法执行sqlplus

A.执行sqlplus的步骤分析

通过sqlplus进入数据库操作的步骤：

  1. 执行**docker exec -it oracle bash**进入容器的Bash操作
  2. 此时进入的是oracle用户，没有sqlplus命令的执行权限
  3. 切换到root用户，默认密码是helowin
  4. 执行**sqlplus /nolog**
  5. 再切换到oracle用户，默认密码是oracle
  6. 执行**sqlplus /nolog**可以运行
  7. 使用sysdba身份进入数据库
    B.执行步骤
    ```sql

    在oracle用户下找不到sqlplus命令

    [oracle@dbcef03180fe /]$ sqlplus /nolog bash: sqlplus: command not found

切换到root用户-默认密码是helowin

[oracle@dbcef03180fe /]$ su - root Password:

root用户下可以执行sqlplus，此时需要退出sqlplus切换到oracle用户对数据库进行操作

[root@dbcef03180fe ~]# sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 28 08:10:22 2019

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

退出sqlplus

SQL> exit

切换到oracle用户-因为数据库在oracle用户下

[root@dbcef03180fe ~]# su oracle

再次执行就可以找到sqlplus命令了

[oracle@dbcef03180fe root]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 28 08:14:36 2019

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

使用sysdba权限连接数据库

SQL> conn / as sysdba Connected. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@dbcef03180fe root]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 28 08:15:19 2019

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

SQL> ``` image.png
image.png