Oracle and dbc_max_pct

[Thomas Lee_1]

While the performance testing, I found that it's runs faster with dynamic buffer cache.

Now I have a question, my system haven't got advanced JFS, can set mincache=direct to mount point for redo and archive log.

What's the possibility for losing my data in cache when the HPUX crash?

[Hari Kumar]

Hi,
U cannot use the "mincache=direct" without the Online JFS installed.
as far my knowledge the mincache setting is advised for the small files and in efficient for large files.the setting actually synchronizes the file's in-core and on-disk states.

[Stefan Farrelly]

You must have OnlineJFS installed to use the options like mincache=direct for Oracle performance, otherwise they are ignored.

dbc_max_pct controls HP-UX buffer cache for I/O - this will help performance as long as it doesnt get too big (ie. over 400MB) - otherwise it starts to slow down performance again as it requires a lot of work by HP-UX to administer it.

We set dbc_max_pct to a max of 400MB on all our servers (ie. Physical_RAM * dbc_mac_pct doesnt exceed 400MB). Weve tried running the mincache=direct options for Oracle (we have OJFS) but it doesnt really help performance that much in my opinion. Better to have a larger SGA size for Oracle as this helps performance better.

[twang]

No, you cannot use mincache=direct without OnlineJFS. For performance, in my option, the cost is expensive for OnlineJFS. The real benefit of OnlineJFS is you can increase filesystems without having to unmount them, no for performance.
And there is a very big performance drop when running JFS as opposed to Raw.
So for good performance, I would suggest you to use RAW partitions.

[Thomas Lee_1]

Stefan Farrelly

My another concern is, if the systen crash while some unwritten data in dynamic buffer cache, then the data will lost.

Any solution to this?

[Thomas Lee_1]

twang

So, how much effort I need for shifting Oracle database from JFS to RAW? Can anyone of you provide me some steps?

Also, how to do backup when using RAW?

[Stefan Farrelly]

Yes, keep your database in Archive log mode, this way it will recover/rollback in the event of a crash and some data lost due to it being in cache at the time.

As well as this you have JFS logging on your filesystems (dont need OJFS for this). Normally theyre set to delaylog in /etc/fstab, if you want to ensure your data is safe change this from delaylog to log (but this will reduce performance slightly).

[twang · Accepted solution]

1. Creating the partitions

Due to the complex nature of setting up a database to use raw devices
it is important that the Oracle DBA works very closely with the System
Administrator for the machine. This will ensure that when partitioning up
disks things like swap space won't get used !! (Swap space doesn't show in
a df command).

Each raw partition can only be used for ONE database datafile, so any space
that is not allocated to the data file is wasted and cannot be used for
anything else. It is convenient to partition up the disk into a number of
evenly-sized partitions with a number of small medium and large
partitions. If the operating system allows you to name these partitions,
then choose a logical name. For a Parallel Server environment, this could
be:
__


2 Calculating The Size of the Partition

When creating the oracle tablespace on the raw partition a slightly smaller
size than the actual partition size needs to be specified. This size can be
calculated as follows:

Size of Redo Log = Raw Partition Size - 1*512 byte block
Size of Data File = Raw Partition Size - 2* Oracle Block Size


3 First Partition of a disk.

On some operating systems, if the first partition of a disk is used as a raw
device it will overwrite the disk partition table. This will, at the next
machine reboot, cause the disk to be unreadable. Check with your hardware
supplier to see if this applies.


4 File Protections

After creating a raw partition the devices are usually still owned by root.
To allow Oracle to use the partition, the owner and group must be changed
so that the oracle account owns the device and its group is the DBA group.


5 Specifying in a create tablespace command

Once the raw device has been created, its group and owner set correctly,
and the required size of the tablespace calculated, it may be referenced in a
create tablespace command as follows:

Three raw partitions each 50M in size and called /dev/rpart1 ..2 ..3
The database has a 4K block size

create tablespace tab_on_raw
data file '/dev/rpart1' size 51196K,
data file '/dev/rpart2' size 51196K,
data file '/dev/rpart3' size 51196K

6 Oracle Block Size

The Oracle Block size can be changed on raw devices, but make sure that the
logical block size is a multiple of the physical block size on the raw disk.
On raw disks, you can do seek only to physical block boundaries and read
or write in multiples of the physical block size.


Backup Strategies
-----------------
1 dd

To backup raw partitions you will need to use the Unix dd command.
Utilities like tar, cpio and dump CANNOT be used for backing up raw
partitions.

The typical command line for dd to do this is as follows:

dd if=/dev/rpart1 of=/dev/tape_device bs=16K

(Keep the Block size to multiple of the Oracle Block Size)

It is important that all raw partitions are included in the backup procedure.
It will require close cooperation between the Oracle DBA and Systems
Administrator for this to be achieved. Any errors or missed partitions will
make the backup invalid !!

2 Oracle Parallel Backup/Restore

This utility provides an effective mechanism to backup and restore Oracle
Data files and control files. The Oracle Parallel Backup/Restore works in
conjunction with a tape management product provided by a third party
software vendor. As long as this third party product is capable of backing up
raw partitions via dd or its own proprietary method then Oracle Parallel
Backup/Restore can be used.

[Jakes Louw]

We use raw lvols for all our corporate databases running on HP, and indeed Sun as well.
With some thought, some intelligent combinations of VG PVGs and striping, and some Oracle design, you can get some awesome response times.
Backups are handled here by raw backups through Omniback, and as mentioned by the previous post, you and the DBA must sit and plan this: you will have to put all the tablespaces into backup mode if you want to do online backups, and make sure to revert to normal mode afterwards. Alternatively, shut the D/B first, if possible. We extract a list of datafiles from the database prior to EVERY backup session, to make sure all the raw datafiles are backed up.
If you are using Mirror/UX and not an array I have some ideas for you on the judicious use of lvsplit for doing backups.

[Thomas Lee_1]

twang

So will put the redo log and control file as RAW will get benefit?

And, usually how much performance gain will found for OLTP from FS to RAW? (Note: I'm using VA7100 as storage with RAID 1+0, the db around 60G, lvm without mirror/split)