Buffer Cache Management

MANAGING BUFFER CACHE

Sizing the Buffer Cache
• Describe how the buffer cache is used by different Oracle processes
• List the tuning issues related to the buffer cache
• Monitor the use of buffer cache, and the different pools within buffer cache
• Implement dynamic SGA allocation
• Set the DB_CACHE_ADVICE parameter
• Create and size multiple buffer pools
• Detect and resolve free list contention

Buffer Cache Characteristics
The buffer cache holds copies of the data blocks from the data files. Because the buffer cache is a part of the SGA, these blocks can be shared by all users. The server processes read data from the data files into the buffer cache.

The buffer caches can be individually sized with the following parameters:
• DB_CACHE_SIZE specifies the size of default buffer pool in bytes.
• DBA_KEEP_CACHE_SIZE specifies the size of keep buffer pool in bytes.
• DBA_RECYCLE_CACHE_SIZE specifies the size of recycle buffer pool in bytes.

The buffer pools can be resized dynamically.

The blocks in the buffer cache are managed using two lists:
– The least recently used (LRU) list is used to keep the most recently accessed
blocks in memory. The blocks on the list are organized from the most recently
used (MRU) to the least recently used.

– The dirty list points to blocks in the buffer cache that have been modified but not
written to disk.

• Blocks in the buffer cache can be in one of three states:
– Free buffers are blocks that have the same image on disk and in memory. These
blocks are available for reuse.
– Dirty blocks are blocks with a different image in memory than the image on disk.
These blocks must be written to disk before they can be reused.
– Pinned buffers are memory blocks that are currently being accessed.

Dynamic SGA

The dynamic SGA feature implements an infrastructure to allow the server to change its SGA configuration without shutting down the instance.
• With a dynamic SGA, the Oracle server can modify its physical address space use to respond to the operating system’s use of physical memory.
• A dynamic SGA provides an SGA that will grow and shrink in response to a DBA command.




GRANULES
In the dynamic SGA model, a new unit of allocation called a granule has been created.
• SGA memory is tracked in granules by SGA components.
• Use V$BUFFER_POOL to monitor granule allocation and de-allocation in the buffer cache.
The SGA components are allocated and deallocated in units of contiguous memory called granules.
• A granule is a unit of contiguous virtual memory allocation.
• The size of a granule depends on the estimated total SGA:
– - 4 MB if estimated SGA size is less than 128 MB
– – 16 MB otherwise

Allocating Granules at Startup
• At instance startup, the Oracle server allocates granule entries, one for each granule to support SGA_MAX_SIZE bytes of address space.
• As startup continues, each component acquires as many granules as it requires.
• The minimum SGA configuration is three granules:
– One granule for fixed SGA (includes redo buffers)
– One granule for the buffer cache
– One granule for the shared pool

Adding Granules to Components
• A DBA can dynamically increase memory allocation to a component by issuing an ALTER SYSTEM command.
• Increase of the memory use of a component succeeds only if there are enough free granules to satisfy the request.
• Memory granules are not freed automatically from another component in order to satisfy the increase.

SQL> show parameter db_cache_size;
NAME TYPE VALUE
------------------------------- ----------- ------------------
db_cache_size big integer 4194304

SQL> alter system set db_cache_size=8M;
System altered.

SQL> show parameter db_cache_size;
NAME TYPE VALUE
------------------------------- ----------- ------------------
db_cache_size big integer 8388608


Example: Increasing the Size of an SGA Component
• Initial parameter values:
– SGA_MAX_SIZE = 128M, DB_CACHE_SIZE = 96M
– SHARED_POOL_SIZE = 32M

• ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
Error message indicating insufficient memory

• ALTER SYSTEM SET DB_CACHE_SIZE = 64M;

• ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
Error message indicating insufficient memory. Check scoreboard to
see if shrink has completed.

• ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
The statement is now processed.

Deprecated Buffer Cache Parameters
• Three parameters have been deprecated and will be maintained for backward compatibility.
– DB_BLOCK_BUFFERS
– BUFFER_POOL_KEEP
– BUFFER_POOL_RECYCLE
• These parameters cannot be combined with the dynamic size parameters.
Deprecated Buffer Cache Parameters

Dynamic Buffer Cache Advisory Parameter
• The buffer cache advisory feature enables and disables statistics gathering for predicting behavior with different cache sizes.
• The information provided by these statistics can help DBAs size the buffer cache optimally for a given workload.
• The buffer cache advisory is enabled by means of the DB_CACHE_ADVICE initialization parameter:
– This parameter is dynamic, and can be changed using ALTER SYSTEM.
– Three values are allowed: OFF, ON, and READY.

OFF: Advisory is turned off and the memory for the advisory is not allocated
• READY: Advisory is turned off, but the memory for the advisory remains allocated.
• ON: Advisory is turned on and both CPU and memory overhead is incurred.

Dynamic Views to Support Buffer Cache Advisory

• Buffer cache advisory information is collected in the V$DB_CACHE_ADVICE view.
• The view contains different rows that predict the estimated number of physical reads for different cache sizes.
• The rows also compute a physical read factor, which is the ratio of the number of estimated reads to the number of reads actually performed during the measurement interval by the real buffer cache.

Using V$DB_CACHE_ADVICE
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor,
estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT‘
AND block_size = (
SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
AND advice_status = 'ON';

Using V$DB_CACHE_ADVICE View
The following output shows that if the cache was 212MB, rather than the current size of
304MB, the estimated number of physical reads would be 17 million (17,850,847). Increasing the cache size beyond its current size would not provide a significant benefit.
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
(10%) 30 3,802 18.70 192,317,943
60 7,604 12.83 131,949,536
91 11,406 7.38 75,865,861
121 15,208 4.97 51,111,658
152 19,010 3.64 37,460,786
182 22,812 2.50 25,668,196
212 26,614 1.74 17,850,847
243 30,416 1.33 13,720,149
273 34,218 1.13 11,583,180
(Current) 304 38,020 1.00 10,282,475
334 41,822 .93 9,515,878
364 45,624 .87 8,909,026
395 49,426 .83 8,495,039
424 53,228 .79 8,116,496
(150%) 456 57,030 .76 7,824,764

Tuning Goals
Because physical I/O takes significant time and increases CPU demand, Oracle server
performance is improved when the servers find most of the blocks that they need in memory.
The statistic that measures the performance of the database buffer cache is the cache hit ratio.

Diagnostic Measures
To effectively monitor the usage of the buffer cache, you can use the following mechanisms:
• Measure the cache hit ratio: Use the V$SYSSTAT view, the utlbstat.sql and utlestat.sql scripts, or the Stats Pack utility (available in Oracle8i and later versions)
• Use the V$DB_CACHE_ADVICE view (available in Oracle9i)

Tuning Techniques
The DBA monitors the buffer cache by calculating the cache hit ratio from statistics collected by the Oracle server.

To improve the cache hit ratio, the DBA can:
• Increase the size of buffer cache
• Use multiple buffer pools to separate blocks by access characteristics
• Configure the tables to be cached in memory
• Configure to bypass the buffer cache for sorting and parallel reads if possible.

Diagnostic Tools
Description of the Views
• The V$SYSSTAT and V$SESSTAT views contain the statistics used to calculate the
cache hit ratio:
SQL> SELECT name, value FROM v$sysstat
2> WHERE
name in (‘session logical reads‘,
3> ‘physical reads`,
4> ‘physical reads direct’
5> ‘physical reads direct (lob)’);
• V$BUFFER_POOL: Describes multiple buffer pools, and V$BUFFER_POOL
STATISTICS shows information on individual pool. You can also use monitor buffer
pools with the query:

SQL> SELECT name, physical_reads, db_block_gets, consistent_gets FROM v$buffer_pool_statistics;

• V$BH: Describes blocks held in the buffer cache

From the STATSPACK report:
Statistic Total Per Per
Trans Logon Second
---------------------- ------ --------- ---------
physical reads 15,238 13.0 15,238.0
physical reads direct 863 0.7 863.0
Physical reads direct(lob) 0 0 0
session logical reads 119,376 101.8 119,376.0

Measuring the Cache Hit Ratio
The server collects statistics on data access and stores them in the dynamic performance table V$SYSSTAT. You measure the cache hit ratio using the following system statistics:
• physical reads: Number of blocks read from disk
• physical reads direct: Number of direct reads, does not require the cache
• physical reads direct (lob): Number of direct reads of large binary objects
• session logical reads: Number of logical read requests
Calculate the hit ratio for the buffer cache with this formula:
Hit Ratio = 1 – (physical reads – physical reads direct - physical reads direct (lob) ) / session logical reads
Session logical reads gives the total number of read requests for data. This value includes requests satisfied by access to buffers in memory and requests that cause a physical I/O. You can multiply the ratio by 100 to convert it to a percentage.
Because these statistics are collected since the instance startup time, query them during
normal working loads but not immediately after startup. Because the buffer cache is empty when the instance starts, there are more physical reads after startup.

SQL> SELECT 1 - (phy.value – lob.value – dir.value) / ses.value "CACHE HIT RATIO"
FROM v$sysstat ses, v$sysstat lob, v$sysstat dir, v$sysstat phy WHERE ses.name = 'session logical reads' AND dir.name = ‘physical reads direct' AND lob.name = 'physical reads direct (lob)' AND phy.name = 'physical reads';

Guidelines for Using the Cache Hit Ratio
Hit ratio is affected by data access methods:
• Full table scans
• Data or application design
• Large table with random access
• Uneven distribution of cache hits

Increase the cache size ratio under the following conditions:
• The cache hit ratio is less than 0.9 on OLTP system.
• There is no undue page faulting.
• If the previous increase was effective.

Increasing the Cache Hit Ratio by Reducing Buffer Cache Misses
If your hit ratio is low, you may want to increase the number of buffers in the cache to
improve performance.

To make the buffer cache larger:
• Allocate more memory to the buffer cache by decreasing the size of any other component of SGA with unused memory.
• If there is room for SGA to grow (that is, if SGA_MAX_SIZE is not reached), use
ALTER SYSTEM to increase the value of DB_CACHE_SIZE,
(DB_KEEP_CACHE_SIZE, or DB_RECYCLE_CACHE_SIZE)
• Increase the value of the DB_BLOCK_BUFFERS initialization parameter for oracle8i
databases. This parameter is static.

Removing Unnecessary Buffers when Cache Hit Ratio Is High
If your hit ratio is high, your cache is probably large enough to hold your most frequently
accessed data. In this case, you can reduce the cache size and still maintain good
performance.

Evaluating the Cache Hit Ratio
• Do not continue increasing DB_BLOCK_BUFFERS if the last increase made no significant difference in the cache hit ratio. This may be because of the way that you are
accessing your data, or there may be other operations that do not even use the buffer
pool.
For example, the Oracle server bypasses the buffer cache for sorting and parallel
reads.

• Also, when looking at the cache hit ratio, bear in mind that blocks encountered during a
full table scan are not put to the head of the LRU list; therefore, repeated scanning does
not cause the blocks to be cached.

• The solution lies at the design or implementation level, in that repeated scanning of the
same large table is rarely the most efficient solution to the problem. It may be better to
perform all of the processing in a single pass or add appropriate indexes to the table.
• In large databases running an OLTP application, most rows are accessed either one or
zero times in any given unit of time. On this basis there is little point in keeping the row
(or the block that contains it) in memory for very long after its use.
• Finally, the relationship between cache hit ratio and number of buffers is far from a
smooth distribution. When tuning the buffer pool, avoid the use of additional buffers
that contribute little or nothing to the cache hit ratio.

Increasing DB_BLOCK_BUFFERS
As a general rule, increase DB_BLOCK_BUFFERS under the following conditions:
• The cache hit ratio is less than 90%.
• There is adequate memory for other processes, as measured by the amount of page
faults.
• The previous increase of DB_BLOCK_BUFFERS was effective.

Multiple Buffer Pools
The DBA may be able to improve the performance of the database buffer cache by creating multiple buffer pools. Objects are assigned to a buffer pool depending on how the objects are accessed. There are three buffer pools:
• KEEP: This pool is used to retain objects in memory that are likely to be reused.
Keeping these objects in memory reduces I/O operations.
• RECYCLE: This pool is used to eliminate blocks from memory that have little chance
of being reused. Flushing these blocks from memory enables you to allocate the space
that would be used by their cache buffers to other objects.
• DEFAULT: The pool always exists. It is equivalent to the single buffer cache.

Individual pools have their own size defined by
DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE, and DB_RECYCLE_CACHE_SIZE.
– These parameters are dynamic.
– Latches are automatically allocated by Oracle RDBMS.

Example : Enabling Multiple Buffer Pools
SQL>CREATE INDEX cust_idx …
STORAGE (BUFFER_POOL KEEP …);

SQL>ALTER TABLE customer
STORAGE (BUFFER_POOL RECYCLE);

SQL> ALTER INDEX cust_name_idx
STORAGE (BUFFER_POOL KEEP);

KEEP Buffer Pool Guidelines
• Tuning goal: Keeping blocks in memory
• Size: Holds all or nearly all blocks
• Tool: ANALYZE ... ESTIMATE STATISTICS

SQL> ANALYZE TABLE hr.countries ESTIMATE STATISTICS;
SQL> SELECT table_name, blocks FROM dba_tables WHERE owner = ’HR’
4 AND table_name = ’COUNTRIES’;
TABLE_NAME BLOCKS
---------- ----------
COUNTRIES 14


RECYCLE Buffer Pool Guidelines
• Tuning goal: Eliminating blocks from memory
when transactions are completed
• Size: Holds only active blocks
• Tool: V$CACHE
...
SQL> SELECT owner#, name, count(*) blocks FROM v$cache
GROUP BY owner#, name;


OWNER# NAME BLOCKS
------ ---------- ----------
5 CUSTOMER 147
...

Using V$CACHE to Find Blocks in the Buffer Pool
The DBA can also monitor the number of buffer pool blocks by object using V$CACHE.
V$CACHE is created by the catparr.sql script.

To determine the number of blocks required for objects in the RECYCLE pool:
• Tune the buffer cache with the RECYCLE pool disabled.
• Run catparr.sql to set up and populate V$CACHE.
• During peak running times, use the following query to calculate how many blocks are
used by each object:
SQL> SELECT owner#, name, count(*) blocks FROM v$cache GROUP BY owner#, name;
• Sum the blocks for all objects that will be used in the RECYCLE buffer pool and divide
by four to get RECYCLE pool size. You divide by four because it is assumed that onefourth
of the blocks targeted for the RECYCLE pool are active; the other three-fourths
are waiting to be aged out of the cache.

Tool: V$SESS_IO
SQL> SELECT s.username, io.block_gets, io.consistent_gets, io.physical_reads
FROM v$sess_io io, v$session s WHERE io.sid = s.sid ;

BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS
---------- --------------- --------------
2187 23271 1344

Example : Calculating the Hit Ratio for Multiple Pools

SQL> SELECT name, 1 - (physical_reads / (db_block_gets + consistent_gets)) "HIT_RATIO" FROM sys.v$buffer_pool_statistics WHERE db_block_gets + consistent_gets > 0;

NAME HIT_RATIO
------------------ ----------
KEEP .983520845
RECYCLE .503866235
DEFAULT . 790350047

SQL> SELECT * FROM v$buffer_pool;
ID NAME LO_SETID HI_SETID SET_COUNT BUFFERS LO_BNUM HI_BNUM
-- ------- -------- -------- --------- ------- ------- -------
1 KEEP 3 3 1 14000 0 13999
2 RECYCLE 4 6 3 2000 14000 15999
3 DEFAULT 1 2 2 4000 16000 19999



Dictionary Views
These dictionary views have a BUFFER_POOL column that indicates the default buffer pool for the given object:
• USER_SEGMENTS, DBA_SEGMENTS
• USER_CLUSTERS, ALL_CLUSTERS, DBA_CLUSTERS
• USER_INDEXES, ALL_INDEXES, DBA_INDEXES
• USER_TABLES, ALL_TABLES, DBA_TABLES
• USER_OBJECT_TABLES, ALL_OBJECT_TABLES, DBA_OBJECT_TABLES
• USER_ALL_TABLES, ALL_ALL_TABLES, DBA_ALL_TABLES

The V$BUFFER_POOL view describes the buffer pools allocated. The columns from
V$BUFFER_POOL show:
• The number and range of LRU latches allocated to the buffer (sets)
• The number and range of blocks allocated to the buffer

SQL> SELECT * FROM v$buffer_pool;
ID NAME LO_SETID HI_SETID SET_COUNT BUFFERS LO_BNUM HI_BNUM
-- ------- -------- -------- --------- ------- ------- -------
1 KEEP 3 3 1 14000 0 13999
2 RECYCLE 4 6 3 2000 14000 15999
3 DEFAULT 1 2 2 4000 16000 19999

Caching Tables
• Enable caching during full table scans by:
– Creating the table with the CACHE clause
– Altering the table with the CACHE clause
– Using the CACHE hint in a query
• Guideline: Do not overcrowd the cache.

Performance Indicators FOR Buffer Cache
The buffer cache hit ratio is by far the most useful measure of buffer cache performance.
However, there are some other indicators.

Wait Statistics
You should consider increasing the buffer cache size if there are high or increasing values for the Free Buffer Inspected system statistic. This statistic is the number of buffers skipped to find a free buffer. Buffers are skipped because they are dirty or pinned.

Wait Events
You can find out whether there have been waits for buffers from V$SYSTEM_EVENT or
V$SESSION_WAIT. If there are no waits, the event has not yet occurred. There are three main events to look out for:

a) Buffer Busy Waits
• This wait indicates that there are some buffers in the buffer cache that multiple
processes are attempting to access concurrently. Query V$WAITSTAT for the wait
statistics for each class of buffer.



Common buffer classes that have buffer busy waits include
1) data block
2) segment header
3) undo header
4)undo block.

1. data block - if the contention is on tables or indexes (not the segment header):
• Check for SQL statements using unselective indexes.
• Check for right-hand-indexes (that is, indexes that are inserted at the same point by many processes; for example, those which use sequence number generators for the key values).
• Consider using automatic segment-space management, or increasing free lists to avoid multiple processes attempting to insert into the same block

2. undo header
Displays contention on rollback segment header: If you are not using automatic
undo management, then add more rollback segments.
3.undo block
Displays contention on rollback segment block: If you are not using automatic undo management, consider making rollback segment sizes larger.

b) Free Buffer Inspected
This is a measure of how many buffers on the LRU list are inspected by a process looking for a free buffer (writing a new block) before triggering DB writer to flush the dirty buffers to disk.

c) Free Buffer Waits
This wait event indicates that a server process was unable to find a free buffer and has
posted the database writer to make free buffers by writing out dirty buffers. A dirty buffer is a buffer whose contents have been modified. Dirty buffers are freed for reuse when DBWR has written the blocks to disk.
In order to resolve the contention, DBWR has to make blocks available faster for overwriting. To achieve this, examine ways of speeding up the write process. This event is also an indication that the buffer cache is too small. Examine the hit ratios for the buffer cache in order to determine if the cache should be resized.

Free Lists
• A free list for an object maintains a list of blocks that are available for inserts.
• The number of free lists for an object can be set dynamically.
• Single-CPU systems do not benefit greatly from multiple free lists.
• The tuning goal is to ensure that an object has sufficient free lists to minimize contention.
• Using Automatic Free Space Management eliminates the need for free lists, thus reducing contention on the database.

Diagnosing Free List Contention
Dynamic Performance Views
The V$SESSION_WAIT, V$WAITSTAT, and V$SYSTEM_EVENT dynamic performance
views are used to diagnose free list contention problems.
The DBA_SEGMENTS data dictionary view is used to identify the objects that need to be modified to increase the number of free lists.

Initialization Parameters
There are no initialization parameters to set for minimizing free list contention. The
FREELISTS keyword is used at the segment level. This value cannot be set dynamically; an object must be dropped and re-created in order to change it.

Modifying Free List
The number of free lists in a free list group can be changed by an alter table statement. Note that you cannot alter the free list storage parameter for segments in tablespaces with AUTO SEGMENT SPACE MANAGEMENT.

Diagnostic Criteria
You can query V$WAITSTAT and V$SYSTEM_EVENT to determine whether there is free list contention. If high numbers are returned, you must identify the object or objects.

• To query V$WAITSTAT:
SQL>SELECT class, count, time FROM v$waitstat WHERE class = ’segment header’;

• To query V$SYSTEM_EVENT:
SQL> SELECT event, total_waits FROM v$system_event WHERE event = ’buffer busy waits’;

To reduce buffer busy waits on:
• Data blocks: Change PCTFREE and/or PCTUSED; check for right-hand indexes
(indexes that are inserted into at the same point by many processes); increase
INITRANS; reduce the number of rows per block
• Segment headers: Use free lists or increase the number of free lists; use free list groups
(This can make a difference even in a single instance environment.)
• Free list blocks: Add more free lists (in the case of Oracle Parallel Server, making sure
that each instance has its own free list group)

Resolving Free List Contention
• Query V$SESSION_WAIT.
• Identify the object and get free lists for the segment from DBA_SEGMENTS.

• Either:
– Change the object in question, or
– Move into an auto-managed tablespace.

Example : Identifying the Objects with Free list contention
Determine the File, Block, and ID for which free list contention is occurring by querying
V$SESSION_WAIT.
• Identify the segment and determine the number of free lists that currently exist for the
segment identified by querying DBA_SEGMENTS:

SQL> SELECT s.segment_name, s.segment_type, s.freelists, w.wait_time, w.seconds_in_wait, w.state FROM dba_segments s, v$session_wait w
WHERE w.event ='buffer busy waits‘
AND w.p1 = s.header_file
AND w.p2 = s.header_block;

Use either:
– Change the object
To increase the number of free lists for the object, use the ALTER TABLE
command in Oracle9i, specifying the FREELISTS keyword.
or
– Move the object to an auto managed tablespace.

Example : Auto-management of Free Space

SQL> CREATE TABLESPACE BIT_SEG_TS
DATAFILE ’$HOME/ORADATA/u04/bit_seg01.dbf’ SIZE 1M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

SQL>CREATE TABLE BIT_SEG_TABLE (IDNUM NUMBER)
TABLESPACE BIT_SEG_TS;

• Create an auto-managed tablespace:
• Create a table that uses auto-management of free space:
Auto-Management of Free Space

Oracle Backups & Recovery

• Describe the Oracle processes, memory structures, and files relating to recovery
• Identify the importance of checkpoints, redo log files, and archived log files
• Describe ways to tune instance recovery


Components & their definitions.

Database buffer cache :
Memory area used to store blocks read from data files. Data is read into the blocks by server processes and written out by DBWn asynchronously.
Log buffer :
Memory containing before and after image copies of changed data to be written to the redo logs

Large pool
An optional area in the SGA that provides large memory allocations for backup and restore operations, I/O server processes, and session memory for the shared server and Oracle XA.

Shared pool :
Stores parsed versions of SQL statements, PL/SQL procedures, and data dictionary information

Database writer (DBWn)
Writes dirty buffers from the data buffer cache to the data files. This activity is asynchronous.

Log writer (LGWR)
Writes data from the redo log buffer to the redo log files System monitor

(SMON)
Performs automatic instance recovery. Recovers space in temporary segments when they are no longer in use. Merges contiguous areas of free space depending on parameters that are set.

Process monitor (PMON)
Cleans up the connection/server process dedicated to an abnormally terminated user process. Performs rollback and releases the resources held by the failed process.


Checkpoint (CKPT)
Synchronizes the headers of the data files and control files with the current redo log and checkpoint numbers.

Archiver (ARCn)
A process that automatically copies redo logs that have been marked for archiving.

Dynamic Views (Examples)
The Oracle server provides a number of standard views to obtain information on the database and instance. These views include:
• V$SGA: Queries the size of the instance for the shared pool, log buffer, data buffer cache, and fixed memory sizes (operating system-dependent)
• V$INSTANCE: Queries the status of the instance, such as the instance mode, instance
name, startup time, and host name
• V$PROCESS: Queries the background and server processes created for the instance
• V$BGPROCESS: Queries the background processes created for the instance
• V$DATABASE: Lists status and recovery information about the database. It includes
information on the database name, the unique database identifier, the creation date, the
control file creation date and time, the last database checkpoint, and other information.
• V$DATAFILE: Lists the location and names of the data files that are contained in the
database. It includes information relating to the file number and name, creation date, status (online or offline), enabled (read-only, read-write), last data file checkpoint, size, and other information.

Database Checkpoints
Database checkpoints ensure that all modified database buffers are written to the database files.
The database header files are then marked current, and the checkpoint sequence number is recorded in the control file. Checkpoints synchronize the buffer cache by writing all buffers to disk whose corresponding redo entries were part of the log file being checkpointed. Incremental checkpoints are continuous, low overhead checkpoints that write buffers as a background activity.

Checkpoint Process (CKPT) Features
• The CKPT process is always enabled.
• The CKPT process updates file headers at checkpoint completion.
• More frequent checkpoints reduce the time needed for recovering from instance failure at the possible expense of performance.



When Does Checkpointing Occur?
• At every log switch (cannot be suppressed)
• When fast-start checkpointing is set to force DBWn to write buffers in advance in order to shorten the instance recovery
• At a frequency defined by the LOG_CHECKPOINT_INTERVAL initialization parameter. It specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log.
• When the elapsed time since writing the redo block at the current checkpoint position exceeds the number of seconds specified by the LOG_CHECKPOINT_TIMEOUT initialization parameter. • At instance shutdown, unless the instance is aborted
• When forced by a database administrator (ALTER SYSTEM CHECKPOINT command)
• When a tablespace is taken offline or an online backup is started

Note: Read-only data files are an exception: Their checkpoint numbers are frozen and do not correspond with the number in the control file.

Checkpoint Synchronization
• At each checkpoint, the checkpoint number is updated in every database file header and in the control file.
• The checkpoint number acts as a synchronization marker for redo, control, and data files.
If they have the same checkpoint number, the database is considered to be in a consistent
state.
• Information in the control file is used to confirm that all files are at the same checkpoint
number during database startup. Any inconsistency between the checkpoint numbers in the various file headers results in a failure, and the database cannot be opened. Recovery is required.

Database Synchronization
An Oracle database cannot be opened unless all datafiles, redo logs, and control files are synchronized. In this case, recovery is required.

Database File Synchronization
• For the database to open, all datafiles must have the same checkpoint number, unless they are offline or part of a read-only tablespace.
• Synchronization of all Oracle files is based on the current redo log checkpoint and
sequence numbers.
• Archived and online redo log files recover committed transactions and roll back
uncommitted transactions to synchronize the database files.
• Archived and online redo log files are automatically requested by the Oracle server during the recovery phase. Make sure logs exist in the requested location.

Control File Function
The control file is a small binary file that describes the structure of the database.

Control File Contents
• Database name
• Time stamp of database creation
• Synchronization information (checkpoint and log sequence information) needed for
recovery
• Names and locations of datafiles and redo log files
• Archiving mode of the database
• Current log sequence number
• Recovery Manager backup meta data

Dynamic View (Example)
To obtain the location and names of the control files, use either the dynamic performance view V$PARAMETER or the dynamic performance view V$CONTROLFILE.
SQL> SELECT name FROM v$controlfile;
NAME
-----------------------
/ORADATA/u01/ctrl01.ctl
/ORADATA/u02/ctrl02.ctl
2 rows selected.

How to Multiplex the Control File
To add a new control file or change the number or location of the control file, follow these steps:
1. Shut down the database.
2. Make a copy of the existing control file to a different device by using operating system
commands.
3. Edit or add the CONTROL_FILES parameter and specify names for all the control files.
4. Start the database.


Archiving Requirements
The database must be in Archivelog mode. Issuing the command to put the database into Archivelog mode updates the control file. The ARCn background processes can be enabled to implement automatic archiving.
Sufficient resources should be available to hold generated archived redo log files.

Implications of Setting the Database in Archivelog Mode
• The database is protected from loss of data when media failure occurs.
• You can back up the database while it is online.
• When a tablespace other than SYSTEM goes offline as a result of media failure, the
remainder of the database remains available because tablespaces (other than SYSTEM)
can be recovered while the database is open.

Media Recovery Options
• You can restore a backup copy of the damaged files and use archived log files to bring
the datafiles up-to-date while the database is online or offline.
• You can recover the database to a specific point in time.
• You can recover the database to the end of a specified archived log file.
• You can recover the database to a specific system change number (SCN).

Changing the Archiving Mode (continued)
A user must have the ALTER SYSTEM privilege to alter the Archivelog mode of the
database.
Note: After the mode has been changed from Noarchivelog mode to Archivelog, you must
back up all the datafiles and the control file. Your previous backup is not usable anymore
because it was taken while the database was in Noarchivelog mode.
The new backup that is taken after putting the database into Archivelog mode is the back up
against which all your future archived redo log files will apply.
Setting the database in Archivelog mode does not enable the Archiver (ARCn) processes.



Automatic and Manual Archiving

The Archive Process
After a database is set in Archivelog mode, you must decide whether online redo log files are to be archived automatically or manually. This is the second step in creating archived redo log files to use for recovery.

Automatic Versus Manual Archiving
• In automatic archiving, the ARCn background processes are enabled and they copy redo log files as they are filled.
• In manual archiving, you must use SQL*Plus or Oracle Enterprise Manager to copy the
files.
• It is recommended that you enable automatic archiving of log files.

LOG_ARCHIVE_MAX_PROCESSES Parameter
Parallel Data Definition Language (DDL) and parallel Data Manipulation Language (DML) operations may generate a large number of redo log files. A single ARC0 process to archive these redo log files might not be able to keep up. Oracle starts additional processes as needed.
However if you wish to avoid the run time overhead of invoking the additional processes, you can specify the number of processes to be started at instance startup

You can specify up to ten ARCn processes by using the LOG_ARCHIVE_MAX_PROCESSES parameter.


Dynamic Number of ARCn Processes
During a period of heavy transaction load or activity, you can temporarily start additional
archive processes to eliminate archiving bottlenecks. After the transaction activity returns to a normal level, you can stop some of the ARCn processes.

Example
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;

The day after, if the instance is not shut down, you can issue the following SQL command to stop the additional archive process:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=2;


Enabling Automatic Archiving at Instance Startup
If the database is in Archivelog mode, then archiver processes can be started every time the database instance is started by setting the parameter:
LOG_ARCHIVE_START = boolean
where: boolean TRUE automatically starts n ARCn processes upon instance
startup, where n is determined by the value of LOG_ARCHIVE_MAX_PROCESSES.
FALSE inhibits ARCn from starting upon instance startup.

Example : Enabling Automatic Archiving
You can enable automatic archiving without shutting down the instance by using the ALTER SYSTEM command. The database should be in Archivelog mode.

1 Open the database:
SQL> ARCHIVE LOG LIST;
2 Enable the archiver processes (ARCn):
UNIX:
SQL> ALTER SYSTEM ARCHIVE LOG START
TO ‘/ORADATA/ARCHIVE1‘;
NT:
SQL> ALTER SYSTEM ARCHIVE LOG START
TO ‘c:\u04\Oracle\TEST\log‘;
3 The ARCn processes automatically archive log files as they are filled


Example : Disabling Automatic Archiving

You can always stop archive processes regardless of how you started them by using the
ALTER SYSTEM command in SQL*Plus or Oracle Enterprise Manager.
Note: Stopping ARCn processes does not set the database in Noarchivelog mode. When all groups of redo logs are used and not archived, the database will hang if it is in Archivelog mode.

1 Execute the command to stop the ARCn processes, if ARCn processes have been already enabled:
SQL> ALTER SYSTEM ARCHIVE LOG STOP;

2 Ensure that automatic archiving is not enabled upon instance startup by editing the init.ora file and setting the parameter:
LOG_ARCHIVE_START=FALSE

Example : Manual Archiving of Redo Log Files
If your database is in Archivelog mode and you have not enabled automatic archiving, you must manually archive online redo log files.

1 Execute the ALTER SYSTEM SQL command:
SQL> ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;
2 The server process for the user executing the command performs the archiving of the online redo log files.

Step Explanation
1 Execute the ALTER SYSTEM SQL command:
SQL> ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;
2 The server process for the user executing the command performs the archiving of the online redo log files.

LOG_ARCHIVE_FORMAT Parameter
The LOG_ARCHIVE_FORMAT is used to include the log sequence number and the thread number as part of the file name.

Specifying Multiple Archive Log Destinations
Use LOG_ARCHIVE_DEST_n to specify up to ten archival destinations which can be on a:
• Local disk
• Remote standby database
log_archive_dest_1 = "LOCATION=/archive1"
log_archive_dest_2 = "SERVICE=standby_db1"


LOG_ARCHIVE_DEST_n Options
• Set archive location as MANDATORY or OPTIONAL.
• Define time before retry in case of failures.

Examples :
Eg1
log_archive_dest_1="LOCATION=/archive
MANDATORY REOPEN"

Eg2.
log_archive_dest_2="SERVICE=standby_db1
MANDATORY REOPEN=600"

Eg3
log_archive_dest_3="LOCATION=/archive2
OPTIONAL"

REOPEN Attribute
• The REOPEN attribute defines whether archiving to a destination must be re-attempted
in case of failure. If a number is specified along with the keyword REOPEN, as in REOPEN=600, the archiver attempts to write to this destination after the specified number of seconds following a failure. The default is 300 seconds.

Specifying a Minimum Number of Local Destinations
• LOG_ARCHIVE_MIN_SUCCEED_DEST parameter
• An online redo log group can be reused only if:
– Archiving has been done to all mandatory locations
– The number of local locations archived is greater than or equal to the value of the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter
LOG_ARCHIVE_MIN_SUCCEED_DEST = 2


Controlling Archiving to a Destination
• An archival destination may be disabled by using the dynamic initialization parameter
LOG_ARCHIVE_DEST_STATE_n.

• Archiving to a destination can be enabled again.
LOG_ARCHIVE_DEST_STATE_2 = DEFER
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
ALTER SYSTEM SET log_archive_dest_state_3 = ENABLE
ALTER SYSTEM SET log_archive_dest_state

Example : Specifying LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_FORMAT = extension
where: extension should include the variables %s or %S for
log sequence number. The default value is operating system-specific.
LOG_ARCHIVE_FORMAT=arch%s.arc
Filename Options
• %s or %S: Includes the log sequence number as part of the filename.
• %t or %T: Includes the thread number as part of the filename.
• Using %S causes the value to be a fixed length padded to the left with zeros.


Dynamic Views (Examples)

You can view information about the archived log files by using the following views:
• V$ARCHIVED_LOG: Displays archived log information from the control file.
• V$ARCHIVE_DEST: For the current instance, describes all archive log destinations,
the current value, mode, and status.

1. SQL> SELECT destination, binding, target, status FROM v$archive_dest;
DESTINATION BINDING TARGET STATUS
---------------------- --------- ------- --------
/db1/oracle/DEMO/arch MANDATORY PRIMARY VALID
/db2/oracle/DEMO/arch OPTIONAL PRIMARY DEFERRED
standbyDEMO OPTIONAL STANDBY ERROR
OPTIONAL PRIMARY INACTIVE
OPTIONAL PRIMARY INACTIVE


Note: A status of INACTIVE indicates that this destination is not defined. A status of VALID indicates the destination is enabled and error-free.

2. To check for errors and the log sequence number at which the error occurred for each destination, use the following query:
SELECT destination,fail_sequence,error FROM v$archive_dest WHERE status=’ERROR’;

DESTINATION FAIL_SEQ ERROR
------------ -------- -------------------------------
standbyDEMO 2010 ORA-12154: TNS:could not
resolve service name
1 row selected.

• V$LOG_HISTORY: Contains log file information from the control file.
• V$DATABASE: Current state of archiving.
• V$ARCHIVE_PROCESSES: Provides information about the state of the various ARCH processes for the instance.



SQL>SELECT * FROM v$archive_processes;

PROCESS STATUS LOG_SEQUENCE STAT
------------- ---------- ------------- -----
0 ACTIVE 2014 BUSY
1 ACTIVE 0 IDLE
2 ACTIVE 0


One row for each of the 10 possible archiver processes is displayed. A status of ACTIVE
indicates that the process is up and running. A process that is currently archiving has a state of BUSY. The LOG_SEQUENCE column for a busy process shows the current log sequence number it is archiving.

The ARCHIVE LOG LIST command provides the DBA with information about the log
mode and status of archiving for the database:

SQL> ARCHIVE LOG LIST;
Database log mode Archive mode
Automatic archival Enabled
Archive destination /ORADATA/ARCHIVE1/
Oldest online log sequence 1304
Next log sequence to archive 1305
Current log sequence 1305


USER MANAGED BACKUP METHODS

The user managed backups can be classified under the following types

a) Whole database backup
– Target database may be open or closed
– Backup of all datafiles and the control file

b) Partial database backups
– Tablespace
– Datafile
– Control file

c) Consistent backups
d) Inconsistent backups

Whole database backup (also known as whole backup) refers to a backup of all datafiles and the control file of the database.

Consistent backup
The whole backup that is taken when the database is closed (after the database is shut down using the NORMAL, IMMEDIATE, or TRANSACTIONAL options) is called a consistent backup.



Inconsistent backup
When the database is shut down with the ABORT option this inconsistency persists. Backups of the database in such a state are termed as an inconsistent backup. Inconsistent backups need recovery to bring the database into a consistent state.

Tablespace Backup
A tablespace backup is a backup of the datafiles that make up a tablespace. Tablespace backups are valid only if the database is in Archivelog mode because redo entries will be required to make the datafiles consistent with the rest of the database. You can make tablespace backups when the tablespace is read-only or offline-normal in Noarchivelog mode.

Datafile Backups
You can make backups of a single datafile if your database is in Archivelog mode. You can make backups of read-only or offline-normal datafiles in Noarchivelog mode.

Control File Backups
You can configure RMAN for automatic backups of the control file after a BACKUP or COPY command is issued. The control file can also be backed up through SQL commands.

Querying Dynamic Views
Before you begin the backup, you should obtain information about the files of the database by querying the V$DATAFILE, V$CONTROLFILE, V$LOGFILE, and V$TABLESPACE views.

Examples
Use the V$DATAFILE view to obtain a listing of the names and status for all data files.
SQL> SELECT name, status FROM v$datafile;
NAME STATUS
------------------------------------------- ------
/databases/db01/ORADATA/u01/system01.dbf SYSTEM
/databases/db01/ORADATA/u02/undotbs.dbf ONLINE

Use the V$CONTROLFILE view to display the names of all control files.
SQL> SELECT name FROM v$controlfile;
NAME
--------------------------------------------
/databases/db01/ORADATA/u01/ctrl01.ctl
/databases/db01/ORADATA/u01/ctrl02.ctl

Use the V$LOGFILE view to display the names of all redo log files.
SQL> SELECT member FROM v$logfile;
MEMBER
----------------------------------------------
/databases/db01/ORADATA/u03/log01a.rdo
/databases/db01/ORADATA/u03/log02a.rdo
/databases/db01/ORADATA/u04/log01b.rdo

Consistent Whole Database Backup
A consistent whole database backup, also known as a closed database backup, is a backup that is taken of all the datafiles and control files that constitute an Oracle database while the database is closed. It can also include the online redo log files, parameter file, and the password file


Advantages of Making Consistent Whole Database Backups
• A consistent whole database backup is conceptually simple because all you need to do is:
– Shut down the database
– Copy all required files to the backup location
– Open the database

Disadvantages of Making Consistent Whole Database Backups
• For a 24x 7 business where the database must be continuously available, a consistent
whole database backup is unacceptable because the database is shutdown and unavailable during the backup.
• The amount of time that the database is unavailable is affected by the size of the database.
• The recovery point is only to the last full consistent whole database backup, and lost
transactions may have to be entered manually.

Performing a Consistent Whole Database Backup
Perform a consistent whole database backup while the Oracle server instance is shut down.
1. Compile an up-to-date listing of all relevant files to back up.
2. Shut down the Oracle instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL command.
3. Back up all datafiles and control files by using an operating system backup utility. You
can also include the redo log files although it is not required. You should also backup the
parameter file and the password file
4. Restart the Oracle instance.

Lab Exercise for a cold backup
Step1
SQL>SHUTDOWN IMMEDIATE;

Step2.
HOST cp /backup/

Step3
SQL>STARTUP OPEN;









Open Database Backup
If business requirements do not permit you to shut down the database to perform backups, then you can perform the following backups of the database while it is in use:
• Perform backups of all the tablespaces or individual datafiles while they are online or
offline.
• Back up the control file to a binary file or create a script to re-create the control file.
The online redo log files do not need to be backed up.

Advantages of Making an Open Database Backup
• The database is available for normal use during the backup.
• A backup can be done at a tablespace or datafile level.
• Supports businesses that operate all day every day.

Open Database Backup Requirements
You can perform backups of tablespaces or individual datafiles while the database is in use, provided two criteria are met:
• The database is set to Archivelog mode.
• You ensure that the online redo logs are archived, either by enabling the Oracle automatic archiving (ARCn) processes or by manually archiving the redo log files.

Example : How to Perform an Online Tablespace Backup
1. Set the datafile or tablespace in backup mode by issuing the ALTER TABLESPACE...BEGIN BACKUP command. This prevents the sequence number in the
datafile header from changing, so that logs are applied in recovery from backup start time.

Even if the datafile is in backup mode, it is available for normal transaction.
SQL> ALTER TABLESPACE users BEGIN BACKUP;

2. Use an operating system backup utility to copy all datafiles in the tablespace to backup storage. The log sequence numbers in the backup files may be different when each tablespace is backed up sequentially.
UNIX:
cp /ORADATA/u03/users01.dbf /BACKUP/users01.dbf
NT:
ocopy c:\users\disk1\user01.ora e:\users\backup\user01.ora

3. After the datafiles of the tablespace have been backed up, set them into normal mode by issuing the following command:
SQL> ALTER TABLESPACE users END BACKUP;

4. Archive the unarchived redo logs so that the redo required to recover the tablespace
backup is archived as follows:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Repeat these steps for all tablespaces, including SYSTEM and undo segment tablespaces.
Note : The time between the ALTER TABLESPACE BEGIN BACKUP and ALTER TABLESPACE END BACKUP commands should be minimized, because more redo
information is generated as a result of modified blocks being written to the redo log files. It is therefore recommended that you perform online backup of one tablespace at a time.

Example : Dynamic Views
You can obtain information about the status of datafiles while performing open database
backups by querying the V$BACKUP and V$DATAFILE_HEADER views.

V$BACKUP View
Query the V$BACKUP view to determine which files are in backup mode. When an ALTER TABLESPACE BEGIN BACKUP command is issued the status changes to ACTIVE.
SQL> SELECT * FROM v$backup;

FILE# STATUS CHANGE# TIME
------ ----------- ------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 ACTIVE 312905 05-APR-01

V$DATAFILE_HEADER View
Information about datafiles that are in backup mode can also be derived by querying the
V$DATAFILE_HEADER view. When an ALTER TABLESPACE BEGIN BACKUP command is issued, the value in the FUZZY column for the tablespace’s data files changes to YES to indicate that the corresponding files are in backup mode.

SQL> SELECT name, status, fuzzy FROM v$datafile_header;
NAME STATUS FUZ
-------------------------------- ------ ---
/…/u01/system01.dbf ONLINE
/…/u02/undotbs.dbf ONLINE
/…/u03/users01.dbf ONLINE YES
…
The value of the FUZZY column changes to NULL when the ALTER TABLESPACE END
BACKUP command is issued.

SQL> SELECT name, status, fuzzy FROM v$datafile_header;
NAME STATUS FUZ
-------------------------------- ------ ---
/…/u01/system01.dbf ONLINE
/…/u02/undotbs.dbf ONLINE
/…/u03/users01.dbf ONLINE
…

Failure During an Online Tablespace Backup
During an online tablespace backup, the system may crash, a power failure may occur, the database may be shut down, and so on. If any of these occurs:
• The backup files will be unusable if the operating system did not complete the backup.
You will need to back up the files again.

• The database files in online backup mode will not be synchronized with the database,
because the header is frozen when the backup starts.
• The database will not open because the Oracle server assumes that the files have been restored from a backup.

Example : Failure During an Online Tablespace Backup (continued)
If you are unsure whether a file needs to be recovered, or if it was left in online backup mode, query the V$BACKUP view:
SQL> SELECT * FROM v$backup;
FILE# STATUS CHANGE# TIME
------- ------------------ ------- --------
1 NOT ACTIVE 0
2 ACTIVE 228596 30-NOV-01
3 NOT ACTIVE 0
4 NOT ACTIVE 0
This output indicates that file number 2 is currently in online backup mode. To unfreeze the header, issue the command:

SQL> ALTER DATABASE datafile 2 END BACKUP;
Database altered.

Alternatively, with Oracle9i you can issue the following command:
SQL> ALTER DATABASE END BACKUP;
Database altered.
This command takes all of the datafiles that were in backup mode out of the mode
simultaneously.

You can then query V$BACKUP to check the status again as follows:
SQL> SELECT * FROM v$backup;
FILE# STATUS CHANGE# TIME
-------- ------------------ ------- -------
1 NOT ACTIVE 0
2 NOT ACTIVE 228596 30-NOV-01
...
Now you can open the database for users:
SQL> ALTER DATABASE OPEN;

Read-Only Tablespace Backup Issues
• Only one backup is needed after altering the tablespace to read-only.
• Resume a normal backup schedule for that tablespace after making it read-write.
• The control file must correctly identify the tablespace in read-only mode, otherwise you
must recover it.

Backing Up the Control File Manually
If you are not using RMAN for backups, you must manually back up the control file. You must protect against loss of the control file because information in the control file is required at instance startup time

Things to do about control file
• Multiplex the control files and name them in the init.ora file by using the CONTROL_FILES parameter.
• The ALTER DATABASE BACKUP CONTROLFILE TO TRACE command creates a
script to re-create the control file. The file is located in the directory specified in the
initialization parameter USER_DUMP_DEST. This script does not contain RMAN meta
data.
• In addition, the individual control files should also be backed-up by using the ALTER
DATABASE BACKUP CONTROLFILE to filename command. This provides a
binary copy of the control file at that time.
• During a full backup, shut down the instance normally and use an operating system backup utility to copy the control file to backup storage.

Lab Exercise on control file backups

Creating a binary image
ALTER DATABASE BACKUP CONTROLFILE TO ‘control1.bkp`;

Creating a text trace file
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Backing Up the Server Initialization Parameter File
You can use the CREATE PFILE statement to create a backup of the server parameter file. The contents of the server parameter file are exported to an initialization parameter file in text format.
The CREATE PFILE command can create the file in a default location or you can specify the file name as shown in the second example in the slide.

Example : Parameter file backups

SQL> CREATE PFILE FROM SPFILE;
SQL>CREATE PFILE = ‘/backup/init.ora’ FROM SPFILE;

Verifying Backups Using the DBVERIFY Utility
The DBVERIFY utility enables you to perform verification of datafiles by checking the
structural integrity of data blocks within specified datafiles. The utility is external to the
database in order to minimize the impact on database activities.

1 The utility can be used to verify online data files.
2 You can invoke the utility on a portion of a data file.
3 The utility can be used to verify offline data files.
4 You can direct the output of the utility to an error log.


%dbv file=/ORADATA/u03/users01.dbf logfile=dbv.log

Lab Exercise on DBverify
Example
To verify the integrity of the users01.dbf data file, starting with block 1 and ending with block 500, you execute the following command:
UNIX
$ dbv /ORADATA/u03/users01.dbf start=1 end=500


User Managed Recovery



Media Recovery
Media recovery is used to recover a lost or damaged current datafile or control file. You can also use it to recover changes that were lost when a datafile went offline without the
OFFLINE NORMAL option.

Restoring Files
When you restore a file, you are replacing a missing or damaged file with a backup copy.

Recovery of Files
When you recover a file, changes recorded in the redo log files are applied to the restored
files.

Recovery Steps
1. Damaged or missing files are restored from a backup.
2. Changes from the archived redo log files and online redo log files are applied as
necessary. Undo blocks are generated at this time. This is referred to as rolling forward
or cache recovery.
3. The database may now contain committed and uncommitted changes.
4. The undo blocks are used to roll back any uncommitted changes. This is known as
rolling back or transaction recovery.
5. The database is now in a recovered state.

Restoration and Datafile Media Recovery With User-Managed Procedures
When you restore a file, you use operating system commands to copy the file from a backup.
You can restore datafiles, control files, archived redo log files, and the server parameter file.
You use the SQL*Plus RECOVER command to apply redo log files to the restored files. You can perform automatic recovery or step through the log files to apply the changes.

Example : Recovery Scenario 1
Recovery When the Database is in Noarchivelog Mode

--There are two redo logs for a database.
– A closed database backup was taken at log sequence 144.
– While the database was at log sequence 145, data file 2 was lost.
• Result
Because log sequence 144 has not been overwritten, datafile number 2 can be restored
and recovered manually.

Note: You do not have to restore all Oracle files if no redo log file has been overwritten since the last backup

Example : Recovery Scenario 2
Disk 2 is damaged, losing datafile number 2. Only two online redo log files exist.
The last backup was taken at log sequence 144 and the current log sequence number is 146.
You cannot recover the datafile, because redo log 144 has been overwritten. This would be
confirmed if recovery was attempted. Therefore, you must shut down the database and restore
all Oracle files.
SQL> SHUTDOWN ABORT;
To restore files:
Unix: cp /BACKUP/* /databases/db01/ORADATA
NT: copy d:\disk1\backup\*.* d:\disk1\data\
When the copy is finished, restart the instance:
SQL> CONNECT / as sysdba;
SQL> STARTUP;
Notify users that they will need to reenter data from the time of the last backup.


Example : Recovery Without Redo Log File Backups
1. If the database is open, then shut down the database as follows:
SQL> SHUTDOWN IMMEDIATE

2. Restore the most recent whole database backup with operating system commands. You must restore all of the datafiles and control files, not just the damaged files. The
following example restores a whole database backup:
$ cp /db01/BACKUP/*.dbf /ORADATA/u*/* # restores datafiles
$ cp /db01/BACKUP/*.ctl /ORADATA/u*/* # restores control file

3. Because you did not back up the online redo logs, you cannot restore them with the
datafiles and control files. So that Oracle can reset the online redo logs, you must mimic
incomplete recovery as follows:
SQL> RECOVER DATABASE UNTIL CANCEL
SQL> CANCEL

4. Then open the database with the RESETLOGS option to reset the current redo log
sequence to 1 as follows:
SQL> ALTER DATABASE OPEN RESETLOGS;
------------------end of recovery -----------------------


Recovery in Archivelog Mode
Complete Recovery
– Uses redo data or incremental backups
– Updates the database to the most current point in
time
– Applies all redo changes

• Incomplete Recovery
– Uses backup and redo logs to produce a
noncurrent version of the database




Recovery in Archivelog Mode: Advantages and Disadvantages
Advantages
• Only need to restore lost or damaged files.
• No committed data is lost. Restoring the files, then applying archived and redo logs,
brings the database to the current point in time.
• The total recovery time is the length of time required to restore the files and apply all
archived and redo logs.
• Recovery can be performed while the database is open (except system tablespace files
and datafiles that contain online rollback segments).
Disadvantages
You must have all archived redo log files from the time of your last backup to the current
time. If you are missing one, you cannot perform a complete recovery, because all archived
redo log files must be applied in sequence; that is, archived log 144, then 145, then 146, and
so on.

Determining Which Files Need Recovery

Example : Identifying Datafiles That Need Recovery
To identify datafiles needing recovery, and from where recovery needs to start, use the
V$RECOVER_FILE view as follows:
SQL> SELECT * FROM v$recover_file;
FILE# ONLINE ERROR CHANGE# TIME
----- ------- ------ ------- ---------
2 OFFLINE 288772 02-MAR-01

Example : Locating Archived Log Files to Apply
To locate archived log files, view V$ARCHIVED_LOG for all archived log files or
V$RECOVERY_LOG for archived log files needed during recovery:
SQL> SELECT * FROM v$recovery_log;
THREAD# SEQUENCE# TIME ARCHIVE_NAME
------- --------- --------- -------
1 34 02-MAR-01 /…/ORADATA/ARCHIVE1/arch_34.arc
...
1 43 04-MAR-01 /…/ORADATA/ARCHIVE1/arch_43.arc
1 44 04-MAR-01 /…/ORADATA/ARCHIVE1/arch_44.arc
From the above information, archived logs from 34 on are required to recover datafile 2
completely.
Note: V$RECOVERY_LOG contains useful information only for the Oracle process doing the recovery V$ARCHIVED_LOG displays archived log information from the control file, including archive log names.

Using RECOVER Commands
One of the following commands can be issued to recover the database:
• RECOVER [AUTOMATIC] DATABASE
This command can only be used for a closed database recovery.
• RECOVER [AUTOMATIC] TABLESPACE
This command can only be used for an open database recovery.
• RECOVER [AUTOMATIC] DATAFILE
This command can be used for both an open and closed database recovery.
where: automatic automatically applies archived and redo log files.

Using Archived Redo Log Files During Recovery
During recovery, the Oracle server can manually or automatically apply the necessary
archived and online redo log files to reconstruct the data files. Before a redo log file is
applied, the Oracle server suggests the log file name to apply.
Restoring Archives to a Different Location
If archived redo log files are not restored to the LOG_ARCHIVE_DEST directory, then the Oracle server needs to be notified before or during recovery, by one of the following
methods:
• Specifying the location and name at the recover prompt:
Specify log: {=suggested filename AUTO CANCEL}

• Using the ALTER SYSTEM ARCHIVE command:
SQL> ALTER SYSTEM ARCHIVE LOG START TO ;

• Using the RECOVER FROM command:
SQL> RECOVER FROM ‘’ DATABASE;

How to Apply Redo Log Files Automatically
You can automatically apply redo log files in the following ways:
• Before starting media recovery, issue the SQL*Plus statement:
SQL> SET AUTORECOVERY ON
• Enter auto when prompted for a redo log file:
SQL> RECOVER datafile 4;
ORA-00279: change 308810...03/22/01 17:00:14 needed for
thread 1
ORA-00289: suggestion : /ORADATA/ARCHIVE1/arch_35.arc
ORA-00280: change 308810 for thread 1 is in sequence #35
Specify log: {=suggested filename AUTO CANCEL}
AUTO
Log applied.
...
• Use the AUTOMATIC option of the RECOVER command:
SQL> RECOVER AUTOMATIC datafile 4;
Media recovery complete.

User-Managed Procedures for Restoring Datafiles to a New Location
1. Use operating system commands to restore the file to the new location.
Note: In the UNIX environment, the files must exist in the new location prior to issuing
the ALTER DATABASE RENAME command. This is not the case in an NT
environment.
2. Start up the instance and mount the database.
3. Use the ALTER DATABASE command to update the control file with the new file
name or location:
SQL> ALTER DATABASE RENAME FILE
2> ‘/ORADATA/u03/users01.dbf‘
3> to ‘/ORADATA/u04/users01.dbf‘;
Closed Database Recovery Example
You have determined that u01 contains corrupt blocks. This is where datafile 1 is stored.
By querying V$DATAFILE and V$TABLESPACE views, you discover that datafile 1 is one of the files belonging to the system tablespace. Proceed as follows to recover the database:
1. If the instance is not already shut down, issue the SHUTDOWN command as follows:
SQL> SHUTDOWN ABORT;
2. Restore the file from backup (the most recent if available):
UNIX> host cp /BACKUP/system01.dbf /ORADATA/u01
NT > host copy c:\backup\df1.dbf d:\data\
3. Start the instance in Mount mode and recover the data file:
SQL> STARTUP MOUNT;
SQL> RECOVER DATABASE;
or SQL> RECOVER DATAFILE ‘/ORADATA/u01/system01.dbf‘;
ORA-00279: change 148448 ...03/29/01 17:04:20 needed for
thread
ORA-00289: suggestion : /ORADATA/ARCHIVE1/arch_144.arc
ORA-00280: change 148448 for thread 1 is in sequence #144
Log applied.
...
Media recovery complete.

To bring the data file to the point of failure, all needed archived logs and redo logs are
applied.
4. When recovery is finished, all data files are synchronized. Open the database.
SQL> ALTER DATABASE OPEN;
You can now notify users that the database is available for use, and tell them to reenter
any data that was not committed before system failure.
Note: During this method of recovery, the database must be closed; the entire database
is inaccessible to users during the recovery process.

Recovering an Open Database When It Is Initially Open
This method of recovery is generally used when:
• File corruption, accidental loss of file, or media failure has occurred, which has not
resulted in the database being shut down.
• The database is operational 24 hours a day, 7 days a week. Downtime for the database
must be kept to a minimum.
• Affected files do not belong to the system or rollback tablespaces.

Open Database Recovery Example
You have just determined that the media failure was due to a failed disk controller, which
contains only disk 2. From your familiarity with the database, you know datafile 2 is not a system or rollback segment datafile, nor will its unavailability prevent users from running their end-of-month reports.
1. Mount the database. It will not open because datafile 2 cannot be opened.
SQL> STARTUP MOUNT
Database mounted.
If you are not sure of the tablespace number to which the file belongs, issue the
following query:
SQL> SELECT d.file#, d.ts#, h.tablespace_name, d.name,
2> h.error
3> FROM v$datafile d, v$datafile_header h
4> WHERE d.file# = h.file#;

Open Database Recovery Example (continued)
FILE# TS# TABLES NAME Error
----- --- ------ ------------------------ -----
1 0 SYSTEM /disk1/data/system01.dbf
2 1 /disk2/data/df2.dbf FILE NOT FOUND
3 2 RBS /disk1/data/rbs01.dbf
...
2. If the datafile is not offline, the database will not open. Therefore, the file must be taken offline. You have queried V$DATAFILE and determined that the file is online. The
following command must be issued:

SQL> ALTER DATABASE datafile ‘/disk2/data/df2.dbf‘ offline;
Note: The ALTER TABLESPACE command cannot be used here because the database
is not yet open.
3. The database can now be opened so that users can access the system:
SQL> ALTER DATABASE OPEN;
4. Now restore the file. Because it cannot be restored to the damaged disk 2, restore it to
disk 3:
UNIX > host cp /disk1/backup/df2.dbf /disk3/data/
NT > host copy c:\backup\df2.dbf e:\data\

The Oracle server must now be informed of the new file location:
SQL> ALTER DATABASE rename file ‘/disk2/data/df2.dbf‘
2> to ‘/disk3/data/df2.dbf‘;
When the database is opened and tablespace recovery is required, issue the following
query to determine the name of the tablespace that owns the data file:
SQL> SELECT file_id f#, file_name,
2> tablespace_name tablespace, status
3> FROM dba_data_files;
F# FILE_NAME TABLESPACE STATUS
--- ------------------ --------- -------
1 /disk1/data/system_01.dbf SYSTEM AVAILABLE
2 /disk3/data/df2.dbf USER_DATA AVAILABLE
3 /disk1/data/rbs01.dbf RBS AVAILABLE

5. Use the RECOVER or ALTER DATABASE RECOVER command to start applying the
archived redo log files and online redo log files to the restored datafile.
SQL> RECOVER DATAFILE ‘/disk3/data/df2.dbf‘;
or SQL> RECOVER TABLESPACE user_data;
6. When recovery is finished, all datafiles are synchronized. Bring the datafile online:
SQL> ALTER DATABASE datafile ‘/disk3/data/df2.dbf‘ online;
or SQL> ALTER TABLESPACE user_data online;



RECOVERY MANAGER – RMAN
RMAN provides a flexible way to:
• Back up the database, tablespaces, datafiles, control files, and archive logs
• Store frequently executed backup and recovery operations
• Perform incremental block-level backup
• Skip unused blocks
• Specify limits for backups
. Detect corrupted blocks during backup
• Increase performance through:
– Automatic parallelization
– Generation of less redo
– Restricting I/O for backups
– Tape streaming

• You can detect block corruption. The information relating to the block corruption that
is detected during backup can be obtained by using the dynamic views
V$BACKUP_CORRUPTION and V$COPY_CORRUPTION.
• RMAN provides performance enhancements such as:
– Automatic parallelization of backup, restore, and recovery operations
– No generation of extra redo during online database backups
– Backups that are restricted to limit reads per file, per second to avoid interfering
with OLTP work
– Prevention of flooding of any one file with reads and writes while still keeping a
tape drive streaming, using multiplexing
• RMAN has a media management API to work seamlessly with third-party media
management tools interfacing with storage devices providing increased speed and
reliability.

Recovery Manager Components
Recovery Manager Executable The Recovery Manager command-line interface is
invoked through the executable RMAN. RMAN interprets user commands and appropriately invokes server sessions to perform the desired tasks.
Server Sessions The server processes (Unix) or services (Windows NT) invoked by
RMAN connect to the target database to perform the backup, restore, and recovery functions through a PL/SQL interface.
Target Database The database for which backup and recovery operations are being
performed using RMAN is called the target database. The control file of the target database contains information about its physical structure, such as the size and location of datafiles, online and archived redo log files, and control files. This information is used by the server sessions invoked by RMAN in backup and recovery operations.
RMAN Repository The data used by RMAN for backup, restore, and recovery operations is referred to as RMAN metadata. It is stored in the control file of the target database and in an optional recovery catalog database.
Although it is not mandatory to create a recovery catalog to use RMAN, it is beneficial to
use a recovery catalog. The recovery catalog should be located in a database different from the target database. The creation and maintenance of the recovery catalog is discussed in another lesson.

Channel To perform and record backup and recovery operations, RMAN requires a link to the target database. This link is referred to as a channel. You can allocate channels manually or preconfigure channels using automatic channel allocation.
Media Management Library The media management library (MML) is used by RMAN
when writing to or reading from tapes. The additional media management software required for using the tape medium is provided by media and storage system vendors.

Using the Control File as the Sole RMAN Repository
RMAN stores information about the target database and its backup and recovery operations
in the RMAN repository. The target database control file can be used as the exclusive
storage location for this information. The amount of information stored can increase
depending on the frequency of backups, the number of archived redo log files that are
generated, and the retention period for RMAN records.

Channel Allocation
A channel represents one stream of data to a device type. A channel must be allocated before
you execute backup and recovery commands. Each allocated channel establishes a
connection from the RMAN executable to a target or auxiliary database instance (either a
database created with the duplicate command or a temporary database used in TSPITR)
by starting a server session on the instance. This server session performs the backup and
recovery operations. Only one RMAN session communicates with the allocated server
sessions.
Each channel usually corresponds to one output device, unless your MML is capable of
hardware multiplexing.
You can allocate channels manually or preconfigure channels for use in all RMAN sessions
using automatic channel allocation.

Manual Channel Allocation
The ALLOCATE CHANNEL command with a RUN command and the ALLOCATE
CHANNEL FOR MAINTENANCE command issued at the RMAN prompt are used to
allocate a channel manually. Manual channel allocation overrides automatic allocation.

Automatic Channel Allocation
In Oracle9i, you can preconfigure channels for use in all RMAN sessions using automatic channel allocation.
RMAN provides a preconfigured DISK channel that you can use for backups and copies to disk.
In addition, you can configure a set of persistent, automatic channels.You specify automatic channels to disk or tape by using the CONFIGURE CHANNEL command.


Manual Channel Allocation
• BACKUP, COPY, RESTORE, and RECOVER commands require at least one channel.
• Allocating a channel starts a server process on the target database.
• Channels affect the degree of parallelism.
• Channels write to different media types.
• Channels can be used to impose limits.

Example : Using Configure Command

RMAN> RUN {
2> ALLOCATE CHANNEL c1 TYPE disk
3> FORMAT = ’/db01/BACKUP/usr0520.bak’;
4> BACKUP DATAFILE ’/db01/ORADATA/users01.dbf’;}

Change the default device type:
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;

Configure parallelism for automatic channels:
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 3;

Configure automatic channel options:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT = ‘/BACKUP/RMAN/%U';

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;

Media Management
To use tape storage for your database backups, RMAN requires a media manager. A media manager is a utility that loads, labels, and unloads sequential media, such as tape drives for the purpose of backing up, restoring, and recovering data.
that RMAN can communicate with it.Instructions for this procedure should be available in
the media manager vendor’s software documentation.
Depending on the product that you are installing, the following basic steps apply:
1. Install and configure the media management software on the target host or production
network. No RMAN integration is required at this stage.
2. Ensure that you can make non-RMAN backups of operating system files on the target
database host. This step makes later troubleshooting much easier. Refer to your media
management documentation to learn how to back up files to the media manager.
3. Obtain and install the third-party media management module for integration with the
Oracle server. This module must contain the library that Oracle loads when accessing
the media manager.
After you install the media management software, the media management library should
already be integrated with the Oracle server.

Backup and Restore Operations Using a Media Manager
The following Recovery Manager script performs a data file backup to a tape drive
controlled by a media manager:

run {
# Allocating a channel of type ’sbt_tape’ for serial device
ALLOCATE CHANNEL ch1 DEVICE TYPE ’sbt_tape’;
BACKUP DATAFILE 3;
}

Types of Database Connections with RMAN
With Recovery Manager you can connect to the following types of databases:
• Target database You are connected to the target database with the SYSDBA privilege.
You must have this privilege for the connection to succeed.
• Recovery catalog database This is an optional database which is configured for the
RMAN repository.
• Auxiliary database An auxiliary database is a database created using the RMAN
DUPLICATE command. Or it may be a temporary database used during tablespace
point-in-time recovery (TSPITR). A standby database is a copy of your production
database that can be used for disaster recovery.


Example : Connecting to the Target Database Without a Catalog

Local Connection
For a local RMAN connection, at an operating system prompt, enter the following:
UNIX:$ ORACLE_SID=DB01; export ORACLE_SID
$ rman target sys/change_on_install
NT: C:\> SET ORACLE_SID=DB01
C:\> rman target sys/change_on_install
Optionally, you can specify the keyword NOCATALOG as follows:
rman target sys/change_on_install nocatalog
NOCATALOG is the default mode.

Remote Connection
To connect from another server, use the net service name for the target database:
rman target sys/change_on_install@DB01


Recovery Manager Modes
Recovery Manager acts as a command-line interpreter (CLI) with its own command language.
There are two modes of operation with the RMAN— interactive and batch.
Example : Interactive Mode To run RMAN commands interactively, start RMAN and then type
commands into the command-line interface. For example, you can start RMAN from the
UNIX command shell and then execute interactive commands as follows:
$ rman target sys/sys_pwd@db1
RMAN> BACKUP DATABASE;
Batch Mode You can type RMAN commands into a file, and then run the command file by
specifying its name on the command line. The contents of the command file should be
identical to commands entered at the command line.
When running in batch mode, RMAN reads input from a command file and writes output
messages to a log file (if specified).



RMAN Commands
RMAN has two basic types of commands:
1. Stand-alone
2. Job commands.

Stand-alone commands are executed at the RMAN prompt and are generally self-contained.
Following are some of the stand-alone commands:
• CHANGE
• CONNECT
• CREATE CATALOG, RESYNC CATALOG
• CREATE SCRIPT, DELETE SCRIPT, REPLACE SCRIPT

Job Commands
The job commands are usually grouped and RMAN executes the job commands inside of a
RUN command block sequentially. If any command within the block fails, RMAN ceases
processing—no further commands within the block are executed.
There are some commands that can be issued either at the prompt or within RUN. Executing
stand-alone commands at the RMAN prompt allows you to take advantage of the automatic
channel functionality.

Some Command Examples
To Mount the Target Database Issue the startup command as follows:
RMAN> STARTUP MOUNT

To Shut Down the Target Database Issue the shutdown command as follows:
RMAN> SHUTDOWN IMMEDIATE

To List the Current Configuration of the Target Database
Use the REPORT command to obtain the configuration of the database as follows:
RMAN> REPORT SCHEMA;
RMAN-03022: compiling command: report
Report of database schema
File K-bytes Tablespace RB Name
---- -------- ---------- --- -----------------
1 117760 SYSTEM *** …/ORADATA/u01/system_01.dbf
2 30720 UNDO1 *** …/ORADATA/u02/undotbs.dbf
3 5120 USERS *** …/ORADATA/u04/users_01.dbf

The CONFIGURE Command
Using the Configure Command

a) Configure Automatic Channels
You can specify the default backup location and file naming convention with the
CONFIGURE CHANNEL command.
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
’/db01/BACKUP/%U’;

b) Configure Backup Retention Policies
You can use the CONFIGURE RETENTION POLICY command to create a persistent and
automatic backup retention policy. Based on the criteria that you specify in the CONFIGURE command, RMAN determines when backups and copies of datafiles and control files are obsolete; that is, when they are no longer needed for media recovery. You can issue the REPORT OBSOLETE command to view obsolete files and DELETE OBSOLETE to delete them. You can issue the CONFIGURE RETENTION POLICY CLEAR command to return the setting to the default value.

You can implement a retention policy in one of the following mutually exclusive ways:
• Specify a recovery window, which is a period of time that begins with the current time
and extends backward in time to the point of recoverability. In the example, the
CONFIGURE command ensures that for each datafile, one backup that is older than the
point of recoverability (7 days) must be retained.
• Specify a redundancy value, which indicates that any number of backups or copies
beyond a specified number need not be retained. The default value is 1 day.

Implement retention policy by specifying a recovery window:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 days;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/db01/BACKUP/%U’;

Implement retention policy by specifying redundancy:
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Configure Duplexed Backup Sets
You can create up to four copies of each backup piece in a backup set for all backup
commands that use automatic channels. This applies only for datafiles and archived redo log files.
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE disk TO 2;

Configure Backup Optimization
You set backup optimization on so that the BACKUP command does not back up files to a device type if the identical file has already been backed up to the device type. For two files to be identical, their content must be exactly the same. The default value for backup
optimization is OFF.

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

Use the CLEAR option to return to the default value:
RMAN> CONFIGURE RETENTION POLICY CLEAR;
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;


Example : SHOW command
The SHOW command is used to to display persistent configuration settings specified with the CONFIGURE command. These settings are configured for use with any RMAN session.
You can use the SHOW command to display the following:
• Automatic channel configuration settings
SHOW CHANNEL;
SHOW DEVICE TYPE;
SHOW DEFAULT DEVICE TYPE;
• RMAN retention policy configuration settings
SHOW RETENTION POLICY;
• Number of backup copies
SHOW DATAFILE BACKUP COPIES;
• Maximum size for backup sets
SHOW MAXSETSIZE;
• Tablespaces excluded from whole database backups
SHOW EXCLUDE;
• Status of backup optimization
SHOW BACKUP OPTIMIZATION;

The LIST Command
The LIST command is used to produce a detailed report listing all information for the
following:
• Backup sets that contain a backup of a specified list of data files
• Copies of a specified list of data files
• Backup sets that contain a backup of any data file that is a member of a specified list of
tablespaces
• Copies of any data file that is a member of a specified list of tablespaces
• Backup sets that contain a backup of any archived logs with a specified name or range
• Copies of any archived logs with a specified name or range
• Incarnations of a specified database

Example : List Command
List backups of all files in the database:
RMAN> LIST BACKUP OF DATABASE;

List all backup sets containing the users01.dbf datafile:
RMAN> LIST BACKUP OF DATAFILE
2> “/db01/ORADATA/u03/users01.dbf”;

List all copies of datafiles in the SYSTEM tablespace:
RMAN> LIST COPY OF TABLESPACE “SYSTEM”;

The REPORT Command
This command helps you analyze information in the RMAN repository in more detail.
Reports can be produced for a variety of questions, such as:

Example : Report Command
• What is the structure of the database?
RMAN> REPORT SCHEMA;
• Which files need to be backed up?
RMAN> REPORT NEED BACKUP ...;
• Which backups can be deleted (that is, are obsolete)?
RMAN> REPORT OBSOLETE;
• Which files are not recoverable because of unrecoverable operations?
RMAN> REPORT UNRECOVERABLE ...;


The REPORT NEED BACKUP Command
The REPORT NEED BACKUP command is used to identify all data files that need a
backup. The report assumes that the most recent backup would be used in the event of a
restore.
There are three options:
• Incremental: An integer specifies the maximum number of incremental backups that
should be restored during recovery. If this number, or more, is required, then the data
file needs a new full backup.
For example, to report files needing three or more incremental backups for recovery:
RMAN > REPORT NEED BACKUP incremental 3 database;
• Days: An integer specifies the maximum number of days since the last full or
incremental backup of a file. The file needs a backup if the most recent backup is equal
to or greater than this number.
For example, to report what system files have not been backed up for three days:
RMAN > REPORT NEED BACKUP days 3 tablespace system;
• Redundancy: An integer specifies the minimum level of redundancy considered
necessary. For example, redundancy level two requires a backup if there are not two or
more backups.

DBMS_RCVCAT and DBMS_RCVMAN
Two packages, DBMS_RCVCAT and DBMS_RCVMAN, are used by RMAN to perform its
tasks. These are internal, undocumented packages created by the CREATE CATALOG
command. DBMS_RCVMAN is created in the target database by the scripts dbmsrman.sql
and prvtrmns.plb which are called by catproc.sql.
DBMS_RCVCAT is used by Recovery Manager to maintain information in the recovery
catalog, and DBMS_RCVMAN queries the control file or recovery catalog.

RMAN Usage Considerations
Before Recovery Manager is used, consider the following points:
• Shared Resources on the System Most of RMAN’s work is performed through
Oracle server processes. The operations can also be performed in parallel to increase
throughput. This implies that the PROCESSES parameter must be sufficiently high.
From the OS standpoint, this means that shared memory and semaphores are
adequately set.
• Set of Users Performing Privileged Operations You must decide on the set of users
who perform privileged operations. Accordingly, you can set the users’ accounts with
the necessary privileges at the operating system and at the Oracle database.
To start up and shut down a database, the user should have the SYSDBA privilege.
Remote Operations You need to use a password file to connect to the target database
over Oracle Net to perform privileged operations, such as shutdown, startup, backup,
and recovery from a remote machine. You may have to set up a password file. You
should ensure that there is a strategy to backup the password file as well.
• Globalization Environment Variables Before invoking RMAN, set the
NLS_DATE_FORMAT and NLS_LANG environment variables. These variables
determine the format used for the time parameters in RMAN commands, such as
RESTORE, RECOVER, and REPORT.
• Use of the Recovery Catalog When you use a recovery catalog, RMAN can perform
a wider variety of automated backup and recovery functions. Use of the recovery
catalog involves storage space and maintenance efforts.
You should also decide whether to have a database dedicated to maintain the recovery
catalog of many target databases. Also consider the strategy to back up the recovery
catalog.
Example/Lab Sessions on RMAN fundamentals
Connect to your database as the target database in the default Nocatalog mode.
$rman
RMAN> connect target
Recovery Manager: Release 9.0.0.0.0 – Beta
(c) Copyright 2000 Oracle Corporation. All rights reserved.
connected to target database: DB01 (DBID=1125003950)
4. Use the RMAN REPORT command to generate a listing of your database structure.
RMAN> report schema;
using target database controlfile instead of recovery catalog
Report of database schema
Fi K-bytes Tablespace Datafile Name
-- ------- ---------- -----------------------------------
1 128000 SYSTEM /databases/db01/ORADATA/u01/system.dbf
2 30720 UNDOTBS /databases/db01/ORADATA/
u02/undotbs.dbf
3 5120 USERS /databases/db01/ORADATA/
u03/users01.dbf
4 5120 INDX /databases/db01/ORADATA/u03/indx01.dbf
5 81920 SAMPLE /databases/db01/ORADATA/
u02/sample01.dbf
6 1024 QUERY_DATA /databases/db01/ORADATA/
u01/querydata01f

2. Use the RMAN SHOW command to generate a listing of the RMAN configuration settings.
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK
TO ’%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
# default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO
1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
’/databases/oracle9i/dbs/snapcf_db01.f’;t
3. Use the RMAN CONFIGURE command to set the backup retention policy to a recovery window of 14 days.
RMAN> configure retention policy to recovery window of 14 days;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
new RMAN configuration parameters are successfully stored
4. Verify the setting for the backup retention policy.
RMAN> SHOW RETENTION POLICY;
RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
5. Set the backup retention policy back to the default value.
RMAN> CONFIGURE RETENTION POLICY CLEAR;
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
RMAN configuration parameters are successfully reset to
default value

RMAN Backup
Types of Recovery Manager Backups
Recovery Manager provides functionality to back up:
• The entire database, every datafile in a tablespace, or a single datafile
• The control file
• All or selected archived logs
Note: The online redo log files are not backed up when using Recovery Manager.

Closed Database Backups
A closed database backup is defined as a backup of the database while it is closed (offline). This is the same as the consistent database backup. If you are performing a closed backup, the target database must not be open. If you are using a recovery catalog, the recovery catalog database must be open.

Open Database Backups
An open database backup is defined as a backup of any portion of the database while it is open (online). Recovery Manager uses server processes to make copies of datafiles, control files, or archive logs. When using Recovery Manager, do not put tablespaces in backup mode using the ALTER TABLESPACE ... BEGIN BACKUP command. RMAN reads a block until a consistent read is obtained.

Recovery Manager Backups
You can make the following types of backups with Recovery Manager:
• Image copies are copies of a datafile, control file, or archived redo log file. A copy can
be made using Recovery Manager or an operating system utility. The image copy of a datafile consists of all the blocks of the datafile, including the unused blocks. The image copy can include only one file and a single operation of copy cannot be multiplexed.
• Backup sets can include one or more datafiles, the control file or archived redo log files.
The backup set can contain one or more files.
You can make a backup set in two distinct ways

– Full backup: In a full backup, you back up one or more files. In a full backup, all
blocks containing data for the files specified are backed up.
– Incremental backup: An incremental backup is a backup of datafiles that include
only the blocks that have changed since the last incremental backup. Incremental
backups require a base-level (or incremental level 0) backup, which backs up all
blocks containing data for the files specified. Incremental level 0 and full
backups copy all blocks in datafiles, but full backups cannot be used in an
incremental backup strategy.

Note: You can configure automatic control file backup so that the control file is backed up when you issue a BACKUP or COPY command.

Control Files in Datafile Backup Sets
Each file in a backup set must have the same Oracle block size (control files and datafiles
have the same block size, whereas archived log block sizes are machine dependent). When a control file is included, it is written in the last datafile backup set. A control file can be included in a backup set either:
• Explicitly using the INCLUDE CONTROL FILE syntax
• Implicitly by backing up file 1 (the system datafile)
The RMAN BACKUP command is used to back up datafiles, archived redo log files, and
control files. The BACKUP command backs up the files into one or more backup sets on disk or tape. You can make the backups when the database is open or closed. Backups can be full or incremental backups


Backup Piece
A logical backup set usually only has one backup piece. A backup piece is a single physical file that can contain one or more Oracle datafiles or archived logs.

Limiting the Backup piecesize
RMAN> RUN {
2> ALLOCATE CHANNEL t1 TYPE ’SBT_TAPE’
3> MAXPIECESIZE = 4G;
4> BACKUP
5> FORMAT ’df_%t_%s_%p’ FILESPERSET 3
6> (tablespace users); }


Backup File Format

%p Specifies the backup piece number within the backup set. This value starts at 1 for each backup set and is increased by 1 as each backup piece is created.
%s Specifies the backup set number. This number is a counter in the control file that is increased for each backup set.
%d Specifies the database name.
%t Specifies the backup set time stamp, which is a 4-byte value derived as the
number of seconds elapsed since a fixed reference time. The combination
of %s and %t can be used to form a unique name for the backup set.
%n Specifies the database name, padded on the right with x characters to a total
length of 8 characters.

Parallelization of Backup Sets
You can configure parallel backups by setting the PARALLELISM option of the
CONFIGURE command to greater than 1 or manually allocate multiple channels, RMAN
parallelizes its operation and writes multiple backup sets in parallel. The server sessions
divide the work of backing up the specified files.
Example
RMAN> run {
2> allocate channel c1 type sbt;
3> allocate channel c2 type sbt;
4> allocate channel c3 type sbt;
5> backup
6> incremental level = 0
7> format ’/disk1/backup/df_%d_%s_%p.bak‘
8> (datafile 1,4,5 channel c1 tag=DF1)
9> (datafile 2,3,9 channel c2 tag=DF2)
10> (datafile 6,7,8 channel c3 tag=DF3);
11> sql 'alter system archive log current';
}

RMAN Multiplexed Backup Sets
The technique of RMAN multiplexing is to simultaneously read files on disks and and then write them into the same backup piece. When more than one file is written to the same backup file or piece,
RMAN Multiplexed Backup Sets
The technique of RMAN multiplexing is to simultaneously read files on disks and then write them into the same backup piece. When more than one file is written to the same backup file or piece,


Duplexed Backup Sets
You can create up to four identical copies of each backup piece by duplexing the backup set.
This example shows how you can create 2 copies of the backup of datafile 2:
RMAN> BACKUP COPIES 2 DATAFILE 2
2> FORMAT ’/BACKUP1/%U’,’/BACKUP2/%U’;

RMAN places the first copy of each backup piece in /BACKUP1 and the second in
/BACKUP2. RMAN produces one backup set with a unique key and generates three identical copies of each backup piece in the set.

Backing up Backup Sets
You can back up a backup set as an additional way to manage your backups. You can use the RMAN BACKUP BACKUPSET command for disk-to-disk and disk-to-tape backups. This allows you to make an additional backup on tape or to move your backup from disk to tape.

Archived Redo Log File Backup Sets
A common problem experienced by DBAs is not knowing whether an archived log has been
completely copied out to the archive log destination before attempting to back it up.
Recovery Manager has access to control file or recovery catalog information, so it knows
which logs have been archived and can be restored during recovery.
You can back up archived redo log files with the BACKUP ARCHIVELOG command or
include them when backing up datafiles and control files with the BACKUP… PLUS
ARCHIVELOG command.
Characteristics of Archived Log Backup Sets
• Can include only archived logs, not datafiles or control files.
• Are always full backups. (There is no logic in performing incremental backups because
you can specify the range of archived logs to backup.)

Example
This example backs up all archived redo logs to a backup set, where each backup piece
contains three archived logs. After the archived logs are copied, they are deleted from disk and marked as deleted in the V$ARCHIVED_LOG view.

RMAN> BACKUP
2> FORMAT ’/disk1/backup/ar_%t_%s_%p’
3> ARCHIVELOG ALL DELETE ALL INPUT;

Datafile Backup Set Processing
Recovery Manager performs backup of datafiles in the following steps:
1. Memory buffers are allocated for each file in the set. Each buffer is sized by
(db_block_size*db_file_direct_io_count).
2. The files to be backed up are in descending order by their size in a channel.
3. The files in the set are checkpointed and each file header block is copied.
4. Each block is checked before inclusion in the backup as follows:
– If incremental, the SCN in the block is checked to see if it qualifies for inclusion.
– If full or level 0, the block is checked to see if it has ever contained data.
5. If corrupt blocks are found, this information is stored in the control file and can be
queried using V$BACKUP_CORRUPTION after backup completion.
6. The checksum is calculated.
7. When the output buffer is filled, it is sent to the output devi

Backup Constraints
When performing a backup using Recovery Manager, you must be aware of the following:
• The target database must be mounted for Recovery Manager to connect.
• Backups of online redo logs are not supported.
• If the target database is in Noarchivelog mode, only “clean” tablespace and datafile
backups can be taken (that is, backups of “offline normal” or “read only” tablespaces).
Database backups can be taken only if the database has first been shut down cleanly and
restarted in Mount mode.
• If the target database is in Archivelog mode, only “current” datafiles can be backed up
(restored datafiles are made current by recovery).
• If a recovery catalog is used, the recovery catalog database must be open.

Image Copies
An image copy contains a single datafile, archived redo log file, or control file. An image
copy can be created with the RMAN COPY command or an operating system command.
When you create the image copy with the RMAN COPY command, the server session
validates the blocks in the file and records the copy in the control file.

Characteristics of an Image Copy
An image copy has the following characteristics:
• An image copy can be written only to disk. Hence additional disk space may be
required to retain the copy on the disk. When large files are being considered, copying
may take a long time, but restoration time is reduced considerably because the copy is
available on the disk.
• If files are stored on disk, they can be used immediately (that is, they do not need to be
restored from other media). This provides a fast method for recovery using the SWITCH
command in Recovery Manager, which is equivalent to the ALTER DATABASE
RENAME FILE SQL statement.
• In an image copy all blocks are copied, whether they contain data or not, because an
Oracle server process copies the file and performs additional actions such as checking
for corrupt blocks and registering the copy in the control file. To speed up the process
of copying, you can use the NOCHECKSUM parameter.
• Image copy can be part of a full or incremental level 0 backup, because a file copy
always includes all blocks. Use the level 0 option if the copy will be used in
conjunction with an incremental backup set.
• Image copy can be designated as a level 0 backup in incremental backup strategy, but
no other levels are possible with image copy.

Example of Image copy with manual allocation of channel
RMAN> RUN {
2> ALLOCATE CHANNEL c1 type disk;
3> COPY
4> DATAFILE ’/ORADATA/users_01_db01.dbf’ to
5> ’/BACKUP/users01.dbf’ tag=DF3,
6> ARCHIVELOG ’arch_1060.arc’ to
7> ’arch_1060.bak’;}

Example : Automatic copy with manual allocation of channel
RMAN> COPY
2> DATAFILE ’/ORADATA/users_01_db01.dbf’ TO
3> ’/BACKUP/users01.dbf’ tag=DF3,
4> ARCHIVELOG ’arch_1060.arc’ TO
5> ’arch_1060.bak’;


How to Make an Image Copy of the Whole Database
To make an image copy of all the datafiles using Recovery Manager, follow this procedure:
1. Connect to RMAN and start up in mount mode:
RMAN> STARTUP MOUNT
2. Obtain a list of data files of the target database:
RMAN> REPORT SCHEMA;
3. Use the COPY command or script to create the copy of all datafiles listed above:
RMAN> COPY datafile 1 TO ’/BACKUP/df1.cpy’,
datafile 2 TO ’/BACKUP/df2.cpy ’,...;
4. Use the LIST COPY command to verify the copy:
RMAN> LIST COPY;
You can include the control file in the copy with the CURRENT CONTROLFILE command.
In addition, if CONFIGURE CONTROLFILE AUTOBACKUP is ON, RMAN automatically
backs up the control file after the COPY command is issued.

Image Copy Parallelization
By default, Recovery Manager executes each COPY command serially. However, you can
parallelize the copy operation by:
• Using the CONFIGURE DEVICE TYPE … PARALLELISM

Example :
RMAN> CONFIGURE DEVICE TYPE disk parallelism 4;
2> COPY # 3 files copied in parallel
3> datafile 1 TO ’/BACKUP/df1.dbf’,
4> datafile 2 TO ’/BACKUP/df2.dbf’,
5> datafile 3 TO ’/BACKUP/df3.dbf’;
RMAN> COPY # Second copy command
2> datafile 4 TO ’/BACKUP/df4.dbf’;

How to Perform a Multiplexed Backup in Noarchivelog Mode
1. Ensure that the destination directory where you want to store the backup is available
and has sufficient space.
2. Shut down the database cleanly using the NORMAL, IMMEDIATE, or
TRANSACTIONAL clause.
3. Mount the database.
4. If you are not using automatic channel allocation, allocate multiple channels and use a
format string to multiplex channels to different disks.
5. Run the BACKUP command. Because the database is in Noarchivelog mode, the
incremental backups are not applicable, so use the full backup option.
6. Verify that the backup is finished and cataloged.
7. Open the database for normal use.

Example
RMAN> BACKUP DATABASE FILESPERSET 3;

Control File Autobackups
If CONFIGURE CONTROLFILE AUTOBACKUP is ON, RMAN automatically performs a control file autobackup in these situations:
• After every BACKUP or COPY command issued at the RMAN prompt
• Whenever a BACKUP or COPY command within a RUN block is followed by a command that is neither BACKUP nor COPY
• At the end of every RUN block if the last command in the block was either BACKUP or
COPY RMAN automatically backs up the current control file using the default format of %F. You can change this format using the CONFIGURE CONTROLFILE AUTOBACKUP FORMAT and SET CONTROLFILE AUTOBACKUP FORMAT commands. The format string must include the %F substitution variable.

Example
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk
2> TO ’controlfile_%F’;

Tags for Backups and Image Copies
A tag is a meaningful name that you can assign to a backup set or image copy. The
advantages of user tags are as follows:
• Tags provide a useful reference to a collection of file copies or a backup set.
• Tags can be used in the LIST command to locate backed up files easily.
• Tags can be used in the RESTORE and SWITCH commands.
• The same tag can be used for multiple backup sets or file copies.
If a nonunique tag references more than one datafile, then Recovery Manager chooses the
most current available file.
Example
• Each month, a full backup of datafiles 1, 2, 3, and 4 is performed. The tag in the control
file for this backup is month_full_backup, even though the physical filename
generated is df_DB00_863_1.dbf.
• Each week, a full backup of datafiles 3 and 4 is performed. The tag name for this
backup is week_full_backup.
RMAN Dynamic Views
You can use the following views to obtain RMAN information stored in the control file:
• V$ARCHIVED_LOG shows which archives have been created, backed up, and cleared
in the database.
• V$BACKUP_CORRUPTION shows which blocks have been found corrupt during a
backup of a backup set.
• V$COPY_CORRUPTION shows which blocks have been found corrupt during an image
copy.
• V$BACKUP_DATAFILE is useful for creating equal-sized backup sets by determining
the number of blocks in each data file. It can also find the number of corrupt blocks for
the data file.
• V$BACKUP_REDOLOG shows archived logs stored in backup sets.
• V$BACKUP_SET shows backup sets that have been created.
• V$BACKUP_PIECE shows backup pieces created for backup sets.
Oracle9i DBA Fundamentals II 11-36
11-36 Copyright © Oracle Corporation, 2001. All rights reserved.

Monitoring RMAN Backups
• Correlate server sessions with channels with the SET COMMAND ID command.
• Query V$PROCESS and V$SESSION to determine which sessions correspond to which RMAN channels.
• Query V$SESSION_LONGOPS to monitor the progress of backups and copies.
• Use an operating system utility to monitor the process or threads.

How to Monitor the Copy Process
To correlate a process with a channel during a backup:
1. Start Recovery Manager and connect to the target database and, optionally, the recovery
catalog.
rman target / catalog rman/rman@rcat
2. Set the COMMAND ID parameter after allocating the channels and then copy the desired
object.
run {
allocate channel t1 type disk;
set command id to ’rman’;
copy datafile 1 to ’/u01/backup/df1.cpy’;
release channel t1;}

3. Query the V$SESSION_LONGOPS view to get the status of the copy.
SELECT sid, serial#, context, sofar, totalwork
round(sofar/totalwork*100,2) "% Complete",
FROM v$session_longops
WHERE opname LIKE ’RMAN:%’
AND opname NOT LIKE ’RMAN: aggregate%’;

How to Monitor the Copy Process (continued)
4. Using SQL*Plus and query V$PROCESS and V$SESSION to get the SID and SPID.
Then use an operating system utility to monitor the process or threads.
SELECT sid, spid, client_info FROM v$process p, v$session s WHERE p.addr = s.paddr
AND client_info LIKE ’%id=rman%’;
Note: For monitoring the copy process, you must query the target database, and hence, the target database should be in Open or Mount state.

Example Questions/ Lab Sessions
1. What are the two supported backup types for Recovery Manager? List some of the differences between the two backup types.
The two types of backups supported by the recovery manager are backup set and image
copy.
A backup set is a backup of one or more database files, while the image copy contains a
backup of only one datafile.
An image copy can be made to a disk only while backup set can be taken to disk or tape.
An image copy contains all the blocks of the input file (even the unused blocks in
datafiles) while backup set may contain only the used blocks.
Image copies operate on single files at file level while backup sets operate on files and
their logical groups (such as Tablespace, Database).

2. Use RMAN to back up the datafiles belonging to the SAMPLE and USERS tablespace. Be sure you also make a copy of the current control file. Your backups should be placed in the $HOME/BACKUP/RMAN directory and should use the format df_%d_%s_%p.bus for the file names.

RMAN> BACKUP TABLESPACE sample INCLUDE CURRENT CONTROLFILE
FORMAT ‘$HOME/BACKUP/RMAN/df_%d_%s_%p.bus‘;

3. Create an image copy of the datafiles belonging to the SYSTEM tablespace. The copy should be placed in the $HOME/BACKUP/RMAN directory with the name of
sys0101.cpy. The tag should be SYSTEM01.

RMAN> COPY DATAFILE ‘$HOME/ORADATA/u01/system01.dbf’
TO ‘$HOME/BACKUP/RMAN/sys0101.cpy’
TAG ‘SYSTEM01’;

4. Using RMAN, back up the archived logs generated today to the $HOME/BACKUP/RMAN directory.
RMAN> BACKUP ARCHIVELOG FROM TIME ‘SYSDATE-1’;

5. Obtain a listing of all data files that have not been backed up.
RMAN> REPORT NEED BACKUP;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- ----------------------------------
2 0 /databases/db01/ORADATA/u02/undotbs.dbf
3 0 /databases/db01/ORADATA/u03/users01.dbf