Shell script to disable and enable Flashback in database

 *****************************************Version 1 *******************************************************


./$HOME/fra_delete_1.sh SID Database_role >$HOME/fradelete.log 2>&1



#!/bin/bash

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

#   Script Created by  :- ***************                                                              #

#   Date of Creation   :- *********************                                                   #

#   Purpose of Script  :- To CLear Flashback on all Databases                                 #

#   Mode of Usage      :- ALl database(Physica,Standby,Logical)                            #

#   Remarks            :- you need to have two script accompanied with this one        #

#                         for operating on primary and standby database                 #

#   Checks Required    :- Use only if Flashback has been enabled on Database           #

#                                                                                      #

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

if [ "$#" -ne 1 ]

   then

     echo " EROOR!! Wrong Arguments  Usage fra_delete <SID> " ;

     exit 1;

   else

     echo "Arguments are perfect and sid is:$1"

fi


filedate=$(date +%Y%m%d)

ORACLE_SID=$1 export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

tab='v$database'

MAILLIST=********

cat /dev/null > $HOME/FRA_DELETE_$filedate.log


# extract the ORACLE_HOME from /etc/oratab and validate

ORACLE_HOME=`grep "^$ORACLE_SID:" /etc/oratab | awk -F: '{print $2}'`

if [ -z "$ORACLE_HOME" ]

then

  echo " ERROR!! ORACLE_HOME for $ORACLE_SID not extracted from /etc/oratab ";

  exit 1;

else

  echo "Today : $filedate"

  echo "ORACLE_HOME for $ORACLE_SID :$ORACLE_HOME"

fi


DBROLE=`sqlplus -s / as sysdba << EOF

set heading off

set verify off

set echo off

select  database_role from $tab ;

exit

EOF`


if [ $DBROLE=PRIMARY ] && [ -e $HOME/fra.sql ]

  then

   echo "$ORACLE_SID IS $DBROLE"

fi


FRA_OUTPUT=`sqlplus -s / as sysdba << EOF >>$HOME/FRA_DELETE_$filedate.log

@$HOME/fra.sql

exit

EOF`


exit


####################################  Version 2######################################################################


oracle@pfs-4db100.mandtbank.com:  /home/oracle #-- cat fra_delete.sh

#!/bin/bash

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

#   Script Created by  :- Rama Krishna Bikkina                                         #

#   Date of Creation   :- 21/09/2020                                                   #

#   Purpose of Script  :- To CLear Flashback on all Databases                          #

#   Mode of Usage      :- ALl database(Physica,Standby,Logical)                        #

#   Remarks            :- you need to have two script accompanied with this one        #

#                         for operating on primary and standby database                 #

#   Checks Required    :- Use only if Flashback has been enabled on Database           #

#                                                                                      #

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

if [ "$#" -ne 2 ]

   then

     echo " EROOR!! Wrong Arguments  Usage fra_delete <SID> " ;

     exit 1;

   else

     echo "Arguments are perfect and sid is:$1"

fi


filedate=$(date +%Y%m%d)

ORACLE_SID=$1 export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

MAILLIST="MAIL ID"

cat /dev/null > $HOME/FRA_DELETE_$filedate.log


if [ $DBROLE=PRIMARY ] && [ -e $HOME/fra.sql ]

  then

   echo "$ORACLE_SID IS $DBROLE"

   else

   echo "$ORACLE_SID IS PHYSICAL STANDBY"

fi


FRA_OUTPUT=`sqlplus -s / as sysdba << EOF >>$HOME/FRA_DELETE_$filedate.log

@$HOME/fra.sql

exit

EOF`


ERRCNT=`grep -ic ora- $HOME/FRA_DELETE_$filedate.log`


if [[ $ERRCNT -gt 1 ]]

then

  mailx -s  "Error in FRA cleanup" $MAILLIST

else

  mailx -s  "FRA cleanup successful" $MAILLIST

fi


exit



oracle@pfs-4db100.mandtbank.com:  /home/oracle #-- cat fra_delete.sh

#!/bin/bash

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

#   Script Created by  :- ********************                                         #

#   Date of Creation   :-******************                                                   #

#   Purpose of Script  :- To Clear Flashback on all Databases                          #

#   Mode of Usage      :- All database(Physical Standby,Logical)                        #

#   Remarks            :- you need to have two script accompanied with this one        #

#                         for operating on primary and standby database                 #

#   Checks Required    :- Use only if Flashback has been enabled on Database           #

#                                                                                      #

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

if [ "$#" -ne 2 ]

   then

     echo " EROOR!! Wrong Arguments  Usage fra_delete <SID> " ;

     exit 1;

   else

     echo "Arguments are perfect and sid is:$1"

fi


filedate=$(date +%Y%m%d)

ORACLE_SID=$1 export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

MAILLIST="*********************"

cat /dev/null > $HOME/FRA_DELETE_$filedate.log

DBROLE=PRIMARY ; export DBROLE


if [ $DBROLE=$2 ] && [ -e $HOME/fra.sql ]

  then

   echo "$ORACLE_SID IS $DBROLE"

   else

   echo "$ORACLE_SID IS PHYSICAL STANDBY"

fi


FRA_OUTPUT=`sqlplus -s / as sysdba << EOF >>$HOME/FRA_DELETE_$filedate.log

@$HOME/fra.sql

exit

EOF`


exit





Comments

Post a Comment

Popular posts from this blog

Oracle Architecture SGA

Image
 Architecture :   Basic Memory Structures Oracle Database includes several memory areas, each of which contains multiple subcomponents. The basic memory structures associated with Oracle Database include: ·          System global area (SGA) The SGA is a group of shared memory structures, known as SGA components , that contain data and control information for one Oracle Database instance. All server and background processes share the SGA. Examples of data stored in the SGA include cached data blocks and shared SQL areas. ·          Program global area (PGA) A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. Oracle Database creates the PGA when an Oracle process starts.One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA . Database ...
Read more

FRA Usage and Administration

Image
 In Oracle database, the Flash Recovery Area or FRA is a location on disk where the database can create and manage several kinds of backup and recovery-related files. Main file types are archivelog, flashback log, backups, as well as mirrors for your control files and redo log files. All files in the FRA are Oracle-managed files. Using a Flash Recovery Area simplifies the administration of your database by automatically retaining them for as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed, because the space is needed for another backup and recovery-related purpose. Checking the current usage  You can check the configuration by looking at two parameters.The parameters are db_receovery_file_dest and second one db_recovery_file_dest_size .  db_receovery_file_dest point to location where the files will be located in Server . FOr normal single Databases this will be any directory for RAC this will b...
Read more

Format Date in Oracle Database - NLS_DATE_FORMAT

Image
 When you connect to the Oracle database, anything that has the DATE datatype, is displayed in the default format of DD-MON-YY, and no time component.   There are multiple ways to see the time component of a DATE column, in sqlplus. You can either alter your session to set the date format, or you can use a function in your select statement to alter the format of the column. alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS' ; select sysdate from dual;   SYSDATE ------------------- 12/01/2022 20:19:06   select to_char(sysdate, 'DD-MON-YY HH24:MI:SS' ) from dual;   TO_CHAR(SYSDATE,'D ------------------ 12-JAN-22 20:19:50   How about RMAN? How can you see the date and time a backup completed or started? By default you only see the same format as in sqlplus: DD-MON-YY. This format is not always enough. You try an alter session command in RMAN (same as above), and it doesn’t work. Instead, what is the solution? You must set the NLS_DA...
Read more