TECHNOLOGY: Recovery

No data loss in NOARCHIVELOG mode

Find out how complete recovery and also cancel-based, time-based or change-based incomplete recovery can be performed on the Database running in noarchivelog mode.

by Dragoslav Gnjatovic

If you are a DBA who’s deciding between ARCHIVELOG and NOARCHIVELOG mode you must know implications of running the database in both of them. If you choose NOARCHIVELOG mode you cannot use some of data recovery techniques. These include complete and point-in-time recovery.

However, there are situations when it is possible to use these techniques on the database running in NOARCHIVELOG mode. In these situations, you can save a lot of data, thus becoming an Oracle DBA hero, who was able to perform complete or point-in-time recovery on the database running in NOARCHIVELOG mode. These are the reasons why you should read this article.

This article provides example on how and when to use complete and point-in-time recovery on the database running in NOARCHIVELOG mode.

Real world situations

Many times on the discussion forums I’ve seen something like that:

§ Today at 11 am, there is a lost or deleted data file in my database. The error received upon startup is as follows:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file.
My database is running in NOARCHIVELOG mode. The last backup was taken yesterday at 10 pm. What can I do?

§ Today at 11 am, there is a table dropped by user error in the database. My database is running in NOARCHIVELOG mode. The last backup was taken yesterday at 10 pm. Can I do recovery to the point in time?

And the answers are always the same:

Because the database is running in NOARCHIVELOG mode, a full database restore from the most recent backup must be performed. Data entered into the database today, must be reentered, if possible.

But this doesn’t have to be the case! Maybe, you have chance not to lose your data.

In the rest of article I will explain how and when you can recover the database running in NOARCHIVELOG mode. I will use the above real world situations.

Recovering in NOARCHIVELOG mode

If you have one of the above situations or similar one, just before restoring the database to the last recent backup and losing all of your data entered between last backup and the point of failure, you should try the following:

Mount the database. Start RMAN and connect to the target database. Run command list backup and check what the system change number in the last backup is. The result of the RMAN list command is shown in the Listing 1.

Code Listing 1: Example of backup list

RMAN> list backup;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

4 Full 625.66M DISK 00:01:06 17-JUL-07

BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20070717T004725

Piece Name: D:\DATABASE\ORCL\ORCL\RMANBACKUP\FULL_BACKUP05IN110U_1_1.BCK

List of Datafiles in backup set 4

File LV Type Ckp SCN Ckp Time Name

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

1 Full 5467444 17-JUL-07 D:\DATABASE\ORCL\ORCL\DATAFILE\O1_MF_SYSTEM_

2RRYF1NZ_.DBF

2 Full 5467444 17-JUL-07 D:\DATABASE\ORCL\ORCL\DATAFILE\O1_MF_UNDOTBS

1_2RRYG70H_.DBF

3 Full 5467444 17-JUL-07 D:\DATABASE\ORCL\ORCL\DATAFILE\O1_MF_SYSAUX_

2RRYGGQX_.DBF

4 Full 5467444 17-JUL-07 D:\DATABASE\ORCL\ORCL\DATAFILE\O1_MF_USERS_3

BBM5WH4_.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

5 Full 6.17M DISK 00:00:03 17-JUL-07

BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20070717T004725

Piece Name: D:\DATABASE\ORCL\ORCL\RMANBACKUP\FULL_BACKUP06IN113A_1_1.BCK

Control File Included: Ckp SCN: 5467444 Ckp time: 17-JUL-07

SPFILE Included: Modification time: 04-MAR-07

Run SQLPLUS and connect to the database. Check your v$log view. The result is shown in the Listing 2.

Code Listing 2: Example of online log groups

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 02:29:57 2007

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

Enter user-name: /as sysdba

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select group#, sequence#, first_change#, status from v$log;

GROUP# SEQUENCE# FIRST_CHANGE# STATUS

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

1 928 5469182 CURRENT

3 927 5469163 INACTIVE

2 926 5467100 INACTIVE

Look at the column Ckp SCN in the Listing 1. All database files and the control file are backed up with SCN (System Change Number) 5467444. In order to perform complete recovery you must have all the changes greater or equal to the SCN from your last recent backup. In order to perform incomplete recovery you must have the changes greater or equal to the SCN from your last recent backup to the SCN from point in time you wish to recover to. In the above example, the first SCN needed for recovery is 5467444. From the Listing 2 you can see the SCN 5467444 is containing in the redo log group 2, in the log sequence 926. That means, all changes that have been made from the most recent backup onwards are in the online redo logs. And that means the lost data file can be restored and recovered. Also, cancel-based, time-based or change-based incomplete recovery on the database can be performed.

Complete recovery in NOARCHIVELOG mode

Let’s consider the first situation where we lost the datafile.

Trying to startup the database results with error ORA-01157, as shown in the Listing 3.

Code Listing 3: ORA-01157 upon startup of the database

SQL> startup

ORACLE instance started.

Total System Global Area 62914560 bytes

Fixed Size 1247324 bytes

Variable Size 54527908 bytes

Database Buffers 4194304 bytes

Redo Buffers 2945024 bytes

Database mounted.

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

ORA-01110: data file 4:

'D:\DATABASE\ORCL\ORCL\DATAFILE\O1_MF_USERS_39TLLJJ3_.DBF'

Now, start RMAN and connect to the target database. From the RMAN prompt issue the command list backup (Listing 1). After that, start SQLPLUS, connect to the database and check your v$log view (Listing 2). If you have a lucky day, all changes from the most recent backup will be in the online redo logs, like in our example. If this is the case, you can restore and recover the lost datafile. The command and the results of restore operation on the lost datafile are given in the Listing 4.

Code Listing 4: Restoring the lost datafile

RMAN> restore datafile 4;

Starting restore at 18-JUL-07

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to D:\DATABASE\ORCL\ORCL\DATAFILE\O1_MF_USERS_2RRYH45C_

.DBF

channel ORA_DISK_1: reading from backup piece D:\DATABASE\ORCL\ORCL\RMANBACKUP\F

ULL_BACKUP05IN110U_1_1.BCK

channel ORA_DISK_1: restored backup piece 1

piece handle=D:\DATABASE\ORCL\ORCL\RMANBACKUP\FULL_BACKUP05IN110U_1_1.BCK tag=TA

G20070717T004725

channel ORA_DISK_1: restore complete, elapsed time: 00:00:36

Finished restore at 18-JUL-07

After the lost datafile is successfully restored you can perform complete recovery. The command and the results of recovery operation on the lost datafile are shown in the Listing 5.

Code Listing 5: Complete recovery of the lost datafile

RMAN> recover datafile 4;

Starting recover at 18-JUL-07

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 18-JUL-07

Finally, after the lost datafile is successfully recovered, you can open the database:

SQL> alter database open;

Database altered.

As you can see, the complete recovery operation is successfully performed on the database running in NOARCHIVELOG mode.

Incomplete recovery in NOARCHIVELOG mode

Let’s consider the second situation where table is dropped by user error and the database is running in NOARCHIVELOG mode.

List backups, and check v$log (see Listing 1, Listing 2).

If all changes, from the most recent backup to the point you wish to recover to, are stored in the groups of the online redo logs, like in our example, you can restore the database and also you can perform incomplete recovery. The command and the results of restore operation on the database are given in the Listing 6.

Code Listing 6: Restoring the database

RMAN> restore database;

Starting restore at 17-JUL-07

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=210 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to D:\DATABASE\ORCL\ORCL\DATAFILE\O1_MF_SYSTEM_2RRYF1NZ

_.DBF

restoring datafile 00002 to D:\DATABASE\ORCL\ORCL\DATAFILE\O1_MF_UNDOTBS1_2RRYG7

0H_.DBF

restoring datafile 00003 to D:\DATABASE\ORCL\ORCL\DATAFILE\O1_MF_SYSAUX_2RRYGGQX

_.DBF

restoring datafile 00004 to D:\DATABASE\ORCL\ORCL\DATAFILE\O1_MF_USERS_2RRYH45C_

.DBF

channel ORA_DISK_1: reading from backup piece D:\DATABASE\ORCL\ORCL\RMANBACKUP\F

ULL_BACKUP05IN110U_1_1.BCK

channel ORA_DISK_1: restored backup piece 1

piece handle=D:\DATABASE\ORCL\ORCL\RMANBACKUP\FULL_BACKUP05IN110U_1_1.BCK tag=TA

G20070717T004725

channel ORA_DISK_1: restore complete, elapsed time: 00:00:56

Finished restore at 17-JUL-07

After the database is successfully restored you can perform incomplete recovery. In our example, incomplete recovery until SCN 5469165 which is a part of the log group 3 has been performed, as shown in the Listing 7 . In the same way you can recover until time. Also, you can recover the database from the SQLPLUS using command recover database until change.

Code Listing 7: Incomplete recovery until SCN

RMAN> recover database until scn 5469165;

Starting recover at 17-JUL-07

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:06

Finished recover at 17-JUL-07

Finally, after the database is restored and recovered, you can open the database with the resetlogs option:

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

RMAN>

As you can see, we successfully performed incomplete recovery on the database running in NOARCHIVELOG mode.

Now, assume the following scenario:

The database has a media failure on today at 9 am, destroying half of the datafiles as well as an online redo log group. Let’s assume, the redo log group 1 from our example is destroyed (see Listing 2). The database is running in NOARCHIVELOG mode. The last backup was taken yesterday at 10 pm.

Trying to startup the database results with error ORA-01157 as you can see in the Listing 8.

Code Listing 8: Failed to open the database

SQL> startup

ORACLE instance started.

Total System Global Area 62914560 bytes

Fixed Size 1247324 bytes

Variable Size 54527908 bytes

Database Buffers 4194304 bytes

Redo Buffers 2945024 bytes

Database mounted.

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

ORA-01110: data file 1:

'D:\DATABASE\ORCL\ORCL\DATAFILE\O1_MF_SYSTEM_2RRYF1NZ_.DBF'

Now list backups, and check v$log (see Listing 1, Listing 2).

The command and the results of restore operation on the database are given in the Listing 6.

Trying to perform complete recovery of the database, results in failure of recover command, because there is no the redo log group 1 as you can see in the Listing 9.

Code Listing 9: Failed to perform complete recovery of the database

RMAN> recover database;

Starting recover at 24-JUL-07

using channel ORA_DISK_1

starting media recovery

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/24/2007 00:21:41

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database reco

ver if needed

start

ORA-00283: recovery session canceled due to errors

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'D:\DATABASE\ORCL\ORCL\ONLINELOG\O1_MF_1_2RRYD

Z5C_.LOG'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

Because, SCN from the last backup is part of the online log group 2, all changes from redo log group 2 (sequence 926) and redo log group 3 (sequence 927) can be recovered. All changes from the redo log group 1 have been lost. In order to recover the database you can use RMAN command recover database until sequence or you can recover from SQLPLUS using command recover database until cancel.

From the Listing 1 you can see that the redo log group 1 is one with sequence 928. So incomplete recovery until the log sequence 928 can be performed. Usage and results of this operation are given in the Listing 10.

Code Listing 10: Incomplete recovery until log sequence

RMAN> recover database until sequence 928;

Starting recover at 24-JUL-07

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 24-JUL-07

Finally, after restore and recover of the database you can open the database with the reset logs option:

SQL> alter database open resetlogs;

Database altered.

The database is opened which means that we successfully performed incomplete recovery on the database running in NOARCHIVELOG mode.

Conclusion

In NOARCHIVELOG mode you cannot make backups of the database when it is open. In most situations it is impossible to use some of data recovery techniques such as complete and point-in-time recovery. Also you cannot use features of Oracle like Flashback Database and guaranteed restore points as more efficient alternatives to point-in-time recovery.

So for most applications, running in ARCHIVELOG mode is preferable then running in NOARCHIVELOG mode. However if your database running in NOARCHIVELOG mode and you lose your database files, don’t lose your mind. Try to recover as much data as possible. Recovering in NOARCHIVELOG mode is not impossible. If your redo logs are not rewritten since the last backup was taken, you will be able to recover. You can even maximize your chance to recover your database running in NOARCHIVELOG mode in the case of media failure or user error by adding more redo log groups, or by making redo log files bigger.

If the rate of change to your database is predictable, then you can even observe the size of your redo log files and number of redo log groups to maximize chance for recovering in NOARCHIVELOG mode.

Deciding between ARCHIVELOG and NOARCHIVELOG Mode: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/strategy003.htm#sthref127