docker/docker-compose安装Oracle11gr2/oracle12c等各种版本数据库

docker/docker-compose安装Oracle11gr2/oracle12c等各种版本数据库

1. 推荐使用镜像gvenzl/oracle-xe，支持安装各种数据库版本，查看版本信息：https://hub.docker.com/r/gvenzl/oracle-xe
2. 当前Oracle11gr2使用镜像wnameless/oracle-xe-11g-r2(Oracle12c使用truevoly/oracle-12c或absolutapps/oracle-12c-ee) 登录用户密码sys/oracle 实例信息XE

安装前可能用到的操作

# 查询Oracle镜像
docker search oracle
# 创建持久化目录
mkdir -p ./data/oracle && chmod 777 ./data/oracle
# 监听相关操作
lsnrctl status
lsnrctl stop
lsnrctl start

sqlplus可能用到的相关操作

-- 登录
sqlplus system/oracle@//localhost:1521/orcl

-- 登录修改密码等
sqlplus /nolog
connect sys/oracle as sysdba;
alter user sys identified by newpassword;
alter user system identified by newpassword;
-- 创建用户和授权
create user xxx identified by password;
grant create session to xxx;
grant create table,unlimited tablespace to xxx;
grant select any table to xxx;
GRANT UPDATE ANY TABLE,DROP ANY TABLE,INSERT ANY TABLE TO xxx;
alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
-- 删除用户
drop user xxx cascade;

-- 查看实例
select instance_name from v$instance;
-- 查看表空间
select TABLESPACE_NAME, FILE_NAME from dba_data_files;
-- 创建表空间
create tablespace user_data logging datafile '/u01/app/oracle/oradata/xe/user_data01.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
-- 查看临时表空间
select TABLESPACE_NAME, FILE_NAME from dba_temp_files;
-- 创建临时表空间
create temporary tablespace user_temp tempfile '/u01/app/oracle/oradata/xe/user_temp01.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
-- 创建用户
create user username identified by password default tablespace user_data temporary tablespace user_temp;

-- 给用户授予权限
grant connect,resource,dba to username;

使用docker

docker run --name oracle -d -p 1521:1521 -e ORACLE_ALLOW_REMOTE=true wnameless/oracle-xe-11g-r2

使用docker-compose

version: '3'

services:
  oracle11gr2:
    image: wnameless/oracle-xe-11g-r2
    container_name: oracle11gr2
    environment:
      # 时区上海
      TZ: Asia/Shanghai
    ports:
      - "8080:8080"
      - "1521:1521"
    # volumes:
      # - ./data/oracle:/u01/app/oracle
    privileged: true
    # restart: always
    networks:
      - my_net
    deploy:
      resources:
        limits:
          cpus: 0.25
          memory: 1g
        reservations:
          # cpus: 0.2
          memory: 1g

  oracle12c:
#   image: wnameless/oracle-xe-11g-r2
    image: truevoly/oracle-12c
    container_name: oracle12c
    environment:
      # 时区上海
      TZ: Asia/Shanghai
      # IMPORT_FROM_VOLUME: true
      ORACLE_ALLOW_REMOTE: "true"
    ports:
      - "1521:1521"
      - "8080:8080"
    volumes:
      - ./data/oracle:/u01/app/oracle
    privileged: true
    # restart: always
    networks:
      - my_net
    deploy:
      resources:
        limits:
          memory: 3G
        reservations:
          cpus: 0.8
          memory: 3G

networks:
  my_net:
    driver: bridge
    ipam:
      config:
        - subnet: 172.30.0.0/16

检查是否安装成功

首次安装会有两点慢：拉取镜像和初始化数据库，最好通过volumes指定持久化目录，避免容器删除后出新初始化数据库，出现Database ready to use. Enjoy! 😉代表启动成功

登录信息

hostname: localhost
port: 1521
sid: xe
username: system/sys
password: oracle

plsql链接

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 43.***.***.37 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xe)
    )
  )

Oracle备份参考脚本

SELECT * FROM ALL_DIRECTORIES;
CREATE OR REPLACE DIRECTORY backup_dir AS '/data/oracle11g/backup_dir';/data/oracle11g/backup_dir

脚本中可将日期自行改为自动获取系统日期，然后使用linux或win系统的定时任务自动执行

@echo off
chcp 65001 > nul
setlocal enabledelayedexpansion
echo 
:getInput
set /p today=请输入今天日期，如20240403：
if "!today!"=="" (  
    echo 输入不能为空，重新输入
    goto getInput  
) 
echo 已输入日期为：%today%
echo expdp USERNAME/PASSWORD@172.16.2.40:1521/orcl SCHEMAS=db1,db2 DIRECTORY=BACKUP_DIR DUMPFILE=db1_db2_backup_%today%.dmp LOGFILE=db1_db2_backup_%today%.log
echo --------
set /p bak=确认备份请输入y，否则不备份：
if "!bak!"=="y" (  
echo %bak%
    expdp USERNAME/PASSWORD@172.16.2.40:1521/orcl SCHEMAS=db1,db2 DIRECTORY=BACKUP_DIR DUMPFILE=db1_db2_backup_%today%.dmp LOGFILE=db1_db2_backup_%today%.log
) else (
    echo 已取消备份！
)
echo 已完成
endlocal
pause