backup - liamlamth/blog GitHub Wiki

multitenant database backup script

cdb including pdbs 
#!/bin/ksh

SID=$1
LOG=/operation/log/dbbackupFULL_$1_$(date '+%Y%m%d').log


(
if [ -z $1 ]; then
echo "Usage: dbbackupFULL.sh [ORACLE_SID]"
echo "e.g.: dbbackupFULL.sh DB_NAME"
echo "Error" | /usr/bin/mutt -s "Error : running $(basename $0)" -- db_adm
exit 1
fi


if [ $(ps -ef | grep pmon_${SID}$ | awk '{print $1}' | tail -1) != $(whoami) ]; then
echo "Please use Oracle user to execute this script"
echo " Error" | /usr/bin/mutt -s "Error : running $(basename $0)" -- db_adm
exit 1
fi


if [ $(ps -ef | grep pmon_${SID}$ | wc -l) -eq 0 ]; then
echo "[ $(date "+%F %H:%M:%S") ] Database is not running"
echo " Error" | /usr/bin/mutt -s "Error : running $(basename $0)" -- db_adm
exit 1
fi


export PATH=/usr/local/bin:$PATH;
export ORACLE_SID=$SID
export ORACLE_PDB_SID=$SID
export ORAENV_ASK=NO
. oraenv


export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"
ENCRYPT_KEY=$(cat /operation/par/rman_encrypt.db)
CURR_DATE=$(date "+%Y%m%d")
CURR_DATE_LONG=date "+%Y%m%d%H%M%S"
LOCALHOUSEKEEPDATE=date -d "1 day ago" "+%Y%m%d"
REMOTEHOUSEKEEPDATE=date -d "3 day ago" "+%Y%m%d"


DBBKDATADIR=/backup/DB/FULL/$ORACLE_SID/$CURR_DATE/


BACKUPLEVEL=0
BACKUPTAG="_db_full_bk"
ARCHIVELOGTAG="_arc_full_bk"


ssh -q [email protected] "rm -rf /remote/backupfolder/$ORACLE_SID/DB/FULL/*"
ssh -q [email protected] "mkdir -p /remote/backupfolder/$ORACLE_SID/DB/FULL/"
rm -rf /backup/DB/FULL/$ORACLE_SID/$LOCALHOUSEKEEPDATE


if [ ! -d "$DBBKDATADIR" ]; then
echo "[ $(date "+%F %H:%M:%S") ] Folder of $DBBKDATADIR does not exist, create now"
mkdir -p $DBBKDATADIR
fi


echo "[ $(date "+%F %H:%M:%S") ] Start Database hot Backup - ${ORACLE_SID}"


rman << EOF
connect target /;
crosscheck archivelog all;
delete noprompt expired archivelog all;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR device type disk to '${DBBKDATADIR}/${ORACLE_SID}autoctl%F.bkp';
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
configure compression algorithm 'MEDIUM';
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CLEAR;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CLEAR;
CONFIGURE CHANNEL 3 DEVICE TYPE DISK CLEAR;
CONFIGURE CHANNEL 4 DEVICE TYPE DISK CLEAR;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '${DBBKDATADIR}/%d_%T_%t_%s.bkp';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '${DBBKDATADIR}/%d_%T_%t_%s.bkp';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '${DBBKDATADIR}/%d_%T_%t_%s.bkp';
CONFIGURE CHANNEL 4 DEVICE TYPE DISK FORMAT '${DBBKDATADIR}/%d_%T_%t_%s.bkp';
alter system archive log current;
set encryption on identified by ${ENCRYPT_KEY} only;
configure snapshot controlfile name to '${DBBKDATADIR}/control_${ORACLE_SID}_${CURR_DATE_LONG}.ctl';
backup as compressed backupset incremental level ${BACKUPLEVEL} database tag=${ORACLE_SID}${BACKUPTAG} include current controlfile plus archivelog tag=${ORACLE_SID}${ARCHIVELOGTAG};
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
DELETE noprompt ARCHIVELOG ALL BACKED UP 2 TIMES to device type disk;
EOF


if [ $? -ne 0 ]; then
echo "."|/usr/bin/mutt -s "[ $(date "+%F %H:%M:%S") ] [DB Backup] ${ORACLE_SID} DBs Backup Error" -- db_adm
exit 1
else
sqlplus / as sysdba << EOF
create pfile='${DBBKDATADIR}/init_${ORACLE_SID}_${CURR_DATE_LONG}.ora' from spfile;
EOF
echo "[ $(date "+%F %H:%M:%S") ] End Database hot Backup - ${ORACLE_SID}"
fi
) > $LOG


CHKBACKUPLOG=cat $LOG | grep ORA- | wc -l
if [ $CHKBACKUPLOG -ne 0 ]; then
echo "."|/usr/bin/mutt -s "[DB Backup] $1 Backup Error - date +'%Y-%m-%d'" -i ${LOG} -- db_adm
else
echo "."|/usr/bin/mutt -s "[DB Backup] $1 Backup Completed - date +'%Y-%m-%d'" -i ${LOG} -- db_adm
/usr/bin/rsync -avSz --delete --stats --human-readable -e "ssh -p 22 -o StrictHostKeyChecking=no" /backup/DB/FULL/$ORACLE_SID/ admin@192.168.1.99:/remote/backupfolder/$ORACLE_SID/DB/FULL/
fi
pdb 
#!/bin/ksh

LOG=/operation/log/dbbackupPDB_$2_date '+%Y%m%d'.log


(
if [ $# -ne 2 ]; then
echo "Usage: dbbackupPDB.sh [ORACLE_SID] [PDB_NAME]"
echo "e.g.: dbbackupPDB.sh CDB_NAME PDB_NAME"
echo "Error" | /usr/bin/mutt -s "Error : running $(basename $0)" -- db_adm
exit 1
fi


if [ $(ps -ef | grep pmon_${1}$ | awk '{print $1}' | tail -1) != $(whoami) ]; then
echo "Please use Oracle user to execute this script"
echo "Error" | /usr/bin/mutt -s "Error : running $(basename $0)" -- db_adm
exit 1
fi


if [ $(ps -ef | grep pmon_${1}$ | wc -l) -eq 0 ]; then
echo "[ $(date "+%F %H:%M:%S") ] Database is not running"
echo " Error" | /usr/bin/mutt -s "Error : running $(basename $0)" -- db_adm
exit 1
fi


export PATH=/usr/local/bin:$PATH;
export ORACLE_SID=$1
export ORACLE_PDB_SID=$1
export ORAENV_ASK=NO
. oraenv


export PDB_SID=$2


export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"
ENCRYPT_KEY=$(cat /operation/par/rman_encrypt.db)
CURR_DATE=$(date "+%Y%m%d")
CURR_DATE_LONG=$(date "+%Y%m%d%H%M%S")
LOCALHOUSEKEEPDATE=$(date -d "1 day ago" "+%Y%m%d")
REMOTEHOUSEKEEPDATE=$(date -d "3 day ago" "+%Y%m%d")


DBBKDATADIR=/backup/DB/PDB/$PDB_SID/$CURR_DATE/


ssh -q root@192.168.1.99 "rm -rf /remote/backupfolder/$ORACLE_SID/DB/PDB/$PDB_SID/*"
ssh -q root@192.168.3.146 "mkdir -p /remote/backupfolder/$ORACLE_SID/DB/PDB/$PDB_SID"
rm -rf /backup/DB/PDB/$PDB_SID/$LOCALHOUSEKEEPDATE


if [ ! -d "$DBBKDATADIR" ]; then
echo "[ $(date "+%F %H:%M:%S") ] Folder of $DBBKDATADIR is not existed, create now"
mkdir -p $DBBKDATADIR
fi


echo "[ $(date "+%F %H:%M:%S") ] Start PDB Database hot Backup - $PDB_SID"


rman << EOF
connect target sys/$ORACLE_PDB_SID;
configure compression algorithm 'MEDIUM';
BACKUP pluggable database $PDB_SID FORMAT '${DBBKDATADIR}/%d_${PDB_SID}%T%t_%s.bkp';
EOF


if [ $? -ne 0 ]; then
echo "."|/usr/bin/mutt -s "[ $(date "+%F %H:%M:%S") ] [PDB DB Backup] ${PDB_SID} DBs Backup Error" -- db_adm
exit 1
else
echo "[ $(date "+%F %H:%M:%S") ] End PDB Database hot Backup - ${PDB_SID}"
fi
) > $LOG


CHKBACKUPLOG=cat $LOG | grep ORA- | wc -l
if [ $CHKBACKUPLOG -ne 0 ]; then
echo "."|/usr/bin/mutt -s "[DB Backup] $PDB_SID Backup Error - date +'%Y-%m-%d'" -i ${LOG} -- db_adm
else
echo "."|/usr/bin/mutt -s "[DB Backup] $PDB_SID Backup Completed - date +'%Y-%m-%d'" -i ${LOG} -- db_adm
/usr/bin/rsync -avSz --delete --stats --human-readable -e "ssh -p 22 -o StrictHostKeyChecking=no" /backup/DB/PDB/$PDB_SID/ admin@192.168.1.99:/remote/backupfolder/$ORACLE_SID/DB/PDB/$PDB_SID/
fi
incremental backup 
export SID=$1
export LOG=/operation/log/$(basename $0 | sed s/.ksh//g)_${SID}_$(date '+%Y%m%d').log

(
if [ -z $SID ]; then
        echo "Usage: dbbackupFULL.sh <ORACLE_SID>"
        exit 1
fi

if [ $(ps -ef | grep pmon_${SID}$ | awk '{print $1}' | tail -1) != $(whoami) ]; then
        echo "Please use Oracle user to execute this script"
        exit 1
fi

if [ $(ps -ef | grep pmon_${SID}$ | wc -l) -eq 0 ]; then
        echo "[ $(date "+%F %H:%M:%S") ] Database is not running"
        exit 1
fi

export PATH=/usr/local/bin:$PATH;
export ORACLE_SID=$SID
export ORACLE_PDB_SID=$SID
export ORAENV_ASK=NO
. oraenv


ENCRYPT_KEY=$(cat /par/rman_encrypt.db)
CURR_DATE=$(date "+%Y%m%d")

BK_BASE_DIR=backup
BK_EXTN_DIR=db/${ORACLE_SID}/incremental
BK_PATH=/${BK_BASE_DIR}/${BK_EXTN_DIR}/${CURR_DATE}
BK_PRE="${ORACLE_SID}_INCR"
TAG_BK="${ORACLE_SID}_BACKUP_TAG"
TAG_ARCH="${ORACLE_SID}_ARCH_TAG"

NAS_LOGIN="[email protected]"
NAS_LOCAL=/volume1/DB_SERVER_BACKUP/${BK_EXTN_DIR}
NAS_OFFSITE=/share/MyArchive1/${BK_BASE_DIR}/${BK_EXTN_DIR}

mkdir -p ${BK_PATH}

## ! -name '*[!0-9]*' means file with numeric name
## mmin +720 (12 hours) avoid deleting other fresh backup
if [ $(date +%u) -eq 6 -a $(date +%H) -gt 18 ] || [ $(date +%u) -eq 7 -a $(date +%H) -lt 6 ]; then
        ssh ${NAS_LOGIN} /bin/sh << EOF
        echo "[ $(date "+%F %H:%M:%S") ] Cleanup Old Backup - ${ORACLE_SID}"
        find ${NAS_LOCAL}/* -maxdepth 0 -type d ! -name '*[!0-9]*' -mmin +720 -exec rm -rf {} \;
EOF
fi

echo "[ $(date "+%F %H:%M:%S") ] Start Database Hot Backup - ${ORACLE_SID}"

rman target / << EOF
configure backup optimization off;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '${BK_PATH}/%d_autoctl_%F.bkp';
configure device type disk parallelism 4 backup type to compressed backupset;
configure channel 1 device type disk format '${BK_PATH}/${BK_PRE}_DATA_%T_%t_%s.bkp';
configure channel 2 device type disk format '${BK_PATH}/${BK_PRE}_DATA_%T_%t_%s.bkp';
configure channel 3 device type disk format '${BK_PATH}/${BK_PRE}_DATA_%T_%t_%s.bkp';
configure channel 4 device type disk format '${BK_PATH}/${BK_PRE}_DATA_%T_%t_%s.bkp';
configure compression algorithm 'MEDIUM';
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt expired archivelog all;
set encryption on identified by ${ENCRYPT_KEY} only;
backup as compressed backupset incremental level 1 database tag=${TAG_BK} plus archivelog tag=${TAG_ARCH} format '${BK_PATH}/${BK_PRE}_ARCH_%T_%t_%s.bkp';
delete noprompt archivelog all backed up 2 times to device type disk;
EOF

if [ $? -eq 0 ]; then
        echo "[ $(date "+%F %H:%M:%S") ] End Database Hot Backup - ${ORACLE_SID}"
else
        echo "[ $(date "+%F %H:%M:%S") ] Failed Database Hot Backup - ${ORACLE_SID}"
        exit
fi

echo "[ $(date "+%F %H:%M:%S") ] Start Offsite Transfer"
ssh ${NAS_LOGIN} /bin/sh << EOF
mkdir -p ${NAS_OFFSITE}
rsync -av --delete  ${NAS_LOCAL}/ ${NAS_OFFSITE}
EOF
echo "[ $(date "+%F %H:%M:%S") ] End Offsite Transfer"
) > $LOG

if [ $(cat $LOG | grep -ic "ORA-\|Failed\|ERROR") -ne 0 ]; then
        echo ""|/usr/bin/mutt -s "[DB Backup] $SID Backup Error - $(date +'%Y-%m-%d')" -i ${LOG} -- db_adm
else
        echo ""|/usr/bin/mutt -s "[DB Backup] $SID Backup Completed - $(date +'%Y-%m-%d')" -i ${LOG} -- db_adm
fi
incrementally update 
export SID=$1
export LOG=/operation/log/$(basename $0 | sed s/.ksh//g)_${SID}_$(date '+%Y%m%d').log

(
if [ -z $SID ]; then
        echo "Usage: dbbackupFULL.sh <ORACLE_SID>"
        exit 1
fi

if [ $(ps -ef | grep pmon_${SID}$ | awk '{print $1}' | tail -1) != $(whoami) ]; then
        echo "Please use Oracle user to execute this script"
        exit 1
fi

if [ $(ps -ef | grep pmon_${SID}$ | wc -l) -eq 0 ]; then
        echo "[ $(date "+%F %H:%M:%S") ] Database is not running"
        exit 1
fi

export PATH=/usr/local/bin:$PATH;
export ORACLE_SID=$SID
export ORACLE_PDB_SID=$SID
export ORAENV_ASK=NO
. oraenv


ENCRYPT_KEY=$(cat /par/rman_encrypt.db)
CURR_DATE=$(date "+%Y%m%d")

BK_BASE_DIR=backup
BK_EXTN_DIR=db/${ORACLE_SID}/full
BK_PATH=/${BK_BASE_DIR}/${BK_EXTN_DIR}
BK_PRE="${ORACLE_SID}_FULL_N"
TAG_BK="${ORACLE_SID}_BACKUP_TAG"
TAG_ARCH="${ORACLE_SID}_ARCH_TAG"

NAS_LOGIN="[email protected]"
NAS_LOCAL=/volume1/DB_SERVER_BACKUP/seed
NAS_OFFSITE=/volume1/DB_SERVER_BACKUP
HOUSEKEEP_DAY=7

mkdir -p ${BK_PATH}

## ! -name '*[!0-9]*' means file with numeric name
## mmin +720 (12 hours) avoid deleting other fresh backup
ssh ${NAS_LOGIN} /bin/sh << EOF
        find ${NAS_OFFSITE}/* -maxdepth 0 -type d ! -name '*[!0-9]*' -mtime ${HOUSEKEEP_DAY} -exec rm -rf {} \;
EOF
fi

echo "[ $(date "+%F %H:%M:%S") ] Start Database Hot Backup - ${ORACLE_SID}"

rman target / << EOF
configure backup optimization off;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '${BK_PATH}/%d_autoctl_%F.bkp';
configure device type disk parallelism 4 backup type to compressed backupset;
configure channel 1 device type disk format '${BK_PATH}/${BK_PRE}_DATA_%s.bkp';
configure channel 2 device type disk format '${BK_PATH}/${BK_PRE}_DATA_%s.bkp';
configure channel 3 device type disk format '${BK_PATH}/${BK_PRE}_DATA_%s.bkp';
configure channel 4 device type disk format '${BK_PATH}/${BK_PRE}_DATA_%s.bkp';
configure compression algorithm 'MEDIUM';
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt obsolete device type disk;
set encryption on identified by ${ENCRYPT_KEY} only;
set decryption    identified by ${ENCRYPT_KEY};
backup as compressed backupset incremental level 1 tag=${TAG_BK} for recover of copy database plus archivelog tag=${TBK_PRE}_ARCH_%s.bkp';
host 'date "+[ %F %H:%M:%S ] Merging ..."';
recover copy of database with tag ${TAG_BK};
delete noprompt archivelog all backed up 2 times to device type disk;
delete noprompt obsolete device type disk;
EOF

if [ $? -eq 0 ]; then
        echo "[ $(date "+%F %H:%M:%S") ] End Database Hot Backup - ${ORACLE_SID}"
else
        echo "[ $(date "+%F %H:%M:%S") ] Failed Database Hot Backup - ${ORACLE_SID}"
        exit
fi

echo "[ $(date "+%F %H:%M:%S") ] Start Offsite Transfer"
ssh ${NAS_LOGIN} /bin/sh << EOF
mkdir -p ${NAS_OFFSITE}
rsync -av --delete  ${NAS_LOCAL}/ ${NAS_OFFSITE}/${CURR_DATE}/${BK_BASE_DIR}
EOF
echo "[ $(date "+%F %H:%M:%S") ] End Offsite Transfer"
) > $LOG

if [ $(cat $LOG | grep -ic "ORA-\|Failed\|ERROR") -ne 0 ]; then
        echo ""|/usr/bin/mutt -s "[DB Backup] $SID Backup Error - $(date +'%Y-%m-%d')" -i ${LOG} -- db_adm
else
        echo ""|/usr/bin/mutt -s "[DB Backup] $SID Backup Completed - $(date +'%Y-%m-%d')" -i ${LOG} -- db_adm
fi

restore database

restore standalone database (non-cdb)
  1. prepare backup file and
  2. create database
    1. modify oraenv config 
      [oracle@myserver ~]# vi /etc/oratab                            ### not important for short time usage
DB_NAME:/u01/app/oracle/product/19.0.0/dbhome_1:N
    2. modified pfile
    3. create audit directory, following pfile
  3. rman restore 
    [oracle@myserver ~]$ rman target /
RMAN> startup nomount force;
RMAN> set decryption identified by decrypt_password;
RMAN> restore spfile from '/backup/control_DBNAME_20220101001001.ctl';
RMAN> create pfile from spfile;                                   ### modify pfile if neccessary
RMAN> create spfile from pfile;
RMAN> shutdown immediate;
RMAN> startup nomount;
RMAN> restore controlfile from '/backup/control_DBNAME_20220101001001.ctl';
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database until available redo;
RMAN> alter database open resetlogs;
restore RAC to standalone database
  • pfile setting
  *.audit_file_dest='/u01/app/oracle/admin/DB_NAME/adump'
  *.audit_trail='db'
  *.compatible='19.0.0'
  *.control_files='/u06/oradata/DB_NAME/control01.ctl','/u02/oradata/DB_NAME/control02.ctl'
  *.db_block_size=8192
  *.db_name='DB_NAME'
  *.diagnostic_dest='/u01/app/oracle'
  *.log_archive_dest_1='location=/u99/oradata/DB_NAME/archive/'
  *.log_archive_format='DB_NAME_ARCH_%t_%s_%r.arc'
  *.nls_language='AMERICAN'
  *.nls_territory='AMERICA'
  *.open_cursors=300
  *.pga_aggregate_target=12470m
  *.processes=1920
  *.remote_login_passwordfile='EXCLUSIVE'
  *.sga_target=37407m
  *.standby_file_management='AUTO'
  ##*.db_file_name_convert='+SHDATA/','/u02/oradata/'                                                      ### this only works in duplicate command, not applicable in restore backup
  ##*.log_file_name_convert='+ARCH/','/u02/oradata/'                                                       ### this only works in duplicate command, not applicable in restore backup

  1. script

    #!/bin/bash
LOG=/operation/log/`basename $0|sed s/.sh//g`_`date +%Y%m%d_%H%M`.log

(
export ORACLE_SID=DB_NAME
export PATH=$PATH:/usr/local/bin
export ORAENV_ASK=NO
. oraenv
NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'; export NLS_DATE_FORMAT                              ### optional
NLS_LANG='AMERICAN_AMERICA.WE8ISO8859P1'; export NLS_LANG                                    ###

echo "$(date +'[%F %X]') begin restoring backup"

sqlplus / as sysdba << EOF                                                                   ### cleaning directory
shutdown abort;
EOF
rm -rf /u02/oradata/DB_NAME/

rman << EOF
connect target;
startup nomount;
set decryption identified by decrypt_password;
restore controlfile from '/u02/DB_NAME/DB_NAME_autoctl_c-235490133-20220101-00.bkp';
alter database mount;
CONFIGURE DEVICE TYPE DISK PARALLELISM 5;                                                    ### applicable when different backup location
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/new_backup_dir/%d_%T_%t_%s.bkp';               ### applicable when different backup location
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/new_backup_dir/%d_%T_%t_%s.bkp';               ###
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '/new_backup_dir/%d_%T_%t_%s.bkp';               ###
CONFIGURE CHANNEL 4 DEVICE TYPE DISK FORMAT '/new_backup_dir/%d_%T_%t_%s.bkp';               ###
CONFIGURE CHANNEL 5 DEVICE TYPE DISK FORMAT '/new_backup_dir/%d_%T_%t_%s.bkp';               ###
run {
catalog start with '/new_backup_path/' noprompt;                                             ### applicable when different backup location
set newname for datafile 1 TO '/u02/oradata/DB_NAME/system.dbf';                             ### a must when different storage
set newname for datafile 2 TO '/u02/oradata/DB_NAME/work.dbf';                               ### 
set newname for datafile 3 TO '/u02/oradata/DB_NAME/sysaux.dbf';                             ### 
set newname for datafile 4 TO '/u02/oradata/DB_NAME/undotbs.dbf';                            ### 
set newname for datafile 5 TO '/u02/oradata/DB_NAME/ts_audio.dbf';                           ### 
set newname for TEMPFILE 1 TO '/u02/oradata/DB_NAME/bigtemp.dbf';                            ### 
restore database;
switch datafile all;
switch tempfile all;
alter database flashback off;
recover database;
}

echo "$(date +'[%F %X]') complete restoring backup"
) >$LOG 2>&1

  2. post-cleaning

    SQL> select a.group#, a.thread#, a.sequence#, a.archived, a.status, b.member as redolog_file_name, (a.bytes/1024/1024) as size_mb from v$log a join v$logfile b on a.group#=b.group# order by 1;
SQL> alter system set db_create_online_log_dest_1='/u02/oradata/';
SQL> alter database open resetlogs;
  • errors 
    RMAN> restore database;
channel ORA_DISK_2: errors found reading piece handle=/old_backup_path/DB_NAME_20220104_1093050304_160625.bkp
channel ORA_DISK_2: failover to piece handle=/new_backup_path/DB_NAME_20220104_1093050304_160625.bkp tag=DB_NAME_DB_FULL_BK
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 01:37:08
>> solution: it is normal and no damage caused
>> cause:    when the backup location is different from the original


RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/01/2022 00:00:0
ORA-00349: failure obtaining block size for '+ARCH'
ORA-29701: unable to connect to Cluster Synchronization Service
ORA-29701: unable to connect to Cluster Synchronization Service
ORA-29701: unable to connect to Cluster Synchronization Service
>> solution: make post-cleaning
>> cause:    online log creation path is set to old path (ASM)


SQL> alter database drop logfile group 100;
alter database drop logfile group 100
*
ERROR at line 1:
ORA-01623: log 100 is current log for instance DB_NAME (thread 1) - cannot drop
ORA-00312: online log 100 thread 1: '+ARCH'
>> solution: make post-cleaning. below statement can be used to forcefully clean corrupted redo file
             SQL> alter database clear unarchived logfile group 100;
>> cause:    after opened database, if the new valid path for online redo log is still not set, the invalid online log will be bind even if the log doesn't exist 


SQL> alter database noarchivelog;
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ksvworkmsgalloc: bad reaper], [0x080010003], [], [], [], [], [], [], [], [], [], []
>> solution: clear unarchived logfile
>> cause:    corrupted logfile (online redo log) is being used (clearing_current)
restore archivelog from backup
  • cannot restore from archive log backup
  • the restored archivelog can be used for recover database 
    run
{
SET ARCHIVELOG DESTINATION TO '/backup/arch_extraction';
restore archivelog from logseq=25574 thread=1 until logseq=26579 thread=1;
}
run
{
SET ARCHIVELOG DESTINATION TO '/backup/arch_extraction';
restore archivelog from logseq=13645 thread=2 until logseq=14119 thread=2;
}
restore RAC database from tape device (veritas)
  1. shutdown node 2
  2. alter database set cluster_database=false scope=spfile;
  3. (optional) restore controlfile
    1. check filename in tape device
      bplist -S prddb-bk -C uatdb-BK -s 06/30/2023 00:00:00 -t 4 -l -R / | grep "c-"
    2. startup mount 
      run {
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=uatdb-BK,NB_ORA_SID=uatdb1,NB_ORA_POLICY=UAT_PHY_UATDB_D,NB_ORA_SERV=prddb-bk,NB_ORA_SCHED=Default-Application-Backup';
RESTORE controlfile to '/tmp/c-999341999-20230701-01' from 'c-999341999-20230701-01' ;
RELEASE CHANNEL ch01;
}
    3. startup nomount 
      run {
replicate controlfile from '/tmp/c-999341999-20230701-01';
}
  4. restore database 
    run {
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=uatdb-BK,NB_ORA_SID=uatdb1,NB_ORA_POLICY=UAT_PHY_UATDB_D,NB_ORA_SERV=prddb-bk,NB_ORA_SCHED=Default-Application-Backup';
set until time "to_date('2023-06-01:00:00:00','yyyy-mm-dd:hh24:mi:ss')";
RESTORE database;
RECOVER database;
RELEASE CHANNEL ch01;
}
    alter database open resetlogs
  5. alter database set cluster_database=true scope=spfile;
  6. restart both nodes *if node2 cannot start database (only asm is up), try: srvctl start db -d racdb
⚠️ **GitHub.com Fallback** ⚠️