Difference between revisions of "RMAN"

From dbawiki
Jump to: navigation, search
(→‎Check whether the backup pieces registered in the controlfile still exist)
Line 5: Line 5:
 
<pre>
 
<pre>
 
export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"
 
export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"
 +
</pre>
 +
 +
====Quick backup to tape via tdpo===
 +
<pre>
 +
rman nocatalog target /
 +
run {
 +
    allocate channel c1  type sbt_tape parms 'ENV=(TDPO_OPTFILE=/oracle/VRAP1/admin/tdpo.opt)';
 +
    backup database;
 +
}
 
</pre>
 
</pre>
  

Revision as of 12:41, 1 June 2013

list - tells you what has already been done
report - tells you what needs to be done

Change date and time format to get better reporting

Set this at unix prompt before starting RMAN 

export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"

=Quick backup to tape via tdpo

rman nocatalog target /
run {
    allocate channel c1  type sbt_tape parms 'ENV=(TDPO_OPTFILE=/oracle/VRAP1/admin/tdpo.opt)';
    backup database;
}

Check the status of currently running backups

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
/

Check the status of the database backups for the past 7 days

Run this one from sys on the database server 

set pages 100
set lines 200
col operation   for a12
col backup_type for a27
col start_time  for a17
col end_time    for a17
col duration    for 90.99
col days_ago    for 90

select vrbsd.operation
,      vrbsd.command_id                              backup_type
,      to_char(vrbsd.start_time,'DD-MON-YY HH24:MI') start_time
,      to_char(vrbsd.end_time,'DD-MON-YY HH24:MI')   end_time
,      vrbsd.status                                  status
,      (vrbd.elapsed_seconds/3600)                   duration
,      trunc(sysdate)-trunc(vrbsd.start_time)        days_ago
from   v$rman_backup_job_details     vrbd
,      v$rman_backup_subjob_details  vrbsd
where  1=1
and    vrbd.session_key = vrbsd.session_key
and    vrbd.input_type='DB INCR'
and    vrbsd.start_time > sysdate -8
order  by vrbsd.start_time desc

Check the status of the database backups from the RMAN catalog

Run this one from the RMAN catalog server 

select s.db_name
,      s.status
,      max(s.start_time) start_time
,      max(round(s.mbytes_processed/1024))  "processed_data(GB)"
,      max(round((end_time-start_time)*60*24)) "duration(min)"
,      trunc(sysdate)-trunc(s.start_time)   days_since_last_backup
from   rc_rman_status s 
where  1=1
and    lower(s.operation)   = 'backup'
and    lower(s.object_type) ='db full'
and    s.start_time =
       (
       select max(md.start_time)
       from   rc_rman_status md 
       where  1=1
       and    lower(md.operation)   = 'backup'
       and    lower(md.object_type) ='db full'
       and    md.db_name = s.db_name
       )
group  by s.db_name
,      s.object_type
,      s.operation
,      s.status
,      trunc(sysdate)-trunc(s.start_time)
order  by trunc(sysdate)-trunc(s.start_time) desc


Which tapes are the backups on?

Not really necessary as RMAN can work it out but if you need to know... 

select media
, to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') backed_up
from   rc_backup_piece
where  1=1
and    db_id           = 4053457814
and    completion_Time > sysdate - 2
order  by 2

To remove all rows from v$rman_status

SYS.DBMS_BACKUP_RESTORE.resetCfileSection(28);

Useful RMAN commands

Connect RMAN to the databases controlfile (assuming db environment is set)

rman target /

Connect RMAN to the recovery catalog (assuming db environment is set)

rman target / catalog catowner/catpass@catdb

Show what backups are available

list backup of database;
list backup of spfile;
list backup of controlfile;
LIST ARCHIVELOG ALL;
LIST BACKUP OF ARCHIVELOG ALL;
LIST BACKUP OF DATAFILE 1;
LIST BACKUP SUMMARY;
LIST INCARNATION;
LIST BACKUP BY FILE;
LIST BACKUP OF DATAFILE 11 SUMMARY;
LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
LIST BACKUPSET OF DATAFILE 1;
LIST FAILURE;
LIST FAILURE 641231 detail;

Delete archivelogs that have been backed up twice

delete noprompt archivelog all backed up 2 times to sbt_tape;

Backup the archivelogs to tape then delete the ones on disk

backup device type sbt_tape archivelog all delete all input;

Show all the configured options

show all;

What needs backing up to satisfy retention policy (times backed up/recovery window etc)?

report need backup;

What can be deleted?

report obsolete;

Check whether the backup pieces registered in the controlfile still exist

CROSSCHECK BACKUP;
CROSSCHECK COPY;
CROSSCHECK backup of database;
CROSSCHECK backup of controlfile;
CROSSCHECK archivelog all;

Delete all log files up till yesterday provided they are already on tape

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT  'LOG_%d_t%T_s%s_u%U' parms 'ENV=(TDPO_OPTFILE=/home/tools/scripts/rman/tdpo_SID.opt)';
BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES TAG=BU20130506T023623P27000930;
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'sysdate-(1*24/24)' BACKED UP 1 TIMES TO DEVICE TYPE 'SBT_TAPE';

Restore a database using RMAN

Reproduced from ss64.com 

There are many ways to restore a database using an RMAN backup - this example assumes you are performing a Disaster-Recovery restore of all data and recovering the entire database with the same SID and the same disk/tablespace layout.

You will need the following information:

Database SID: ________

Database SYS password: ________

Disk layout and sizes: ________

Database ID (DBID): ________

There are 5 steps to recover the database:

    1) Create a new (empty) database instance
    2) Mount the instance
    3) Restore the datafiles
    4) Recover the database
    5) Reset the logs

1) Create a new (empty) database instance

    Configure the new server with same disk layout as the original database - if necessary use Symbolic Links (or in Windows use disk manager to re-assign drive letters.)

    Ensure you have enough disk space for both the backup files plus the restored database files.

    Create a new database with the database configuration assistant (DBCA) and set the SYS password and global database_name to the same as the original database.

    If the database to be restored is in archive log mode, set the LOG_ARCHIVE_FORMAT parameter to match the setting in the original database.

    The ORAPWD utility can also be used to change the SYS password.

    Set the environment variable NLS_LANG for your character set -
    NLS_LANG=American_America.WE8ISO8859P1

2)  Mount the empty instance

    SQL> Shutdown immediate;
    SQL> Startup mount;

    or specifying the pfile explicitly:

    SQL> CREATE PFILE='C:\oracle\Database\initLive.ora' FROM SPFILE;
    SQL> Shutdown immediate;
    SQL> Startup mount pfile=C:\oracle\Database\initLive.ora

3) Restore the datafiles

    In this case we have copied the RMAN backup files and archive logs to R:\Rman\

    Change the dbid to match that of the database being restored

    RMAN> SET dbid = 477771234;

    RMAN> run {
    ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT 'R:\Rman\%U';
    restore database;
    }

    At this point the datafiles and tablespaces will be re-created. For a large database it can take a long time to restore each tablespace - for better performance during a restore place the RMAN backup files on a separate disk to the Oracle datafiles to reduce disk contention.

4) Recover the database

    SQL> Recover from 'L:\oradata\live' database until cancel using backup controlfile;
    SQL> cancel

5) Reset the logs

    SQL> alter database open resetlogs;

    This will update all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.

    As soon as you have done a resetlogs run a full backup, this is important as should you suffer a second failure you will not be able to perform a second recovery because after resetting the logs the SCN numbers will no longer match any older backup files.

Notes:

The DBID can be retrieved in several places, if the database is running: Select dbid from V$DATABASE;

The RMAN client displays the dbid at startup when connecting to a database:

Copyright (c) 1995, 2003, Oracle. All rights reserved.
connected to target database: RDBMS (DBID=7776644123)

The default filename format for an RMAN controlfile autobackup is c-IIIIIIIIII-YYYYMMDD-QQ, where: IIIIIIIIII is the DBID.

RMAN reporting by André Araujo

Reference: pythian.com
Reproduced here in case it disappears from the internets.


A lot of metadata about the RMAN backup jobs can be found in the V$RMAN_% views.
These views show past RMAN jobs as well as jobs currently running.
Once the jobs complete backup sets, metadata about the sets and pieces are also added to the control file and can be accessed through the V$BACKUP_% views.

For the queries in this post I need only four of those views:

  • V$BACKUP_SET
  • V$BACKUP_SET_DETAILS
  • V$RMAN_BACKUP_JOB_DETAILS
  • GV$RMAN_OUTPUT

Query 1 (Backup jobs' status and metadata) 

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;

Query 2 (Backup set details) 

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;

Query 3 (Backup job output) 

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;
Retrieved from "https://mailnest.com/dbawiki/index.php?title=RMAN&oldid=571"