Backup Options   «Prev  Next»

Lesson 4 Implications of NOARCHIVELOG database recovery
Objective Explain the recovery implications for a NOARCHIVELOG database.

Database Recovery Implications of NOARCHIVELOG

To recover a NOARCHIVELOG database, the only available option is to restore the database from a consistent closed database backup. Since no redo logs after the last backup can be applied, the database can only be recovered to the point in time when the last backup was made. All changes made after the closed database backup will be lost and must be reentered manually by users. Thus, it becomes the DBA's responsibility to inform users and management about the risk of operating a database in NOARCHIVELOG mode.

Advantages of NOARCHIVELOG mode

Recovering a database in NOARCHIVELOG mode is easy. Since you only have to restore all the files from a consistent whole database backup, the risks of recovery are minimal. At the same time, to ensure a valid backup, be careful that you do not restore the wrong backup copy, overwrite the backup, or shut down the database before restore. These risks can be greatly reduced by training the DBA properly or by setting up some automated procedures using batch files.
Recovering the database can be a quick procedure. The time required for recovery is determined by how fast your hardware can copy all the essential files.

How to make a Batch File by using the trace file

For a large database, you may have dozens of datafiles. It is rather cumbersome to copy them one by one when you perform backup or restore, but you can establish a procedure to customize these processes. One thing you can do to make your life easier is to create a batch file to include the copy commands. Each time you need to make a backup or restore a NOARCHIVELOG database, you can run this batch file.
In order to include all the database files in the batch file, you create a trace file by issuing the
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

statement. Locate this trace file in the ORACLE_HOME\RDBMS80\TRACE folder, open it with an operating system editor and find the part you are interested in.

MAXINSTANCES 16
LOGFILE
  GROUP 1 'C:\ORANT\DATABASE\LOG4ORCL.ORA'  SIZE 1M,
  GROUP 2 'C:\ORANT\DATABASE\LOG3ORCL.ORA'  SIZE 1M,
  GROUP 3 'C:\ORANT\DATABASE\LOG2ORCL.ORA'  SIZE 1M,
  GROUP 4 'C:\ORANT\DATABASE\LOG1ORCL.ORA'  SIZE 1M
DATAFILE
  'C:\ORANT\DATABASE\SYS1ORCL.ORA',
  'C:\ORANT\DATABASE\USR1ORCL.ORA',
  'C:\ORANT\DATABASE\RBS1ORCL.ORA',
  'C:\ORANT\DATABASE\TMP1ORCL.ORA',
  'C:\ORANT\DATABASE\OEM1ORCL.ORA';

Then you edit it and save it as a batch file (such as DO_BACKUP.BAT) in your file system. .
View the code below to see an example.
COPY C:\ORANT\DATABASE\LOG4ORCL.ORA D:\ORCL_BCKUP\LOG4ORCL.ORA
COPY C:\ORANT\DATABASE\LOG3ORCL.ORA D:\ORCL_BCKUP\LOG3ORCL.ORA 
COPY C:\ORANT\DATABASE\LOG2ORCL.ORA D:\ORCL_BCKUP\LOG2ORCL.ORA
COPY C:\ORANT\DATABASE\LOG1ORCL.ORA D:\ORCL_BCKUP\LOG1ORCL.ORA
COPY C:\ORANT\DATABASE\SYS1ORCL.ORA D:\ORCL_BCKUP\SYS1ORCL.ORA
COPY C:\ORANT\DATABASE\USR1ORCL.ORA D:\ORCL_BCKUP\USR1ORCL.ORA
COPY C:\ORANT\DATABASE\RBS1ORCL.ORA D:\ORCL_BCKUP\RBS1ORCL.ORA
COPY C:\ORANT\DATABASE\TMP1ORCL.ORA D:\ORCL_BCKUP\TMP1ORCL.ORA
COPY C:\ORANT\DATABASE\OEM1ORCL.ORA D:\ORCL_BCKUP\OEM1ORCL.ORA 
COPY C:\ORANT\DATABASE\INITORCL.ORA D:\ORCL_BCKUP\INITORCL.ORA
COPY C:\ORANT\DATABASE\CTL1ORCL.ORA D:\ORCL_BCKUP\CTL1ORCL.ORA

Do not forget to add 1) the control files, 2) parameter files, and 3) the password file in your batch file, otherwise you will not get a complete backup. These three files are not included in the trace file. You can create a similar batch file to perform restore.
If you change the structure of these files, for instance, if you move one file to a different location, remember to edit the batch file to reflect the change.

Disadvantages of recovering Database in NOARCHIVELOG mode

There are two main disadvantages to recovering a database in NOARCHIVELOG mode. First, because all data entered by the user since the last backup will be lost, the data must be reapplied manually. Second, all the datafiles have to be restored from the last whole closed database backup, even if only one of them is damaged. The next lesson describes the steps to recover a NOARCHIVELOG database.