How To: Custom Backup and Restore using Oracle Flashback Database for PDB

package com.datical.hammer.scripts.backup
  
import com.datical.db.project.DatabaseDef
import com.datical.db.project.Project
import com.datical.hammer.repl.command.DaticalDBHelper
import com.datical.hammer.scripts.util.PackagerProperties
import com.datical.hammer.scripts.util.ScriptLogging
import com.datical.hammer.scripts.util.ScriptUtils
import groovy.sql.Sql
import org.apache.commons.lang3.StringUtils
  
import java.sql.Connection
import java.text.SimpleDateFormat
import java.util.regex.Pattern
  
/*********************************************************************************************************************
 * Has methods to perform Oracle database backup and restore using Guaranteed Restore points
 * and flashback database.
 * SQLplus must be available on the OS Path. The database must be in archivelog mode, and the database account used to
 * connect to the database must have SYSDBA privileges.
 * SQL operations are performed by shell scripts. The connect string is passed as a parameter to the shell scripts.
 *
 ********************************************************************************************************************/
class CustomBackupRestoreOraclePDBFlashback implements BackupRestore {
  private ScriptLogging logger = new ScriptLogging('CustomBackupRestoreOraclePDBFlashback')
  private ScriptUtils scriptUtils = new ScriptUtils()
  private DaticalDBHelper daticalDB
  String defaultExportFilename
  String defaultExportLogFilename
  String jobNameBase
    
  Boolean windowsOS = false;
  
  CustomBackupRestoreOraclePDBFlashback(DaticalDBHelper daticalDB) {
    this.daticalDB = daticalDB
    def startTime = new Date()
    def timestamp            = new SimpleDateFormat("yyyyMMdd_HHmmss_S").format(startTime)
    defaultExportFilename    = ""
    defaultExportLogFilename = ""
    def jobnameTimestamp     = new SimpleDateFormat("yyyyMMdd_HHmmss").format(startTime)
    // The jobNameBase cannot be longer than 30 characters on Oracle
    jobNameBase              = "datical_pkg_${jobnameTimestamp}"
      
    // If on Windows...
    if (File.separator == "\\") {
      logger.printInfo "Detected Windows operating system"
      windowsOS = true;
    } else {
      logger.printInfo "Detected Non-Windows operating system"
    }
    logger.debug "  using CustomBackupRestoreOracleFlashback"
  }
  
  
  /***************************************************************************************************************
   * Create a Guaranteed Restore Point in dbDef. The restore point is named DATICAL_BACKUP.  It is dropped upon successful flashback.
   * Returns a list of [Integer, StringBuffer, StringBuffer], where the Integer is a return code (0 is success, non-zero is an error),
   * the first StringBuffer contains the stderr from any external process, and the second StringBuffer contains the stdout from any external process.
   *
   * @param dbDef               A DatabaseDef object that can be used to create a connection to a database server.
   *                            The dbDef will be automatically constructed based on the name supplied by the
   *                            user on the command line.
   * @param schemaList          NOT USED.  A List of Strings, where each String is the name of a schema to backup. This list
   *                            is created by examining the datical.project file, or can be supplied by the user
   *                            on the command line. It may also be calculated by deploy packager from entries in
   *                            various metadata.properties files.
   * @param filename            NOT USED.  This is a String used to name the backup file. This value is calculated in the
   *                            method getFilename(),
   * @return                    A List of [Integer, StringBuffer, StringBuffer]
   * *************************************************************************************************************/
  @Override
  List runBackup(DatabaseDef dbDef, List<String> schemaList, String filename) {
    def rc
    def stdout = new StringBuffer()
    def stderr = new StringBuffer()
    logger.printInfo "Running Backup using CustomBackupRestoreOracleFlashback"
  
    try {
      
      ProcessBuilder processBuilder = new ProcessBuilder("")
      def commands = processBuilder.command()
      commands.clear()
        
      String connectString
      String printableConnectString
        
      logger.printInfo "Connectivity based on URL ${dbDef.url}"
        
      if (StringUtils.isNotBlank(dbDef.tnsName)) {
         connectString          = "${dbDef.username}/\"${dbDef.password}\"@${dbDef.tnsName}"
         printableConnectString = "${dbDef.username}/\"***********************\"@${dbDef.tnsName}"
      }
      else if (StringUtils.isNotBlank(dbDef.sid)) {
         connectString          = "${dbDef.username}/\"${dbDef.password}\":@${dbDef.hostname}:${dbDef.port}:${dbDef.sid}"
         printableConnectString = "${dbDef.username}/\"***********************\":@${dbDef.hostname}:${dbDef.port}:${dbDef.sid}"
      }
      else if (StringUtils.isNotBlank(dbDef.serviceName)) {
         connectString          = "${dbDef.username}/\"${dbDef.password}\"@${dbDef.hostname}:${dbDef.port}/${dbDef.serviceName}"
         printableConnectString = "${dbDef.username}/\"***********************\"@${dbDef.hostname}:${dbDef.port}/${dbDef.serviceName}"
      } else {
         logger.printError "No TNSName, SID, or ServiceName was specified in the connection (${dbDef.name})."
         return [1,null,null]
      }
        
      String[] restorepointOptions = ["", ""];
      String restorepointCommand = "BackupRestoreScripts/datical_custom_create_restore_point.sh"
      if (windowsOS) {
        restorepointCommand = "BackupRestoreScripts\\datical_custom_create_restore_point.bat"
      }
        
      commands.add(restorepointCommand.toString())
      commands.add(connectString.toString())
	  commands.add(dbDef.serviceName.toString())
        
      restorepointOptions.eachWithIndex { item, index -> commands.add(item.toString()) }
  
      logger.printInfo "executing ${restorepointCommand} command:\n " + "${restorepointCommand} ${printableConnectString} ${dbDef.serviceName} ${restorepointOptions.join(" ")}"
  
      Process proc = processBuilder.start()
      proc.waitForProcessOutput(stdout, stderr)
      rc = proc.exitValue()
        
    }
    catch (Exception ex) {
      stderr = ex
      rc = 1
    }
  
    def message = """
       rc    : ${rc}
       stderr: ${stderr}
       stdout: ${stdout}
----------------------------- end of CREATE_RESTORE_POINT output ---------------------------------
"""
  
    if (rc != 0) {
      logger.printError message
    }
    else {
      logger.printInfo stdout.toString()
      logger.printInfo "  Successfully created restore point for '${dbDef.name}'"
    }
    return [rc,stderr,stdout]
  }
  
  
  /****************************************************************************************
   * Restore the database specified by the dbDef using flashback database.
   * Returns a list of [Integer, StringBuffer, StringBuffer], where the Integer is a return code
   * (0 is success, non-zero is an error), the first StringBuffer contains the stderr from any external process,
   * and the second StringBuffer contains the stdout from any external process.
   *
   * @param dbDef               A DatabaseDef object that can be used to create a connection to a database server.
   *                            The dbDef will be automatically constructed based on the name supplied by the
   *                            user on the command line.
   * @param schemaList          NOT USED.  A List of Strings, where each String is the name of a schema to backup. This list
   *                            is created by examining the datical.project file, or can be supplied by the user
   *                            on the command line. It may also be calculated by deploy packager from entries in
   *                            various metadata.properties files.
   * @param filename            NOT USED.  This is a string that should be used to find the file that will be restored. This value
   *                            is specified by the user on the command line with the option 'restore=filename'
   *
   * @return                    A List of [Integer, StringBuffer, StringBuffer]
   *
   * *************************************************************************************************************/
  @Override
  List runRestore(DatabaseDef dbDef, List<String> schemaList, String filename) {
    def rc
    def stdout = new StringBuffer()
    def stderr = new StringBuffer()
    logger.printInfo "Running Restore using CustomBackupRestoreOracleFlashback"
  
    try {
  
      ProcessBuilder processBuilder = new ProcessBuilder("")
      def commands = processBuilder.command()
      commands.clear()
         
      String connectString
      String printableConnectString
        
      logger.printInfo "Connectivity based on URL ${dbDef.url}"
        
      if (StringUtils.isNotBlank(dbDef.tnsName)) {
         connectString          = "${dbDef.username}/\"${dbDef.password}\"@${dbDef.tnsName}"
         printableConnectString = "${dbDef.username}/\"***********************\"@${dbDef.tnsName}"
      }
      else if (StringUtils.isNotBlank(dbDef.sid)) {
         connectString          = "${dbDef.username}/\"${dbDef.password}\":@${dbDef.hostname}:${dbDef.port}:${dbDef.sid}"
         printableConnectString = "${dbDef.username}/\"***********************\":@${dbDef.hostname}:${dbDef.port}:${dbDef.sid}"
      }
      else if (StringUtils.isNotBlank(dbDef.serviceName)) {
         connectString          = "${dbDef.username}/\"${dbDef.password}\"@${dbDef.hostname}:${dbDef.port}/${dbDef.serviceName}"
         printableConnectString = "${dbDef.username}/\"***********************\"@${dbDef.hostname}:${dbDef.port}/${dbDef.serviceName}"
      } else {
         logger.printError "No TNSName, SID, or ServiceName was specified in the connection (${dbDef.name})."
         return [1,null,null]
      }
  
      String[] flashbackOptions = ["", ""];
      String flashbackCommand = "BackupRestoreScripts/datical_custom_restore_point_flashback.sh"
      if (windowsOS) {
         flashbackCommand = "BackupRestoreScripts\\datical_custom_restore_point_flashback.bat"
      }
        
      commands.add(flashbackCommand.toString())
      commands.add(connectString.toString())
	  commands.add(dbDef.serviceName.toString())
        
      flashbackOptions.eachWithIndex { item, index -> commands.add(item.toString()) }
  
      //logger.printInfo "executing ${flashbackCommand} command:\n " + "${flashbackCommand} ${printableConnectString} ${dbDef.serviceName} ${flashbackOptions.join(" ")}"
  
      Process proc = processBuilder.start()
      proc.waitForProcessOutput(stdout, stderr)
      rc = proc.exitValue()
        
    }
    catch (Exception ex) {
      stderr = ex
      rc = 1
    }
  
    def message = """
      rc    : ${rc}
      stderr: ${stderr}
      stdout: ${stdout}
----------------------------- end of FLASHBACK output ---------------------------------
"""
  
    if (rc != 0) {
      logger.printError message
    }
    else {
      logger.printInfo stdout.toString()
      logger.printInfo "  Successful flashback for '${dbDef.name}'"
    }
  
    return [rc,stderr,stdout]
  }
  
  
  /***************************************************************************************************************
   * This method is called just after the runRestore method. It is used to Drop the Restore Point.
   *
   * @param project             This will be populated with the Project object that deployPackager/backup/restore
   *                            is currently working with.
   * @param dbDef               A DatabaseDef object that can be used to create a connection to a database server.
   *                            The dbDef will be automatically constructed based on the name supplied by the
   *                            user on the command line.
   *
   * returns an integer. Zero indicates success, non-zero is failure.
   * *************************************************************************************************************/
  @Override
  int postRestore(Project project, DatabaseDef dbDef) {
      
    def rc
    def stdout = new StringBuffer()
    def stderr = new StringBuffer()
      
    // Add a Sleep Component to ensure Flashback is complete
    // Update this variable to increase or decrease the wait time. Value is in milliseconds.
	int dropRestoreWaitTime = 30000
      
    logger.printInfo "\n*****************************************"
    logger.printInfo "sleeping for ${dropRestoreWaitTime} milliseconds..."
    logger.printInfo "*****************************************\n"
    sleep(dropRestoreWaitTime); 
      
    logger.printInfo "Dropping Restore Point using CustomBackupRestoreOracleFlashback"
  
    try {
  
      ProcessBuilder processBuilder = new ProcessBuilder("")
      def commands = processBuilder.command()
      commands.clear()
         
      String connectString
      String printableConnectString
        
      logger.printInfo "Connectivity based on URL ${dbDef.url}"
        
      if (StringUtils.isNotBlank(dbDef.tnsName)) {
         connectString          = "${dbDef.username}/\"${dbDef.password}\"@${dbDef.tnsName}"
         printableConnectString = "${dbDef.username}/\"***********************\"@${dbDef.tnsName}"
      }
      else if (StringUtils.isNotBlank(dbDef.sid)) {
         connectString          = "${dbDef.username}/\"${dbDef.password}\":@${dbDef.hostname}:${dbDef.port}:${dbDef.sid}"
         printableConnectString = "${dbDef.username}/\"***********************\":@${dbDef.hostname}:${dbDef.port}:${dbDef.sid}"
      }
      else if (StringUtils.isNotBlank(dbDef.serviceName)) {
         connectString          = "${dbDef.username}/\"${dbDef.password}\"@${dbDef.hostname}:${dbDef.port}/${dbDef.serviceName}"
         printableConnectString = "${dbDef.username}/\"***********************\"@${dbDef.hostname}:${dbDef.port}/${dbDef.serviceName}"
      } else {
         logger.printError "No TNSName, SID, or ServiceName was specified in the connection (${dbDef.name})."
         return 1
      }
  
      String[] flashbackOptions = ["", ""];
      String flashbackCommand = "BackupRestoreScripts/datical_custom_drop_restore_point.sh"
      if (windowsOS) {
         flashbackCommand = "BackupRestoreScripts\\datical_custom_drop_restore_point.bat"
      }
        
      commands.add(flashbackCommand.toString())
      commands.add(connectString.toString())
	  commands.add(dbDef.serviceName.toString())
        
      flashbackOptions.eachWithIndex { item, index -> commands.add(item.toString()) }
  
      logger.printInfo "executing ${flashbackCommand} command:\n " + "${flashbackCommand} ${printableConnectString} ${dbDef.serviceName} ${flashbackOptions.join(" ")}"
  
      Process proc = processBuilder.start()
      proc.waitForProcessOutput(stdout, stderr)
      rc = proc.exitValue()
  
    }
    catch (Exception ex) {
      stderr = ex
      rc = 1
    }
  
    def message = """
      rc    : ${rc}
      stderr: ${stderr}
      stdout: ${stdout}
----------------------------- end of FLASHBACK output ---------------------------------
"""
  
    if (rc != 0) {
      logger.printError message
    }
    else {
      logger.printInfo stdout.toString()
      logger.printInfo "  Successful drop of restore point for '${dbDef.name}'"
    }
  
    return rc
  }
  
  /***************************************************************************************************************
   * The DatabaseBackupRestore class calls this method to allow an engine to return the name of the file to be
   * used for backup, restore, and verify operations. This implementation does nothing.  An enhancement could be to look at
   * deployPackager.properties for the property databaseBackupRestoreLocation and use that value for the name
   * of the Guaranteed Restore point.
   *
   * @param project             This will be populated with the Project object that deployPackager/backup/restore
   *                            is currently working with.
   * @param dbDef               A DatabaseDef object that can be used to create a connection to a database server.
   *                            The dbDef will be automatically constructed based on the name supplied by the
   *                            user on the command line.
   * @param forBackup           Not used in this implementation.
   *
   * Returns an empty String.
   * *************************************************************************************************************/
  @Override
  String getFilename(Project project, DatabaseDef dbDef, boolean forBackup) {
    return ""
  }
  
  /***************************************************************************************************************
   * Verify that the backup restore class is able to perform the operations. Returns a list of [Integer, StringBuffer, StringBuffer], where
   * the Integer is a return code (0 is success, non-zero is an error), the first StringBuffer contains the
   * stderr from any external process, and the second StringBuffer contains the stdout from any external process.
   *
   * @param dbDef               A DatabaseDef object that can be used to create a connection to a database server.
   *                            The dbDef will be automatically constructed based on the name supplied by the
   *                            user on the command line.
   * @param forBackup           This will be true if the system is preparing to do a backup. It will be false if
   *                            the system is preparing to do a restore.
   * @param filename            A String that refers to the backup file. The value is specified by the user on the
   *                            command line with the option 'backup=filename' or 'restore=filename'. This name
   *
   * This implementation does nothing.  Could be enhanced to verify if the database restore point exists.
   * *************************************************************************************************************/
  @Override
  List verify(DatabaseDef dbDef, boolean forBackup, String filename) {
  int rc = 0
    StringBuffer messages = new StringBuffer()
  
    return [rc, messages.toString()]
   }
  
  
  /***************************************************************************************************************
   * This method is called just prior to the runRestore method. This implementation does nothing.  There is no need to
   * drop the schemas since we will flashback the entire database.
   *
   * @param project             This will be populated with the Project object that deployPackager/backup/restore
   *                            is currently working with.
   * @param dbDef               A DatabaseDef object that can be used to create a connection to a database server.
   *                            The dbDef will be automatically constructed based on the name supplied by the
   *                            user on the command line.
   * @param schemaList          A List of Strings, where each String is the name of a schema to backup. This list
   *                            is created by examining the datical.project file, or can be supplied by the user
   *                            on the command line. It may also be calculated by deploy packager from entries in
   *                            various metadata.properties files.
   * *************************************************************************************************************/
  @Override
  int preRestore(Project project, DatabaseDef dbDef, List<String> schemaList) {
      logger.printInfo "Skipping schema drop, will flashback the database."
      return 0
  }
  
  
} 

@echo off
 
SET CONNECT_STRING=%~1
SET PDB_NAME=%~2
echo "Running datical_custom_create_restore_point.bat for Pluggable Database %PDB_NAME%"
 
sqlplus %CONNECT_STRING% @BackupRestoreScripts\\datical_custom_create_restore_point.sql '%PDB_NAME%'

whenever sqlerror continue
drop restore point DATICAL_BACKUP FOR PLUGGABLE DATABASE &1;

whenever sqlerror exit failure
whenever oserror exit oscode

create restore point DATICAL_BACKUP FOR PLUGGABLE DATABASE &1; 
 
set serveroutput on
declare
    flag integer;
    restore_point_check EXCEPTION;
begin
    dbms_output.put_line('Info: Querying Restore Point DATICAL_BACKUP....');
 
    BEGIN
		SELECT count(*) into flag FROM V$RESTORE_POINT WHERE NAME = 'DATICAL_BACKUP';
 
        IF ( flag > 0 ) THEN
            dbms_output.put_line('Successful Restore Point Created');
        ELSE
            raise restore_point_check;
        END IF;
    END;
 
EXCEPTION
    WHEN restore_point_check THEN
        dbms_output.put_line('Error Message: Restore Point Check Failed');
        RAISE_APPLICATION_ERROR (-20000, 'The Restore Point Check Failed');
    WHEN OTHERS THEN
        dbms_output.put_line('Error Message: Other');
        RAISE_APPLICATION_ERROR (-20001, 'Unknown error from Create Restore Point script');
end;
/
 
exit;

#!/bin/bash
CONNECT_STRING=$1
PDB_NAME=$2
echo "Running datical_custom_create_restore_point.sh for Pluggable Database " + $PDB_NAME
sqlplus /nolog << EOF
whenever sqlerror exit failure
whenever oserror exit oscode
 
connect $CONNECT_STRING

whenever sqlerror continue
drop restore point DATICAL_BACKUP FOR PLUGGABLE DATABASE $PDB_NAME;

whenever sqlerror exit failure;
create restore point DATICAL_BACKUP FOR PLUGGABLE DATABASE $PDB_NAME;

set serveroutput on
declare
    flag integer;
    restore_point_check EXCEPTION;
begin
    dbms_output.put_line('Info: Creating Restore Point DATICAL_BACKUP to Restore Database If Needed.');
  
    BEGIN
        SELECT count(*) into flag FROM V$RESTORE_POINT WHERE NAME = 'DATICAL_BACKUP';
  
        IF ( flag > 0 ) THEN
            dbms_output.put_line('Successful Restore Point Created');
        ELSE
            raise restore_point_check;
        END IF;
    END;
  
EXCEPTION
    WHEN restore_point_check THEN
        dbms_output.put_line('Error Message: Restore Point Check Failed');
        RAISE_APPLICATION_ERROR (-20000, 'The Restore Point Check Failed');
    WHEN OTHERS THEN
        dbms_output.put_line('Error Message: Other');
        RAISE_APPLICATION_ERROR (-20001, 'Unknown error from Create Restore Point script');
end;
/
exit;
EOF

@echo off
 
SET CONNECT_STRING=%~1
SET PDB_NAME=%~2
echo "Running datical_custom_restore_point_flashback.bat for Pluggable Database %PDB_NAME%"

echo "Flashback is using CONNECT STRING: sqlplus C##BBB_BACKUP_USER/BBB_BACKUP_USER_PW@localhost:1521/%PDB_NAME% as sysbackup"

echo exit | sqlplus C##BBB_BACKUP_USER/BBB_BACKUP_USER_PW@localhost:1521/%PDB_NAME% as sysbackup @BackupRestoreScripts\\datical_custom_restore_point_flashback.sql '%PDB_NAME%'

whenever sqlerror exit failure
whenever oserror exit oscode

ALTER PLUGGABLE DATABASE &1 CLOSE IMMEDIATE;
flashback pluggable database &1 to restore point DATICAL_BACKUP;
ALTER PLUGGABLE DATABASE &1 OPEN RESETLOGS;

set serveroutput on
declare
    flag integer;
    restore_point_check EXCEPTION;
begin
    dbms_output.put_line('Info: Querying Restore Point DATICAL_BACKUP....');
 
    BEGIN
        SELECT count(*) into flag FROM V$RESTORE_POINT WHERE NAME = 'DATICAL_BACKUP';
 
        IF ( flag > 0 ) THEN
            dbms_output.put_line('Restore point DATICAL_BACKUP was used to restore database');
        ELSE
            raise restore_point_check;
        END IF;
    END;
 
EXCEPTION
    WHEN restore_point_check THEN
        dbms_output.put_line('Error: Restore Point DATICAL_BACKUP Does not Exist to Restore the Database.');
        RAISE_APPLICATION_ERROR (-20000, 'The Restore Point Check Failed');
    WHEN OTHERS THEN
        dbms_output.put_line('Error Message: Other');
        RAISE_APPLICATION_ERROR (-20001, 'Unknown error from Restore Point Flashback script');
end;
/
 
exit;

#!/bin/bash
CONNECT_STRING=$1
PDB_NAME=$2
echo "Running datical_custom_restore_point_flashback.sh for Pluggable Database " + $PDB_NAME
echo "Flashback is using CONNECT STRING: C##BBB_BACKUP_USER/BBB_BACKUP_USER_PW@localhost:1521/%PDB_NAME% as sysbackup"
sqlplus /nolog << EOF
whenever sqlerror exit failure
whenever oserror exit oscode
connect C##BBB_BACKUP_USER/BBB_BACKUP_USER_PW@localhost:1521/$PDB_NAME as sysbackup

ALTER PLUGGABLE DATABASE $PDB_NAME CLOSE IMMEDIATE;
flashback pluggable database $PDB_NAME to restore point DATICAL_BACKUP;
ALTER PLUGGABLE DATABASE $PDB_NAME OPEN RESETLOGS;

set serveroutput on
declare
    flag integer;
    restore_point_check EXCEPTION;
begin
    dbms_output.put_line('Info: Querying Restore Point DATICAL_BACKUP....');
 
    BEGIN
        SELECT count(*) into flag FROM V$RESTORE_POINT WHERE NAME = 'DATICAL_BACKUP';
 
        IF ( flag > 0 ) THEN
            dbms_output.put_line('Restore point DATICAL_BACKUP was used to restore database');
        ELSE
            raise restore_point_check;
        END IF;
    END;
 
EXCEPTION
    WHEN restore_point_check THEN
        dbms_output.put_line('Error: Restore Point DATICAL_BACKUP Does not Exist to Restore the Database.');
        RAISE_APPLICATION_ERROR (-20000, 'The Restore Point Check Failed');
    WHEN OTHERS THEN
        dbms_output.put_line('Error Message: Other');
        RAISE_APPLICATION_ERROR (-20001, 'Unknown error from Restore Point Flashback script');
end;
/
  
exit;
EOF

@echo off
 
SET CONNECT_STRING=%~1
SET PDB_NAME=%~2
echo "Running datical_custom_drop_restore_point.bat for Pluggable Database %PDB_NAME%"

sqlplus %CONNECT_STRING% @BackupRestoreScripts\\datical_custom_drop_restore_point.sql '%PDB_NAME%'

whenever sqlerror exit failure
whenever oserror exit oscode

drop restore point DATICAL_BACKUP FOR PLUGGABLE DATABASE &1;
 
set serveroutput on
declare
    flag integer;
    restore_point_check EXCEPTION;
begin
    dbms_output.put_line('Info: Querying Restore Point DATICAL_BACKUP....');

    BEGIN
        SELECT count(*) into flag FROM V$RESTORE_POINT WHERE NAME = 'DATICAL_BACKUP';
 
        IF ( flag = 0 ) THEN
            dbms_output.put_line('Restore Point Dropped Successfully');
        ELSE
            raise restore_point_check;
        END IF;
    END;
 
EXCEPTION
    WHEN restore_point_check THEN
        dbms_output.put_line('Error: Database Restore Failed, Please Engage DBA Support Team.');
        RAISE_APPLICATION_ERROR (-20000, 'The Restore Point Check Failed');
    WHEN OTHERS THEN
        dbms_output.put_line('Error Message: Other');
        RAISE_APPLICATION_ERROR (-20001, 'Unknown error from Drop Restore Point script');
end;
/
 
exit;

#!/bin/bash
CONNECT_STRING=$1
PDB_NAME=$2
echo "Running datical_custom_drop_restore_point.sh for Pluggable Database " + $PDB_NAME
sqlplus /nolog << EOF
whenever sqlerror exit failure
whenever oserror exit oscode
connect $CONNECT_STRING

drop restore point DATICAL_BACKUP FOR PLUGGABLE DATABASE $PDB_NAME;

set serveroutput on
declare
    flag integer;
    restore_point_check EXCEPTION;
begin
    dbms_output.put_line('Info: Querying Restore Point DATICAL_BACKUP....');

    BEGIN
        SELECT count(*) into flag FROM V$RESTORE_POINT WHERE NAME = 'DATICAL_BACKUP';
 
        IF ( flag = 0 ) THEN
            dbms_output.put_line('Restore Point Dropped Successfully');
        ELSE
            raise restore_point_check;
        END IF;
    END;
 
EXCEPTION
    WHEN restore_point_check THEN
        dbms_output.put_line('Error: Database Restore Failed, Please Engage DBA Support Team.');
        RAISE_APPLICATION_ERROR (-20000, 'The Restore Point Check Failed');
    WHEN OTHERS THEN
        dbms_output.put_line('Error Message: Other');
        RAISE_APPLICATION_ERROR (-20001, 'Unknown error from Drop Restore Point script');
end;
/
 
exit;
EOF