CODEWITHSUNDEEP
oracle-databasedockerdocker-composedockerfile
Dmitry Senkovich
Dmitry Senkovich

Reputation: 5921

How do I check if Oracle is up in Docker?

As the title says: how do I check if Oracle is up in Docker? Now my app tries to create a Hibernate session and I'm getting

ERROR :
ORA-01033: ORACLE initialization or shutdown in progress

So I would like some kind of health check having url only. Is that possible? Thank you!

Upvotes: 14

Views: 10401

Answers (7)

JRichardsz
JRichardsz

Reputation: 16564

The following script worked for me for docker 24.0.2 + oracle 11g

First create this script: /opt/oracle/oracle_health_check.sh

#!/bin/bash

#change 'system/changeme' with your credentials or use env variables

query_validation_response=$(echo "select 'true' as status from dual;" | sqlplus -s system/changeme@localhost:1521/xe)

if [[ "${query_validation_response,,}" == *"error"* ]]; then
  #failed
  exit 1;
else
  #connection was success
  exit 0;
fi;

Don't forget execution permissions

chmod +x /opt/oracle/oracle_health_check.sh

Usage:

./opt/oracle/oracle_health_check.sh

Result if database is ready to use:

enter image description here

Result if database is offline:

enter image description here

Usage with docker-compose yaml:

healthcheck:
   test: [ "CMD", "/opt/oracle/oracle_health_check.sh"]
   interval: 2s

Explanation

  • sqlplus is used to test the oracle connectivity
  • select 1 from dual is used as query validation
  • sqlplus always returns 0 as exit code, regardless of whether the query was executed successfully or returned an error
  • To determine the success/error, I parse the generated stdout looking for "error" word, because sqlplus always return that word in case of error

sqlplus error stdout sample

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]

sqlplus success stdout sample

STAT
----
true
  • Finally, if stdout contains "error" word, I return 1 as exit code and 0 in the opposite case

Some lectures

Upvotes: 0

TrojanName
TrojanName

Reputation: 5365

For Windows Oracle containers, unfortunate checkDBStatus.sh isn't available so here's how I did it.

Create a file called C:\scripts\healthcheck.ps1 as follows, replaced the password and SID with your own. You should probably use a different user with less privileges and select 1 from dual or whatever. The idea is that it should return 0 when Oracle is ready to accept a connection, otherwise it should return 1.

# Healthcheck script that returns 0 if Oracle is ready to accept connections, else returns 1

$chekdbsql = "`nselect status from v`$instance;"
$chkdb = ""
$chkdb = ($chekdbsql | cmd /c "sqlplus sys/password@localhost/ora193 as sysdba")
if ($chkdb.Contains("OPEN") -eq 'True'){
  exit 0
}
else {
  exit 1
}

Add this line to Oracle DB Dockerfile:

HEALTHCHECK --interval=10s --timeout=10s --retries=20 CMD powershell C:\scripts\healthcheck.ps1

Finally, in your docker-compose.yaml add the following to the relevant section for the service that depends on your oracle service:

depends_on: 
  oracle:
      condition: service_healthy

Upvotes: 2

Kirill
Kirill

Reputation: 8096

Using docker-compose.yml and Official Oracle docker images you can use checkDBStatus.sh script as a healthcheck. The script returns non-0 while db is in ORA-01033 state. Below is an example. Notice the combination of db's service healthcheck and tomcat's depends_on with service_healthy condition:

  tomcat:
    image: "tomcat:9.0"
    depends_on:
      oracle-db:
        condition: service_healthy
    links:
      - oracle-db
services:
  oracle-db:
    build:
      context: src/main/docker/oracle_db
      dockerfile: Dockerfile.xe
    mem_reservation: 2g
    environment:
      - ORACLE_PWD=oracle
    volumes:
      - oracle-data:/opt/oracle/oradata
    healthcheck:
      test: [ "CMD", "/opt/oracle/checkDBStatus.sh"]
      interval: 2s

volumes:
  oracle-data:

Upvotes: 10

Heri
Heri

Reputation: 4598

If you are starting an oracle DB docker container within a jenkinsfile you may find this useful:

def waitForDbHealthy(containerName) 
{
    timeout(time: 4, unit: 'MINUTES') 
    {
        def HEALTH_RESULT=""
        while (! HEALTH_RESULT.toString().contains("healthy") )
        {
            echo "DB not yet healthy. going to sleep 10 sec."
            sleep 10
            HEALTH_RESULT=sh(returnStdout: true, script: "docker inspect --format='{{json .State.Health.Status}}' $containerName").trim()
            echo "HEALTH_RESULT: $HEALTH_RESULT"
            
            if ( HEALTH_RESULT.toString().contains("unhealthy") )
            {
                sh("docker logs $containerName")
                echo "Going to throw IllegalStateException"
                throw new IllegalStateException("Oracle DB switched to state unhealthy")
            }
        }
    }
}

On my build server it tooks about 1 minute until the container is "healthy".

Be aware that oracle's TNS listener might not be ready yet. I found that an additional "sleep 60" (seconds) does the trick. Alternatively you can implement the java TNSPING as Krzysztof Kaszkowiak pointed out in his answer.

Another note: Throwing an IllegalStateException is not allowed per default in Jenkinsfile's groovy. Your Jenkins administrator must explicitely accept it (Jenkins/Manage Jenkins/In-process Script Approval).

Jenkins 2.249.2

Docker Version: 19.03.8

Oracle docker image: based on store/oracle/database-enterprise:12.2.0.1-slim

Upvotes: 2

wutzebaer
wutzebaer

Reputation: 14865

i'm using wnameless/oracle-xe-11g-r2 and this works for me

version: '3'
services:
   db:
      image: wnameless/oracle-xe-11g-r2
      environment:
      - ORACLE_ALLOW_REMOTE=true
      ports:
      - 49261:1521
      volumes:
      - ./0_init.sql:/docker-entrypoint-initdb.d/0_init.sql
      healthcheck:
         test: [ "CMD", "bash", "-c", "echo 'select 1 from dual;' | ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe /u01/app/oracle/product/11.2.0/xe/bin/sqlplus -s USERNAME/PASSWORD@localhost"]
         # docker inspect --format "{{json .State.Health }}"  myproject_db_1
         interval: 10s
         timeout: 10s
         retries: 60
   myservice:
      image: xxx
      depends_on:
         db:
            condition: service_healthy

Upvotes: 5

Dmitry Senkovich
Dmitry Senkovich

Reputation: 5921

I've finished with a simple check for APEX:

while [[ "$(curl -s -o /dev/null -w ''%{http_code}'' db:8080/apex)" != "302" ]]; do sleep 5; done

302 is used because it redirects /apex to /apex/some_stuff. In my case db is the name of the container with Oracle:

version: '3'
services:
  ...
  * other containers *
  ...
  db:
    image: some/image
    ports:
      - "8383:8080"
      - "1521:1521"

Hope it helps someone!

Upvotes: 1

Krzysztof Kaszkowiak
Krzysztof Kaszkowiak

Reputation: 896

You can mimic tnsping in your Java app: How to do oracle TNSPING with java?

If you can't modify the app, tnsping can be called from a bash script - if you have Oracle client installed. If you don't, simply create a simple application from the link above and execute it in a script.

Upvotes: 2

Related Questions