How to Create an Oracle Database Directory with Jenkins Automation ~ APEX Note

In this blog post, I will show you step-by-step how to create an Oracle database directory, combined with Jenkins automation. These commands can also be executed manually without creating a Jenkins script, simply by running them directly in the terminal.

Step 1: Define Variables

First, we define the necessary variables that we will use later in the script. These variables include the server IP address, schema name, username, password, container name, directory name, directory path, and database name.

Shell

xxxxxxxxxx
 
# Specific server changes
V_REMOTE_SERVER_IP="85.215.45.123"  # IP address of the remote server
export V_DB_SCHEMA_NAME="OLD_INVOICE_DEV"  # Name of the DB schema
​
#################### =========== NORMAL DEFAULT =========== #################### 
# Remote server variables
V_USER_NAME="root"  # Username for the remote server
V_USER_PASS="1*2*3*4*"  # Password for the remote server
​
# Remote CONTAINER DATABASE variables:
V_CONTAINER_NAME="database"  # Name of the Docker container
export V_DIRECTORY="DB_DUMP_$V_DB_SCHEMA_NAME"  # Name of the directory
export V_DIRECTORY_PATH="/opt/oracle/db_dumb/$V_DB_SCHEMA_NAME"  # Path of the directory
export V_DB_NAME="ORCLPDB"  # Name of the PDB

Step 2: Connect to the Remote Server

Next, we connect to the external remote server where the database is installed. We use sshpass to pass the password and ssh to establish the connection.

Shell

xxxxxxxxxx
 
# Connect to the remote server
sshpass -p $V_USER_PASS ssh -o StrictHostKeyChecking=no $V_USER_NAME@$V_REMOTE_SERVER_IP << EOF

sshpass -p $V_USER_PASS: Passes the password for the SSH connection.
ssh -o StrictHostKeyChecking=no: Disables host key checking.
$V_USER_NAME@$V_REMOTE_SERVER_IP: Connects to the remote server.

Step 3: Open Docker Container

Within the remote server, we open the Docker container where the Oracle database is running.

Plain Text

xxxxxxxxxx
 
# Open Docker container
docker exec $V_CONTAINER_NAME bash -c "

docker exec $V_CONTAINER_NAME bash -c ": Executes a command inside the running Docker container.

Step 4: Create and Navigate to Directory

Inside the container, we create the directory and navigate into it.

Shell

xxxxxxxxxx
 
# Create folder
mkdir -p $V_DIRECTORY_PATH
​
# Navigate to folder
cd $V_DIRECTORY_PATH

mkdir -p $V_DIRECTORY_PATH: Creates the directory if it does not exist.
cd $V_DIRECTORY_PATH: Changes to the newly created directory.

Step 5: Create Oracle Directory

Now we create the Oracle directory. We use sqlplus to send SQL commands to the database.

SQL

xxxxxxxxxx
 
sqlplus / as sysdba <<EOF
show pdbs;  # Show available PDBs
alter session set container = $V_DB_NAME;  # Switch to the specific PDB
DROP DIRECTORY $V_DIRECTORY;  # Drop the directory if it exists
CREATE OR REPLACE DIRECTORY $V_DIRECTORY AS '$V_DIRECTORY_PATH';  # Create or replace the directory
commit;  # Commit the changes
SELECT directory_name, directory_path FROM all_directories WHERE directory_name = '$V_DIRECTORY';  # Verify the created directory
GRANT READ, WRITE ON DIRECTORY $V_DIRECTORY TO $V_DB_SCHEMA_NAME;  # Grant read and write permissions
GRANT DATAPUMP_EXP_FULL_DATABASE TO $V_DB_SCHEMA_NAME;  # Grant export permissions
exit;
EOF

sqlplus / as sysdba: Starts SQL*Plus as SYSDBA.
show pdbs;: Shows the available Pluggable Databases (PDBs).
alter session set container = $V_DB_NAME;: Switches to the specific PDB.
DROP DIRECTORY $V_DIRECTORY;: Drops the directory if it exists.
CREATE OR REPLACE DIRECTORY $V_DIRECTORY AS '$V_DIRECTORY_PATH';: Creates or replaces the directory.
commit;: Commits the changes.
SELECT directory_name, directory_path FROM all_directories WHERE directory_name = '$V_DIRECTORY';: Verifies the created directory.
GRANT READ, WRITE ON DIRECTORY $V_DIRECTORY TO $V_DB_SCHEMA_NAME;: Grants read and write permissions.
GRANT DATAPUMP_EXP_FULL_DATABASE TO $V_DB_SCHEMA_NAME;: Grants export permissions.

Step 6: Verify Directory

At the end, we check again if the folder is empty and created, and display the entire path.

Shell

xxxxxxxxxx
 
ls  # List the contents of the directory
pwd  # Show the current path
"
EOF

Complete Code

Here is the complete code summarized:

Shell

xxxxxxxxxx
 
# Specific server changes
V_REMOTE_SERVER_IP="85.215.45.123"
export V_DB_SCHEMA_NAME="OLD_INVOICE_DEV" 
​
#################### =========== NORMAL DEFAULT =========== #################### 
# Remote server variables
V_USER_NAME="root"
V_USER_PASS="1*2*3*4*"
​
# Remote CONTAINER DATABASE variables:
V_CONTAINER_NAME="database"
export V_DIRECTORY="DB_DUMP_$V_DB_SCHEMA_NAME"
export V_DIRECTORY_PATH="/opt/oracle/db_dumb/$V_DB_SCHEMA_NAME"
export V_DB_NAME="ORCLPDB" 
​
#################### =========== DO NOT CHANGE =========== #################### 
​
# Connect to the remote server
sshpass -p $V_USER_PASS ssh -o StrictHostKeyChecking=no $V_USER_NAME@$V_REMOTE_SERVER_IP << EOF
​
# Open Docker container
docker exec $V_CONTAINER_NAME bash -c "
​
# Create folder
mkdir -p $V_DIRECTORY_PATH
​
# Navigate to folder
cd $V_DIRECTORY_PATH
​
sqlplus / as sysdba <<EOF
show pdbs;
alter session set container = $V_DB_NAME;
DROP DIRECTORY $V_DIRECTORY;
CREATE OR REPLACE DIRECTORY $V_DIRECTORY AS '$V_DIRECTORY_PATH';
commit;
SELECT directory_name, directory_path FROM all_directories WHERE directory_name = '$V_DIRECTORY';
GRANT READ, WRITE ON DIRECTORY $V_DIRECTORY TO $V_DB_SCHEMA_NAME;
GRANT DATAPUMP_EXP_FULL_DATABASE TO $V_DB_SCHEMA_NAME;
exit; 
ls 
pwd
"
EOF

That's it! This Jenkins script should automate the creation of a new directory for you, and you can simply place backups or imports into the path to then load them into the respective schema, or to import or export data.

Call to Action:

Test the Script: Run the script in your environment to ensure it works as expected.
Customize for Your Needs: Adjust the variables according to your specific requirements and environment.
Share Your Experience: Let us know how the script worked for you or if you made any improvements.

{fullWidth}

APEX Note