Random Oracle FRA Usage Notes

Posted on in System Administration

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

Slaptijack's Koding Kraken