docker系列8: 安装oracle: [12c,11g] , 导入导出，内存设置
继上篇 docker系列7：使用nginx 反向代理docker-mysql，安装好mysql后， 继续搭建oracle环境，流程如下：1， client —>远程windows服务器：1521—&am
·
文章目录
1, oracle12c
-
Oracle数据库Dockerfile：官网模板https://github.com/oracle/docker-images/blob/master/OracleDatabase/SingleInstance/dockerfiles/12.2.0.1/Dockerfile
-
oracle12c dockerhub官网镜像(需要注册dockerhub, 并登陆帐号)：https://hub.docker.com/u/eyeofeagle/content/sub-6e7a456b-ae49-4b7d-bedd-0dadc27ee39a
#系统要求： 8GB+ 磁盘, 2GB+ 内存
#默认用户名，密码： sys / Oradoc_db1, sid: oracle12(通过下面的参数来设置）
DB_SID: This parameter changes the ORACLE_SID of the database. The default value is set to ORCLCDB.
DB_PDB: This parameter modifies the name of the PDB. The default value is set to ORCLPDB1.
DB_MEMORY: This parameter sets the memory requirement for the Oracle server. This value determines the amount of memory to be allocated for SGA and PGA. The default value is set to 2GB.
DB_DOMAIN: This parameter sets the domain to be used for database server. The default value is localdomain
#导入本地oracle镜像，或登录账号联网下载
docker run -d --restart="always" -e DB_SID="oracle12" -e DB_MEMORY="2GB" \
-it --name oracle12 -v OracleDBData:/ORCL -p 1521:1521 -p 5500:5500 \
store/oracle/database-enterprise:12.2.0.1
##测试环境： 可使用微型版oracle12: database-enterprise:12.2.0.1-slim (disk space (4GB) , quicker startup)
oracle 12c 创建用户
create user c##test identified by "test";
#select * from user$ t where t.name like '%TEST%';
grant connect,resource to c##test;
ALTER USER c##test QUOTA UNLIMITED ON users;
#用户登录 sqlplus c##test/test
[oracle@9b231b405482 /]$ sqlplus c##test/test
SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 6 21:03:19 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Fri Nov 06 2020 21:02:34 +00:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
2, oracle11g
- Oracle数据库Dockerfile：官网模板 https://github.com/oracle/docker-images/blob/master/OracleDatabase/SingleInstance/dockerfiles/11.2.0.2/Dockerfile.xe
| 镜像 | 用户登录 |
|---|---|
| helowin/oracle_11g | sid: helowin, system/helowin |
docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
#数据持久化：
docker run -d --net=host \
--name=oracle\
-v oracle_data_oracle3:/home/oracle/app/oracle/oradata \
-v oracle_recovery_oracle3:/home/oracle/app/oracle/flash_recovery_area \
--restart=always registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
#====默认配置如下
#hostname: localhost
#port: 1521
#sid: helowin
#username: system
#password: helowin
docker exec -it oracle_11g bash
su root # 密码： helowin
#====使得环境变量生效: 可以使用sqlplus
source /home/oracle/.bash_profile
#====登录oracle数据库 ，修改密码
docker exec -it -u root oracle_11g bash
su oracle ;
source ~/.bash_profile
sqlplus
# username: system
# password: helowin
alter user system identified by oracle;
alter user sys identified by oracle;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
exit #退出oracle 命令行
exit; # 退出容器
a，配置nginx： 反向代理oracle服务
编辑nginx.conf， 配置反向代理，使得外网可以访问服务器内部的服务
# nginx 反向代理
stream {
upstream oracle{
hash $remote_addr consistent;
server 192.168.99.101:1521 max_fails=3 fail_timeout=30s;
}
server {
listen 1521;
proxy_connect_timeout 3000s;
proxy_timeout 6000s;
proxy_pass oracle;
}
}
# http { ... }
b，oracle 语法
批量生成测试数据：https://blog.csdn.net/liang_zhiqiang/article/details/5406581
#1，使用system用户登陆，创建普通用户
create user test identified by "123456" ;
grant connect,dba to test;
#2，使用普通用户登陆，创建表，插入数据
--查询数据库名， 表名
-- select name from v$database;
-- select table_name from all_tables -- where table_name like '%PER' ; --where ROWNUM <10 ;
--===================================
--创建表
-- create table per(id int, name varchar(10));
--增加数据【两种方式, insert into /insert all into ..into ..select ..】
-- insert into per(id,name) values(1 ,'a');
-- insert all into per values( 2,'b')
c, oracle日志诊断
SQL> show parameter dump
background_dump_dest string /home/oracle/app/oracle/diag/rdbms/helowin/helowin/trace
user_dump_dest string /home/oracle/app/oracle/diag/rdbms/helowin/helowin/trace
[oracle@hadoop04 /]$ ls -t /home/oracle/app/oracle/diag/rdbms/helowin/helowin/trace/helowin_ora_* -lh |head
-rw-r----- 1 oracle oinstall 4.0K May 27 15:34 /home/oracle/app/oracle/diag/rdbms/helowin/helowin/trace/helowin_ora_42510.trc
-rw-r----- 1 oracle oinstall 1.6K May 27 15:34 /home/oracle/app/oracle/diag/rdbms/helowin/helowin/trace/helowin_ora_42510.trm
-rw-r----- 1 oracle oinstall 4.1K May 25 16:58 /home/oracle/app/oracle/diag/rdbms/helowin/helowin/trace/helowin_ora_26406.trc
-rw-r----- 1 oracle oinstall 1.6K May 25 16:58 /home/oracle/app/oracle/diag/rdbms/helowin/helowin/trace/helowin_ora_26406.trm
[oracle@hadoop04 /]$ tail /home/oracle/app/oracle/diag/rdbms/helowin/helowin/trace/helowin_ora_42510.trc
Child Node: 1 ID=40 reason=Bind mismatch(22) size=4x4
bind position: 1
original oacflg: 3
original oacmxl: 2000
upgradeable new oacmxl: 128
Child Node: 0 ID=40 reason=Bind mismatch(22) size=4x4
bind position: 0
original oacflg: 3
original oacmxl: 32
upgradeable new oacmxl: 2000
[oracle@hadoop04 /]$ ls /home/oracle/app/oracle/diag/rdbms/helowin/helowin/alert/ -tlh |head
total 3.1G
-rw-r----- 1 oracle oinstall 8.3M May 28 08:33 log.xml
-rw-r----- 1 oracle oinstall 11M May 27 16:08 log_314.xml
-rw-r----- 1 oracle oinstall 11M May 27 06:27 log_313.xml
[oracle@hadoop04 /]$ tail /home/oracle/app/oracle/diag/rdbms/helowin/helowin/alert/log_314.xml
<txt>Archived Log entry 814227 added for thread 1 sequence 795492 ID 0xafcf63f9 dest 1:
</txt>
</msg>
<msg time='2021-05-27T16:08:49.201+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='hadoop04' host_addr='10.10.235.8' module=''
pid='116'>
<txt>Thread 1 cannot allocate new log, sequence 795494
</txt>
</msg>
3, oracle 11g 内存设置
# 11g 以后sga+pga整个内存可以自动管理AMM，相关参数memory_max_target, memory_target
# 设置好这两个参数后就不用关心 SGA 和 PGA了
# ( 11g 默认为 0 则初始状态下取消了 memory_target 的作用，完全和 10g 在内存管理上一致 ）
# memory_target ( 系统内存 * 70%) >= sga_target (system global area: 系统内存 * 30%)
# + pga_aggregate_target ( process global area: 系统内存 * 40% )
# 11g手动内存管理：（11g不支持memory_target参数 )
# 假如要设置 总的oracle内存8g: 分配如下
alter system set resource_limit=true;
alter profile default limit IDLE_TIME 20;
alter system set sga_max_size=2000m scope=spfile;
alter system set sga_target=1000m scope=spfile;
alter system set pga_aggregate_target=6000m scope=spfile;
#oracle启动失败，提示 sga_target > sga_max_size, 补救办法如下:
SQL> CREATE pfile='/home/oracle/pfile01.ora' FROM SPFILE;
#修改sga_max_size的值，然后指定 pfile 文件启动
SQL> startup pfile='/home/oracle/pfile01.ora'
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='/home/oracle/pfile01.ora' ;
File created.
SQL> startup;
ORACLE instance started.
Total System Global Area 2.1379E+10 bytes
Fixed Size 2213136 bytes
Variable Size 1.1811E+10 bytes
Database Buffers 9126805504 bytes
Redo Buffers 438775808 bytes
Database mounted.
Database opened.
######## 观察oracle pga 内存使用情况
SQL> set lines 256
SQL> set pages 42
SQL> SELECT * FROM V$PGASTAT;
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter 3.2212E+10 bytes
aggregate PGA auto target 2.8316E+10 bytes
global memory bound 1073741824 bytes
total PGA inuse 758904832 bytes
total PGA allocated 796636160 bytes
maximum PGA allocated 796636160 bytes
total freeable PGA memory 18350080 bytes
process count 62
max processes count 320
PGA memory freed back to OS 307429376 bytes
total PGA used for auto workareas 7316480 bytes
maximum PGA used for auto workareas 29142016 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
bytes processed 539155456 bytes
extra bytes read/written 6815744 bytes
cache hit percentage 98.75 percent
recompute count (total) 990
19 rows selected.
SQL> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,ESTD_OVERALLOC_COUNT FROM v$pga_target_advice;
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
3840 100 0
7680 100 0
15360 100 0
23040 100 0
30720 100 0
36864 100 0
43008 100 0
49152 100 0
55296 100 0
61440 100 0
92160 100 0
122880 100 0
184320 100 0
245760 100 0
14 rows selected.
SQL> select username, b.*from dba_users a, dba_profiles b where a.profile = b.profile and username='TEST' ;
USERNAME PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ ------------------------------ -------------------------------- -------- ----------------------------------------
TEST DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
TEST DEFAULT PASSWORD_LOCK_TIME PASSWORD .0208
TEST DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G
TEST DEFAULT PASSWORD_REUSE_MAX PASSWORD 3
TEST DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
TEST DEFAULT PASSWORD_LIFE_TIME PASSWORD 60
TEST DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
TEST DEFAULT PRIVATE_SGA KERNEL UNLIMITED
TEST DEFAULT CONNECT_TIME KERNEL UNLIMITED
TEST DEFAULT IDLE_TIME KERNEL 30
TEST DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
USERNAME PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ ------------------------------ -------------------------------- -------- ----------------------------------------
TEST DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
TEST DEFAULT CPU_PER_CALL KERNEL UNLIMITED
TEST DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
TEST DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
TEST DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
16 rows selected.
SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '_realfree_heap_pagesize_hint';
NAME
--------------------------------------------------------------------------------
VALUE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DESCRIPTION
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
_realfree_heap_pagesize_hint
65536
hint for real-free page size in bytes
4, oracle数据导入导出：exp, imp (可连接远程主机）
-
导出的数据中，包含有建表语句
-
exp 用户名/密码@主机:1521/实例名
################### exp导出数据: 查看帮助 exp help=y ##################
#TABLES 列出要导出的表, 无选项默认全部表和视图都导出: tables=test1,test2
[oracle@9b231b405482 /]$ exp c##test/test file=/tmp/test.dump tables=test1,test2
Export: Release 12.2.0.1.0 - Production on Fri Nov 6 20:54:10 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table TEST1 1 rows exported
. . exporting table TEST2 1 rows exported
Export terminated successfully without warnings.
[oracle@9b231b405482 /]$ cat /tmp/test.dump
EXPORT:V12.02.00
UC##TEST
...
TABLE "TEST1"
CREATE TABLE "TEST1" ("ID" NUMBER(*,0), "NAME" VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
INSERT INTO "TEST1" ("ID", "NAME") VALUES (:1, :2)
....
################## imp 导入数据: 查看帮助 imp help=y ##################
#FULL 是否导入整个文件的数据 (N): full=Y
#TABLES 列出文件中要导入的表: tables=test1,test2
[oracle@9b231b405482 /]$ imp c##test2/test file=/tmp/test.dump tables=test2
Import: Release 12.2.0.1.0 - Production on Fri Nov 6 21:07:12 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Export file created by EXPORT:V12.02.00 via conventional path
Warning: the objects were exported by C##TEST, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing C##TEST's objects into C##TEST2
. importing C##TEST's objects into C##TEST2
. . importing table "TEST2" 1 rows imported
Import terminated successfully without warnings.
权威｜前沿｜技术｜干货｜国内首个API全生命周期开发者社区
更多推荐
3
0- 0
已为社区贡献6条内容
所有评论(0)