/ / Oracle Database 19.3.0

Portfolio

Oracle Database 19.3.0

Oracle Database 19.3.0

You can support us by donate in the link: https://qntdata.com/qt-donation

If you have any question, search or create an issue here: https://github.com/qtdatainc/oracle-docker/issues

I. Docker

Install docker: https://docs.docker.com/engine/install/ubuntu/

Install docker compose: https://docs.docker.com/compose/install/

II. Oracle DB

1. Initialization

Link: https://github.com/qtdatainc/oracle-docker/tree/main/OracleDatabase/SingleInstance

Issue: Can’t start Oracle Database XE 18.4 Docker Container

Solution: You will have to provide the installation binaries of Oracle Database (except for Oracle Database 18c XE) and put them into the dockerfiles/19.3.0 folder. You only need to provide the binaries for the edition you are going to install. The binaries can be downloaded from the Oracle Technology Network, make sure you use the linux link:

https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

1.1 Building an image

cd docker-images/OracleDatabase/SingleInstance/dockerfiles

./buildDockerImage.sh -v 19.3.0 -e

1.2 Create a new volume contains all data of the database

docker volume create oracledb19c-vol

chmod -R a+w /var/lib/docker/volumes/oracledb19c-vol

All data will be contained at: var/lib/docker/volumes/oracledb19c-vol

1.3 Run the container

docker run --name oracledb19c -p 1521:1521 -p 5500:5500 -e ORACLE_SID=ORCLCDB -e ORACLE_PDB=ORCLCDB1 -e ORACLE_PWD=password -e INIT_SGA_SIZE=8000 -e INIT_PGA_SIZE=2000 -e ORACLE_EDITION=enterprise -v oracledb19c-vol:/opt/oracle/oradata oracle/database:19.3.0-ee

After installation,  press Ctrl+C to exit.

Reopen docker: docker container start oracledb19c

Config docker always restart automatically when there is a problem: docker run -d --restart unless-stopped oracledb19c

2. Create TableSpace and User

2.1 SSH to docker

sudo docker exec -it oracledb19c bash

Command in docker: Login sqlplus with user sys sqlplus sys/password@ORCLCDB as sysdba or login with new user sqlplus system/password@ORCLCDB

2.2 Create TABLESPACE

BIGFILE: A big file tablespace contains only one datafile or temp file, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single datafile or temp file is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.

SQL> CREATE BIGFILE TABLESPACE BIGDB
	DATAFILE '/opt/oracle/oradata/ORCLCDB/BIGDATA.DBF'
		SIZE 10G
		AUTOEXTEND ON NEXT 100G MAXSIZE 300G;

SMALLFILE: A small file tablespace is a traditional Oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million (222) blocks.

SQL> CREATE TABLESPACE SMALLDB
	DATAFILE '/opt/oracle/oradata/ORCLCDB/SMALLDATA.DBF'
		SIZE 1024M
		AUTOEXTEND ON NEXT 100M MAXSIZE 32000M;

Edit TABLESPACE

SQL> ALTER TABLESPACE SMALLDB
	ADD DATAFILE '/opt/oracle/oradata/ORCLCDB/SMALLDATA.DBF'
	SIZE 1024M
	AUTOEXTEND ON;

2.3 Create user

SQL> alter session set "_ORACLE_SCRIPT"=true; You are allowed to name it as you like

SQL> create user USER identified by password default tablespace BIGDB account unlock;

Provide roles

SQL> GRANT CONNECT,RESOURCE,DBA TO USER;

Provide privileges

SQL> GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO USER;

SQL> GRANT UNLIMITED TABLESPACE TO USER;

Or

SQL> GRANT ALL PRIVILEGES TO USER;

2.4 Create directory

Here in this query IMP_DIR/EXP_DIR is a directory object (you can give whatever name you want to your directory object) which is just a name mapped over a directory path. Or you can say that it’s just a pointer pointing to a directory which you want your expdp utility to use when storing all exported files.

Mind here CREATE DIRECTORY command will not create any actual directory in your system. This command only helps you in creating a directory object.

  • Import

SQL> CREATE OR REPLACE DIRECTORY IMP_DIR AS '/home/oracle/import';

SQL> GRANT READ, WRITE ON DIRECTORY IMP_DIR TO USER;

SQL> GRANT UNLIMITED TABLESPACE TO USER;

For Example: /home/oracle/import is where the data import are stored outside of the mount. Need to adjust to the import path

  • Export

SQL> CREATE OR REPLACE DIRECTORY EXP_DIR AS '/opt/oracle/export';

SQL> GRANT READ, WRITE ON DIRECTORY EXP_DIR TO USER;

SQL> GRANT UNLIMITED TABLESPACE TO USER;

For example home/oracle/export is where the data export are stored outside of the mount.

2.5 Import data from dump file

CMD> impdp USER/password tables=TABLE_A,TABLE_B,TABLE_C directory=IMP_DIR dumpfile= BIGDB.DBF logfile=impBIGDB.log

2.6 Export data to dump file

If not set DIRECTORY then DEFAULT_DUMP_DIR is /opt/oracle/admin/ORCLCDB/dpdump

CMD> expdp \'sys/password@ORCLCDB as sysdba\' SCHEMAS=USER DIRECTORY=EXP_DIR
 DUMPFILE=DATA_`date +%y%m%d_%H%M%S.`dmp LOGFILE=DATA_`date +%y%m%d_%H%M%S.`log;

See more details:  http://www.rebellionrider.com/data-pump-expdp-how-to-export-tablespace-oracle

See other args in expdp: https://docs.oracle.com/cd/E18283_01/server.112/e16536/dp_export.htm

2.7 Change localtime inside docker container

Login as root user into the container: docker exec -u root -it oracledb19c bash

Check for /etc/localtime file which would be pointing to UTC

bash# cd /etc

bash# ls -lrt localtime

We will see: lrwxrwxrwx 1 root root 25 May 26 12:56 localtime -> ../usr/share/zoneinfo/UTC

Remove the file and create a new softlink to point to the file corresponding to your local timezone.

bash# rm -f localtime

bash# ln -s /usr/share/zoneinfo/Asia/Ho_Chi_Minh localtime

3. SQL execution

  • Check user info: SQL> SELECT * FROM all_users SQL> SELECT * FROM dba_users SQL> SELECT * FROM user_users
  • Check table info: SQL> SELECT table_name FROM all_tables SQL> SELECT table_name FROM dba_tables SQL> SELECT table_name FROM user_tables
  • Check free space on tablespace: SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
  • Check table size: SQL> select bytes from user_segments where segment_name like 'table_name'
  • Check segments size: SQL> select owner, sum(bytes)/1024/1024 Size_MB from dba_segments group by owner
  • Check table column: SQL> SELECT column_name FROM user_tab_cols WHERE table_name = 'table_name'
  • Check maximum connection: SQL> select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in (‘processes’,’sessions’);
  • Check connection pooling and the number of sessions currently active: SQL> SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL; SQL> SELECT COUNT(*) FROM v$session
  • Stop all sessions are running: SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
Buy us some coffee

Thank You for your support as we work to give you the best of guides and articles.

top