Backup Options   «Prev  Next»

Lesson 5 Recovering a NOARCHIVELOG database
Objective Perform a NOARCHIVELOG database recovery.

Recovering NOARCHIVELOG Database

Suppose you have a database running in NOARCHIVELOG mode. Disk 1 is damaged due to a media failure, and datafile #1 on this disk is lost. There are two online log files containing sequence numbers 75 and 76, with the current log sequence 76. The most recent backup was made at log sequence 73. Is it possible to completely recover this database?
Answer: No.

Damaged Data files along with the available backup files
Damaged Data files along with the available backup files

Explanation:
If the DBA replaces datafile #1 with a backup copy made at log sequence 73, it cannot be made current with the rest of the database, which is currently at 76. There are not enough logs available. Online redo logs are not being archived and the current two logs, 75 and 76, at one time held sequence number 73. But each log is over-written at every log switch. Since the redo log 73 was over-written, a whole database restore is necessary. Remember, if a database is running in NOARCHIVELOG mode, you have to restore the whole database backup even though only one file has been corrupted or lost.

You may also preview all steps.

Steps to recover an Oracle database in NOARCHIVELOG mode

  1. To shutdown the database, issue the SHUTDOWN ABORT command in Server Manager.
  2. Restore the files using the COPY command on an NT platform.
  3. Restart Oracle instance in Server Manager.
  4. Notify users that they will need to renter data since the time of the last backup.

RMAN does not backup online redo logs. To fix the problem, we simply have to tell the database to recreate the online redo logs: 

SQL> alter database clear logfile group1;

Database altered.

SQL > alter database clear logfile group2;
alter database clear logfile group2
*
ERROR at line1: ORA-00350:log 2 of instance orcl1 (thread1) needs to be archived
ORA-00312: online log 2 thread 1:'+ DATA/orcl1/onlinelog/group_2.263.902310515'
ORA-00312: online log 2 thread 1:'+DATA/orcl1/onlinelog/group_2.262.902310515' 
SQL>alter database clear unarchived logfilegroup2;
Database altered.

SQL>alter database clear log filegroup 3;
Database altered.

SQL>alter database open;
Database altered.

NOARCHIVELOG Database Recovery Steps

  1. To connect as sysdba, type CONNECT INTERNAL at the prompt and press Enter.
  2. Type ORACLE at the prompt and press Enter.
  3. To shut down the database, type SHUTDOWN ABORT at the prompt and press Enter.
  4. To access the operating system, type HOST at the prompt and press Enter.
  5. To copy the backup files to the directory where the original files are located, type COPY C:\DIGITAL\BACKUP\*.* C:\ORANT\DATABASE\ at the prompt and press Enter.
  6. To reenter Server Manager, type EXIT at the prompt and press Enter.
  7. To start the Oracle instance, type STARTUP OPEN PFILE=C:\ORANT\DATABASE\INITORCL.ORA at the prompt and press Enter.
  8. You have now successfully restored the database. This is the end of the Simulation.

Recovering to a Previous Incarnation

Problem: You experience a media failure immediately following an open resetlogs command. For example, you are faced with this scenario:
  1. You have recently performed an incomplete recovery and opened your database with the open resetlogs command.
  2. Before you can take another backup, you experience a media failure. You determine that you need to restore and recover the database to a point in time prior to the time you opened the database with the open resetlogs command.
  3. You initiate a point in time recovery prior to the time the database was opened with the open resetlogs command:

RMAN > restore database until time
"to_date('28-aug-2012 08:22:00','dd-mon-rrrr hh24:mi:ss')";
When restoring datafiles, the prior command throws this error:
RMAN-03002: failure of restore command at ...
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

In this situation you must restore and recover to a previous incarnation of your database.
Solution: Before starting, ensure that you set the NLS_DATE_FORMAT variable so that you can see the time component in RMAN’s output: 
$ export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"

This will allow you to see the time component in RMAN’s output.
Step 1: Run the List Incarnation Command
You have to restore a control file that knows about the incarnation of the database to which you want to restore. Therefore, you first need to determine which incarnation you will be restoring to. Run the following command to display incarnation information: 
RMAN > list incarnation;
Here is the sample output.
Apply, Filter, Sort
For this example, we want to restore to the time of 8:22 (which would be incarnation 14, which is prior to the current incarnation of 15).

Step 2: Shut Down the Database 
RMAN > shutdown immediate;

Step 3: Start the Database in Nomount Mode
If you are not using a recovery catalog, start up this way: 
RMAN > startup nomount;

If you are using a recovery catalog, use this command: 
RMAN > startup force nomount;

Step 4: Restore the Control File to the Desired Time
This example assumes the use of an FRA and the auto backup of the control file is enabled: 
RMAN> restore controlfile from autobackup until time
"to_date('28-aug-2012 08:22:00','dd-mon-rrrr hh24:mi:ss')";

If you are not using an FRA and the auto backup of the control file, see Chapter 10 for details on restoring the control file.
Step 5: Mount the Database 
RMAN > alter database mount;

Step 6: Set the Database to the Desired Incarnation
From the output in step 1, for this scenario 14 is the desired incarnation. 
RMAN > reset database to incarnation 14;

Step 7: Restore and Recover the Database
Now restore and recover the database to the same time as you specified when restoring the control file: 
RMAN> restore database until time
"to_date('28-aug-2012 08:22:00','dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database until time
"to_date('28-aug-2012 08:22:00','dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database open resetlogs;

When successful, you should see this message: 
Statement processed

Note Y ou can restore until a time, SCN, sequence, or restore point. The following code shows what you would see on your screen when you perform a NOARCHIVELOG database recovery. 
SQL>
SQL> CONNECT INTERNAL
Password:
Connected.
SQL> SHUTDOWN ABORT
ORACLE instance shut down.
SQL> HOST
Microsoft(R) Windows NT(TM)
(C) Copyright 1985-1996 Microsoft Corp.

C:\> COPY C:\DIGITAL\BACKUP\*.* C:\ORANT\DATABASE\
C:\digital\backup\Usr1orcl.ora
C:\digital\backup\Rbs1orcl.ora
C:\digital\backup\Tmp1orcl.ora
C:\digital\backup\Sys1orcl.ora
C:\digital\backup\Oem1orcl.ora
C:\digital\backup\Ctl1orcl.ora
C:\digital\backup\Log1orcl.ora
C:\digital\backup\Log2orcl.ora
C:\digital\backup\Log3orcl.ora
C:\digital\backup\Log4orcl.ora
C:\digital\backup\Initorcl.ora
 11 file(s) copied.
 
C:\>EXIT
SQL> STARTUP OPEN PFILE=C:\ORANT\DATABASE\INTIORCL.ORA
ORACLE instance started.
Total System Global Area                         15077376 bytes
Fixed Size                                          49152 bytes
Variable Size                                    12906496 bytes
Database Buffers                                  2048000 bytes
Redo Buffers                                        73728 bytes
Database mounted.
Database opened.
SQL>

The next lesson explains how to restore files to a different location after a media failure.

Recovering Noarchivelog Database - Quiz

Click the Quiz link below to review your understanding of recovering a NOARCHIVELOG database.
Recovering Noarchivelog Database - Quiz