Showing posts with label DatabaseManagement. Show all posts
Showing posts with label DatabaseManagement. Show all posts

Sunday 7 January 2024

Docker Compose with Oracle 21c and Java Spring Boot Application

Hi Team,

Today I'm sharing my learning on Docker compose with Oracle Database 21 c, Pluggable Database and Java Spring Boot Application.

Pre-Requisites:

1. Download Oracle 21c docker Image.
2. Download Java17 docker Image.
3. Download Docker Desktop / Rancher Desktop.

Note: All of the above tools are platform independent (I've tested on Windows & Mac). 

Docker Compose File

version: '3.8'
services:
oracle-db:
image: container-registry.oracle.com/database/enterprise:21.3.0.0
ports:
- "1521:1521"
- "5500:5500"
environment:
- ORACLE_PWD:Welcome123#
volumes:
- C:/oracleDB:/opt/oracle/oradata
- C:/workspace/oracle-ws/docker-practice/createservice/scripts: /opt/oracle/scripts/setup
networks:
- backend
cs-app:
#image: app:1.0
build: .
ports:
- "8081:8081"
depends_on:
oracle-db:
condition: service_healthy
networks:
- backend
networks:
backend:
driver: bridge

Dockerfile

FROM container-registry.oracle.com/java/jdk:17.0.9
COPY /target/createservice.jar home/createservice.jar
ENTRYPOINT java -jar home/createservice.jar


dbscript.sh

#!/bin/bash
echo Start Executing SQL commands
mkdir /opt/oracle/oradata/ORCLCDB/dockerpdb
sqlplus / as sysdba @"C:/oracleDB/scripts/pdbscript.sql"


pdbscript.sql

--CONNECT / AS SYSDBA;
--ALTER USER sys IDENTIFIED BY Welcome123#;
create pluggable database dockerpdb1 admin user dockerpdb identified by Welcome123# roles =(DBA) FILE_NAME_CONVERT =('/opt/oracle/oradata/ORCLCDB/pdbseed', '/opt/oracle/oradata/ORCLCDB/dockerpdb');
ALTER pluggable database dockerpdb1 open;
ALTER SESSION SET container = dockerpdb1;
GRANT CONNECT TO dockerpdb;
GRANT RESOURCE TO dockerpdb;
GRANT
CREATE VIEW TO dockerpdb;
GRANT
CREATE
SESSION TO dockerpdb;
GRANT
CREATE TABLE TO dockerpdb;
GRANT
CREATE SEQUENCE TO dockerpdb;
GRANT
CREATE PROCEDURE TO dockerpdb;
GRANT
CREATE TRIGGER TO dockerpdb;
alter user dockerpdb quota unlimited on SYSTEM;
GRANT UNLIMITED TABLESPACE TO dockerpdb;

exit;

GitRepo: https://github.com/pratikgaurav88/dockerjavaoracledb

References:

1. Oracle DB Image & Documentation: https://container-registry.oracle.com/ords/ocr/ba/database/enterprise
2. Oracle JDK Image & Documentation: 
https://container-registry.oracle.com/ords/ocr/ba/java/jdk
3. Docker Documentation: 
https://docs.docker.com/compose/


Logs:

C:\workspace\oracle-ws\docker-practice\createservice>docker compose up
2024/01/07 21:23:44 http2: server: error reading preface from client //./pipe/docker_engine: file has already been closed
[+] Building 0.1s (7/7) FINISHED
=> [internal] load .dockerignore 0.0s
=> => transferring context: 2B 0.0s
=> [internal] load build definition from Dockerfile 0.0s
=> => transferring dockerfile: 191B 0.0s
=> [internal] load metadata for container-registry.oracle.com/java/jdk:17.0.9 0.0s
=> [internal] load build context 0.0s
=> => transferring context: 74B 0.0s
=> [1/2] FROM container-registry.oracle.com/java/jdk:17.0.9 0.0s
=> CACHED [2/2] COPY /target/createservice.jar home/createservice.jar 0.0s
=> exporting to image 0.0s
=> => exporting layers 0.0s
=> => writing image sha256:0c381a5b81bab915b342e33e5ff4326f38993fb0ae61137e77c57f5952e15acc 0.0s
=> => naming to docker.io/library/createservice-cs-app 0.0s
[+] Running 3/3
- Network createservice_backend Created 0.0s
- Container createservice-oracle-db-1 Created 1.0s
- Container createservice-cs-app-1 Created 0.1s
Attaching to createservice-cs-app-1, createservice-oracle-db-1
createservice-oracle-db-1 | [2024:01:07 15:53:46]: Acquiring lock .ORCLCDB.create_lck with heartbeat 30 secs
createservice-oracle-db-1 | [2024:01:07 15:53:46]: Lock acquired
createservice-oracle-db-1 | [2024:01:07 15:53:46]: Starting heartbeat
createservice-oracle-db-1 | [2024:01:07 15:53:46]: Lock held .ORCLCDB.create_lck
createservice-oracle-db-1 | ORACLE EDITION: ENTERPRISE
createservice-oracle-db-1 |
createservice-oracle-db-1 | LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 07-JAN-2024 15:53:47
createservice-oracle-db-1 |
createservice-oracle-db-1 | Copyright (c) 1991, 2021, Oracle. All rights reserved.
createservice-oracle-db-1 |
createservice-oracle-db-1 | Starting /opt/oracle/product/21c/dbhome_1/bin/tnslsnr: please wait...
createservice-oracle-db-1 |
createservice-oracle-db-1 | TNSLSNR for Linux: Version 21.0.0.0.0 - Production
createservice-oracle-db-1 | System parameter file is /opt/oracle/homes/OraDB21Home1/network/admin/listener.ora
createservice-oracle-db-1 | Log messages written to /opt/oracle/diag/tnslsnr/35f8a986dc41/listener/alert/log.xml
createservice-oracle-db-1 | Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
createservice-oracle-db-1 | Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
createservice-oracle-db-1 |
createservice-oracle-db-1 | Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
createservice-oracle-db-1 | STATUS of the LISTENER
createservice-oracle-db-1 | ------------------------
createservice-oracle-db-1 | Alias LISTENER
createservice-oracle-db-1 | Version TNSLSNR for Linux: Version 21.0.0.0.0 - Production
createservice-oracle-db-1 | Start Date 07-JAN-2024 15:53:47
createservice-oracle-db-1 | Uptime 0 days 0 hr. 0 min. 0 sec
createservice-oracle-db-1 | Trace Level off
createservice-oracle-db-1 | Security ON: Local OS Authentication
createservice-oracle-db-1 | SNMP OFF
createservice-oracle-db-1 | Listener Parameter File /opt/oracle/homes/OraDB21Home1/network/admin/listener.ora
createservice-oracle-db-1 | Listener Log File /opt/oracle/diag/tnslsnr/35f8a986dc41/listener/alert/log.xml
createservice-oracle-db-1 | Listening Endpoints Summary...
createservice-oracle-db-1 | (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
createservice-oracle-db-1 | (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
createservice-oracle-db-1 | The listener supports no services
createservice-oracle-db-1 | The command completed successfully
createservice-oracle-db-1 | Prepare for db operation
createservice-oracle-db-1 | 8% complete
createservice-oracle-db-1 | Copying database files
createservice-oracle-db-1 | 31% complete
createservice-oracle-db-1 | Creating and starting Oracle instance
createservice-oracle-db-1 | 32% complete
createservice-oracle-db-1 | 36% complete
createservice-oracle-db-1 | 40% complete
createservice-oracle-db-1 | 43% complete
createservice-oracle-db-1 | 46% complete
createservice-oracle-db-1 | Completing Database Creation
createservice-oracle-db-1 | 51% complete
createservice-oracle-db-1 | 54% complete
createservice-oracle-db-1 | Creating Pluggable Databases
createservice-oracle-db-1 | 58% complete
createservice-oracle-db-1 | 77% complete
createservice-oracle-db-1 | Executing Post Configuration Actions
createservice-oracle-db-1 | 100% complete
createservice-oracle-db-1 | Database creation complete. For details check the logfiles at:
createservice-oracle-db-1 | /opt/oracle/cfgtoollogs/dbca/ORCLCDB.
createservice-oracle-db-1 | Database Information:
createservice-oracle-db-1 | Global Database Name:ORCLCDB
createservice-oracle-db-1 | System Identifier(SID):ORCLCDB
createservice-oracle-db-1 | Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL*Plus: Release 21.0.0.0.0 - Production on Sun Jan 7 16:00:55 2024
createservice-oracle-db-1 | Version 21.3.0.0.0
createservice-oracle-db-1 |
createservice-oracle-db-1 | Copyright (c) 1982, 2021, Oracle. All rights reserved.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Connected to:
createservice-oracle-db-1 | Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
createservice-oracle-db-1 | Version 21.3.0.0.0
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL>
createservice-oracle-db-1 | System altered.
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL>
createservice-oracle-db-1 | System altered.
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL>
createservice-oracle-db-1 | Pluggable database altered.
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL>
createservice-oracle-db-1 | PL/SQL procedure successfully completed.
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL> SQL>
createservice-oracle-db-1 | Session altered.
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL>
createservice-oracle-db-1 | User created.
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL>
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL>
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL>
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL>
createservice-oracle-db-1 | User altered.
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL> SQL> Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
createservice-oracle-db-1 | Version 21.3.0.0.0
createservice-oracle-db-1 | The Oracle base remains unchanged with value /opt/oracle
createservice-oracle-db-1 |
createservice-oracle-db-1 | Executing user defined scripts
createservice-oracle-db-1 | /opt/oracle/runUserScripts.sh: running /opt/oracle/scripts/extensions/setup/swapLocks.sh
createservice-oracle-db-1 | [2024:01:07 16:00:56]: Releasing lock .ORCLCDB.create_lck
createservice-oracle-db-1 | [2024:01:07 16:00:56]: Lock released .ORCLCDB.create_lck
createservice-oracle-db-1 | [2024:01:07 16:00:56]: Acquiring lock .ORCLCDB.exist_lck with heartbeat 30 secs
createservice-oracle-db-1 | [2024:01:07 16:00:56]: Lock acquired
createservice-oracle-db-1 | [2024:01:07 16:00:56]: Starting heartbeat
createservice-oracle-db-1 | [2024:01:07 16:00:56]: Lock held .ORCLCDB.exist_lck
createservice-oracle-db-1 |
createservice-oracle-db-1 | DONE: Executing user defined scripts
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Executing user defined scripts
createservice-oracle-db-1 | /opt/oracle/runUserScripts.sh: running /opt/oracle/scripts/setup/dbscript.sh
createservice-oracle-db-1 | Start Executing SQL commands
createservice-oracle-db-1 |
createservice-oracle-db-1 | SQL*Plus: Release 21.0.0.0.0 - Production on Sun Jan 7 16:00:57 2024
createservice-oracle-db-1 | Version 21.3.0.0.0
createservice-oracle-db-1 |
createservice-oracle-db-1 | Copyright (c) 1982, 2021, Oracle. All rights reserved.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Connected to:
createservice-oracle-db-1 | Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
createservice-oracle-db-1 | Version 21.3.0.0.0
createservice-oracle-db-1 |
createservice-oracle-db-1 | SP2-0310: unable to open file "C:/oracleDB/scripts/pdbscript.sql"
createservice-oracle-db-1 | SQL> Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
createservice-oracle-db-1 | Version 21.3.0.0.0
createservice-oracle-db-1 |
createservice-oracle-db-1 | /opt/oracle/runUserScripts.sh: running /opt/oracle/scripts/setup/pdbscript.sql
createservice-oracle-db-1 |
createservice-oracle-db-1 | Pluggable database created.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Pluggable database altered.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Session altered.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | User altered.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | Grant succeeded.
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 |
createservice-oracle-db-1 | DONE: Executing user defined scripts
createservice-oracle-db-1 |
createservice-oracle-db-1 | The Oracle base remains unchanged with value /opt/oracle
createservice-oracle-db-1 | #########################
createservice-oracle-db-1 | DATABASE IS READY TO USE!
createservice-oracle-db-1 | #########################
createservice-oracle-db-1 | The following output is now a tail of the alert.log:
createservice-oracle-db-1 | ****************************************************************
createservice-oracle-db-1 | Post plug operations are now complete.
createservice-oracle-db-1 | Pluggable database DOCKERPDB1 with pdb id - 4 is now marked as NEW.
createservice-oracle-db-1 | ****************************************************************
createservice-oracle-db-1 | DOCKERPDB1(4):Resize operation completed for file# 14, fname /opt/oracle/oradata/ORCLCDB/dockerpdb/sysaux01.dbf, old size 327680K, new size 337920K
createservice-oracle-db-1 | DOCKERPDB1(4):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
createservice-oracle-db-1 | 2024-01-07T16:01:04.090573+00:00
createservice-oracle-db-1 | DOCKERPDB1(4):Opening pdb with no Resource Manager plan active
createservice-oracle-db-1 | Pluggable database DOCKERPDB1 opened read write
createservice-oracle-db-1 | Completed: ALTER pluggable database dockerpdb1 open
createservice-cs-app-1 |
createservice-cs-app-1 | . ____ _ __ _ _
createservice-cs-app-1 | /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
createservice-cs-app-1 | ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
createservice-cs-app-1 | \\/ ___)| |_)| | | | | || (_| | ) ) ) )
createservice-cs-app-1 | ' |____| .__|_| |_|_| |_\__, | / / / /
createservice-cs-app-1 | =========|_|==============|___/=/_/_/_/
createservice-cs-app-1 | :: Spring Boot :: (v3.2.1)
createservice-cs-app-1 |
createservice-cs-app-1 | 2024-01-07T16:01:49.344Z INFO 1 --- [ main] com.oracle.CreateserviceApplication : Starting CreateserviceApplication using Java 17.0.9 with PID 1 (/home/createservice.jar started by root in /)
createservice-cs-app-1 | 2024-01-07T16:01:49.347Z INFO 1 --- [ main] com.oracle.CreateserviceApplication : No active profile set, falling back to 1 default profile: "default"
createservice-cs-app-1 | 2024-01-07T16:01:50.117Z INFO 1 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
createservice-cs-app-1 | 2024-01-07T16:01:50.168Z INFO 1 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 44 ms. Found 1 JPA repository interface.
createservice-cs-app-1 | 2024-01-07T16:01:50.630Z INFO 1 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port 8081 (http)
createservice-cs-app-1 | 2024-01-07T16:01:50.640Z INFO 1 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
createservice-cs-app-1 | 2024-01-07T16:01:50.640Z INFO 1 --- [ main] o.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/10.1.17]
createservice-cs-app-1 | 2024-01-07T16:01:50.672Z INFO 1 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
createservice-cs-app-1 | 2024-01-07T16:01:50.674Z INFO 1 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1218 ms
createservice-cs-app-1 | 2024-01-07T16:01:50.929Z INFO 1 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default]
createservice-cs-app-1 | 2024-01-07T16:01:50.978Z INFO 1 --- [ main] org.hibernate.Version : HHH000412: Hibernate ORM core version 6.4.1.Final
createservice-cs-app-1 | 2024-01-07T16:01:51.003Z INFO 1 --- [ main] o.h.c.internal.RegionFactoryInitiator : HHH000026: Second-level cache disabled
createservice-cs-app-1 | 2024-01-07T16:01:51.198Z INFO 1 --- [ main] o.s.o.j.p.SpringPersistenceUnitInfo : No LoadTimeWeaver setup: ignoring JPA class transformer
createservice-cs-app-1 | 2024-01-07T16:01:51.225Z INFO 1 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
createservice-cs-app-1 | 2024-01-07T16:01:51.656Z INFO 1 --- [ main] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Added connection oracle.jdbc.driver.T4CConnection@3dfd7eaa
createservice-cs-app-1 | 2024-01-07T16:01:51.658Z INFO 1 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
createservice-cs-app-1 | 2024-01-07T16:01:51.815Z WARN 1 --- [ main] org.hibernate.orm.deprecation : HHH90000025: OracleDialect does not need to be specified explicitly using 'hibernate.dialect' (remove the property setting and it will be selected by default)
createservice-cs-app-1 | 2024-01-07T16:01:53.500Z INFO 1 --- [ main] o.h.e.t.j.p.i.JtaPlatformInitiator : HHH000489: No JTA platform available (set 'hibernate.transaction.jta.platform' to enable JTA platform integration)
createservice-cs-app-1 | Hibernate:
createservice-cs-app-1 | create table employee (
createservice-cs-app-1 | emp_id raw(16) not null,
createservice-cs-app-1 | first_name varchar2(255 char) not null,
createservice-cs-app-1 | last_name varchar2(255 char) not null,
createservice-cs-app-1 | primary key (emp_id)
createservice-cs-app-1 | )
createservice-cs-app-1 | 2024-01-07T16:01:53.989Z INFO 1 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
createservice-cs-app-1 | 2024-01-07T16:01:54.223Z WARN 1 --- [ main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
createservice-cs-app-1 | 2024-01-07T16:01:54.539Z INFO 1 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port 8081 (http) with context path ''
createservice-cs-app-1 | 2024-01-07T16:01:54.553Z INFO 1 --- [ main] com.oracle.CreateserviceApplication : Started CreateserviceApplication in 5.651 seconds (process running for 6.236)
createservice-cs-app-1 | 2024-01-07T16:02:34.698Z INFO 1 --- [nio-8081-exec-2] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
createservice-cs-app-1 | 2024-01-07T16:02:34.699Z INFO 1 --- [nio-8081-exec-2] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
createservice-cs-app-1 | 2024-01-07T16:02:34.700Z INFO 1 --- [nio-8081-exec-2] o.s.web.servlet.DispatcherServlet : Completed initialization in 1 ms
createservice-cs-app-1 | Employee Save :: Pratik :: LastName :: Gaurav
createservice-cs-app-1 | Hibernate:
createservice-cs-app-1 | insert
createservice-cs-app-1 | into
createservice-cs-app-1 | employee
createservice-cs-app-1 | (first_name, last_name, emp_id)
createservice-cs-app-1 | values
createservice-cs-app-1 | (?, ?, ?)

A Guide to Installing Oracle HR Schema on an Existing Docker Container

  Hi Reader, Today I want to share my learning on how to install Oracle HR schema on a existing docker container. Step 1: Download the verif...