Script for logical backup and compress files using expdp

export ORACLE_SID=vygrdevd
export ORACLE_HOME=/home/oracle/app/oracle/product/11.1.0/db_1
echo $ORACLE_HOME
echo $ORACLE_SID
echo ' BACKING UP VYGRDEVD DB FULL LEVEL'
export dumpfilepath=vygrdevd_expdp_full_`date +%d%m%y%H%M`.dmp
export logfilepath=vygrdevd_expdp_full_`date +%d%m%y%H%M`.log
echo 'Creating the dump files...'
echo 'Dump file = $dumpfilepath'
echo 'Log file = $logfilepath'
expdp system/oracle@vygrdevd dumpfile=$dumpfilepath logfile=$logfilepath full=y directory=daily_backup "EXCLUDE=SCHEMA:\"='SYSMAN'\"" parallel=3
echo 'Compressing to save space...'
zip /data/data_dump/daily_backup/$dumpfilepath.gz /data/data_dump/daily_backup/$dumpfilepath
zip /data/data_dump/daily_backup/$logfilepath.gz /data/data_dump/daily_backup/$logfilepath
rm -f /data/data_dump/daily_backup/$dumpfilepath
rm -f /data/data_dump/daily_backup/$logfilepath
echo 'BELOW ARE THE BACKUP FILES CHECK WITH REFRENCE TO DATE'
cd /data/data_dump/daily_backup
ls -l
pwd

temp tablespace usage check scripts

set pagesize 1000
set linesize 9999
col file_name for a60
col tablespace_name for a20
select file_name,bytes/1024/1024/1024 gb,AUTOEXTENSIBLE,INCREMENT_BY INC,MAXBYTES/1024/1024/1024 MAX_GB_CAN_EXTEND from dba_temp_files where TABLESPACE_NAME='&TBS_NAME'

---To check for held TEMP segments:
set pagesize 1000
set lines 9999
col TABLESPACE for a20
col USERNAME for a20
col OSUSER for a20
select
   srt.tablespace,
   srt.segfile#,
   srt.segblk#,
   srt.blocks,
   a.sid,
   a.serial#,
   a.username,
   a.osuser,
   a.status
from
   v$session    a,
   v$sort_usage srt
where
   a.saddr = srt.session_addr
order by
   srt.tablespace, srt.segfile#, srt.segblk#,
   srt.blocks;
---select name,sum(bytes)/1024/1024/1024 gb from v$tempfile group by name;

---SELECT tablespace_name, bytes_used/1024/1024 used_mb, bytes_free/1024/1024 free_mb FROM v$temp_space_header;

---select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 GB,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 GB from dba_temp_files;

---select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS,MAX_BLOCKS from v$sort_segment;

----SELECT SUM (u.blocks * blk.block_size) / 1024 / 1024 "Mb. in sort segments"
, (hwm.MAX * blk.block_size) / 1024 / 1024 "Mb. High Water Mark"
FROM v$sort_usage u
, (SELECT block_size
FROM DBA_TABLESPACES
WHERE CONTENTS = 'TEMPORARY') blk
, (SELECT segblk# + blocks MAX
FROM v$sort_usage
WHERE segblk# = (SELECT MAX (segblk#)
FROM v$sort_usage)) hwm
GROUP BY hwm.MAX * blk.block_size / 1024 / 1024;

---------------Temp tablespace unable to extend
sqlplus '/as sysdba'
set linesize 9999
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024/1024, 0) "Size (Gb)",
(NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024/1024) "Used (Gb)",
TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select
tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name like '&TABLESPACE%' and d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)", NVL(t.bytes, 0)/1024/1024 "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files
group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like
'TEMPORARY'

select extent_size,current_users,total_extents,used_extents,free_extents from v$sort_segment where tablespace_name='&temp_tbs_name';
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

set pagesize 1000
set lines 9999
col SQL_TEXT for a70
col USERNAME for a15
col OSUSER for a15
col TABLESPACE for a15
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks

set lines 9999
col USERNAME for a20
col TABLESPACE for a20
SELECT s.username, s.sid,  u.TABLESPACE, u.CONTENTS, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr
==========================================

ALTER TABLESPACE TEMP_TS_dbname
   ADD TEMPFILE '/oracle/temp1/dbname/temp_ts_dbname_01.dbf' SIZE 5g REUSE;
ALTER TABLESPACE TEMP_TS_dbname TEMPFILE '/oracle/temp1/dbname/temp_ts_dbname_01.dbf' AUTOEXTEND ON NEXT 5g MAXSIZE 5g;

ALTER DATABASE TEMPFILE '/oracle/temp1/dbname/temp_ts_dbname.dbf' RESIZE 35g;
ALTER DATABASE TEMPFILE '/oracle/temp1/dbname/temp_ts_dbname.dbf' autoextend on next 35g maxsiz2 35g;
ALTER DATABASE TEMPFILE '/oracle/temp1/dbname/dbname_temp_01.dbf' RESIZE 27g;
ALTER DATABASE TEMPFILE '/oracle/temp1/dbname/dbname_temp_01.dbf' autoextend on next 27g maxsize 27g;

Restore & Recovery using rman

##################################
#####
##### Restore & Recovery Template
#####
##################################
#####
##### restore & recovery of an ORACLE database with RMAN from a SAT Backup: regular
#####
#####
#
#
##### restore & recovery for database with DBID
#set DBID=1234;
#
#
#####
##### restore spfile from database backupset
#####
##run {
##
##   startup nomount force;
##
##   allocate channel RESTORE_SPFILE type sbt_tape parms 'ENV=(TDPO_OPTFILE=/sysmgmt/sat/backup/etc/tsm/tdpo_servername.dbname.dbname_IMAGE.opt)' format 'DB-%d_BUSet-%uPieceNo-%p_CopyNo-%c';
##   restore spfile from tag='R_201210110621_ALL_ONLI_T';
##   release channel RESTORE_SPFILE;
##   shutdown abort;
##
##}
#
#
##### set database incarnation for restores
#reset database to incarnation 213545;
#
#
#####
##### restore controlfile from controlfile backupset
#####
##run {
##
##    startup nomount force;
##
##   allocate channel RESTORE_CONTROL type sbt_tape parms 'ENV=(TDPO_OPTFILE=/sysmgmt/sat/backup/etc/tsm/tdpo_servername.dbname.dbname_IMAGE.opt)' format 'DB-%d_BUSet-%uPieceNo-%p_CopyNo-%c';
##   restore controlfile from tag='R_201210110621_ALL_ONLI_C';
##   release channel RESTORE_CONTROL;
##   alter database mount;
##   shutdown abort;
##
##}
#####
##### restore and recover tablespaces from database backupset and archivelog backupsets
#####
#run {
#
#    startup mount force;
#
##### if an incomplete recovery has to be done ...
##    set until time ="to_date('2008-12-32 12:00:00','yyyy-mm-dd hh24:mi:ss')";
##    set until scn ;
##    set until sequence thread ;
###### channel allocation for the restore of the datafiles
#    allocate channel RESTORE_DATA_1 type sbt_tape parms 'ENV=(TDPO_OPTFILE=/sysmgmt/sat/backup/etc/tsm/tdpo_servername.dbname.dbname_IMAGE.opt)' format 'DB-%d_BUSet-%uPieceNo-%p_CopyNo-%c';
#    allocate channel RESTORE_DATA_2 type sbt_tape parms 'ENV=(TDPO_OPTFILE=/sysmgmt/sat/backup/etc/tsm/tdpo_servername.dbname.dbname_IMAGE.opt)' format 'DB-%d_BUSet-%uPieceNo-%p_CopyNo-%c';
#
##### channel allocation for the restore of the archivelogs
#    allocate channel RESTORE_LOGS_1 type sbt_tape parms 'ENV=(TDPO_OPTFILE=/sysmgmt/sat/backup/etc/tsm/tdpo_servername.dbname.dbname_ARCHIVELOG_PRIMARY.opt)' format 'DB-%d_BUSet-%uPieceNo-%p_CopyNo-%c';
#    allocate channel RESTORE_LOGS_2 type sbt_tape parms 'ENV=(TDPO_OPTFILE=/sysmgmt/sat/backup/etc/tsm/tdpo_servername.dbname.dbname_ARCHIVELOG_SECONDARY.opt)' format 'DB-%d_BUSet-%uPieceNo-%p_CopyNo-%c';
#
#
##### validation if the restore and recovery could be done
#    restore database validate;
#
##### restore the datafiles
#    restore database;
#
#
##### recover the database
#    recover database;
##### optionally: delete logs restored for recovery to limit used disk space
##    recover database delete archivelog maxsize 128m;
#
#
##### release archivelog restore channel(s)
#    release channel RESTORE_LOGS_2;
#    release channel RESTORE_LOGS_1;
#
##### release datafile restore channel(s)
#
#}
#
#
##### open the database if a complete recovery was done ...
##alter database open;
#
##### open the database if an incomplete recovery was done ...
##alter database open resetlogs;
#
#####
#####
##### Don't forget to check the temporary tablespaces and their tempfiles !!!
#####
##### Also check & restore files which were not part of RMAN backups (password file, listener.ora, ...)

Tbs_usage_metrics_check

select TABLESPACE_NAME,USED_SPACE/1024/1024/1024 USED_SPACE_GB,TABLESPACE_SIZE/1024/1024/1024 TABLESPACE_SIZE_GB,USED_PERCENT from dba_tablespace_usage_metrics
where TABLESPACE_NAME='&TBS_NAME';

shrink.sql

REM -----------------------------------------------------------------------------------------------
REM Name                : shrinksegs.sql
REM Compatible versions : 10.x and above
REM Description         : This script will initiate segment shrink for the TOP-5 Tables in reverse
REM                       order.
REM Note                : For some 10.x versions, If the UNDO Tablespace is not present in view
REM                       dba_tablespace_usage_metrics, it will calculate on dba_data_files and
REM                       dba_free_space
REM -----------------------------------------------------------------------------------------------
REM
------------------
-- Set SQL prompt:
------------------
whenever sqlerror exit;
set pagesize 60 lines 1000 serveroutput on feedback off echo off verify off
execute dbms_output.enable(1000000);
------------------------
-- Start segment shrink:
------------------------
DECLARE
  v_starttime   number := dbms_utility.get_time;
  v_owner       dba_segments.owner%TYPE := 'PERFSTAT';
  v_usercnt     NUMBER;
  v_maxundo     NUMBER := Nvl(Trim(&&maxundo),90);
  v_undo_pct    NUMBER;
  v_objcnt      NUMBER;
  v_tbscnt      NUMBER;
  v_noundo      EXCEPTION;
  v_nouser      EXCEPTION;
  v_invalidpct  EXCEPTION;
BEGIN
  SELECT Count(* )
  INTO   v_usercnt
  FROM   dba_users
  WHERE  username = v_owner;
  IF (v_usercnt = 0) THEN
    RAISE v_nouser;
  END IF;
  IF (v_maxundo < 0
       OR v_maxundo > 100) THEN
    RAISE v_invalidpct;
  END IF;
  SELECT Count(*)
  INTO   v_tbscnt
  FROM   dba_tablespace_usage_metrics
  WHERE  tablespace_name = (SELECT VALUE
                            FROM   v$parameter
                            WHERE  NAME = 'undo_tablespace');
  dbms_output.Put_line(Chr(10));
  FOR i IN (SELECT   owner,
                     segment_name
            FROM     (SELECT   owner,
                               segment_name,
                               Sum(bytes) tabsize
                      FROM     dba_segments
                      WHERE    owner = v_owner
                               AND segment_type = 'TABLE'
                      GROUP BY owner,
                               segment_name,
                               segment_type
                      ORDER BY Sum(bytes) DESC)
            WHERE    ROWNUM <= 5
            ORDER BY tabsize)
  LOOP
    IF (v_tbscnt <> 0) THEN
      SELECT Nvl(Round(used_percent),0)
      INTO   v_undo_pct
      FROM   dba_tablespace_usage_metrics
      WHERE  tablespace_name = (SELECT VALUE
                                FROM   v$parameter
                                WHERE  NAME = 'undo_tablespace');
    ELSE
      SELECT Decode(Nvl(a.maxbytes,0),0,0,Round(((Nvl(a.bytes,0) - Nvl(b.bytes,0)) / Nvl(a.maxbytes,0)) * 100,2)) pct_on_max
      INTO   v_undo_pct
      FROM   (SELECT   tablespace_name   tbs,
                       Nvl(Sum(bytes),0) bytes,
                       Nvl(Sum(CASE
                                 WHEN (Decode(maxbytes,0,bytes,maxbytes) >= bytes)
                                 THEN Decode(maxbytes,0,bytes,maxbytes)
                                 ELSE bytes
                               END),0) maxbytes
              FROM     dba_data_files
              GROUP BY tablespace_name) a,
             (SELECT   tablespace_name   tbs,
                       Nvl(Sum(bytes),0) bytes
              FROM     dba_free_space
              GROUP BY tablespace_name) b
      WHERE  a.tbs = b.tbs (+)
             AND a.tbs = (SELECT UPPER(VALUE)
                          FROM   v$parameter
                          WHERE  NAME = 'undo_tablespace');
    END IF;
    IF (v_undo_pct < v_maxundo) THEN
      dbms_output.Put_line('INFO : Current UNDO usage : '
                           ||v_undo_pct
                           ||'%');
      EXECUTE IMMEDIATE 'alter table '
                        ||i.owner
                        ||'.'
                        ||i.segment_name
                        ||' enable row movement';
      EXECUTE IMMEDIATE 'alter table '
                        ||i.owner
                        ||'.'
                        ||i.segment_name
                        ||' shrink space cascade';
      EXECUTE IMMEDIATE 'alter table '
                        ||i.owner
                        ||'.'
                        ||i.segment_name
                        ||' disable row movement';
      dbms_output.Put_line(Chr(10)
                           ||'INFO : Table '
                           ||i.owner
                           ||'.'
                           ||i.segment_name
                           ||' shrinked successfully'
                           ||Chr(10));
    ELSE
      RAISE v_noundo;
    END IF;
  END LOOP;
  dbms_output.Put_line(Chr(10));
  dbms_utility.Compile_schema(SCHEMA => v_owner,compile_all => false);
  dbms_output.Put_line(rpad('-',length('INFO : Compilation successfully completed for '||v_owner),'-'));
  dbms_output.Put_line('INFO : Compilation successfully completed for '||v_owner);
  SELECT Count(object_name)
  INTO   v_objcnt
  FROM   dba_objects
  WHERE  owner = v_owner
         AND status <> 'VALID';
  dbms_output.Put_line('INFO : Invalid object count in '
                       ||v_owner
                       ||' schema : '
                       ||v_objcnt);
  dbms_output.Put_line(rpad('-',length('INFO : Compilation successfully completed for '||v_owner),'-'));
  dbms_output.Put_line(Chr(10));
  dbms_output.put_line('INFO : Execution Time : '||round((dbms_utility.get_time-v_starttime)/100,2)||' seconds');
  dbms_output.Put_line(Chr(10));
EXCEPTION
  WHEN v_nouser THEN
    Raise_application_error(-20002,'ERROR! : Invalid user');
  WHEN v_invalidpct THEN
    Raise_application_error(-20003,'ERROR! : Percentage should be in between 0 - 100');
  WHEN v_noundo THEN
    Raise_application_error(-20004,'WARNING! : default UNDO Tablespace usage is '
                                   ||v_undo_pct
                                   ||'% Please try later..');
  WHEN OTHERS THEN
    Raise_application_error(-20050,'Error while executing PL/SQL block !'
                                   ||SQLCODE
                                   ||'-Error-'
                                   ||sqlerrm);
END;
/

migration_file_paths.sql

-- destination for datafile,control file, redolog file,temp file, dump file, dump locations
-- parameter file,
--
select distinct file_path
from
(select distinct substr(name,0,instr(name,'/',-1,1)) file_path from  v$datafile
 union all
 select distinct substr(name,0,instr(name,'/',-1,1)) file_path from  v$controlfile
 union all
 select distinct substr(member,0,instr(member,'/',-1,1)) file_path from  v$logfile
 union all
 select distinct substr(name,0,instr(name,'/',-1,1)) file_path from  v$tempfile
 union all
 select distinct substr(value,0,instr(value,'/',-1,1)) file_path from  v$parameter where name like '%dump%'
 union all
 select destination from v$archive_dest where destination is not null
 union all
 select decode(value,null,(select substr(file_spec,0,instr(file_spec,'/lib/libqsmashr.so' ))||'dbs/'
         from dba_libraries
         where library_name = 'DBMS_SUMADV_LIB'
       ),substr(value,0,instr(value,'/',-1,1))) file_path
 from sys.v_$parameter
 where name = 'spfile')
/
-- list listener log
--
lsnrctl status LISTENER_CBFCPPPM|grep -i file

Tbs_usage_check.sql

set linesize 9999
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024/1024, 0) "Size (Gb)",
(NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024/1024) "Used (Gb)",
TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select
tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name like '&TABLESPACE%' and d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)", NVL(t.bytes, 0)/1024/1024 "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files
group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like
'TEMPORARY'