Raw Device Syntax

[Ketan_6]

Hi!,

We have just started installing oracle Rac on XP512 - 1 thing which I am concerned with is how do I check if the raw partition is filled or empty. If its a small database - Yes I could check the Raw paritions under /dev/RAC and than make sure which are used under v$datafile - but if you get a Page at 2 am and you have a couple of 100 datafiles - its a tough one. Can some one please let me know some commands to know the size of the raw partition and how do I know its empty of not before I add a datafile ?

Thanks
Ketan

[Steven E. Protter]

I run a different database on raw storage. The only way we have to check usage is with commands provided with the database software.

In oracle land, its sqlplus commands. That doesn't mean there is no way to do it with the OS, but every command I've seen just shows the size of the raw disk area.

SEP

[Sanjay_6]

Hi Ketan,

That is why it is called Raw. Because you cannot find out from unix whether you are using the complete space or not. You can check that from oracle only.

Hope this helps.

Regds

[Hein van den Heuvel]

Script it!

Ask oracle to tell you which 'files' is knows about, and compare/diff with the raw devices. All include a little show_file_sizes.sql script to get the oracle data. SHoudl be easy enough to adapt to your needs.
Add a little perl and/or shell script and let the system do the work.

I try to avoid this situation by very clear LVM naming conventions.

If I do use raw devices directly, then I do not tell oracle abut them but go through a softlink in a directotry created just for the db instances. Such softlink list would be any easy driver table to see what is in use.
[Other forum members do not care much for the intermediate softlink directory, but it has severd me well. It allows me to flip from production to test and back with a single directory rename!]

fwiw,
Hein.

[Hein van den Heuvel]

[not having a good day doing attachment/includes]

column status format a8
column member format a55
column file_name format a45
column Tablespace format a15
column segment_name format a15
column extent_name format a15
column owner format a10
column file format a45
column id format 99

set pages 9999
set heading off
set FEEDBACK off
select 'redo_'||l.group# "Tablespace", l.group# "Id", l.bytes/(1024*1024) "MB",
MEMBER "File" from v$logfile f, v$log l where l.group# = f.group#
union
select tablespace_name "Tablespace", FILE_ID "Id", bytes/(1024*1024) "MB",
file_name "File" from dba_data_files
union
select tablespace_name "Tablespace", FILE_ID "Id", bytes/(1024*1024) "MB",
file_name "File" from dba_temp_files
union
select 'Control_file' "Tablespace", rownum "Id", 0 "MB",
name "File" from v$controlfile
order by 1
/

[Sanjiv Sharma_1]

Hi Ketan,

Please check this link:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=56780

hth.

[Kiyoshi Miyake]

Hi.

Probably, there is no way for check %used of raw device by OS command.
OS does not know how oracle is using raw device.

You should write SQL script.
I don't know SQL well.
e.g.)
select sum(...),,,
from v$data, sys.dba_data_files, ...
group by tablespace_name

Thanks.

[Ketan_6]

Hi!,

AS Heuvel - suggested - I do have a script which will give me the list of all my datafiles. But here is the problem I face

OS has /dev/RAC/RAW Partitions..
The PArtitions range from 2 GB to 8 GB.
Total Partitions 320

From an oracle script I would surely know which partitions are used and than add datafiles accordingly. But it would be very error prone and cumbersome once you have to scan through 150 datafiles name and compare with OS partitions to check which are free.

I guess from all the replies I hvae received so far - creating an TXT doc on the host would be helpfull and update the sheet when one adds a datafile. Or to have a small table with this information and keep updating the table once to add a datafile.

Thanks
Ketan

[Hein van den Heuvel]

But why not write a scripts to do the comparing and/or adding.
About that adding... what is the function of that. The datafiles remain independend even though though logically add up to a tablespace.

Why not send us a sample (attach to next reply) say 5 or 10 line output from the SQL file name/size report, AS WELL AS the a 5 - 10 line sample of the ls/vgdisplay output you use to list the raw devices. (and the command used to get that data). I'm sure someone here can whip up a perl/shell or awk script to compare them, calling out missing items.

If a raw device is too big (oracle uses less than all of the device give or take one mb) then you conceivably can 'dd count=oracle-size-in-mb if=old-raw bs=1024k of=temp-file;
recreate raw smaller; dd ... if=temp of=new-raw; rename file in Oracle if need be (startup nomount, alter...., mount...)

Hein.

[Ketan_6]

from oracle side - I run
select name from v4datafile <- this gives me the list of raw partitions already used :
Eg of the output is

/dev/RAC/RACsystem01
/dev/RAC/RAC1undo
/dev/RAC/RAC2undo
/dev/RAC/RAC3undo
/dev/RAC/RAC4undo
/dev/RAC/RACdata01
/dev/RAC/RACdata02
/dev/RAC/RACdata03
/dev/RAC/RACdata04
/dev/RAC/RACdata05

Now from the OS here is the output

oracle@tsonode1[eraclte1]$ ls -l /dev/RAC/
total 0

crw-r--r-- Oct 4 2002 RACdata01
crw-r--r-- Oct 4 2002 RACdata02
crw-r--r-- Oct 4 2002 RACdata03
crw-r--r-- Oct 4 2002 RACdata04
crw-r--r-- Oct 4 2002 RACdata05
crw-r--r-- Oct 4 2002 RACdata06
crw-r--r-- Oct 4 2002 RACdata07
crw-r--r-- Oct 4 2002 RACdata08
crw-r--r-- Oct 4 2002 RACdata09
crw-r--r-- Oct 4 2002 RACdata10
crw-r--r-- Oct 4 2002 RACdata11
crw-r--r-- Oct 4 2002 RACdata12
crw-r--r-- Oct 4 2002 RACdata13
crw-r--r-- Oct 4 2002 RACdata14
crw-r--r-- Oct 4 2002 RACdata15
crw-r--r-- Oct 4 2002 RACdata16

The list goes on till RACdata340.

[Hein van den Heuvel]

If those are the files you have to work with then for example do:

> awk -F/ '{print $NF}' < oracle.dat | sort > oracle.sorted
> awk '{print $NF}' < device.dat | sort > device.sorted
> diff oracle.sorted device.sorted

(the -F tells awk to use "/" as field seperator instead of the default 'space'. NF is 'Number of Fields' (here 3), thus $NF is $3 is the last field.)

You can do is unsorted in one go with perl:

perl -e 'open (O,"){(/(\w+)$/);$o{$1}=1}
open (D,"){(/(\w+)$/);
print "$1 not used\n" unless($o{$1})}'

read the oracle file, using the last word on the line to create a hash array element.
read the device file, print 'not in use' unless the last word has an array element.

You can make your life easier by making the lists closer as you make them. For example for oracle use:
.. select name from v$datafile order by name;
for the device files use:
find /dev/RAC/* | sort > device.dat
Now diff.

Enjoy!
Hein.