Data File Various Scenarious

Contents:- Data File Recovery, Non-Critical Datafile Recovery, Critical Datafile Recovery, Rename Datafile, Datafile Recovery without Backup, Datafile and Controlfile Recovery Without backup

Notes:- Critical Datafiles Loss requires the Database to be restored from the Mount Stage. While Non-Critical Data Files that May be restored with the Database Open.

Recover Critical Data File (i.e System ,undo, Sysaux) if the System is in Archivelog Mode

Steps

Sql> shutdown abort;

Sql> startup mount;

SQL> Restore tablespace System;

SQL> Recover Tablespace System;

SQL> Alter Database Open;

Recover Image Copies by using Incremental Backup

Image Copies are updated with all changes up to the incremental backup SCN.

Incremental Backup reduces the time required for recovery.

In this Recovery Method, We use RMAN to recover a copy of data file, means we recovery the image copies to the specified point in time by applying incremental backups to image copy. The image copy is updated with all changes up through the SCN at which the incremental backup was taken.

Benefits of Applying Incremental Backup to Image Copies

You Reduce the time required for media recovery (using archive logs) because you need to apply archive logs only since the last incremental backup.

Rename a Data file.

To Move or Rename a Datafile to New Location

Steps

SQL> Shutdown immediate

Physically Change Datafile location or rename it.

SQL> startup mount;

SQL> alter database rename file ‘file_name’;

Recover a Lost Datafile with no Backup with RMAN with Catalog.

Steps

 è Take Fresh Backup.

 è SQL> alter tablespace debt add datafile ‘+data(datafile)’ size 10m;

 è SQL> shutdown immediate

 è Physically delete new data file

 è SQL> Startup;       [ After mount stage getting error]

 è SQL> select * from V$recover_file;

 è In RMAN Connect target with Catalog Database.

 è Rman>sql ‘shutdown immediate’;

 è Rman>sql ‘startup mount’;

 è Rman> restore datafile <datafile_numbeR>;

 è Rman>recover datafile <datafile_numbeR>;

 è Rman>sql ‘alter database open’;

Or

Recovery of data file whose backup is not available.

i.e

sql>create tablespace stc add datafile ‘+data(datafile)’ size 10m;

sql> create table demo(id number) tablespace stc;

insert some records into table demo

disaster : deleted stc datafile.

Sql> startup mount

Sql>alter database datafile <datafile_number> offline;

Sql> alter database open;

Sql> select file#,ts#,status,name from V$datafile;

Sql> shut immediate;

Sql> alter database create datafile ‘<delete datafile with path>’;

Sql> alter database datafile <datafile_numbeR> online;

Sql> select checkpoint_change#,file# from V$datafile_header;

here scn(checkpoint number of datafile 6 is not the same as other because it's not recovered yet).

Rman> run

{

Allocate channel c1 device type disk;

Recover tablespace <tablespace_name>;

}

Recover a Lost Datafile and all control files  with no Backup with RMAN with Catalog.

Steps

 è Take Fresh Backup.

 è Sql>alter tablespace debt add datafile ‘+data(datafile)’ size 10m;

 è Sql>shutdown immediate;

 è Physically delete newly created datafile and all controlfiles.

 è Sql> startup         [ after nomount stage error comes ]

 è In RMAN Connect to target database with catalog.

 è RMAN> sql ‘shutdown immdiate’;

 è Rman> sql ‘startup nomount’;

 è Rman> restore controlfile from autobackup;

 è Rman> SQL ‘Alter database mount’;

 è Rman> restore database;

 è Rman> recover database;

 è Rman> SQL ‘alter database open resetlogs’;