Oracle: OFA Part 8

Dear Readers,

My name is Franz Devantier, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g) Security DBA.  I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks. 

OFA – Optimal Flexible Architecture

(Part 8, Oracle Database Files)

Oracle Database Files:

Database files need to be separated from all the other file types on a system.  Database files have a different lifespan to other Oracle files.  Database files have a different backup strategy to other Oracle files, especially if you are running cold backups.

OFA Standard 10:

• Database files should be named, so that they are easily distinguishable from other files.
• Database files should be names, so that the database that they are associated with is hinted at in the name.
• Database files should be named so that the type, for example control files, data files, and redo log files are apparent.
• Database files should be named in such a way, that the association of table-spaces to datafiles is apparent.

You can accomplish this by naming the database files like this:

./mount_point/oradata/<db_name>/control_n.ctl         - control files, n denotes the number

./mount_point/oradata/<db_name>/redo_n.log             - redo log files

./mount_point/oradata/<db_name>/<db_name>_tablespace_name_n.dbf

or

./mount_point/oradata/<db_name>/tablespace_name_n.dbf                 - database files, are distinguished by the extension of .dbf

You can optionally include the name of the database in the above file descriptions, for example: ./<db_name>_control_n.ctl.  This is optional, because you already have the file in the ./<db_name>/ subdirectory.  However sometimes when you have multiple databases on one box, you may find a few database files, entered under the wrong directory structure.  You can pick this up through RMAN, or by examining the tablespaces and underlying datafiles in detail.  This situation could have disastrous results, when you start migrating databases to other computers, and cleaning up the directories.

There should be no other files stored in this directory.  If you would prefer to use another directory name instead of “oradata”, then you are free to do so.  However it must be immediately apparent that the sub-directories, hold data-files.

OFA Standard  11

• Control Files: Oracle control files should be duplexed, and best practices suggest that their should be three copies of the control file.
• Redo log files: Should be encoded with a distinguishing key to denote the redo files group and sequence.
• Data Files: use the optional <db_name>, and the tablespace name as the root of the datafile name, with a distinguishing 2 digit key appended to the name.

The control files should be stored on different mount points or disks, in order to protect them.  If one control file is lost, then the remaining control files are still duplexed.

A distinguishing key could be for example  redo1a.log, which says that this is the first redo group, and the first sequence being ‘a’.  Like wise: redo0201.log, or <db_name>_redo01b.log

Redo logs should be duplexed, and the minimal size of each redo log is 100MB,  by default Oracle will create three unduplexed redo logs of 50MB each.  So it is easy to see if they are still at the default, or been brought into compliance with standards.

A typical datafile name could by system01.dbf, or <db_name>_system03.dbf. typically 99 values for the two digits, ie 01 to 99 would be sufficient.  If those are not enough, then you can add the letters of the alphabet into the equation as well, for example  ./usersc7.dbf

There are two requirements for properly setting up and storing your datafiles:

• The directories actual name does not matter, as long as it is consistent with the names of other similar directories.
• The directory name must be carefully chosen to properly represent the contents of the directory.

The depth of the subdirectory structure does not matter when it comes to I/O balancing; as long as similar I/O balanced subdirectories, have the same subdirectory depth.

Franz Devantier,

Need a database health check?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report:  The report will give you all the information you need to start making a nice long-term residual income stream for yourself.