How to check RMAN backup job status in Oracle using v$rman_backup_job_details

 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)

--------- --- -------------------- ---------------------- ------------------- ------------------- --------- ---------- ---------- ---------- ----------- ------------

To get the job details for a specific backup job,  use the following query:-

set lines 220
set pages 1000
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 output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  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,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     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.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$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.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;

Where,

CF: Number of controlfile backups included in the backup set

DF: Number of datafile full backups included in the backup set

I0: Number of datafile incremental level-0 backups included in the backup set

I1: Number of datafile incremental level-1 backups included in the backup set

 L: Number of archived log backups included in the backup set

Backup set details : -

To get the Backup set details for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, use the following query:
 
set lines 220
set pages 1000
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
  d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
  to_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,
  d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
from V$BACKUP_SET_DETAILS d
  join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where session_recid = &SESSION_RECID
  and session_stamp = &SESSION_STAMP
order by d.start_time;

Backup job output :-

To get the Backup job output for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, use the following query:
   
set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by recid;

----------

Ref->RMAN

------------------

Find the last RMAN backup’s log?

 Find the last RMAN backup’s log?

Execute the script to check RMAN log

set pagesize 2000

set linesize 2000

select

output

from

GV$RMAN_OUTPUT

where

session_recid =

(

select

session_recid

from

V$RMAN_BACKUP_JOB_DETAILS

where

session_key=(select max(session_key) from v$RMAN_BACKUP_JOB_DETAILS)

);

Output

connected to target database: WYPTCDB (DBID=2161084537)

using target database control file instead of recovery catalog

run {

report obsolete recovery window of 30 days;

delete noprompt obsolete recovery window of 30 days;

no obsolete backups found

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=927 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1382 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=1611 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=15 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=468 device type=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: SID=929 device type=DISK

allocated channel: ORA_DISK_7

channel ORA_DISK_7: SID=1384 device type=DISK

allocated channel: ORA_DISK_8

channel ORA_DISK_8: SID=1610 device type=DISK

no obsolete backups found

Starting backup at 07-AUG-2021 15:15:44

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

using channel ORA_DISK_7

using channel ORA_DISK_8

channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

Finished Control File and SPFILE Autobackup at 07-AUG-2021 15:22:30

released channel: ORA_DISK_1

released channel: ORA_DISK_2

released channel: ORA_DISK_3

released channel: ORA_DISK_4

released channel: ORA_DISK_5

released channel: ORA_DISK_6

released channel: ORA_DISK_7

released channel: ORA_DISK_8

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=927 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1382 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=1611 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=15 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=468 device type=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: SID=929 device type=DISK

allocated channel: ORA_DISK_7

channel ORA_DISK_7: SID=1384 device type=DISK

allocated channel: ORA_DISK_8

channel ORA_DISK_8: SID=1610 device type=DISK

exit;

How to restore archive logs to an alternative location when they already reside on disk (Doc ID 399894.1)

SYMPTOMS

When attempting to restore archive logs that are already located on disk using RMAN the following message will be raised:

restore not done; all files readonly, offline, or already restored

CAUSE

The message above is correct however if the archivelogs are stored within ASM you are not able to use the os copy or move command to retrieve them.

RMAN> run {
2> set archivelog destination to '/tmp';
3> restore archivelog from logseq=60 until logseq=65;
4> }

executing command: SET ARCHIVELOG DESTINATION
using target database controlfile instead of recovery catalog

Starting restore at 2006/11/17 08:51:01
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK

archive log thread 1 sequence 60 is already on disk as file +DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_60.267.606732487
archive log thread 1 sequence 61 is already on disk as file +DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_61.263.606732499
archive log thread 1 sequence 62 is already on disk as file +DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_62.265.606732505
archive log thread 1 sequence 63 is already on disk as file +DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_63.278.606732509
archive log thread 1 sequence 64 is already on disk as file +DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_64.277.606732509
archive log thread 1 sequence 65 is already on disk as file +DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_65.276.606732521
restore not done; all files readonly, offline, or already restored
Finished restore at 2006/11/17 08:51:02

SOLUTION

There are three potential solutions:

1) Use the RMAN copy command

RMAN> copy archivelog '+DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_60.267.606732487' to '/tmp/archive_seq_60.arc';

If there are only a few archivelogs required then the copy command can be repeated for each log. However if there are multiple archive logs below solutions may be preferable.

2) Try to use the FORCE clause in the restore command to restore the archivelog to an alternate location even when the archvie already exists on disk:

RMAN> run {
2> set archivelog destination to '/tmp';
3> restore FORCE archivelog from logseq=60 until logseq=65;
4> }

3)  Use the RMAN uncatalog command.

RMAN> change archivelog from logseq=60 until logseq=65 uncatalog;

uncataloged archive log
archive log filename=+DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_60.267.606732487 recid=64 stamp=606732490
uncataloged archive log
archive log filename=+DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_61.263.606732499 recid=65 stamp=606732500
uncataloged archive log
archive log filename=+DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_62.265.606732505 recid=66 stamp=606732507
uncataloged archive log
archive log filename=+DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_63.278.606732509 recid=67 stamp=606732508
uncataloged archive log
archive log filename=+DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_64.277.606732509 recid=68 stamp=606732511
uncataloged archive log
archive log filename=+DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_65.276.606732521 recid=69 stamp=606732523
Uncataloged 6 objects

Once the archivelogs have been successfully uncataloged you are able to successfully restore the archive logs to new location.

RMAN> run {
2> set archivelog destination to '/tmp';
3> restore archivelog from logseq=60 until logseq=65;
4> }

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 2006/11/17 08:57:06
using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/tmp
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=60
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=61
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=62
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=63
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=64
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=65
channel ORA_DISK_1: restored backup piece 1
piece handle=+DGROUP2/rman/backupset/2006_11_17/annnf0_tag20061117t084948_0.275.606732589 tag=TAG20061117T084948
channel ORA_DISK_1: restore complete
Finished restore at 2006/11/17 08:57:08

 Following the successul restore of the archivelogs you can then continue to recatalog the archivelogs back into the ASM diskgroup. Below is an example of cataloging one archivelog.

RMAN> catalog archivelog '+DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_60.267.606732487';

cataloged archive log
archive log filename=+DGROUP2/rman/archivelog/2006_11_17/thread_1_seq_60.267.606732487 recid=76 stamp=606733388

Measure Throughput of RMAN

 I’m using 16 channels.  It seems to move along.   Every once in a while one of the channels finishes with one of the pieces.   Is there a way to measure how fast it is going?

The following query will serve this purpose:

set linesize 126

column Pct_Complete format 99.99

column client_info format a25

column sid format 999

column MB_PER_S format 999.99

select s.client_info,

l.sid,

l.serial#,

l.sofar,

l.totalwork,

round (l.sofar / l.totalwork*100,2) "Pct_Complete",

aio.MB_PER_S,

aio.LONG_WAIT_PCT

from v$session_longops l,

v$session s,

(select sid,

serial,

100* sum (long_waits) / sum (io_count) as "LONG_WAIT_PCT",

sum (effective_bytes_per_second)/1024/1024 as "MB_PER_S"

from v$backup_async_io

group by sid, serial) aio

where aio.sid = s.sid

and aio.serial = s.serial#

and l.opname like 'RMAN%'

and l.opname not like '%aggregate%'

and l.totalwork != 0

and l.sofar <> l.totalwork

and s.sid = l.sid

and s.serial# = l.serial#

order by 1;

CLIENT_INFO                SID    SERIAL#      SOFAR  TOTALWORK Pct_Complete MB_PER_S LONG_WAIT_PCT

------------------------- ---- ---------- ---------- ---------- ------------ -------- -------------

rman channel=ORA_DISK_11   150      22676     505343    1310720        38.55    112.23 7.17365149

rman channel=ORA_DISK_13   157      30929     297151    1310720        22.67    68.24       9.69663

rman channel=ORA_DISK_15   164      46354    1229631    1310720        93.81    59.56    9.87603869

rman channel=ORA_DISK_17   170      50562    1268543    1310720        96.78   118.82    9.07014682

rman channel=ORA_DISK_2    121       3182    1083775    1310720        82.69    72.77    9.04937857

rman channel=ORA_DISK_20   177       1125    1035135    1310720        78.97    64.06     9.5649286

rman channel=ORA_DISK_21   191      11483     883007    1310720        67.37    65.17    10.2629709

rman channel=ORA_DISK_23   198      43552     879807    1310720        67.12    58.13    10.7894355

rman channel=ORA_DISK_25   205      61706     478911    1310720        36.54   102.89    9.02772999

rman channel=ORA_DISK_27   212       6382     849279    1310720        64.79   113.14    9.22199866

rman channel=ORA_DISK_3    128      42605     399999    1310720        30.52    74.83    9.69133306

rman channel=ORA_DISK_31   226      62824     351679    1310720        26.83    68.14    9.70159909

rman channel=ORA_DISK_9    143       4726     388351    1310720        29.63   145.21 7.54851525

The fastest channel of any of them is pushing the blazing-fast speed of 145,21 MB per second.  

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

When we are taking archive log backup with delete then got an error.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=/u01/backup/acrhivelog/1_1001_9845328.dbf thread=1 sequence=13456

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

SOLUTION:

Cause: The archive logs seems that it is not yet applied in the standby database.

                        Select sequence# , applied from v$archived_log;

Action: Check the standby database and apply the logs manually and the start the deletion process.

              I can delete the archive logs by force using the below command.

      RMAN > delete noprompt force archivelog all;

But I don't want to delete the archives which are not applied in standby  so i'm changing the configuration

              

RMAN> show all;

Old configuration:

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

Change as Below:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

new RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

new RMAN configuration parameters are successfully stored

RMAN-08591: WARNING: invalid archived log deletion policy


Now check the configuration

RMAN> show all;

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

Now I am able to delete those archivelogs now.

RMAN> delete noprompt archivelog all;

RMAN> crosscheck archivelog all;

Once I deleted those logs, I changed the rman configuration by the default one.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY CLEAR;

RMAN> Show all;

RMAN>delete noprompt archivelog until time ‘SYSDATE-10’;  

RMAN COMMANDS

Useful RMAN commands

followed very good article by

http://rd-oracledba.blogspot.com

This article about various RMAN commands which are useful in our daily job.

RMAN will take database backup in two methods

 1.backup set and 2.image copy.

By default method is backup set.

Diff between backup set and image copy:

==============================

A backup set consists of one or more backup pieces, which are physical files written in a format that only RMAN can access.You can also take compressed backups using backup set.

Image copy is a bit-for-bit copy of a database file created on disk. Image copies are identical to copies created with operating system commands like cp on Linux or COPY on Windows, but are recorded in the RMAN repository and so are usable by RMAN.

Monitor RMAN Backups:

==================

set pages 2000 lines 200

COL STATUS FORMAT a9

col START_TIME for a20

col END_TIME for a20

COL hrs FORMAT 999.99

select 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,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

OUTPUT_DEVICE_TYPE

FROM V$RMAN_BACKUP_JOB_DETAILS

order by SESSION_KEY;

To check the progress of your current RMAN backup use this script:

 select recid , output_device_type, dbsize_mbytes, input_bytes/1024/1024/1024 input_gbytes
, output_bytes/1024/1024/1024 output_gbytes , (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/1024 dbsize_mbytes from v$datafile) 
where status='RUNNING' and output_device_type is not null

BACKUP SET examples :

===================

* To take full backup using tag:

RMAN> BACKUP AS BACKUPSET TAG 'FULL_DB' DATABASE PLUS ARCHIVELOG;

* To take compressed backups:

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

* To creates a level 1 differential incremental backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

* To creates a level 1 cumulative incremental backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

* To take archivelog backup:

rman> backup archivelog all;

rman> backup filesperset 10 archivelog all;

rman> backup archivelog all delete all input;  

rman> backup archivelog all not backed up 1 times;

rman> backup archivelog all not backed up 1 times tag 'arch_tape_backup';

rman> backup  as compressed backupset filesperset 1 archivelog until time 'sysdate - 2/24';

* To list backups:

rman> list backup summary;

rman> list backup;

rman> list backup of database:

rman> list backuppiece 'db_rameshxsx_vgdffj_01';

rman> list backupset 13202;

rman> list backup of datafile 10;

rman> list backupset of datafile 10;

* To list and delete archive files:

rman> list archivelog all;

rman> list backup of archivelog all;

rman> list archivelog from sequence 1145 until sequence 1250;

rman> list backup of archivelog until time 'sysdate-2';

rman> list backup of archivelog from sequence 78895 until sequence 92566;

rman> delete archivelog all;

rman> delete archivelog until time 'sysdate -2';

rman> delete noprompt archivelog until time 'sysdate -3/24';

rman> delete archivelog all completed before 'sysdate-5';

rman> delete noprompt archivelog all  backed up 1 times to device type sbt;

rman> delete noprompt archivelog until time 'sysdate-2' backed up 1 times to device type disk;

rman> delete noprompt archivelog until time 'sysdate - 2/24' backed up 1 times to device type disk;

Note: All delete all input means RMAN will delete archive logs from archive destinations once they are backed up.

Image copy examples:

===================

* To take full image backup with archivelogs

RMAN> BACKUP AS COPY TAG 'FULL_DB' DATABASE PLUS ARCHIVELOG; // with archive logs

RMAN> BACKUP AS COPY INCREMENTAL LEVEL 0 TAG  'FULL_DB'  DATABASE;

To take full image backup to specific location:

rman> backup as copy database tag 'full_db' format '/u02/backup/%b';

rman> backup as copy database tag 'full_db' format '/u01/backup/%f_%b';

(or)

allocate channel ch1 device type disk format '/u02/backup/%b';

backup as copy database tag 'full_db';

release channel ch1;

* To take incremental for image backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'FULL_DB' DATABASE;

* Rollforward full image backup with incremental:

RMAN> RECOVER COPY OF DATABASE WITH TAG 'FULL_DB';

or 

RMAN> RECOVER COPY OF DATABASE WITH TAG 'FULL_DB' UNTIL TIME 'SYSDATE-1';

*  Back Up Controlfile  as copy:

RMAN>BACKUP AS COPY CURRENT CONTROLFILE;

* Backup recovery area to tape:

RMAN> BACKUP RECOVERY AREA TAG 'FULL_DB';

* To take archivelog copy backup

RMAN> BACKUP AS COPY ARCHIVELOG;

* To delete archivelogs copy backup:

RMAN> DELETE COPY OF ARCHIVELOG;

* To list copy backups:

RMAN> LIST COPY OF BACKUP;

RMAN> LIST COPY OF ARCHIVELOG;

RMAN> LIST COPY OF ARCHIVELOG FROM SEQUENCE 10;

RMAN> List Copy of archivelog from sequence 10 until sequence 20;

RMAN> LIST COPY;

* Delete copy backups:

RMAN> delete copy of database tag='REFRESH_DB_8th_DEC';

RMAN> delete copy TAG='REFRESH_DB_8th_DEC';

Crosscheck and Expired:

=====================

Crosschecks update RMAN repository information about backups whose repository records do not match their physical status.

For example, if a user removes archived logs from disk with an os commands (ex: rm) , the repository still indicates that the logs are on disk, when in fact logs are not there .So by using crosscheck command we are checking physical existance of files and if files are not there then rman will mark these missing/deleted files as expired.

rman> crosscheck backup;

rman> crosscheck backup of database;

rman> crosscheck backupset;

rman> crosscheck copy;

rman> crosscheck archivelog all ;

rman> list expired;

rman> list expired archivelog all;

rman> list expired backup;

rman> list expired backup of archivelog all;

rman> delete expired backup;

rman> delete expired archivelog all;

rman> delete force noprompt expired copy tag='REFRESH_DB_8th_DEC';

Obsolete backups:

================

Backups will become obsolete based on retention policy.For example if retention policy is "recovery window 7 days" then rman will mark backups as an obsolete

after 7 days.

RMAN> REPORT OBSOLETE;

RMAN> DELETE OBSOLETE;

RMAN> DELETE FORCE OBSOLETE;

validate :

==========

You can use validate cmd to check database files and archived redo log files are physically and logically  corrupted.

RMAN> BACKUP VALIDATE;

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

RMAN> VALIDATE BACKUPSET 3;

RMAN> VALIDATE DATAFILE 5;

RMAN> VALIDATE DATAFILECOPY ALL;

Catalog files :

==============

Add information about file copies and user-managed backups to the RMAN repository.

CATALOG START WITH '/u02/backup/';

CATALOG START WITH '+FRA_DG';

CATALOG BACKUPPIECE '/u02/backup/annnf0_full1_0.276.895785483';

CATALOG DATAFILECOPY '/u02/backup/users01.dbf' LEVEL 0;

CATALOG ARCHIVELOG '/u02/backup/thread_1_seq_8.321.895785769', '/u02/backup/thread_2_seq_2.326.895781619';

Change :

======

Change command will update the availability status of backups and copies recorded in the RMAN repository.For example if you don't want any particular backup then we can mark them as unavailable.

CHANGE BACKUPSET 4 UNAVAILABLE;

CHANGE ARCHIVELOG ALL UNCATALOG; // for suppose if we moved archive files from one location to another location...then we can uncatlog those files by using change cmd.

Configuration settings :

=================

you can change rman configuration settings and below is one of the example to change the retention policy.

rman> show all;

rman> configure retention policy to redundancy 3;

rman> configure retention policy to recovery window of 7 days;

rman> configure retention policy clear;   // to set to default 

Backup controlfile to trace :

======================

RMAN> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

RMAN> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/U02/BACKUP/CONTROLFILE.CTL';

Control File and Server Parameter File Autobackups:

=======================================

If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current sp file at the end of a successful BACKUP command.

RMAN> show CONTROLFILE AUTOBACKUP;

RMAN configuration parameters for database with db_unique_name RAMESH1 are:

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

To run the rman script:

=======================

rman target / @rman_backup.cmd log=rman_backup.log

rman_backup.cmd

---------------

run

{ 

allocate channel ch1 device type disk FORMAT '/u02/backup/%b';

BACKUP AS COPY DATABASE TAG 'FULL_DB';

release channel ch1;

}

Trace a rman session:

===============

rman target / debug trace rman_ramesh1_crosscheck.trc log rman_ramesh1_crosscheck.log

RUN

{

ALLOCATE CHANNEL C1 DEVICE TYPE DISK;

ALLOCATE CHANNEL C2 DEVICE TYPE SBT;

ALLOCATE CHANNEL C3 DEVICE TYPE DISK;

ALLOCATE CHANNEL C4 DEVICE TYPE SBT;

CROSSCHECK COPY;

}

control_file_record_keep_time parameter value change:

========================================

select name, ISSYS_MODIFIABLE from v$parameter where name='control_file_record_keep_time';

alter system set control_file_record_keep_time=10 scope=both sid='*';