how to execute sql script after oracle docker container startup?

Question

I have setup docker container with spring boot + oracle DB.

I need to run one sql script when oracle container startup.

I tried below approach but container stuck in docker-entrypoint shell script waiting for database startup.

docker-compose.yml

version: "3.4"
services:
  oracle:
    build: 
        context: .
        dockerfile: ./docker/oracle/Dockerfile
    container_name: cc_admin_portal_oracleDB
    volumes:
      - oracle-data:/var/lib/oracle/data
      - ./docker/oracle/docker-entrypoint.sh:/docker-entrypoint-initdb.d/docker-entrypoint.sh
      - ./docker/oracle/oracle-scripts.sql:/docker-entrypoint-initdb.d/oracle-scripts.sql
    entrypoint: ["/docker-entrypoint-initdb.d/docker-entrypoint.sh"]
    expose:
    - 1522
    ports:
      - 1522:1521
    environment:
        - ORACLE_SID=ORCLCDB
        - SYS_PASSWORD=Oradoc_db1
        - PDB_NAME=abcd
        - PDB_PASSWORD=abcd1234
    restart: unless-stopped
    networks:
      - inbound-network
# APP*****************************************
  cc_admin_portal:
    image: cc_admin_portal:latest
    build: 
        context: .
        dockerfile: ./docker/cc_admin_portal/Dockerfile
    container_name: cc_admin_portal
    environment:
        SPRING_DATASOURCE_TYPE : com.zaxxer.hikari.HikariDataSource
        SPRING_DATASOURCE_URL: jdbc:oracle:thin:@oracle:1521:ORCLCDB
        SPRING_DATASOURCE_USERNAME: abcd
        SPRING_DATASOURCE_PASSWORD: abcd1234
    expose:
      - 8080
    ports:
      - 8080:8080
    restart: unless-stopped
    entrypoint: ["/usr/local/wait-for-it.sh","oracle:1521","--timeout=0","--strict", "--", "catalina.sh","run"]
    depends_on:
      - oracle
    links:
      - oracle
    networks:
      - inbound-network
volumes:
  oracle-data:
networks:
  inbound-network:
    name: inbound-network

docker-entrypoint.sh

#!/bin/sh

until echo "exit" | sqlplus -L sys/Oradoc_db1 as sysdba < /dev/null | grep 'Connected to'; do
  echo "Waiting for database to start.Trying again in 5 seconds..."
  sleep 5
done

for f in /docker-entrypoint-initdb.d/*; do
    case "$f" in
        *.sql)    echo "[IMPORT] $0: running $f"; echo "exit" |  sqlplus -S / as sysdba @$f; echo ;;
        *)        echo "[IMPORT] $0: ignoring $f" ;;
    esac
    echo
done

./docker/oracle/Dockerfile

FROM store/oracle/database-enterprise:12.2.0.1-slim
RUN rm -rf /docker-entrypoint-initdb.d/*
ENV PATH="${ORACLE_HOME}/bin:${PATH}"
ENV LD_LIBRARY_PATH="${ORACLE_HOME}"

is there any other way i can achieve sql script execution after oracle container up and running?

Answer 1

From what I see I suspect that you just need to add @path_to_sql_script into your ./docker/oracle/oracle-scripts.sql.

But I'd suggest to use official Oracle images. In this case you could just mount your directory with sql scripts into as /opt/oracle/scripts/startup and configurations scripts as /opt/oracle/scripts/setup.

Answer 2

Depending on your needs, you can customize your Oracle DB image and add a directory for start-up scripts by yourself. Usually, a Docker image with a database has some bash script file that runs commands on the container startup. One can edit this script and add literally any logic to it by following steps below:

1. Run a container from the image
2. Poc around inside the container and find the script mentioned above. Most likely you will find a hint for script name and location in the container logs.
3. Copy this script to PC, add custom logic.
4. Create Dockerfile which uses the original image as a base, and in COPY instruction specify a command to copy the edited script into the image.
5. Build a new image using created Dockerfile.
6. Spin container from the newly built image.

You can follow this tutorial for customaizing Oracle database-enterprise:12.2.0.1 image.