Rman Backup Status and Type of Backups

Below Sql generates the Size of backup and Time taken to Completed the backup . 


SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
  FROM
  ( SELECT  CASE
    WHEN s.backup_type='L' THEN 'ARCHIVELOG'
    WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
    WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
    WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
    END type, TRUNC(s.completion_time) completion_time, p.bytes, s.elapsed_seconds
    FROM v$backup_piece p, v$backup_set s
    WHERE p.status='A' AND p.recid=s.recid
    UNION ALL
    SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details)
GROUP BY TO_CHAR(completion_time, 'YYYY-MON-DD'), type
ORDER BY 1 ASC,2,3 ;



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
where input_type='DB INCR'
order by session_key;
 
 Extract the L1 and L0 Backup Details for the Database in HTML Format . 
 
set pages 100 lines 200 feedback off markup html on
alter session set nls_date_format='DD-MON-YYYY HH24:MI';
spool backup_details.html append
select host_name,instance_name from v$instance;
select
(select host_name from v$instance) AS "Host_NAME",
(Select name from v$database) as "DB_NAME",
start_time,end_time,elapsed_seconds/60/60 as "DURATION(HOURS)", INPUT_TYPE,
(r.status) as status,(b.incremental_level) as incremental_level
from v$RMAN_BACKUP_JOB_DETAILS r
inner join
(select distinct session_stamp,incremental_level from v$backup_set_details) b on
r.session_stamp = b.session_stamp where incremental_level is not null
and r.start_time > sysdate - 7
and INPUT_TYPE <>'ARCHIVELOG' order by 3;
spool off;
exit 
 
 
Backup Status and Type of backup 
 
col TYPE for a20 Heading "Backup Type"
col completion_time Heading "Completion Time"
col MB for 99999999999  Heading "Size (MB)"
col BCPTIME for 99999999999 Heading "Backup Time (minutes)"

SELECT TO_CHAR (completion_time, 'YYYY-MON-DD') completion_time
       , TYPE
       , ROUND (SUM (bytes) / 1048576)          MB
       , ROUND (SUM (elapsed_seconds) / 60)     BCPTIME
    FROM (SELECT CASE
                    WHEN s.backup_type = 'L' THEN 'Archive Log'
--                    WHEN s.controlfile_included = 'YES' THEN 'Control File'
                 WHEN s.backup_type = 'D' THEN 'Full (Level ' || NVL (s.incremental_level, 0) || ')'
                    WHEN s.backup_type = 'I' THEN 'Incemental (Level ' || s.incremental_level || ')'
                    ELSE s.backup_type
                 END
                    TYPE
               , TRUNC (s.completion_time) completion_time
               , p.tag
               , p.bytes
               , s.elapsed_seconds
            FROM v$backup_piece p, v$backup_set s
           WHERE status = 'A' AND p.recid = s.recid
          UNION ALL
          SELECT 'Datafile Copy' TYPE, TRUNC (completion_time), tag, output_bytes, 0 elapsed_seconds FROM v$backup_copy_details)
GROUP BY tag, TO_CHAR (completion_time, 'YYYY-MON-DD'), TYPE
ORDER BY 1 ASC, 2, 3; 
 
 

Comments

Post a Comment

Popular posts from this blog

Cold Backup Cloning of Database .

 Cold Backup :  A cold backup is done when there is no user activity going on with the system. Also called as offline backup, is taken when the database is not running and no users are logged in. all files of the database are copied and no changes during the copy are made. Cold backup can be preformed when Database is in archive mode as well as Non archive log mode  . Backup Strategy :  Gather all the details of physical files (CRD) files in  database . redo log files are online redo log and archived redo log ones .   Select file_name,tablespace_name from dba_data_files  ; select * from v$controlfile  select member from v$logfile ; select name from v$archived_Log ;  create pfile from spfile  Backup  the contol file manually . alter database backup controlfile to trace as '/tmp/coldbackup_controlfile.sql' ;     Stop all the DB services (Database ,Listener)on source DB Server. Copy all the files(Datafiles,redologfiles,pfile,contol file backup) from source to backup location 
Read more

Find ALert log Location

 The alert log is an XML file that is a the log of messages and errors. Each alert log is specific to its component type, such as database, Oracle ASM, listener, and Oracle Clusterware. For the database, the alert log includes messages about the following:     Critical errors (incidents)     Administrative operations, such as starting up or shutting down the database,         recovering the database, creating or dropping a tablespace, and others.     Errors during automatic refresh of a materialized view     Other database events      Trace files are updated periodically over the life of the process and can contain information on the process environment,  status, activities, and errors. In addition, when a process detects a critical error,  it writes information about the error to its trace file.      Using Find Command    find $ORACLE_BASE -type f -name alert_$ORACLE_SID.log    select value from GV$DIAG_INFO WHERE name='Diag Trace';    find /opt/oracle -name alert*log
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 be set to a Diskgroup whi
Read more