RMAN Quick Ref

External Links

http://www.fadalti.com/oracle/database/rman_effective_use.htm
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14193/toc.htm

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;

SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK         %_COMPLETE
---------- ---------- ---------- ---------- ---------- ---------             
---------       
17        167          1                    4784     116328                   4.11

You can reissue this query throughout the backup process to get an update on the
work still needing to be completed:

SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK   %_COMPLETE

---------- ---------- ---------- ---------- ---------- ---------       
---------       
17        167          1                   96999     116328            83.38

Flash Recovery Area

posted Jul 13, 2011, 8:14 AM by Sachchida Ojha   [ updated Jul 13, 2011, 9:28 AM ]


The Flash R ecovery Area: The flash recovery area (FRA) is not a requirement for using RMAN, but it should be. New in 10g, the FRA is a specific location on disk that you set up to house all the Oracle recovery files.
Recovery files refers to all files that might be required for a media recovery operation: full datafile backups, incremental backups, datafile copies, backup control files, and archive logs. The FRA also functions as a repository for mirrored copies of online redo log files, the block-change tracking file, and for a current control file. If set up, flashback logs for using the flashback database option also live in the FRA. (We discuss flashback technologies in Chapter 1 3.)
The concept behind the FRA is to simplify the management of your backup and recovery duties by consolidating the requisite files into a single location that Oracle and RMAN can then micromanage, while the DBA moves on to other important duties. This simplification is based on some underlying principles of a solid backup strategy that focuses on availability:

  1. At least one copy of important datafiles, if not the entire database, should be kept on disks that are locally accessible to the database.
  2. Backups past a certain age should be moved to tape based on storage pressure on local disks.
  3. Long-term backup management should be almost completely automatic, based on business rules.

The FRA that you set up can be either a directory on a normal disk volume or an Automatic Storage Management (ASM) disk group. The FRA is determined by two initialization parameters:

DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE. The first determines the location and the second, the size. These can be set With an FRA configured, you are not required to set any other LOG_ARCHIVE_DEST_n
parameter for archive logs; by default, with an FRA, Oracle will default LOG_ARCHIVE_DEST_10 to the FRA. It should also be noted that with an FRA in use, you cannot use LOG_ARCH IVE_DEST or LOG_ARCH IVE_DUPLEX_DEST—but, of course, you rid yourself of these outdated parameters long ago… right?

The FRA manages recovery files internally, first based on database name, then on types of files, and then by the dates the files are generated. The files themselves are named according to the Oracle Managed Files (OMF) format. As such, the files are hard to decipher (except for archive logs, which still maintain the structure you give them with the LOG_ARCHIVE_FORMAT parameter). Significant internal directory structures exist for file management. However, the point of an FRA is that you don’t need to spend much time worrying about the files. That being said, it’s worth taking note of the internal structure and familiarizing yourself with where the files go. Sooner or later, you will end up digging for a particular file manually. Trust us.The same FRA can be used by multiple databases. This can provide significant advantages, particularly for a Data Guard configuration, but also if you have a large ASM disk group and multiple databases on the same system. It can come in handy, as well, when it comes time to clone production for test purposes. Here’s the catch: all the databases that share the FRA either have a different value for DB_NAME or have a different name for the value DB_UNIQUE_NAME.

Setting Up the Flash Recovery Area
To set up the FRA, you will want to configure the following parameters:

Parameter DB_RECOVERY_FILE_DEST_SIZE DB_RECOVERY_FILE_DEST

Example
Alter system set db_recovery_file_dest_size=20G scope=both;

Purpose  Sets the allocated size of the FRA, in bytes, and must be defined in order to enable the FRA. This allows you to control how much disk space is allocated to the FRA.
You should not set this value to a size greater than the total amount of available disk space.

Alter system set db_recovery_file_dest=' /u01/oracle/flash_recovery' scope=both;

Specifies the location of the FRA. This can be a file system, an ASM disk location, or an OMF location.

Note that you must specify the DB_RECOVERY_FILE_DEST_SIZE parameter before you specify the DB_RECOVERY_FILE_DEST parameter. Failure to do so will result in an ORA-32 001 error message. In a similar fashion, you must disable the DB_RECOVERY_FILE_DEST parameter before you reset the DB_RECOVERY_FILE_DEST_SIZE parameter. Leaving DB_RECOVERY_FILE_DEST empty will disable the FRA. Here is an example of disabling the FRA by resetting the DB_
RECOVERY_FILE_DEST parameter:

alter system set db_recovery_file_dest=' ' scope=both;

Oracle allows you to archive to both the FRA and to one or more additional locations through the use of the LOG_ARCHIVE_DEST_n parameters. One case when you would want to do this is if you were configuring standby databases and you still wanted to take advantage of the features of the FRA.

To configure both FRA and archive log destination directories, you set the standard FRA parameter DB_RECOVERY_FILE_DEST, defining the location of the FRA. You will also define the various LOG_ARCHIVE_DEST_n parameters that are required. By default, when a LOG_ARCHIVE_DEST_n parameter is defined, that location will be used instead of the FRA. To get Oracle to use the FRA when a LOG_ARCHIVE_DEST_n parameter is set, you need to define an additional LOG_ARCHIVE_DEST_n parameter for the FRA. Typically, this will be LOG_ARCHIVE_DEST_1 0, and you will use the Oracle-supplied constant USE_DB_RECOVERY_FILE_DEST to indicate that this destination is the FRA. Here is an example where we configure Oracle to use the FRA and a regular archive log destination directory:

alter system set log_archive_dest_10=' LOCATION=USE_DB_RECOVERY_FILE_DEST' ;
alter system set log_archive_dest_1=' location=c: \oracle\oraarc\beta1 mandatory' ;


In this example, the ARCH process will now create archived redo logs in both LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_10, which is the FRA.

Flash Recovery Area Views
Several views are available to help you manage the FRA. These views include the following:
  1. DBA_OUTSTANDING_ALERTS
  2. V$RECOVERY_FILE_DEST
  3. V$FLASH_RECOVERY_AREA_USAGE
Also, columns are available in several other views that help you to manage the FRA. Let’s look at each of these views and columns in more detail. The DBA_OUTSTANDING_ALERTS View As files are added or removed from the FRA, records of these events are logged in the database alert log. You can check the new DBA view, DBA_OUTSTANDING_ALERTS, for information on outstanding issues with the FRA. Note that there is somewhat of a lag between the time a space-related issue occurs and when the warning appears in the DBA_OU TSTANDING_ALERTS view. The following is an example where the FRA has run out of space and is posting an alert to the DBA_OUTSTANDING_ALERTS view. You would need to deal with this situation quickly or risk the database coming to a complete halt. In this case, we used the alter system command to increase the amount of space allocated to the FRA.

SQL> select reason from dba_outstanding_alerts;
REASON
-------------------------------------------------------------
db_recovery_file_dest_size of 524288000 bytes is 100. 00% used
and has 0 remaining bytes available.

SQL> alter system set db_recovery_file_dest_size=800m;

The V$RECOVERY_FILE_DEST View The V$RECOVERY_FILE_DEST view provides an overview of the FRA that is defined in your database. It provides the size that the FRA is configured for, the amount of space used, how much space can be reclaimed, and the number of files in the FRA. In the following example, we can see that the increase in space to the FRA to 800MB has been recorded (SPACE_LIMIT). However, we still have used too much space (SPACE_
USED), and the FRA is still full.

SQL> select * from v$recovery_file_dest;
NAME
-----------------------------------------------------------------------  
SPACE_LIMIT             SPACE_USED  SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------- ---------------------- ------------------ --------------
c: \oracle\product\10. 2.0\flash_recovery_area
   838, 860, 800          1, 057, 116, 672        338, 081, 280              11
One nice thing about Oracle is that it manages the FRA space for us as much as it can, and if there is reclaimable space available, it will free it as required. Note that in the previous query, Oracle indicated we were out of FRA space. Did you notice the SPACE_RECLAIMABLE column, though? This column indicates that there is reclaimable space available. This is space that is taken up by archived redo logs or backup set pieces that are no longer needed by virtue of whatever
retention criteria we have selected (we will discuss retention criteria and setting those criteria later in this chapter). When Oracle needs space in the FRA (say, for example, we force a log switch), it will remove any files that are reclaimable and free up space. In the next query, we can see that this has occurred. After we ran the previous query that indicated we were out of FRA space, we forced a log switch. This caused Oracle to reclaim space from the FRA for reuse, and
it then was able to write out the archived redo log. We can query the V$RECOVERY_FILE_DEST view and see that this has indeed occurred:
SQL> alter system switch logfile;
System altered.
SQL> select * from v$recovery_file_dest;
NAME
-----------------------------------------------------------------------  
SPACE_LIMIT             SPACE_USED  SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------- ---------------------- ------------------ --------------
c: \oracle\product\10. 2.0\flash_recovery_area
   838, 860, 800            719, 412, 7 36             64, 000        

The V$FLASH_RECOVERY_AREA_USAGE View The V$FLASH_RECOVERY_AREA_USAGE view provides more detailed information on which types of files are occupying space in the FRA. This view groups the file types and then provides the percentage of space that is used by each file type, the percentage of the total FRA reclaimable space that comes from that group, and the number of files in the FRA that come from that group. Here is a query of the V$FLASH_RECOVERY_AREA_USAGE view:

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- --------------CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                17. 14                     17 . 09               7
BACKUPPIECE              108. 88                     23. 22               4
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0
In this example, we notice a few things:We are over our defined space allocation (the PERCENT_SPACE_USED of all the rows exceeds 1 00 percent). This is probably because the size of the FRA was recently changed and Oracle has not yet reclaimed enough space to bring the total used below 1 00 percent.The backup set pieces are consuming most of that space, and 23 .22 percent of that space is reclaimable.The archived redo logs consume only 1 7 percent of the space allocated to the FRA, and even if we were to remove all of the archived redo logs, we would  not free up enough space to bring the FRA under the amount of space allocated to it.Other Views with FRA Columns The column IS_RECOVERY_DEST_FILE can be found in a number of Oracle Database V$ views such as V$CONTROLFILE, V$LOGFILE, V$ARCHIVED_LOG, V$DATAFILE_COPY, V$DATAFILE, and V$BACKU P_PIECE. This column is a Boolean that
indicates whether the file is in the FRA.Another column, BYTES, can be found in the V$BACKU P_PIECE and RC_BACKUP_PIECE (an RMAN recovery catalog view) views.

Auxiliary Database

posted Jul 12, 2011, 2:07 PM by Sachchida Ojha


The auxiliary database refers to the instance that will become host to restored files from the target database in the event of a tablespace point-in-time recovery (TSPITR), a duplication operation (cloning the database), or the creation of a standby database using RMAN backups. When you perform any of these tasks, you will be connecting to the target database and the auxiliary database at the same time from within RMAN. In this way, you can utilize the information about 
the backups in the target database control file to coordinate the restore of those backups to the auxiliary database location. The following shows the connection to both the target database (locally) and the auxiliary database (using an Oracle Net connection):

rman>connect target /
rman>connect auxiliary sys/pwd@aux1

RMAN makes a simultaneous connection to each database and requires access to the SYS.DBMS_BACKUP_RESTORE and SYS.DBMS_RCVMAN packages in both the target database and the auxiliary database. As such, RMAN requires sysdba privileges at the auxiliary, just as it does at the target. Because RMAN must make a sysdba connection to two separate databases, you are required to configure at least one of them with a password file and make an Oracle Net
connection to it.there is no way to connect locally to two different databases.

The RMAN Server Processes

posted Jul 12, 2011, 1:52 PM by Sachchida Ojha

RMAN makes a client connection to the target database, and two server processes are spawned. The primary process is used to make calls to packages in the SYS schema in order to perform the backup or recovery operations. This process coordinates the work of the channel processes during backups and restores.

The secondary, or shadow, process polls any long-running transactions in RMAN and then logs the information internally. You can view the results of this polling in the view V$SESSION_LONGOPS:

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
/

You can also view these processes in the V$SESSION view. When RMAN allocates a channel, it provides the session ID information in the output:
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
The sid information corresponds to the SID column in V$SESSION. So you could construct a query such as this:
SQL> column client_info format a30
SQL> column program format a15
SQL> select sid, saddr, paddr, program, client_info      from v$session where sid=16;
     
 SID SADDR    PADDR    PROGRAM         CLIENT_INFO
---------- -------- -------- --------------- -----------------------       
16 682144E8 681E82BC RMAN.EXE        rman channel=ORA_DISK_1   

Back up the control file

posted Jul 12, 2011, 1:44 PM by Sachchida Ojha


When you issue the command

alter database backup control file to trace;

the script that is generated does not include the information in the control file that identifies your backups. Without these backup records, you cannot access the backups when they are needed for recovery. All RMAN information is lost, and you cannot get it back. The only RMAN information that gets rebuilt when you rebuild the control file is any permanent configuration parameters you have set with RMAN. In Oracle 1 0g and higher, a new mechanism generates limited backup metadata within a control file, but you are still building in a lot of manual work that never used to exist. Therefore, we encourage you to avoid a control file rebuild at all costs.

If you back up the control file to a binary file, instead of to trace, then all backup information is preserved. This command looks like the following:

alter database backup controlfile to ' /u01/backup/bkup_cfile. ctl' ;

SQL returns the min and max SCN numbers contained in the last online backup.

posted Mar 18, 2011, 11:16 AM by Sachchida Ojha   [ updated Mar 18, 2011, 12:21 PM ]

SELECT MIN(VBD.CHECKPOINT_CHANGE#), MAX(VBD.CHECKPOINT_CHANGE#)
FROM V$BACKUP_DATAFILE VBD, V$DATAFILE VDF,
(SELECT
CREATION_CHANGE#,
MAX(COMPLETION_TIME) COMPLETION_TIME
FROM V$BACKUP_DATAFILE
WHERE CREATION_CHANGE# IN (
SELECT CREATION_CHANGE# FROM V$DATAFILE)
GROUP BY CREATION_CHANGE#
) QUERY1
WHERE VBD.CREATION_CHANGE# = VDF.CREATION_CHANGE#
AND VBD.CREATION_CHANGE# = QUERY1.CREATION_CHANGE#
AND VBD.COMPLETION_TIME = QUERY1.COMPLETION_TIME;

Modify TNSNAMES.ORA

cd $ORACLE_HOME/network/admin

vi tnsnames.ora

-- add the following:
REPOS =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.119)(PORT = 1521))
  )
  (CONNECT_DATA =
  (SERVICE_NAME = repos)
  )
)

-- save change
<Esc> zz

$ tnsping repos
Put the Database into Archive Log Mode
-- as oracle
$ sqlplus /nolog

SQL> conn / as sysdba

col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%arch%';

SQL> shutdown immediate;

SQL> startup mount exclusive;

SQL> alter database archivelog;

SQL> alter database open;

SQL> SELECT group#, thread#, sequence#, archived, status, first_time
     FROM v$log;

SQL> alter system switch logfile;

SQL> SELECT group#, thread#, sequence#, archived, status, first_time
     FROM v$log;

SQL> alter system switch logfile;

SQL> SELECT group#, thread#, sequence#, archived, status, first_time
     FROM v$log;

SQL> SELECT dbid, name FROM v$database;
-- be sure you save the dbid for future reference
Create Repository Tablespace and Schema
SQL> conn sys@repos AS SYSDBA
     password: ************

SQL> SELECT file_name
     FROM dba_data_files;

SQL> SELECT name, value
     FROM gv$parameter
     WHERE name like '%block%';

SQL> CREATE TABLESPACE cat_tbs
     DATAFILE '/app/oracle/product/oradata/REPOS/cat_tbs.dbf' SIZE 50M
     AUTOEXTEND ON
     BLOCKSIZE 8192
     FORCE LOGGING
     DEFAULT NOCOMPRESS
     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
     SEGMENT SPACE MANAGEMENT AUTO
     ONLINE;

SQL> CREATE USER repoomega#
     IDENTIFIED BY oracle1
     DEFAULT TABLESPACE cat_tbs
     TEMPORARY TABLESPACE temp
     QUOTA UNLIMITED ON cat_tbs;

SQL> GRANT create session TO repoomega#;
SQL> GRANT recovery_catalog_owner TO repoomega#;
SQL> GRANT execute ON dbms_stats TO repoomega#;

SQL> CREATE OR REPLACE PUBLIC SYNONYM dbms_stats FOR sys. dbms_stats;

SQL> conn repoomega#/oracle1@repos

SQL> SELECT COUNT(*) FROM user_objects;
Create RMAN Catalog and Register the Database
$ rman target / catalog repoomega#/oracle1@repos

-- if the tablespace already exists:
RMAN> create catalog;

-- if CAT_TBS is not the default tablespace
RMAN> create catalog tablespace cat_tbs;

RMAN> register database;

RMAN> report need backup;
Explore the RMAN Catalog
SQL> SELECT object_type, COUNT(*)
     FROM user_objects
     GROUP BY object_type;

SQL> SELECT object_name, object_type
     FROM user_objects
     WHERE object_type IN ('FUNCTION', 'PACKAGE');

SQL> desc rc_listbackuppipe

SQL> SELECT DISTINCT package_name, object_name
     FROM all_arguments
     WHERE owner = 'repoomega#'
     AND package_name LIKE 'DBMS%';
Configure Backup Parameters

RMAN> show all;

/* Enable the backup optimization feature introduced in 9i to make sure that RMAN won't backup an archivelog or datafile if there already exists a backup of that file. The FORCE option can be used to override optimization on a specific BACKUP command. */
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;                   -- do not back up unchanged data files

RMAN> CONFIGURE MAXSETSIZE TO 2 G;                        -- make filesize <= 2GB
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;               -- encrypt backups
RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256';            -- use 256 bit encryption
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;        # default

/* Configure the number of server processes (channels) that write backups to DISK. */
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

/* Set the retention policy to a recovery window of 30 days. This ensures that RMAN retains all backups needed to recover the database to any point in time in the last 30 days. You can use the DELETE OBSOLETE command to delete backups that are no longer required by the retention policy. To exclude a backup from consideration by the policy, you can use KEEP option with the BACKUP command. */
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;

/* Enable automatic controlfile backup after each database or archivelog backup */
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> show all;
Run Full Backup
Need to configure a backup location. Go to NetApp but for now:
$ mkdir $HOME/backup1
$ mkdir $HOME/backup2

RMAN> report need backup;

RMAN> RUN
      {
        ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/home/oracle/backup1/%U';
        ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/home/oracle/backup2/%U';
        # AS COPY is default when backing up to disk
        BACKUP DATABASE PLUS ARCHIVELOG;
      }

RMAN> report need backup;

RMAN> list backup summary;

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SQL> desc v$rman_status

SQL> SELECT operation, status, mbytes_processed, start_time, end_time
     FROM v$rman_status;
Validate Backup
RMAN> crosscheck backup of database;

-- need to look at data dictionary too
Trash Control File and Recover
$ cd /app/oracle/product/OracleHomes/oradata/orcl

$ ls -la

$ rm control02.ctl

$ cd /app/oracle/product/OracleHomes/admin/orcl/bdump

$ tail alert_orcl.log
Trash Log File and Recover
Error when you try to start db after copying one logfile to another (a common mistake)

ORA-00341: log 1 of thread 1, wrong log # in header
ORA-00312: online log 1 thread 1: '/app/oracle/product/oradata/prod/redo01.log'

-- to recover
alter database clear unarchived logfile group 1;
alter database drop logfile group 1;

-- then recreate the log file
alter database add logfile group 1 ('/app/oracle/product/oradata/prod/redo01.log') SIZE 50M;

Using CROSSCHECK to Update the RMAN Repository

posted Mar 18, 2011, 9:29 AM by Sachchida Ojha   [ updated Mar 18, 2011, 9:30 AM ]

RMAN Crosscheck

posted Mar 18, 2011, 9:26 AM by Sachchida Ojha   [ updated Mar 18, 2011, 9:28 AM ]

Check whether backup pieces, proxy copies, or disk copies still exist in comparison to the control file's repository.

Syntax:

  CROSSCHECK
    {
      { BACKUP [OF listObjList]
      | COPY [OF listObjList]
      | archivelogRecordSpecifier
      } [maintQualifier [maintQualifier]...]
    | recordSpec [DEVICE TYPE deviceSpecifier [, deviceSpecifier]...]
    };

   listObjList::=
      [ DATAFILE datafileSpec [, datafileSpec]...
      | TABLESPACE ['] tablespace_name ['] [, ['] tablespace_name [']]...
      | archivelogRecordSpecifier
      | DATABASE [SKIP TABLESPACE ['] tablespace_name [']
           [, ['] tablespace_name [']]...]
      | CONTROLFILE
      | SPFILE
      ]...

   recordSpec::=
      { { BACKUPPIECE | PROXY }
        { 'media_handle' [, 'media_handle']...
        | primary_key [, primary_key]...
        | TAG [=] ['] tag_name [']
        }
      | BACKUPSET primary_key [, primary_key]...
      | { CONTROLFILECOPY | DATAFILECOPY }
        { { primary_key [, primary_key]...
          | 'filename' [, 'filename']...
          }
        | TAG [=] ['] tag_name ['] [, ['] tag_name [']]...
        }
      | ARCHIVELOG
        { primary_key [, primary_key]...
        | 'filename' [, 'filename']...
        }
      }

Use RMAN Crosscheck to check that files are in place ready for a restore.
To validate a restore has worked, use the RMAN restore validate command.

Examples:

Assuming you have configured automatic channels:

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

Query the media manager for the status of backup sets in a given date range:

RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt;
RMAN> CROSSCHECK BACKUP DEVICE TYPE sbt COMPLETED BETWEEN '01-AUG-04' AND '31-DEC-04';

"Wealth heaped on wealth, nor truth nor safety buys,
The dangers gather as the treasures rise" - Samuel Johnson: The Vanity Of Human Wishes

Related Commands:

RMAN Restore Validate
RMAN CHANGE - Update the status of a backup in the RMAN repository.
RMAN CONFIGURE - Persistent RMAN settings.
RMAN DELETE - Delete backups from disk or tape
RMAN LIST - List backups and copies
RMAN REPORT - Report backup status: database, files, backups
NLS_DATE_FORMAT parameter

RMAN Quick References

posted Mar 18, 2011, 9:24 AM by Sachchida Ojha   [ updated Apr 20, 2011, 6:34 AM ]

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.

1-8 of 8