So in summary...
Michael Friedman, January 02, 2005 - 11:05 am UTC
So in summary, no RAID, no SAN.
Just lots of cheap SCSI or even ATA disks even for mission critical production systems?
Makes sense... except I found this: </code>
http://www.oracle.com/database/features_db_rlcstorage.html <code>
Oracle recommends this approach for:
o Online disk backup and recovery
o Development and testing databases
o Reporting databases
o Disaster recovery databases
o Data warehouses
They seem to specifically avoid recommending it for mission critical transactional systems. Why?
January 02, 2005 - 11:21 am UTC
why do you say no RAID/no SAN?
no, that is not at all what I'm saying.
first -- raid is here, 100%, in spirit, soul and fact.
Second -- you need to figure out what kind of storage you need.
*(speed)* think *(speed)*
think flexibility -- with a SAN/NAS I can drop a couple of devices from my disk group on server1 (don't need the space right now) and you can add them to your disk group on server2 -- you don't want direct attach storage in a "big way", not very flexible. SAN/NAS gives you flexibility in that sense.
And OLTP?
Mike Friedman, January 02, 2005 - 11:52 am UTC
And why is this approach specifically not recommended for OLTP systems?
January 02, 2005 - 7:47 pm UTC
cheap disk is not always the fastest disk?
Cache in SAN
A reader, January 02, 2005 - 6:18 pm UTC
We are in the initial phases of implementing a SAN with EMC Symmetrix. One of the things that EMC said caught my attention. They said that the SAN subsystem has huge gobs of RAM (16GB+) cache and is therefore able to return a "success" to the OS before it has really finished writing to the disk.
In the context of Oracle, does this mean that my buffer cache is now effectively 16GB? Most all physical IO will be magically superfast because of the cache?
What is your experience with these claims by hardware vendors?
Thanks
January 02, 2005 - 8:04 pm UTC
in the context of a big data warehouse -- that 16 gig cache gets flooded really fast sometimes too.
the emc array has a solid state "delayed write" cache in front it it, if you don't flood it, writes will most likely appear "fast", but 16 gig is starting to get "small" these days when you have slow write devices behind it -- so watch out, it will be semi-magic, but not black magic..
substained low volume write IO will be faster -- read IO, not too much affected by the presence of this cache (for they cannot necessary tell what we are going to read next -- maybe full scans benefit from a read ahead from time to time)
but throw a parallel 20 load of 100gig at it and ask "is 16gig enough" :)
Cheap disk fast disk
Michael Friedman, January 02, 2005 - 9:52 pm UTC
I know cheap disks may not be fast..
But say I buy 6 36GB 15K SCSI drives? Those are fast. Normally I would put them in a RAID array to balance I/O. With ASM should I just create two failure groups and use ASM with no hardware/OS RAID for best performance and reliability?
In short, is hardware/OS level RAID dead for Oracle?
January 02, 2005 - 10:48 pm UTC
we need to add a little to our terms.
RAID is RAID - raid is not mirroring, raid is not redundancy for data -- not without a modifier anyway..
raid is just a bunch of disks. raid-5 -- that is an array of disk with some minimal small protection. raid 01 -- striped mirrors. raid 10 -- mirrored stripes. raid X - -whatever.
ASM is just like raid 10 in effect -- done at the extent level of database segments instead of some os stripe size.
if I had (well, hey -- i do, in my basement for playing with :) 6 of those disks, there would be a disk group with two failure groups of 3 devices each (for moderate protection -- just mirrored stripes).
I don't need (but could use) hardware mirroring instead of failure groups.
I could put the 6 disks into a nice SAN so I could use them where ever I wanted to.
So, no RAID is not dead (RAID is a SAN perhaps)
Where the mirroring is done is up for grabs though.
ASM will always stripe regardless.
Stripe * 2 = No stripe?
A reader, January 03, 2005 - 10:28 am UTC
"ASM will always stripe regardless"
So, if the underlying SAN stripes and ASM stripes on top of it, would they cancel each other out?
I ask this semi-facetiously but I always wondered if there is such a thing as too much striping? If you shuffle a deck of playing cards N number of times, at some point, they come back to their un-shuffled order?
Thanks
January 03, 2005 - 10:18 pm UTC
but what is their unshuffled order ? :)
I don't have enough practical experience at this point to comment if it is good, bad or indifferent.
Benchmarking
Dominic, January 04, 2005 - 6:34 am UTC
Any ideas if Oracle and the Hardware people will a) benchmark the different approaches and b) release those results?
only helpful in the same way as TPC's but ...
January 04, 2005 - 8:36 am UTC
probably not as tpc's are horribly expensive to run. we are using asm now on them... hard to compare to old runs as the hardware itself changes over time and adds to the numbers.
Real example
Mike Friedman, January 04, 2005 - 7:45 am UTC
I'm upgrading a customer to 10g in March for an ERP system.
He has 5 drives in a RAID array right now.
I'm trying to decide how to advise him on how to use ASM.
Seems like he should have a pair of mirrored drives for OS, Oracle software, etc.
Then I'm not sure if we should user RAID 5 for 3 drives and ASM without redundancy or buy an additional drive and not use RAID at all for those four drives, just use ASM to stripe and mirror 2 and 2.
I (and I think many more people) need a document that clearly explains pros and cons of various configs of ASM and RAID and how to choose between them.
January 04, 2005 - 8:41 am UTC
with 5 disks, you don't have "many options" here. it is a very small system.
ASM implements mirroring and striping.
RAID is just a bunch of disks (without modifiers, RAID does not provide any sort of protection from failure, without modifiers RAID does not provide any sort of striping)
RAID 0+1
RAID 1
RAID 0
RAID 5
RAID 1+0
etc -- they are all different "protect to some degree (not all of them)" and "stripe in some fashion"
or ASM -- to mirror and stripe Oracle data.
Your choice.
Raid 5 provides minimal protection from failure (very small).
RAID5
A reader, January 05, 2005 - 9:51 am UTC
"Raid 5 provides minimal protection from failure (very small)"
Huh? RAID5 is able to tolerate failure of 2 disks out of 5 isnt it? I wouldnt call that minimal protection!
Yes, performance is degraded while the data is shuffled from the remaining disks to the hot spared and stuff, but I am sure ASM has a similar performance hit when disks fail?
January 05, 2005 - 10:58 am UTC
raid 5 can survive the failure of a single disk, when you lose two in your set -- you are out of luck.
raid 5 is characterized as "cheap and semi-resistant to failure"
if you have 5 disks in a set, the logical equivalent of 1 of them will be removed (so you get 4 disks sizewise for your data). It can tolerate the failure of a single disk (at the expensive of performance after that). But if you lose two disks -- you've lost the set.
(and disks are many times put into these raid cabinets sequetially from the assembly line and defects happen in manufacturing lots. More than once I've seen both sides of a mirrored pair fail within minutes of eachother)
Little guys matter
Mike Friedman, January 05, 2005 - 9:26 pm UTC
Tom,
5 disks may be a small system, but I bet you that 90% of Oracle installations and 50% of Oracle revenue comes from that order of "small system". I know the clusters of multiple 32 CPU machines with multi-terabyte SANs supporting them are sexy and fun, but the workhorse systems that run most businesses are just like the one I'm dealing with now.
This is an ERP system that supports all sales, production planning, and material purchasing for a listed Hong Kong company. It's a distributed system with nodes in Hong Kong, Dongguang, and Hangzhou, so these guys spent about US$60,000 on Oracle license + another 12,000+ per year in support. Again, not a huge amount measured against Oracle's total revenue, but it's the tens of thousands of people like that that make Oracle a Fortune 500 company.
People like these need realistic advice on how to configure their systems in ways that protect their data without breaking the bank and that therefore don't begin with "Set up a SAN with switches, etc. an multiple drives in each of your three locations."
If that's the advice we give they'll say "Thanks" and then turn around and call Intentia and be running on an AS400 in 3 months.
January 06, 2005 - 10:06 am UTC
I'm just saying "there ain't many options here"
you won't be overanalyzing this. I'd go six disks, 2 controllers and ASM, period. everything on 2 failure groups. done.
I'm not getting your attitude. Where did you get the impression I was telling you to "go away" -- I just said "your choice of technology, it is for you to make, i'll gladly answer any concrete questions you have"
given that I can get a ton of disk for very little money, I'd be looking at getting a couple more disk IF you want to start talking about separate redo from data, having separate archive areas, etc.
Else, if you are going to be small, get an EVEN number of disk, two failure groups in a single ASM disk group and away you go.
Perhaps I misinterpreted...
Mike Friedman, January 06, 2005 - 10:33 am UTC
Your response came off as "very small, very basic, and not very interesting". Since that apparently wasn't what you meant I apologize.
Sun ZFS
VA, January 19, 2005 - 2:41 pm UTC
</code>
http://www.sun.com/2004-0914/feature/index.html <code>
This sounds uncannily like the new ASM feature in Oracle 10g?
So 10g on this new ZFS filesystem would be overkill. Either use ASM with raw disks or non-ASM with ZFS?
ZFS looks like interesting technology. They seem to have made the filesystem fully transactional with almost a 2-phase commit.
The ZFS filesystem is also looking like a autoextend tablespace taking/giving back from the storage pool as necessary!
Thanks
January 20, 2005 - 10:00 am UTC
no, it to me sounds like zfs would be overkill and overhead for database systems :)
databases, the largest consumers of disk, know best how to store their data. file systems just get in the way.
Not that you using ASM gives or takes away a single dime -- it is a database feature because it makes the database better, easier to manage the *database* on all platforms.
ASM vs. SAN
VA, January 20, 2005 - 9:45 pm UTC
I am still unclear on whether to use ASM or not if the underlying storage is provided by a SAN.
Suppose the SAN already provides striping, redundancy (tolerance to disk failure), mirroring. SAN also gives you the ability to add/remove storage from the storage "pool".
Given all this, why would I want to use ASM? I am asking purely from a feature, value-add perspective.
Yes, the terminology is a little different, ASM stripes at the database segment/extent level, SAN would stripe at the filesystem level, but the concept is the same i.e. distribute IO to prevent hotspots and maximize disk throughput.
I dont care who is administering the thing, sysadmins or dbas, the question is, does ASM provide any value-add in this scenario?
Thanks
January 20, 2005 - 11:13 pm UTC
use asm, ambiguity "resolved"
don't use a file system, file systems are for "files", files are databases in themselves. no sense in a file system on a file system is there?
you DO care who admins this. ask yourself "who is the major consumer of disk and should they not have some degree of control over this resource due to the fact they are the major consumer"
ASM vs. SAN
VA, January 21, 2005 - 9:38 am UTC
You are dodging the question!
Your comment about "no sense in a file system on a file system is there?" can be resolved by simply creating Oracle datafiles on raw devices, thats been possible since version 7.3! Yes, raw devices are a pain to manage, but I am sure the SAN software provides a virtualization layer to address this.
Assuming the dba and sysadmin work well together (or if they are the same person/team), that reason is not valid anymore.
So what is left? I had some very specific focused questions about the value-add provided by ASM in a SAN scenario?
ASM looks like great technology, but IMHO its "too late" given the state of the art in the storage virtualization, SAN/NAS, storage subsystems area. If ASM were available with Oracle 8i (1999 or so), it would have made a tremendous impact in the industry.
Thanks
January 21, 2005 - 12:17 pm UTC
I am not. where did I dodge anything? I don't want a file system, lots of people are 'afraid of raw' -- people are afraid of raw for some reason (works perfectly well for me and others but whatever). I've had people say "just stop saying that word, I don't care how easy it is today in 2005, it is still raw".
(and it has been possible far far longer than 7.3 with the raw trick, goes way way way back)
I'm saying as a database administrator, a file system is an extra layer of mgmt I just DO NOT NEED, or even really want.
At the end of the day -- it is your choice, it is a CHOICE. Working in a clustered environment, a very nice choice (little bit, well alot, less expensive).
In a small shop, this is really utterly attractive.
At the end of the day we get nothing more, nor less by you using it. Use what you know I guess.
otn.oracle.com has some good papers on it as well, if you are interested.
You make lots of assumptions too -- It is rare to see the DBA/Sysadmin be one in the same and even more rare to see them "work towards a common goal as a team".
VA, January 21, 2005 - 1:21 pm UTC
Fair enough, thanks a lot for your comments. Appreciate your insight, as always.
RAID vs ASM
Jeremy, February 22, 2005 - 10:20 am UTC
I have something I've always wondered about which hasn't really been addressed in this discussion... how much processing is involved in actually doing the redundancy? I mean, sure - one huge advantage of ASM is that the database (which would know best) can now manage storage and IO itself at the most granular level possible. But would there be an advantage to using hardware RAID or a SAN simply to offload the processing to a different physical processor, and freeing up more system CPU cycles for oracle to do sorts, queries, updates, etc? Especially considering that Oracle is licensed by CPU... I have to pay for each system CPU i want Oracle to use... and I'd like to get as much bang for that dollar as possible?
of course i've never seen the code that does redundancy, either in ASM or in hardware RAID controllers or symmetrix's or whatever. :) it might be such a trivial operation that it truly makes no difference.
February 22, 2005 - 11:32 am UTC
it is totally up to you -- if you have hardware that does the redundancy already -- great (especially if you like the way they do it), if you don't -- we got it.
by the "especially" what I mean is we mirror and stripe -- so each unit will have a mixture of primary key secondary (or tertiary) extents on it. A unit will be backup and primary for different extents at the same time.
If you lose a 36gig disk, all we need is 36 gig of free space on the other units and we'll give you back the redundancy (it is not a 1:1 pairing, you don't need a hot spare for every failed disk, just free space on other disks)
writes are generally done in parallel using async io, so if the hardware can do it -- we can use it.
You choose.
If ASM instance crashes...
Alex Daher, March 17, 2005 - 3:13 pm UTC
Tom,
If the ASM instance crashes, what is going to happen to the "normal" (instance_type = RDBMS) instance?
How are the files gonna be managed?
Regards,
Alex
March 17, 2005 - 3:54 pm UTC
they won't be.
ask yourself, what happens if <disk managment software goes here> crashes, asm is just a database filesystem, the same thing that happens if your volume manager crashes..
Arun Gupta, March 17, 2005 - 4:25 pm UTC
Tom,
One thing I like about ASM is that mirroring and striping is at file level and not at volume level. What does fine and coarse striping mean?
Thanks
March 17, 2005 - 5:59 pm UTC
mirroring and striping is at the diskgroup level....
fine is small stripes (control files with small stripes)
coarse is big strips (datafiles with big stripes)
Arun Gupta, March 17, 2005 - 8:40 pm UTC
Tom,
Please see the quotes from concepts manual:
</code>
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/mgmt_db.htm#sthref2256 <code>
#
For many situations, Automatic Storage Management provides the functions provided by external volume managers and file systems.
Automatic Storage Management includes storage reliability features, such as mirroring. The storage reliability policy is applied on a file basis, rather than on a volume basis. Hence, the same disk group can contain a combination of files protected by mirroring, parity, or not protected at all.
I thought these implied that different files even on the same diskgroup could have different mirroring and striping levels.
Thanks.
March 18, 2005 - 6:52 am UTC
not striping
ASM & Non-ASM
Shivaswamy, April 03, 2005 - 5:49 pm UTC
Tom,
I understand that, ASM & Non-ASM storage can co-exist in the same database. Is there any draw back in using the two together on an existing production system? Is it advisible to migrate to ASM (of sexisting storage) first and then continue with ASM? What is the best practice guidance on this?
Thanks.
April 03, 2005 - 7:05 pm UTC
Before you do anything, you will *test* of course.
They can coexist, I am not personally aware of any drawbacks (anyone who is, feel free). The problem is -- "best practices" are "new practices" still.
Shivaswamy, April 03, 2005 - 7:39 pm UTC
Tom,
Sure, we will test. Thanks.
We have OS(HP) cluster package(software) for failover now(9i) and are in initial phase to move to 10G. If we go to ASM, - and to RAC latter, in phase 2 - this OS cluster will not recognize ASM and we will necessarily have to go to CSS(Cluster Synchrniztion Services). AM not I right on this? CSS is not yet time tested. There lies our dilemma on ASM.
Thank you.
April 03, 2005 - 7:41 pm UTC
if you goto 10g RAC, it has it's own clustering software included, no 3rd party stuff needed.
ASM just needs access to devices.
A few questions on diskgroup state
Alex Daher, April 06, 2005 - 6:20 pm UTC
1. Is it possible to use ASM with cooked filesystem with ASM files, or ASM demands raw? If so, does it make sense?
2. What is the difference between a MOUNTED and a CONNECTED diskgroup (V$ASM_DISKGROUP.STATE). Could you detail the manual description:
CONNECTED - Disk group is in use by the database instance
MOUNTED - Instance is successfully serving the disk group to its database clients
3. Why there is such a difference between the query below?
In the RDBMS instance:
SQL> SELECT * FROM V$ASM_DISKGROUP;
GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB
------------ ---------- ----------- ---------- -------------------- ----------- ------ ---------- ----------
1 ARCH1 512 4096 1048576 MOUNTED EXTERN
2 SCOP1 512 4096 1048576 MOUNTED EXTERN
3 SCOP2 512 4096 1048576 MOUNTED EXTERN
4 XSAB1 512 4096 1048576 CONNECTED EXTERN 18826 0
5 XSAB2 512 4096 1048576 CONNECTED EXTERN 9546 0
In the ASM instance:
SQL> SELECT * FROM V$ASM_DISKGROUP;
GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ----------
FREE_MB
----------
1 ARCH1 512 4096 1048576 MOUNTED EXTERN 9546
3491
2 SCOP1 512 4096 1048576 MOUNTED EXTERN 9522
7869
3 SCOP2 512 4096 1048576 MOUNTED EXTERN 9522
9243
4 XSAB1 512 4096 1048576 MOUNTED EXTERN 18826
17087
5 XSAB2 512 4096 1048576 MOUNTED EXTERN 9546
9267
A reader, May 03, 2005 - 5:36 pm UTC
<quote>
if you goto 10g RAC, it has it's own clustering software included, no 3rd party stuff needed.
</quote>
If i understand you correctly i can set oracle RAC on 2 sun boxes without installing sun cluster at all?
Thanks.
May 03, 2005 - 7:13 pm UTC
without installing the sun clusterware software, correct. You need the hardware, but not that software.
Hardware RAID or just ASM?
SDiZ, May 29, 2005 - 11:37 am UTC
In administration guide:
<quote>
Storage Arrays and Automatic Storage Management
With Automatic Storage Management, the definition of the logical volumes of a storage array is critical to database performance. Automatic Storage Management cannot optimize database data placement when the storage array disks are subdivided or aggregated. Aggregating and subdividing the physical volumes of an array into logical volumes can hide the physical disk boundaries from Automatic Storage Management. Consequently, careful consideration of storage array configuration is required.
</quote>
Does that means I should not use hardware RAIDs (which aggregate / subdividing the physical volume), instead I should just go for ASM and let it managed all my disks?
May 29, 2005 - 12:14 pm UTC
ASM was designed to take all of the devices and stripe over all of them. The comment here is that if you subdivide them up into various groups -- those groups might well be nicely striped within themselves but not across themselves.
As ASM does the striping, if you are using ASM -- you might as well just let ASM do it.
Your real questions lies in the redundancy -- should you use ASM's mirror and stripe everything approach or external hardware redundancy.
Filesystem cache advantage
Santhoshkumar Name, June 23, 2005 - 1:18 pm UTC
Hi Tom
I a big fan of you and enjoy reading this almost everyday
Can you explain this behavior
I was moving a DW database from 9i to 10g
I created a set of tables in ASM (on EMC RAID 10 , external redundancy) and the same set on filesystem (same drive , no-asm)
When I execute almost all operations (selects,jons, indexing etc..) non-asm tables are very fast compared to asm tables
Why is this, I noticed some thing
After bounce I query a 2G table (system have 8G RAM, 1.4G SGA, EM64T dual XEONS) in FTS mode
asm table : 2 minutes
non-asm tables : almost 1 minute 50 sec
query again (no bounce )
asm table : 1m 50s
non-asm : 4 seconds
Bounce database alone and same test again
asm table : 2 minutes
non-asm : 4 seconds
Is it because of a linux file system cache (in case of a non-asm table )?
We were testing the migration of a 1TB database to linux and 10g (from linux , 9i )
Please clarify
June 23, 2005 - 6:58 pm UTC
after you make sure you are doing about the same number of physical and logical IOS.....
when you move from a cached file system to a non-cached one, the thing you need to make sure happens is.....
the buffer cache is given the memory the cached file system was using previously.
the file system cache acts as a "second sga". you need to give that "second sga" to Oracle.
What about 32 bit SGA Size
Santhoshkumar Nair, June 24, 2005 - 2:42 am UTC
Thanks tom
Yes , Its all PIO , I was testing with 10046 tracing and the PIO ela for 16 blocks was very high on ASM but very low with Linux filesystem cache (double buffer )
Ok , thats right , give the available memory to sga . In my case it should be fine becuase I am on a 64 bit platform . But what about 32 bit sga limitations
So a 32bit linux box with lots of RAM should perform great without ASM and ASM may slow down because of filesystem cache absence ? Is that correct ?
I am planning to give almost all ram (80%) to SGA and repeat the tests with some big tables ( 3-5G) , I will post the outcome
Thanks a lot
Santhosh
June 24, 2005 - 6:36 am UTC
you can get large SGA's with 32 bit. but -- you'd have to ask yourself "if I really need a multi-gigabyte SGA, why am I playing games with 32bit OS's, I have something 'big'"
Still non-asm is fast
Santhoshkumar Name, June 24, 2005 - 6:15 pm UTC
Hi Tom
My box have 6G RAM
I created a 4.5G SGA (2.9G Buffer Cache )and repeated the tests with a 5G table . The non-asm version of the query was very fast compared to ASM version .
This is what I did
Cleared the cache using alter system flush buffer_cache
Verified no blocks are in memory using x$bh
Executed FTS on asm table (ela : 5min 15sec )
Verified the buffer cache for non-asm version of the table
Executed FTS on non-asm version (ela : 50sec ) By no-way my 5G table is in linux cache - may be some blocks
Reexecuted both versions starting with asm first without flushing the cache (some asm blocks and non-asm blocks are still in SGA)
ASM Version ( 3min 39sec)
Reexecuted ASM version (half of the table - about 2.3G in memory, non-asm table is completely out ) ( 3min 26 sec )
Non-ASM version (nothing in memory ) ( 40sec)
Why this huge difference , I agree some blocks of the non-asm tables were in Linux Cache , but my asm table also had half of it in SGA
Very little swapping involved during these tests
I am still investigating .
TIA
Santhosh
June 24, 2005 - 6:51 pm UTC
lets see the results of the 10046 level 12 trace via tkprof for both.
Still non-asm is fast
Santhoshkumar Nair, June 24, 2005 - 7:17 pm UTC
Thanks the reply
Here is the tkprof output (only the concerned result is shown )
****************************************************************************************************
ASM table with a clean cache
select count(*)
from
al_unit_dim_asm
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 11.51 317.82 317996 318023 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 11.51 317.83 317996 318023 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=318023 pr=317996 pw=0 time=317817534 us)
24973561 TABLE ACCESS FULL AL_UNIT_DIM_ASM (cr=318023 pr=317996 pw=0 time=324671219 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 19921 0.04 312.71
SQL*Net message from client 2 18.01 18.01
********************************************************************************
*********************************************************************************************
NON-ASM Version
select count(*)
from
al_unit_dim_nasm
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 12.96 47.49 317996 318023 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 12.96 47.49 317996 318023 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=318023 pr=317996 pw=0 time=47490506 us)
24973561 TABLE ACCESS FULL AL_UNIT_DIM_NASM (cr=318023 pr=317996 pw=0 time=74935597 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1 0.01 0.01
db file scattered read 19921 0.03 42.33
SQL*Net message from client 2 4.82 4.82
********************************************************************************
santhosh
June 24, 2005 - 9:55 pm UTC
pretty sure it is the unix buffer caching at work here still.
0.002 seconds per multiblock read (ufs) vs
0.015 seconds per multiblock read (asm)
the asm disks do appear "slow", are we talking the same hardware here?
Filesystem cache advantage
Santhoshkumar Nair, June 27, 2005 - 12:05 pm UTC
Thanks tom
Yes, Its the same disk array (EMC Clarion) . Two 150GB LUNs configured as ORAVOL1 for asm with external redundancy and another 10g from the same array formatted as ext3 for non-asm tablespace .
Santhosh
June 27, 2005 - 2:05 pm UTC
use the OS tools to see what is happening, iostat and such.
the IO isn't done any differently with ASM -- just like RAW. It has to be still that the file system or the EMC is caching this data and that is the cause.
Filesystem cache advantage
A reader, June 27, 2005 - 1:44 pm UTC
Tom
I am pulling back my earlier post . When we were testing on different storage arrays , the last one I tested was (the tracefile output) on a different array (the internal array of Dell 2850 which has a 200mb/sec transfer speed ) and thats the one which shows a 50sec time on 5GB table . When I moved that table to the right array (the EMC SAN which stores the ASM ) , the time increased to 3min50sec (still faster than ASM but no huge difference )
I am still doing more tests today and then planning for my main migration where I move my 1TB DW to 10g . I will post the results later
Thanks a lot for your wonderful support and help
Santhosh
Automatic Storage Managemen (ASM) and RAID
Sumon, August 08, 2005 - 4:43 pm UTC
Questions are clear but answers are not direct and specific.
Can Tom be more specific while answering the questions?
August 08, 2005 - 8:35 pm UTC
how about you be more specific as to what you did not understand.
What question was asked that you felt was not sufficiently and directly answered?
Does ASM make sense?
mark, November 21, 2005 - 11:00 am UTC
Our sysadmins do not want to give up to be the middle man. They want to continue creating file systems for us on Unix. Does it make sense to use ASM on top of them or would that be an overkill?
November 21, 2005 - 11:16 am UTC
you do not use ASM on top of filesystems. ASM is the filesystem.
No, it would not make sense.
Filesystem cache versus ASM
Brad, November 21, 2005 - 12:29 pm UTC
This thread had some postings from Santhosh indicating that his testing showed ASM was tangibly slower than using filesystem on EMC hardware. Later there was a brief post that "seemed" to indicate the test was flawed and that the difference in speed was actually very minor.
Do you understand his last post to mean that the ASM performance was satisfactory after the testing method was "fixed"? It wasn't entirely clear to me.
November 21, 2005 - 12:31 pm UTC
he wasn't comparing on similar hardware.
... When we were testing on different storage
arrays , the last one I tested was (the tracefile output) on a different array
(the internal array of Dell 2850 which has a 200mb/sec transfer speed ) and
thats the one which shows a 50sec time on 5GB table . When I moved that table
to the right array (the EMC SAN which stores the ASM ) , the time increased to
3min50sec ....
Settled on ASM
A reader, January 19, 2006 - 8:22 pm UTC
Hi All
Here Santhosh again . AS tom pointed out earlier , We were testing on different hardware and thats why the huge difference came from . So all the tests wre concluded and we decided to use ASM with emc storage and its running for almost 3 months now with a total 1.3 database size. Now we have an EM64T based server with 24G RAM and we give almost all RAM to the database (less unix buffer cache usage because of ASM) .Performance is good but we may change the configuration to use multiple low cost storage arrays to increase the IO throughput (using ASM)
Thanks to all
Santhosh
When not to use ASM for production (OLTP)
DanielD, January 30, 2006 - 5:57 pm UTC
Tom,
Are there cases when not to use ASM (10gR2) for production? Are there any papers published comparing ASM performance vs raw disks?
Thank you.
January 31, 2006 - 1:50 am UTC
ASM is one of the rare things that is universally applicable. It is a database file system.
It is in fact "raw" that is "easy to manage". It is raw underneath, we are just striping segments over all of the raw disks. The DBA doesn't deal with raw however, they manage it as if it were a file system.
Shared ASM Instance
Yogesh, January 31, 2006 - 1:08 pm UTC
I'm using one ASM instance for two databases. How can I differentiate between the files from these databases?
January 31, 2006 - 3:35 pm UTC
not sure precisely what you mean there?
Shared ASM
Yogesh, February 01, 2006 - 6:03 am UTC
I've two databases and one asm instance running on one development server. After querying v$asm_file view, I can get details like group number, file number, type etc... I want to filter out these files based on the database using them.
A reader
A, April 19, 2006 - 6:48 am UTC
Hi,
In ASM when we choose not to specify fail group(while creating diskgroup)oracle documentation says it will be under it's own fail group..what does this mean ?? How it handles the failure of one or more disk.
Another question is(with asm) if we have 4*73gb disk ,how much disk space will be available for data storage while using normal,high redundancy.It should give 2*73gb as it uses stripe and mirror ??
If we have only one contoller ,which controls ,say 4 disks ,is it advisable not to create a fail group ??
Cheers
April 19, 2006 - 8:47 am UTC
how about a pointer to otn documentation (a direct link to the online docs) that has the quote on context.
A reader
A, April 21, 2006 - 6:42 am UTC
</code>
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#BDCGGGJC <code>
From above link copy and paste
Failure Groups
Failure groups are administratively assigned sets of disks sharing a common resource. Failure groups are used to determine which Automatic Storage Management disks to use for storing redundant copies of data. The use of failure groups ensures that data and the redundant copy of the data do not both reside on disks that are likely to fail together.
The composition of failure groups is site-specific. Failure group decisions are based on what component failures the system can tolerate. For example, suppose you have five disks and one SCSI controller. The failure of the SCSI controller makes all disks unavailable. In this scenario, you should put each disk in its own failure group. However, if you have two SCSI controllers, each with two disks, and you want to tolerate a SCSI controller failure, then you should create a failure group for each controller.
By default, Automatic Storage Management assigns each disk to its own failure group. When creating a disk group or adding a disk to a disk group, you can specify you own grouping of disks into failure groups. Automatic Storage Management can then optimize file layout to reduce the unavailability of data due to failures.
A failure group is maintained in a disk group, and multiple failure groups can exist within any given disk group. However, changing a disk's failure group requires dropping the disk from the disk group and then adding the disk back to the disk group under the new failure group name.
======
It says ..By default(which means when i don't create any disk group) it puts every disk to it's own failure group.What does this means ?? how it handles the disk failure in this case. Am I missing something here ?
Also please answer my previous post questions related to this.
Cheers
April 21, 2006 - 7:36 am UTC
first - it is pretty clear this is not a place for new questions to be demanded to be answered isn't it? I scan these quickly and make followups - I do not see them all every time.
You configure
no redundancy
mirrored redundancy (normal)
triple mirroring (high)
and set up the disks appropropriately.
How many diskgroups
A reader, May 09, 2006 - 12:39 pm UTC
Tom,
I have 2TB of SAN attached disk to be managed by ASM for data storage. The redundancy is managed by hardware so I will be creating disk groups with external redundancy
My question is how should I lay out the disk? Should I create one diskgroup with 2TB disk partitioned as say 4 disks of 500GB each or should I create two diskgroups with 4x250GB disk each?
My second question is about the number of member disks in a diskgroup. Is there a recommended range or can I have even 20 or 30 member disks in a diskgroup?
Thanks.
May 09, 2006 - 1:21 pm UTC
what would you like to do? This is entirely up to you.
do you want two separate disk groups that we'll never stripe across?
do you want a single disk group that we'll stripe across?
if you were to make these into mount points using a traditional cooked file system - what would you have done then?
you can have way more than that in a diskgroup.
RAID 5 or no RAID 5
Tino, May 23, 2006 - 11:11 am UTC
Hi Tom,
In terms of performance is it better to go with ASM with normal redundancy, and RAID 5 or no RAID 5 (no RAID)?
What are the pros and cons?
May 23, 2006 - 3:46 pm UTC
no raid 5 :) with anything.
RAID 5
A reader, May 23, 2006 - 4:31 pm UTC
Tom,
Why not RAID 5 in DW environment? especially where there are weekly batch loads, or even daily small loads?
Regards,
May 24, 2006 - 7:03 am UTC
in a DW could be really fatal.
Since in a DW you tend to do large direct path operations (create index, create table as select, insert /*+ APPEND */, sqlldr direct=y and so on).
Those bypass the buffer cache, write directly to disk. If you would like to really make them "not so fast", use raid-5.
Recommendation
A reader, May 24, 2006 - 4:56 pm UTC
Tom,
So, what are your recommendations for DW applications?
Regards
May 25, 2006 - 1:18 pm UTC
see above???
An article..
A reader, May 24, 2006 - 5:03 pm UTC
May 25, 2006 - 1:25 pm UTC
#b
gotta love typos like this:
<quote>and moist Oracle professionals</quote>
Only wet people...
I'd be concerned about an article that refers to "block level striping". I don't know about you - but would you really stripe "at the block level".
<quote>
with RAID 0, the Oracle datafile is automatically striped one block at a time across all of the disk devices.
</quote>
that is just not true.
also, the article seems to be saying that raid 0+1 and raid 1+0 are the same. They are very different. There is a huge difference availability wise between striped mirrors (raid 0+1) and mirrored stripes (raid 1+0).
ASM for example is similar to raid 10 - mirrored stripes. If you lose a disk with mirrored stripes, all you need is sufficient free space elsewhere in the array to remirror them. raid 10 - good performance on read and write and good availability.
If you use raid 0+1 on the other hand - well, that is striped mirrors. Here if you lose a disk, you need an entire new empty disk to remirror onto. Not nearly as available as raid 10. raid 0+1 - good performance on read and write but lower availability characteristics.
However, it did get this part correct:
<quote>for Oracle data warehouses where the load speeds are not important </quote>
which is exactly what I said above.
Thanks lot!
A reader, May 25, 2006 - 4:38 pm UTC
Put ASM on RAID 10
Tino, June 16, 2006 - 4:40 pm UTC
Is it wise/practical to put ASM on Hardware RAID 10? To me it sounds redundant, ASM doing striping on top of hardware striping... hardware redundancy when ASM can do the redundancy in terms of normal or high failure groups. I am seeing a lot of people still holding on to the old way of doing things and not embracing ASM to manage their storage. It seems as if they are unsure of the technology. What are your views on this?
June 16, 2006 - 7:24 pm UTC
sure it can - you can stripe a stripe - no problem there and you would use external redundancy.
ASM and striping
A reader, June 17, 2006 - 12:18 am UTC
Actually you can use ASM with RAID 10 disks. EMC recommends doing this. They call it double striping. It is in one of their white papers. At ASM level, I can have a stripe size of 128k which is much smaller than I can get with hardware.
I am still struggling with one LUN vs. multiple LUNs. Some testing that I did indicated that all the disk presented as a single LUN was more efficient for certain operations than same amount of disk presented as multiple LUNs. However EMC states that multiple LUNs must always be used.
If using ASM for multiple databases, increase the processes accordingly. The default number of processes (40) is good for about 4-5 databases. The shared pool must also be increased if using DBConsole to manage these databases. DBConsoles keep querying ASM instance and too many of them can result in shared pool fragmentation and 4031 in ASM instance crashing the ASM instance.
ASM for ILM
Arup Nanda, June 17, 2006 - 10:25 am UTC
Just to add to Tom's response to "Tino from Relaxing on the Beach" (and I hate you for relaxing on the beach :)
Even if you use RAID10, you may still want to do ASM striping for another reason. Make your LUNs stripe across many physical disks and then make more than one LUN part of a disk group (example is for illustration only; not checked for syntax)
create diskgroup dg1
external redundancy
disks '/dev/rdsk/c0t0d0','/dev/rdsk/c0t0d1';
Now suppose this data has gone to the realm of less frequently used and you would want to move them to some slower (and cheaper) disk, say LUNs c1t0d0 and c1t0d1. The tradtional option is to create a new tablespace on those two LUNs and moving the table (or the partition of the table) to the new tablespace. This process works;but it is intrusive, i.e. it affects the availability of the table or partition.
In the ASM option, you would transition the slow LUNs into the disk group and transitionthe fast ones out.
alter diskgroup dg1 add disk '/dev/rdsk/c1t0d0';
This causes the data on the existing two LUNs to be redistributed over the three LUNs, while the operations on the table is ongoing.
After the rebalance, just drop the high speed disk LUN from the group:
alter diskgroup dg1 drop disk <disk_name_of_c0t0d0>;
Again, it makes the data rebalanced across the two LUNs c0t0d1 and c1t0d0. The high speed LUN c0t0d0 is now free and can be used for some other high access tablespace.
Repeat the process to ease in c1t0d1 and ease out c0t0d1. At the end of the process, the diskgroup dg1 has only slow disks and the fast disks will be available for deployment elsewhere.
All this, while the database operations are going on as if nothing happened. Sweet! Of course, performance is affected during the rebalance operations; but it can be managed - using a higher "power" of rebalance, using a relatively "quiet" period and so on.
We do that now on our databases for Information Lifecycle Management.
A reader, June 19, 2006 - 12:31 pm UTC
Say I setup the diskgroup below where I am not doing hardware RAID10 (no RAID at all), is this actually RAID10 at the software level (ASM)? What is the advantages or disadvantages of using ASM this way without performing hardware RAID? Don't the DBA have more control now of the disk configuration, deciding no which disks he/she wants to stripe over and which disks he/she wants to mirror over without the intervention of the System Administrator. Can't the DBA now just buy some equally fast disks which currently exists for ASM, do some minor configuration without thinking about hardware RAID and just do the RAID (RAID 10) at the ASM level?
Guys tell me if I am missing something here?
create diskgroup dg1
normal redundancy
failgroup ctl0 disk '/dev/rdsk/c0t0d0', <--| striping <-|
'/dev/rdsk/c0t0d1' <--| |Mir
failgroup ctl1 disk '/dev/rdsk/c1t0d0', <--| striping |
'/dev/rdsk/c1t0d1'; <--| <-|
June 19, 2006 - 6:43 pm UTC
if you have two failure groups in a single diskgroup (mirrors of eachother) and they consist of striped disks (mirrored stripes) you have the functional equivalent of raid 1+0 - mirrored stripes.
The writes are done in parallel, typically using async IO - just like they would be at the hardware level.
Most "hardware raid" is really "software raid" as most SAN's/NAS are really front ended with a ton of software.
A from Aus
A reader, August 25, 2006 - 5:56 pm UTC
Hi,
I have been testing ASM lately.I have few questions,and not getting expert answer for this.Tom your expert comment is highly appreciated
1)Would you recommend raid 10(hardware level) and external redundancy at ASM level(which will do stripping again) or raid 1(just mirror at hardware level) and external redundancy at ASM level.What I trying to get here-- is Double stripping recommended ?? I know it can be done ,but is it a good practice ??
2)In my testing phase ,I came across another problem ,which is I want to do expdp(dump) of certain schema and store in ASM file system daily.Each night it should create different filename for dump and another process should delete an dump file older then say 3 days.In normal unix file system I can append date along with file name to make it unique and hence it can be deleted easily.Here I can't do this way.Is there any safe way to achieve this.
Cheers
August 27, 2006 - 8:55 pm UTC
1) if you have external redundancy and are happy with it, use it. else we can provide it.
and it is perfectly ok, normal, whatever to stripe a stripe - so even if it is striped, us striping again is OK.
2) why can't you? you can call datapump directly from plsql - programatically.
A
A, August 29, 2006 - 1:47 am UTC
Hi,
This is what I did,But this will not create a unique file each time.Any example of creating a unique file name and deleting them (older then say 3 days)will be Very helpful.
It must create a unique file name each time as I will be calling this from dbms_scheduler.
Cheers.
DECLARE
hand1 NUMBER;
BEGIN
hand1 := Dbms_DataPump.Open(operation => 'EXPORT',
job_mode => 'SCHEMA',
job_name => 'EXPJOB',
version => 'COMPATIBLE');
Dbms_DataPump.Add_File(handle => hand1,
filename => 'a_exp%U.dmp',
directory => 'EXPORT');
DBMS_DATAPUMP.metadata_filter(
handle => hand1,
name => 'SCHEMA_EXPR',
value => '= ''aaa''');
Dbms_DataPump.Start_Job(hand1);
DBMS_DATAPUMP.detach(hand1);
END;
August 29, 2006 - 7:08 am UTC
how about
l_filename := 'a_exp' || to_char( sysdate,'yyyymmdd' ) || '.dmp'
for example.
ASM vs SAME
Jairo Ojeda, August 29, 2006 - 6:20 pm UTC
Tom,
I want to implement SAME methodology, so if I take the tow RAID 5 that are using now for datafiles, undo and temp-file and configure one RAID 0+1, How can I configure the ASM?
August 30, 2006 - 7:57 am UTC
if you have raid 5, you are already striped.
does it really make sense to you to mirror on top of raid 5?
SAME & ASM
Jairo Ojeda, August 31, 2006 - 1:32 pm UTC
No, I don't want to mirror on top of RAID 5, RAID 5 is the configuration that we are using.
We are looking to buy a new database server and to migrate to 10gR2, and I'm not sure to configure SAME stands and ASM, so mi configuration is as following:
1. RAID 1 to OS, oracle installation and control file
2. four dedicated disk on RAID 0 for online and standby redo logs --multiplexed like you described on Expert Oracle Database Architecture
3. RAID 0+1 to archivelogs, exports, backups and recovery area, and a control file
4. RAID 0+1 to datafiles, temporary datafiles, undo and a control file
My question is about the datafiles disks that now are configured in tow RAID 5, so if I use the SAME methodology, should I create only one big devise on RAID 0+1 for all datafiles or should I create tow devices on RAID 0+1 and balance the datafiles across the tow RAID 0+1 like d:\oracle\database\dbfA01), e:\oracle\database\dbfA02),
d:\oracle\database\dbfA03), e:\oracle\database\dbfA04?
If the answer is to create only one devise on RAID 0+1, is right to use ASM? and if is possible your comments about this configurations.
Thanks
August 31, 2006 - 2:56 pm UTC
SAME stands for "stripe and mirror everything"
so, you don't really want to do SAME, you would like to use ASM on top of raid 5 arrays.
But then you talk about raid 0 = stripes, raid 1 = mirrors again??
You said "i don't want to mirror on top of raid 5" but...
You are talking raid 1, which is mirroring?
A
A reader, August 31, 2006 - 11:39 pm UTC
Hi,
Datapump method worked beautifully.But I also want to delete them from ASM automatically(older then 3 days dump files).All this test will dictate whether we can use ASM or not.At this moment we are testing it on 10gr2.So your help is highly appreciated.
Thanks,
Cheers
September 01, 2006 - 8:26 am UTC
just erase them? see the alter diskgroup command, drop diskgroup file section.
A
A reader, September 03, 2006 - 2:52 am UTC
Hi,
I already tested it(thats whay I used the word safely..in my earlier post),If I have to use alter diskgroup command then I will have to hard code sys user name and password in the file and then connect to the instance and generate another file which will have some thing like this...
sqlplus "sys as sysdba"/password @<the file with alter
diskgroup command>
And then scheduled it using dbms_scheduler.This will expose my sys username and password(which any body can see at OS level),which I don't want.
Any trick here ??
Cheers
September 04, 2006 - 8:26 am UTC
umm, schedule a plsql job? don't use a script to connect back to the database (to which you are already connected when the job runs)
A
A reader, September 04, 2006 - 5:26 pm UTC
Hi,
How can we do this using pl/sql program ? Any example will be helpful.
Cheers
September 04, 2006 - 7:58 pm UTC
if you can do it in a sqlplus script, you can do it in plsql.
got script? make it short, I'll translate.
a
A reader, September 07, 2006 - 10:55 pm UTC
HI,
This is what I want to do...(All this has to be done on ASM instance).
set heading off
spool /u01/del.sql
export ORACLE_SID=+ASM
select 'alter diskgroup flash drop file
''FLASH/ORCL/DUMPSET/'|| a.name||''';'
from v$asm_alias a, v$asm_file b
where a.group_number = b.group_number
and a.file_number = b.file_number
and b.type='DUMPSET'
order by a.name;
spool off
sqlplus -s sys/password@del.sql
and del.sql will have something like this..
alter diskgroup flash drop file
'FLASH/ORCL/DUMPSET/SYSSYS_EXPORT_SCHEMA_04_150102_1.281.599935349';
alter diskgroup flash drop file
'FLASH/ORCL/DUMPSET/SYSSYS_EXPORT_SCHEMA_07_150155_1.336.599935453';
Thanks
September 08, 2006 - 4:24 pm UTC
just use execute immeidate in loop then, you can execute immediate 'atler diskgroup....'
for x in ( select 'alter diskgroup flash drop file
''FLASH/ORCL/DUMPSET/'|| a.name||''' cmd from ..... )
loop
execute immediate x.cmd;
end loop;
A
A reader, September 12, 2006 - 1:15 am UTC
Hi,
This is what I am getting...
1 begin
2 for x in ( select 'alter diskgroup flash drop file
3 ''+FLASH/ORCL/DUMPSET/'|| a.name||''';' cmd from v$asm_alias a, v$asm_file b
4 where a.group_number = b.group_number
5 and a.file_number = b.file_number
6 and b.type='DUMPSET'
7 order by a.name)
8 loop
9 dbms_output.put_line (x.cmd);
10 execute immediate x.cmd;
11 end loop;
12* end;
SQL> /
alter diskgroup flash drop file
'+FLASH/ORCL/DUMPSET/SYSSYS_EXPORT_SCHEMA_04_150102_1.281.599935349';
begin
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 10
September 12, 2006 - 8:21 am UTC
lose the ';'
';' is a sqlplus "flag", it is NOT part of the sqlstatement.
it simply tells sqlplus "go ahead, run this statement"
and sqlplus removes the ';'
I love speed
Anil, October 08, 2006 - 11:21 am UTC
Hi Tom,
Thanks for sharing knowledge.
ASM provides redundancy by stripping and mirroring at the extent level. This guards your data. Can it provide considerable performance improvement in OLTP, DSS environments as well?
Pls Consider this senario:
1 database, 2 schemas, accessed and sometimes shared by two groups of 100 users each.
1.) Create 1 diskgroup using 1 RAID with external redundancy for each schema.
2.) Create 1 diskgroup using 2 RAIDs with normal redundancy for the use of both the schemas.
Can the second option increase I/O performace because more disks get involved in the I/O operation and the prallel degree of read and writes increases.
Thanks once again.
October 08, 2006 - 2:39 pm UTC
... Can it provide considerable performance improvement in OLTP,
DSS environments as well?...
Only reasonable answer to that is "it depends"
Are you suffereing from unbalanced/unprotected IO right now? If so, ASM is a method of spreading out out (to avoid the unbalanced IO) and protect data.
1 database good - number of schemas, not relevant at all, an IO is an IO regardless of the schema name.
The more disks involved, in general, the better - if you have an IO problem to start with (if you do not, "fixing" IO will not do a thing for you)
I got the point
Anil, October 09, 2006 - 6:59 am UTC
Thanks Tom.
I got it.
If you would like to know more about the situation here:
Curretly we have IO bottle necks.
I mentioned schemas because here user requests are hitting different sets of tables for each user.
And each schema is under seperate tablespace. What I wanted to say is I would like to define both tablespaces under 1 diskgroup consisting 2 RAIDs so that the load of a single hit would be shared between 6 disks than 3.
regards
October 09, 2006 - 8:50 am UTC
the schemas are quite simply not relevant. Segments and the amount of physical IO they experience and the devices they are on - that matters.
Whether the segments are owned by one or one thousand different schemas is not relevant.
Tablespaces are not relevant either! Or at least they do not say very much in themselves.
You could have 100 tablespace, each with 100 files, each file coming from one of one hundred devices. That'd be "good", spread out.
You could have 100 tablespace, each tablespace with one file, each tablespace "owning a single device and no other tablespace will allocate a file on that device". I would say that would be bad - a single hot table would only use a single device - not all 100 devices.
RAID is one way to achieve even IO, you would take 100 devices and turn them into one big striped thing. Now you have 100 tablespaces, each with 1 datafile, that 1 datafile on the single logical volume that is really 100 devices - striped. This would be "good" (in general).
ASM implments raid 1+0 - mirrored (optionally) stripes (what you seem to desire)
please do not confuse "schemas", "tablespaces" (logical organization constructs) with tuning devices - it all comes down to getting your IO evenly spread out.
You could do that by creating tablespaces with files from every device.
You could do that by RAID in your disk array.
You could do that using ASM (which is raid 1+0 with lots of extra database "ease of use" things about it)
and so on.
Two disk groups vs one disk group
A reader, November 02, 2006 - 7:35 am UTC
Our solutions are based on servers with four disks.
For us, recovery till last commited transaction is more important, than performance. Would it be better in such case to go with two disk groups each with one failure group? One for data and one for recovery?
e.g.:
SQL> select name, header_status, path, failgroup from v$asm_disk;
NAME HEADER_STA PATH FAILGROUP
---------- ---------- ------------------------------ ---------
DATA_0000 MEMBER /dev/rdsk/c1t0d0s4 D1
DATA_0001 MEMBER /dev/rdsk/c1t1d0s4 D2
RECV_0000 MEMBER /dev/rdsk/c1t2d0s1 R1
RECV_0001 MEMBER /dev/rdsk/c1t3d0s1 R2
While extremely rare, I have seen file system corruption on NTFS, ext2 and once even on proven UFS filesystem, so I am little uncomfortable with only one disk group. If there is a metadata corruption, then tablespaces, control files and logs are lost, so point in time recovery is not possible.
What is your view on this? When point in time recovery is very important, would you still go with one disk group and one failure group?
November 02, 2006 - 7:45 am UTC
if recovery is important - where is the other machine that is physically separate from this one located?
you know, for when someone spills coffee.
with 4 disks, there isn't all that much you can really do - your call entirely.
ASM was unable to see EMC's pseudo-device
reader, December 11, 2006 - 7:44 pm UTC
When our ASM instance was initially created about 1 year ago, we used EMC's native path-names (e.g.: /dev/sdc1) for ASM diskgroups. Since the database data is not mirrored on the native path, we intend to move to the pseudo device (e.g.: /dev/emcpowerb). But our initial attempts failed. After the change, Oracle ASM was not able to see the pseudo devices. So we had to rollback. Could anyone tell me in details how to switch to EMC's pseudo device from native path?
December 11, 2006 - 7:55 pm UTC
have you contacted support?
Confused!!!
Christer, December 12, 2006 - 4:25 am UTC
Hi Tom!
In this thread you use the following terms
several times.
raid 01 "striped mirrors"
raid 10 "mirrored stripes"
Shouldn't it be the other way around?
raid 01 is first striped and then mirrored
for me that is "mirrored stripes".
raid 10 is first mirrored and then striped
for me that is striped mirrors".
Perhaps the fact that my first language wasn't
english, can contribute to my confusion.
In another thread </code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2403901570002 <code>
you don't use the terms "striped mirrors"/"mirrored stripes"
This thread actually makes sense to me.
Testing ASM
A reader, April 12, 2007 - 2:23 am UTC
I'm testing ASM, liking it a lot so far, but found one issue I'm having trouble resolving. Hope you can help.
In the process of running DBCA several times early on (different sid's each time), but failing to complete, and eventually succeeding, I find that ASM has assigned space/files to the failed database creation attempts and kept it. i.e. my final succesful database is 12GB but ASM is using 52GB....
Normally I'd reclaim this space with drop database, but since these failed database creations are not even mountable I can't do this.
So, I've been exploring the V$_asm views but still I don't see any means of identifying the obsolete "files". Besides, if identified I'm also struggling to find out how to then "drop" them.
Any clues Tom?
Thanks....
April 13, 2007 - 11:47 am UTC
I'm not sure what you did or how you think "asm has assigned..."
ASM is a standalone thing - it is like a file system driver (not really like, it is). You have diskgroups in there, they have storage associated with them. Databases create datafiles in those diskgroups.
A reader, April 15, 2007 - 7:01 pm UTC
I meant that I told the dbca to use the asm instance, but when the dbca failed midway (after it had already created a bunch of objects/tablespaces) the asm instance keeps the "files" that were created.
Basically, I want to clean out the ASM instance. Normally a "drop database" does this nicely. But these failed database installations don't exist to be dropped, yet they do have space allocated in the asm. How do I identify this and remove it?
Thanks
April 16, 2007 - 1:14 pm UTC
A reader, April 15, 2007 - 8:11 pm UTC
I had a dig and came up with a solution.
export ORACLE_SID=+ASM
export ORACLE_HOME=......./asm
asmcmd
then simply:
rm -r -f orcl
rm -r -f orc2
rm -r -f test
etc
Nice!
ASM Check functionality
Rodrigo Murillo, April 19, 2007 - 10:52 pm UTC
Hi Tom
The information here (at asktom.oracle.com) is very usefull.
About ASM, how can ASM do something like a file system check. I mean, if there is some kind of failure at the volume level, can ASM runs a check? or what else can be done? or do i have to drop and re-create the disk?
Thanks
April 20, 2007 - 6:50 am UTC
what do you mean by "some kind of failure at the volume level"
if a disk fails, ASM is told that and ASM will find other disks and mirror the information that was one the failed device elsewhere and tell us that this failure has happened.
ASM and system crashes
Rodrigo Murillo, April 20, 2007 - 3:54 pm UTC
Thanks for the answer.
May be, i don't understand ASM very well. . . not yet . . .
But, what happen in the case of a server crash . . . how does ASM to check the integrity of the contents on the ASM Disks? What does ASM if some of the disks has inconsistent blocks due to the system failure, for example, in the case of external redundancy.
And where is the functional limit between the volume manager and ASM . . . I mean, where does finish the scope of ASM?, and where begins the volume manager's scope?
April 20, 2007 - 4:59 pm UTC
it would be not any different than if an OS crashed with raw disks. There is no need to "fschk" in the conventional sense.
you would be handing the devices to ASM, you are cutting out that volume manager/file system stuff.
More about ASM
Rodrigo Murillo G, April 23, 2007 - 8:00 pm UTC
Is it possible to use Veritas Availavility Suite with the volumes used for ASM Disks?
This is a tool used for data replication on an operating system block basis.
???
Rodrigo Murillo G, April 25, 2007 - 10:26 pm UTC
This is not the thread to ask about availavility suite??? Thanks
April 26, 2007 - 11:31 am UTC
I don't sell, support or use that veritas product.
so, I would be the wrong one to ask about it...
It is not the thread, it is me :)
question on diskgroup,
A reader, June 20, 2007 - 4:40 pm UTC
In our new environment we have LUNs alloted for us as part of storage for the new database. The luns are named like asm001, asm002, asm003 and so far. These luns under linux are seen under /dev/db_name/diskgroup_name/asm*.
Apparently asm001 is seen under different diskgroup_name folders. For example asm001 can be seen in
/dev/arcdb/datagrp_01/asm001
/dev/arcdb/datagrp_sys_02/asm001
Is this possible?
what exactly asm001 is? Is this a raw device? How differnet is it from local mount (like /u01)?
Thanks,
Aman, June 21, 2007 - 7:27 am UTC
Hi sir,
I am not sure should I ask it here but this thread is about ASM so I am going ahead.ASM uses extents to move across disks.Can you please explain what does this extent movment means and how does that helps as compared to normal RAID?In documentation,it is mentioned that ASM doesnt require free disk but just requires "free space" across the disk and it will use it.It is related to extent movement,I am not able to get it.
Thanks in advance.
Best regards
Aman....
June 21, 2007 - 10:47 am UTC
raid stripes data.
asm stripes data.
with raid stripes, a system administrator says "please take data and break it into Nbyte chunks and store the chunks striped across disks"
with asm, a DBA says "please take data and break it into Nbyte chunks (in oracle speak - EXTENTS) and store the chunks (extents) striped across disks"
An extent in Oracle speak is a stripe in Raid speak.
Smaller, 5 disk configuration
Jason McIntosh, June 21, 2007 - 10:08 am UTC
I've got a smaller DB server running RedHat Advanced Server, with a total of 5 15k RPM disks and a Dell Perc RAID controller for hardware raid, and the server has a total of 8GB of ram (main application tablespace is about 2.5gb in size at the moment and expanding), with 2 quad core processors.
In this kind of environment, I could do a full RAID 5 system for all the devices, which is what we've done in the past (I've been more of a linux sys admin than a DB admin, but am slowly learning!). It sounds like ASM does some amazing things, and I'm wanting to learn to use ASM, so I'm thinking of using hardware RAID1 for the operating system, and then assigning the remaining 3 drives to ASM. The question I have is this:
Will ASM support redundency on those drives, so if one fails, the data is still valid? You've said it works with 2 drives, but with an odd number, I wasn't sure how ASM would handle things.
My understanding is that most hardware RAID controllers use their own processors to calculate striping and offload CPU time that OS/software level redundency would generate. With the above configuration, would I be better off just using a RAID 5 for the Oracle datafiles on a file system, and let the RAID controller handle redundency? Particularly considering that ASM looks like it adds additional CPU usage, memory usage, etc.? I don't think I'll be hurting for CPU time, but I'm curious as to what a good configuration would be ;)
Last, slightly off topic, I vaguely recall in the past, you've said not to create an overly large SGA for smaller databases due to flushing of the statements or something along those lines. Considering the above configuration, with a mixed mode between DW and OLTP, do I need an SGA of something like 4GB or should I go with a much smaller SGA? And I'm sure what ASM takes up memory/processor wise, if it's a static amount or expands upwards, etc?
Thanks!
June 21, 2007 - 11:15 am UTC
it'll work with 3. however, as documented:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/storeman.htm#sthref1698 ..
it is best if all failure groups are the same size. Failure groups of different sizes can lead to wasted disk space
.....
eg: 3 would not be wise unless you were going for high redundancy (3 failure groups)
Oracle always stripes - with ASM, raw, cooked, whatever - we call these stripes extents. We are not doing any extra 'work' to stripe, we've ALWAYS done that.
Redundancy - async IO and background writes by dbwr - doesn't really impact runtime (and we've always done our own redundancy with log information)
the overly large was in regards to shared pool settings for applications that do not use bind variables - larger is NOT better for them.
followup question on diskgroup,
A reader, June 21, 2007 - 11:20 am UTC
You have asked whether two diskgroups are mounted: the answer is NO. We are not able to mount two diskgroups.
So what I understand is a LUN is assigned to one and only diskgroup.
Thanks,
June 21, 2007 - 11:40 am UTC
yes
ASM Raid5 performance
Rijesh, July 06, 2007 - 9:57 am UTC
Hi Tom,
Recently we tested our application with RAID5 storage with out ASM in Oracle database 10g and benchmarked the performance numbers [basically the transactions per second].
Then in the same machine, we configured ASM On RAID5 with diskgroup created with NORMAL REDUNDANCY. When we took the performance numbers, it was way behind the numbers given by RAID5 non-ASM.
Here the only difference is ASM is configured and the oracle instance is set with the same set of parameters for both the tests.
Do you feel that the performance degradation in ASM with RAID5 is because of the NORMAL redundancy?
Please let me know your thoughts.
Thanks.
July 06, 2007 - 1:07 pm UTC
when you use your raid-5 without ASM, how was it configured.
probably, you used a normal "cached filesystem" and your physical IO's were not true physical IO's (they were reads from the file system cache!!!)
and hence, your buffer cache should have been larger with ASM (which is directio - faster true physical IO, but if the physical IO used to be against the file system cache)
so, I would suggest you increase your buffer cache to compensate for the file system cache that is not there anymore.
To: Rijesh
Tom Fox, July 07, 2007 - 12:07 am UTC
Do you feel that the performance degradation in ASM with RAID5 is because of the NORMAL redundancy?
What Tom said is true, but I'd also like to add, that you have additional overhead in using normal redundancy on top of an already RAID'ed filesystem. I guess that buys you more protection, but it may be that the cost exceeds the benefit.
I'd try testing it with external redundancy and see if there are any changes.
July 07, 2007 - 10:49 am UTC
but the writes are in the background (dbwr) and the reads would not be affected, so - I doubt it.
ASM normal redundancy vs external redundancy
A reader, July 09, 2007 - 8:48 pm UTC
Are there any significant advantages one way or another between the following cases:
1. SAN provisioned raid 10 storage with ASM using external redundancy.
2. SAN provisioned disks (no RAID, just the raw devices) with ASM and normal redundancy.
At the moment the only ones I can think of are:
- No hot-spare disks are needed for #2, free-space provides the ability to rebuild.
- #2 is easier to grow/shrink while maintaining balanced IO between all devices
- #1 may be quicker for reads, as ASM will only read from the primary extent (unless it fails). A SAN RAID10 can read from any copy.
Are there any others?
July 09, 2007 - 9:03 pm UTC
#2 - dba has control, I view that as a plus. they have to understand the physical layout of the disk, but they have control
Shouldn't #1 write faster?
A reader, July 10, 2007 - 4:29 am UTC
Shouldn't #1 write faster? I mean, with ASM redundancy I guess ASM must issue at least two write operation, one for each copy, while with external redundancy it should issue only one write operation, the hardware controller will take care to write the copies. It should lower SAN bandwith usage too, especially for large writes. Or I'm wrong?
July 10, 2007 - 11:56 am UTC
async IO lets ASM do things in parallel, without blocking. So, no, not really.
and since most all writes happen in the background, it really doesn't affect you for the most part.
ASM disk extension.
keyur patel, July 13, 2007 - 12:27 pm UTC
Hello Tom:
We are moving our database from Local Disks to EMC Clarion on Sun solaris. We were able to add a disk group to our ASM instance and move some files to that newly created diskgroup. That disk group has external redundancy. One already exist local disk has ASM with Mirror and Striping. Now we were trying to test LUN extension. SA are able to do LUN extension from 6 gb original size to 10 gb. But they said Sun solaris can't see that extension and in order to make it available those added space to o/s they need to re-format original 6gb LUN. IF they re-format we lose our data on that LUN and we need to re create disk group again. Do you have any idea how to add space to already created lun so we can just resize out ASM disk to increase the capacity.
Thanks
July 13, 2007 - 1:29 pm UTC
that would be a question for EMC and Sun to address - outside my area of expertise.
Followup for "ASM Raid5 performance"
Rijesh, July 24, 2007 - 5:31 am UTC
Hi Tom,
This is in followup to my post on July 6th.
We got the performance improvement in ASM with External redundancy[RAID5] than we were getting with Normal Redundancy.
We didn't change any other settings in the DB server side and used the same set of oracle initora parameters.
Thanks.
July 24, 2007 - 9:52 am UTC
sorry, this doesn't correspond to the above. above you said "we tested with and without ASM"
Now you are saying "we tested ASM vs ASM"
which was it, how was it tested, what were the results
main reasons to use asm
Andrea A.A. Gariboldi, November 11, 2007 - 2:03 pm UTC
It is correct to say, that one SHOULD use ASM:
1) To use a better 'file system'. (ASM way to store data on devices)
2) To have a more flexible way of managing database (moving data from disk to disk... change data protection and availability policy over time).
3) To have a single point to manage I/O resources, used by different instances. (??)
4) Give DBAs more control on I/O resources.
Thanks,
Andrea
November 11, 2007 - 7:40 pm UTC
yes, those are all valid
Tom Fox, November 14, 2007 - 10:13 am UTC
sorry, this doesn't correspond to the above. above you said "we tested with and without ASM"
Now you are saying "we tested ASM vs ASM"
which was it, how was it tested, what were the results
I think Rijesh meant he changed ASM to external redundancy after his original ASM with normal redundancy vs no-ASM test, and found he got better performance.
I still don't understand why normal redundancy would be used with external array devices. You're using the host CPU to manage I/O resources that don't really need to be managed at that point, since you already have redundancy from a dedicated device.
Best Practice
Utpal Dhar, December 10, 2007 - 3:45 pm UTC
Hi Tom,
I am about to create an Oracle 10gR2 3-node rac cluster connected to a HP MSA 1000 SAN. I have 10 - 300GB (10k) and 10 - 300GB (15k) hard disk availbe on the SAN.
I plan to create two arrays of 10 disks each and use the first array for OCR, Voting Disks and Recovery on OCFS2 and create a disk group on the second array for the database on ASM. I am going to do RAID-5 on both the arrays.
This is going to be for an OLTP production application and i wanted to know how best i could configure the disks. Are there any best practices that you could recommend and there are some questions that come to my mind:
1. Should i do ASM with external redundancy (raid 5) or ASM with High redundancy.
2. If i create one disk group then everything goes in there. i.e datafiles, redo log files, undo tablespace, temp tablespae etc.. Is that normal practice when using ASM?
3. When expanding an array which would be much easier and faster, raid-5 or asm high redundancy?
4. How many disk failures can raid-5 (i think just one) and high redundancy handle at a given point in time.
5. I don't understand the concept of failure groups.
6. Since OCR and voting disks cannot be put on ASM, i chose to use OCFS2 for them and also for recovery. I know i can put Recovery on ASM but then i would have to create OCR and Voting Disks on 3 - 300 GB hard disk if i do raid-5. That does not sound right.
Thanks,
Utpal
December 10, 2007 - 4:34 pm UTC
you know what I like about best practices? there are so many to choose from.
If there were a universal best, there would be no other way....
1) you tell me, what would you LIKE. raid-5, good for reads, not good for writes so much, low to fair resiliency to failure. Mirrored Stripes (ASM) - good IO, highly available.
2) it is normal to some people, it is an aberration to others. "it depends". Do you want to segregate these file types or not? Something to be said for not using raid-5 on log, something to be said for no redundancy on temp (to give you more), undo and data tablespaces are written to in the background by dbwr - we can live with raid5 on those files much easier than say log files.
but then again, could be that a single massive logical device is better than good enough.
3) they both start at the same place - add hardware. then they both just issue commands and rebalance. they both take the same amount of time - no time practically speaking because it is an online operation (well, for ASM it is, cannot speak for your raid implementation)
4) raid-5 is low from availability. raid-10 (which is ASM like) is high.
5) sorry? Have you read about them? They are groupings of devices that are independent from each other - a hardware failure would/should not affect two different failure groups. The disks in a single failure group are dependent on each other - the disks in two failure groups should not be.
eg: if you have two disks and they use the same controller - they should not be in separate failure groups for each other, the failure of a controller would take BOTH of them out - there is nothing to "fail over" to.
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14220/glossary.htm#sthref4154 6) that is what you get when you buy huge disks. So, it does sound right since you have such large units of allocation.
ASM on extended RAC
Josep, December 14, 2007 - 5:43 am UTC
Hi Tom, I have a question about ASM suitability on extended RACs.
We have now a setup with 2 Symmetrix Cabins (25 Km away), syncronized via srdf. ASM mounts disk from just 1 cabin. In case of failure (mainly contingency because all storage have redundant components and disks), we can mount 2nd cabin disks an restart ASM, with some outage (disk management + oracle restart).
I wonder if we could improve our arquitecture just breaking srdf, and having our ASM DISK_GROUP with 2 failure groups (1 x cabin with all its disks) and normal redundancy. I think thats the idea about failure groups but I'm wondering if there are other issues playing in an extended RAC setup, that could make that idea not appropiate. This could solve the outage...
What do you think? Would it work ok?
December 14, 2007 - 1:17 pm UTC
I've no experience with that sort of configuration, sorry.
Sync or Async ?
Andy, December 14, 2007 - 7:46 pm UTC
The key question here is if you are running the remote storage in synchronous mode. If you are then you are already waiting for each IO to be performed on the remote site and for the remote to acknowledge.
If you aren't then doing this now then you are likely to slow your system down by impacting the underlying IO times as ASM will need to write to both sets of disk before considering anything to be written.
Fast Failover in 10g will give you better failover behaviour if you use dataguard in sync mode but it won't be completely transparent.
Josep, December 17, 2007 - 7:23 am UTC
Thank you Andy.
After setting up our env, tech manager decided that is should use remote disk replication in syncrhonous mode. Abailability/Contingency is his concern, and the IO latency should not be a problem (at first sight).
I guess latency will be slighty lower with srdf than with ASM extended to both cabins, as its done on lower I/O stack and presumably with high performance link (dark fiber?). But I really don't know, and its difficult to perform comparative tests.
We've not considered Dataguard so far. As it involves more things to manage.
Ragards, Josep.
ASM normal redundancy vs. RAID10 one thing to consider.
Adrian Angelov, November 14, 2008 - 2:01 pm UTC
I would like to share a thought on ASM normal redundancy and RAID10. There is a difference and I think it's a very important one.
Let's say we have 20 disks. Striping will be on 10 disks - called 'set1' and mirroring on 'set2'(remaining 10 disks). This can be done using
- ASM: one diskgroup configured using normal redundancy. There will be two failure groups(failure group 1 will use 'set1' and failure group 2 - 'set2')
- RAID10('set1' is a mirror of 'set2')
In case of a disk failure(let's say one of the disks in 'set1' ), ASM will start a rebalance operation to spread all lost data which was available on the failed disk(data now only available on 'set2') on all remaining disks in 'set1' to meet the normal redundancy diskgroup requirement. During this rebalance, subsequent failure of a disk on 'set2' will trigger ASM instance to unmount the diskgroup. And in this situation it doesn't matter which disk has failed on 'set2'. The storage will not be able to process requests.
When RAID10 is used and a disk in 'set1' fails, even after a subsequent 'set2' disk failure, there is a possibility that the storage will be able to process requests. The storage will fail in case of a particular disk failure in 'set2'(the mirror of the failed disk on 'set1') - 1/10th possibility. In all other cases the storage will survive- 9/10.
Am I correct or wrong? Please, comment.
Thanks for your efforts to all of you and especially Tom.
November 18, 2008 - 6:14 pm UTC
... Let's say we have 20 disks. Striping will be on 10 disks - called 'set1' and
mirroring on 'set2'(remaining 10 disks). ...
but that is not how raid 10 works - raid 10 is like ASM - mirrored stripes, of the 20 disks - all will contain a) primary chunks, b) backup, redundant chunks.
striping takes place over all 20.
and each of the 20 has backup and primary bits.
we just make sure that the backup for a primary is on a different failure group inside the disk group.
can you use diskgroup and failure groups in your nomenclature so I understand what you are talking about - and point to documentation if you feel it is relevant.
Adrian Angelov, November 19, 2008 - 6:57 pm UTC
Take a look at RAID10 section on
http://www.howtofriends.com/raid/ (if this picture is wrong, ok, I'm wrong too and many thanks for your clear answer,anyway, in either cases thanks :) ).
Combining the meaning of the picture with my previous post example(think with 4 disks instead of 20):
If configured using RAID10 my terms used will be:
'set1' consists of disk0 and disk1
'set2' consists of disk2 and disk3
Simultaneous failure of disk0(set1) and disk3(set2) in RAID10 configuration as described will not lead to availability problems.
If configured using ASM:
One diskgroup with normal redundancy and two failure groups:
failure group 1 - disk0 and disk1
failure group 2 - disk2 and disk3
Simultaneous failure of disk0 and disk3 will lead to data unavailability(diskgroup dismount).
November 24, 2008 - 11:13 am UTC
think disk group please - the disk group is your 'set1' and 'set2'
if you have two sets, you have two diskgroups.
Adrian Angelov, November 24, 2008 - 11:46 am UTC
Ok, it was not a good idea to mess up with sets and self terms. Simply said:
After simultaneous failure of two disks in a 4 disk configuration:
- when RAID10 is in use, it will fail in 2 of all 6 possible cases.
- when ASM normal redundancy group with 2 failure groups is in use, it will fail in 4 of all 6 possible cases.
And things are getting worse with ASM compared(regardless availability) to RAID10 with more disks and failure groups.
Thanks.
November 24, 2008 - 7:34 pm UTC
ok, back up.
give us the set up - from soup to nuts. disk group setup, then your raid 10 setup.
and make sure they provide the same characteristics (eg: if you have two sets, I want two diskgroups and so on)
eg: if you want set1 and set2 we would create two diskgroups
diskgroup1: disk0 and disk1. disk0/disk1 are in separate failure groups.
diskgroup2: disk2 and disk3. disk2/disk3 are in separate failure groups.
So, disk0 will have the primary copy of an extent for some segment and disk1 will have the copy of it. disk1 will have the primary copy of an extent for some other segment and disk0 will have a copy of it.
Same with 2 and 3.
Simultaneous failure of disk0(set1) and disk3(set2) in RAID10 configuration as described will not lead to availability problems.
change set1/set2 to read diskgroup1 and diskgroup2
change raid10 to be ASM with normal redundancy
and the statement is still true.
Adrian Angelov, November 25, 2008 - 7:57 am UTC
'and the statement is still true. '
Yes, the statement is true, but in a real world scenario with let's say 40 disks, you'll have 20 diskgroups(2 disk drives per diskgroup) to have the same availability as with RAID10.
I don't think this is practical because:
- one datafile can be in only one diskgroup at a time.
- striping is only on two disks in this case.
I appreciate your valuable comments. Your followups are another point of view of what I'm thinking about.
Thank You.
November 25, 2008 - 11:52 am UTC
please - map it out.
raid 10:
one datafile can only be in one 'set' at a time.
striping is only on two disks in this case.
Please - if you think ASM is less than RAID 10, draw it completely out.
If you have 40 disks, you could have anywhere between 1 and 20 diskgroups with normal redundancy - with raid 10 or ASM
Please - clearly and specifically give us a raid 10 setup that you claim cannot be done the same way with raid 10?
Take your 40 disk scenario and make it more 'available' using raid 10 (and then I'll just change your word "set" to "diskgroup" and viola' - the statement will still be true once again)
Adrian Angelov, November 25, 2008 - 6:28 pm UTC
Let's say:
- every hard disk drive is 20GB
- there is a 100GB datafile named 'myfile.dbf'.
- 20 hard disk drives available
here it the mapping:
I. RAID10
dA dB dC dD dE dF dG dH dI dJ
dK dL dM dN dO dP dQ dR dS dT
dA and dK is mirrored pair
sB and dL is mirrored pair
...
dJ and dT is mirrored pair
It's obvious that 'myfile.dbf' will be on more than 2 disks and it's possible to have it on this RAID10 configuration.
II. Your point of view when ASM is in use:
dA dB dC dD dE dF dG dH dI dJ
dK dL dM dN dO dP dQ dR dS dT
dA and dK constitute diskgroup1 normal redundancy
dB and dL constitute diskgroup2 normal redundancy
...
dJ and dT constitute diskgroup10 normal redundancy
There is no way to have this 100GB file('myfile.dbf') in your ASM diskgroups since one datafile can not belong to several diskgroups. Diskgroup non-mirrored max size is 20GB in this configuration.
So, this configuration is not practical.
III. Oracle recommends one diskgroup for the database files and one diskgroup for backups:
source:
Oracle Database 10g Release 2
Automatic Storage Management
Overview and Technical Best Practices
(
http://www.oracle.com/technology/products/database/asm/pdf/asm_10gr2_bestpractices%2009-07.pdf )
"
To reduce the complexity of managing ASM and its diskgroups, Oracle recommends that generally no
more than two diskgroups be maintained and managed per RAC cluster or single ASM instance.
o Database area: This is where active database files such as datafiles, control files, online redo logs,
and change tracking files used in incremental backups are stored
o Flash recovery area: Where recovery-related files are created, such as multiplexed copies of the
current control file and online redo logs, archived redo logs, backup sets, and flashback log files
...
5. As stated earlier, Oracle generally recommends no more than two diskgroups. For example, a
common deployment can be four or more disks in a database diskgroup (DATA diskgroup for
example) spanning all back-end disk adapters/directors, and 8-10 disks for the Flash Recovery
Area Diskgroup.
"
dA dB dC dD dE dF dG dH dI dJ
dK dL dM dN dO dP dQ dR dS dT
let's these disks constitute diskgroup DATA used for database files(not backups).
Example statement for its creation:
CREATE DISKGROUP data NORMAL REDUNDANCY
FAILGROUP fg1 DISK
'/devices/dA' NAME dA,
'/devices/dB' NAME dB,
'/devices/dC' NAME dC,
'/devices/dD' NAME dD
'/devices/dE' NAME dE,
'/devices/dF' NAME dF,
'/devices/dG' NAME dG,
'/devices/dH' NAME dH,
'/devices/dI' NAME dI,
'/devices/dJ' NAME dJ,
FAILGROUP fg2 DISK
'/devices/dK' NAME dK,
'/devices/dL' NAME dL,
'/devices/dM' NAME dM,
'/devices/dN' NAME dN,
'/devices/dO' NAME dO,
'/devices/dP' NAME dP,
'/devices/dQ' NAME dQ,
'/devices/dR' NAME dR,
'/devices/dS' NAME dS,
'/devices/dT' NAME dT;
Summary:
I. and II. have the same availability, I agree, but II. is not practical, I cannot store bigger than 20GB datafile in such configuration.
I. and III. are not the same when availability is considered.
I. - simultaneous failure of dA and any of dL,dM,dN,dO,dP,dQ,dR,dS,dT will NOT make RAID10 unavailable.
The only simultaneous two disk failure ,when one of the failed disks is dA, and will make RAID10 unavailable, is when the other failed disk is dK.
III. - simultaneous failure of dA and any of dK,dL,dM,dN,dO,dP,dQ,dR,dS,dT will make diskgroup DATA unavailable.
P.S. You see, no word 'set' written by me(only 'backup sets', but not me, it's Oracle, and it's not relevant), so no 'set' changes :)
December 02, 2008 - 11:00 am UTC
but, would you really set up raid10 on two disks - that means the loss of two disks can ASSURE you lose access to data.
Typically you would stripe many more - so that if a disk failed - the system would pick up and re-duplicate the missing information on the excess capacity. As long as you have sufficient free space to remirror - it does. So that the subsequent failure of another disk cannot cause data loss.
So, you can configure asm to do it like you did it - but it would not be very available (that configuration isn't). You would just use 20gb files (to which I say "so what", it looks pretty much to the same to everyone - they just see a tablespace). Either approach would want many disks in there to be 'less failure prone'
Having two disks in a single raid 10 set - do you *really* do that. That is the minimum disks - but I haven't seen it.
Adrian Angelov, November 25, 2008 - 6:49 pm UTC
Of course, RAID10 max file size depends on the filesystem used, no problem when ext3 in use.
RAC AND ASM
lalu, January 16, 2009 - 6:01 am UTC
Hi Tom,
I have a 2 node RAC cluster.Trying to create the ASM instance on it using dbca.
The ASM instance creation happened successfully and during diskgroup i am getting error:
Before Creating diskgroups:
SQL> select NAME FROM v$asm_diskgroup;
no rows selected
During DG creation(DBCA)
ORA-15018:diskgroup cannot be created.
ORA-15030:diskgroup name "MENADATA" is in use by another diskgroup.
SQL> select NAME FROM v$asm_diskgroup;
NAME
--------------------------------------------------------------------------------
MENADATA
SQL>
The above diskgroup was in unmounted state.The free bytes as well size shows 0.
And getting error while trying to mount it.
Thanks.
lalu.
RAC AND ASM
lalu, January 16, 2009 - 6:25 am UTC
Hi Tom,
Apologize for the incomplete info to the above thread.
OS-Solaris 10.
I have done the wipe out of complete raw device using dd before the dbca.
Thanks.
ASM on top of OS volume manager
Jacky, June 15, 2009 - 5:22 am UTC
Oracle doc says:
"Although you can also present a volume (a logical collection of disks) for management by ASM, it is not recommended to run ASM on top of another host-based volume manager."
The raw devices (exist as logical volume) on our server are created with Veritas Volume Manager. I am very concerned if I should proceed with ASM instead of using direct raw disk (no ASM). Please shed some lights.
June 15, 2009 - 12:24 pm UTC
It is OK to stripe a stripe - you can do this
you do not *need* to, it can easily lead to *confusion* on your part since you have so many layers of indirection
but you can
Thank you
Jacky, June 16, 2009 - 3:20 am UTC
Hi Tom,
Thank you for your comment. I appreciate it.
upgrade...
A reader, June 23, 2009 - 9:27 am UTC
Tom,
We presently have:
02 node RAC cluster.
having total 10 databases. so 10 instances on each host.
Oracle 9.2.0.8
HP Ux 11.23
VCS 4.1 for Cluster management
&
vxvm, vxfs 4.1 for disk management
now aiming
Oracle 10.2.0.4
CRS for CLuster management
ASM for disk management
Questions
a) What would be the upgrade path...?
my understanding
a1) upgarde vCS, vxvm & vxfs to 5.0 ( min pre-requisite for 10.2.0.4)
a2) upgrade current databases to 10.2.0.4
a3) Take out the second node from cluster
a4) Install CRS & ASM on second node
a5) Create physical standby on second node from 1st node
a6) migrate physical standby to ASM
a7) Open 2nd node ( physical standby ) as primary
a8) Install CRS ASM on 1st node
a9) add 1st node to the cluster
b) How many ASM instances we would need? is it per database or per host basis?
c) does CRS, ASM will cost us additionally or it would be part of 10.2.0.4 upgrade?
( if you wish you can skip (c) as i will get it from other sources in Oracle )
many thanks.
regards
June 26, 2009 - 9:12 am UTC
a) please utilize support/consulting for creating an upgrade plan if you don't have one. I don't install/upgrade on every platform, every release.
b) you need an ASM instance on each host machine, it is like a file system driver - so just like you run veritas everywhere, you would run ASM everywhere.
c) the cluster ready services and ASM come with - they are part of what you purchased.
asm mirror
reader, September 25, 2009 - 9:40 pm UTC
assuming I have normal redundancy with asm mirror with a failure group. Does asm read from both groups or only from primary group? RDBMS version is 10.2.0.4.0. Thanks.
September 29, 2009 - 12:01 pm UTC
failure groups are peer sets of disks that are independent from each other.
each "member" of the group contains primary and backup chunks. Failure groups are what we mirror across - if you have failure group 1 (fg1) and failure group 2 (fg2) - it is assumed that if fg1 fails - fg2 will still keep on trucking - therefore we would protect data that has a primary copy in fg1 on fg2 and vice versa.
disk header corruption
Ramki, January 14, 2010 - 12:26 pm UTC
Dear Tom,
In our customer place we had ASM disk header corruption.
We are using external redundancy RAID 1+0 at HW level.(EXTERNAL REDUNDANCY)
We lost all data :-((.
we are using Oracle 10g. We are not able to find the root cause of the problem.
now i come across some link it says
<<
http://www.orafaq.com/wiki/ASM_FAQ#ASM_disk_header.2Fsuperblock_backups.3F >>
from the link In Oracle 10g, the only viable method to prevent logical corruption of ASM header block is to add failgroup. Is it true ? by creating normal redundancy & failgroup can we avoid disk header corruption?
please advice us.
Regards
Ramki
January 18, 2010 - 4:43 pm UTC
hopefully you lost data on disk - not truly "lost all data". If you truly lost all data - then shame on you - everything breaks *everything*.
You can have header corruption for any file system, and you'd need to go to backups to fix it. For all of them.
External redundancy - great way to have errors instantly mirrored to all devices :)
that link says you would restore the diskgroup. If you want an easier way to survive a corruption, you can use a failure group
again, you should not have lost a byte of data - if you have backups.
if you don't have backups, you will lose every bit of data - over and over and over again.
disk header corruption
Ramki, January 18, 2010 - 11:31 pm UTC
Thanks Tom,
>> External redundancy - great way to have errors instantly mirrored to all devices :) >> true storage guys said "it mirrored corrupted data also perfectly".
Can you please explain me how this will not happen in case of Normal/High redundancy.
Will oracle do some checksum before mirroring.
And general comment from my architect is failure group will be slow in read/write so they want to use External redundancy.
January 19, 2010 - 4:23 pm UTC
we'll have separate groups - not mirroring of header there.
Why would
a) us writing in parallel to two devices versus
b) them writing in parallel to two devices
be automatically considered slower?
but the alternative is to be prepared to restore the disk group -their/your choice.
ASM & RAID
Balakrishna, January 28, 2010 - 12:31 am UTC
Hi,
I have read all the followups and i couldn't understand one thing do we really need RAID when we are using ASM. If yes , i appreciate your comments on this please..
Regards
Bala
January 29, 2010 - 3:32 pm UTC
ASM provides striping (always) and mirroring (optionally). It is sort of like raid 10 already.
It would be OK to use a RAID set of disks for redundancy if you like - but not necessary.
Does ASM remove the necessity of Multiple Tablespace
Umesh Kasturi, January 29, 2010 - 9:53 pm UTC
I have a table with the following structure
( each of the tablespaces are on different files )
partition on a tablespace TBSP1
sub_part1 on tablespace TBSP_A
sub_part2 on tablespace TBSP_B
sub_part3 on tablespace TBSP_C
sub_part4 on tablespace TBSP_D
There are 10 partitions with the above structure in a table
The company has hired an expert , and the expert suggests
"There is no benefit to the multiple Tablespaces, however you should still follow the same indexing and partitioning strategies. ASM will manage the workload across the tablespace, indexes and data. "
In view of this should I MOVE all the sub_partitions to the main tablespace TBSP1 . Is this right
I think ASM will manage the file system and is NOT related to spreading of data across multiple tablespace and files
What do you say on this ? Please Suggest
Thanks in advance
February 01, 2010 - 9:25 am UTC
I read the subject
"Does ASM remove the necessity of Multiple Tablespace"
and knew immediately you don't know what tablespaces are for....
and the 'expert', hmmmmm, consider them "caught".
A tablespace is used to make your life more enjoyable and easier, you use it to organize things in a manner that pleases you. They are not about performance, they are about making the life of the DBA more pleasant. They are an administrative, organizational tool - that is all.
To the "there is no benefit to the multiple tablespaces" - hah, what a joke. Of course there MIGHT be.. Depends on what you are doing. What if your partitioning by some data that implies age (range partitioning on a date column for example) and in your system data that is older than one year is READ ONLY. Wouldn't it be nice to have a years worth of data in a tablespace so you could after a while mark that tablespace read only and back it up one last time and never again??? That would be nice, and that would be a benefit of using multiple tablespaces.
What if you purge data over time by old partition? Wouldn't it be nice to be able to completely empty out a tablespace and have it just be "empty" after a while so you could reuse it or get rid of it? It might be nice.
It might be nice to have smaller units of recovery - what if a tablespace got damaged in some way? If it had everything - well, think about how that would compare to if it had just 10% of something?
Tablespaces - not about performance, all about making your life better.
Does ASM remove the necessity of Multiple Tablespace
Umesh Kasturi, January 31, 2010 - 9:55 pm UTC
Can you please help me with my above Question.. Thanks
February 01, 2010 - 10:26 am UTC
can you imagine a world in which I enjoy my weekend..... hmmmmmm
I did.
Excellent rejoinder
Subra, February 01, 2010 - 10:30 am UTC
That was a good response. Not bad for a Monday start :)
Does ASM remove the necessity of Multiple Tablespaces
Umesh Kasturi, February 01, 2010 - 9:41 pm UTC
Thanks Tom. Sorry for the reminder follow-up.
I agree that Tablespaces are for spreading out data and ease of administration. All my Tables are similar in nature and the prototype is as below
CREATE TABLE ACCT_DOC(
ACCT_DOC_ID NUMBER NOT NULL,
CUST_JRNY_ID NUMBER NOT NULL,
ISSUE_DATE DATE,
)
PCTFREE 0
INITRANS 16
PARTITION BY RANGE (ISSUE_DATE)
SUBPARTITION BY HASH (CUST_JRNY_ID)
SUBPARTITIONS 4
STORE IN (tbsp_a,tbsp_b,tbsp_c,tbsp_d)
(
PARTITION tbsp_1
VALUES LESS THAN (TO_DATE ('2008-07-01', 'YYYY-MM-DD'))
TABLESPACE tbsp1
LOGGING
STORAGE(MAXEXTENTS UNLIMITED
)
(SUBPARTITION ACCT_DOC_1_A TABLESPACE tbsp1_a,
SUBPARTITION ACCT_DOC_1_B TABLESPACE tbsp1_b,
SUBPARTITION ACCT_DOC_1_C TABLESPACE tbsp1_c,
SUBPARTITION ACCT_DOC_1_D TABLESPACE tbsp1_d)
.......
.... there will be more partitions like above structure
;
I am hashing the based on the CUST_JRNY_ID as the values in this column can have varied data.
In the above context does it really help by removing the Sub-partitons ? I don't think so .
Thanks
February 02, 2010 - 12:12 pm UTC
can you tell us why you are using multiple tablespaces, what was/is your goal - what are you accomplishing by doing that.
I'm not saying "don't do it", rather I'm saying you can answer this expert yourself, when you tell us WHY you are doing it the way you are.
If you have no logical reason, if you have no illogical reasons even, then it would make sense to ask yourself "why" - and when you have the answer, there you go.
You would be using hashing under your range partitions simply to keep the size of those partitions down to 25% of what you would have otherwise. If your partitions were large in the first place, this makes good sense. If they were small - maybe not.
hopefully cust_jrny_id is almost unique - has lots and lots of distinct values - to spread the data out.
ASM and Multiple tablespaces
Umesh Kasturi, February 03, 2010 - 5:13 am UTC
Thanks Tom.
Yes the CUST_JRNY_ID has lots and lots of distinct values to spread the data out on to the sub-partitions.
With ASM being implemented, does it help in removing Multiple tablespaces and going for a Single tablespace w.r.t performance of inserts and selects?
Thanks
February 03, 2010 - 10:11 am UTC
ASM is just a filesystem - a good filesystem - but just a filesystem.
The same practices you would follow on UFS, NFTS, raw, OCFS, ACFS, whatever - do with ASM. ASM just gives you files.
files that are striped.
files that are mirrored.
files that are easy to grow and shrink (without having to move or reorganize objects).
and more
but files none the less. It would not change the way you think about tablespaces.
mfz, March 01, 2010 - 8:10 pm UTC
I am a newbie to ASM.
Initially , I created 11g ASM instance on Windows ( to get familiar with ASM) .
After that , I was playing with the disks (changing the label of the disk ) ... resulting in non functional ASM instance.
Please advise of how to fix this . ....
C:\>sqlplus / as sysasm
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 1 20:59:10 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ASM instance started
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 509162388 bytes
ASM Cache 25165824 bytes
ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"DISKGP02"
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE NAME PATH
------------ ----------- ------- ------------ -------- ------------------------------ -------------------
----------------------
0 0 CLOSED MEMBER NORMAL \\.\ORCLDISKDATA0
1 0 CACHED MEMBER NORMAL DISKGP01_0000 \\.\ORCLDISKDATA10
1 1 CACHED MEMBER NORMAL DISKGP01_0001 \\.\ORCLDISKDATA20
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
March 02, 2010 - 7:18 am UTC
restore the data as it was before you did this thing.
Pratik, May 04, 2010 - 6:38 am UTC
Hi Tom,
I want your approval stamp on my thoughts. It helps me a lot to go into right Direction.
I have 160GB of database and 4 disks of 100GB each.
I made one disk group with two failgroups. each failgroup has 2 disk assign.
My doubt is, if in this scenario any of disk fail in any failgroup then it is not possible for ASM to do re-balance because of lack of space.
To ensure Full redundancy in event of disk failure, I have to get 2 more 100GB disks and need to add one more disk in each failgroup.
So here, for 160GB database I need 300GB of two failgroups. So total space required is 600GB for 160GB database.
Is this ok ? is there any other way so that we can reduce required storage space and get same I/O performance and reliability?
Thanks for AskTom
May 06, 2010 - 12:54 pm UTC
... Is this ok ? ...
that is something only you can answer. Is it OK given your system requirements for availability and cost?
If you need to maintain redundancy even in the event of a failure of one disk at all times, the math is the math.
If you have 200gb protected by 200gb to store 160gb
And you lose 100gb of that storage, you now have
200gb protected by 100gb to store 160gb - meaning at least 60gb is not going to be redundant.
Meaning, you need to keep at least one hot spare available and add it to the disk group with a failed device when it fails OR have two more disks always there so you have 300gb storing 160gb protected by 300gb.
Large DB migration,
A reader, June 23, 2010 - 2:23 pm UTC
Hi,
I want to run through an option which I am thinking about to migrate (relocate) large DB (16 TB) from one DB to another.
Our situation:
-3-node RAC (node 0a, 0b and 0c)
-Database and ASM instance is only on node 0a and not registered to cluster. Diskgroup name is +data1. However, we have ASM instances (+asm1,+asm2 and +asm3) in the cluster but this doesn't know about +data1 diskgroup
-Need to migrate the database to different set of disks which is 20 TB (from tier2 to tier1 storage)
Approach that I am thinking to take:
-- Add the new 20 TB (about 100 LUNs) to the existing data diskgroup (+data1). At this stage the total size of ASM diskgroup is 36 TB
-- Oracle will rebalance in the background
-- After rebalance completes, drop the old LUNs from the diskgroup
-- Rebalance will happen again. During the rebalance, database can be accessed for read-write purposes.
-- shutdown the database and rename the database
-- Register the other two instances to cluster and make it RAC database
Will this concept work and is safe to use?
Other question: The ASM instance on node 0a is not registered to cluster. How can we make the +data1 diskgroup visible to ASM instances which is on the cluster?
Thanks,
June 24, 2010 - 6:55 am UTC
...
-3-node RAC (node 0a, 0b and 0c)
-Database and ASM instance is only on node 0a and not registered to cluster.
....
this is not clear, do you mean to say that host 0a is having two instances - one a RAC member and the other the database of concern?
Is this RAC database even of consequence in this discussion?
I get really confused by :
... -- shutdown the database and rename the database
-- Register the other two instances to cluster and make it RAC database
...
that wasn't part of the original thought process here.
Is the question really:
I have a single instance oracle database, 16tb in size. I would like to migrate the data to a new set of disks AND make it a RAC database - how do I do that.
(the only correct number of instances on a host is ONE, if you really have two instances on NODE 0A, you want to rethink that.)
Large DB migration,
A reader, June 24, 2010 - 9:31 am UTC
Sorry for the confusion. I am finding it hard to convey my thoughts even at my work place :-(
This is NOT a RAC database. The database on node 0a is using Oracle binaries that we used for RAC purposes. There is no other database
running on node 0a (besides database instance and ASM instance).
For convenience let me give the name of database instance and ASM instance on node 0a.
DB name: CPG
DB Instance: CPG
ASM Instance: ASMFC
Diskgroup name: +DATA1
Tasks we need to perform:
1. Migrating the database to new diskgroup
2. Rename CPG database to CPGEDM
3. Rename ASMFC instance to ASM1
4. Add the other two instances (node 0b and node 0c) and make CPGDM database as RAC
I am not thinking to create CPGDM database (say on node 0b) and restore the CPG database using hot/cold backups.
My approach towards migration are:
1. Add new disks to existing diskgroup, allow Oracle to rebalance and drop the old disks. At the end, we will have +DATA1 diskgroup with new set of disks.
2. create a new diskgroup (+DATA2 for example) which ASMFC instance can recognize,migrate the contents from old diskgroup to new diskgroup (using RMAN)
If I use either of the two approaches, my tasks 2,3 and 4 (above) still need to be done. I need your input on how task #3 works.
Thanks,
July 06, 2010 - 9:31 am UTC
but adding new disks to an existing diskgroup will not, cannot achieve "migrating the database to a new diskgroup"??!!!
that will move data from one set of devices to another, but the diskgroup will remain "the same"
why would you do this - just to rename something?
http://docs.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_1007.htm#SQLRF01113 likewise - renaming a database is documented. Frankly, I wouldn't bother renaming a database - nothing really uses the database name - the global_name yes (that is easy, alter database rename global name). But you can do it.
as for renaming an instance, all that takes is changing an environment variable - and if you want, renaming startup files to incorporate the new name ( the init.ora files)
A reader, June 28, 2010 - 3:38 pm UTC
ASM and application
Dan, January 03, 2011 - 11:20 pm UTC
Hi Tom:
Our application is third party vendor application.
Can they run on ASM storage management ?
We are planning to move database (Oracle 10.2.0.3) from Veritas managed storage to
ASM (Oracle managed).
Is ASM operation transparent to vendor application ?
Do we need to consider application infrastructure (vendor feedback) before migrating database to ASM ?
Thanks for your inputs.
January 04, 2011 - 7:12 am UTC
... Can they run on ASM storage management ?
...
it depends. Is the 3rd party SAP - maybe not. SAP tries to hide the fact there is a database from you as much as possible. They do all of the admin stuff.
Is the 3rd party a simple thing that mandates you have the tablespaces created up front and they never do any administration type of commands? Then probably yes.
You would need to discuss this with the vendor. It would be transparent to database clients - but if their software does the admin stuff of adding datafiles - doing things during an upgrade and so on - maybe not.
ASM and application
Dan, January 04, 2011 - 8:43 am UTC
Thanks Tom.
My vendor application is purely insurance application which does not perform database admin task. We, as DBAs does all admin tasks like tablespace creation/extending data files and so on.
So it looks to me ASM storage is compatible with this vendor app.
I will discuss with vendor.
Thanks!
January 04, 2011 - 9:04 am UTC
I don't see any reason why it would not work in this case given the information available.
number of LUNs and size of LUNs
A reader, January 07, 2011 - 2:13 pm UTC
Hello,
We have Oracle 11g both single instance and RAC on Linux and HP-UX using ASM. To have an efficient storage system, is there a thumb rule on how many LUNs and how big each LUN should be?
Say, the database is 1 TB. Is it good to have 100 GB times 10 LUNs, 250 GB times 4 LUNs, 500 GB times 2 or one big TB LUN?
Also what is the impact if we have multi-sized LUNs (like 200 GB, 300 GB, 400 GB and 100GB).
Thanks,
January 07, 2011 - 2:48 pm UTC
well, you cannot really have a single answer for this - it depends.
The luns take a set of physical devices and physical resources and put them together as a single logical unit. If you are going to take 1tb of stuff and just stripe over it using ASM - it won't matter that much if you have 1 or 10.
If you go with more luns and keep them physically separate - you can use ASM mirroring for redundancy. You will have the ability to segregate things if you want - you could put redo over there, temp over here, data over there - if you want. In general - just striping everything together would be reasonable.
I guess it would not hurt to have more, smaller, independent sets of resources so you can set up failure groups if you want, so you can segregate things if you want. One big on is simple - if you are doing external redundancy, more smaller ones let you slice and dice things differently - if you want.
follow up,
A reader, January 07, 2011 - 3:44 pm UTC
Thanks for the explanation. There are three different aspects that I am looking at:
1. Performance
2. Cost
3. Maintenance/Support.
I believe from your explanation, from performance point of view, the size of LUNs may not matter much but it will provide a greater flexibility if we mave more smaller size LUNs.
Say, if we use thin provisioning, will it matter if we go for one big size LUN?
Thanks,
January 07, 2011 - 4:02 pm UTC
It is a matter of separation - not the ability to add/remove resources to a given luns.
If you have one big one - you cannot mirror with ASM, you cannot separate redo if you wanted to, and so on - there is no separation.
That is what you are choosing between. You have flexibility as to your configuration with many separate things - if you just have one thing, you just have one thing. There could be something to be said about having the ability to segregate things for performance reasons - it depends.
Both.
Arunima, July 03, 2011 - 6:14 pm UTC
Hi Tom,
Earlier in the thread you said "Your real questions lies in the redundancy -- should you use ASM's mirror and stripe everything approach or external hardware redundancy."
Will it make sense if I use 1+0 on my SAN disks and then also use ASM redundancy with one failgroup?? Will this be overkill? Sounds really safe way to preserve data...don't know about performance impact though..
Regards,
Aru.
July 05, 2011 - 7:54 am UTC
you can do that, at the expense of more disk. If you are careful how you set it up, you'll be more "available" as well. We will always stripe - no getting around that. Mirroring should not add much if anything to performance overhead however since it is done in parallel anyway.
Striping in ASM and SAN
dkalaluhi, July 22, 2011 - 1:12 pm UTC
Ok - so we can use HW RAID w/ ASM, but what about the shared pool size? So if we are using High Redundency on the ASM side (every 33G needs 1+6MB shared pool) and External (every 100G needs 1+2MB shared pool) will we need to set the shared pool to every 21G needs 1+8MB shared pool? Or is the 33G 1+6MB rule still apply.
Need to find some docs about ASM and SAN technology...
Thanks
July 22, 2011 - 2:41 pm UTC
what is 1+6mb? 1+2mb? 1+8mb?
reference the documentation you are reading so I can see it in context please.
Why do you need docs about ASM on SAN - it is the same as ASM on X, Y or Z - there is nothing "special" about SAN?
pathes asm 11gr2
Gustavo, July 23, 2011 - 11:59 am UTC
how do I will pathes my ASM. I intalled Grid infractructure 11gr2, but I was pathes, but I have error at lot.
D:\app\oracle\product\11.2.0\grid\Bundle\Patch13>asmcmd
ASMCMD> cp c:\hola.txt +Data
ASMCMD-08012: can not determine file type for file->'c:\hola.txt'
ORA-15056: additional error message
ORA-17503: ksfdopn:DGGetFileAttr15 Failed to open file C:\HOLA.TXT
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch
O/S-Error: (OS 3) The system cannot find the path specified.
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 304
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
ASMCMD>
July 27, 2011 - 7:53 pm UTC
pathes?
what are you trying to do here?
storage
A reader, July 23, 2011 - 12:51 pm UTC
A reader, November 15, 2011 - 2:25 am UTC
Hi
If I want to create a general purpose ASM cluster file system(ACFS to be automatically mounted by Oracle Clusterware),
do I manually need to add an entry to /etc/fstab defining the volume,mount point and mount points on each node in cluster?
ASM file extents and striping types
A reader, January 08, 2012 - 8:16 am UTC
Hi Tom,
I learned about variable file extents. It says that in a disk for file extent# 0-19999 the extent size=AU_SIZE. and for extent# 20000-39999 it is = 4*AU_SIZE and so on. But I could not understand how the larger files can be kept in larger file system (extent# 20000+) in a disk. I suppose there is no striping. Thus there should be only single disk. Then how can be the file content distribution?
Now for a Fine grained striping what I understood is, if AU_SIZE=1MB, then the extent is divided into 128k of small chunks for extent# 0-19999. That single extent is distributed into 8 disks (8*128=1024KB). But how the file contents are distributed when there are only 3 disks in a disk group?
For a Coarse-granied striping what I understood is, the extent is only contained inside a single disk and not distributed into multiple disks. If yes then how file contents are distributed in this case?
recommended RAID for RAC
Sidi, February 18, 2012 - 12:16 pm UTC
Hi Tom,
Which RAID levels are recommended to store OCR/Voting disks , Redo logs, Control files, datafiles in an 11gR2 RAC using ASM?
- OCR/Voting disks with NORNAL redundancy ASM level.
- Redo logs, Control files, datafiles EXTERNAL redundancy ASM level.
(we will use Redhat Enterprise Linux 5.5)
Thank you
storage
A reader, June 18, 2012 - 11:43 am UTC
excellent.