How to drop and recreate UNDO Tablespace

1.Determine the size of your undo tablespace

  SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';

  SUM(BYTES)/1024/1024/1024
  ---------------------------------
         12.09375
               
2.Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

  SQL>create undo tablespace UNDOTBS2 datafile '<PATH>\<FILENAME.DBF>'  size 400M;

   Tablespace created.
   
3. Edit your init.ora file and change the parameter "undo_tablespace=UNDOTBS2" so it points to the  newly created tablespace.
   
     Change undo_tablespace=UNDOTBS2
     Change undo_management=MANUAL

   Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step.7.  

4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.    

5. Startup the database

6. Confirm the new tablespace is in use:

   SQL> show parameter undo_tablespace

      NAME                 TYPE              VALUE
   -------------        -----------       ------------
   undo_tablespace      string            UNDOTBS2
   
7.Check the status of the undo segments and determine if all the segments in the old undo tablespace  are offline. The segments in the new tablespace may also show offline.
   
  SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

   OWNER         SEGMENT_NAME         TABLESPACE_NAME     STATUS
   ---------         --------------------             -------------------------  ----------
   PUBLIC         _SYSSMU3$                       UNDOTBS1                OFFLINE
   PUBLIC          SYSSMU2$                       UNDOTBS1                 OFFLINE
   PUBLIC          SYSSMU19$                     UNDOTBS2                 OFFLINE

    If the old segments are online, then they must be taken offline:

    SQL>alter rollback segment "_SYSSMU3$" offline;
    SQL>alter rollback segment "_SYSSMU2$" offline;

    This should be executed for all online rollback segments in the old tablespace.

8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo     tablespace:


    SQL>drop tablespace UNDOTBS1 including contents and datafiles;

    Tablespace dropped.


9. Edit your init.ora file do the following changes.

    undo_management='AUTO'
    undo_tablespace='UNDOTBS2'
   
10. Shutdown the database (shutdown immediate) and restart it.

11. Monitor the alert log simultaneously during all steps.

Reference Metalink Note: [ID 431652.1]

Importanat UNIX commands

1. How do you delete 3 days old log files?

    Usage: find /location -name "*.log" -mtime +3 -exec rm -rf{} \;

    Example : find ./ -name "*.req" -mtime +4 -exec ls -ltr {} \;

2. Display latest 20 largest files/directories in current directory?

    Solution: du -ka sort -n tail -20

3. How do you display/remove Specifice Month files in Unix?

    Solution: rm `ls -l grep Jun awk '{print $9}'`

4. How do you find the files which contains a specific Word?
    
    Solution : find /home/joe \( -type f \) -exec grep -l test {} \;

YUM command usuage

#yum search packagename
#yum install packagename

Understanding oracle versioning

Script (UNIX) to automate your Hot Backups

#! /bin/ksh
# The script can be tailored to meet your Environment
# Plug in appropriate values between the "<>" to meet your environment
# The following values needs to be replaced
# <ORACLE_SID>
# <ORACLE_HOME>
# <COLD_BACKUP_DIRECTORY>
# <email_address>
# <username>
# <password>

trap  2 3

# Set up environmental variables - Oracle specific
#
export ORACLE_SID=<ORACLE_SID>
export ORACLE_HOME=<ORACLE_HOME>
export PATH=$PATH:/$ORACLE_HOME/bin

# Set up variables to hold directory information
#
HOT=<HOT_BACKUP_DIRECTORY>
LOG=<BACKGROUND_DUMP_DEST>/alert_${ORACLE_SID}.log

# Make sure that the Lock File does not exists this confirms that
# the previous cron job has finished its activity of cold backup
#
if [ -f ${HOT}/hotbackup.lock ]; then
  mail <email_address> <<-EOF
    ERROR - HOT BACKUP For "${ORACLE_SID}" Has Failed
    Previous Hot Backup was not completely Successful !
  EOF
  exit 1
else
  touch ${HOT}/hotbackup.lock
fi

# Make sure that the Database Is up and running
# If not inform the concerned person
#
database_up=`ps -ef | grep pmon_${ORACLE_SID} | grep -v grep | wc -l`
if [ $database_up -eq 0 ]
then
  mail <email_address> <<-EOF
    ERROR - COLD BACKUP For "${ORACLE_SID}" Has Failed
    Database Should be up to take a trace of the data files - Please Investigate!
  EOF
  exit 1
fi

# Make sure that the Database Is running in archive log mode
# If not inform the concerned person
#
sqlplus /nolog <<-EOF
  connect / as sysdba
  spool ${HOT}/archive_mode.lst
  archive log list
  spool off
  EXIT
EOF
archive_mode=`grep -i "No Archive Mode*" ${HOT}/archive_mode.lst|grep -v grep|wc -l`
if [ $archive_mode -eq 1 ]
then
  mail <<email_address> <<-EOF
    ERROR - HOT BACKUP For "${ORACLE_SID}" Has Failed
  Database Is Not Running in Archive Log Mode as Desired
  during Hot Backup's - Please put the database in Archive Log Mode and restart!
  EOF
  exit 1
fi

# Find the count of 'alter tablespace .* begin backup' and
# 'alter tablespace .* end backup' in your alert logs
# if both are equal this signifies the previous hot backup is successful
#
if [ -f $LOG ] ; then
  Bgn_Bkp=egrep -ci '^alter tablespace .* begin backup' $LOG
  End_Bkp=egrep -ci '^alter tablespace .* end backup' $LOG
  Diff_Bkp=expr $Bgn_Bkp - $End_Bkp
else
  mail <email_address> <<-EOF
    ERROR - HOT BACKUP For "${ORACLE_SID}" Has Failed
    Could not locate Alert Log - Please Investigate !
  EOF
  exit 1
fi

# Logon to sqlplus - preferably as a DBA
# the sql script, generates a files called ora_HOT_backup.sql
# The file contains all the commands to perform hot backup's
# 1. Backup control file before starting hot backup.
# 2. Put first tablespace online.
# 3. Backup the datafile associated with the tablespaces and compress them (to save space).
# 4. Bring the first tablespace online.
# 5. Repeat steps 2 through 5 till all tablespaces are backed up
#
sqlplus <<username>/<password> <<-EOF
  column tablespace_name noprint
  column seqn noprint
  set pagesize 0
  set linesize 132
  set feedback off
  set sqlprompt ""
  Whenever SQLERROR exit FAILURE
  select chr(1) tablespace_name, -9999 seqn,
  'alter system switch logfile;'
  from dual
  UNION
  select chr(1) tablespace_name, -9998 seqn,
  'alter database backup controlfile to  ||
  '${HOT}/controlfile_${ORACLE_SID}.HOT.full.before reuse;'
  from dual
  UNION
  select tablespace_name, 0,
  'alter tablespace '||tablespace_name||' begin backup;'
  from sys.dba_tablespaces
  where status = 'ONLINE'
  UNION
  select tablespace_name, file_id,
  '!compress < '||file_name||'> ${HOT}/' ||
  substr(file_name,instr(file_name,'/',-1) + 1) || '.Z'
  from sys.dba_data_files
  where status = 'AVAILABLE'
  UNION
  select tablespace_name, 9999,
  'alter tablespace '||tablespace_name||' end backup;'
  from sys.dba_tablespaces
  where status = 'ONLINE'
  UNION
  select chr(255) tablespace_name, 9998 seqn,
  'alter database backup controlfile to  ||
  '${HOT}/controlfile_${ORACLE_SID}.HOT.full.after reuse;'
  from dual
  UNION
  select chr(255) tablespace_name, 9999 seqn,
  'alter system switch logfile;'
  from dual
  ORDER
  BY 1, 2
  spool ora_HOT_backup.sql
  /
  spool off
  start ora_HOT_backup.sql
  exit
EOF

# Find the count of 'alter tablespace .* begin backup' and
# 'alter tablespace .* end backup' in your alert logs
# if both are equal this signifies the previous hot backup is successful
# Else mail the concerned person associated with the backup's.
#
Bgn_Bkp=egrep -ci '^alter tablespace .* begin backup' $LOG
End_Bkp=egrep -ci '^alter tablespace .* end backup' $LOG
if [ $Bgn_Bkp != expr $End_Bkp + $Diff_Bkp ] ; then
  mail <email_address> <<-EOF
    ERROR : HOT BACKUP For "${ORACLE_SID}" Has Failed
    Number of "Begin Backup" statenents does not equal "End Backup"
    Please Investigate Immediately !
  EOF
  exit 1
fi