Skip to content

Instantly share code, notes, and snippets.

@rponte

rponte/oracle-11g-docker.sh

Last active July 2, 2024 09:03
Show Gist options
  • Save rponte/dd6810412150c86e21b8d814764c4222 to your computer and use it in GitHub Desktop.
Save rponte/dd6810412150c86e21b8d814764c4222 to your computer and use it in GitHub Desktop.
Download ZIP
Docker: Building and running Oracle Database 11gR2 Express Edition in a Docker container
Raw
oracle-11g-docker.sh
# based on
# https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance
# more infos:
# https://docs.docker.com/v17.09/edge/engine/reference/commandline/run/
# building it
cd ~/docker-images/OracleDatabase/SingleInstance/dockerfiles/11.2.0.2
docker build --force-rm=true --no-cache=true --shm-size=1G --build-arg DB_EDITION=xe -t "oracle/database:11.2.0.2-xe" -f Dockerfile.xe .
# running it
docker run --name "oracle-11g-xe" -d --shm-size=1g -p 1521:1521 -p 8080:8080 -e ORACLE_PWD=manager -v /tmp/oracle-xe/oradata:/u01/app/oracle/oradata oracle/database:11.2.0.2-xe
@rponte
Copy link
Author

rponte commented Jan 19, 2019 •
edited
Loading

Memory leaks on MacOSX and Docker 18.09.1,

@rponte
Copy link
Author

rponte commented Jan 21, 2019 •
edited
Loading

Tuning Oracle 11g XE. Execute those commands via SSH:

echo "ALTER SYSTEM SET processes=200 scope=spfile;" | sqlplus -s SYSTEM/manager
echo "ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=ASYNCH SCOPE=SPFILE;" | sqlplus -s SYSTEM/manager
echo "ALTER SYSTEM SET disk_asynch_io=TRUE SCOPE=SPFILE;" | sqlplus -s SYSTEM/manager
sudo service oracle-xe restart

More informations,

Some useful queries:

--
-- Processes
--
SHOW PARAMETER PROCESSES;
-- or
select name, value  
  from v$parameter  
 where lower(name) in ('processes', 'sessions', 'transactions');  

--
-- Disk
--
SHOW PARAMETER FILESYSTEMIO_OPTIONS;
SHOW PARAMETER disk_asynch_io;
SHOW PARAMETER dbwr_io_slaves;
-- or
select name, value  
  from v$parameter  
 where lower(name) in ('disk_asynch_io', 'filesystemio_options', 'dbwr_io_slaves'); 

select  FILETYPE_NAME, ASYNCH_IO 
  from  v$iostat_file;

--
-- Memory
--
SELECT * 
  FROM v$parameter
 WHERE LOWER(name) like '%memory%target';

-- what's my SGA and PGA settings?
select m.memory_name                             as memory_type
      ,m.memory_size                             as memory_size_in_bytes
      ,round(m.memory_size/(1024*1024), 2)       as memory_size_in_megabytes
      ,round(m.memory_size/(1024*1024*1024), 2)  as memory_size_in_gigabytes
  from (
      select name             as memory_name
            ,to_number(value) as memory_size
        from v$parameter
       where name in ('pga_aggregate_target', 'sga_target')
    union
      select name   as memory_name
            ,value  as memory_size
        from v$pgastat
       where name = 'maximum PGA allocated'
       ) m
   ;
 
select mem.* 
      ,round(mem.bytes/1024/1024, 2) as mega_bytes
  from v$sgainfo mem;
  
SELECT POOL,NAME, ROUND(BYTES/(1024*1024),2) FREE_MB 
  FROM V$SGASTAT 
 WHERE POOL='shared pool'
   AND NAME='free memory'
 ORDER BY BYTES DESC;
 
show parameter shared_pool_size;

--  current memory usage
SELECT  component
      , ROUND(current_size/(1024*1024),2) || 'M' as current_size
      , ROUND(min_size/(1024*1024),2) || 'M'     as min_size
      , ROUND(max_size/(1024*1024),2) || 'M'     as max_size
FROM    v$memory_dynamic_components
WHERE   current_size != 0;

@rponte
Copy link
Author

rponte commented May 2, 2019 •
edited
Loading 

-- turning ON automatic memory management (AMM)
ALTER SYSTEM SET MEMORY_MAX_TARGET=1G SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_TARGET=800M SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;

-- turning OFF automatic memory management (AMM)
alter system reset memory_target;
alter system reset memory_max_target;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200540160 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=601620480 SCOPE=SPFILE;

https://oracle-base.com/articles/11g/automatic-memory-management-11gr1
https://www.oracle.com/technetwork/pt/articles/database-performance/configurando-sga-pga-database-3210653-ptb.html
https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams133.htm#REFRN10285
https://dba.stackexchange.com/questions/120834/unable-to-turn-off-automatic-memory-management-amm

@rponte
Copy link
Author

rponte commented May 8, 2019 •
edited
Loading 

-- showing the current state
select name, value  
  from v$parameter
 where lower(name) in ('commit_write', 'commit_wait', 'commit_logging')
 order by name asc
 ;  

-- works on Oracle 10g and 11g, but deprecated on 11g
alter session set COMMIT_WRITE='BATCH,NOWAIT';

-- works on Oracle 11g and above
alter session set COMMIT_WAIT='NOWAIT'; -- WAIT or NOWAIT
alter session set COMMIT_LOGGING='BATCH'; -- IMMEDIATE or BATCH

-- ...we can change them on system level as well
alter system set COMMIT_WAIT='NOWAIT'; 
alter system set COMMIT_LOGGING='BATCH';

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment