V$ Tables Related to Backup Recovery/Restore
Each release Oracle brought up new features and enhancements to RMAN
improving its ease of use and functionality. Using the updateable
persistent configuration options (CONFIGURE ….), backup up the database
is easier then in the first version of RMAN introduced in 8i. It is help
to list the Synonyms containing the word RMAN and BACKUP in them.
Here’s a list from an Oracle 11g database for those with BACKUP in the name:
OBJECT_NAME
——————————
V$BACKUP
V$BACKUP_SET
V$BACKUP_PIECE
V$BACKUP_DATAFILE
V$BACKUP_SPFILE
V$BACKUP_REDOLOG
V$BACKUP_CORRUPTION
V$BACKUP_DEVICE
V$BACKUP_SYNC_IO
V$BACKUP_ASYNC_IO
GV$BACKUP
GV$BACKUP_SET
GV$BACKUP_PIECE
GV$BACKUP_DATAFILE
GV$BACKUP_SPFILE
GV$BACKUP_REDOLOG
GV$BACKUP_CORRUPTION
GV$BACKUP_DEVICE
GV$BACKUP_SYNC_IO
GV$BACKUP_ASYNC_IO
V$BACKUP_FILES
V$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_JOB_DETAILS
V$BACKUP_SET_DETAILS
V$BACKUP_PIECE_DETAILS
V$BACKUP_COPY_DETAILS
V$BACKUP_DATAFILE_DETAILS
V$BACKUP_CONTROLFILE_DETAILS
V$BACKUP_ARCHIVELOG_DETAILS
V$BACKUP_SPFILE_DETAILS
V$BACKUP_SET_SUMMARY
V$BACKUP_DATAFILE_SUMMARY
V$BACKUP_CONTROLFILE_SUMMARY
V$BACKUP_ARCHIVELOG_SUMMARY
V$BACKUP_SPFILE_SUMMARY
V$BACKUP_COPY_SUMMARY
V$UNUSABLE_BACKUPFILE_DETAILS
V$RMAN_BACKUP_TYPE
MGMT$HA_BACKUP
39 rows selected.
Here’s a list from the same Oracle 11g database with RMAN in the name:
OBJECT_NAME
——————————
V$RMAN_CONFIGURATION
GV$RMAN_CONFIGURATION
V$RMAN_STATUS
V$RMAN_OUTPUT
GV$RMAN_OUTPUT
V$RMAN_COMPRESSION_ALGORITHM
GV$RMAN_COMPRESSION_ALGORITHM
V$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_JOB_DETAILS
V$RMAN_BACKUP_TYPE
V$RMAN_ENCRYPTION_ALGORITHMS
MGMT$HA_RMAN_CONFIG
MGMT$HA_RMAN_CONFIG_ALL
13 rows selected.
Controlfile views to reference are:
V_$ARCHIVE
V_$ARCHIVED_LOG
V_$ARCHIVE_DEST
V_$ARCHIVE_DEST_STATUS
V_$ARCHIVE_GAP
V_$ARCHIVE_PROCESSES
V_$BACKUP_ARCHIVELOG_DETAILS
V_$BACKUP_ARCHIVELOG_SUMMARY
V_$BACKUP_CONTROLFILE_DETAILS
V_$BACKUP_CONTROLFILE_SUMMARY
V_$BACKUP_DATAFILE
V_$BACKUP_DATAFILE_DETAILS
V_$BACKUP_DATAFILE_SUMMARY
V_$CONTROLFILE
V_$CONTROLFILE_RECORD_SECTION
V_$DATAFILE
V_$DATAFILE_COPY
V_$DATAFILE_HEADER
V_$SYSTEM_FIX_CONTROL
V_$PROXY_ARCHIVEDLOG
V_$PROXY_ARCHIVELOG_DETAILS
V_$PROXY_ARCHIVELOG_SUMMARY
V_$PROXY_DATAFILE
GV_$FOREIGN_ARCHIVED_LOG
GV_$PROXY_ARCHIVEDLOG
GV_$PROXY_DATAFILE
GV_$SESSION_FIX_CONTROL
V_$SESSION_FIX_CONTROL
V_$DLM_TRAFFIC_CONTROLLER
V_$FOREIGN_ARCHIVED_LOG
GV_$BACKUP_DATAFILE
GV_$CONTROLFILE
GV_$CONTROLFILE_RECORD_SECTION
GV_$DATAFILE
GV_$DATAFILE_COPY
GV_$DATAFILE_HEADER
GV_$DLM_TRAFFIC_CONTROLLER
GV_$ARCHIVE
GV_$ARCHIVED_LOG
GV_$ARCHIVE_DEST
GV_$ARCHIVE_DEST_STATUS
GV_$ARCHIVE_GAP
GV_$ARCHIVE_PROCESSES
DBA_REGISTERED_ARCHIVED_LOG
DBA_REPRESOL_STATS_CONTROL
USER_REPRESOL_STATS_CONTROL
ALL_REPRESOL_STATS_CONTROL
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
DBA_HIST_DATAFILE
DBA_HIST_WR_CONTROL
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES
GV_$SYSTEM_FIX_CONTROL
PS_CONTROL_TBL_VW
MGMT_V_DB_CONTROLFILES_ECM
MGMT_V_DB_DATAFILES_ECM
MGMT$DB_CONTROLFILES
MGMT$DB_CONTROLFILES_ALL
MGMT$DB_DATAFILES
MGMT$DB_DATAFILES_ALL
62 rows selected.
That is a lot of objects and views. To view the default RMAN
configuration and then check the V$ view to see what is stored you can
accomplish this with a query like this:
set linesize 132
column name format a40
column value format a40
set echo on
set serveroutput on
select * from v$rman_configuration
/
CONF# NAME VALUE
1 BACKUP OPTIMIZATION ON
2 DEFAULT DEVICE TYPE TO DISK
3 CONTROLFILE AUTOBACKUP ON
4 DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET
PARALLELISM 2
5 DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1
6 ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1
7 SNAPSHOT CONTROLFILE NAME TO ‘/opt/app/oracle/admin/backup/PS90HRD
V/backupsets/snapcf_ev.f’
8 MAXSETSIZE TO UNLIMITED
9 CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE DISK TO ‘+ps90hr_disk3/ps90hrdv/autoback
TYPE up/%F’
9 rows selected.
If you aren’t using a recovery catalog you can look at the
controlfile since this is were backup information is maintained if you
aren’t using a recovery catalog:
select type, records_total, records_used
from v$controlfile_record_section
where type like ‘%BACKUP%’
/
TYPE RECORDS_TOTAL RECORDS_USED
—————————- ————- ————
BACKUP SET 1227 224
BACKUP PIECE 1000 331
BACKUP DATAFILE 2126 2126
BACKUP REDOLOG 215 215
BACKUP CORRUPTION 1115 0
BACKUP SPFILE 131 93
6 rows selected.
The records used column isn’t zero because I’ve taken numerous
backups, however, if you haven’t completed a backup yet this column will
be zero. When RMAN reads the files to determine the files that will be
backed up, if any corruption is detected then the corruption views are
populated with that information such as file#, block# and contiguous
blocks after the initial corrupt block.
RMAN will continue to use the persistent backup configuration
settings once they are set unless they are overridden. The RMAN “SHOW”
command displays those settings:
SHOW option [,parameter, ...]
RETENTION POLICY
EXCLUDE
BACKUP COPIES
CHANNEL
DEFAULT DEVICE TYPE
DEVICE TYPE
SNAPSHOT CONTROLFILE
…
ALL
RMAN> show all;
Recovery Manager: Release 11.1.0.6.0 – Production on Sat Nov 1 21:32:38 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
show all;connected to target database: PS90HRDV (DBID=2173713801)
connected to recovery catalog database
RMAN>
RMAN configuration parameters for database with db_unique_name PS90HRDV are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘+ps90hr_disk3/ps90hrdv/autobackup/%F’;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE COMPRESSION ALGORITHM ‘BZIP2′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/snapcf_ev.f’;
You can script the configuration parameters you want to use within a
run block to change one or more parameters or just change a single
parameter at a time from the RMAN prompt and not use the run block
option.
Example:
CONNECT catalog rman/rman
crosscheck copy;
delete expired copy;
crosscheck backup;
delete expired backup;
run {
configure channel 1 device type disk clear;
configure channel 2 device type disk clear;
configure device type disk backup type to backupset parallelism 1;
backup spfile;
configure snapshot controlfile name to ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/snapcf_ev.f’;
backup current controlfile;
configure channel 1 device type disk format ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/df_t%t_s%s_p%p’ maxpieces
ize 1750 M;
configure channel 2 device type disk format ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/df_t%t_s%s_p%p’ maxpieces
ize 1750 M;
configure device type disk backup type to compressed backupset parallelism 2;
backup as compressed backupset incremental level 0 database tag=”ps90hrdv”;
configure channel 1 device type disk format ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/al_t%t_s%s_p%p’ maxpieces
ize 5000M;
configure channel 2 device type disk format ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/al_t%t_s%s_p%p’ maxpieces
ize 5000M;
backup archivelog all delete all input tag=”ps90hrdv_archivelogs”;
configure channel 1 device type disk clear;
configure channel 2 device type disk clear;
configure snapshot controlfile name to ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/snapcf_ev.f’;
backup current controlfile;
configure channel 1 device type disk clear;
}
Enabling and Disabling Change Tracking You can enable or disable change tracking when the database is either open or mounted. To alter the change tracking setting, you must use SQL*Plus to connect to the target database with administrator privileges.
To store the change tracking file in the database area, set DB_CREATE_FILE_DEST in the target database. Then issue the following SQL statement to enable change tracking: SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
You can also create the change tracking file in a location you choose yourself, using the following SQL statement: SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/mydir/rman_change_track.f' REUSE;
The REUSE option tells Oracle to overwrite any existing file with the specified name.
To disable change tracking, use this SQL statement: SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
If the change tracking file was stored in the database area, then it is deleted when you disable change tracking.
Checking Whether Change Tracking is Enabled
From SQL*Plus, you can query V$BLOCK_CHANGE_TRACKING.STATUS to determine whether change tracking is enabled, and if it is, query V$BLOCK_CHANGE_TRACKING.FILENAME to display the filename.
Moving the Change Tracking File
If you need to move the change tracking file, the ALTER DATABASE RENAME FILE command updates the control file to refer to the new location. The process outlined in this section describes how to change the location of the change tracking file while preserving its contents.
To relocate the change tracking file:
If necessary, determine the current name of the change tracking file: SELECT filename
FROM V$BLOCK_CHANGE_TRACKING;
Shut down the database. For example: SHUTDOWN IMMEDIATE
Using host operating system commands, move the change tracking file to its new location.
Mount the database and move the change tracking file to a location that has more space. For example: ALTER DATABASE RENAME FILE 'ora_home/dbs/change_trk.f' TO '/new_disk/change_trk.f';
Open the database: ALTER DATABASE OPEN;
If you cannot shut down the database, then you must disable change tracking and re-enable it at the new location, as in the following example: ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new_location';
If you choose this method, you will lose the contents of the change tracking file. Until the next time you complete a level 0 incremental backup, RMAN will have to scan the entire file.
Estimating Size of the Change Tracking File on Disk
The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database. Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. Note, however, the following two factors that may cause the file to be larger than this estimate suggests:
To avoid overhead of allocating space as your database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB incremenents. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.
For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.
