Home > Oracle, RDBMS > Recovery of non system datafile without backup.

Recovery of non system datafile without backup.

Assumptions:

  • the database is in archive log mode

A non-system datafile can be recovered even if no backups are available for that datafile, provided all the archive logs since the creation of the datafile are available and the datafile is added after the control file is created. Datafiles created before the creation of the controlfile cannot be recoverd by this method.

1. Database is open.
– identify the datafile which needs recovery

select * from v$recover_file;

– connect datafile with tablespace

select t.name, f.name
from v$tablespace t, v$datafile f
where t.ts# = f.ts#
and f.file# = <file_number>;

– offline the tablespace with immediate option

ALTER TABLESPACE <tablespace_name> OFFLINE IMMEDIATE;

– create the missing datafile

ALTER DATABASE CREATE DATAFILE '<datafile_name>';

– recover the tablespace

RECOVER TABLESPACE <tablespace_name>;

– online the tablespace

ALTER TABLESPACE <tablespace_name> ONLINE;

2. Database is close.
– mount the database

STARTUP MOUNT;

– identify the datafile which needs recovery

select * from v$recover_file;

– connect datafile with tablespace

select t.name, f.name
from v$tablespace t, v$datafile f
where t.ts# = f.ts#
and f.file# = <file_number>;

– offline the missing datafile

ALTER DATABASE DATAFILE <file_number> OFFLINE;

– open the database

ALTER DATABASE OPEN;

– create the missing datafile

ALTER DATABASE CREATE DATAFILE '<datafile_name>';

– recover the tablespace

RECOVER TABLESPACE <tablespace_name>;

– online the tablespace

ALTER TABLESPACE <tablespace_name> ONLINE;

Hope, this helps.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: