docker系列8: 安装oracle: [12c,11g] , 导入导出，内存设置

深碍√TFBOYSˉ_ 2022-03-26 07:22 513阅读 0赞

### 文章目录 ###

*  1, oracle12c
 *   *  oracle 12c 创建用户
 *  2, oracle11g
 *   *  a，配置nginx： 反向代理oracle服务
     *  b，oracle 语法
     *  c, oracle日志诊断
 *  3, oracle 11g 内存设置
 *  4, oracle数据导入导出：exp, imp (可连接远程主机）

# 1, oracle12c #

*  Oracle数据库Dockerfile：官网模板[https://github.com/oracle/docker-images/blob/master/OracleDatabase/SingleInstance/dockerfiles/12.2.0.1/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][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][https_github.com_oracle_docker-images_blob_master_OracleDatabase_SingleInstance_dockerfiles_11.2.0.2_Dockerfile.xe]

<table> 
 <thead> 
  <tr> 
   <th align="left">镜像</th> 
   <th align="left">用户登录</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">helowin/oracle_11g</td> 
   <td align="left">sid: helowin, system/helowin</td> 
  </tr> 
 </tbody> 
</table>

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 语法 ##

#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.

[https_github.com_oracle_docker-images_blob_master_OracleDatabase_SingleInstance_dockerfiles_12.2.0.1_Dockerfile]: https://github.com/oracle/docker-images/blob/master/OracleDatabase/SingleInstance/dockerfiles/12.2.0.1/Dockerfile
[https_hub.docker.com_u_eyeofeagle_content_sub-6e7a456b-ae49-4b7d-bedd-0dadc27ee39a]: https://hub.docker.com/u/eyeofeagle/content/sub-6e7a456b-ae49-4b7d-bedd-0dadc27ee39a
[https_github.com_oracle_docker-images_blob_master_OracleDatabase_SingleInstance_dockerfiles_11.2.0.2_Dockerfile.xe]: https://github.com/oracle/docker-images/blob/master/OracleDatabase/SingleInstance/dockerfiles/11.2.0.2/Dockerfile.xe