Oracle7 Administrator's Reference for UNIX

Naming Conventions

A careful naming strategy for database files eliminates data administration problems. OFA outlines a number of naming and placement conventions described in this section.

Attention: The OFA rule numbers provided correspond to the original OFA recommendations published in The OFA Standard: Oracle7 for Open Systems, by Cary V. Millsap.

Naming Mount Points

OFA Rule 1: Naming Mount Points

Name all mount points using the syntax /pm. For example:

where:

p is a string constant
m is a unique fixed-length key used to distinguish each mount point

For example:

/u01 and /u02, or /disk01 and /disk02

Naming Mount Points for Very Large Databases (VLDBs)

If each disk drive contains database files from one application, and you have enough drives for each database to ensure that there will be no I/O bottleneck, name mount points matching using the syntax /qdm. For example:

where:

q is a string denoting that Oracle data is stored here
d is the value of the initialization parameter DB_NAME (synonymous with the instance sid for single-instance databases)
m is a unique, fixed-length key distinguishing one mount from another

For example, /ora/test01 indicates that data from the Oracle database test is stored on this drive.

Naming Directories

OFA Rule 2: Naming Home Directories

Name home directories using the syntax /pm/h/u. For example:

where:

pm is a mount point name
h is a standard directory name
u is the name of the owner of the directory

For example, /u01/app/oracle is an oracle software owner home directory and /u01/app/applmgr is an Oracle applications software owner home directory.

Placing home directories at the same level in the UNIX file system allows home directories on different mount points. Refer to the collection of login homes with a single pattern, such as /*/app/*.

OFA Rule 3: Referring to Pathnames

Refer to pathnames only in files designed to store them, such as /etc/passwd and /etc/oratab. Refer to group memberships only in the /etc/group file.

OFA Rule 4: Software Directories

Store each version of the Oracle7 Server software in a directory matching the pattern h/product/v. For example:

where:

h is the login home directory of the Oracle software owner
v represents the version of the software

For example: /u01/app/oracle/product/7.3.2 names the start of the directory structure where the Oracle7 Server files are located. Set the environment variable ORACLE_HOME to this directory.

Naming Files

OFA Rule 5: Administration Files

Store database administration files in the subdirectories of h/admin/d. For example:

where:

h is the Oracle software owner's home directory
d is the database $DB_NAME

The subdirectories are:

adhoc ad hoc SQL scripts for a given database
arch archived redo log files
adump audit files. Set AUDIT_FILE_DEST in configdb_name.ora to point here. Clean out this subdirectory periodically.
bdump background process trace files
cdump core dump files
create programs used to create the database
exp database export files
logbook files recording the status and history of the database
pfile instance parameter files
udump user SQL trace files

OFA Rule 6: Database Files

Name Oracle database files using the following syntax:

/pm/q/d/control.ctl control files
/pm/q/d/redon.log redo log files
/pm/q/d/tn.dbf data files

where:

pm is a mount point name described earlier in this chapter
q is a string distinguishing Oracle data from all other files. The q layer is normally named ORACLE or oradata, satisfying OFA requirement 10a.
d is the $DB_NAME of the database. Naming the d layer $DB_NAME satisfies Requirement 10b.
t is an Oracle tablespace name
n is a fixed-length key for a given file type

Do not store files other than a control, redo log or data file associated with database d in /pm/q/d.

OFA Rule 7: Separate Segments with Different Requirements

Separate groups of segments with different lifespans, I/O request demands, and backup frequencies across different tablespaces.

For each Oracle database, create the following special tablespaces, in addition to those needed for application segments:

SYSTEM data dictionary segments
TEMP temporary segments
RBS rollback segments
TOOLS general-purpose tools
USERS miscellaneous user segments

This method is effective because dictionary segments are never dropped, and no other segments that can be dropped are allowed in the SYSTEM tablespace. This ensures that the SYSTEM tablespace does not require a rebuild due to tablespace free space fragmentation.

Because rollback segments are not stored in tablespaces holding applications data, the administrator is not blocked from taking an applications tablespace offline for maintenance. The segments are partitioned physically by type, and the administrator can record and predict data growth rates without complicated tools.

Naming Tablespaces

OFA Rule 8: Naming Tablespaces

Name tablespaces descriptively using a maximum of eight characters.

Although Oracle7 tablespace names can be thirty characters long, portable UNIX file names are restricted to fourteen characters. The recommended standard for a data file basename is tn.dbf, where t is a tablespace name and n is a two-digit string.

Note: Do not embed reminders of the word "tablespace" in your tablespace names. Tablespaces are distinguishable by context, and names do not need to convey information about type.

Exploiting OFA Structure for Oracle Files

The following examples of UNIX syntax identify the classes of files:

/u[0-9][0-9] user data directories

/*/home/* user home directories

/*/app/* user application software directories

/*/app/applmgr Oracle applications software subtrees

/*/app/oracle/product Oracle Server software subtrees

/*/app/oracle/product/7.2.3 Oracle Server 7.2.3 distribution files

/*/app/oracle/admin/sab sab database administrative subtrees

/*/app/oracle/admin/sab/arch/* sab database archived log files

/*/oradata Oracle database directories

/*/oradata/sab/* sab database files

/*/oradata/sab/*.log sab database redo log files

OFA File-Mapping

Figure 2 - 1 is a file map of a sample OFA-compliant database, including each file's mount point, application, database, and tablespace. The file names indicate the file type (control, log, or data).

/ root mount point
u01/ 'user data' mount point #1
app/ subtree for app software
oracle/ home for oracle software owner
admin/ subtree for database admin files
TAR/ subtree for Support logs
db_name1/ admin subtree for db_name1 database
db_name2/ admin subtree for db_name2 database
db_name2/ admin subtree for db_name2 database
local/ subtree for local Oracle software
aps6/ an Oracle6 admin package
aps7/ an Oracle7 admin package
product/ distribution files
7.1.6/ ORACLE_HOME for 7.1.6 instances
7.3.2/ ORACLE_HOME for 7.3.2 instances
home/ subtree for login home directories
ltb/ home for a user
sbm/ home for a user
oradata/ subtree for Oracle data
db_name1/ subtree for db_name1 database files
db_name2/ subtree for db_name2 database files
u02/ 'user data' mount point #2
home/ subtree for login home directories
cvm/ home for a user
vrm/ home for a user
oradata/ subtree for Oracle data
db_name1/ subtree for db_name1 database files
db_name2/ subtree for db_name2 database files
u03/ 'user data' mount point #3
home/ subtree for login home directories
oradata/ subtree for Oracle data
db_name1/ subtree for db_name1 database files
db_name2/ subtree for db_name2 database files

Figure 2 - 1. Hierarchical Directory Listing for an OFA Installation

File-Mapping for Multi-Instance OFA Database

Multiple-instance databases (Oracle Parallel Server installations) have an additional guideline for file-mapping.

OFA Rule 10: Administrative Home for Oracle Parallel Server

When using Oracle Parallel Server, select one node to act as the Oracle administrative home for the cluster. The administrative home contains the administrative subtree defined

. Create and name subdirectories for each instance accessing the database within the bdump, cdump, logbook, pfile, and udump directories of ~/admin/d. Ensure the admin directory for the administrative home is mounted as the admin directory for every instance. For example:

u01/app/oracle/admin/sab/ administrative directory for sab database
adhoc/ directory for miscellaneous scripts
arch/ log archive dest for all instances
redo001.arc archived redo log file
bdump/ directory for background dump files
inst1/ background dump dest for inst1 instance
inst2/ background dump dest for inst2 instance
cdump/ directory for core dump files
inst1/ core dump dest for inst1 instance
inst2/ core dump dest for inst2 instance
create/ directory for creation scripts
1-rdbms.sql SQL script to create inst database
exp/ directory for exports
950920full.dmp Sept 20 full export dump file
export/ directory for export parfiles
import/ directory for import parfiles
logbook/ directory for inst logbook entries
inst1/ directory for inst1 instance reports
params.lst v$parameter report for inst1 instance
inst2/ directory for inst2 instance reports
params.lst v$parameter report for inst2 instance
user.lst dba_users report
pfile/ directory for instance parameter files
inst1/ directory for inst1 instance parameters
init instance parameters for inst1 instance
inst2/ directory for inst1 instance parameters
init instance parameters for inst2 instance
udump/ directory for user dump files
inst1/ user dump dest for inst1 instance
inst2/ user dump dest for inst1 instance parameters

Figure 2 - 2. Administrative Directory Structure for Dual-Instance Oracle Parallel Server

p	is a string constant
m	is a unique fixed-length key used to distinguish each mount point

q	is a string denoting that Oracle data is stored here
d	is the value of the initialization parameter DB_NAME (synonymous with the instance sid for single-instance databases)
m	is a unique, fixed-length key distinguishing one mount from another

pm	is a mount point name
h	is a standard directory name
u	is the name of the owner of the directory

h	is the login home directory of the Oracle software owner
v	represents the version of the software

h	is the Oracle software owner's home directory
d	is the database $DB_NAME

adhoc	ad hoc SQL scripts for a given database
arch	archived redo log files
adump	audit files. Set AUDIT_FILE_DEST in configdb_name.ora to point here. Clean out this subdirectory periodically.
bdump	background process trace files
cdump	core dump files
create	programs used to create the database
exp	database export files
logbook	files recording the status and history of the database
pfile	instance parameter files
udump	user SQL trace files

/pm/q/d/control.ctl	control files
/pm/q/d/redon.log	redo log files
/pm/q/d/tn.dbf	data files

pm	is a mount point name described earlier in this chapter
q	is a string distinguishing Oracle data from all other files. The q layer is normally named ORACLE or oradata, satisfying OFA requirement 10a.
d	is the $DB_NAME of the database. Naming the d layer $DB_NAME satisfies Requirement 10b.
t	is an Oracle tablespace name
n	is a fixed-length key for a given file type

SYSTEM	data dictionary segments
TEMP	temporary segments
RBS	rollback segments
TOOLS	general-purpose tools
USERS	miscellaneous user segments

/						root mount point
	u01/					'user data' mount point #1
		app/				subtree for app software
			oracle/			home for oracle software owner
				admin/		subtree for database admin files
					TAR/	subtree for Support logs
					db_name1/	admin subtree for db_name1 database
					db_name2/	admin subtree for db_name2 database
					db_name2/	admin subtree for db_name2 database
				local/		subtree for local Oracle software
					aps6/	an Oracle6 admin package
					aps7/	an Oracle7 admin package
				product/		distribution files
					7.1.6/	ORACLE_HOME for 7.1.6 instances
					7.3.2/	ORACLE_HOME for 7.3.2 instances
		home/				subtree for login home directories
			ltb/			home for a user
			sbm/			home for a user
		oradata/				subtree for Oracle data
			db_name1/			subtree for db_name1 database files
			db_name2/			subtree for db_name2 database files
	u02/					'user data' mount point #2
		home/				subtree for login home directories
			cvm/			home for a user
			vrm/			home for a user
		oradata/				subtree for Oracle data
			db_name1/			subtree for db_name1 database files
			db_name2/			subtree for db_name2 database files
	u03/					'user data' mount point #3
		home/				subtree for login home directories
		oradata/				subtree for Oracle data
			db_name1/			subtree for db_name1 database files
			db_name2/			subtree for db_name2 database files

u01/app/oracle/admin/sab/				administrative directory for sab database
	adhoc/			directory for miscellaneous scripts
	arch/			log archive dest for all instances
		redo001.arc		archived redo log file
	bdump/			directory for background dump files
		inst1/		background dump dest for inst1 instance
		inst2/		background dump dest for inst2 instance
	cdump/			directory for core dump files
		inst1/		core dump dest for inst1 instance
		inst2/		core dump dest for inst2 instance
	create/			directory for creation scripts
		1-rdbms.sql		SQL script to create inst database
	exp/			directory for exports
		950920full.dmp		Sept 20 full export dump file
		export/		directory for export parfiles
		import/		directory for import parfiles
	logbook/			directory for inst logbook entries
		inst1/		directory for inst1 instance reports
			params.lst	v$parameter report for inst1 instance
		inst2/		directory for inst2 instance reports
			params.lst	v$parameter report for inst2 instance
		user.lst		dba_users report
	pfile/			directory for instance parameter files
		inst1/		directory for inst1 instance parameters
			init	instance parameters for inst1 instance
		inst2/		directory for inst1 instance parameters
			init	instance parameters for inst2 instance
	udump/			directory for user dump files
		inst1/		user dump dest for inst1 instance
		inst2/		user dump dest for inst1 instance parameters