Redo Log Files and Backups
Revision 4
J. Mike Rollins
rollins@alumni.wfu.edu
Introduction:

Redo log files record changes made to the database and are used by Oracle for system crash recovery. Archiving of redo log files is necessary for hot (on-line) backups, and is mandatory for point-in-time recovery. Redo log files are created upon database creation and addition ones can be added by the DBA. To enable archive redo logging, the init.ora file must be modified, the database needs to be altered, and filesystem space is required. The following explains a little about redo logs, how archive logging can be enabled, and how backups can be performed.

How are log files created?

When databases are created at least two redo log files are created. A paranoid DBA might create two groups of log files where group members are mirror images of each other. Redo log files are used to record data changes to the database. The redo log files are initially specified within the CREATE DATABASE command.
	CREATE DATABASE 
	. . . 
	LOGFILE	'/t02/oradata/MYDB/redo1.rdo' size 4m,
 		'/t03/oradata/MYDB/redo2.rdo' size 4m;
Or if redo log files are mirrored, you can have groups of redo log files.
        CREATE DATABASE . . . 
        LOGFILE	group 1 ('/t01/oradata/MYDB/redo1a.rdo',
                         '/t02/oradata/MYDB/redo1b.rdo') size 4m,
                group 2 ('/t03/oradata/MYDB/redo2a.rdo',
                         '/t04/oradata/MYDB/redo2b.rdo') size 4m;
If you need to add redo log files to an existing database, you can use ALTER DATABASE ADD LOGFILE command.

Why have redo log files?

Crash Recovery: Redo log files record changes made to the database. Databases can crash in many ways, such as a sudden power loss, a SHUTDOWN ABORT, or the death of an Oracle process. In these cases, redo log files can provide information about how to repair the database. During the ALTER DATABASE OPEN phase of database startup, the on-line redo log files are used for "crash recovery". This type of recover is generally handled by Oracle and does not require DBA intervention.

Point-In-Time Recovery: Redo log files contain information that can be useful for broader types of recover. Since they contain all the changes that brought the database to its current state, the redo logs can bring an old backup forward to any point in time. However, on-line redo log files are used in a circular fashion, so it is important to make a copy of each redo log file before it gets overwritten with new information. This can be done automatically with archive log mode.

Hot Backups: During a hot backup, a tablespace is writes are done in a special manner. During this time, tables residing on this tablespace can be modified, however, extra information about the change is written to the redo log files. After the tablespace backup is finished, normal on-line redo logging is resumed. Note that during a hot backup each datafile backup is from a different point in time. And, in some cases, the datafile itself could have been modified during the backup process. If all of these datafiles were restored, the database would be completely out of sync - each part would be from a different time. In this case, old copies of the on-line redo log files (archived redo logs) can be applied to each datafile to bring them all to a single point in time.

What is archive log mode?

Archive log mode simply means that a redo log file is copied (archived) to another location before being reused. This yields a complete history of all changes made to the database. How do I view the redo logging status of my database? To examine the status of the on-line redo log files, select the appropriate fields from the V$LOG view:
SVRMGR> select * from V$LOG;
GROUP#   THREAD#   ARC   STATUS     FIRST_TIME
------   -------   ---   --------   --------------------
     1         1   YES   INACTIVE   01/15/99 10:48:59
     2         1   NO    CURRENT    01/15/99 11:27:28
The V$LOGFILE view can be used to find the datafiles for the redo logs.
SVRMGR> select * from V$LOGFILE;
GROUP#  STATUS  MEMBER
------  ------  ------------------------------
     1          /t01/oradata/MYDB/redo.1a.rdo
     1          /t02/oradata/MYDB/redo.1b.rdo
     2          /t02/oradata/MYDB/redo.2a.rdo
     2          /t03/oradata/MYDB/redo.2b.rdo
4 rows selected.
To examine the current archive logging status, use the following command:
SVRMGR> archive log list;
Database log mode               Archive Mode
Automatic archival              Enabled
Archive destination             /t05/oradata/MYDB
Oldest online log sequence      53
Next log sequence to archive    54
Current log sequence            54
A listing of the archived log files can be retrieved from the V$LOG_HISTORY.
SVRMGR> select SEQUENCE#,TIME, ARCHIVE_NAME from V$LOG_HISTORY;
SEQUENCE#    TIME                 ARCHIVE_NAME
----------   ------------------   ----------------------------------------
        74   02/02/99 10:55:35    /t05/oradata/MYDB/T0001S0000000074.ARC
        73   02/01/99 15:00:04    /t05/oradata/MYDB/T0001S0000000073.ARC
        72   01/29/99 16:21:52    /t05/oradata/MYDB/T0001S0000000072.ARC
        71   01/29/99 16:21:49    /t05/oradata/MYDB/T0001S0000000071.ARC


How do I begin archiving?

Step 1: Modify the initSID.ora file.
log_archive_start = true
This parameter will cause the database to archive the log files automatically. This frees the DBA from having to manually archive them.
log_archive_dest = /t05/oradata/MYDB
Customize this parameter to collect all of the archive log files in a specific directory.
log_archive_format = "T%TS%S.ARC"
This parameter can be customized to specify a particular format for the archived log file. %T refers to the thread number, and %S refers to the sequence number. An example archive file name is as follows: /t05/oradata/MYDB/T0001S0000000052.ARC
Step 2: Alter Database.
To activate the init file changes, the database needs to be restarted. Furthermore, some changes need to be made that requires the database to be brought down.
First, log into Server Manager and shutdown the database.
{oracle} f2n2:/~oracle [ 80 ]%svrmgrl

Oracle Server Manager Release 2.3.3.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.3.1.0 - Production Release
PL/SQL Release 2.3.3.0.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> 
Second, startup the database in mount exclusive mode.
SVRMGR> startup exclusive mount mydb
ORACLE instance started.
Total System Global Area       4348448 bytes
Fixed Size                       38976 bytes
Variable Size                  4047328 bytes
Database Buffers                245760 bytes
Redo Buffers                     16384 bytes
Database mounted.
SVRMGR>
Third, alter the database into archivelog mode.
SVRMGR> alter database archivelog;
Statement processed.
SVRMGR> 
Last, shutdown and restart database.
SVRMGR> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup
ORACLE instance started.
Total System Global Area       4348448 bytes
Fixed Size                       38976 bytes
Variable Size                  4047328 bytes
Database Buffers                245760 bytes
Redo Buffers                     16384 bytes
Database mounted.
Database opened.


How can I identify the files that need to be backed up?

A backup of an oracle database must consist of the following files: Parameter files (initialization and configuration), Control files, Data files, Redo log files, and Archived log files. Some DBAs may also want to backup trace files and other oracle log files.

Parameter files: The initSID.ora file's default location is $ORACLE_HOME/dbs directory. The parameter file contains information about the memory configuration for Oracle's SGA. This file is a plain text file and is never modified by by oracle. Each database can have only one init file. The initSID.ora file can include a reference to a configuration file. And, one configuration file can be included by many oracle databases. The configuration file can be found by looking for a line like the following in the init file:
ifile = /oracle/admin/MYDB/pfile/configMYDB.ora

Control files: The best way to find the control files is too look in the init file and parameter file for the following entry:
control_files = (/t01/oradata/MYDB/ora_control1, 
                 /t02/oradata/MYDB/ora_control2, 
                 /t04/oradata/MYDB/ora_control3) 
The control files can also be seen by the V$CONTROLFILE view.
SVRMGR> select * from V$CONTROLFILE;
STATUS  NAME
------- ------------------------------------
        /t01/oradata/MYDB/ora_control1
        /t02/oradata/MYDB/ora_control2
        /t04/oradata/MYDB/ora_control3
3 rows selected.
The control files contain information about all the tablespaces that comprise the database. Because the control file is very important it is a good idea to have multiple copies. Oracle recommends at least three copies.

Data files: In a database, data is stored in tables, tables are stored in tablespaces, and tablespaces are stored on data files. The data files and corresponding tablespaces can be found in the DBA_DATA_FILES table.
SVRMGR> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

Tablespace      File
----------      ----------------------------------
TRAINDB         /oracle/oradata/MYDB/traindb_ts.dbf
USERS           /oracle/oradata/MYDB/users01.dbf
TOOLS           /oracle/oradata/MYDB/tools01.dbf
TEMP            /oracle/oradata/MYDB/temp01.dbf
Redo log files: The location of the redo log files can be found from the V$LOGFILE table:
SVRMGR> select member from v$logfile;
MEMBER                                                                          
------------------------------------
/oracle/oradata/MYDB/redoMYDB02.log                                             
/oracle/oradata/MYDB/redoMYDB01.log                                             
/oracle/oradata/MYDB/redoMYDB03.log        

Archived log files: The initSID.ora file contains two parameters that reveal the location and name format of the archived log files. The directory of the archived files is given by log_archive_dest. The format of the file name is determined by the log_archive_format parameter.

How do I make a cold backup?

A cold backup is comprised of three steps: Shutting down the server, copying the files identified in the previous section, and starting up the server.

Shutdown and Startup: Oracle databases can be administered by the server manager program. On Unix, the command for the sever manager is svrmgrl. Oracle8 for NT uses svrmgr30. Server manager can be passed an SQL command file on both Unix and NT.

svrmgrl command=@shutdown.sql

Sample shutdown.sql and startup.sql scripts are as follows: Copy: On Unix, one could use the tar, cpio, or other file copy/archive command to backup the datafiles. For ADSM, one uses the dsmc command for archiving the datafiles. A simple backup script using the dsmc command directly is shown here.
svrmgrl command=@shutdown.sql
dsmc archive -des="Oracle Backup" /t01/oradata/MYDB/ora_control1
dsmc archive -des="Oracle Backup" /t01/oradata/MYDB/system.01.dbf
...
dsmc inc -des="Oracle Backup" "/t05/oradata/MYDB/T*"
svrmgrl command=@startup.sql


How do I make a hot backup?

The database must be in archive log mode to perform a hot backup. Since the database is in use during the backup, the backup may contain copies of tablespaces that are hours off from each other. Furthermore, the backup may contain datafiles that were modified during the backup copying process. When these mismatched datafiles are restored, the database will be so out of sync that it would be useless. However, the archived redo logs can be applied and will bring the database up to date.

To begin a backup of a tablespace the DBA should issue the command ALTER TABLESPACE NAME BEGIN BACKUP. From here, buffers are flushed, a checkpoint is performed and the tablespace is flagged as being in hot backup mode. Normally, when a datafile is updated, only a small amount of information is written to the redo log files. However, during a hot backup a simple change to the datafile will cause an entire oracle block from the data file to be written to the redo log file. This extra information will allow Oracle to roll the datafile forward when recover is performed. When the command ALTER TABLESPACE NAME END BACKUP is issued normal logging is resumed. The backup status of tablespaces can be seen from the view V$BACKUP.

The DBA_DATA_FILES table can be used to find the tablespace names and on which datafile they reside. Once a list of these are made a process can be formulated to perform a backup.
SVRMGR> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

Tablespace      File
----------      ----------------------------------
USERS           /oracle/oradata/MYDB/users01.dbf
TOOLS           /oracle/oradata/MYDB/tools01.dbf
TOOLS           /oracle/oradata/MYDB/tools02.dbf
TEMP            /oracle/oradata/MYDB/temp01.dbf
Using ALTER TABLESPACE BEGIN/END BACKUP, the following algorithm can be used to backup these datafiles. Note that a tablespace can sometimes span multiple datafiles.
SVRMGR: ALTER TABLESPACE USERS BEGIN BACKUP
backup: /oracle/oradata/MYDB/users01.dbf
SVRMGR: ALTER TABLESPACE USERS END BACKUP
SVRMGR: ALTER TABLESPACE TOOLS BEGIN BACKUP
backup: /oracle/oradata/MYDB/tools01.dbf
backup: /oracle/oradata/MYDB/tools02.dbf
SVRMGR: ALTER TABLESPACE TOOLS END BACKUP
SVRMGR: ALTER TABLESPACE TEMP BEGIN BACKUP
backup: /oracle/oradata/MYDB/temp01.dbf
SVRMGR: ALTER TABLESPACE TEMP END BACKUP
Let's assume we have a script called begin_backup which will alter a tablespace into backup mode and a script end_backup which will alter a tablespace out of backup mode. Let's also assume there is a script adsm which will backup a datafile via ADSM/dsmc commands. Here is a korn shell script that demonstrates how to backup a database.
#   Archive the parameter file

./adsm /t01/home/oracle/product/7.3.3/dbs/initMYDB.ora

#   Archive each tablespace / datafile

./begin_backup SYSTEM
./adsm /t01/oradata/MYDB/system.01.dbf
./end_backup SYSTEM

./begin_backup RBS
./adsm /t04/oradata/MYDB/rbs01.dbf
./end_backup RBS

   . . .
   . . .
   . . .

#    Flush the log files so that the latest changes will be written to the archive log files.
	
svrmgrl <<END
connect internal
alter system switch logfile;
alter system switch logfile;
END

#   Copy the on-line redo log files.  These files should have no important 
#   information, however, they are needed to restore the structure of the 
#   database.

./adsm /t01/oradata/MYDB/redo.1.rdo
./adsm /t02/oradata/MYDB/redo.2.rdo

#   Copy the archived logfiles from the log_archive_dest parameter

./adsm /archive/log/files/directory/*

#   Create a backup control file.  This is not an actual copy of the 
#   control file, but a special "backup control file".

svrmgrl  <<END
connect internal
alter database backup controlfile to '/backup/control/file/directory/controlfile.backup';
exit
END

./adsm /backup/control/file/directory/controlfile.backup

#   Lastly, just because I am paranoid, I make a backup of the in-use
#   control files.  I just have a feeling that they might someday be
#   helpful. 

./adsm /t01/oradata/MYDB/ora_control1
./adsm /t02/oradata/MYDB/ora_control2
./adsm /t04/oradata/MYDB/ora_control3
The status of the hot backup can be found by querying the V$BACKUP table. To show the tablespace names and the datafiles, the query can be joined with information from DBA_DATA_FILES and V$DATAFILE. ACTIVE means the tablespace is in backup mode. NOT ACTIVE implies the table is in its normal state.
SVRMGR>  select  b.status,   f.TABLESPACE_NAME, d.NAME
2>         from  v$backup b, dba_data_files f,  v$datafile d, 
3>        where  b.file# = d.file# 
4>          and  d.NAME  = f.FILE_NAME;

STATUS          TABLESPACE_NAME         NAME
------------    ------------------      ----------------------------------
NOT ACTIVE      SYSTEM                  /t01/oradata/MYDB/system.01.dbf
NOT ACTIVE      RBS                     /t04/oradata/MYDB/rbs01.dbf
NOT ACTIVE      TEMP                    /t03/oradata/MYDB/temp01.dbf
NOT ACTIVE      TOOLS                   /t03/oradata/MYDB/users01.dbf
ACTIVE          USERS                   /t04/oradata/MYDB/users01.dbf

5 rows selected.