While researching a problem with our Oracle Fast Recovery Area (FRA), I took note of the more useful commands I used. This is from Oracle 11g R2 with the Real Application Clusters (RAC) and Automatic Storage Management (ASM) options.
SQL*Plus
Show how much of the FRA is in use by different file types:
SQL> SELECT * FROM V$RECOVERY_AREA_USAGE ;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 27.8 0 292
BACKUP PIECE 2.8 0 2
IMAGE COPY 20.45 0 9
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
Show how big archived logs are growing each day for the last few days:
SQL> SELECT TRUNC(first_time), SUM(blocks*block_size)/1048576 Size_MB
FROM V$ARCHIVED_LOG
WHERE first_time > TRUNC(SYSDATE)-12
GROUP BY TRUNC(first_time)
ORDER BY 1 ;
TRUNC(FIRST_TIME) SIZE_MB
-------------------- ----------
Jun 25 2012 00:00:00 3397.69531
Jun 26 2012 00:00:00 9701.8252
Jun 27 2012 00:00:00 9688.72998
Jun 28 2012 00:00:00 9780.02295
Jun 29 2012 00:00:00 9860.13379
Jun 30 2012 00:00:00 10002.0894
Jul 01 2012 00:00:00 10028.8901
Jul 02 2012 00:00:00 9810.0166
Jul 03 2012 00:00:00 6563.98486
Show the history of backup jobs over the last few days. This example includes a failed backup:
SQL> 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 ;
SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS
----------- ------------- --------- -------------- -------------- -------
3041 ARCHIVELOG FAILED 06/27/12 07:39 06/27/12 07:40 .03
3043 ARCHIVELOG COMPLETED 06/27/12 07:41 06/27/12 08:04 .39
3049 DB FULL COMPLETED 06/28/12 12:15 06/28/12 13:59 1.72
3078 DB INCR COMPLETED 06/28/12 15:20 06/28/12 15:26 .10
3089 DB INCR COMPLETED 06/29/12 01:00 06/29/12 01:06 .10
3105 DB INCR COMPLETED 06/29/12 09:45 06/29/12 10:35 .83
3138 DB INCR COMPLETED 06/30/12 01:00 06/30/12 01:07 .11
3146 DB INCR COMPLETED 07/01/12 01:00 07/01/12 01:08 .13
3154 DB INCR COMPLETED 07/02/12 01:00 07/02/12 01:07 .12
3162 DB INCR COMPLETED 07/02/12 12:47 07/02/12 12:53 .10
3173 DB INCR COMPLETED 07/03/12 01:00 07/03/12 01:06 .11
Recovery Manager (RMAN)
Create a summary list of backups:
RMAN> LIST BACKUP SUMMARY ;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
859 B 1 A DISK Jul 03 2012 01:06:37 1 1 NO ORA_OEM_LEVEL_0
860 B F A DISK Jul 03 2012 01:06:52 1 1 NO TAG20120703T010649
Report on backups and archived redo logs that are now obsolete. I don't have anything interesting to report in this example:
RMAN> REPORT OBSOLETE ;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found
