How to recover a datafile in Oracle

Here’s a simple guide on how to recover a datafile in Oracle.

1. Shutdown the database

 SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

2. Start up the database in mount mode.

 SQL> startup mount;
 ORACLE instance started.

3. Find which files are off line.
NOTE : This state will also show SYSTEM datafiles, ignore those. You only need to know about the ones that are OFFLINE or mark RECOVERY.

SQL> select name, status from v$datafile where status != 'ONLINE';
NAME                                                    STATUS
-------------------------------------------------      -----------------------------------------------
 /oracle/oradata/db1/undotbs01.dbf                      RECOVER


4. Recover the datafile.

 SQL> alter database recover datafile '/oracle/oradata/db1/undotbs01.dbf';
 Database altered.

5. Bring the datafile back online.

 SQL> alter database datafile '/oracle/oradata/db1/undotbs01.dbf' online;
 Database altered.

6. Open the database

 SQL> alter database open;
 Database altered.

7 Do a restart to make sure the database can stop and start OK.

 SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
SQL> startup
 ORACLE instance started
 Database mounted.
 Database opened.
 SQL>

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s