Menu based Shell Script to take Oracle Database Backup and Restore on other server


I am going to share the a script which is menu based and can perform full database backup and restore on a new server. This script can be used for regular clones and refreshes. The script is written based on few scenarios. There might be various scenarios and options that can built in the script. I am using duplicate command.

If you need to get paid consultation and development, then feel free to reach out ot me.

Example:

Source DB: TRAIN
Version: 12.1.0.2

Target DB : DEV
Version: 12.1.0.2


I am going to perform clone for DEV using the backup from source db.

Script Menu:




Script:

#!/bin/bash
#set -x

# Function to perform full RMAN backup
perform_backup() {
    # Create directory with today's date
    TODAY_DIR="$(date +'%Y-%m-%d')"
    BACKUP_DIR="$BACKUP_ROOT_DIR/$TODAY_DIR"
    export ORACLE_SID="$SOURCE_DB_SID"
    export PATH=$ORACLE_HOME/bin:$PATH
    mkdir -p $BACKUP_DIR

    echo "Performing full RMAN backup to directory: $BACKUP_DIR..."
    rman target / <<EOF
    RUN {
        ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT '$BACKUP_DIR/${SOURCE_DB_SID}_%U';
        BACKUP SPFILE;
        BACKUP DATABASE PLUS ARCHIVELOG;
        RELEASE CHANNEL ch1;
    }
EOF
#Giving full permission to backup
chmod -R 777 $BACKUP_DIR
}

# Function to restore database to a new server using DUPLICATE
perform_restore() {
    echo "Dropping existing target database..."
    export ORACLE_SID="$TARGET_DB_SID"
    export PATH=$ORACLE_HOME/bin:$PATH
    sqlplus / as sysdba <<EOF
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT EXCLUSIVE;
    alter system enable restricted session;
    EXIT;
EOF
rman target / <<EOF
drop database NOPROMPT;
exit
EOF

#Keeping database down in case its already running
sqlplus / as sysdba <<EOF
    SHUTDOWN ABORT;
    EXIT;
EOF

# Create a dummy pfile in target directory
    echo "Creating a dummy pfile in target directory..."
    echo "DB_NAME='$TARGET_DB_SID'" > "/tmp/init$TARGET_DB_SID.ora"
    echo "DB_FILE_NAME_CONVERT=('/u01/db/TRAIN/data/','/u02/database/data/')" >> "/tmp/init$TARGET_DB_SID.ora"
    echo "LOG_FILE_NAME_CONVERT=('/u01/db/TRAIN/data/','/u02/database/data/')" >> "/tmp/init$TARGET_DB_SID.ora"
    echo "control_files=/u02/database/data/cntrl01.dbf,/u02/database/data/cntrl02.dbf,/u02/database/data/cntrl03.dbf" >> "/tmp/init$TARGET_DB_SID.ora"
    echo "compatible=12.1.0" >> "/tmp/init$TARGET_DB_SID.ora"
    echo  "db_files=512" >> "/tmp/init$TARGET_DB_SID.ora"
    echo "Restoring database to target server using DUPLICATE command..."
        sqlplus / as sysdba <<EOF
    STARTUP NOMOUNT pfile=/tmp/init${TARGET_DB_SID}.ora;
    EXIT;
EOF
    rman auxiliary / <<EOF
    DUPLICATE TARGET DATABASE TO $TARGET_DB_SID
    BACKUP LOCATION '${BACKUP_DIR}'
    NOFILENAMECHECK;
EOF
}

# Function to display menu
display_menu() {
    echo "Select an option:"
    echo "1. Perform RMAN Backup"
    echo "2. Restore Database"
    echo "3. Exit"
}

# Main script
echo "Welcome to Oracle RMAN Backup and Restoration Script!"
echo ""


export ORACLE_HOME=$ORACLE_HOME

while true; do
    display_menu
    read -p "Enter your choice: " choice
    case $choice in
        1)
                # Prompt user for input parameters
                read -p "Enter source database SID: " SOURCE_DB_SID
                echo ""
                read -p "Enter backup root directory: " BACKUP_ROOT_DIR
                read -p "Enter Oracle home directory: " ORACLE_HOME
            perform_backup
            ;;
        2)
                # Prompt user for input parameters
                read -p "Enter target database SID: " TARGET_DB_SID
                echo ""
                read -p "Enter backup location directory: " BACKUP_DIR
                read -p "Enter Oracle home directory: " ORACLE_HOME
            perform_restore
            ;;
        3)
            echo "Exiting script..."
            exit 0
            ;;
        *)
            echo "Invalid choice. Please select a valid option."
            ;;
    esac
    echo ""
done

Steps:

1) On Source DB Create a RMAN backup using the script.



[oracle@ebstraining ~]$ sh full_rman_refresh.sh
Welcome to Oracle RMAN Backup and Restoration Script!

Select an option:
1. Perform RMAN Backup
2. Restore Database
3. Exit
Enter your choice: 1
Enter source database SID: TRAIN

Enter backup root directory: /u02/database/rman_backup ->Values based on  requirement.
Enter Oracle home directory: /u01/db/TRAIN/12.1.0 -> Source DB Oracle Home.
Performing full RMAN backup to directory: /u02/database/rman_backup/2024-05-11...

Recovery Manager: Release 12.1.0.2.0 - Production on Sat May 11 06:44:36 2024

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TRAIN (DBID=1561347130)

RMAN> 2> 3> 4> 5> 6> 
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=57 device type=DISK

Starting backup at 11-MAY-24
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 11-MAY-24
channel ch1: finished piece 1 at 11-MAY-24
piece handle=/u02/database/rman_backup/2024-05-11/TRAIN_1f2qgvul_1_1 tag=TAG20240511T064437 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-MAY-24


Starting backup at 11-MAY-24
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=2 STAMP=1168654708
input archived log thread=1 sequence=6 RECID=3 STAMP=1168655027
input archived log thread=1 sequence=7 RECID=4 STAMP=1168655185
input archived log thread=1 sequence=8 RECID=5 STAMP=1168655503
input archived log thread=1 sequence=9 RECID=6 STAMP=1168656685
input archived log thread=1 sequence=10 RECID=7 STAMP=1168656721
input archived log thread=1 sequence=11 RECID=8 STAMP=1168657039
input archived log thread=1 sequence=12 RECID=9 STAMP=1168670678
channel ch1: starting piece 1 at 11-MAY-24
channel ch1: finished piece 1 at 11-MAY-24
piece handle=/u02/database/rman_backup/2024-05-11/TRAIN_1g2qgvun_1_1 tag=TAG20240511T064438 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-MAY-24

Starting backup at 11-MAY-24
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00019 name=/u01/db/TRAIN/data/system13.dbf
input datafile file number=00022 name=/u01/db/TRAIN/data/system16.dbf
input datafile file number=00025 name=/u01/db/TRAIN/data/system19.dbf
input datafile file number=00028 name=/u01/db/TRAIN/data/system22.dbf
input datafile file number=00031 name=/u01/db/TRAIN/data/system25.dbf
input datafile file number=00034 name=/u01/db/TRAIN/data/a_ref06.dbf
input datafile file number=00392 name=/u01/db/TRAIN/data/a_txn_data01.dbf
input datafile file number=00404 name=/u01/db/TRAIN/data/a_txn_ind03.dbf
input datafile file number=00403 name=/u01/db/TRAIN/data/a_txn_ind02.dbf
input datafile file number=00394 name=/u01/db/TRAIN/data/a_ref01.dbf
input datafile file number=00396 name=/u01/db/TRAIN/data/a_summ01.dbf
input datafile file number=00393 name=/u01/db/TRAIN/data/a_txn_ind01.dbf
input datafile file number=00002 name=/u01/db/TRAIN/data/system02.dbf
input datafile file number=00005 name=/u01/db/TRAIN/data/system05.dbf
input datafile file number=00035 name=/u01/db/TRAIN/data/undo02.dbf
input datafile file number=00351 name=/u01/db/TRAIN/data/system07.dbf
input datafile file number=00008 name=/u01/db/TRAIN/data/a_queue02.dbf
input datafile file number=00399 name=/u01/db/TRAIN/data/a_queue01.dbf
channel ch1: starting piece 1 at 11-MAY-24
channel ch1: finished piece 1 at 11-MAY-24
piece handle=/u02/database/rman_backup/2024-05-11/TRAIN_1h2qgvuo_1_1 tag=TAG20240511T064440 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:45
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00020 name=/u01/db/TRAIN/data/system14.dbf
input datafile file number=00023 name=/u01/db/TRAIN/data/system17.dbf
input datafile file number=00026 name=/u01/db/TRAIN/data/system20.dbf
input datafile file number=00029 name=/u01/db/TRAIN/data/system23.dbf
input datafile file number=00032 name=/u01/db/TRAIN/data/a_ref04.dbf
input datafile file number=00379 name=/u01/db/TRAIN/data/undo01.dbf
input datafile file number=00405 name=/u01/db/TRAIN/data/a_txn_ind04.dbf
input datafile file number=00400 name=/u01/db/TRAIN/data/a_media01.dbf
input datafile file number=00013 name=/u01/db/TRAIN/data/system12.dbf
input datafile file number=00352 name=/u01/db/TRAIN/data/system09.dbf
input datafile file number=00407 name=/u01/db/TRAIN/data/a_ref02.dbf
input datafile file number=00395 name=/u01/db/TRAIN/data/a_int01.dbf
input datafile file number=00288 name=/u01/db/TRAIN/data/system10.dbf
input datafile file number=00003 name=/u01/db/TRAIN/data/system03.dbf
input datafile file number=00011 name=/u01/db/TRAIN/data/sysaux01.dbf
input datafile file number=00398 name=/u01/db/TRAIN/data/a_archive01.dbf
input datafile file number=00012 name=/u01/db/TRAIN/data/apps_ts_tools01.dbf
input datafile file number=00397 name=/u01/db/TRAIN/data/a_nolog01.dbf
input datafile file number=00017 name=/u01/db/TRAIN/data/a_int02.dbf
input datafile file number=00007 name=/u01/db/TRAIN/data/owad01.dbf
channel ch1: starting piece 1 at 11-MAY-24
channel ch1: finished piece 1 at 11-MAY-24
piece handle=/u02/database/rman_backup/2024-05-11/TRAIN_1i2qh021_1_1 tag=TAG20240511T064440 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:45
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00021 name=/u01/db/TRAIN/data/system15.dbf
input datafile file number=00024 name=/u01/db/TRAIN/data/system18.dbf
input datafile file number=00027 name=/u01/db/TRAIN/data/system21.dbf
input datafile file number=00030 name=/u01/db/TRAIN/data/system24.dbf
input datafile file number=00033 name=/u01/db/TRAIN/data/a_ref05.dbf
input datafile file number=00406 name=/u01/db/TRAIN/data/a_txn_ind05.dbf
input datafile file number=00353 name=/u01/db/TRAIN/data/system08.dbf
input datafile file number=00014 name=/u01/db/TRAIN/data/a_txn_data04.dbf
input datafile file number=00401 name=/u01/db/TRAIN/data/a_txn_data02.dbf
input datafile file number=00402 name=/u01/db/TRAIN/data/a_txn_data03.dbf
input datafile file number=00354 name=/u01/db/TRAIN/data/system11.dbf
input datafile file number=00015 name=/u01/db/TRAIN/data/a_txn_ind06.dbf
input datafile file number=00001 name=/u01/db/TRAIN/data/system01.dbf
input datafile file number=00004 name=/u01/db/TRAIN/data/system04.dbf
input datafile file number=00018 name=/u01/db/TRAIN/data/sysaux02.dbf
input datafile file number=00295 name=/u01/db/TRAIN/data/system06.dbf
input datafile file number=00016 name=/u01/db/TRAIN/data/a_ref03.dbf
input datafile file number=00314 name=/u01/db/TRAIN/data/portal01.dbf
input datafile file number=00006 name=/u01/db/TRAIN/data/ctxd01.dbf
input datafile file number=00010 name=/u01/db/TRAIN/data/olap.dbf
input datafile file number=00009 name=/u01/db/TRAIN/data/odm.dbf
channel ch1: starting piece 1 at 11-MAY-24
channel ch1: finished piece 1 at 11-MAY-24
piece handle=/u02/database/rman_backup/2024-05-11/TRAIN_1j2qh05a_1_1 tag=TAG20240511T064440 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:45
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 11-MAY-24
channel ch1: finished piece 1 at 11-MAY-24
piece handle=/u02/database/rman_backup/2024-05-11/TRAIN_1k2qh08j_1_1 tag=TAG20240511T064440 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-MAY-24

Starting backup at 11-MAY-24
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=10 STAMP=1168670997
channel ch1: starting piece 1 at 11-MAY-24
channel ch1: finished piece 1 at 11-MAY-24
piece handle=/u02/database/rman_backup/2024-05-11/TRAIN_1l2qh08l_1_1 tag=TAG20240511T064957 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-MAY-24

released channel: ch1

RMAN> 

Recovery Manager complete.

Select an option:
1. Perform RMAN Backup
2. Restore Database
3. Exit
Enter your choice: 3
Exiting script...

2) Copy the backup pieces to the target server and run script from target server. Make sure Oracle Home is already present 

The script will automatically drop and restore the database. Once restore done please start with your pfile as required and proceed with you database activities.


[oradev@ebstraining tmp]$ sh full_rman_refresh.sh
Welcome to Oracle RMAN Backup and Restoration Script!

Select an option:
1. Perform RMAN Backup
2. Restore Database
3. Exit
Enter your choice: 2
Enter target database SID: DEV

Enter backup location directory: /u02/database/rman_backup/2024-05-11/
Enter Oracle home directory: /u02/database/DEV/12.1.0
Dropping existing target database...

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 11 07:05:17 2024

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2923488 bytes
Variable Size             226493472 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes
Database mounted.
SQL> 
System altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Recovery Manager: Release 12.1.0.2.0 - Production on Sat May 11 07:05:52 2024

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEV (DBID=4247004201, not open)

RMAN> 
database name is "DEV" and DBID is 4247004201
database dropped

RMAN> 

Recovery Manager complete.

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 11 07:06:01 2024

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> ORACLE instance shut down.
SQL> Disconnected
Creating a dummy pfile in target directory...
Restoring database to target server using DUPLICATE command...

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 11 07:06:05 2024

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2923488 bytes
Variable Size             226493472 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Recovery Manager: Release 12.1.0.2.0 - Production on Sat May 11 07:06:08 2024

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: DEV (not mounted)

RMAN> 2> 3> 
Starting Duplicate Db at 11-MAY-24

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     285212672 bytes

Fixed Size                     2923488 bytes
Variable Size                226493472 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''TRAIN'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''DEV'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u02/database/rman_backup/2024-05-11/TRAIN_1k2qh08j_1_1';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''TRAIN'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DEV'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     285212672 bytes

Fixed Size                     2923488 bytes
Variable Size                226493472 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes

Starting restore at 11-MAY-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=3 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/database/data/cntrl01.dbf
output file name=/u02/database/data/cntrl02.dbf
output file name=/u02/database/data/cntrl03.dbf
Finished restore at 11-MAY-24

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=3 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=74 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=146 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=217 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=288 device type=DISK


3) Verify the database

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DEV       READ WRITE








Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment