Oracle RMAN (catalog) full backup script

Changes you need to make are highlighted in yellow. Database name, Oracle home location, user executing the script (oracle user here), and email to at the end. Optional RMAN configuration items such as filesperset, archive log files to keep, and tag name also highlighted.

#!/bin/sh
# ----------------------------------------------------
# Determine the user which is executing this script.
# ----------------------------------------------------

CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`

# Set begin time of script
c_begin_time_sec=`date +%s`

# ----------------------------------------------------
# Put output in .out. Change as desired.
# Note: output directory requires write permission.

# RMAN_LOG_FILE=${0}.out
RMAN_LOG_FILE=${0}.`date +%Y%m%d_%H%M%S`.out

# ----------------------------------------------------
# You may want to delete the output file so that

# backup information does not accumulate.

#  If not, delete the following lines.
# ----------------------------------------------------

if [ -f "$RMAN_LOG_FILE" ]
then
        rm -f "$RMAN_LOG_FILE"
fi

# ----------------------------------------------------
# Initialize the log file.
# ----------------------------------------------------
set NLS_DATE_FORMAT="YYYY/MM/DD HH24:MI:SS"
set echo on
echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE

# ----------------------------------------------------
# Log the start of this script.
# ----------------------------------------------------

echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

# ----------------------------------------------------
# Replace below, with the Oracle home path.
# ----------------------------------------------------

ORACLE_HOME=/oracle/app/
export ORACLE_HOME

# ----------------------------------------------------
# Replace below, with the Oracle SID of

# the target database.
# ----------------------------------------------------

ORACLE_SID=ORCL
export ORACLE_SID

# ----------------------------------------------------
# Replace below, with the Oracle DBA user id (account).
# -----------------------------------------------------

ORACLE_USER=oracle
# -----------------------------------------------------
# Set the target connect string.
# Replace "sys/manager", below,

# with the target connect string.
# -----------------------------------------------------

TARGET_CONNECT_STR=/
CATALOG_CONNECT_STR=catalog_login/password@sid_of_catalog;

# -----------------------------------------------------
# Set the Oracle Recovery Manager name.
# -----------------------------------------------------

RMAN=$ORACLE_HOME/bin/rman

# -----------------------------------------------------
# Print out the value of the variables set by this script.
# -----------------------------------------------------


echo >> $RMAN_LOG_FILE
echo   "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo   "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo   "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo   "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE

# -----------------------------------------------------
# NOTE: This script assumes that the database

# is properly opened. If desired,
# this would be the place to verify that.
# -----------------------------------------------------

echo >> $RMAN_LOG_FILE

#     schedule type is                BACKUP_TYPE is
#     ----------------                --------------
# Automatic Full                     INCREMENTAL LEVEL=0
# Automatic Differential Incremental INCREMENTAL LEVEL=1
# Automatic Cumulative Incremental   INCREMENTAL LEVEL=1 CUMULATIVE
#
# Note that we use incremental level 0 to specify full backups.
# That is because, although they are identical in content, only
# the incremental level 0 backup can have incremental backups of
# level > 0 applied to it.
# ----------------------------------------------------

ORA_FULL=1;
ORA_INCR=0;
ORA_CINC=0;

if [ "$ORA_FULL" = "1" ]
then
    echo "Full backup requested" >> $RMAN_LOG_FILE
    BACKUP_TYPE="INCREMENTAL LEVEL=0"
elif [ "$ORA_INCR" = "1" ]
then
    echo "Differential incremental backup requested" >> $RMAN_LOG_FILE
    BACKUP_TYPE="INCREMENTAL LEVEL=1"

elif [ "$ORA_CINC" = "1" ]
then
    echo "Cumulative incremental backup requested" >> $RMAN_LOG_FILE
    BACKUP_TYPE="INCREMENTAL LEVEL=1 CUMULATIVE"

elif [ "$BACKUP_TYPE" = "" ]
then
    echo "Default - Full backup requested" >> $RMAN_LOG_FILE
    BACKUP_TYPE="INCREMENTAL LEVEL=0"
fi
# -------------------------------------------------
c_begin_time_sec=`date +%s`

CMD_STR="
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target $TARGET_CONNECT_STR catalog $CATALOG_CONNECT_STR msgno append << EOF
RUN {
BACKUP
    $BACKUP_TYPE
    SKIP INACCESSIBLE
    TAG orcldb_full_db_bk
    FILESPERSET 8
    DATABASE;
   sql 'alter system archive log current';
# backup all archive logs and then delete them
BACKUP
    filesperset 8
    ARCHIVELOG ALL;
DELETE NOPROMPT ARCHIVELOG
    UNTIL TIME 'SYSDATE - 1';
}
EOF
"
# Initiate the command string
/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
RSTAT=$?


c_end_time_sec=`date +%s`
echo >> $RMAN_LOG_FILE
v_total_execution_time_sec=`expr ${c_end_time_sec} - ${c_begin_time_sec}`
echo "Script execution time is $((v_total_execution_time_sec / 60)) Minutes" >> $RMAN_LOG_FILE
# echo "Script execution time is $v_total_execution_time_sec" >> $RMAN_LOG_FILE
echo "Wow, that is really good time." >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

# -----------------------------------------------------
# Log the completion of this script.
# -----------------------------------------------------
if [ "$RSTAT" = "0" ]
then
    LOGMSG="ended successfully"
else
    LOGMSG="ended in error"
fi

echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

#
# Email log file and status to DBA
cat $RMAN_LOG_FILE | mailx -s "$((v_total_execution_time_sec / 60)) Mins. ${ORACLE_SID} on `uname -n` $BACKUP_TYPE $LOGMSG - EXIT Status = $RSTAT" yourname@company.com

exit $RSTAT

 

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Oracle 12.1.0.2.0

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

RMAN-04006: error from auxiliary database

Oracle 12.1.0.2.0

RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Instance "dbname", status BLOCKED, has 1 handler(s) for this service...

Oracle duplication on 12c. RMAN-04006: error from auxiliary database. RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Oracle 12.1.0.2.0

RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Instance "dbname", status BLOCKED, has 1 handler(s) for this service...
RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Solution to resolve these errors.

After upgrading my Oracle instance from 11.2 to 12.1 I tried to run the RMAN duplication.

I am using the Backup Duplication method, but I expect the same issues could arise with Active Duplication as well.

Normal preparation for duplication has been preformed. Our target database is in nomount state.

First step from the target database is to connect to the auxiliary using RMAN

RMAN> connect auxiliary sys/xxxxxx@DEV01;
RMAN-00571: =================================
 RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =
RMAN-00571: =================================
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

LSNRCTL> status LISTENER
Instance "dev01", status BLOCKED, has 1 handler(s) for this service...

Solution:

1. Add (UR=A) to your tnsnames.ora
2. SERVICE_NAME entry in the tnsname.ora file should reference just the target SID and not the SID.domain_name.com
3. Add the database as a static reference in the listener.ora file so it would be recognized even if the database was not started (in nomount state).

Example of tnsnames.ora
--------------------------------
UAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = uat.company.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = UAT)(UR=A)
    )
  )

Add static reference in the listener.ora file

Example of listener.ora. Add the following.

SID_LIST_LISTENER =

   (SID_LIST =

     (SID_DESC =

       (GLOBAL_DBNAME = dev01)

       (ORACLE_HOME = /app/oracle/product/12.1.0)

       (SID_NAME = dev01)

     )

  )

This should resolve the inability to connect to the target as an auxiliary while it is in no mount state.

RMAN duplication will now run but there is another issue that may crop up.

Here’s my RMAN duplication output:

…

Oracle instance shut down

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 08/19/2016 13:36:41

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

RMAN>

Solution:

The SID in your listener needs to match the case in your password file. Update DEV01 to dev01.

Password file is orapwdev01.

That should do it. Good luck. I hope this was helpful to you.