Recovering the Database After the Loss of All Datafiles

Recovering the Database After the Loss of All Datafiles

You can use the recover database command to recover the entire database all at once.

Having restored all the database datafiles from the backup media, you would follow

these steps:

1. Log into the database as SYS.

2. Mount the database with the startup mount command.

3. Issue the recover database command from the SQL prompt.

4. The recover database command will recommend to you the correct archived redo log to apply. At the prompt,   type AUTO; the recover database command automatically starts applying all redo until the database is recovered.

5. Once database recovery is complete, the recover database command will return you to the SQL prompt. You can then issue the alter database open command to open the database for business.

1. Back up the database. Details on how to do a full online database backup are found in

2. In summary, follow these steps:

Ø  First put the database in hot backup mode.

Ø  Copy all database datafiles to a backup location.

Ø  Take the database out of hot backup mode.

Ø  Force a log switch. Back up the archived redo logs.

Here is an example of a backup:

C:\Users\Administrator>sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 22 14:14:31 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: sys@test as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database begin backup;

Database altered.

SQL> host copy D:\app\Administrator\oradata\TEST \*.dbf  E:\HOT_BACKUP

SQL> alter database end backup;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> host copy D:\app\Administrator\fast_recovery_area\TEST\ARCHIVELOG\2013_10_31\*.ARC       E:\HOT_BACKUP\ARCHIVELOG

SQL> alter database backup controlfile to trace;

Database altered.

SQL> alter database backup controlfile to ‘E:\HOT_BACKUP\control1.bak’;

Database altered.

3. Now remove all datafiles from the database. On some operating-system platforms (Linux, for example), you can do this with the database up and running, and on others (Windows) you will have to shut down the database.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

5. Once you are sure the database is down, move old datafiles to some other location abd restore the database datafiles from their  backup location to the location where the database files belong.

6. Now connect to the database and issue the startup mount command.

C:\Users\Administrator>sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 22 14:14:31 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: sys@test as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1.1758E+10 bytes

Fixed Size                  2265896 bytes

Variable Size            8522829016 bytes

Database Buffers         3221225472 bytes

Redo Buffers               12099584 bytes

Database mounted.

7. To recover the database, issue the recover database command. The command may return a response that says “media recovery complete,” as shown here:

SQL> recover database;

You may also be prompted to apply archived redo logs. Simply enter AUTO at the prompt.

SQL> recover database;

ORA-00279: change 5071334 generated at 08/17/2008 15:35:51 needed for thread 1

ORA-00289: suggestion :

D:\app\Administrator\fast_recovery_area\TEST\ARCHIVELOG \2008_08_17/o1_mf_1_5_4bk6onh8_.arcORA-00280:

change 5071334 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 5071583 generated at 08/17/2008

15:40:04 needed for thread 1

ORA-00289: suggestion :

D:\app\Administrator\fast_recovery_area\TEST\ARCHIVELOG \2008_08_17/o1_mf_1_6_4bk76kwk_.arcORA-00280:

change 5071583 for thread 1 is in sequence #6

ORA-00279: change 5091960 generated at 08/17/2008

15:49:05 needed for thread 1

ORA-00289: suggestion :

D:\app\Administrator\fast_recovery_area\TEST\ARCHIVELOG \2008_08_17/o1_mf_1_7_4bk9ksb4_.arcORA-00280:

change 5091960 for thread 1 is in sequence #7

ORA-00279: change 5112317 generated at 08/17/2008

16:29:13 needed for thread 1

ORA-00289: suggestion :

D:\app\Administrator\fast_recovery_area\TEST\ARCHIVELOG \2008_08_17/o1_mf_1_8_4bk9p236_.arcORA-00280:

change 5112317 for thread 1 is in sequence #8

ORA-00279: change 5112647 generated at 08/17/2008

16:31:29 needed for thread 1

ORA-00289: suggestion :

D:\app\Administrator\fast_recovery_area\TEST\ARCHIVELOG \2008_08_17/o1_mf_1_9_4bk9p2mz_.arcORA-00280:

change 5112647 for thread 1 is in sequence #9

Log applied.

Media recovery complete.

8. Oracle will apply the needed redo and then return you to the SQL prompt. Assuming no errors occur, you can now open the database with the alter database open command as shown here:

SQL> alter database open resetlogs;

Database altered.