r man overview

8/13/2019 r Man Overview

1/24

10g Recovery Manager Overview

10g Recovery Manager- Overview

Author: Jamshid T. C

Creation Date: May 18, 2005

Last Update date: sep 01, 2005

Version: 1.0


2/24


1. Purpose

This document will give an overview about Oracle 10g RMAN backup and walkthrough the steps involved in setting up a RMAN Backup. The purpose of this guide is to

help you quickly find the syntax for, and use, RMAN commands to back up, restore, andrecover a database

2. Introduction

Recovery Manager (RMAN) is an Oracle utility that can back up, restore, and recoverdatabase files. RMAN uses server sessions to perform backup and recovery operationsand stores metadata in a repository. RMAN automates backup and recovery, whereas theuser-managed method requires you to keep track of all database files and backups.

3. RMAN Environment

RMAN environment contains the following components:

RMAN executable

Recovery catalog database(optional)

Recovery catalog schema in the recovery catalog database

Target database

Media management layer (optional)

Through out this document i am using rman/rman@catdb10g onrman.dbora.corp.yahoo.com as the recovery catalog schema. For anybody who wants to

try out things with RMAN can use targetdb as the target database andtest/test@catdb10g as this recovery catalog schema. rman/rman@catdb10g is used inproduction.

4. RMAN compatibil ity matrix

In general, the rules of RMAN compatibility are as follows:

The RMAN executable version should be the same as the target database.Legal exception combinations are there. But it is always good to use thesame version of target Database and the RMAN executable to be able to

use all the features without fail. The RMAN catalog schema version must be greater than or equal to the

RMAN executable.

The RMAN catalog is backwards compatible with target databases fromearlier releases


3/24


5. RMAN Repository

The RMAN repository is the collection of metadata about the target database thatRMAN uses for backup, recovery, and maintenance. RMAN always stores this

information in records in the Target Databases control file. RMAN must keep track ofmany things. When was the last backup taken? Which files were backed up? Whichbackup sets contain which database files? The answers to these questions can be found inthe target control files.You can also create a recovery catalog, an external Oracle database in which to store thisinformation. The recovery catalog consists of three components

A separate database referred to as the catalog database (from the targetdatabase)

A schema within the catalog database

Tables (and supporting objects) within the schema that contain data pertaining

to RMAN backup and recovery operations performed on the target

Inside the catalog database is a special schema containing the tables that storeinformation about RMAN backup and recovery activities. This includesinformation such as:

Details about the physical structure of the target database

A log of backup operations performed on the target database'sdatafiles, control files, and archived redo log files

Stored scripts

5.1 Catalog advantages

When you use a catalog repository, you get more flexibility and access to all thefeatures of RMAN. The advantages of using a catalog accrue in the following areas:

You can retain backup and recovery metadata for long periods of time.

You can centralize operations.

RMAN becomes more flexible in certain backup and recoveryscenarios. . For example, you can use previous incarnations of thedatabase for recovery.

You can store set of RMAN commands as Scripts in recovery catalog

5.2 Catalog disadvantages

While the advantages of the catalog are substantial, there are a few potentialheadaches that you should be aware of:

Upgrades and compatibility can be problematic.


4/24


Loss of the recovery catalog can be disastrous as it would containinformation for multiple databases.

Using a catalog created prior to Oracle Version 8.1.6 with multipleversions of target databases can be problematic.

A catalog can increase your needs for hardware and DBA resources.

If you're backing up production databases, and you want the most flexibility possible forany given backup and recovery scenario, Oracle recommends that you use a catalogdatabase. However, if you have non production databases for which you want toimplement RMAN functionality, but you don't want the overhead of a catalog, you maywant to consider just using the target control

6. Terminologies

6.1 RMAN Channels

A channel is a RMAN server process started when there is a need to communicatewith an I/O device, such as a disk or a tape. A channel is what reads and writes RMANbackup files. Any time you issue an RMAN allocate channel command, a server processis started on the target database server

6.2 RMAN backup

A backup of all or part of your database. This results from issuing an RMANbackup command. A backup consists of one or more backup sets.

6.3 Backup set

A logical grouping of backup files -- the backup pieces -- that are created whenyou issue a RMAN backup command. A backup set is RMAN's name for a collection offiles associated with a backup. A backup set is composed of one or more backup pieces.

6.4 Backup piece

A physical binary file created by RMAN during a backup. Backup pieces arewritten to your backup medium, whether to disk or tape. They contain blocks from thetarget database's datafiles, archived redo log files, and control files.When RMAN constructs a backup piece from datafiles, there are a several rules that it

follows:

A datafile cannot span backup sets.

A datafile can span backup pieces as long as it stays within one backupset.

Datafiles and control files can coexist in the same backup sets.

Archived redo log files are never in the same backup set as datafiles orcontrol files.


5/24


RMAN is the only tool that can operate on backup pieces. If you need to restore a filefrom an RMAN backup, you must use RMAN to do it. There's no way for you tomanually reconstruct database files from the backup pieces. You must use RMAN torestore files from a backup piece.

6.5 Image copy

An image copy is an exact copy of a single datafile, archived redo log file, orcontrol file. Image copies are not stored in a RMAN-specific format. They are identical tothe results of copying a file with operating system commands. RMAN can use imagecopies during RMAN restore and recover operations, and you can also use image copieswith non-RMAN restore and recovery techniques.

7. Setting up a RMAN backup with recovery catalog

Following section will explain how to set up a 10g RMAN Backup with Recovery

catalog. The Examples are done on rman.dbora.corp.yahoo.com.The test target databaseof which the backups are taken is called targetdband the recovery catalog db is calledcatdb10gwhich will be used as the centralized recovery catalog for later 10g productiondatabase RMAN backup. This document will discuss about only RMAN backups toDISK

7.1 Creating the Recovery Catalog Owner

Creating recovery catalog owner and recovery catalog is a one time operation tobe done when creating a centralized recovery catalog.

7.1.1. Start SQL*Plus and then connect with administ rator pr ivileges to thedatabase containing the recovery catalog. For example, enter:

SQL> CONNECT SYS/oracle@catdb10g AS SYSDBAConnected

7.1.2. Create a user and schema for the recovery catalog. For example,enter:

SQL> create user rman identified by rman temporary tablespace temp default tablespacetools quota unlimited on tools;

User created.

7.1.3.Grant the RECOVERY_CATALOG_OWNER role to the schema owner.

This role provides the user with all privileges required to maintain and query the recoverycatalog.


6/24


SQL> GRANT CONNECT,RESOURCE,RECOVERY_CATALOG_OWNER TO rman;Grant succeeded.

7.2 Creating the Recovery Catalog

7.2.1 Connect to the database that will contain the catalog as the catalogowner. For example, enter the following from the operating systemcommand line:

% rmanRMAN> connect CATALOG rman/rman@catdb10g

connected to recovery catalog databaserecovery catalog is not installed

7.2.1 Run the CREATE CATALOG command to create the catalog. The

creation of the catalog can take several minutes.

RMAN> create catalog;

recovery catalog created

7.3 Registering a Database in the Recovery Catalog

The first step in using a recovery catalog with a target database is registering thedatabase in the recovery catalog. Any new target database has to be registered in recoverycatalog. Use the following procedure:

After making sure the recovery catalog database is open, connect RMAN to the targetdatabase and recovery catalog database.(use the RMAN executable from the target db)

%rman catalog rman/rman@catdb10g target sys/oracle@targetdb

connected to target database: TARGETDB (DBID=949703603)connected to recovery catalog database

RMAN> register database;Database registered in recovery catalogStarting full resync of recovery catalogfull resync complete

RMAN creates rows in the catalog tables to contain information about the targetdatabase, then copies all pertinent data about the target database from the control file intothe catalog, synchronizing the catalog with the controlfile.


7/24


The same way unregister Database command will unregister the database fromrecovery catalog.

8. Resynchronizing the Recovery Catalog

RMAN compares recovery catalog with the target databases controlfile and updatesthe recovery catalog with information that is missing or changed.This is calledresynchronizing. When resynchronizing, RMAN does the following:

1. Creates a snapshot control file.2. Compares the recovery catalog to the snapshot control file.3. Updates the recovery catalog with missing or changed information.

RMAN performs resynchronizations automatically as needed when you executecertain commands, including BACKUP command. You can also manually performa full resynchronization using the RESYNC CATALOG command.

9. Taking the backup of the target Database

After registering the target database in the recovery catalog, we can start takingdifferent types of backup. for example executing the below run block after connecting toboth the catalog and target database will take a full database backup including thearchivelog file .Remember the target database need to be in the archive log mode to takean online backup of it.RMAN can take the backup of the database in NOARCHIVEmode only in MOUNTED state.

%rman catalog rman/rman@catdb10g target sys/oracle@targetdb

RMAN> run {Allocate channel d1 type disk;Backup database format '/d1/RMANBKP/targetdb/%d_%T_%U';

}

It first manually allocates a channel called d1 to disk. If we dont manuallyallocate channel RMAN will automatically allocate a default channel.Format tells RMAN where the backup has to be stored and in which format .if we dontspecify format, RMAN stores it in the default location (?/dbs) in the default format (%U-a system-generated unique filename).

%d specifies the name of the database and %T Specifies the year, month, and day inYYYYMMDD format.

Checking the backup location we can see these backup pieces got generated in thespecified file format. These include all the datafiles and the control file.

%ls -ltr-rw-r----- 1 oracle dba 291012608 May 16 02:26TARGETDB_20050516_07gkhc78_1_1-rw-r----- 1 oracle dba 9469952 May 16 02:27TARGETDB_20050516_08gkhc81_1_1


8/24


9.1 Backing up a Tablespace

The same way you can backup just the system and users tablespaces with thefollowing run block. This one uses a default channel and doesnt require a run block.

RMAN>backup tablespace system, users format d1/RMANBKP/targetdb/%d_%T_%U';

9.2 Backing Up Archived Redo Log Files

To backup all the archive logs in the archive log directory you can give thiscommand. All the archivelogs will be backed up to the default location as we are notspecifying any format for the backup pieces.

RMAN> backup archivelog all;

Another technique to use the backup command's plus archivelog clauses to include thearchive redo log files as part of the backup. This creates separate backup pieces for thearchived redo log files:

RMAN> backup database plus archivelog;

10. Control File and Server Parameter File Auto backups

RMAN can automatically back up the control file and server parameter file(SPFILE) in situations in which the RMAN repository data for your database has beenupdated in a way that affects RMAN's ability to restore your database. for example with

this feature ON, RMAN will backup the controlfile and spfile whenever a backupcommand is issued or there is change in the database structure by adding a datafile,logfile etc.

With a control file autobackup, RMAN can recover the database even if the currentcontrol file, recovery catalog, and server parameter file are inaccessible.

11. Configuring RMAN environment:

You can configure persistent settings in the RMAN environment. Theconfiguration setting is done once, and used by RMAN to perform all subsequentoperations. Display the preconfigured settings by typing the command SHOW ALL.There are various parameters that can be used to configure RMAN operations to suit yourneeds. Some of the things that you can configure are:

Required number of backups of each datafile

Number of server processes(channels) that will do backup/restoreoperations in parallel

Directory where on-disk backups will be stored


9/24


Let us look at these configure command after connecting the targetdb and catdb10g.

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4DAYS;

This command configures RMAN to keep all the backups needed for recovering thedatabase to any point of time in the last four days.

RMAN>CONFIGURE BACKUP OPTIMIZATION ON;

This command configures RMAN to check and skip the backing up of any datafiles orarchivelogs which is already backed up and has not been changed after that.

RMAN>CONFIGURE DEFAULT DEVICE TYPE TO DISK;

This command configures RMAN to store the backup to DISK.

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;

This command configures RMAN to set the controlfile and spfile autobackup feature ON

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICETYPE DISK TO '/d1/RMANBKP/targetdb/%F';

This command configures RMAN the location and the format the controlfile and spfilebackup set will be stored

RMAN>CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TOBACKUPSET;

This command configures RMAN to allocate two channels and the backup to be taken asbackup set (other method is image copy)

RMAN>CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO1;

This command configures RMAN to take a single copy of all the datafiles. We can havemore copies of the backup in different location by specifying that number.

RMAN>CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISKTO 1;

This configure RMAN to save more copies of the backed up archive logs.

RMAN>CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT'/d1/RMANBKP/targetdb/%d_%T_%U' MAXPIECESIZE 4 G;


10/24


This command configures the location and the format where the backup sets will bestored. IT also sets the maximum size of the backpiece to 4gig.

You can return any CONFIGURE command to its default setting by running the samecommand with the CLEAR option.

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP CLEAR

12. RMAN Backup Types

RMAN backups can be classified in these ways:12.1 Full:A backup that includes every block in the file being backed up, except never-used blocks omitted due to unused block compression. A full backup cannot be part of anincremental backup strategy; it cannot be the parent for a subsequent incremental backup

12.2 Incremental:An incremental backup is either a level 0 backup, which includes

every block in the file except blocks compressed out because they have never been used,or a level 1 backup, which includes only those blocks that have been changed since theparent backup was taken. A level 0 incremental backup is physically identical to a fullbackup. The only difference is that the level 0 backup can be used as the parent for alevel 1 backup, but a full backup will never be used as the parent for a level 1 backup.

Incremental backup can be further divided into two:

12.2.1 Differential Incremental Backups(default) : In a differential level 1 backup,RMAN backs up all blocks that have changed since the most recent incremental backupat level 1 (cumulative or differential) or level 0. For example, in a differential level 1

backup, RMAN determines which level 1 backup occurred most recently and backs up allblocks modified after that backup. If no level 1 is available, RMAN copies all blockschanged since the base level 0 backup.

If no level 0 backup is available, then the behavior varies with the compatibility modesetting. If compatibility is >=10.0.0, RMAN copies all blocks that have been changedsince the file was created. Otherwise, RMAN behaves as it did in previous releases, bygenerating a level 0 backup.


11/24


Diferential Incremental Backups

In the above example the following occurs each week:

SundayAn incremental level 0 backup backs up all blocks that have ever been in use in

this database.

Monday - SaturdayOn each day from Monday through Saturday, a differential incremental level 1

backup backs up all blocks that have changed since the most recent incremental backup atlevel 1 or 0. The Monday backup copies blocks changed since Sunday level 0 backup, theTuesday backup copies blocks changed since the Monday level 1 backup, and so forth.

12.2.2 Cumulative Incremental Backups

In a cumulative level 1 backup, RMAN backs up all the blocks used since themost recent level 0 incremental backup. Cumulative incremental backups reduce thework needed for a restore by ensuring that you only need one incremental backup from

any particular level. Cumulative backups require more space and time than differentialbackups, however, because they duplicate the work done by previous backups at the samelevel.


12/24


Cumulative Incremental Backups

In the above example the following occurs each week:

SundayAn incremental level 0 backup backs up all blocks that have ever been in use

in this database.

Monday - SaturdayA cumulative incremental level 1 backup copies all blocks changed since the most

recent level 0 backup. Because the most recent level 0 backup was created on Sunday,the level 1 backup on each day Monday through Saturday backs up all blockschanged since the Sunday backup.

The below example shows how to take a Level 0 incremental back up and then a Level 1Differential(default) incremental backup

% rman catalog rman/rman@catdb10g target sys/oracle@targetdb

RMAN> run{backuptag Level_zerofilesperset=4incremental level 0 database plus archivelog;


13/24


}

RMAN> run{backuptag Level_one

filesperset=4incremental level 1 database plus archivelog;}

Tag will give a name to the backup set which can be used while restoring thedatabase.filesperset tells how many files should be included in a backupset.so if there are6 datafiles it will create 2 backup sets for that.

13. 10g RMAN New features:Some of the 10g new features are:

13.1 Incrementally Updated BackupsUsing this feature all changes between the SCN of the original image copy andthe SCN of the incremental backup are applied to the image copy, winding it forward tomake the equivalent of a new database image copy without the overhead of such abackup. The following example shows how this can be used:

RMAN>RUN {RECOVER COPY OF DATABASE WITH TAG 'incr_backup' UNTIL TIME

'SYSDATE - 7';BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG

'incr_backup' DATABASE;}

The RECOVER COPY... line will not do anything until the script has been running formore than 7 days. The BACKUP INCREMENTAL line will perform a complete backup(level 0) the first day it is run, with all subsequent backups being level 1 incrementalbackups. After 7 days, the RECOVER COPY... line will start to take effect, merging allincremental backups older than 7 days into the level 0 backup, effectively moving thelevel 0 backup forward. The effect of this is that you will permanently have a 7 dayrecovery window with a 7 day old level 0 backup and 6 level 1 incremental backups.Notice that the tag must be used to identify which incremental backups apply to whichimage copies.

If you wanted to keep your image copy as up to date as possible you might do thefollowing:RMAN>RUN {BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG

'incr_backup' DATABASE;RECOVER COPY OF DATABASE WITH TAG 'incr_backup';

}


14/24


In this example the incremental backup is merged into the image copy as soon as it iscompleted.

13.2 Fast Incremental BackupsThere is performance issues associated with incremental backups as the whole of

each datafile must be scanned to identify changed blocks. In Oracle 10g it is possible totrack changed blocks using a change tracking file. Enabling change tracking doesproduce a small overhead, but it greatly improves the performance of incrementalbackups. The current change tracking status can be displayed using the following query:

SQL>SELECT status FROM v$block_change_tracking;

Change tracking is enabled using the ALTER DATABASE command:

SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

By default the change tracking file is created as an Oracle Managed File (OMF) in thelocation pointed to by the DB_CREATE_FILE_DEST parameter. An alternate locationcan be specified using the following command:

SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE'/home/oracle/OraHome1/dbs/targetdb_change_track.f' REUSE;

The tracking file is created with a minimum size of 10M and grows in 10M increments.Its size is typically 1/30,000 the size of the data blocks to be tracked.

Change tracking can be disabled using the following command:SQL>ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

13.3 BACKUP for Backup sets and Image Copies

In Oracle 10g the BACKUP command has been extended to allow it to initiatebackups of image copies in addition to backupsets. As a result the COPY command hasbeen deprecated in favor of this new syntax.BACKUP AS COPY DATABASE;BACKUP AS COPY TABLESPACE users;BACKUP AS COPY DATAFILE 1;RMAN supports the creation of image copies of datafiles and datafile copies, control filesand controlfile copies, archived redo logs, and backup pieces.

13.4 Backupset Compression

The AS COMPRESSED BACKUPSET option of the BACKUP command allows RMANto perform binary compression of backupsets. The resulting backupsets do not need to beuncompressed during recovery. It is most useful in the following circumstances:

You are performing disk-based backup with limited disk space.


15/24


You are performing backups across a network where networkbandwidth is limiting.

You are performing backups to tape, CD or DVD where hardwarecompression is not available.

We can configure the compressed backup set as below

RMAN>CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TOCOMPRESSED BACKUPSET;

13.5 Restore Preview

The PREVIEW option of the RESTORE command allows you to identify thebackups required to complete a specific restore operation. The output generated by thecommand is in the same format as the LIST command. In addition the PREVIEWSUMMARY command can be used to produce a summary report with the same format asthe LIST SUMMARY command. The following examples show how these commands

are used:# PreviewRESTORE DATABASE PREVIEW;RESTORE TABLESPACE users PREVIEW;

# Preview SummaryRESTORE DATABASE PREVIEW SUMMARY;RESTORE TABLESPACE users PREVIEW SUMMARY;

13.6 Managing Backup Duration and Throttl ing

The DURATION clause of the of the BACKUP command restricts the total timeavailable for a backup to complete. At the end of the time window backup is interruptedwith any incomplete backupsets discarded. All complete backupsets are kept and used forfuture restore operations. The following examples show how it is used:RMAN>BACKUP DURATION 2:00 TABLESPACE users;RMAN>BACKUP DURATION 5:00 DATABASE PLUS ARCHIVELOGS;

13.7 Channel Failover

When multiple channels are available for the same device type retriable errors ina backup step will automatically be retried on another channel. Retriable errors areusually associated with media managers failing to access tapes or instance failures inRAC environments.

14. Restoring Methods

In general there are three steps involved in restoring files:1. Ensure that the target database is started in the appropriate mode for the

restoration operation. For lost control files, this will be nomount mode. If the


16/24


entire database needs to be restored, this will be mount mode. If datafiles thatdon't belong to the SYSTEM tablespace are damaged, you have the option ofkeeping the database open and taking only the tablespace(s)/datafile(s) that needsto be restored offline.

2. Start RMAN and connect to the target and recovery catalog if one is

being used.3. Run the appropriate RMAN RESTORE command to bring backrequired files. The requested files and the appropriate archived redo log files willbe restored.

Once the necessary files are restored, you need to recover your database and open it foruse. You can recover the database from either RMAN, SQL*Plus

Now we will discuss some of the recovery scenarios:

14.1 Restoring and Recovering All Datafiles

In this scenario, it is assumed that your control files are still accessible. You havea backup, done for example with backup database plus archivelog;

Your first step is to make sure that the target database is shut down:

SQL> connect / as SYSDBA;SQL> shutdown abort;ORACLE instance shut down.

Next, you need to start up your target database in mount mode. RMAN cannot restore

datafiles unless the database is at least in mount mode, because RMAN needs to be ableto access the control file to determine which backup sets are necessary to recover thedatabase. If the control file isn't available, you have to recover it first. Issue theSTARTUP MOUNT command shown in the following example to mount the database:

SQL> startup mount;Oracle instance started.

Now Connect to RMAN

$ rman catalog rman/rman@catdb10g target /

When restoring database files RMAN reads the datafile header and makes thedetermination as to whether the file needs to be restored.

RMAN> restore database;RMAN> recover database;RMAN> alter database open;


17/24


Or alternatively, once RMAN has restored the datafiles, you can use SQL*Plus to recoverthe database and open it for use:

$ sqlplus /nolog

SQL> connect /as SYSDBA;SQL> recover database;SQL> alter database open;

14.2 Restoring Specific Tablespaces/Datafiles

In this scenario, it is assumed that your control files are still accessible. You have abackup, done for example with backup database plus archivelog;Take the tablespace/datafile that needs recovery offline, restore the tablespace/datafile,recover the tablespace/datafile, and bring the tablespace/datafile online. If you cannottake the tablespace/datafile offline, then shutdown abort the database and restore in

mount mode.

First try to take the Tablespace offline;$ sqlplus /nolog

SQL> alter tablespace tab offline;

$ rman catalog rman/rman@catdb10g target /RMAN> restore tablespace users;RMAN> recover tablespace users;RMAN> sql 'alter tablespace users online';

If this fails:

SQL> connect / as SYSDBA;SQL> shutdown abort;SQL> startup mount;

$ rman catalog rman/rman@catdb10g target /RMAN> restore tablespace users;RMAN> recover tablespace users;

SQL> alter database open;Instead of a tablespace, you can restore and recover a specific datafile:

RMAN> restore datafile /home/oracle/oradata/targetdb/users_data01.dbfRMAN> recover datafile /home/oracle/oradata/targetdb/users_data01.dbf'

14.3 Restoring Control Files


18/24


In this scenario, it is assumed that your control files are backed up. You have abackup done for example with backup database plus archivelog;In an ideal world you'll never use RMAN to restore a control file. But if somethingcatastrophic happens, and you lose all control files, here are the steps for getting themback:

SQL> connect / as SYSDBA;SQL> shutdown abort;SQL> startup nomount;

$ rman catalog rman/rman@catdb10g target /RMAN> restore controlfile from autobackup;RMAN> alter database mount;RMAN> alter database open;

If this fails with..

RMAN-00571:===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS===============RMAN-00571:===========================================================RMAN-03002: failure of alter db command at 09/28/2004 10:31:52ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

... Then you must perform a recover database:

SQL> shutdown abort;SQL> startup mount;

$ rman catalog rman/rman@catdb10g target /RMAN> recover database;RMAN> alter database open resetlogs;

Note, that all offline archivelogs are now useless, perform a full back as soon as possible

14.4 Restoring Online Redologs

In this scenario, it is assumed that your control files are backed up. You have abackup, done for example with backup database plus archivelog;

SQL> connect / as SYSDBA;SQL> shutdown abort;SQL> startup nomount;


19/24


$ rman catalog rman/rman@catdb10g target /

RMAN> restore controlfile;RMAN> alter database mount;RMAN> restore database;

RMAM> recover database;

RMAN-00571:===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS===============RMAN-00571:===========================================================RMAN-03002: failure of recover command at 09/28/2004 11:03:23RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 8448414Since the online logs were lost, complete recovery is not possible. Open the database with

resetlogs to continue.

RMAN> alter database open resetlogs;

Note, that all offline archivelogs are now useless, perform a full back as soon as possible.

14.5 Time-Based or Change-Based Incomplete Recovery

Incomplete recovery uses a backup to produce a non-current version of thedatabase. In other words, you do not apply all of the redo records generated after the mostrecent backup .You usually perform incomplete recovery of the whole database in thefollowing situations:

1. Media failure destroys some or all of the online redo logs.2. A user error causes data loss, for example, a user inadvertently drops a table.3. You cannot perform complete recovery because an archived redo log is missing.4. You lose your current control file and must use a backup control file to open the

database.

To perform incomplete media recovery, you must restore all datafiles from backupscreated prior to the time to which you want to recover and then open the database withthe RESETLOGS option when recovery completes. The RESETLOGS operation createsa new incarnation of the database; in other words, a database with a new stream of logsequence numbers starting with log sequence 1.

SQL> connect / as SYSDBA;SQL> shutdown abort;SQL> startup mount;


20/24


$ rman catalog rman/rman@catdb10g target /RMAN> restore database;RMAN> recover database until time "to_date('2005-05-18:02:24:14','yyyy-mm-hh24:mi:ss')";media recovery complete.

SQL> alter database open resetlogs;

14.6 Performing Block Media Recovery with RMAN

The BLOCKRECOVER command can restore and recover individual data blockswithin a datafile. This procedure is useful when a trace file or standard output reveals thata small number of blocks within a datafile are corrupt. Block media recovery is not usefulin cases where the extent of data loss or corruption is not known; in this case, use datafilerecovery instead.In this scenario, you identify the blocks that require recovery and then use any available

backup to perform the restore and recovery of these blocks.To recover data blocks by using all available backups:

1. Obtain the datafile numbers and block numbers for the corrupted blocks.Typically, you obtain this output from the standard output, the alert.log, tracefiles, or a media management interface. For example, you may see the followingin a trace file:

ORA-01578: ORACLE data block corrupted (file # 8, block # 13)ORA-01110: data file 8: '/oracle/oradata/trgt/users01.dbf'ORA-01578: ORACLE data block corrupted (file # 2, block # 19)

ORA-01110: data file 2: '/oracle/oradata/trgt/undotbs01.dbf'

2. Assuming that you have preallocated automatic channels, run the BLOCKRECOVERcommand at the RMAN prompt, specifying the file and block numbers for the corruptedblocks as in the following example:

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19;

15. Some useful commands in RMAN

RMAN> report need backup; What files require a backup now

RMAN> crosscheck backup; Determines whether a backup set and its relatedpieces still exist on media. If a backup pieceexists in the location recorded in the control fileof the target database or in the optional recoverycatalog, its status is marked as AVAILABLE. If itis not at the specified location, it is marked asEXPIRED.

RMAN> delete expired backup of This command deletes the physical files


21/24


database;RMAN> delete backup of database;

associated with backup sets and datafile copies,updates their status in the control file, andremoves their information from the optionalrecovery catalog (if one is used).

Backups are flagged as EXPIRED if they cannotbe found at their recorded location. Deletion ofEXPIRED backups removes their informationfrom the control file and from the optionalrecovery catalog (if one is used).

RMAN>create catalog; Create Recovery Catalog.

RMAN> drop catalog; Drops all objects associated with the recoverycatalog schema.

RMAN> report need backup days 2database;

To report on those datafiles that, if restored,would require application of two days (or more)worth of archived redo log files:

RMAN> report need backup days 2tablespace system;

To generate the same report but only forSYSTEM tablespace datafiles:

RMAN> report obsolete To list backups considered obsolete

RMAN> report unrecoverable; Reports on all unrecoverable datafiles

RMAN> report schema; To see a database schema:

RMAN> list backup;RMAN> list backup of database; # allfiles in the databaseRMAN> list copy of Datafile'/u01/oracle/dbs/tbs1.dbf';RMAN> list copy of archivelog all;RMAN> list backup of tablespace'SYSTEM';RMAN> list backup of control file;

Listing the backups :

RMAN> backup validate databasearchivelog all;

Checks datafiles, archivelogs for physical andlogical corruption and that all database files existand are in the correct locations.

RMAN>restore database validate; Is Backup restorable?

RMAN> backup incremental level 0database;

Takes a Level 0 diffrential incremental backup

RMAN> backup incremental level 1cumulative database;

Takes Level 1 cumulative incremental backup

RMAN>show all Show all the configurations

RMAN>backup database plus archivelogdelete input

Take the database backup with archivelogs anddelete the archivelogs after backing up

RMAN> backup as copy database; Take the image copy of datafile

RMAN>delete noprompt archivelog allcompleted before 'sysdate-2'

Delete all the archivelogs completed before 15days from all the locations.


22/24


16. Useful views and queries on target database

V$RMAN_OUTPUT Displays messagesreported by an RMAN jobin progress.

select Sid, OUTPUT fromv$rman_output

V$RMAN_STATUS Shows the success/failurestatus of all recentlycompleted RMAN jobs.

select SID,STATUS fromv$rman_status where sid=158;

V$BACKUP_DATAFILE When deciding how oftento take full or level 0backups, a good rule ofthumb is to take a newlevel 0 whenever 20% ormore of the data haschanged.

V$DATABASE_BLOCK

_CORRUPTION

records the address of the

corrupt blocks and the typeof corruption ifMAXCORRUPT(this saysRMAN to continue backupeven it finds corruptedblock upto this limit) is set

V$BACKUP_FILES Location and other detailsof the backup

V$RMAN_CONFIGURATION

Show all the configuration

V$RECOVER_FILE Identifies which datafiles

require recovery.V$SESSION_LONGOPS Provides progress reports

on RMAN backup andrestore jobs.

SELECT SID, SERIAL#,CONTEXT, SOFAR,TOTALWORK,ELAPSED_SECONDS/60/60 "TimeElapsed(hr)",time_Remaining/60/60 "Timeremianing(hr)",ROUND(SOFAR/TOTALWORK*100,2)"%COMPLETE" FROMV$SESSION_LONGOPS

WHERE OPNAME LIKE'RMAN%' AND OPNAME NOTLIKE '%aggregate%' ANDTOTALWORK != 0 ANDSOFAR TOTALWORK;

V$PROCESS,V$SESSION

Provide SID and SPID ofchannels

Column client_info format a30COLUMN SID FORMAT 999COLUMN SPID FORMAT 9999


23/24


SELECT s.SID, p.SPID,s.CLIENT_INFOFROM V$PROCESS p,V$SESSION s

WHERE p.ADDR = s.PADDRAND CLIENT_INFO LIKE'rman%';

17. Useful recovery catalog views

Name Description

RC_ARCHIVED_LOG : information about all archivelogs

RC_BACKUP_CONTROLFILE backup control files in backup sets

RC_BACKUP_CORRUPTION Corrupt blocks in datafile backups and copies.

RC_BACKUP_DATAFILE Datafile backups (in backup sets).

RC_BACKUP_PIECE Backup pieces.

RC_BACKUP_REDOLOG redolog backups (in backup sets).

RC_BACKUP_SET backup sets.

RC_CONTROLFILE_COPY controlfile copies.

RC_COPY_CORRUPTION corrupt block ranges in datafile copies for alldatabase incarnations

RC_DATABASE information about databases and their currentincarnations

RC_DATABASE_INCARNATION Information about all incarnations registeredrecovery catalog.

RC_DATAFILE information about all datafiles registered inrecovery catalog

RC_DATAFILE_COPY datafile copies (on disk).

RC_LOG_HISTORY Information about redo log history.

RC_OFFLINE_RANGE offline ranges for datafilesRC_REDO_LOG Information about online redo logs.

RC_REDO_THREAD information about redo threads

RC_RESYNC information about recovery catalog resyncs

RC_STORED_SCRIPT Stored scripts.

RC_STORED_SCRIPT_LINE Each line of each stored script.

RC_TABLESPACE Information about all tablespaces registered inrecovery catalog.


24/24

r man overview

Documents