RMAN Backup Script

## Mohamed Fowjil

## RMAN Database backup

#!/bin/bash
# Configuration
ORACLE_HOME=/u01/app/oracle/product/19c/db_1
ORACLE_SID=DBSPRD
RMAN_LOG_DIR=/home/oracle/logs
RMAN_BACKUP_BASE_DIR=/BACKUP/DBSPRD/
##EMAIL_RECIPIENT="fowjil@abc.ae"
BACKUP_TYPE=$1
DATE_FORMAT=$(date +%Y%m%d)
CURRENT_BACKUP_DIR="$RMAN_BACKUP_BASE_DIR/$DATE_FORMAT"
##LOG_FILE="$RMAN_LOG_DIR/rman_backup_${DATE_FORMAT}.log"
LOG_FILE="$RMAN_LOG_DIR/rman_backup_${BACKUP_TYPE}_${DATE_FORMAT}.log"
PARALLELISM=6
MAXPIECESIZE=25G

# Export Oracle environment variables
export ORACLE_HOME ORACLE_SID
# Validate backup type
if [[ "$BACKUP_TYPE" != "full" && "$BACKUP_TYPE" != "INC0" && "$BACKUP_TYPE" != "INC1" && "$BACKUP_TYPE" != "archivelog" ]]; then
    echo "Invalid backup type specified. Use 'full', 'incremental', or 'archivelog'."
    exit 1
fi
# Create necessary directories if they don't exist
mkdir -p $RMAN_LOG_DIR $CURRENT_BACKUP_DIR
# Function to send email
send_email() {
    SUBJECT=$1
    BODY=$2
    echo -e "$BODY" | mailx -s "$SUBJECT" $EMAIL_RECIPIENT
 }
# Function to perform RMAN backup based on type
perform_rman_backup() {
    case $BACKUP_TYPE in
        full)
            BACKUP_CMD="BACKUP DATABASE TAG 'FULL BACKUP'; BACKUP ARCHIVELOG ALL FORMAT '${CURRENT_BACKUP_DIR}/ARC_%d_%Y%M%D_%U.bkp' DELETE ALL INPUT;"
            ;;
        INC0)
            BACKUP_CMD="BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INC0 BACKUP'; BACKUP ARCHIVELOG ALL FORMAT '${CURRENT_BACKUP_DIR}/ARC_%d_%Y%M%D_%U.bkp' DELETE ALL INPUT; "
            ;;
        INC1)
            BACKUP_CMD="BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC1 BACKUP'; BACKUP ARCHIVELOG ALL FORMAT '${CURRENT_BACKUP_DIR}/ARC_%d_%Y%M%D_%U.bkp' DELETE ALL INPUT; "
            ;;
        archivelog)
            BACKUP_CMD="BACKUP ARCHIVELOG ALL FORMAT '${CURRENT_BACKUP_DIR}/ARC_%d_%Y%M%D_%U.bkp' DELETE ALL INPUT;"
            ;;
    esac
    $ORACLE_HOME/bin/rman target / <<EOF > $LOG_FILE
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE DEVICE TYPE DISK PARALLELISM $PARALLELISM;
RUN {
    ALLOCATE CHANNEL C1 TYPE DISK FORMAT '${CURRENT_BACKUP_DIR}/%d_%Y%M%D_%U.bkp' MAXPIECESIZE ${MAXPIECESIZE};
    ALLOCATE CHANNEL C2 TYPE DISK FORMAT '${CURRENT_BACKUP_DIR}/%d_%Y%M%D_%U.bkp' MAXPIECESIZE ${MAXPIECESIZE};
    ALLOCATE CHANNEL C3 TYPE DISK FORMAT '${CURRENT_BACKUP_DIR}/%d_%Y%M%D_%U.bkp' MAXPIECESIZE ${MAXPIECESIZE};
    ALLOCATE CHANNEL C4 TYPE DISK FORMAT '${CURRENT_BACKUP_DIR}/%d_%Y%M%D_%U.bkp' MAXPIECESIZE ${MAXPIECESIZE};
    ALLOCATE CHANNEL C5 TYPE DISK FORMAT '${CURRENT_BACKUP_DIR}/%d_%Y%M%D_%U.bkp' MAXPIECESIZE ${MAXPIECESIZE};
    ALLOCATE CHANNEL C6 TYPE DISK FORMAT '${CURRENT_BACKUP_DIR}/%d_%Y%M%D_%U.bkp' MAXPIECESIZE ${MAXPIECESIZE};
        CROSSCHECK BACKUP;
        CROSSCHECK ARCHIVELOG ALL;
        SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
        $BACKUP_CMD
        BACKUP FORMAT '${CURRENT_BACKUP_DIR}/ctrlfile_%d_%Y%M%D_%U.bkp' CURRENT CONTROLFILE;
        BACKUP SPFILE FORMAT '${CURRENT_BACKUP_DIR}/spfile_%d_%Y%M%D_%U.bkp' ;
        REPORT OBSOLETE;
        DELETE NOPROMPT OBSOLETE;
    RELEASE CHANNEL C1 ;
    RELEASE CHANNEL C2 ;
    RELEASE CHANNEL C3 ;
    RELEASE CHANNEL C4 ;
    RELEASE CHANNEL C5 ;
    RELEASE CHANNEL C6 ;
}
EOF
}
# Perform the backup
perform_rman_backup
# Function to delete old backups
cleanup_old_empty_dir() {
    find $RMAN_BACKUP_BASE_DIR -maxdepth 1 -type d -empty -delete;
}
# Check RMAN backup status
if grep -q "ORA-" $LOG_FILE; then
##    send_email "RMAN Backup Failed" "RMAN $BACKUP_TYPE backup failed. Check the log file for details: $LOG_FILE"
    exit 1
else
 ##   send_email "RMAN Backup Successful" "RMAN $BACKUP_TYPE backup completed successfully. Check the log file for details: $LOG_FILE"
        # Delete old backups empty directories if current backup is successful
    cleanup_old_empty_dir
fi

-------------------------------------------------------------------------------------------------------------

Usage:

sh rman_backup.sh full

sh rman_backup.sh archivelog

User DDL

set termout off
set linesize 19000
set pages 50000
set feedback off
set trim on
set echo off
set serveroutput on
set long 99999999
set longchunksize 20000 pagesize 0
column Extracted_DDL for a1000
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
select (case
when ((select count(*)
from dba_users
where username = '$dbuser' and profile <> 'DEFAULT') > 0)
then chr(10)||' -- Note: Profile'||(select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.username = '$dbuser')
else to_clob (chr(10)||' -- Note: Default profile, no need to create!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_users
where username = '$dbuser') > 0)
then ' -- Note: Create user statement'||dbms_metadata.get_ddl ('USER', '$dbuser')
else to_clob (chr(10)||' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = '$dbuser') > 0)
then ' -- Note: TBS quota'||dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '$dbuser')
else to_clob (chr(10)||' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '$dbuser') > 0)
then ' -- Note: Roles'||dbms_metadata.get_granted_ddl ('ROLE_GRANT', '$dbuser')
else to_clob (chr(10)||' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from V\$PWFILE_USERS
where username = '$dbuser' and SYSDBA='TRUE') > 0)
then ' -- Note: sysdba'||chr(10)||to_clob (' GRANT SYSDBA TO '||'"'||'$dbuser'||'"'||';')
else to_clob (chr(10)||' -- Note: No sysdba administrative Privilege found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = '$dbuser') > 0)
then ' -- Note: System Privileges'||dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '$dbuser')
else to_clob (chr(10)||' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = '$dbuser') > 0)
then ' -- Note: Object Privileges'||dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '$dbuser')
else to_clob (chr(10)||' -- Note: No Object Privileges found!')
end ) from dual;

Gather Stats Script

 -- -----------------------------------------------------------------------------------
-- File Name    : gather_stats_script.sql
-- Author       : Mohamed Fowjil
-- Description  : Gathers statistics for the database using DBMS_STATS procedures.
-- Last Modified: 2024-07-01
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
-- Define variables for timing
VARIABLE start_time VARCHAR2(100);
VARIABLE end_time VARCHAR2(100);
-- Get current timestamp as start time
BEGIN
   SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') INTO :start_time FROM DUAL;
   DBMS_OUTPUT.PUT_LINE('Start Time: ' || :start_time);
END;
/
-- Spool start time to separate log file
SPOOL gather_stats_log.txt
SELECT 'Start Time: ' || :start_time FROM DUAL;
SPOOL OFF
-- Set global preferences for DBMS_STATS
EXEC DBMS_STATS.SET_GLOBAL_PREFS('DEGREE', 8);
EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'AUTOMATIC');
-- Gather dictionary stats
BEGIN
   DBMS_STATS.GATHER_DICTIONARY_STATS();
   DBMS_OUTPUT.PUT_LINE('Gathered dictionary stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering dictionary stats: ' || SQLERRM);
END;
/
-- Gather fixed objects stats
BEGIN
   DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();
   DBMS_OUTPUT.PUT_LINE('Gathered fixed objects stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering fixed objects stats: ' || SQLERRM);
END;
/
-- Gather database stats
DECLARE
   l_options VARCHAR2(200);
BEGIN
   l_options := 'GATHER STALE';
   DBMS_STATS.GATHER_DATABASE_STATS(
      options => l_options,
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
      granularity => 'ALL',
      cascade => TRUE,
      degree => 8,
      no_invalidate => DBMS_STATS.AUTO_INVALIDATE
   );
   DBMS_OUTPUT.PUT_LINE('Gathered database stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering database stats: ' || SQLERRM);
END;
/
-- Get current timestamp as end time
BEGIN
   SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') INTO :end_time FROM DUAL;
   DBMS_OUTPUT.PUT_LINE('End Time: ' || :end_time);
END;
/

--SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON
SET TIMING ON
SET HEADING ON

SRVCTL Commands

 Usage: srvctl {-version | -version -fullversion | -fullversion}
Usage: srvctl add database -db <db_unique_name> -oraclehome <oracle_home> [-domain <domain_name>] [-spfile <spfile>] [-pwfile <password_file_path>] [-role {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY | FAR_SYNC}] [-startoption <start_options>] [-stopoption <stop_options>] [-dbname <db_name>] [-instance <inst_name>] [-policy {AUTOMATIC | MANUAL | NORESTART | USERONLY}] [-diskgroup "<diskgroup_list>"] [-verbose]
Usage: srvctl config database [-db <db_unique_name> [-all] | -home] [-verbose]
Usage: srvctl start database -db <db_unique_name> [-startoption <start_options>]
Usage: srvctl stop database -db <db_unique_name> [-stopoption <stop_options>] [-drain_timeout <timeout>] [-force]
Usage: srvctl status database {-db <db_unique_name> [-sid] [-home] | -thisversion | -thishome} [-force] [-verbose]
Usage: srvctl enable database -db <db_unique_name>
Usage: srvctl disable database -db <db_unique_name>
Usage: srvctl modify database -db <db_unique_name> [-dbname <db_name>] [-instance <inst_name>] [-oraclehome <oracle_home>] [-user <oracle_user>] [-domain <domain>] [-spfile <spfile>] [-pwfile <password_file_path>] [-role {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-startoption <start_options>] [-stopoption <stop_options>] [-policy {AUTOMATIC | MANUAL | NORESTART | USERONLY}] [-diskgroup "<diskgroup_list>"|-nodiskgroup] [-force]
Usage: srvctl remove database -db <db_unique_name> [-force] [-noprompt] [-verbose]
Usage: srvctl update database -db <db_unique_name> -startoption <start_options>
Usage: srvctl getenv database -db <db_unique_name> [-envs "<name>[,...]"]
Usage: srvctl setenv database -db <db_unique_name> {-envs "<name>=<val>[,...]" | -env "<name>=<val>"}
Usage: srvctl unsetenv database -db <db_unique_name> -envs "<name>[,...]"
Usage: srvctl upgrade database -db <db_unique_name> -oraclehome <oracle_home>
Usage: srvctl downgrade database -db <db_unique_name> -oraclehome <oracle_home> -targetversion <to_version>
Usage: srvctl add service -db <db_unique_name> -service "<service_name_list>"
       [-role [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-policy {AUTOMATIC | MANUAL}]
       [-notification {TRUE | FALSE}] [-clbgoal {SHORT | LONG}] [-rlbgoal {NONE | SERVICE_TIME | THROUGHPUT}]
       [-failovertype {NONE | SESSION | SELECT | TRANSACTION | AUTO}] [-failovermethod {NONE | BASIC}][-failoverretry <failover_retries>] [-failoverdelay <failover_delay>] [-failover_restore {NONE | LEVEL1}]
       [-edition <edition>] [-pdb <pluggable_database>] [-global <TRUE | FALSE>] [-maxlag <max_lag_time>] [-sql_translation_profile <sql_translation_profile>]
       [-commit_outcome {TRUE | FALSE}] [-retention <retention>] [replay_init_time <replay_initiation_time>] [-tablefamilyid <table_family_id>] [-drain_timeout <timeout>]
       [-stopoption <stop_option>] [-session_state {STATIC | DYNAMIC}] [-force]
Usage: srvctl config service -db <db_unique_name> [-service <service_name>] [-verbose]
Usage: srvctl start service -db <db_unique_name> [-service  "<service_name_list>" | -pdb <pluggable_database>] [-startoption <start_options>] [-global_override] [-role] [-verbose]
Usage: srvctl stop service -db <db_unique_name> [-service "<service_name_list>" | -pdb <pluggable_database>] [-drain_timeout <timeout>] [-stopoption <stop_option>] [-global_override] [-wait <wait_option>] [-force] [-verbose]
Usage: srvctl status service -db <db_unique_name> [-service  "<service_name_list>" | -pdb <pluggable_database>] [-force] [-verbose]
Usage: srvctl enable service -db <db_unique_name> -service  "<service_name_list>" [-global_override]
Usage: srvctl disable service -db <db_unique_name> -service  "<service_name_list>" [-global_override]
Usage: srvctl modify service -db <db_unique_name> -service <service_name>
       [-role [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-policy {AUTOMATIC | MANUAL}]
       [-notification {TRUE | FALSE}] [-clbgoal {SHORT | LONG}] [-rlbgoal {NONE | SERVICE_TIME | THROUGHPUT}]
       [-failovertype {NONE | SESSION | SELECT | TRANSACTION | AUTO}] [-failovermethod {NONE | BASIC}] [-failoverretry <integer>] [-failoverdelay <integer>] [-failover_restore {NONE | LEVEL1}]
       [-edition <edition>] [-pdb <pluggable_database>] [-sql_translation_profile <sql_translation_profile>] [-commit_outcome {TRUE | FALSE}]
       [-retention <retention>] [replay_init_time <replay_initiation_time>] [-tablefamilyid <table_family_id>] [-drain_timeout <timeout>] [-stopoption <stop_option>]
       [-session_state {STATIC | DYNAMIC}] [-global_override]
Usage: srvctl remove service -db <db_unique_name> -service <service_name> [-global_override] [-force]
Usage: srvctl add asm [-listener <lsnr_name>] [-spfile <spfile>] [-pwfile <password_file_path>] [-diskstring <asm_diskstring>]
Usage: srvctl config asm [-detail]
Usage: srvctl start asm [-startoption <start_options>]
Usage: srvctl stop asm [-stopoption <stop_options>] [-force]
Usage: srvctl status asm [-all] [-verbose]
Usage: srvctl enable asm
Usage: srvctl disable asm
Usage: srvctl modify asm [-listener <lsnr_name>] [-spfile <spfile>] [-pwfile <password_file_path>] [-diskstring <asm_diskstring>]
Usage: srvctl remove asm [-force]
Usage: srvctl getenv asm [-envs "<name>[,...]"]
Usage: srvctl setenv asm {-envs "<name>=<val>[,...]" | -env "<name>=<value>"}
Usage: srvctl unsetenv asm -envs "<name>[,...]"
Usage: srvctl start diskgroup -diskgroup <dg_name>
Usage: srvctl stop diskgroup -diskgroup <dg_name> [-force]
Usage: srvctl status diskgroup -diskgroup <dg_name> [-all] [-verbose]
Usage: srvctl enable diskgroup -diskgroup <dg_name>
Usage: srvctl disable diskgroup -diskgroup <dg_name>
Usage: srvctl remove diskgroup -diskgroup <dg_name> [-force]
Usage: srvctl add listener [-listener <lsnr_name>] [-skip] [-endpoints "[TCP:]<port>[, ...][/IPC:<key>][/NMP:<pipe_name>][/TCPS:<s_port>][/SDP:<port>][/EXADIRECT:<port>]"] [-oraclehome <path>]
Usage: srvctl config listener [-listener <lsnr_name>]
Usage: srvctl start listener [-listener <lsnr_name>]
Usage: srvctl stop listener [-listener <lsnr_name>] [-force]
Usage: srvctl enable listener [-listener <lsnr_name>]
Usage: srvctl disable listener [-listener <lsnr_name>]
Usage: srvctl modify listener [-listener <lsnr_name>] [-oraclehome <path>] [-endpoints "[TCP:]<port>[, ...][/IPC:<key>][/NMP:<pipe_name>][/TCPS:<s_port>][/SDP:<port>][/EXADIRECT:<port>]"]
Usage: srvctl remove listener [-listener <lsnr_name> | -all] [-force]
Usage: srvctl getenv listener [-listener <lsnr_name>] [-envs "<name>[,...]"]
Usage: srvctl setenv listener [-listener <lsnr_name>] -envs "<name>=<val>[,...]" | -env "<name>=<value>"
Usage: srvctl unsetenv listener [-listener <lsnr_name>] -envs "<name>[,...]"
Usage: srvctl start home -oraclehome <oracle_home> -statefile <state_file>
Usage: srvctl stop home -oraclehome <oracle_home> -statefile <state_file> [-stopoption <stop_options>] [-force]
Usage: srvctl status home -oraclehome <oracle_home> -statefile <state_file>
Usage: srvctl add ons [-emport <em_port>] [-onslocalport <ons_local_port>]  [-onsremoteport <ons_remote_port>] [-remoteservers <host>[:<port>][,<host>[:<port>]...]] [-verbose]
Usage: srvctl remove ons [-force] [-verbose]
Usage: srvctl enable ons [-verbose]
Usage: srvctl disable ons [-verbose]
Usage: srvctl modify ons [-emport <em_port>] [-onslocalport <ons_local_port>]  [-onsremoteport <ons_remote_port>] [-remoteservers <host>[:<port>][,<host>[:<port>]...]] [-verbose]
Usage: srvctl config ons
Usage: srvctl status ons [-verbose]
Usage: srvctl start ons [-verbose]
Usage: srvctl stop ons [-verbose]
Usage: srvctl add oraclehome -name <home_name> -path <path>
Usage: srvctl config oraclehome [-name <home_name>]
Usage: srvctl start oraclehome -name <home_name>
Usage: srvctl stop oraclehome -name <home_name> [-force]
Usage: srvctl enable oraclehome -name <home_name>
Usage: srvctl disable oraclehome -name <home_name>
Usage: srvctl status oraclehome -name <home_name>
Usage: srvctl modify oraclehome -name <home_name> -path <path>
Usage: srvctl remove oraclehome -name <home_name> [-force]

Datafile Resize for Specific Tablespace

 DECLARE
  v_tablespace_name VARCHAR2(30) := 'YOUR_TABLESPACE_NAME'; -- Change to your tablespace name
  v_new_size        VARCHAR2(20) := '1024M'; -- New size for each datafile
BEGIN
  FOR rec IN (SELECT file_name
              FROM dba_data_files
              WHERE tablespace_name = v_tablespace_name) 
  LOOP
    EXECUTE IMMEDIATE 'ALTER DATABASE DATAFILE ''' || rec.file_name || ''' RESIZE ' || v_new_size;
  END LOOP;
END;
/