RMAN BACKUPS DETAILS CHECK

 

set lines 200 pages 200;

col END_TIME for a20;
col ELAPSED_TIME for a25;
SELECT T.SESSION_KEY,
       T.INPUT_TYPE,
       T.STATUS,
       T.SUM_BACKUP_PIECES_IN_GB,
       T.START_TIME,
       T.END_TIME,
       T.SECONDS ELAPSED_TIME
  FROM (   SELECT SESSION_KEY,
                 INPUT_TYPE,
                 STATUS,
                 OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,
                 TO_CHAR (START_TIME, 'dd/mm/yyyy hh24:mi') START_TIME,
                 TO_CHAR (END_TIME, 'dd/mm/yyyy hh24:mi') END_TIME,
                 TIME_TAKEN_DISPLAY SECONDS
            FROM V$RMAN_BACKUP_JOB_DETAILS
                        WHERE INPUT_TYPE='ARCHIVELOG'
        ORDER BY SESSION_KEY DESC) T
 WHERE ROWNUM <= 1;


set lines 200 pages 200;
col END_TIME for a20;
col ELAPSED_TIME for a25;
SELECT T.SESSION_KEY,
       T.INPUT_TYPE,
       T.STATUS,
       T.SUM_BACKUP_PIECES_IN_GB,
       T.START_TIME,
       T.END_TIME,
       T.SECONDS ELAPSED_TIME
  FROM (   SELECT SESSION_KEY,
                 INPUT_TYPE,
                 STATUS,
                 OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,
                 TO_CHAR (START_TIME, 'dd/mm/yyyy hh24:mi') START_TIME,
                 TO_CHAR (END_TIME, 'dd/mm/yyyy hh24:mi') END_TIME,
                 TIME_TAKEN_DISPLAY SECONDS
            FROM V$RMAN_BACKUP_JOB_DETAILS
                        WHERE INPUT_TYPE LIKE '%INC%'
        ORDER BY SESSION_KEY DESC) T
 WHERE ROWNUM <= 1;




=========percentage completion of query ================


col dbsize_mbytes      for 99,999,990.00 justify right head "DBSIZE_MB"
col input_mbytes       for 99,999,990.00 justify right head "READ_MB"
col output_mbytes      for 99,999,990.00 justify right head "WRITTEN_MB"
col output_device_type for a10           justify left head "DEVICE"
col complete           for 990.00        justify right head "COMPLETE %" 
col compression        for 990.00        justify right head "COMPRESS|% ORIG"
col est_complete       for a20           head "ESTIMATED COMPLETION"
col recid              for 9999999       head "ID"
select recid
     , output_device_type
     , dbsize_mbytes
     , input_bytes/1024/1024 input_mbytes
     , output_bytes/1024/1024 output_mbytes
     , (output_bytes/input_bytes*100) compression
     , (mbytes_processed/dbsize_mbytes*100) complete
     , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:mi:SS') est_complete
  from v$rman_status rs
     , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 
 where status like '%RUNNING%'
   and output_device_type is not null
/


================ ASM disk group free space ==============

set sqlblanklines on
set lines 180
set pages 999le
col "Group Name"   form a25
col "Disk Name"    form a30
col "State"  form a15
col "Type"   form a7
col "gbtotal_Gross"   	form 9999999999
col gbtotal_Free_Gross form 9999999999
col "Free GB"			form 9999999999
col FreePerc		form 999.99
select group_number  , name "Group Name", state , type , total_mb/1024 gbtotal_Gross,free_mb/1024 gbtotal_Free_Gross,
(total_mb-REQUIRED_MIRROR_FREE_MB)/decode(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024 "Total GB",
(USABLE_FILE_MB/1024) "Free GB",
(USABLE_FILE_MB/1024)/((total_mb-REQUIRED_MIRROR_FREE_MB)/decode(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024)*100 FreePerc
from   v$asm_diskgroup;









If the RMAN ARCHIVE log backup job fails. Run below command to restart the job
-----------------------------------------------------------------------------------------------
nohup /ibmdba/backup/job/RmanBackupArchive.ksh BKOFFICE &


nohup /ibmdba/backup/job/RmanBackupArchive.ksh BKMSAF &

BKLABQA


nohup /ibmdba/backup/job/RmanBackupArchive.ksh BKLABQA &


log location in the server /ibmdba/backup/log





set sqlblanklines on
set lines 180
set pages 999le
col "Group Name"   form a25
col "Disk Name"    form a30
col "State"  form a15
col "Type"   form a7
col "gbtotal_Gross"   	form 9999999999
col gbtotal_Free_Gross form 9999999999
col "Free GB"			form 9999999999
col FreePerc		form 999.99
select group_number  , name "Group Name", state , type , total_mb/1024 gbtotal_Gross,free_mb/1024 gbtotal_Free_Gross,
(total_mb-REQUIRED_MIRROR_FREE_MB)/decode(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024 "Total GB",
(USABLE_FILE_MB/1024) "Free GB",
(USABLE_FILE_MB/1024)/((total_mb-REQUIRED_MIRROR_FREE_MB)/decode(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024)*100 FreePerc
from   v$asm_diskgroup;



============== Archives backup and deleted per day wise ==================

set lines 200
col DAY format a10
SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_GB,
         SUM_ARCH_DEL.DELETED_GB,
         SUM_ARCH.GENERATED_GB - SUM_ARCH_DEL.DELETED_GB "REMAINING_GB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024 * 1024), 2))
                      GENERATED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024 * 1024), 2))
                      DELETED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');


set lines 200 pages 200

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;

select sid,serial#,opname, TOTALWORK,SOFAR,to_char(START_TIME,'dd-mm-yyyy hh24:mi:ss') START_TIME, TIME_REMAINING/60 TIME_REMAINING, ELAPSED_SECONDs/60 ELAPSED_SECONDs,
round(sofar/totalwork*100,2) "% Complete" FROM V$SESSION_LONGOPS WHERE opname LIKE 'RMAN:%' AND totalwork != 0 and time_remaining>0 order by start_time;


alter system set log_archive_dest_1='LOCATION=/oraarchives' scope=memory sid='*';


[oracle@bkbko0dbo001 alert]$ find . -name '*.xml' -mtime +5  -exec gzip {} \;

============== Archives backup and deleted per day wise ==================

set lines 200

col DAY format a10

SELECT SUM_ARCH.DAY,

 SUM_ARCH.GENERATED_GB,

 SUM_ARCH_DEL.DELETED_GB,

 SUM_ARCH.GENERATED_GB - SUM_ARCH_DEL.DELETED_GB "REMAINING_GB"

 FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,

 SUM (ROUND ( (blocks * block_size) / (1024 * 1024 * 1024), 2))

 GENERATED_GB

 FROM V$ARCHIVED_LOG

 WHERE ARCHIVED = 'YES'

 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,

 ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,

 SUM (ROUND ( (blocks * block_size) / (1024 * 1024 * 1024), 2))

 DELETED_GB

 FROM V$ARCHIVED_LOG

 WHERE ARCHIVED = 'YES' AND DELETED = 'YES'

 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL

 WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)

ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');