How to Create an Oracle Database Backup with Jenkins Automation

           , , ,      No comments   
In this blog post, I'll explain how to set up automatic backups for an Oracle database running in a Docker container. The goal is to automate the backup process using Jenkins, allowing it to run daily. This approach is an efficient way to ensure the security of your data and facilitate quick recovery in case of data loss or system failure.

1. Preparation of the Remote Database Server

First, we need to ensure that the remote database server is properly configured. A crucial aspect of this is setting up a directory on the server that will serve as the storage location for the export files. This directory is referenced in the Oracle database as a DIRECTORY and should have enough space to accommodate the backup data.

2.1 Accessing the Docker Container

Since our Oracle database operates within a Docker container, the next step is to gain access to the container to perform the necessary configurations. This includes creating the required directories inside the container that will be used for storing the backups.

2.2 Configuring the Oracle Database for Backups

Inside the Docker container, the database needs to be configured to write backups to the previously created directory. This involves creating an Oracle DIRECTORY that points to the backup directory and granting the necessary permissions to the database user to access and place backups in it.

2.3 Automation Through Jenkins

To execute the backup daily, we use Jenkins, an open-source Continuous Integration/Continuous Deployment (CI/CD) software that allows scheduling jobs. By setting up a Jenkins job to start at the same time every day, we can ensure that our database is regularly backed up. The Jenkins job is configured to run the necessary scripts to initiate the database backup in the Docker container and ensure everything runs smoothly.
This method of automated backups provides a robust solution for data security, reduces the risk of data loss, and enhances the overall reliability of your IT infrastructure.
 
Shell

  • docker exec: Executes a command in a running container.
  • -it: Allows interactive use of the container.
  • database: The name of the container.
  • bash: The shell that will be started in the container.

3. Create a Directory Inside the Container

Create a directory in a location that is marked as a volume. This allows us to access the files later without opening the container, but through the host volume.
 
Shell

  • mkdir -p: Creates a directory and all necessary parent directories.
  • /opt/oracle/oradata/db_export/SCHEMA_NAME: The path to the new directory.

4. Access from Outside the Container

From outside the container, you can access the directory using the following command:
 
Shell

  • cd: Change to the specified directory.
  • /var/lib/docker/volumes/...: The path to the Docker volume on the host system.

5. Start SQLPlus in the Container

Log into the container and start SQLPlus to select the correct container database.
 
SQL

  • sqlplus / as sysdba: Starts SQLPlus as a system administrator.
  • show pdbs: Displays all pluggable databases (PDBs).
  • alter session set container = ORCLPDB: Switches to the specified PDB.

6. Create a Directory in the Database

Create a directory in the database. If it already exists, delete it and create it again. It is important to specify the path that was set in the container, as this is where the dump will be saved later.
 
SQL

  • DROP DIRECTORY DB_EXPORT: Deletes the directory if it exists.
  • CREATE OR REPLACE DIRECTORY DB_EXPORT AS '/opt/oracle/oradata/dbdump/SCHEMA_NAME': Creates a new directory or replaces an existing one.
  • commit: Confirms the changes.

7. Test the Directory and Set Permissions

Test the directory and set the appropriate permissions for the schema.
 
SQL

  • SELECT directory_name, directory_path FROM all_directories WHERE directory_name = 'DB_EXPORT': Checks if the directory was created correctly.
  • GRANT READ, WRITE ON DIRECTORY DB_EXPORT TO SHSS_DEV: Grants read and write permissions on the directory to the SHSS_DEV schema.
  • GRANT DATAPUMP_EXP_FULL_DATABASE TO SHSS_DEV: Grants the SHSS_DEV schema the permission to perform database exports.
  • exit: Exits the SQLPlus session.

8. Prepare the Backup Command

To create a backup, you can use the following command. However, we want to automate this process using a Jenkins job.
 
Shell

  • expdp: Oracle Data Pump Export Utility.
  • SHSS_DEV/SHSS_DEV@ORCLPDB: Username and password for the database connection.
  • schemas=SHSS_DEV: The schema to be exported.
  • directory=DB_EXPORT: The directory where the export files will be saved.
  • dumpfile=SHSS_DEV.dmp: The name of the dump file.
  • logfile=SHSS_DEV.log: The name of the log file.

9. Create a Jenkins Job

Create a new item (Freestyle Project) in Jenkins and execute a shell script.

9.1 Define Remote Server Variables

Define the IP of the remote server and the schema name and password.
 
Shell

  • V_REMOTE_SERVER_IP: IP address of the remote server.
  • V_DB_SCHEMA_NAME: Name of the schema.
  • V_DB_SCHEMA_PASS: Password of the schema.

9.2 Define a Timestamp

Define a timestamp so you always know when the backup was made.
 
Shell

  • V_TIMESTAMP: Timestamp in the format DATE_YYYY_MM_DD_TIME_HH_MM_SS.

9.3 Define Remote Server Credentials

Define the credentials for the remote server.
 
Shell

  • V_USER_NAME: Username for the remote server.
  • V_USER_PASS: Password for the remote server.

9.4 Set Additional Variables

Set additional variables that will be used later in the script.
 
Shell

  • DB_DIRECTORY: Directory for the export.
  • DB_NAME: Name of the container database.
  • REMOTE_BACKUP_PATH: Path to the backup directory in the container.
  • VOLUME_REMOTE_PATH: Path to the Docker volume on the host system.

9.5 Set Jenkins Workspace Path and Create Backup Folder

Navigate to the correct path of the Jenkins workspace and create a new folder for the backup.
 
Shell

  • cd $WORKSPACE: Change to the Jenkins workspace directory.
  • mkdir "$BACKUP_FOLDER_NAME": Create a new directory with the timestamp in the name.
  • cd "$BACKUP_FOLDER_NAME": Change to the newly created directory.

9.6 Connect to the Remote Server

Log into the remote server.
 
Shell

  • sshpass -p $V_USER_PASS: Pass the password for the SSH connection.
  • ssh -o StrictHostKeyChecking=no: Disable host key checking.
  • $V_USER_NAME@$V_REMOTE_SERVER_IP: Log in to the remote server.

9.7 Open the Docker Container and Create a Backup

Open the Docker container and execute the Oracle database dump command to start a backup of the database.
 
Shell

  • docker exec database bash -c: Execute a command in the Docker container.
  • expdp "$V_DB_SCHEMA_NAME"/"$V_DB_SCHEMA_PASS"@"$DB_NAME" schemas="$V_DB_SCHEMA_NAME" directory="$DB_DIRECTORY" dumpfile="$V_DB_SCHEMA_NAME""$V_TIMESTAMP".dmp logfile="$V_DB_SCHEMA_NAME""$V_TIMESTAMP".log: Execute the Data Pump Export command.

9.8 Copy Backup Files

Copy the created backup files from the remote server to the Jenkins server.
 
Shell

  • scp: Secure copy files over SSH.
  • "$V_USER_NAME@$V_REMOTE_SERVER_IP:"$VOLUME_REMOTE_PATH"/"$V_DB_SCHEMA_NAME"_"$V_TIMESTAMP.dmp": Path to the dump file on the remote server.
  • "$WORKSPACE"/"$BACKUP_FOLDER_NAME": Destination path on the Jenkins server.

10. Complete Script

Here is the complete script for the Jenkins job:
 
Shell

11. Set Up Regular Backups

Now you can set up daily or weekly backups. This way, the backup will be stored both on the database server and on the Jenkins server.
Note: Make sure all paths and credentials are correct and adjust the variables according to your environment.

 {fullWidth}

0 $type={blogger}:

Kommentar veröffentlichen