DBA Tips Archive for Oracle

  


Views and Synonyms

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. v$rman_configuration
  3. v$controlfile_record_section
  4. Automatically Backed Up Control Files
  5. Automatically Backed Up SPFILES
  6. Available Backup Sets
  7. Available Backup Pieces



Overview

It is always helpful to know the status of your backups and much of that information is available through views contained in the target database. First, let's take a look at a list of those views that contain operational information about RMAN:

Now, let's take a more detailed look at some of these views and how they can be queried.



v$rman_configuration

When I first tried to query from this view, I received no records. This view only gets populated with non-default configuration values. If you want to see all values (including default values), you will need to use the show all; command within the RMAN environment. For example, I tried the following query:
% sqlplus system/manager

SQL> select * from v$rman_configuration;

no rows selected
Now, let's create a backup policy. When we do this, the new values will be persistent for RMAN in the controlfile of the target database. Let's now enter the RMAN interface and set those configuration parameters:
% rman target /

RMAN> configure default device type to disk;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

RMAN> exit

% sqlplus system/manager

SQL> select * from v$rman_configuration;

     CONF# NAME                      VALUE
---------- ------------------------- ----------
         1 DEFAULT DEVICE TYPE TO    DISK
Once you create a persistent backup configuration and these parameters are configured, RMAN will continue to reuse the configured options for subsequent backups unless you override the option within your script or to clear or disable it.



v$controlfile_record_section

Whether you use a recovery catalog or not, RMAN will always store information about backups and recovery operations in the control file of the target database. The target database provides a view named v$controlfile_record_section that can be used view the number of records stored within the control file of the target database. For this example, I have not done any backups. Let's take a look at the v$controlfile_record_section view:
SQL> SELECT type, records_total, records_used
  2  FROM v$controlfile_record_section
  3  WHERE type lIKE '%BACKUP%';

TYPE                 RECORDS_TOTAL RECORDS_USED
-------------------- ------------- ------------
BACKUP SET                     612            0
BACKUP PIECE                  1009            0
BACKUP DATAFILE               1056            0
BACKUP REDOLOG                 107            0
BACKUP CORRUPTION              742            0
BACKUP SPFILE                  226            0

6 rows selected.
The RECORDS_USED column is still zero since I have not taken any backups. The records are insert into these views after successful creation of a backupset. When RMAN reads the files to backup, if any corruption is encountered, then the corruption views are populated with file#, block#, and contigous blocks after the initial corrupt block.

Now, let's performa a backup to show how the control file records get populated. Before performing the backup, let's change several of the configuration settings for RMAN:

RMAN> configure retention policy to redundancy 3;
RMAN> configure default device type to disk;
RMAN> configure controlfile autobackup on;
RMAN> configure controlfile autobackup format for device type disk to '/orabackup/rman/TARGDB/%F';
RMAN> configure device type disk parallelism 2;
Finally, let's perform a consistent backup using the following script backup_level0_cold.rcv:

Script: backup_level0_cold.rcv
run {
shutdown immediate
startup mount
backup
  incremental level 0
  database format '/orabackup/rman/TARGDB/backup_db_%d_S_%s_P_%p_T_%t'
  tag=TARGDB_closed_Level0;
shutdown
startup open;
}
exit

To run the script:

% rman target backup_admin/backup_admin  @backup_level0_cold.rcv
Now, let's take a look at the control file records view again.
SQL> SELECT type, records_total, records_used
  2  FROM v$controlfile_record_section
  3  WHERE type lIKE '%BACKUP%';

TYPE                 RECORDS_TOTAL RECORDS_USED
-------------------- ------------- ------------
BACKUP SET                     612            3
BACKUP PIECE                  1009            3
BACKUP DATAFILE               1056            9
BACKUP REDOLOG                 107            0
BACKUP CORRUPTION              742            0
BACKUP SPFILE                  226            1

6 rows selected.
From this backup, we have 2 backupsets from the parallelism 2 inthe the channel persistent configuration set (show above). The files to channel algorithm will split the files across the allocated channels, since there are 2 channels, there will be 2 backupsets that consist of the datafile backups. the 3rd is the controlfile backup created with the CONTROLFILE AUTOBACKUP feature.



Automatically Backed Up Control Files

Now let's take a look at a query that will provide us with all automatically backed up control files.

Script: rman_controlfiles.sql
prompt
prompt Available automatic control files within all available (and expired) backup sets.
prompt 

SELECT
    bs.recid                                               bs_key
  , bp.piece#                                              piece#
  , bp.copy#                                               copy#
  , bp.recid                                               bp_key
  , DECODE(   bs.controlfile_included
            , 'NO', '-'
            , bs.controlfile_included)                     controlfile_included
  , TO_CHAR(bs.completion_time, 'DD-MON-YYYY HH24:MI:SS')  completion_time
  , DECODE(   status
            , 'A', 'Available'
            , 'D', 'Deleted'
            , 'X', 'Expired')                              status
  , handle                                                 handle
FROM
    v$backup_set    bs
  , v$backup_piece  bp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
  AND bs.controlfile_included != 'NO'
ORDER BY
    bs.recid
  , piece#
/

Available automatic control files within all available (and expired) backup sets. BS Piece Copy BP Controlfile Completion Key # # Key Included? Time Status Handle ----- ------ ----- ----- ----------- -------------------- --------- ----------------------------------------------- 45 1 1 106 YES 25-DEC-2004 00:29:53 Available /orabackup/rman/TARGDB/c-2528050866-20041225-00 49 1 1 110 YES 26-DEC-2004 19:33:36 Available /orabackup/rman/TARGDB/c-2528050866-20041226-00 53 1 1 114 YES 27-DEC-2004 19:39:42 Available /orabackup/rman/TARGDB/c-2528050866-20041227-01



Automatically Backed Up SPFILES

Here is a query that will provide us with all automatically backed up Server Parameter Files (SPFILE).

Script: rman_spfiles.sql
prompt
prompt Available automatic SPFILE files within all available (and expired) backup sets.
prompt 

SELECT
    bs.recid                                               bs_key
  , bp.piece#                                              piece#
  , bp.copy#                                               copy#
  , bp.recid                                               bp_key
  , sp.spfile_included                                     spfile_included
  , TO_CHAR(bs.completion_time, 'DD-MON-YYYY HH24:MI:SS')  completion_time
  , DECODE(   status
            , 'A', 'Available'
            , 'D', 'Deleted'
            , 'X', 'Expired')                              status
  , handle                                                 handle
FROM
    v$backup_set                                           bs
  , v$backup_piece                                         bp
  ,  (select distinct
          set_stamp
        , set_count
        , 'YES'     spfile_included
      from v$backup_spfile)                                sp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
  AND bs.set_stamp = sp.set_stamp
  AND bs.set_count = sp.set_count
ORDER BY
    bs.recid
  , piece#
/

Available automatic SPFILE files within all available (and expired) backup sets. BS Piece Copy BP SPFILE Completion Key # # Key Included? Time Status Handle ----- ------ ----- ----- ----------- -------------------- --------- ----------------------------------------------- 45 1 1 106 YES 25-DEC-2004 00:29:53 Available /orabackup/rman/TARGDB/c-2528050866-20041225-00 49 1 1 110 YES 26-DEC-2004 19:33:36 Available /orabackup/rman/TARGDB/c-2528050866-20041226-00 53 1 1 114 YES 27-DEC-2004 19:39:42 Available /orabackup/rman/TARGDB/c-2528050866-20041227-01



Available Backup Sets

Now, a query that gives a nice overview of all RMAN Backup Sets. This will include both expired and non-expired backup sets.

Script: rman_backup_sets.sql
prompt
prompt Available backup sets contained in the control file.
prompt Includes available and expired backup sets.
prompt 

SELECT
    bs.recid                                              bs_key
  , DECODE(backup_type
           , 'L', 'Archived Logs'
           , 'D', 'Datafile Full'
           , 'I', 'Incremental')                          backup_type
  , device_type                                           device_type
  , DECODE(   bs.controlfile_included
            , 'NO', null
            , bs.controlfile_included)                    controlfile_included
  , sp.spfile_included                                    spfile_included
  , bs.incremental_level                                  incremental_level
  , bs.pieces                                             pieces
  , TO_CHAR(bs.start_time, 'mm/dd/yy HH24:MI:SS')         start_time
  , TO_CHAR(bs.completion_time, 'mm/dd/yy HH24:MI:SS')    completion_time
  , bs.elapsed_seconds                                    elapsed_seconds
  , bp.tag                                                tag
  , bs.block_size                                         block_size
FROM
    v$backup_set                           bs
  , (select distinct
         set_stamp
       , set_count
       , tag
       , device_type
     from v$backup_piece
     where status in ('A', 'X'))           bp
 ,  (select distinct
         set_stamp
       , set_count
       , 'YES'     spfile_included
     from v$backup_spfile)                 sp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bs.set_stamp = sp.set_stamp (+)
  AND bs.set_count = sp.set_count (+)
ORDER BY
    bs.recid
/

Available backup sets contained in the control file. Includes available and expired backup sets. BS Backup Device Controlfile SPFILE Inc. # of Start End Elapsed Block Key Type Type Included? Included? Level Pieces Time Time Seconds Tag Size ------- ------------- ------ ----------- --------- ------- ------ ----------------- ----------------- -------- ------------------- ------ 42 Archived Logs DISK 1 12/25/04 00:25:33 12/25/04 00:25:34 1 TAG20041224T002533 512 43 Datafile Full DISK 1 12/25/04 00:25:35 12/25/04 00:29:50 255 TAG20041224T002535 8,192 44 Archived Logs DISK 1 12/25/04 00:29:50 12/25/04 00:29:51 1 TAG20041224T002950 512 45 Datafile Full DISK YES YES 1 12/25/04 00:29:52 12/25/04 00:29:53 1 8,192 46 Archived Logs DISK 1 12/26/04 19:28:23 12/26/04 19:29:05 42 TAG20041227T192822 512 47 Datafile Full DISK 1 12/26/04 19:29:09 12/26/04 19:33:25 256 TAG20041227T192909 8,192 48 Archived Logs DISK 1 12/26/04 19:33:34 12/26/04 19:33:34 0 TAG20041227T193334 512 49 Datafile Full DISK YES YES 1 12/26/04 19:33:35 12/26/04 19:33:36 1 8,192 50 Archived Logs DISK 1 12/27/04 19:35:23 12/27/04 19:35:23 0 TAG20041227T193523 512 51 Datafile Full DISK 1 12/27/04 19:35:24 12/27/04 19:39:38 254 TAG20041227T193524 8,192 52 Archived Logs DISK 1 12/27/04 19:39:40 12/27/04 19:39:40 0 TAG20041227T193940 512 53 Datafile Full DISK YES YES 1 12/27/04 19:39:41 12/27/04 19:39:42 1 8,192 12 rows selected.



Available Backup Pieces

Finally, here is a query that gives a nice overview of all RMAN Backup Pieces. This will include both expired and non-expired backup pieces.

Script: rman_backup_pieces.sql
prompt
prompt Available backup pieces contained in the control file.
prompt Includes available and expired backup sets.
prompt 

SELECT
    bs.recid                                            bs_key
  , bp.piece#                                           piece#
  , bp.copy#                                            copy#
  , bp.recid                                            bp_key
  , DECODE(   status
            , 'A', 'Available'
            , 'D', 'Deleted'
            , 'X', 'Expired')                           status
  , handle                                              handle
  , TO_CHAR(bp.start_time, 'mm/dd/yy HH24:MI:SS')       start_time
  , TO_CHAR(bp.completion_time, 'mm/dd/yy HH24:MI:SS')  completion_time
  , bp.elapsed_seconds                                  elapsed_seconds
FROM
    v$backup_set    bs
  , v$backup_piece  bp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
ORDER BY
    bs.recid
  , piece#
/

Available backup pieces contained in the control file. Includes available and expired backup sets. BS Piece Copy BP Start End Elapsed Key # # Key Status Handle Time Time Seconds ----- ------ ----- ----- --------- ----------------------------------------------------------------- ----------------- ----------------- -------- 42 1 1 103 Available /orabackup/rman/TARGDB/backup_db_TARGDB_S_42_P_1_T_545703933 12/25/04 00:25:33 12/25/04 00:25:34 1 43 1 1 104 Available /orabackup/rman/TARGDB/backup_db_TARGDB_S_43_P_1_T_545703935 12/25/04 00:25:36 12/25/04 00:29:50 254 44 1 1 105 Available /orabackup/rman/TARGDB/backup_db_TARGDB_S_44_P_1_T_545704190 12/25/04 00:29:51 12/25/04 00:29:51 0 45 1 1 106 Available /orabackup/rman/TARGDB/c-2528050866-20041225-00 12/25/04 00:29:52 12/25/04 00:29:53 1 46 1 1 107 Available /orabackup/rman/TARGDB/backup_db_TARGDB_S_46_P_1_T_546031703 12/26/04 19:28:23 12/26/04 19:29:05 42 47 1 1 108 Available /orabackup/rman/TARGDB/backup_db_TARGDB_S_47_P_1_T_546031749 12/26/04 19:29:10 12/26/04 19:33:25 255 48 1 1 109 Available /orabackup/rman/TARGDB/backup_db_TARGDB_S_48_P_1_T_546032014 12/26/04 19:33:34 12/26/04 19:33:34 0 49 1 1 110 Available /orabackup/rman/TARGDB/c-2528050866-20041226-00 12/26/04 19:33:36 12/26/04 19:33:36 0 50 1 1 111 Available /orabackup/rman/TARGDB/backup_db_TARGDB_S_50_P_1_T_546032123 12/27/04 19:35:23 12/27/04 19:35:23 0 51 1 1 112 Available /orabackup/rman/TARGDB/backup_db_TARGDB_S_51_P_1_T_546032124 12/27/04 19:35:24 12/27/04 19:39:38 254 52 1 1 113 Available /orabackup/rman/TARGDB/backup_db_TARGDB_S_52_P_1_T_546032380 12/27/04 19:39:40 12/27/04 19:39:40 0 53 1 1 114 Available /orabackup/rman/TARGDB/c-2528050866-20041227-01 12/27/04 19:39:42 12/27/04 19:39:42 0 12 rows selected.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Sunday, 23-May-2004 00:00:00 EDT
Page Count: 8902