After I spent a lot of time trying to find a script to delete logs applied to the standby database, I decided to write my own. Script, or PL / SQL block run over DBMS_BACKUP_RESTORE package and run as the job on the operating system. In this particular case, logs that was applied and older than 7 days will be deleted.
declare cursor c_delete_logs is select recid, stamp, name, thread#, sequence#, resetlogs_change#, first_change#, block_size from v$archived_log where deleted = 'NO' and applied='YES' and completion_time < sysdate-7 order by recid desc;
r_delete_logs c_delete_logs%rowtype; res binary_integer; begin open c_delete_logs; loop fetch c_delete_logs into r_delete_logs; exit when c_delete_logs%notfound;
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 KeyType LV SizeDevice Type Elapsed Time Completion Time
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 Area62914560 bytes
Fixed Size1247324 bytes
Variable Size54527908 bytes
Database Buffers4194304 bytes
Redo Buffers2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
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
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
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 Area62914560 bytes
Fixed Size1247324 bytes
Variable Size54527908 bytes
Database Buffers4194304 bytes
Redo Buffers2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
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
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.
One part ofORACLE9i 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 requirementsof 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, andcomplete, cold backup (offline backup) is performed.
When the last backup was taken, the current log sequence was 152:
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 Area30174764 bytes
Fixed Size282156 bytes
Variable Size25165824 bytes
Database Buffers4194304 bytes
Redo Buffers532480 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:
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:
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 nextfull 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.
