RAC

 Oracle RAC 12cR1 introduced a new architecture option called Flex Clusters.

blacklist {
        devnode "^(ram|raw|loop|fd|md|dm-|sr|scd|st|sdao)[0-9]*"
        devnode "^asm/*"
        devnode "^ofsctl"
        wwid    3600605b0033286b0162b06912bb7c0dd
        wwid    3600605b0033286b0162b06912bb7181b
        wwid    3600605b0033286b0162b06912bb76c7b
}

[root@nmsis2d11 oracleasm]# cat /etc/multipath.conf | grep -A19 defaults

defaults {

        polling_interval 5

        path_grouping_policy multibus

        failback immediate

        user_friendly_names yes

        max_fds 8192

}

blacklist {

        devnode "^(ram|raw|loop|fd|md|dm-|sr|scd|st|sdao)[0-9]*"

        devnode "^asm/*"

        devnode "^ofsctl"

        wwid    3600605b0033286b0162b06912bb7c0dd

        wwid    3600605b0033286b0162b06912bb7181b

        wwid    3600605b0033286b0162b06912bb76c7b

}

[root@ipagt1d5 ~]# cat /etc/multipath.conf | grep -A19 defaults

defaults {

        user_friendly_names yes

        failback immediate

        prio const

        find_multipaths yes

        detect_prio yes

        polling_interval 10

}

blacklist {

        devnode "^(ram|raw|loop|fd|md|dm-|sr|scd|st)[0-9]*"

        devnode "^asm/*"

        devnode "^ofsctl"

}

[root@nmsis2d11 oragrid_s2d11_vg]# df -h /dev/mapper/t2ls2idb_s2d11_vg-t2ls2idb

Filesystem                              Size  Used Avail Use% Mounted on

/dev/mapper/t2ls2idb_s2d11_vg-t2ls2idb   99G   60G   34G  64% /opt/app/t2ls2idb

[root@nmsis2d11 oragrid_s2d11_vg]# df -h /dev/mapper/oragrid_s2d11_vg-oragrid

Filesystem                            Size  Used Avail Use% Mounted on

/dev/mapper/oragrid_s2d11_vg-oragrid   99G   56G   38G  60% /opt/app/oragrid

[root@nmsis2d11 oragrid_s2d11_vg]# ls -ltr /dev/mapper/

total 0

crw------- 1 root root 10, 236 Jul  3 14:34 control

lrwxrwxrwx 1 root root       8 Jul 15 02:28 oragrid_s2d11_vg-oragrid -> ../dm-24

lrwxrwxrwx 1 root root       8 Jul 15 02:28 t2ls2idb_s2d11_vg-t2ls2idb -> ../dm-25

[root@nmsis2d11 oragrid_s2d11_vg]# pwd

/dev/oragrid_s2d11_vg

[root@nmsis2d11 oragrid_s2d11_vg]# ls -ltr

total 0

lrwxrwxrwx 1 root root 8 Jul 15 02:28 oragrid -> ../dm-24

[root@nmsis2d11 t2ls2idb_s2d11_vg]# pwd

/dev/t2ls2idb_s2d11_vg

[root@nmsis2d11 t2ls2idb_s2d11_vg]# ls -ltr

total 0

lrwxrwxrwx 1 root root 8 Jul 15 02:28 t2ls2idb -> ../dm-25

[root@nmsis2d11 mapper]# ls -l

total 0

lrwxrwxrwx 1 root root       8 Jul 15 15:29 ASM_20171220_4.0G_EMC_1090_01A3 -> ../dm-22

lrwxrwxrwx 1 root root       7 Jul 15 15:29 ASM_20171220_4.0G_EMC_1090_01A4 -> ../dm-1

lrwxrwxrwx 1 root root       7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01A5 -> ../dm-2

lrwxrwxrwx 1 root root       7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01A6 -> ../dm-3

lrwxrwxrwx 1 root root       7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01A7 -> ../dm-4

lrwxrwxrwx 1 root root       7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01A8 -> ../dm-5

lrwxrwxrwx 1 root root       7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01A9 -> ../dm-6

lrwxrwxrwx 1 root root       7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01AA -> ../dm-7

lrwxrwxrwx 1 root root       7 Jul 15 15:29 ASM_20171220_725G_EMC_1090_019B -> ../dm-0

lrwxrwxrwx 1 root root       8 Jul 15 15:29 ASM_20171220_725G_EMC_1090_019C -> ../dm-11

lrwxrwxrwx 1 root root       8 Jul 15 16:27 ASM_20171220_725G_EMC_1090_019D -> ../dm-17

lrwxrwxrwx 1 root root       8 Jul 15 16:27 ASM_20171220_725G_EMC_1090_019E -> ../dm-18

lrwxrwxrwx 1 root root       8 Jul 15 16:00 ASM_20171220_725G_EMC_1090_019F -> ../dm-19

lrwxrwxrwx 1 root root       8 Jul 15 16:00 ASM_20171220_725G_EMC_1090_01A0 -> ../dm-23

lrwxrwxrwx 1 root root       8 Jul 15 16:25 ASM_20171220_725G_EMC_1090_01A1 -> ../dm-20

lrwxrwxrwx 1 root root       8 Jul 15 15:29 ASM_20171220_725G_EMC_1090_01A2 -> ../dm-21

lrwxrwxrwx 1 root root       8 Jul 15 16:00 ASM_20200311_725G_EMC_1090_0517 -> ../dm-10

lrwxrwxrwx 1 root root       8 Jul 15 16:15 ASM_20200311_725G_EMC_1090_0518 -> ../dm-12

lrwxrwxrwx 1 root root       8 Jul 15 16:00 ASM_20200311_725G_EMC_1090_0519 -> ../dm-13

lrwxrwxrwx 1 root root       8 Jul 15 15:29 ASM_20200311_725G_EMC_1090_051A -> ../dm-14

lrwxrwxrwx 1 root root       8 Jul 15 15:29 ASM_20200311_725G_EMC_1090_051B -> ../dm-15

lrwxrwxrwx 1 root root       8 Jul 15 15:29 ASM_20200311_725G_EMC_1090_051C -> ../dm-16

crw------- 1 root root 10, 236 Jul  3 14:34 control

lrwxrwxrwx 1 root root       7 Jul 15 15:29 LOCAL_20171220_100G_EMC_1090_01AB -> ../dm-8

lrwxrwxrwx 1 root root       7 Jul 15 15:29 LOCAL_20171220_100G_EMC_1090_01AC -> ../dm-9

lrwxrwxrwx 1 root root       8 Jul 15 02:28 oragrid_s2d11_vg-oragrid -> ../dm-24

lrwxrwxrwx 1 root root       8 Jul 15 02:28 t2ls2idb_s2d11_vg-t2ls2idb -> ../dm-25

[root@nmsis2d11 mapper]# df -Th

Filesystem                             Type      Size  Used Avail Use% Mounted on

devtmpfs                               devtmpfs   36G     0   36G   0% /dev

tmpfs                                  tmpfs      36G  389M   35G   2% /dev/shm

tmpfs                                  tmpfs      36G  234M   36G   1% /run

tmpfs                                  tmpfs      36G     0   36G   0% /sys/fs/cgroup

/dev/sda7                              ext4       19G  8.0G  9.3G  47% /

/dev/mapper/t2ls2idb_s2d11_vg-t2ls2idb ext4       99G   60G   34G  64% /opt/app/t2ls2idb

/dev/sda1                              ext4      976M  276M  634M  31% /boot

/dev/sda6                              ext4      4.8G   13M  4.6G   1% /tmp

/dev/sda5                              ext4       15G  2.3G   12G  17% /var

/dev/sda2                              ext4       15G  1.7G   13G  12% /home

/dev/sdd1                              ext4       69G  202M   65G   1% /var/crash

/dev/mapper/oragrid_s2d11_vg-oragrid   ext4       99G   56G   38G  60% /opt/app/oragrid

dy-tdevnas1:/dbbkups01                 nfs       4.0T  3.7T  402G  91% /pac/prod_backup2

tmpfs                                  tmpfs     7.1G     0  7.1G   0% /run/user/51012

tmpfs                                  tmpfs     7.1G     0  7.1G   0% /run/user/0

tmpfs                                  tmpfs     7.1G     0  7.1G   0% /run/user/1563708

dy-tdevnas1.snt.bst.bls.com:/osddba    nfs       200G  184G   17G  92% /nas/osddba

dy-tdevnas1.snt.bst.bls.com:/osd       nfs       2.0T  967G  1.1T  48% /nas/osd

tmpfs                                  tmpfs     7.1G     0  7.1G   0% /run/user/21295

dy-tdevnas1:/dbbkups01     /pac/prod_backup2        nfs     rw,bg,tcp,nfsvers=3,suid,noac,hard,rsize=32768,wsize=32768 0 0

ORA-01103: database name 'IPAGI1DB' in control file is not 'IPAGIT1D'

https://www.pythian.com/blog/technical-track/oracle-rman-restore-to-the-same-machine-as-the-original-database  --> restore database from rman backup with in the same host

ACTION!="add|change", GOTO="dm_end"

ENV{DM_UDEV_RULES_VSN}!="?*", GOTO="dm_end"

# Set permissions for a DM device name starting with ASM for Oracle RAC

ENV{DM_NAME}=="ASM*", OWNER:="oragrid", GROUP:="asmadmin", MODE:="0660"

LABEL="dm_end"

[root@nmsis6d11 rules.d]# ls -l

total 8

-rw-r----- 1 root root 190 Oct  1  2023 55-usm.rules

-rw-r--r-- 1 root root 144 Jun 24 15:23 99-oracle.rules

[root@nmsis6d11 rules.d]# more 99-oracle.rules

# Set permissions for a DM device name starting with ASM for Oracle RAC

ENV{DM_NAME}=="ASM*", OWNER:="oragrid", GROUP:="asmadmin", MODE:="0660"

[root@nmsis6d11 rules.d]#

[root@ipagt5d12 rules.d]# more 99-oracle.rules

ACTION!="add|change", GOTO="dm_end"

ENV{DM_UDEV_RULES_VSN}!="?*", GOTO="dm_end"

# Set permissions for a DM device name starting with ASM for Oracle RAC

ENV{DM_NAME}=="ASM*", OWNER:="oragrid", GROUP:="asmadmin", MODE:="0660"

LABEL="dm_end"

-- Step 1: Check free space in datafiles

SELECT FILE_NAME, BYTES, BLOCKS, FREE_SPACE

FROM DBA_DATA_FILES df

JOIN (SELECT FILE_ID, SUM(BYTES) FREE_SPACE

      FROM DBA_FREE_SPACE

      GROUP BY FILE_ID) fs

ON df.FILE_ID = fs.FILE_ID

WHERE df.TABLESPACE_NAME = 'your_tablespace_name';

-- Step 2: Move segments (tables and indexes) to free up space

ALTER TABLE your_table_name MOVE;

ALTER INDEX your_index_name REBUILD;

-- Step 3: Resize datafiles

ALTER DATABASE DATAFILE 'your_datafile_name1' RESIZE 500M;

ALTER DATABASE DATAFILE 'your_datafile_name2' RESIZE 300M;

-- Repeat for all datafiles

-- Step 4: Coalesce free space

ALTER TABLESPACE your_tablespace_name COALESCE;

 select sum(bytes)/1024/1024/1024 from dba_segments where TABLESPACE_NAME='INDICES';

select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='TOOLS';

DECLARE

    v_id NUMBER := 1;

    v_clob CLOB;

    v_name VARCHAR2(50) := 'Test Data';

    v_date DATE := SYSDATE;

    v_chunk CLOB := RPAD('X', 1000, 'X');  -- 1KB of data

    v_clob_size NUMBER := 10485760;        -- 10MB per row

    PROCEDURE insert_row(p_id IN NUMBER, p_name IN VARCHAR2, p_date IN DATE, p_clob IN CLOB) IS

    BEGIN

        INSERT INTO test_table (id, name, created_date, data)

        VALUES (p_id, p_name, p_date, p_clob);

    END;

BEGIN

    FOR i IN 1..1024 LOOP  -- 1024 * 10MB = 10GB

        DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

        DBMS_LOB.OPEN(v_clob, DBMS_LOB.LOB_READWRITE);

        FOR j IN 1..(v_clob_size / 1000) LOOP

            DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_chunk), v_chunk);

        END LOOP;

        insert_row(v_id, v_name, v_date, v_clob);

        v_id := v_id + 1;

        DBMS_LOB.FREETEMPORARY(v_clob);

    END LOOP;

    COMMIT;

END;

/

From the provided information we see that DATA tablespace is having around 500 GB of free space, but you could not able to shrink all the free space from datafiles.
This is due to fragmentation in the datafiles. There could be an active datablock occupied by the objects data at the end of the datafile which is causing not to shrink the datafile.

To overcome this you need reorg all the objects in the tablespace to be able to shrink more space in the datafiles.

Please find below documents on how to move objects in the tablespace:

How to Move Different Database Objects to Another Tablespace ( Doc ID 1915040.1 )

0----

Do i need to reorg all the objects first , then go for shrinking the datafiles right?

moving within the same tablespace will do the job right?

no need to create new tablespace and then move into that newone --> ?

-------

Yes you need to reorg all the objects first and go for shrinking the datafiles.

No need to create the new tablespace as there is enough free space in the DATA tablespace. Moving within the same tablespace will do the job.

--------

t1ls1jdb@nmsjs1d1(1110) t1ls1jd1 /opt/app/t1ls1jdb/oracle$ more /home/oracle/oraadmin/rman_backup.sh

#!/bin/ksh

#

# ******************************************************************

# *                           NOTICE                               *

# *** NOT FOR USE OR DISCLOSURE OUTSIDE THE AT&T COMPANIES      ****

# ******************************************************************

#          Copyright 2009. AT&T. All rights reserved.

# ******************************************************************

# *

# *   Module name       : rman_backup.sh

# *

# *   Module description: Backup the database using RMAN.

# *

# *   Date created      :

# *

# *   Author            :

# *

# *   Modification History      : Pratul Malhotra

# *   --------------------------------------------------------------

# *

# *******************************************************************

#

#set -x

#

# Format Log File for Backups

#

format_backup_logfile_name()

{

   # Create Backup Log Directory if it does not Exist

   #

   if [ ! -d ${BACKUPSLOG_DIR} ]

   then

      mkdir -p ${BACKUPSLOG_DIR}

   fi

   if [ ${ARCHIVEONLY} = Y ]

   then

      LOGFILE=${BACKUPSLOG_DIR}/DB_Arch_Backup_`date +%Y%m%d%H%M`.log

   elif [ ${INCREMENTALONLY} = Y ]

   then

     LOGFILE=${BACKUPSLOG_DIR}/DB_Incremental_Backup_`date +%Y%m%d%H%M`.log

   else

     LOGFILE=${BACKUPSLOG_DIR}/DB_Full_Backup_`date +%Y%m%d%H%M`.log

   fi

}

#

# List Contents of Backup Directory

#

list_bkup_dir()

{

   BkupDir=${1}

   if [ -d ${BkupDir} ]

   then

      log "Listing ${BkupDir}"

      ls -lisart ${BkupDir} | tee -a ${LOGFILE}

   fi

}

#

# Check for Hanging Backup

#

check_for_hanging_backup()

{

   Oracle_Sid=${1}

   Start_Date=${2}

   # Create Lock File for this backup to help determine if backup is hanging

   #

   LockFile=/tmp/backup.${Oracle_Sid}.${Start_Date}.lck

   touch ${LockFile}

   BkupTimeLimit=`get_dbmodes_value ${Oracle_Sid} BKUPTIMELIMIT 60`

   log "ORACLE_SID = ${Oracle_Sid} : BKUPTIMELIMIT = ${BkupTimeLimit}"

   # Start background process to sleep for $BKUPTIMELIMIT minutes and then verify

   # the backup for this SID has completed using the lock file created above

   # (if the lock file still exists after sleeping, then the backup must have hung)

   ${ADMIN_DIR}/check_backup_status.sh ${Oracle_Sid} ${BkupTimeLimit} ${LockFile} &

}

#

# Cleanup after the Backup Ends

#

end_backup()

{

   Oracle_Sid=${1}

   Start_Date=${2}

   # Remove Lock File created to determine if backup is hanging

   #

   LockFile=/tmp/backup.${Oracle_Sid}.${Start_Date}.lck

   rm -f ${LockFile}

}

#

# Backup the Online Redo Logs for Cold Backups

#

bkup_redo_logs()

{

   Oracle_Sid=${1}

   BkupDir=${2}

   log "Backing up online redo logs"

   RedoLogsDir=`get_dbmodes_value ${Oracle_Sid} REDOLOGSDIR N`

   RedoLogDirList=`echo ${RedoLogsDir} | sed "s/,/ /g"`

   for Field in ${RedoLogDirList}

   do

      for RedoLog in `ls ${Field}/*redo*`

      do

         RedoTarget=${BkupDir}/`basename ${RedoLog}`.`date +%Y%m%d%H%M`

         cp ${RedoLog} ${RedoTarget} | tee -a ${LOGFILE}

         compress ${RedoTarget}      | tee -a ${LOGFILE}

      done

   done

}

#

# Disable Previous Backups

#

disable_backups()

{

   log "Disabling previous backups"

   ${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' @${SQL_DIR}/disable_backups.sql | tee -a ${LOGFILE}

   rm -f /tmp/disable_backups.lst

   rm -f /tmp/disable_backups.sql

}

#

# Backup the Database to Tape

#

bkup_db_to_tape()

{

   BACKUP_POLICY=$1

   NB_ORACLE_SERVER=`grep SERVER /usr/openv/netbackup/bp.conf | grep -v _SERVER | cut -d "=" -f2 | tr -d " "`

   # BKRETENT is the number of days to keep backups.

   # If BKRETENT is not set in dbmodes.txt then set the default REDUNDANCY to 31.

   #

   BKRETENT=`get_dbmodes_value ${ORACLE_SID} TAPERETENT 31`

   log "ORACLE_SID = ${ORACLE_SID} : Backup Retention = ${BKRETENT} days"

   TAG_DATE_FORMAT=%Y_%m_%d_%H%M

   TAG_DATE=`date +${TAG_DATE_FORMAT}`

   log "ORACLE_SID = ${ORACLE_SID} : Copying the database backup sets to tape using Veritas NetBackkup"

   unset NLS_DATE_FORMAT

   # export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

   ${ORACLE_HOME}/bin/rman target / catalog ${RMANDB_USERNAME}/${RMANDB_PASSWORD}@rmancatc.snt.bst.bls.com << EOF >> ${LOGFILE}

     CONFIGURE RETENTION POLICY TO NONE;

     CONFIGURE CONTROLFILE AUTOBACKUP OFF;

     CONFIGURE DEVICE TYPE SBT_TAPE BACKUP TYPE TO COMPRESSED BACKUPSET;

     CONFIGURE BACKUP OPTIMIZATION ON;

     run {

          allocate channel 't1' type 'SBT_TAPE' PARMS='ENV=(NB_ORA_SERV=${NB_ORACLE_SERVER},

          NB_ORA_CLIENT=$HOST,NB_ORA_POLICY=${BACKUP_POLICY},NB_ORA_SCHED=Default-Application-Backup)';

          allocate channel 't2' type 'SBT_TAPE' PARMS='ENV=(NB_ORA_SERV=${NB_ORACLE_SERVER},

          NB_ORA_CLIENT=$HOST,NB_ORA_POLICY=${BACKUP_POLICY},NB_ORA_SCHED=Default-Application-Backup)';

          allocate channel 't3' type 'SBT_TAPE' PARMS='ENV=(NB_ORA_SERV=${NB_ORACLE_SERVER},

          NB_ORA_CLIENT=$HOST,NB_ORA_POLICY=${BACKUP_POLICY},NB_ORA_SCHED=Default-Application-Backup)';

          allocate channel 't4' type 'SBT_TAPE' PARMS='ENV=(NB_ORA_SERV=${NB_ORACLE_SERVER},

          NB_ORA_CLIENT=$HOST,NB_ORA_POLICY=${BACKUP_POLICY},NB_ORA_SCHED=Default-Application-Backup)';

          backup backupset all;

          release channel t1;

          release channel t2;

          release channel t3;

          release channel t4;

     }

     allocate channel for maintenance device type 'SBT_TAPE'

     PARMS='ENV=(NB_ORA_SERV=${NB_ORACLE_SERVER},NB_ORA_CLIENT=$HOST,NB_ORA_POLICY=${BACKUP_POLICY},NB_ORA_SCHED=Default-Application-Backup)';

     DELETE NOPROMPT FORCE OBSOLETE RECOVERY WINDOW OF 31 DAYS;

     release channel ;

     # report obsolete ;

     # delete force noprompt obsolete device type 'SBT_TAPE' ;

     # crosscheck backup device type 'SBT_TAPE' ;

     # delete force noprompt expired backup device type 'SBT_TAPE' ;

     # list backup summary device type disk ;

     list backup summary device type 'SBT_TAPE' ;

     exit;

EOF

   Status=${?}

   return ${Status}

}

#

# Get Backup Directories

#

get_bkup_dirs()

{

   case `hostname` in

   nmsjs6d1|nmsjs6d2|nmsjs6d3|nmsjs6d4)

        BACKUPDIR=+JBBOS_RECOVERY

        BACKUPDIR1=+JBBOS_RECOVERY

        BACKUPDIR2=+JBBOS_RECOVERY

        BACKUPDIR3=+JBBOS_RECOVERY

        BACKUPDIR4=+JBBOS_RECOVERY

        ;;

    n5004002|n5004005|g0212021)

        BACKUPDIR=/orabackup1/dumpdata

        BACKUPDIR1=/orabackup1/dumpdata

        BACKUPDIR2=/orabackup2/dumpdata

        BACKUPDIR3=/orabackup3/dumpdata

        BACKUPDIR4=/orabackup4/dumpdata

        ;;

    sogbuat1)

        BACKUPDIR=/orabackup01/dumpdata

        BACKUPDIR1=/orabackup01/dumpdata

        BACKUPDIR2=/orabackup02/dumpdata

        BACKUPDIR3=/orabackup03/dumpdata

        BACKUPDIR4=/orabackup04/dumpdata

        ;;

    optiersb)

        BACKUPDIR=/pac/optier/bkup/orabkup01/dumpdata01

        BACKUPDIR1=/pac/optier/bkup/orabkup01/dumpdata01

        BACKUPDIR2=/pac/optier/bkup/orabkup01/dumpdata01

        BACKUPDIR3=/pac/optier/bkup/orabkup01/dumpdata01

        BACKUPDIR4=/pac/optier/bkup/orabkup01/dumpdata01

        ;;

    ads00620|ads00621|ads00644|ads00645|ads00616|ads00617|ads00648|ads00649|nmsf1n1|nmsf1n2|nmsf1n3|nmsf1n4|nmsf1n5|nmsf1n6|nmsf1n7|nmsf1n8|nmsp2n1|nmsp2n2|nmsp2n3|nm

sp2n4)

        BACKUPDIR=/orabackup01/dumpdata

        BACKUPDIR1=${BACKUPDIR}

        BACKUPDIR2=${BACKUPDIR}

        BACKUPDIR3=${BACKUPDIR}

        BACKUPDIR4=${BACKUPDIR}

        ;;

    ads00641|ads00642|lstst06|lstst11|nrsb_fs5|nrsb_fs6|ads00610|ads00609)

        BACKUPDIR=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata01

        BACKUPDIR1=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata01

        BACKUPDIR2=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata02

        BACKUPDIR3=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata03

        BACKUPDIR4=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata04

        ;;

    bcsldt10)

        BACKUPDIR=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata01

        BACKUPDIR1=${BACKUPDIR}

        BACKUPDIR2=${BACKUPDIR}

        BACKUPDIR3=${BACKUPDIR}

        BACKUPDIR4=${BACKUPDIR}

        ;;

    *)

        # BACKUPDIR=/pac/cam/bkup/orabkup01/dumpdata01

        BACKUPDIR=`get_Backup_Dir ${ORACLE_SID}`

        BACKUPDIR1=${BACKUPDIR}

        BACKUPDIR2=${BACKUPDIR}

        BACKUPDIR3=${BACKUPDIR}

        BACKUPDIR4=${BACKUPDIR}

        ;;

   esac

}

#

# Backup the Database

#

bkup_db_to_disk()

{

   # BKRETENT is the number of days to keep backups.

   # If BKRETENT is not set in dbmodes.txt then set the default REDUNDANCY to 5.

   #

   BKRETENT=`get_dbmodes_value ${ORACLE_SID} BKRETENT 5`

   log "ORACLE_SID = ${ORACLE_SID} : Backup Retention = ${BKRETENT} days"

   SPFILEFLAG=`get_spfile_flag`

   log "ORACLE_SID = ${ORACLE_SID} : spfileflag=${SPFILEFLAG}"

   TAG_DATE_FORMAT=%Y_%m_%d_%H%M

   TAG_DATE=`date +${TAG_DATE_FORMAT}`

   log "ORACLE_SID = ${ORACLE_SID} : Backing up the database with RMAN"

   export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

   ${ORACLE_HOME}/bin/rman target / catalog ${RMANDB_USERNAME}/${RMANDB_PASSWORD}@rmancatc.snt.bst.bls.com << EOF >> ${LOGFILE}

     # Note: You cannot assign a tag to a backup of the current control file.

     #

     CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKUPDIR}/cf_%F';

     CONFIGURE CONTROLFILE AUTOBACKUP ON;

     CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF ${BKRETENT} DAYS;

     CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

     CONFIGURE BACKUP OPTIMIZATION OFF;

     ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

     CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' CLEAR;

     run {

       crosscheck backup device type disk ;

       crosscheck archivelog all;

        crosscheck backup of archivelog all spfile;

        delete force noprompt obsolete device type disk;

        delete force noprompt expired backup device type disk;

        delete force noprompt expired archivelog all device type disk;

        delete force noprompt backupset device type disk completed before 'sysdate-${BKRETENT}';

       # CR Copy ALTER between blocks

       # Oracle auto does a LOG SWITCH as part of BACKUP block

       #

      sql 'alter system switch logfile';

      sql 'alter system archive log current';

       allocate channel c1 device type disk format='${BACKUPDIR1}/${TAG_DATE}_dbf_s%s_p%p';

       allocate channel c2 device type disk format='${BACKUPDIR2}/${TAG_DATE}_dbf_s%s_p%p';

       allocate channel c3 device type disk format='${BACKUPDIR3}/${TAG_DATE}_dbf_s%s_p%p';

       allocate channel c4 device type disk format='${BACKUPDIR4}/${TAG_DATE}_dbf_s%s_p%p';

       # allocate channel c1 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

       # allocate channel c2 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

       # allocate channel c3 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

       # allocate channel c4 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

       backup

          incremental level 0

          filesperset 4

          # format '${BACKUPDIR}/${TAG_DATE}_dbf_s%s_p%p'

          database

          tag = '${TAG_DATE}_bkup_db'

          include current controlfile for standby;

       release channel c1;

       release channel c2;

       release channel c3;

       release channel c4;

     }

     exit;

     # ${SPFILEFLAG} ;

     # exit;

     # list backup summary device type disk ;

EOF

   Status=${?}

   return ${Status}

}

#

# Incremental Level 1 Backup of  the Database (Below Logic is appended by Pankaj)

#

incremental_bkup_db_to_disk()

{

   # BKRETENT is the number of days to keep backups.

   # If BKRETENT is not set in dbmodes.txt then set the default REDUNDANCY to 5.

   #

   BKRETENT=`get_dbmodes_value ${ORACLE_SID} BKRETENT 5`

   log "ORACLE_SID = ${ORACLE_SID} : Backup Retention = ${BKRETENT} days"

   SPFILEFLAG=`get_spfile_flag`

   log "ORACLE_SID = ${ORACLE_SID} : spfileflag=${SPFILEFLAG}"

   TAG_DATE_FORMAT=%Y_%m_%d_%H%M

   TAG_DATE=`date +${TAG_DATE_FORMAT}`

   log "ORACLE_SID = ${ORACLE_SID} : Backing up the database with RMAN"

   export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

   ${ORACLE_HOME}/bin/rman target / catalog ${RMANDB_USERNAME}/${RMANDB_PASSWORD}@rmancatc.snt.bst.bls.com << EOF >> ${LOGFILE}

     # Note: You cannot assign a tag to a backup of the current control file.

     #

     CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKUPDIR}/cf_%F';

     CONFIGURE CONTROLFILE AUTOBACKUP ON;

     CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF ${BKRETENT} DAYS;

     CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

     CONFIGURE BACKUP OPTIMIZATION OFF;

     ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

     CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' CLEAR;

     run {

       crosscheck backup device type disk ;

       crosscheck archivelog all;

       crosscheck backup of archivelog all spfile;

       delete force noprompt obsolete device type disk;

       delete force noprompt expired backup device type disk;

       delete force noprompt expired archivelog all device type disk;

       # CR Copy ALTER between blocks

       # Oracle auto does a LOG SWITCH as part of BACKUP block

       #

      sql 'alter system switch logfile';

      sql 'alter system archive log current';

       allocate channel c1 device type disk format='${BACKUPDIR1}/${TAG_DATE}_dbf_s%s_p%p';

       allocate channel c2 device type disk format='${BACKUPDIR2}/${TAG_DATE}_dbf_s%s_p%p';

       allocate channel c3 device type disk format='${BACKUPDIR3}/${TAG_DATE}_dbf_s%s_p%p';

       allocate channel c4 device type disk format='${BACKUPDIR4}/${TAG_DATE}_dbf_s%s_p%p';

       # allocate channel c1 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

       # allocate channel c2 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

       # allocate channel c3 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

       # allocate channel c4 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

       backup

          incremental level 1

          filesperset 4

          # format '${BACKUPDIR}/${TAG_DATE}_dbf_s%s_p%p'

          database

          tag = '${TAG_DATE}_bkup_db'

          include current controlfile for standby;

       release channel c1;

       release channel c2;

       release channel c3;

       release channel c4;

     }

     exit;

     # ${SPFILEFLAG} ;

     # exit;

     # list backup summary device type disk ;

EOF

   Status=${?}

   return ${Status}

}

#

# Backup the Archived Logs

#

bkup_arch_to_disk()

{

   # BKRETENT is the number of days to keep backups.

   # If BKRETENT is not set in dbmodes.txt then set the default REDUNDANCY to 5.

   #

   BKRETENT=`get_dbmodes_value ${ORACLE_SID} BKRETENT 5`

   log "ORACLE_SID = ${ORACLE_SID} : Backup Retention = ${BKRETENT} days"

   TAG_DATE_FORMAT=%Y_%m_%d_%H%M

   TAG_DATE=`date +${TAG_DATE_FORMAT}`

   log "ORACLE_SID = ${ORACLE_SID} : Backing up the archived logs with RMAN"

   export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

   ${ORACLE_HOME}/bin/rman target / catalog ${RMANDB_USERNAME}/${RMANDB_PASSWORD}@rmancatc.snt.bst.bls.com << EOF >> ${LOGFILE}

     CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKUPDIR}/cf_%F';

     CONFIGURE CONTROLFILE AUTOBACKUP ON;

     CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF ${BKRETENT} DAYS;

     CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

     CONFIGURE BACKUP OPTIMIZATION OFF;

     ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

     CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' CLEAR;

     run {

       crosscheck backup device type disk ;

       crosscheck archivelog all device type disk;

       # crosscheck backup of archivelog all spfile;

        delete force noprompt obsolete device type disk;

        delete force noprompt expired backup device type disk;

        delete force noprompt expired archivelog all device type disk;

        delete force noprompt backupset device type disk completed before 'sysdate-${BKRETENT}';

       # CR Copy ALTER between blocks

       # ?? Oracle auto does a LOG SWITCH as part of BACKUP block

       # Oracle does an ALTER SYSTEM ARCHIVE LOG CURRENT as part of the BACKUP ARCHIVELOG block

       #

       sql 'alter system switch logfile';

       sql 'alter system archive log current';

       allocate channel c1 device type disk format='${BACKUPDIR1}/${TAG_DATE}_arc_s%s_p%p';

       allocate channel c2 device type disk format='${BACKUPDIR2}/${TAG_DATE}_arc_s%s_p%p';

       allocate channel c3 device type disk format='${BACKUPDIR3}/${TAG_DATE}_arc_s%s_p%p';

       allocate channel c4 device type disk format='${BACKUPDIR4}/${TAG_DATE}_arc_s%s_p%p';

       # allocate channel c1 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_arc_s%s_p%p';

       # allocate channel c2 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_arc_s%s_p%p';

       # allocate channel c3 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_arc_s%s_p%p';

       # allocate channel c4 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_arc_s%s_p%p';

       backup

          incremental level 0

          filesperset 4

          # format '${BACKUPDIR}/${TAG_DATE}_arc_s%s_p%p'

          archivelog all delete input

          # archivelog all

          tag = '${TAG_DATE}_bkup_arch';

       release channel c1;

       release channel c2;

       release channel c3;

       release channel c4;

       # sql 'alter database backup controlfile to trace';

       crosscheck archivelog all device type disk;

       delete force noprompt obsolete device type disk;

     }

     list backup summary device type disk ;

     exit;

EOF

   Status=${?}

   return ${Status}

}

#

# Get the Overall Backup Status

#

get_bkup_status()

{

    Prev_Status=${1}

    Status_Code=${2}

    New_Status=${Prev_Status}

    if [ ${Prev_Status} = INPROGRESS ]

    then

       if [ ${Status_Code} -eq 0 ]

       then

          New_Status=SUCCESSFUL

       else

          New_Status=UNSUCCESSFUL

       fi

    else

       if [ ${Prev_Status} != UNSUCCESSFUL -a ${Status_Code} != 0 ]

       then

          New_Status=WARNING

       fi

    fi

    echo ${New_Status}

}

###########################################

#

#                   Main

#

###########################################

ARCHIVEONLY=N

BKUPTOTAPE=N

INCREMENTALONLY=N

RMANDB_USERNAME=rman102

RMANDB_PASSWORD=cat

USAGE="USAGE - `basename ${0}` [-a] archiveonly [-s SID] [-i] incremental [-t] tape backup [-d] data domain backup [-h] help"

while getopts :aitdhs: opt;

do

   case "${opt}" in

      a) ARCHIVEONLY=Y

          ;;

      i) INCREMENTALONLY=Y

          ;;

      t) BKUPTOTAPE=T

         T_MODE=1

          ;;

      d) BKUPTOTAPE=D

         D_MODE=1

          ;;

      h) echo ""

         echo "   $USAGE"

         echo ""

         echo "   -a archive log backup only"

         echo "   -s if you have multiple database on one server"

         echo "       and only want to backup one - REQUIRES an argument."

         echo "   -i incrmental backup only"

         echo "   -t tape backup"

         echo "   -d data domain backup"

         echo "   -h display help"

         echo ""

         exit

          ;;

      s) ORACLE_SID_LIST=$OPTARG

         CMD_LINE_SID=Y

          ;;

      *) echo ""

         echo "   $USAGE"

         echo ""

         echo "   -a archive log backup only"

         echo "   -s if you have multiple database on one server"

         echo "       and only want to backup one - REQUIRES an argument."

         echo "   -i incrmental backup only"

         echo "   -t tape backup"

         echo "   -d data domain backup"

         echo "   -h display help"

         echo ""

         exit

          ;;

   esac

done

if [ -x /usr/ucb/whoami ]

then

   WHOAMI=/usr/ucb/whoami

else

   WHOAMI=whoami

fi

#if [ `${WHOAMI}` = oracle ]

#then

   . ~oracle/oracle_env

#else

  if [ -x ~/.local ]

  then

     . ~/.local

  else

     echo "###################################"

     echo "# ERROR - ~/.local does not exist #"

     echo "###################################"

     exit

  fi

#fi

#

# Define the LOGFILE

#

format_backup_logfile_name

if [ T_MODE -eq 1 ] && [ D_MODE -eq 1 ]

then

  echo "#######################################################" >> ${LOGFILE}

  echo "" >> ${LOGFILE}

  echo "Tape Mode and Data Domain Mode are exclusive - Pick One" >> ${LOGFILE}

  echo "" >> ${LOGFILE}

  echo "$USAGE" >> ${LOGFILE}

  echo "" >> ${LOGFILE}

  echo "#######################################################" >> ${LOGFILE}

  exit -1

fi

# Load Common Functions

#

. ${ADMIN_DIR}/common_functions.sh

# Set the Date Format

#

DATE_FORMAT=%Y%m%d%H%M

# Set the Oracle Environment

#

# Get the list of ORACLE_SIDs

#

if [ -z ${CMD_LINE_SID} ]

then

   ORACLE_SID_LIST=`list_oracle_sids`

fi

log "HOST = ${HOST}"

for ORACLE_SID in ${ORACLE_SID_LIST}

do

   # Determine whether SID should be Processed

   #

   #PROCESS_FLAG=`get_process_flag ${ORACLE_SID}`

   if [ ${ORACLE_SID} = 'rman' ]

   then

      PROCESS_FLAG = N

   fi

   log "ORACLE_SID = ${ORACLE_SID} : Process Flag = ${PROCESS_FLAG}"

   if [ ${PROCESS_FLAG} = N ]

   then

      log "Skipping backup of ${ORACLE_SID}"

   else

      log "ORACLE_SID = ${ORACLE_SID} : Processing ..."

      if [ -x /usr/ucb/whoami ]

      then

         WHOAMI=/usr/ucb/whoami

      else

         WHOAMI=whoami

      fi

#      if [ `${WHOAMI}` = oracle ]

#      then

#         . ~oracle/oracle_env ${ORACLE_SID}

#      else

#         . ~/oracle/local/bin/oraprof ${ORACLE_SID}

#      fi

. ~oracle/oracle_env ${ORACLE_SID}

. ~/oracle/local/bin/oraprof ${ORACLE_SID}

      START_DATE=`date +'%Y%m%d%H%M%S'`

      BKUPDISABLE=`get_dbmodes_value ${ORACLE_SID} BKUPDISABLE N`

      if [ ${BKUPDISABLE} = Y ]

      then

         log "Backups disabled for Host = ${HOST} : ORACLE_SID = ${ORACLE_SID}"

         REPORT_STATUS='DISABLED'

         ${ADMIN_DIR}/insert_backup_status.sh ${ORACLE_SID} ${REPORT_STATUS} ${START_DATE} ${START_DATE} | tee -a ${LOGFILE}

      else

         DBMODE=`get_dbmodes_value ${ORACLE_SID} DBMODE PRIMARY`

         log "ORACLE_SID = ${ORACLE_SID} : DBMODE = ${DBMODE}"

         if [ ${DBMODE} = STANDBY ]

         then

            log "SID=${ORACLE_SID} : Skipping backup for Standby Database"

            REPORT_STATUS='STANDBY'

            ${ADMIN_DIR}/insert_backup_status.sh ${ORACLE_SID} ${REPORT_STATUS} ${START_DATE} ${START_DATE} | tee -a ${LOGFILE}

         else

            ORACLE_HOME=`get_Oracle_Home ${ORACLE_SID}`

            log "ORACLE_SID = ${ORACLE_SID} : ORACLE_HOME = ${ORACLE_HOME}"

            # BACKUPDIR=`get_Backup_Dir ${ORACLE_SID}`

            get_bkup_dirs

            log "ORACLE_SID = ${ORACLE_SID} : Backup Directory   = ${BACKUPDIR}"

            log "ORACLE_SID = ${ORACLE_SID} : Backup Directory 1 = ${BACKUPDIR1}"

            log "ORACLE_SID = ${ORACLE_SID} : Backup Directory 2 = ${BACKUPDIR2}"

            log "ORACLE_SID = ${ORACLE_SID} : Backup Directory 3 = ${BACKUPDIR3}"

            log "ORACLE_SID = ${ORACLE_SID} : Backup Directory 4 = ${BACKUPDIR4}"

            BKUPTYP=`get_dbmodes_value ${ORACLE_SID} BKUPTYP ONLINE`

            log "ORACLE_SID = ${ORACLE_SID} : BKUPTYP = ${BKUPTYP}"

            REPORT_STATUS='INPROGRESS'

            ${ADMIN_DIR}/insert_backup_status.sh ${ORACLE_SID} ${REPORT_STATUS} ${START_DATE} | tee -a ${LOGFILE}

            # Start background process to sleep for $BKUPTIMELIMIT minutes and then verify

            # the backup for this SID has completed using the lock file created above

            # (if the lock file still exists after sleeping, then the backup must have hung)

            #

            # check_for_hanging_backup ${ORACLE_SID} ${START_DATE}

            STATUS=0

            # List Contents of Backup Directory before Backup

            #

            list_bkup_dir ${BACKUPDIR}

            log "Starting ${BKUPTYPE} Backup"

            # Initialize Report Status

            #

            # REPORT_STATUS=UNKNOWN

              # Incremental Backup Of The Database

              #

            if [ ${INCREMENTALONLY} = Y ]

            then

              incremental_bkup_db_to_disk

              # echo "Doing Incremental Backup" $INCREMENTALONLY $ARCHIVEONLY

              STATUS=${?}

              # Log the Exit Code

              #

              log "ORACLE_SID = ${ORACLE_SID} : Incremental Backup of database completed with return code of ${STATUS}"

              REPORT_STATUS=`get_bkup_status ${REPORT_STATUS} ${STATUS}`

              log "ORACLE_SID = ${ORACLE_SID} : Report Status = ${REPORT_STATUS}"

              ARCHIVEONLY=Y

            fi

            if [ ${ARCHIVEONLY} != Y ]

            then

              # Full Backup Of The Database

              #

              bkup_db_to_disk

              #echo "Doing Full Backup" $INCREMENTALONLY $ARCHIVEONLY

              STATUS=${?}

              # Log the Exit Code

              #

              log "ORACLE_SID = ${ORACLE_SID} : Backup of database completed with return code of ${STATUS}"

              REPORT_STATUS=`get_bkup_status ${REPORT_STATUS} ${STATUS}`

              log "ORACLE_SID = ${ORACLE_SID} : Report Status = ${REPORT_STATUS}"

            fi

            # Backup the Archived Logs

            #

            bkup_arch_to_disk

            STATUS=${?}

            # Log the Exit Code

            #

            log "ORACLE_SID = ${ORACLE_SID} : Backup of archived logs completed with return code of ${STATUS}"

            REPORT_STATUS=`get_bkup_status ${REPORT_STATUS} ${STATUS}`

            log "ORACLE_SID = ${ORACLE_SID} : Report Status = ${REPORT_STATUS}"

            if [ ${BKUPTOTAPE} = T ]

            then

              log "ORACLE_SID = ${ORACLE_SID} : Backup sets will be transferred to tape"

              # Transfer the Database Backup Sets to Tape

              #

              bkup_db_to_tape Oracle-RMAN

              STATUS=${?}

              # Log the Exit Code

              #

              log "ORACLE_SID = ${ORACLE_SID} : Transfer of backup sets to tape completed with return code of ${STATUS}"

              REPORT_STATUS=`get_bkup_status ${REPORT_STATUS} ${STATUS}`

              log "ORACLE_SID = ${ORACLE_SID} : Report Status = ${REPORT_STATUS}"

            fi

            if [ ${BKUPTOTAPE} = D ]

            then

              log "ORACLE_SID = ${ORACLE_SID} : Backup sets will be transferred to tape"

              # Transfer the Database Backup Sets to Data Domain

              #

              bkup_db_to_tape Oracle-RMAN-DD

              STATUS=${?}

              # Log the Exit Code

              #

              log "ORACLE_SID = ${ORACLE_SID} : Transfer of backup sets to Data Domain completed with return code of ${STATUS}"

              REPORT_STATUS=`get_bkup_status ${REPORT_STATUS} ${STATUS}`

              log "ORACLE_SID = ${ORACLE_SID} : Report Status = ${REPORT_STATUS}"

            fi

            # Copy init.ora File to Alternate Location

            # ??? Do we need or want to do this ???

            # ??? Umesh and Romayne say yes ???

            #

            # if [ -s ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora ]

            # then

            #     cp ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora ${BACKUPDIR}/init${ORACLE_SID}.ora.`date +%d-%m-%Y.%H:%M:%S`

            #     cp ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora ${BACKUPDIR}/${TAG_DATE}_init${ORACLE_SID}

            # fi

            # Copy Password File to Alternate Location

            # ??? Do we need or want to do this ???

            # if [ -s ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} ]

            # then

            #     cp ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} ${BACKUPDIR}/orapw${ORACLE_SID}.`date +%d-%m-%Y.%H:%M:%S`

            #     cp ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} ${BACKUPDIR}/${TAG_DATE}_orapw${ORACLE_SID}

            # fi

            # Set the Status for Report

            if [ ${REPORT_STATUS} = UNSUCCESSFUL ]

            then

               #BKUPFAILALERT=`get_dbmodes_value ${ORACLE_SID} BKUPFAILALERT N`

               BKUPFAILALERT=`get_dbmodes_value ${ORACLE_SID} BKUPFAILALERT`

               log "ORACLE_SID = ${ORACLE_SID} : BKUPFAILALERT = ${BKUPFAILALERT}"

               if [ ${BKUPFAILALERT} = Y ]

               then

                   ${ADMIN_DIR}/send_mail.sh critical "Backup Failure" "${HOST}:${ORACLE_SID}:Backup Failed"

               fi

            fi

            # Log the Backup Status

            #

            log "RMAN: The Full Online Backup of ${ORACLE_SID} was ${REPORT_STATUS}"

            # List Contents of Backup Directory after Backup

            #

            list_bkup_dir ${BACKUPDIR}

         fi

         # Cleanup after the Backup is done

         #

         end_backup ${ORACLE_SID} ${START_DATE}

         # 06/08/2006 SRF - replaced call to report_backup_status.sh with call to update_backup_status.sh

         #                  which updates the record written earlier in this script (key is HOST, SID

         #                  and START DATE) - adding the END DATE and changing the STATUS

         ${ADMIN_DIR}/update_backup_status.sh ${ORACLE_SID} ${REPORT_STATUS} ${START_DATE} | tee -a ${LOGFILE}

         # 07/02/2008     -  The script update_rman_transition_status.sh is called to update the backup type used

         #                   by the database,this information is used to track the transition of backups

         #                   to RMAN.

         ${ADMIN_DIR}/update_rman_transition_status.sh ${HOST} ${ORACLE_SID} RMAN | tee -a ${LOGFILE}

      fi

   fi

done

#

# Exit the Script with the Exit Status

#

exit ${STATUS}

Database Tables notes

Heap Organized tables:

•  A full scan of the table will retrieve the data as it hits it, not in the order of insertion.
• Oracle will place the data where it fits, not in any order by date or transaction.
• You should think of a heap organized table as a big unordered collection of rows
• changing the ENABLE STORAGE IN ROW to DISABLE STORAGE IN ROW, which would disable the storage of the LOB data in the row with the structured data, causing it to be stored in another segment.
• PCTFREE: Both ASSM and MSSM
• INITRANS: Both ASSM and MSSM
• FREELIST: MSSM only.
• PCTUSED: MSSM only.
• LOB data that is stored out of line in the LOB segment does not make use of the PCTFREE/PCTUSED parameters set for the table. These LOB blocks are managed differently: they are always filled to capacity and returned to the FREELIST only when completely empty.

CRS log location : /opt/app/oragrid/orabase/diag/crs/nmsjf1d12/crs/trace

[root@node1 sysctl.d]# sysctl -ar '\.rp_filter'
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.bond0.rp_filter = 1
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.eth0.rp_filter = 1
net.ipv4.conf.eth1.rp_filter = 1
net.ipv4.conf.eth2.rp_filter = 1
net.ipv4.conf.eth3.rp_filter = 1
net.ipv4.conf.eth4.rp_filter = 1
net.ipv4.conf.eth5.rp_filter = 1
net.ipv4.conf.eth6.rp_filter = 1
net.ipv4.conf.eth7.rp_filter = 1
net.ipv4.conf.lo.rp_filter = 0

Click Here to Register

Click here to join the meeting

Join the workshop  

Click here to join the meeting     aws solution arc associate

Click here  --->trinocate awsarc asso certificate 

shell: "time {{ oracle_base }}/product/{{ item.oracle_version_db }}/{{ item.home }}/bin/dbca -silent -createDatabase -templateName {{ dbca_templatename }} -gdbname {{ gdb }} -sid {{ sid }} -sysPassword {{ dba_password }} -systemPassword {{ dba_password }} -databaseconftype {{ item.oracle_db_type }} -nodelist {{ nodelist }}"

    with_items: "{{ oracle_databases }}"

trainocate.com/certificate/26feeecb-b7cf-4209-8f14-a795b6aac284

aws arasc exam readiness --> Click here to join the meeting

gcp data engineer - >  Join the workshop  

gcp asc exam readiness: Join the workshop  

https://partner-academy.databricks.com/learn/course/1765/generative-ai-fundamentals

https://partner-academy.databricks.com/learn/course/1749/large-language-models-llms-application-through-production

https://partner-academy.databricks.com/learn/course/internal/view/elearning/1804/large-language-models-llms-foundation-models-from-the-ground-up

https://www.databricks.com/resources/learn/training/generative-ai-fundamentals

              Build Foundational knowledge of generative AI:- 3-4 hours (Self-Paced with knowledge assignment)

https://v2.accounts.accredible.com/login?app=recipient-portal&origin=https:%2F%2Fwww.credential.net%2F17cec98b-3b0c-41cf-b469-de4108baa9c3%3Fkey%3De8fd1c551d27f405bec94054c2194ea9435fac322bac51849c4cee76274c59

You have been assigned a Certification Exam Vouchers (GSI) voucher. ACE-GCP
Voucher: AWjj2W23jppA4Nmd

You have been assigned a *PCA/PCK - ACE - English voucher.  --> PCA-GCP
Voucher: APP52P23e95WZhZQ

Click here to join the meeting

Join the meeting now   -->17-Jun-2024

Click Here to Join

 https://testprep.cloudthat.com/login/signup.php

: Click on the link given here to access the course: https://testprep.cloudthat.com/course/view.php?id=1978 Step 4: Put the Enrolment key (code) “4SRE39PcUj” in the space provided.

 

Aws Solution architect associate training link oct-21 to oct-28th

https://teams.microsoft.com/l/meetup-join/19%3ameeting_MDQ4MWUzZTUtMjNhNy00ZTFiLWFlMjgtMjk4Yzg4NGQ0YmZm%40thread.v2/0?context=%7b%22Tid%22%3a%22815db2f7-1e3a-438f-8bdd-e55de825adee%22%2c%22Oid%22%3a%225a1243b9-21b4-423a-a8e0-c4f6b498eeb3%22%7d

    select a.username, a.sid,a.serial#, a.inst_id, a.program, a.machine,A.STATUS,a.sql_id,a.LAST_CALL_ET

from gv$session a,

gv$process b

where a.USERNAME not in('NL710F','SYS','DBSNMP','PUBLIC') AND A.STATUS='INACTIVE'

and a.inst_id = b.inst_id and a.paddr = b.addr order by a.LAST_CALL_ET,a.sql_id;

aws MLOps labs

==============

1. Create AWS Builder ID:

https://us-east-1.student.classrooms.aws.training/class/cninzrmGifPrKXSQUVxML3

- do not use any other signup/signin options.

- do use official @techm email ONLY.

- be patient with OTP (wait at least 5 minutes)

2. After login verify you see 6 labs. 

3. Click on "Student Guide" button (above list of labs), and from the next page (Vitalsource), click on "Continue without an account". Don't signup/signin again on Vitalsource page. 

4. In the Ebook, on the top right corner find 3 dots. From there you can fine print option, provide page range and download PDF file. Download may take some time. 

Vulnerability in the Oracle Database RDBMS Security component of Oracle Database Server.  Supported versions that are affected are 19.3-19.23. Easily exploitable vulnerability allows high privileged attacker having Execute on SYS.XS_DIAG privilege with network access via Oracle Net to compromise Oracle Database RDBMS Security.  Successful attacks of this vulnerability can result in takeover of Oracle Database RDBMS Security. CVSS 3.1 Base Score 7.2 (Confidentiality, Integrity and Availability impacts).  CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:H/I:H/A:H).

ASM Queries

 SET LINESIZE  550

SET PAGESIZE  9999

SET VERIFY    off  

 

COLUMN full_alias_path        FORMAT a63                  HEAD 'File Name'

COLUMN system_created         FORMAT a8                   HEAD 'System|Created?'

COLUMN type                   FORMAT a18                  HEAD 'File Type'

COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'

COLUMN striped                FORMAT a8                   HEAD 'Striped'

COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'

COLUMN disk_group_name        noprint

SELECT

    CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path

  ,  space/(1024*1024*1024*2) "InGB"

  , NVL(LPAD(type, 18), 'DATAC1')  type

  , creation_date

  , disk_group_name

  , LPAD(system_created, 4) system_created

FROM

    ( SELECT

          g.name               disk_group_name

        , a.parent_index       pindex

        , a.name               alias_name

        , a.reference_index    rindex

        , a.system_created     system_created

        , f.bytes              bytes

        , f.space              space

        , f.type               type

        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date

      FROM

          v$asm_file f RIGHT OUTER JOIN v$asm_alias     a USING (group_number, file_number)

                                   JOIN v$asm_diskgroup g USING (group_number)

    )

WHERE type IS NOT NULL

and  space/(1024*1024*1024*2) >500

START WITH (MOD(pindex, POWER(2, 24))) = 0

    CONNECT BY PRIOR rindex = pindex

order by space/(1024*1024*1024*2) desc 

/

Migration

 Migration

select  count(1),segment_type from dba_segments where tablespace_name='LOB_NEW' group by segment_type;

SELECT TABLE_OWNER,TABLE_NAME,COLUMN_NAME,LOB_NAME,PARTITION_NAME,LOB_PARTITION_NAME,SECUREFILE,TABLESPACE_NAME

FROM DBA_LOB_PARTITIONS 

WHERE TABLE_OWNER = 'CCUSER' 

AND TABLESPACE_NAME = 'LOB'

AND TABLE_NAME = 'CC_MESSAGE';

select 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION '||partition_name||' LOB ('||column_name||') STORE AS BASICFILE (TABLESPACE LOB_NEW) parallel 32;' from 

DBA_LOB_PARTITIONS 

WHERE TABLE_OWNER = 'CCUSER' 

AND TABLESPACE_NAME = 'LOB'

AND TABLE_NAME = 'CC_MESSAGE';

select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE PROD_DATA;' from DBA_TABLES WHERE OWNER ='PROD';

select 'ALTER INDEX ' || OWNER || '.'|| index_name ||' rebuild tablespace INDX_TB online;' from dba_indexes where owner='PROD';

can you try this :

create tablespace lepdata 
datafile '+p1rac1d4_oradata' size 20g
logging
online
permanent
blocksize 8192
extent management local autoallocate
default nocompress
segment space management auto;

Course URL: - https://learn.microsoft.com/en-us/training/courses/AZ-104T00?WT.mc_id=ilt_partner_webpage_wwl&ocid=4428290&DCS=DEPR2340032#study-guide 

Achievement Code URL: - https://learn.microsoft.com/en-us/users/me/achievements?redeem=RDG8DZ&WT.mc_id=ilt_partner_webpage_wwl&ocid=4428290&DCS=DEPR2340032

https://ind01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fforms.office.com%2Fr%2FNx5dy3JiLW&data=05%7C01%7CRM00759529%40TechMahindra.com%7C465303814e924bfb158708db1b1939f9%7Cedf442f5b9944c86a131b42b03a16c95%7C0%7C0%7C638133567383492233%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=C%2FAc9WeKaVKB0QRGjFuk0x%2B6WZQRRgrrrjtKsJJtwkY%3D&reserved=0

important points

1.  Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle 8i introduced the Statspack functionality which Oracle 9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).
2. Oracle 10g took code instrumentation in the kernel to a whole new level with the introduction of the Automatic Workload Repository (AWR) and Active Session History (ASH) features. Oracle 11g takes that further with options such as the Automatic Diagnostic Repository (ADR) and the SQL Performance Analyzer (SPA).
3. In Oracle database 11g, a new facility, the ADR, was added. As part of this new facility, there’s a new V$ view—V$DIAG_INFO.
4. select * from v$diag_info;
5. Starting in Oracle database 11g, The ADRCI tool allows you to review “problems” (critical errors in the database) and incidents (occurrences of those critical errors) and to package them up for transmission to support
   adrci> show tracefile -I 6177
   This shows me the location of the trace file for incident number 6177. Further, I can see a lot of detail about the incident if I so choose:
   adrci> show incident -mode detail -p "incident_id=6177"
6. Automatic Storage Management (ASM): This is a new feature of Oracle 10g Release1 (for both Standard and Enterprise editions).
7. Oracle 11g Release 2, ASM provides not only this database file system but optionally a clustered file system as well, which is described next.
8. This feature of multiple block sizes was introduced for the purpose of making transportable tablespaces usable in more cases.
9. Tablespaces with multiple block sizes should be used to facilitate transporting tablespaces; they are not generally used for anything else.
10. The change-tracking file is a new, optional file for use with Oracle 10g Enterprise Edition and above
11. Flashback logs were introduced in Oracle 10g in support of the FLASHBACK DATABASE command
12. The Data Pump format is exclusive to Oracle 10g Release 1 and above—it did not exist in any Oracle9i release, nor can it be used with that release.
13. PGA_AGGREGATE_TARGET: This parameter controls how much memory the instance should allocate, in total, for all work areas used to sort or hash data.

14. select sum(bytes) from v$sgastat where pool = 'shared pool';

    The sharedpool holds many other structures that are outside the scope of the corresponding parameter. The SHARED_POOL_SIZE is typically the largest contributor to the shared pool as reported by the SUM(BYTES), but it is not the only contributor.

    In Oracle 10g, the SHARED_POOL_SIZE parameter controls the size of the shared pool, whereas in Oracle9i and before, it was just the largest contributor to the shared pool. You should review your 9i and before actual shared pool size (based on V$SGASTAT) and use that figure to set your SHARED_POOL_SIZE parameter in Oracle 10g and above.

15. When someone complains of deadlocks in the database, I have them run a script that finds unindexed foreign keys; 99 percent of the time we locate an offending table. By simply indexing that foreign key, the deadlocks—and lots of other contention issues—go away.

16. The number one cause of deadlocks in the Oracle database, in my experience, is unindexed foreign keys. (Thenumber two cause is bitmap indexes on tables subject to concurrent updates, which we’ll cover in Chapter 11).

17. The higher the transaction isolation level, the locking overhead can increase while user concurrency can decrease.

18. GRANT execute ON dbms_monitor TO scott;

19. What happens when you run an UPDATE
    statement, as follows, and while that statement is running, someone updates a row it has yet to read from Y=5 to Y=6
    and commits?
    Update t Set x = 2 Where y = 5;

20. So, why are constraints validated after the SQL statement executes? Why not during? This is because it is very natural for a single statement to make individual rows in a table momentarily inconsistent.
21. we also have the ability to defer constraint checking, which can be quite advantageous for

    various operations. The one that immediately jumps to mind is the requirement to cascade an UPDATE of a primary key to the child keys.
22. Page:290 : the bottom line is, only use deferrable constraints where you have an identified need to use them. They introduce subtle side effects that could cause differences in your physical implementation (non-unique vs. unique indexes) or in your query plans 
23. With the single UPDATE statement, we just reissue the

    UPDATE. We know that it will entirely succeed or entirely fail; there will not be partial work to worry about.
24. My final words on bad transaction habits concern the one that arises from using the popular programming APIs ODBC and JDBC. These APIs “autocommit” by default.
25. Every Oracle database has at least two online redo log groups with at least a single member (redo log file) in each group.
26. Those two things together—that the segment was actually created by the INSERT but not “uncreated” by the ROLLBACK, and that the new formatted blocks created by the INSERT were scanned the second time around—show that a rollback is a logical “put the database back the way it was” operation. The database will not be exactly the way it was, just logically the same.

RMAN Backup status

 

This script will report on all currently running RMAN backups like full, incremental & archivelog backups:

col STATUS format a9

col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

Below script will give you SID, Total Work, Sofar & % of completion.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 

ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

Oracle ASM

Column HOT_USED_MB & COLD_USED_MB in V$ASM_DISKGROUP

 

What's the below two columns use for?

HOT_USED_MB 
Number of used megabytes in the hot region  

COLD_USED_MB 
Number of used megabytes in the cold region 

you can do it all using a new attribute of diskgroup which allows two values: “hot” and “cold”. The former puts the data near the edges while the latter places it near the center. This is done via templates. Let’s define a template – hot_files – for those most accessed files.

alter diskgroup dg1 add template hot_files attributes (hot) /                        

Diskgroup altered.  

Once the template is created, you can use it to create the datafiles using that template:

create tablespace hot_ts datafile '+DATA(hot_files)/hot_ts_01.dbf' size 1M / 

in a disk, the outer part have greater performance than the inner part.  ---imagine a disk as your DVD or CD.

outer parts are called hot region and inner parts are called cold region.

oracle give you the flexibility to be able to place files in either hot or cold region. IF the geometry  is not mask from ASM instance.

++++++++++++++The below is the db alert log file during startup, ASMB is the process from database to ASM instance communication for data write and read+++++++++++++

This instance was first to open

Starting background process ASMB

Tue Sep 06 23:54:44 2022

ASMB started with pid=35, OS id=5410

Tue Sep 06 23:54:44 2022

NOTE: ASMB registering with ASM instance as Standard client 0xffffffffffffffff (reg:1656733188) (new connection)

Starting background process RBAL

Tue Sep 06 23:54:44 2022

RBAL started with pid=36, OS id=5414

Tue Sep 06 23:54:44 2022

NOTE: Loaded library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so

Tue Sep 06 23:54:44 2022

NOTE: ASMB connected to ASM instance +ASM1 osid: 5412 (Flex mode; client id 0xffffffffffffffff)

NOTE: initiating MARK startup

Starting background process MARK

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQL> alter database datafile '+TESTDG/NMS/DATAFILE/testtbs.256.1114369479' offline;

Database altered.

SQL>

Tablespace monitoring

 set linesize 180

select b.db_unique_name, a.tablespace_name

               ,sum(a.tots)/1024/1024/1024 "Total Size(GB)"

               ,sum(a.sumb)/1024/1024/1024 "Total Free(GB)"

           ,sum(a.sumb)*100/sum(a.tots) "% Free"

                    ,sum(a.tots-a.sumb)*100/sum(a.tots) "% Used"

          ,sum(a.largest)/1024/1024/1024 "Max Free"

           from

          (

            select tablespace_name

               ,0 tots

               ,sum(bytes) sumb

              ,max(bytes) largest

        from dba_free_space a

      group by tablespace_name

      union

      select tablespace_name

            ,sum(bytes) tots

            ,0

            ,0

       from dba_data_files

      group by tablespace_name) a , v$database b , v$instance c

 where a.tablespace_name NOT like 'UNDO%' and c.INSTANCE_NUMBER=1

        group by a.tablespace_name,b.db_unique_name,c.INSTANCE_NUMBER

having sum(a.sumb)*100/sum(a.tots) > 50

and sum(a.tots)/1024/1024/1024 > 100

order by sum(a.tots)/1024/1024/1024 desc

     ;

Note: use gv$instance if it is a rac

2. tablespace_monitoring.sh

HOST=`hostname | cut -b1-14`

dt=`date +%d%m%Y`

BASE_DIR=/u01/app/oracle/scripts/tablespace_monitoring

LOG_DIR=/u01/app/oracle/scripts/tablespace_monitoring/LOG

SPOOL_FILE=${LOG_DIR}/Tablespace_status_${dt}.html

ps -ef|grep smon|grep -v grep |grep -v ASM |awk '{print $8}'|cut -b10-20 > ${BASE_DIR}/NONPROD_DATABASE_LIST.txt

while read line

do

export ORACLE_SID=$line

export ORACLE_HOME=`grep -w "$line" /etc/oratab | awk -F":" '{print $2}'`

echo $line

#$ORACLE_HOME/bin/sqlplus -s / as sysdba  << EOF > ${TEMP_SPOOL}

$ORACLE_HOME/bin/sqlplus -s -M "HTML ON" "/ as sysdba"<<EOF >> ${SPOOL_FILE}

set space 0

set feedback off

set echo off

set serveroutput off

@'$BASE_DIR/datafile_monitoring.sql';

exit

EOF

done <${BASE_DIR}/NONPROD_DATABASE_LIST.txt

fline=`wc -l ${SPOOL_FILE}|awk '{print $1}'`

if [ $fline -gt 0 ]

then

${BASE_DIR}/mail.sh ${SPOOL_FILE} ${HOST}

fi

Testing data

1.CREATE TABLE HR.RTABLE ( RID NUMBER PRIMARY KEY, NOTES VARCHAR2(20));

2.create a procedure that will populate the table.

CREATE PROCEDURE HR.POPULATE_RTABLE ( N NUMBER ) 
IS 
/* this procedure inserts N records into RTABLE */ 
V INTEGER; 
BEGIN 
-- get the maximum ID from the table 
SELECT MAX(RID) INTO V FROM RTABLE; 
FOR I IN 1..N LOOP 
INSERT INTO RTABLE (RID, NOTES) VALUES(I, 'record ' || TO_CHAR(I)); 
-- end the transactions every 5 inserts 
IF MOD(I,5)=0 THEN 
COMMIT; 
END IF; 
END LOOP; 
COMMIT; 
END POPULATE_RTABLE; 
/  

13. Populate the testing table with a few thousands rows. 

SQL> exec HR.POPULATE_RTABLE ( 10000 ) 

SQL> SELECT COUNT(*) FROM HR.RTABLE ;