Oracle: OFA Part 9

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 9, Exploiting the OFA Structure for Oracle Files)

You should be able to easily find any components, if the structure was set up according to standards from a UNIX/LINUX perspective.  Windows should not have strayed too far from the same outlines.

/*/home/* user home directories

/*/app/* user application software directories

/*/app/applmgr Oracle apps software subtrees

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

/*/app/oracle/product/9.0.1. Oracle Server distribution files

/*/app/oracle/admin/<db_name> database administrative subtrees

/*/oradata Oracle database directories

/*/oradata/<db_name>/* <db_name> database files, control files and redo logs, or at least the control files and redo logs, that are still in this directory structure after duplexing, and moving to other mount points, of the control and redo files.

The above represents an OFA compliant directory / file structure.  The directory structure is compliant, but remember that there are also the considerations of having the different structures on separate mount points or drives.  The question of I/O balancing etc.

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.

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.

Oracle: OFA Part 7

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 7, Oracle Software and Administrative Data)

In Unix/Linux; All Oracle software, and administrative data should reside in a subtree of the Oracle Owner’s login home directory.

You can set this directory to the environment variable $ORACLE_BASE.

OFA Standard  8:

·         You must be able to execute multiple versions of the applications software simultaneously.

·         Upgrading must be simple for the administrator and as transparent as possible.

You need to configure the directory structure in such a way, that Standard 8 is possible.  You can achieve this by creating a directory structure something like this:

/Oracle_software_user/product/version

For example:

/u02/oracle/product/10.1.0

and

/u02/oracle/product/10.2.0

So in this way the different versions are logically separated.  To describe this in another way is to say that the $ORACLE_BASE stays the same, but the $ORACLE_HOME changes.

In an OFA compliant site, you can install a new version of the Oracle software, by simply creating a new $ORACLE_HOME.  The installation wizard will prompt you for these values.

After an upgrade, you need to update the oratab file, and make sure that the .oraenv call in a users profile, points them to the new instance.

You should now be able to remove the old $ORACLE_HOME from the box.  Again you can manage this through Universal installer.

OFA Standard 9

• The only files in the product/version sub-tree, should be derived from the Oracle Distribution Media.
• Administrative information or files about one database, must be separated from other databases.

The lifespan of these files will match the lifespan of the version of Oracle.  Make sure that you don’t put any files in this structure that could potentially outlive the version of Oracle that you are running.

You can fulfil the administrative files requirement, by storing the administrative files in either $ORACLE_BASE/admin/<db_name>/admin_subdirectories

Or

$ORACLE_HOME/admin/<db_name>/admin_subdirectories

Admin subdirectories are:

• ./adhoc                                ad-hoc SQL scripts for the database

• ./adump              Audit trail trace files

• ./arch                    Archived redo log files,  only if you are not using a flash recovery area, and there is nowhere else to hose these files.  Preferably the archive logs, should be housed on a separate drive or mount point.

• ./bdump              alert<sid>.log is housed here, as well as your background trace files.  Of course in 11g it is housed in a subdirectory of $ORACLE_BASE/diag

• ./cdump               core dump files

• ./create                                programs used to create the database.  Universal installer will often dump its create files here as well.

• ./exp                     Your export dump files. Good practice to connect with symbolic links to mount points setup for this purpose.

• ./dpump              datapump dump files

• ./logbook            files recording the status and history of the database

• ./pfile                    instance parameter files, you can have a link from $ORACLE_HOME/dbs or $ORACLE_HOME/database, to these files.

• ./udump              user SQL trace files

You may also like to create a Service_request sub-directory, to record all the details of the service_requests that you have submitted.

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.

Oracle: OFA Part 6

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 6, Oracle Files)

One of the problems before OFA, was that some DBA’s were placing database files in $ORACLE_HOME/dbs.

Having data files, control files, and redo log files, in a sub-tree of the directory holding the Oracle software, caused a bottleneck on the server.  Essentially they were putting everything on a single disk.  Also imagine the difficulties that you could expect if you wanted to upgrade the Oracle data base software, and your data files were in a sub-tree, of the software directory structure.

So you can understand how the goals of OFA, was to separate software and data, and the distribution of files across multiple disk drives.   Files that fell into different categories were separated, to minimize the impact by operations on a specific file category on other file categories.

• Product files:  Oracle database software and tools supplied by Oracle.
• Administrative files: Metadata files, archived redo log files, database creation scripts, alert logs, trace files, export dump files, and parameter files.
• Local Software: Custom applications.
• Database Files: Control files, redo log files, and data files.

Standard   7:  The ability to consistently refer to objects, regardless of the infrastructure changes that have taken place.

Under this standard, we must classify Oracle files, according to different life-spans, maintenance schedules, and security requirements.

Product files, Administrative files, Local software, and database files must be located in different directory structures, and preferably different file systems or disk drives.

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.

Oracle: OFA Part 5

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 5, OFA Standard 7)

OFA Standard   7:  The ability to consistently refer to objects, regardless of the infrastructure changes that have taken place.

• Un-affected by the addition of disks, databases, data and users to the infrastructure.
• Un-affected by the movement of files around the environment.
• No maintenance on backup programs, and others needed, as a result of infrastructure changes.
• Zero maintenance required in terms of infrastructure changes
• Access to all the files, of a specific database, distributed over many file systems, and accessed from a central point dynamically.

Summary of the seven OFA standards

1. Sufficient space
2. Performance bottlenecks
3. Minimizing hardware costs
4. Minimizing the impact of disk failure
5. Home directories can be spread across multiple mount points, and the contents of one home directory can be distributed across several mount points.
6. It must be possible to add or move login home directories without having to revise programs that refer to them.
7. The ability to consistently refer to objects, regardless of the infrastructure changes that have taken place.

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.

Oracle: OFA Part 4

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 4, User Profiles & Zero Maintenance Administration)

An Oracle database user’s profile, should do three things for them:

1. Assign environment variables, so that the users shell can execute Oracle programs like SQL*Plus.
2. Assign a default instance name, to enable the user to connect to the database easily.
3. Execute the profile or environment file, that sets up the users profile, so that the user has all the access that is needed.

  

Ensure that oraenv, coraenv, and dbhome are in a neutral location, instead of $ORACLE_HOME/bin or %ORACLE_HOME%\bin

A location like for example: /var/opt/bin

By keeping these three files separate of the Oracle software home directory, you remove dependence on Oracle versions from your login profiles.

A user may go ahead and assign additional variables into the profile, to facilitate ease of use of the system.  A user can connect to different users by simply invoking .oraenv and giving an alternative SID, when promoted to.  In windows you can set the ORACLE_SID=<sid>, in order to achieve a similar effect.  However you will need to set your environment variables in windows for the correct results.

Zero Maintenance Administration

Once you have the scheduler running exports, datapumps, rman backups, and moving things to tape devices; the daily maintenance becomes a lot less.  However in the case of cold backups, if you don’t update the backup program with the latest file systems to backup, you will not end up with a complete database backup, and your backups will be invalidated.

Having a tried and proven program for doing health checks on the database, and those parts of the server that are directly influenced is important.  You need to be able to pick up small changes and add these changes into any automated programs, like backups.

Having a routine of planned maintenance on a regular basis can help to prevent you from putting your face solidly in the mud.

Make sure that all your references to directory structures are dynamically generated, and not hard coded into your scripts.  Hard coded scripts tend to die, with the smallest infrastructure change that is introduced.

OFA Standard 6:  It must be possible to add or move login home directories without having to revise programs that refer to them.

• Refer to explicit path names, only in files such as /etc/passwd and oratab.
• Refer to group memberships only in /etc/group
• Hard coded references to a files path name in a login home directory, must be identified and modified.
• Group memberships should never be recorded in administrative programs, because the group membership can be referenced in /etc/group

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.

Oracle: OFA Part 3

Hi,

My name is Franz Devantier, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g) Security DBA.

When I started working as an oracle DBA in 2000, it was always time-consuming, gathering the needed information to perform my day-to-day duties. 

I spent many hours, and sat up into the early hours of the night frequently; in order to get all the information together, that I needed.  Eventually after a number of years, I knew most of the issues that could occur, and so the amount of research became minimal.

In this blog, I will give you the details of how to carry out the common tasks, and also the not so common tasks of a Database Administrator.  In this blog, I present information in an easy to understand format; that could potentially save you hundreds of hours in research.

OFA – Optimal Flexible Architecture

(Part 3, Operating System Configuration)

OFA – Notes

Name the home directories using a matching pattern like this:

/AB/h/u

AB = mount point

h    = selection from a finite set of directory names

u    = user or owner of the directory

For example:

Oracle software owner, home directory:

/AB/h/u

Or 

/u02/app/oradev

The applications software directory could be:  

/u02/app/appldev

This means that we can place large home sub-trees, at the same directory level, but on separate mount-points.  This satisfies the first part of OFA Standard 5, by allowing different home directories to be distributed across different mount points.  At the same time we comply with the requirement to have sufficient space on the file systems, or drives.  This means that we can access these two large subdirectories with a single substitution pattern.  For example:  /u02/app/*

To satisfy the second part of OFA standard 5; which states that the contents of an individual home directory, must be able to be distributed across two or more disk drives.

One solution is to use symbolic links, to make directories appear in a single sub-tree; despite the fact that they physically reside on a different mount point.  You can achieve a similar effect in windows, by creating shared directories, and mapping network drives.

To satisfy the standard of preventing performance bottlenecks, system administrators may change the value of “h” in the design  /AB/h/u.  Remember we said that “h” represented a finite selection of directory names like “app” and “home”.

So let’s say that all application users were in the schema “/*/app/*/”, and the normal logon users were in the schema “/*/home/*/”.

For example:

/u01/app/*

/u02/app/*

/u03/app/*

and

/u01/home/*

/u02/home/*

/u03/home/*

Then the system administrator can backup all application level user data using this search string: /*/app/*, and all the logon users data using this search string: /*/home/*.  Does that make sense?

So far we have complied with the following OFA Standards:

1. Sufficient space
2. Performance bottlenecks
3. Minimizing hardware costs
4. Minimizing the impact of disk failure
5. Home directories can be spread across multiple mount points, and the contents of one home directory can be distributed across several mount points.

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.