RE: Database layout

MWF pretty well covered the landscape – with today’s storage farms; I’m not sure it’s worthwhile to try to squeeze out every last nanosecond of performance; but I do think there are a few “guidelines” that will make your life and the lives of those who come after you, easier.

1. KISS -simple is always better
2. Minimize or eliminate “exceptions”
3. Document EVERYTHING – which, if you followed the first two, should be easy, and easy-to-understand
4. Allow for expansion in a rational fashion– someone is always going to ask for more. This should “fall out” of the first three. The LAST thing you want is to have to remember, “Oh, yeah, THAT file is “over there”” when you’re in the middle of a recovery.

Clay Jackson

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Michael Kline Sent: Monday, March 1, 2021 8:51 AM
To: 'Mark W. Farnham' <mwf_at_rsiz.com>; backseatdba_at_gmail.com; arian_at_stijf.com Cc: 'ORACLE-L' <oracle-l_at_freelists.org> Subject: RE: Database layout

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

Thanks.

It was the first that I had seen it in probably over 2,000 databases and 35 years of all sorts of O/S…

Usually do a:
/u001 – Software and logs, 100GB anymore, which may be a bit high, but if they hang around, may have 2-4 $ORACLE_HOME with 1 used.
/u002-/unnn – disks, perhaps split by database
/backup containing exports/SID and rman/SID…
/arch containing FRA

They may vary by size. Some of our /backup disks are 4-8TB.

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Monday, March 1, 2021 11:16 AM
To: backseatdba_at_gmail.com<mailto:backseatdba_at_gmail.com>; arian_at_stijf.com<mailto:arian_at_stijf.com> Cc: mkline1_at_comcast.net<mailto:mkline1_at_comcast.net>; 'ORACLE-L' <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: RE: Database layout

In an age when physical disks were small and sys admins tended to mix applications on disk farms, the formal template to remain sane in being able to find the pieces of the Oracle software and data was published by Oracle, courtesy of Cary Millsap.

In the current age the usual practice is that Oracle “owns” the entire disk farm except for bits reserved to the operating system.

Your mileage may vary, but it remains useful to understand Cary’s reasoning even if you ultimately factor some of the levels out of the tree.

At a certain point in time /u01 … /u04 or so was required to have enough disk space to spin up even a small installation of Oracle E-Business suite.

Also, you might have been sharing the disk farm with other database products, which from time to time might completely trash Oracle throughput if they were shared on the same pieces of spinning rust.

Just be clear and rational. Keeping a notebook and log per PDB is useful, but enforcing that folks keep the notebook up to date was a challenge.

IF you start with that template and understand that the levels in the tree as template placeholders and then possibly factor out levels that are not relevant to you, you will still have a tree that makes sense. If your mount points are arranged such that they represent independent paths from main computer memory to something persistent, then you may be able to do something useful in demanding edge cases.

James Morle updated his famous take on Sane San several years ago to account for the huge changes in physical capability. Reading the original kindly understanding the physical limitations of the day and then reading the update and possibly my short piece on stripe sets which I think is still up on JL’s site will likely make your understanding of the issues exceed what is needed with more modern equipment.

Applying that understanding to modern equipment is probably still “better” than organization “HAPHAZARD” on your entire disk farm. Being able to find things at the OS layer is useful enough IMHO, but it also creates opportunity to prevent peak loads from contending with each other.

But you might not experience gain unless you are actually pushing your disk farm due to avoidable contention issues at the physical layer. When Terascape ran a big shop survey of disk farm improvement candidates circa 1999, only about 25% of self-identified disk farm throughput capacity problems sites actually had disk farm capacity problems worth looking at more deeply than the free “Dipstick” application. Persistent storage is much faster today.

mwf

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeff Chirco Sent: Monday, March 01, 2021 10:29 AM
To: arian_at_stijf.com<mailto:arian_at_stijf.com> Cc: mkline1_at_comcast.net<mailto:mkline1_at_comcast.net>; ORACLE-L Subject: Re: Database layout

I basically do that. I have 5 databases on a single server and it works great for me except I have a separate mount for Tempfiles and Control files as our storage vendor - NetApp has that has best practice because of the way they do Snapshot backups. I didn't use tue u01 naming convention though. Everything is its own mount point. If I had more databases this might be too much but it works well. Oh and I am not running ASM, NetApp suggested not too many years ago when we put our data on there.

/mnt/oralcehome
/mnt/FRA/fast_recovery_area/<DB_NAME>
/mnt/<DB_NAME>/archive01
/mnt/ <DB_NAME> /control01
/mnt/ <DB_NAME> /control02
/mnt/ <DB_NAME> /data01
/mnt/ <DB_NAME> /redo01
/mnt/ <DB_NAME> /redo02
/mnt/ <DB_NAME> /temp01

On Sun, Feb 28, 2021 at 3:19 PM Arian Stijf <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> wrote:

Hi,

we use a similar setup too. Een though /u01/app/oracle is quite small and only used for dba scripts.

With multiple database you create more mountpoints. We sepearate redo/oradata/fra in separate mountpoints too. It allows us to assign different i/o profiles to different mountpoints. OEM-agent, backup and DB tree are located on HHD storage. The others are mixed SSD/HHD storage with Tier 1 (highest performance for redo filesystem).

Do you have any reason to think this is a bad layout?

Regards,

Arian

On 2021-02-28 23:48, Michael Kline wrote:

Database setup

First time I’ve seen this layout?

Anyone else using it?

Each is a whole file system. Not sure what they do if you are running 5-8 databases on a server.

/u01/app/oracle

/u01/app/oracle/agent

/u01/app/oracle/backup/<DB_NAME>

/u01/app/oracle/<DB_NAME>/oradata <-- All data here I guess.

/u01/app/oracle/<DB_NAME>/redo_01

/u01/app/oracle/<DB_NAME>/redo_02

/u01/app/oracle/<DB_NAME>/fra

Michael Kline

13308 Thornridge Ct

Midlothian, VA 23112

Cell: 804.314.6262

--
http://www.freelists.org/webpage/oracle-l