Oracle数据库docker镜像建立

如何建立Oracle数据库docker镜像，并完成Oracle 12c可插拔数据库用户设置

  1. 建立oracle数据库 docker image

    #下载dockerfile
    cd ~/workspace
    git clone https://github.com/oracle/docker-images.git oracle_docker_images
    cd oracle_docker_images/OracleDatabase/SingleInstance/12.2.0.1
    wget https://download.oracle.com/otn/linux/oracle12c/122010/linuxx64_12201_database.zip?AuthParam=1598917386_75533533388bb9792d96ceaa3239ef56
    cd ..
    ./buildDockerImage.sh -v 12.2.0.1 -s

     

    运行 oracle container

    sudo mkdir /opt/oracle
    sudo chmod -R a+w /opt/oracle
    docker run --name oracle12r -p 1521:1521 -p 5500:5500 -e ORACLE_PWD="password" -v /opt/oracle/oradata:/opt/oracle/oradata -d oracle/database:12.2.0.1-se2
    #参数解释：
    --name:        The name of the container (default: auto generated)
    -p:            The port mapping of the host port to the container port. Two ports are exposed: 1521 (Oracle Listener), 5500 (OEM Express)
    -e ORACLE_SID: The Oracle Database SID that should be used (default: ORCLCDB)
    -e ORACLE_PDB: The Oracle Database PDB name that should be used (default: ORCLPDB1)
    -v             The data volume to use for the database. Has to be owned by the Unix user "oracle" or set appropriately. If omitted the database will not be persisted over container recreation.

    查询数据库实例和服务：

    [oracle@80202efa5251 ~]$ lsnrctl service
    ​
    LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 07-SEP-2020 12:56:00
    ​
    Copyright (c) 1991, 2016, Oracle.  All rights reserved.
    ​
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    Services Summary...
    Service "ORCLCDB" has 1 instance(s).
      Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:19 refused:0 state:ready
             LOCAL SERVER
    Service "ORCLCDBXDB" has 1 instance(s).
      Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
        Handler(s):
          "D000" established:124 refused:0 current:0 max:1022 state:ready
             DISPATCHER <machine: 80202efa5251, pid: 88>
             (ADDRESS=(PROTOCOL=tcp)(HOST=80202efa5251)(PORT=33958))
    Service "ae373f486c0309bce053020011acccac" has 1 instance(s).
      Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:19 refused:0 state:ready
             LOCAL SERVER
    Service "orclpdb1" has 1 instance(s).
      Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:19 refused:0 state:ready
             LOCAL SERVER
    The command completed successfully
    ​

     

    容器内连接 Oracle

    1. 通过服务名连接

    sqlplus system/password@//localhost:1521/ORCLPDB1

    1. 通过操作系统认证连接（服务器本地连接）

    #设置实例名环境变量
    export ORACLE_SID=ORCLCDB
    #连接
    [oracle@80202efa5251 ~]$ sqlplus / as sysdba
    ​
    SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 7 13:05:07 2020
    ​
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    ​
    ​
    Connected to:
    Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
    ​
    SQL>
    SQL> show con_name;
    ​
    CON_NAME
       ------------------------------
    ​
       CDB$ROOT
       SQL> 
    SQL> alter session set container =orclpdb1;
    ​
    Session altered.
    ​
       SQL> show con_name;
    ​
       CON_NAME
       ------------------------------
    ​
       ORCLPDB1
       SQL> 
    ​
       SQL>  ALTER DATABASE OPEN;
        ALTER DATABASE OPEN
       *
       ERROR at line 1:
       ORA-65019: pluggable database ORCLPDB1 already open
    ​
    ​# 为PCB创建用户
       SQL> create user emc identified by password;
    ​
       User created.
    ​
       SQL>
       SQL> GRANT CONNECT, DBA, RESOURCE TO emc;
    ​
       Grant succeeded.
    ​
       SQL>

 

远程连接 container database

sqlplus emc@kwsensen.f3322.net/orclpdb1

 

 

远程连接Oracle

  1. 通过服务名连接

    sqlplus system/EMCcontainer1@//172.17.0.1:1521/orclpdb1

  2. 启动一个instant client container 连接

    #实例镜像 https://hub.docker.com/u/adam139/content/sub-52cb3e9c-f82c-4aac-a2c1-5df3c72f7a85
    docker run -ti --rm store/oracle/database-instantclient:12.2.0.1 sqlplus system/EMCcontainer1@172.17.0.1/ORCLPDB1
    [emc@centos7 ~]$ docker run -ti --rm store/oracle/database-instantclient:12.2.0.1 sqlplus system/EMCcontainer1@172.17.0.1/orclpdb1
    ​
    SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 7 13:30:02 2020
    ​
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    ​
    Last Successful login time: Mon Sep 07 2020 13:21:30 +00:00
    ​
    Connected to:
    Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
    ​
    SQL>

新建用户连接：

sqlplus emc@kwsensen.f3322.net:1521/orclpdb1

 

 

python 连接：

import cx_Oracle
​
userpwd = ". . ." # Obtain password string from a user prompt or environment variable
​
connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1", encoding="UTF-8")

 

查询Oracle实例名(sid)和服务名

SQL> select * from global_name;
​
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCLPDB1
​
SQL> select distinct sid from v$mystat;
​
       SID
----------
       619
​
SQL>  select instance from v$thread;
​
INSTANCE
--------------------------------------------------------------------------------
ORCLCDB
​
SQL> select value from v$parameter where name like '%service_name%';
​
VALUE
--------------------------------------------------------------------------------
ORCLCDB
​
SQL> 
​
设置
否