Docker部署Oracle11g

SerMs2024-03-182024-03-18

安装部署

oracle镜像来自registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g 账户：root/helowin

拉取镜像

1
2
3

docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
或 
docker pull akaiot/oracle_11g

创建并启动容器

# 无数据卷挂载
docker run -d -p 1521:1521 --name oracle11g akaiot/oracle_11g
# 有数据卷挂载
docker run -d --name oracle11g -p 1521:1521 --privileged=true  -v /data/app/oracle/oradata:/serms/oracle/oradata akaiot/oracle_11g

进入oracle11g容器进行配置

1	docker exec -it oracle11g bash

切换到root用户下进行配置

1 2	su root 密码为：helowin

编辑profile文件配置ORACLE环境变量

1	vi /etc/profile

在最下边添加如下内容:

1
2
3

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH

让配置立刻生效

1	source /etc/profile

创建软连接

1	ln -s $ORACLE_HOME/bin/sqlplus /usr/bin

切换到oracle 用户

1	su - oracle

登录sqlplus并修改sys、system用户密码并刷新权限

sqlplus /nolog
conn /as sysdba
# 修改system用户的密码为"root"
alter user system identified by root;
# 修改sys用户的密码为"root"
alter user sys identified by root;
# 修改默认配置文件中密码有效期策略，将密码的有效期设置为永久有效（UNLIMITED）
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

退出：

exit;

查看一下oracle实例状态

lsnrctl status

[oracle@4b4795fdc52f ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 18-MAR-2024 11:16:46

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                18-MAR-2024 11:04:17
Uptime                    0 days 0 hr. 12 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/4b4795fdc52f/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=4b4795fdc52f)(PORT=1521)))
Services Summary...
Service "helowin" has 1 instance(s).
  Instance "helowin", status READY, has 1 handler(s) for this service...
Service "helowinXDB" has 1 instance(s).
  Instance "helowin", status READY, has 1 handler(s) for this service...
The command completed successfully

修改字符集编码为GBK

connect sys/root as SYSDBA;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
// 跳过超子集检测
ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; 
SHUTDOWN IMMEDIATE;
STARTUP;

集成平台Oracle配置

Oracle的部署至此已经部署完毕,下述为个人项目需要配置的内容,有需要可以做参考:

docker exec -it oracle11g bash
su - oracle
#passowrd oracle
sqlplus / as sysdba

设置Process最大进程数

1 2	alter system set processes=800 scope = spfile; alter system set processes=1600 scope = spfile;

修改账号密码修改期限

1	Alter PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

设置参数SQLNET.EXPIRE_TIME

cd $ORACLE_HOME/network/admin/
vi sqlnet.ora
SQLNET.EXPIRE_TIME=20
# 设置完后需重启服务

设置游标数

1	alter system set open_cursors=2000;

对Oracle Users表空增加dbf文件

alter tablespace USERS  add datafile '/home/oracle/app/oracle/oradata/helowin/USERS02.DBF' size 500M autoextend on next 50M maxsize unlimited ;
alter tablespace USERS  add datafile '/home/oracle/app/oracle/oradata/helowin/USERS03.DBF' size 500M autoextend on next 50M maxsize unlimited ;
alter tablespace USERS  add datafile '/home/oracle/app/oracle/oradata/helowin/USERS04.DBF' size 500M autoextend on next 50M maxsize unlimited ;
alter tablespace USERS  add datafile '/home/oracle/app/oracle/oradata/helowin/USERS05.DBF' size 500M autoextend on next 50M maxsize unlimited ;
alter tablespace USERS  add datafile '/home/oracle/app/oracle/oradata/helowin/USERS06.DBF' size 500M autoextend on next 50M maxsize unlimited ;
alter tablespace USERS  add datafile '/home/oracle/app/oracle/oradata/helowin/USERS07.DBF' size 500M autoextend on next 50M maxsize unlimited ;
alter tablespace USERS  add datafile '/home/oracle/app/oracle/oradata/helowin/USERS08.DBF' size 500M autoextend on next 50M maxsize unlimited ;
alter tablespace USERS  add datafile '/home/oracle/app/oracle/oradata/helowin/USERS09.DBF' size 500M autoextend on next 50M maxsize unlimited ;
alter tablespace USERS  add datafile '/home/oracle/app/oracle/oradata/helowin/USERS10.DBF' size 500M autoextend on next 50M maxsize unlimited ;

链接Docker中Oracle服务,执行如下命令,创建用户并授予权限

create user datamanager  identified by data  default tablespace USERS  temporary tablespace TEMP;
create user datacenter  identified by data  default tablespace USERS  temporary tablespace TEMP;

alter system set job_queue_processes=20; 
alter system set aq_tm_processes=10; 
------------------datamanager给dataceneter授权
grant dba to datamanager;
grant connect to datamanager;
grant resource to datamanager;
grant dba to datacenter;
grant connect to datacenter;
grant resource to datacenter;
alter system set job_queue_processes=20;
grant create view to datacenter;
GRANT EXECUTE ON dbms_sql  To datacenter;
GRANT SELECT ON dba_jobs_running TO datacenter;
GRANT SELECT ON dba_jobs to datacenter;
grant execute on dbms_ddl to datacenter;
grant select on dba_users to datacenter;
GRANT EXECUTE ON dbms_sql  To datacenter;
GRANT SELECT ON dba_jobs_running TO datacenter;
GRANT SELECT ON dba_jobs to datacenter;
grant create any table to datacenter;
grant select any table to datacenter;
grant select on DBA_DATA_FILES  to datacenter;
grant alter tablespace to datacenter;
GRANT CREATE MATERIALIZED VIEW TO datacenter;
--------------导入数据后执行--------------
grant select, insert, update, delete, references, alter, index on datamanager.EAPARAM to DATACENTER;
grant select on DATACENTER.v_dept to datamanager with grant option;
grant select on DATACENTER.v_empl to datamanager with grant option;
grant select on datamanager.Eauser to datacenter;
grant select on datamanager.v_eadept to datacenter with grant option;