complete Recovery

Instance-Crash Recovery

 Crash recovery or instance recovery is the automatic recovery of the database by the oracle server , without any intervention by the DBA. For example, if a power outage brings down your database instance , when the power supply resumes,you only need to restart the database instance.

 The oracle server will use the informaton saved in the onlineredo log files to synchronize the datafiles.Instance recovery involves the following two key operations:

·         Rolling forward:During this operation,the oracle server will update all datafiles with the information from the redo log files.The online redo log-files are always written to before the data is recorded in the datafiles. Thus an instance recovery may usually leave the online log files "ahead" of the data-files.

·         Rolling back:During this operation, uncommitted changes that were added to the datafiles the roll-forward operation are rolled back.Oracle does this by using the undo tablespace contents to return uncommitted changes to return uncommitted changes to their original states.At the end of rollback stage only committed data at the end of time of instance failure is retained in the datafiles.

  During rollforward process , the database server must apply all transactions between the last checkpoint and the end of redo log.You use the parameter fast_start_mttr_target to specify the number of seconds you want the crash recovery to take.Oracle will try to recover the instance as close as possible to the time that you specify for the fast_start_mttr_target parameter.Maximum value of this parameter is 3600 seconds (one hr).

Media Recovery is Required or Not ?

 When you start your database, Oracle uses the SCN information in the control files and datafile headers to determine which one of the following will occur:

·         Starting up normally

·         Performing crash recovery

·         Determining that media recovery is required

 On start-up, Oracle checks the instance thread status to determine whether crash recovery is required. When the database is open for normal operations, the thread status is OPEN. When Oracle is shut down normally (normal, immediate, or transactional), a checkpoint takes place, and the instance thread status is set to CLOSED.

 When your instance abnormally terminates (such as from a shutdown abort command), the thread status remains OPEN because Oracle didn’t get a chance to update the status to CLOSED. On start-up, when Oracle detects that an instance thread was abnormally left open, the system monitor process will automatically perform crash recovery. This query demonstrates how a single instance of Oracle would determine whether crash recovery is required:

SELECT

a.THREAD#, b.open_mode, a.status,

CASE

WHEN ((b.open_mode='MOUNTED') AND (a.status='OPEN')) THEN 'Crash Recovery req.'

WHEN ((b.open_mode='MOUNTED') AND (a.status='CLOSED')) THEN 'No Crash Rec. req.'

WHEN ((b.open_mode='READ WRITE') AND (a.status='OPEN')) THEN 'Inst. already open'

ELSE 'huh?'

END STATUS

FROM v$thread a,

v$database b,

v$instance c

WHERE a.THREAD# = c.THREAD#;

 Oracle will start up normally if the SCN information in the control files matches the SCNs in the corresponding datafiles. If the checkpoint SCN in the datafile is less than the corresponding SCN in the control file, Oracle will throw a media recovery error. For example, if you restored a datafile from backup, Oracle would detect that the SCN in the datafile is less than the corresponding SCN in the control file. Therefore, a recovery is required to apply changes to the datafile to catch it up to the SCN in the control file.

The following SQL query demonstrates the internal checks that Oracle performs to determine whether media recovery is required:

SELECT a.NAME,a.checkpoint_change#,b.checkpoint_change#,

CASE

WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'

WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Recovery'

WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'

ELSE 'what the ?'

END STATUS,

CASE

WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'No action Required'

WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Perform Media Recovery'

WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Restore a newer control file'

ELSE 'what the ?'

END DBA_ACTION

FROM v$datafile a, -- control file SCN for datafile

v$datafile_header b -- datafile header SCN

WHERE a.FILE# = b.FILE#;

---The V$DATAFILE_HEADER view uses the physical datafile on disk as its source.

The V$DATAFILE view uses the control file as its source.

Determining how to restore and recover:

Problem

You just experienced a media failure, and you’re not sure what commands you’ll need to restore and recover your database.

Solution

To be able to perform a complete recovery, all of the following conditions need to be true:

·         Your database is in archivelog mode.

·         You have a good baseline backup of your database.

·         You have any required redo that has been generated since the backup (archived redo log files, online redo log files, or incremental backups that RMAN can use for recovery instead of applying redo).

There are a wide variety of restore and recovery scenarios. How you restore and recover depends directly on your backup strategy and what files have been damaged. Listed next are the general steps to follow when facing a media failure:

·              Determine what files need to be restored.

·              Depending on the damage, set your database mode to nomount, mount, or open.

·              Use the restore command to retrieve files from RMAN backups.

·              Use the recover command for datafiles requiring recovery.

·              Open your database.

When faced with a media failure, you need to have a good understanding of your backup strategy and how that will enable you to restore and recover your database. You should periodically test your backups so that you can confidently recover your database when faced with a media failure. When you architect your backup strategy, you should also architect a corresponding restore and recovery strategy. A sound backup strategy should minimize the risk of you losing data and minimize the downtime of your database.

We also recommend querying the data dictionary for more information. The V$DATAFILE_ HEADER view derives its information from the datafile headers and reports in the ERROR and RECOVER columns any potential problems. For example, a YES or null value in the RECOVER column indicates that you have a problem:

SQL> select file#, status, error,recover from v$datafile_header;

FILE# STATUS ERROR REC

---------- ------- ------------------------- ---

1        ONLINE               NO

2        ONLINE               NO

3        ONLINE               NO

4        ONLINE  FILE NOT FOUND

5        ONLINE               NO

The V$RECOVER_FILE reads from the control file and displays information about files needing media recovery:

SQL> select file#, error from v$recover_file;

FILE# ERROR

---------- -------------------------

4 FILE NOT FOUND

Previewing Backups Needed for Restore

Problem

Before you perform a restore and recovery, you would like to view which backups will be required for the restore operation.

Solution

Use the restore ... preview command to query the RMAN repository for the most recent backup sets and corresponding files that will be used for a restore operation. Three restore ... preview modes are available:

·         Normal --- Similar to list backup command

·         Summarized – output in summary format.

·         Recall

RMAN> restore database preview

This command does not check to see whether the RMAN backup files physically exist or that they are accessible.

Verifying Integrity of Backups

Problem

You need to perform a restore and recovery, but first you want to validate only that the backup pieces are available and structurally sound before you actually restore any datafiles.

Solution

You can use either the restore ... validate or validate command to verify the availability and integrity of backup pieces required by RMAN to perform the restore operation. These commands do not restore datafiles. You can additionally specify the check logical clause to instruct RMAN to check for logical corruption.

Restore database validate	Verify integrity of backups (Physical corruption)	By default RMAN checks only for physical corruption when validating. Physical corruption is when the block contents don't match the physical format that oracle expects.
Restore database validate check logical	To check for logical corruption using RMAN	Logical corruption is when the block is in correct format but the contents aren't consistent with what oracle expects.Same info will be reflected in alert.log file & v$database_block_corruption view.RMAN cannot recover logical corrupt blocks.To recover logically corrupt blocks restore the datafile from a backup and perform media recovery.When rman can't find a backup piece or detects corruption it will issue a "failover to previous backup" message and automatically searches for a previously taken backup abnd will stop when it finds a good backup or untill it has searched through all known backups without finding a good one

Testing Media Recovery

Problem

You need to perform a database recovery, but you suspect one of your archived redo log files is bad. You want to perform a test to see whether all of the redo is available and can be applied.

Solution

The recover ... test command instructs Oracle to apply the redo necessary to perform recovery but does not make the changes permanent in the datafiles. When you recover in test mode, Oracle applies the required redo but rolls back the changes at the end of the process. This example starts up the database in mount mode, restores the entire database, and then does a test recovery:

RMAN> connect target /

RMAN> startup mount;

RMAN> restore database;

RMAN> recover database test;

Here is a partial snippet of the output showing that the test recovery was successful:

ORA-10574: Test recovery did not corrupt any data block

ORA-10573: Test recover tested redo from change 847960 to 848243

ORA-10570: Test recovery complete

You can test a recovery with most recover commands. Here are some examples:

RMAN> recover tablespace users, tools test;

RMAN> recover datafile 1 test;

recover database test

You want to perform a database recovery,but you suspect one of your archived redo log files is bad .You want to perform a test to see whether all of the redo is available and can be applied.

This command instructs oracle to apply the redo necessary to recover but does not make the changes permanent in the datafiles.When you recover in test mode,Oracle applies the required redo but rollbacks the changes at the end of the process.

If you’re missing archived redo log files or online redo log files that are needed for recovery, you’ll receive a message similar to this:

ORA-06053: unable to perform media recovery because of missing log

 ALLOWING CORRUPTION

In Oracle Database 10g and lower, the syntax recover ... test allow n corruption does not work from within RMAN. If you want to run the test command with the allow n corruption clause, then youmust issue that command from inside SQL*Plus, as shown here:

SQL> connect sys/muft as sysdba

SQL> recover tablespace system test allow 5 corruption;

When using the recover ... test allow n corruption command, you can specify integers greater than 1 for n. If you are using recover ... allow n corruption (and not using the test command), then n can be 1 only.

Lost all datafiles but still have all your online redo log files:

Solutions:

·         Perform Database-Level complete recovery with current control file.

·         Perform Database-level Complete recovery with backed by control file.

You must first put your database in mount mode to perform a database-wide restore and recovery.This is because the system tablespace datafile(s) must be offline when beign restored and recovered.Oracle won’t allow you to operate your database in open mode with the system datafile offline.

1.     Perform Database-Level complete recovery with current control file:

·         RMAN>connect target /

·         RMAN>Startup Mount;

·         RMAN>Restore database;

·         RMAN>Recover database;

·         RMAN>Alter database open;

2.     Perform Database Level Complete recovery with backed by control file

·         RMAN> Connect target /

·         RMAN> Startup mount

·         RMAN> Restore control file from autpbackup

·         RMAN> Alter database mount

·         RMAN> Restore database

·         RMAN> Recover database

·         RMAN> Alter database open resetlogs;

You are required to open your database with the open resetlogs command anytime you use a backup control file during a recovery operation.

All datafiles of a tablespace goes missing !! What to do ?

You are seeing a media error assosiated with several datafiles cotained in one tablespace.You want to perform a tablespace level recovery:

Scenario 1: Database is open and you want to perform tablespace-level recovery:

·         RMAN >connect target /

·         RMAN>SQL ‘alter tablespace data_ts offline immediate’ ;

·         RMAN>restore tablespace data_ts;

·         RMAN>recover tablespace data_ts;

·         RMAN>SQL ‘Alter tablespace data_ts online’;

Scenario 2: Database is down or mounted and you want to perform tablespace-recovery:

·         RMAN >Connect target /

·         RMAN>startup mount

·         RMAN>restore tablespace data_ts;

·         RMAN>recover tablespace data_ts;

·         RMAN>alter database open;

You lost a particular Datafile.How to recover!! ?

You have one datafile that has experienced media failure and you want to restore and recover it without restoring the entire tablespace or database.

Scenarion 1: Datafile recovery while database is open

·              RMAN>Connect target /

·              RMAN>SQL ‘Alter database datafile 3,4 offline’;

·              RMAN>Restore datafile 3,4 ;

·              RMAN>Recover datafile 3,4;

·              RMAN>Alter database datafile 3,4 online;

Scenarion 2: Datafile recovery while database is not open.

·              RMAN>Connect target /

·              RMAN>Startup mount

·              RMAN>Restore datafile 1 ;

·              RMAN>Recover datafile 1;

·              RMAN>Alter database open;

Restore datafiles to a non-default location !!

You have just experienced a serious media failure and won't be able to restore datafiles to their original locations.In other words you need to restore to a non default location.

 Use the set newname and switch commands to restore datafiles to nondefault locations.Both these commands will run from within RMAN run {} block.The below example changes the location of datafiles 4 and 5,which are in the data_ts tablespace.

RMAN>connect target /

RMAN>startup mount

RMAN> run{

  > set newname for datafile 4 to '/ora01/db/data_file_new_name.dbf';

  > set newname for datafile 5 to /ora01_new_location/db/data_file_name.dbf';

  >restore tablespace data_ts;

  >switch datafile all #Updates repository with new datafile location

  >recover tablespace data_ts;

  >alter database open;

 }

 If the database is open you can place the datafile offline and then set their new names for restore and recovery:

RMAN>  run{

  >SQL 'Alter database datafile 4,5 offline';

  > set newname for datafile 4 to '/ora01/db/data_file_new_name.dbf';

  > set newname for datafile 5 to /ora01_new_location/db/data_file_name.dbf';

  >restore datafile 4,5;

  >switch datafile all  # Updates repository with new datafile location

  >recover tablespace data_ts;

  > SQL 'Alter database datafile 4,5 online;

 }

RMAN Command	Purpose	Comments
restore database check readonly	By default,the restore command skips the datafile assosiated with read-only tablespaces	If you want read-only tablespaces restored,then you must use the check readonly command or explicitily restore each read-only tablespace.
Restoring Tempfile Tablespaces:	Starting with oracle database 10g,you don't have to restore or re-create missing locally managed temporary tablespace tempfiles.	When you open your database for use,Oracle automatically detects and re-creates locally managed temporary tablespace tempfiles using the information in control files.When Oracle automatically re-created a temp tablespace , it will log a message to you target database alert.log file similar to the following: "Re-Creating tempfile <location of temp files>
restore tablespace force; restore database force;	Use the force command to restore datafile and archived redo log files even if they already exist in a location	.This command forces RMAN to restore files even if RMAN to restore files, even if RMAN determines that they don't need to be restored.

  

Restoring from an Older Backup

You want to specifically instruct RMAN to restore from a backup set that is older than the last backup that was taken.

Solution

You can restore an older backup a couple of different ways: using a tag name or using the restore ... until command.

Specify a Tag Name

Use the list backup to find the tag name of the backup set. Every backup set has a tag name, either the default or one you specified. For example, here’s the partial output of a list backup command that shows the desired tag name:

BP Key: 159 Status: AVAILABLE Compressed: NO Tag: MON_BACK

Once you’ve identified the tag, you can instruct RMAN to use that as follows:

RMAN> startup mount;

RMAN> restore database from tag MON_BACK;

RMAN> recover database;

RMAN> alter database open;

Using restore ... until

You can also tell RMAN to restore datafiles from a point in the past using the until clause of  the restore command in one of the following ways:

·         Until SCN

·         Until sequence

·         Until restore point

Until time

Very IMPORTANT

This recipe uses the restore ... until command with the recover command to perform complete recovery. If you need to perform an incomplete recovery, then you will need to use the recover ... until command (not just the recover command by itself). Incomplete recoveries use recover until in conjunction with the restore ... until command.

If you know the SCN in a backup piece that you want to restore from, you can specify the SCN as follows:

RMAN> startup mount;

RMAN> restore database until SCN 1254174;

RMAN> recover database;

RMAN> alter database open;

Or if you know the log sequence number that you want to restore up to, the syntax is as follows:

RMAN> startup mount;

RMAN> restore database until sequence 17;

RMAN> recover database;

RMAN> alter database open

If you’ve created restore points, then you can also use the restore point name as follows:

RMAN> startup mount;

RMAN> restore database until restore point FRI_RS;

RMAN> recover database;

RMAN> alter database open;

You can also specify a point in time from which you want RMAN to restore an older backup. This example instructs RMAN to retrieve the first backup it finds that is more than 10 days old:

RMAN> startup mount;

RMAN> restore database until time 'sysdate – 10';

RMAN> recover database;

RMAN> alter database open;

Here we’re specifically instructing RMAN to restore from a date and time. Since we don’t instruct RMAN to recover to a point in time, this example will perform a complete recovery:

RMAN> startup mount;

RMAN> restore database until time "to_date('05-oct-2006 14:00:00', dd-mon-rrrr hh24:mi:ss')";

RMAN> recover database;

RMAN> alter database open;