The Application DBA  

Database File Naming

Naming Conventions

There have been many different naming conventions used over the years by Oracle and others for naming the various Oracle database data files. There are only three basic database file types: control files, redo log files, and database files. The OFA specified naming guidelines, and specifically OFA rule 6 specified the naming and format. Unfortunately the guidelines for redo log naming were poorly chosen by terminating the file with a ".log" extension. This is easily confused with the many text log files created by various utilities. This is the only area where we diverge from OFA "examples". However we still meet rule 6's requirements along with the spirit of the OFA's naming guidelines. Therefore the following general naming conventions should be followed and will be detailed in the following sections:

 

File Type

Format

Notes

control file

control[mm].ctl

mm – 01,02,…

redo logs

log[mm][n].rdo

mm – 01,02,… (group);

n – a,b,c,… (member)

data files

[tablespace name][TAB|IDX|LOB][mm].dbf

mm – 01,02,…

archive redo logs

[SID]_%t_%s.arc

10g: [SID]_%t_%s_%r.arc

SID – Database SID, %t – thread, %s – log sequence, %r – reset logs ID (10g only)

 

Controlfile Allocation

Control files are the way the Oracle database keeps track of structural information about database files, database consistency and backup information and are therefore critical to the basic functioning of the database. It is strongly recommended that at least three copies of the control files be maintained (e.g. OFA guidelines as well) and that they be preferably kept on different physical volumes. The format for naming control files is:

control[mm].ctl where mm is a two digit sequentially incrementing number starting with 01. Three control files would then be named:

·       control01.ctl

·       control02.ctl

·       control03.ctl

Redo Log File Allocation

Oracle redo log files record information about all database changes and are critical to database recoverability from both instance failure and media failure. As we have said, the OFA naming guidelines here fail to take into account that files with a ".log" extension are generally considered to be informational text based files and not critical database recovery files. We feel the following is a better naming guideline that helps identify the type of file and accommodates multiple groups and group members:

log[mm][n].rdo where mm is a two digit sequentially increasing number starting with 01 and representing the redo log file group number, and n is a sequentially increasing letter starting with a and representing the redo log file group's member.

The following example shows three redo log files groups with two members each:

 

Group 1

Member 1

log01a.rdo

 

Member 2

log01b.rdo

Group 2

Member 1

log02a.rdo

 

Member 2

log02b.rdo

Group 3

Member 1

log03a.rdo

 

Member 2

log03b.rdo

 

Archivelog Format

Closely related to redo log file format is archivelog file format. Archivelog files are created by the ARCH process from redo log files once they have filled if the database is in archivelog mode. The naming format is controlled by the init.ora parameter "log_archive_format" and the following is our recommended format:

LOG_ARCHIVE_FORMAT = [SID]_%t_%s.arc, where [SID] is the database system identifier.

In Oracle 10g the format changes slightly with the addition of the %r parameter[1]:

LOG_ARCHIVE_FORMAT = [SID]_%t_%s_%r.arc, where [SID] is the database system identifier.


 

[1] See Metalink Note: 274302.1

 

Copyright 1998-2013 AppsDBA Consulting. All Rights Reserved.