No data loss in NOARCHIVELOG mode

One part of ORACLE9i documentation refering to the ARCHIVELOG and NOARCHIVELOG mode of database operation reads as follows:

“The choice of whether to enable the archiving of filled groups of online redo log files depends of the availability and reliability requirements of the application running on the database. If you cannot afford to lose any data in your database in the event of a disk failure, use ARCHIVELOG mode. The archiving of filled online redo log files can require you to perform extra administrative operations.

NOARCHIVELOG mode protects a database only from instance failure, but not from media failure. Only the most recent changes made to the database, which are stored in the groups of the online redo log, are available for instance recovery. In other words, if you are using NOARCHIVELOG mode, you can only restore (not recover) the database to the point of the most recent full database backup. You cannot recover subsequent transactions.”

I shall demonstrate that the last sentence does not necessarily have to be true, i.e. that there are situations when the subsequent transactions CAN be recovered even under the above mentioned circumstances.

Let’s consider the following scenario:

The database is running in NOARCHIVELOG mode. Every night, the database is shut down, and complete, cold backup (offline backup) is performed.

When the last backup was taken, the current log sequence was 152:

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ---------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 151 10485760 1 NO INACTIVE

1206902 03-MAY-05

2 1 152 10485760 1 NO CURRENT

1227015 04-MAY-05

3 1 150 10485760 1 NO INACTIVE

1206697 03-MAY-05

Today at couple of coffee breaks, there is a lost or deleted data file in the database. The error received upon startup is as follows:

SQL> startup

ORACLE instance started.

Total System Global Area 30174764 bytes

Fixed Size 282156 bytes

Variable Size 25165824 bytes

Database Buffers 4194304 bytes

Redo Buffers 532480 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 8 - see DBWR trace file

ORA-01110: data file 8: 'E:\DATABASE\ORADATA\MB\USERS01.DBF'

Because the database is running in NOARCHIVELOG mode, a full database restore from previous night must be performed. Data entered into the database today, must be reentered, if possible. But this doesn’t have to be the case?!

Let us see, after the data file is lost, what the current log sequence is:

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 154 10485760 1 NO CURRENT

1227482 04-MAY-05

2 1 152 10485760 1 NO ACTIVE

1227015 04-MAY-05

3 1 153 10485760 1 NO ACTIVE

1227481 04-MAY-05

Yeah! The current log sequence is 154. Because there are 3 redo log groups, and group 2 with log sequence 152 is not rewritten, data file 8 can be restored and recovered:

C:\>copy E:\ColdBackup\MB\USERS01.DBF E:\DATABASE\oradata\MB\USERS01.DBF

1 file(s) copied.

SQL> recover datafile 8;

Media recovery complete.

SQL> alter database open;

Database altered.

In the same way, database can be restored and cancel-based, time-based or change-based incomplete recovery can be performed.

The point is, if you want your database to be running in NOARCHIVELOG mode, you can still have NO DATA LOSE guaranteed.

If you can garantee, the redo log group that was current when last backup was taken, will not be overwritten until the next full offline backup of database, than NO DATA LOSE is garanteed.

This can be achieved either by adding more redo log groups, or by making redo log files big long enough, so that the redo log group that was current when last backup was taken is not overwritten between to backups.