RMAN

On the RMAN tool, delete archivelog older than a week.
# rman target /
;
RMAN> delete archivelog until time 'sysdate-7';

or
RMAN> delete force archivelog all;

or
RMAN> delete backup completed before 'sysdate-3';

Restore status

restore status
set pages 9999 lines 500
set numformat 99999.99
set trim on 
set trims on
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
   sysdate + TIME_REMAINING/3600/24 end_at
    from v$session_longops
    where totalwork > sofar
    AND opname NOT LIKE '%aggregate%'

    AND opname like 'RMAN%'




RMAN backup job details for 'n' number of days:-
=========================================
Monitoring RMAN backup status using v$rman_backup_job_details and v$rman_status.

Note : - Enter the number of days required for status report, for 1 day backup status report provide input as '1'.

 RMAN backup status using v$rman_backup_job_details :-
set pages 9999 lines 500
col INSTANCE for a9
col ELAPSED for a30
SELECT (  SELECT   instance_name FROM v$instance)
              || ' '
              || (  SELECT   instance_number FROM v$instance)
                 instance,
            --  TO_CHAR (start_time, 'YYYY-MM-DD HH24:MI') start_time,
       to_date (start_time, 'DD-MM-YYYY HH24:MI:SS') start_time,
              TO_CHAR (output_bytes / 1048576, '999,999,999.9') output_mb,
              TO_CHAR (output_bytes_per_sec / 1048576, '999,999.9') output_mb_per_sec,
              time_taken_display elapsed,input_type,status
         FROM v$rman_backup_job_details
         where start_time >= sysdate - &NUMBER_OF_DAYS
         ORDER BY start_time
         /
RMAN backup status using v$rman_backup_job_details , v$rman_status:-
set pages 9999 lines 500
set numformat 99999.99
set trim on 
set trims on
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
col INSTANCE for a9
col status for a22
col COMMAND_ID for a20
col INPUT_TYPE for a10
col OUTPUT_DEVICE_TYPE for a10
col OUTPUT_BYTES_PER_SEC_DISPLAY for a9
col status heading "BACKUP|STATUS" 
col COMMAND_ID heading "BACKUP NAME" 
col STARTED_TIME heading "START TIME" 
COL END_TIME heading "END TIME" 
col ELAPSED_TIME heading "MINUTES | TAKEN" 
col INPUT_TYPE heading "INPUT|TYPE" 
col OUTPUT_DEVICE_TYPE heading "OUTPUT|DEVICES" 
col INPUT_SIZE heading  "INPUT SIZE|GB"
col OUTPUT_SIZE heading  "OUTPUT SIZE|GB" 
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "OUTPUT | RATE|(PER SEC)"
SELECT (SELECT instance_name FROM v$instance) || ' ' || (SELECT instance_number FROM v$instance) instance,rs.sid,
rj.COMMAND_ID,
rj.STATUS,
max(rj.START_TIME) STARTED_TIME, 
rj.END_TIME,
rj.ELAPSED_SECONDS/60 ELAPSED_TIME,
rj.INPUT_TYPE,
rj.OUTPUT_DEVICE_TYPE,
rj.INPUT_BYTES/1024/1024/1024 INPUT_SIZE, 
rj.OUTPUT_BYTES/1024/1024/1024 OUTPUT_SIZE,
rj.OUTPUT_BYTES_PER_SEC_DISPLAY
from v$rman_backup_job_details rj, v$rman_status rs
where rj.COMMAND_ID=rs.COMMAND_ID
group by rs.sid,rj.COMMAND_ID,rj.STATUS,rj.START_TIME,rj.END_TIME,rj.ELAPSED_SECONDS,rj.INPUT_TYPE,rj.OUTPUT_DEVICE_TYPE,rj.INPUT_BYTES,rj.OUTPUT_BYTES,rj.OUTPUT_BYTES_PER_SEC_DISPLAY 
having max(rj.START_TIME) > sysdate-&NUMBER_OF_DAYS order by rj.START_TIME desc
/

                                  BACKUP                                                          MINUTES  INPUT      OUTPUT     INPUT SIZE OUTPUT SIZE OUTPUT RATE
INSTANCE  SID BACKUP NAME          STATUS                 START TIME          END TIME                TAKEN TYPE       DEVICES            GB          GB (PER SEC)
--------- --- -------------------- ---------------------- ------------------- ------------------- --------- ---------- ---------- ---------- ----------- ------------

Check RMAN Backup Status

SQL to report on all backups i.e., Full, Incremental and Archive log backups:
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
order by session_key;

SQL to report on all Full and Incremental backup but not Archive log backups:

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;

To check progress of RMAN Backup:
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';

Backup report no catalog database

set lines 130 pages 1000
set feedback off
col INPUT_TYPE for a11
col START_TIME for a20
col END_TIME for a20
col STATUS format a24
col DB_NAME for a10
col hrs format 999.99
select  (select name "DB_NAME" from v$database) DB_NAME,
        SESSION_KEY,
        INPUT_TYPE,
        STATUS,
        to_char(START_TIME,'DD/MON/YYYY hh24:mi') START_TIME,
        to_char(END_TIME,'DD/MON/YYYY hh24:mi')   END_TIME,
        elapsed_seconds/3600                      HRS
from (  select
        START_TIME,
        END_TIME,
        ELAPSED_SECONDS,
        STATUS,
        INPUT_TYPE,
        SESSION_KEY,
        rank()
                over (PARTITION BY INPUT_TYPE ORDER BY SESSION_KEY desc)
                      rankno
        from  V$RMAN_BACKUP_JOB_DETAILS
where input_type in ('DB INCR','DB FULL')
--            where input_type in ('DB INCR','DB FULL','ARCHIVELOG')
     order by SESSION_KEY desc)
WHERE   rankno < 2;

[oracle@raj ~]$ cat /u01/backup_SCRIPT.sh
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_2
export $ORACLE_SID=DEMO
$ORACLE_HOME/bin/rman target / nocatalog  << EOF
run
{
allocate channel mwares_backup_disk1 type disk format '/u01/backup_06092017/';
backup AS COMPRESSED BACKUPSET tag '%TAG' incremental level 0 cumulative database;
backup as COMPRESSED BACKUPSET tag '%TAG' archivelog all not backed up;
backup current controlfile;
release channel backup_disk1;
}
[oracle@raj ~]$ cat  /mnt/nfs/tmp/RESTORE_SCRIPT.sh
ORACLE_SID=demo
ORAENV_ASK=NO
. oraenv
RESTORE_LOG=/u01/logs/$ORACLE_SID.log

 rman AUXILIARY / <<! | tee -a $RESTORE_LOG

 run {
ALLOCATE AUXILIARY CHANNEL AUX1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL AUX2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL AUX3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL AUX4 DEVICE TYPE DISK;
DUPLICATE DATABASE TO demo
  BACKUP LOCATION '/mnt/BKP_05092017'
  NOFILENAMECHECK;
}
exit;

!

 col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col db_name for 9999 heading "DB"
select
  j.db_name DB,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  j.elapsed_seconds, j.time_taken_display
from RC_RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                 --decode(d.backup_type,'D','Level 0','I','Level 1','L','Archive Log','Other')Backyp_Type,
                    sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO' and
                     d.backup_type = 'D' then d.pieces else 0 end) FB,
                     sum(case when d.backup_type = 'D' then d.incremental_level else 0 end)I0,
                     sum(case when d.backup_type = 'I' then d.incremental_level else 0 end)I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     RC_BACKUP_SET_DETAILS d
                     join RC_BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO' 
                    group by d.session_recid, d.session_stamp ) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.recid, o.stamp
                   from RC_RMAN_OUTPUT o
                  group by o.recid, o.stamp )
    ro on ro.recid = j.SESSION_RECID  and ro.stamp = j.session_stamp
where j.start_time > trunc(sysdate)-1
order by j.db_name, j.start_time;

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Blocking session on RAC database

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...
Read more

Finding object size in Oracle database

Ref: http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153 Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)  SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name  FROM DBA_SEGMENTS  WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',  'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY')  --AND TABLESPACE_NAME LIKE 'COSTE%'  --AND SEGMENT_NAME LIKE 'P2010201%'  --AND partition_name LIKE 'P20100201%' --AND segment_type = 'TABLE' --AND OWNER = 'TARG...
Read more