Datafiles redistribution across mountpoints

[Karthik]

Hi Erman,

We are using EBS R12.2.9 with Database 19c (19.7). Both Application tier and Database tier(Non-Rac, Non-ASM) running on separate servers. Database size 3.7 TB.

In Database server, We have three mountpoints containing datafiles, one mountpoint for Oracle Binaries,  CDB datafiles and redo logs, and one more point to archive logs. All these mountpoints are created using single lun to storage. Now storage team wants to create multiple luns and mount those as 5 mountpoints for datafiles(1 Lun for 1 mountpoint). So we have to redistribute datafiles across 5 mountpoints. we are planningto redistribute all datafiles by using data file_id as below. Kindly provide your inputs on this approach. If you have any suggestions, please let me know.

file_id 1 --> mountpoint1
file_id 2 --> mountpoint2
file_id 3 --> mountpoint3
file_id 4 --> mountpoint4
file_id 5 --> mountpoint5
file_id 6 --> mountpoint1
file_id 7 --> mountpoint2
file_id 8 --> mountpoint3
file_id 9 --> mountpoint4
file_id 10 --> mountpoint5

FYI, We have 160 datafiles for PDB. We had ran ASH report also to identify around 15 hot datafiles and not to keep all those datafiles in one mountpoint. We are shuffling those datafiles across mountpoints.

Thanks in Advance,
Karthik

[ErmanArslansOracleBlog]

Okay. So this is a pure database operation. You will make your plan and move your datafiles to the related mount points accordingly.
This blog post shows some examples -> https://oracle-base.com/articles/misc/renaming-or-moving-oracle-files

[Karthik]

Hi Erman,

Thanks for the update.

we had checked that blog, it helps.


Kindly provide your suggestions on below,

Regarding new mountpoint to place datafiles,

One separate Lun for each mountpoint for holding datafiles

or

Create new mountpoint in existing lun by adding storage to the Lun.

As I mentioned in previous post, how to place the datafiles from 3 mountpoints to 5 mountpoints. Please provide your inputs.

Thanks,
Karthik

[ErmanArslansOracleBlog]

I would recommend ASM. (no mounts, just ASM diskgroups)
I would recommend ASM - Storage disk alignment as well.. (As your Storage vendor for the best practice on that)
4 disks per diskgroup is a common recommendation to avoid I/O contention.
Read this one (it is from Dell) , it is a good start -> https://infohub.delltechnologies.com/l/oracle-asm-on-scaleio-best-practices-4/asm-disk-sizes-and-counts-4

[Karthik]

Hi Erman,

Thanks for update.

Currently our environment is Non-ASM. As of now, We cant got for ASM.

Kindly provide your inputs/suggestions on the previous post.

Thanks,
Karthik Ponnusamy

[ErmanArslansOracleBlog]

Then, organize your mount points/disks/raid groups/adapters according to the SAN/Storage best practices.
Consider aligning your OS level disk configuration to the SAN level disk configuration. (we don't want to do 1 IO in 2 IOS. :)
For Redo, use disks optimized for writes.
For DB files, use disks mostly optimized for DB I/O.

As for disk count and mount point count, we don't have a general suggestion.
The important thing is not to be blocked anywhere in the OS I/O subsystems while doing heavy and concurrent DB I/O.. Having a single big physical disk may serialize some I/O request in the OS level.. (in some queues) and we generally don't want that.
Just take your db size as a reference, and try to be logical, while sizing the underlying disks.
For instance, if the db is 2TB, 4 different disk source (each 500 GB) may be a good start. I mean 500 GB usable space for each raid group maybe..
Aha.. Also, configure RAID. We use raid mirror for write intensive disks, we use both fault tolerant and high performance raid configurations for the raid groups where the db files reside.. Acutally, all depend on the storage.. The question is very generic, but I did try my best to answer in this very short time.

[Roshan]

Hi Erman,

As you mentioned, for Redo, use disks optimized for writes.
for DB files, use disks mostly optimized for DB I/O.

When we create the volumes(disks), how do we assign the disks to specific storage tier(e.g SSDs and SAS disks)? When I create the disk group  for REDO on OS, how do I assign the disks which are on SSD to it?

[ErmanArslansOracleBlog]

Tell your Storage Admin to map those disks/luns .. Scan your scsi.. (OS Admin)
Get the lun names (associated with SSD disks) from your OS Admin.. Do multipath configuration and everything.. (Your OS admin will do these things normally) Then create your ASM disks on those Linux devices and lastly use the relevant ASM Disk names to create your diskgroup.

OS admin will know which disks are SSD.. (He will also communicate with the Storage team if necessary)

If OS admin is you, then of course you will have to do the tasks described above.

[Roshan]

Thanks.