zfs discuss - Apr 2007 - How to bind the oracle 9i data file to zfs volumes

Experts,

I''m installing Oracle 9i on Solaris 10 11/06(update 3),I created some
zfs volumes which will be used by oracle data file,as:

# zfs create -V 200m ora_pool/controlfile01_200m
# zfs create -V 800m ora_pool/system_800m
...

# ls -l /dev/zvol/rdsk/ora_pool
lrwxrwxrwx   1 root     root          39 Apr 11 12:23
controlfile01_200m -> ../../../../devices/pseudo/zfs at 0:1c,raw
lrwxrwxrwx   1 root     root          39 Apr 11 13:34 system_800m ->
../../../../devices/pseudo/zfs at 0:7c,raw

(Please note above,it''s just a link file!)

then bind the data file to these volumes when create database as:
oracle control data file (size:200m)-->
/dev/zvol/rdsk/ora_pool/controlfile01_200m
oracle system data file(size:800m) --> /dev/zvol/rdsk/ora_pool/system_800m
...

it fails with the error:the space of "/devices" is insufficient.I
think this is because above character device files under
"/dev/zvol/rdsk/ora_pool" are link file to point to
"/device" node,not
the particular device file.

Compared with the raw volumes which created by vxvm:
# ls -l /dev/vx/rdsk/oradg/controlfile01_200m
crw-------   1 root     root     283,99000 Apr  7 22:51 controlfile01_200m

It''s a individual character device file,not the link file,this is the
difference between zfs volume and vxvm volume.

Please help to provide solution,any helps are much appreciated !

Best Regards,
Simon

Experts,

I''m installing Oracle 9i on Solaris 10 11/06(update 3),I created some
zfs volumes which will be used by oracle data file,as:

# zfs create -V 200m ora_pool/controlfile01_200m
# zfs create -V 800m ora_pool/system_800m
...

# ls -l /dev/zvol/rdsk/ora_pool
lrwxrwxrwx   1 root     root          39 Apr 11 12:23
controlfile01_200m -> ../../../../devices/pseudo/zfs at 0:1c,raw
lrwxrwxrwx   1 root     root          39 Apr 11 13:34 system_800m ->
../../../../devices/pseudo/zfs at 0:7c,raw

(Please note above,it''s just a link file!)

then bind the data file to these volumes when create database as:
oracle control data file (size:200m)-->
/dev/zvol/rdsk/ora_pool/controlfile01_200m
oracle system data file(size:800m) --> /dev/zvol/rdsk/ora_pool/system_800m
...

it fails with the error:the space of "/devices" is insufficient.I
think this is because above character device files under
"/dev/zvol/rdsk/ora_pool" are link file to point to
"/device" node,not
the particular device file.

Compared with the raw volumes which created by vxvm:
# ls -l /dev/vx/rdsk/oradg/controlfile01_200m
crw-------   1 root     root     283,99000 Apr  7 22:51 controlfile01_200m

It''s a individual character device file,not the link file,this is the
difference between zfs volume and vxvm volume.

Please help to provide solution,any helps are much appreciated !

Best Regards,
Simon

Experts,

I''m installing Oracle 9i on Solaris 10 11/06(update 3),I created some
zfs volumes which will be used by oracle data file,as:

# zfs create -V 200m ora_pool/controlfile01_200m
# zfs create -V 800m ora_pool/system_800m
...

# ls -l /dev/zvol/rdsk/ora_pool
lrwxrwxrwx   1 root     root          39 Apr 11 12:23
controlfile01_200m -> ../../../../devices/pseudo/zfs at 0:1c,raw
lrwxrwxrwx   1 root     root          39 Apr 11 13:34 system_800m ->
../../../../devices/pseudo/zfs at 0:7c,raw

(Please note above,it''s just a link file!)

then bind the data file to these volumes when create database as:
oracle control data file (size:200m)-->
/dev/zvol/rdsk/ora_pool/controlfile01_200m
oracle system data file(size:800m) --> /dev/zvol/rdsk/ora_pool/system_800m
...

it fails with the error:the space of "/devices" is insufficient.I
think this is because above character device files under
"/dev/zvol/rdsk/ora_pool" are link file to point to
"/device" node,not
the particular device file.

Compared with the raw volumes which created by vxvm:
# ls -l /dev/vx/rdsk/oradg/controlfile01_200m
crw-------   1 root     root     283,99000 Apr  7 22:51 controlfile01_200m

It''s a individual character device file,not the link file,this is the
difference between zfs volume and vxvm volume.

Please help to provide solution,any helps are much appreciated !

Best Regards,
Simon

On Thu, 12 Apr 2007, Simon wrote:
> I''m installing Oracle 9i on Solaris 10 11/06(update 3),I created
some
> zfs volumes which will be used by oracle data file,as:
Have you tried using SVM volumes?  I ask, because SVM does the same thing:
soft-link to /devices

If it works for SVM and not for ZFS, then I think that narrows it down to
a ZFS bug.  If it fails for both SVM and ZFS, then that probably indicates
an Oracle bug.

Could you share the relevant bits of a truss output so we can see
what''s
going on under the covers?


Regards,
markm

Mark,

Thanks for your reply.

I tried using SVM volumes to hold the oracle data file,but got the same errors:
# ls -l /dev/md/rdsk
total 4
lrwxrwxrwx   1 root     root          36 Apr 13 12:28 d0 ->
../../../devices/pseudo/md at 0:0,0,raw
lrwxrwxrwx   1 root     root          36 Apr 13 13:10 d1 ->
../../../devices/pseudo/md at 0:0,1,raw

oracle control data file --> /dev/md/rdsk/d0 (200M)
oracle system data file --> /dev/md/rdsk/d1 (800M)

When created database,got errors:
"You don''t have enough free disk space to create the database,You
need
at leat 409600KB on /devices,You have only 0KB available."

The errors are pretty same with before.

So,does mean this is oracle bug ? Or it''s impossible(or inappropriate)
to use ZFS/SVM volumes to create oracle data file,instead,should use
zfs or ufs filesystem to do this.

Thanks.

Regards,
Simon

On 4/12/07, Mark J Musante <mmusante at east.sun.com>
wrote:
> On Thu, 12 Apr 2007, Simon wrote:
>
> > I''m installing Oracle 9i on Solaris 10 11/06(update 3),I
created some
> > zfs volumes which will be used by oracle data file,as:
>
> Have you tried using SVM volumes?  I ask, because SVM does the same thing:
> soft-link to /devices
>
> If it works for SVM and not for ZFS, then I think that narrows it down to
> a ZFS bug.  If it fails for both SVM and ZFS, then that probably indicates
> an Oracle bug.
>
> Could you share the relevant bits of a truss output so we can see
what''s
> going on under the covers?
>
>
> Regards,
> markm
> _______________________________________________
> zfs-discuss mailing list
> zfs-discuss at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
>

Since the entries under /dev/rdsk (for raw disk partitions) are also  
symlinks to /devices/../.. and have been for a very, very long time,  
it seems to me that the inability to support them is either a bug or  
the result of a misuse of the product.

On 13/04/2007, at 3:30 PM, Simon wrote:
> Mark,
>
> Thanks for your reply.
>
> I tried using SVM volumes to hold the oracle data file,but got the  
> same errors:
> # ls -l /dev/md/rdsk
> total 4
> lrwxrwxrwx   1 root     root          36 Apr 13 12:28 d0 ->
> ../../../devices/pseudo/md at 0:0,0,raw
> lrwxrwxrwx   1 root     root          36 Apr 13 13:10 d1 ->
> ../../../devices/pseudo/md at 0:0,1,raw
>
> oracle control data file --> /dev/md/rdsk/d0 (200M)
> oracle system data file --> /dev/md/rdsk/d1 (800M)
>
> When created database,got errors:
> "You don''t have enough free disk space to create the
database,You need
> at leat 409600KB on /devices,You have only 0KB available."
>
> The errors are pretty same with before.
>
> So,does mean this is oracle bug ? Or it''s impossible(or
inappropriate)
> to use ZFS/SVM volumes to create oracle data file,instead,should use
> zfs or ufs filesystem to do this.
>
> Thanks.
>
> Regards,
> Simon
>
> On 4/12/07, Mark J Musante <mmusante at east.sun.com> wrote:
>> On Thu, 12 Apr 2007, Simon wrote:
>>
>> > I''m installing Oracle 9i on Solaris 10 11/06(update 3),I
created
>> some
>> > zfs volumes which will be used by oracle data file,as:
>>
>> Have you tried using SVM volumes?  I ask, because SVM does the  
>> same thing:
>> soft-link to /devices
>>
>> If it works for SVM and not for ZFS, then I think that narrows it  
>> down to
>> a ZFS bug.  If it fails for both SVM and ZFS, then that probably  
>> indicates
>> an Oracle bug.
>>
>> Could you share the relevant bits of a truss output so we can see  
>> what''s
>> going on under the covers?
>>
>>
>> Regards,
>> markm
>> _______________________________________________
>> zfs-discuss mailing list
>> zfs-discuss at opensolaris.org
>> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
>>
> _______________________________________________
> zfs-discuss mailing list
> zfs-discuss at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
>

Peter,

I try to change these devices permission to oracle:dba,but the errors
are persistent.I think in ZFS,there is no concept of volumes, at least
in the traditional sense of volume managers,So if  trying to use the
RAW device that the zpool command created, maybe it is not what ZFS is
intended for, and will not work.

Right ?

Thanks.

Regards,
Simon

On 4/13/07, Peter Ondruska <peter.ondruska at gmail.com>
wrote:
> Well, it could be that Oracle cannot write to those devices. What
> permissions do you have on devices/pseudo...?
>
> On 13.4.2007, at 7:30, Simon wrote:
>
> > Mark,
> >
> > Thanks for your reply.
> >
> > I tried using SVM volumes to hold the oracle data file,but got the
> > same errors:
> > # ls -l /dev/md/rdsk
> > total 4
> > lrwxrwxrwx   1 root     root          36 Apr 13 12:28 d0 ->
> > ../../../devices/pseudo/md at 0:0,0,raw
> > lrwxrwxrwx   1 root     root          36 Apr 13 13:10 d1 ->
> > ../../../devices/pseudo/md at 0:0,1,raw
> >
> > oracle control data file --> /dev/md/rdsk/d0 (200M)
> > oracle system data file --> /dev/md/rdsk/d1 (800M)
> >
> > When created database,got errors:
> > "You don''t have enough free disk space to create the
database,You need
> > at leat 409600KB on /devices,You have only 0KB available."
> >
> > The errors are pretty same with before.
> >
> > So,does mean this is oracle bug ? Or it''s impossible(or
inappropriate)
> > to use ZFS/SVM volumes to create oracle data file,instead,should use
> > zfs or ufs filesystem to do this.
> >
> > Thanks.
> >
> > Regards,
> > Simon
> >
> > On 4/12/07, Mark J Musante <mmusante at east.sun.com> wrote:
> >> On Thu, 12 Apr 2007, Simon wrote:
> >>
> >> > I''m installing Oracle 9i on Solaris 10 11/06(update
3),I created
> >> some
> >> > zfs volumes which will be used by oracle data file,as:
> >>
> >> Have you tried using SVM volumes?  I ask, because SVM does the
> >> same thing:
> >> soft-link to /devices
> >>
> >> If it works for SVM and not for ZFS, then I think that narrows it
> >> down to
> >> a ZFS bug.  If it fails for both SVM and ZFS, then that probably
> >> indicates
> >> an Oracle bug.
> >>
> >> Could you share the relevant bits of a truss output so we can see
> >> what''s
> >> going on under the covers?
> >>
> >>
> >> Regards,
> >> markm
> >> _______________________________________________
> >> zfs-discuss mailing list
> >> zfs-discuss at opensolaris.org
> >> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
> >>
> > _______________________________________________
> > zfs-discuss mailing list
> > zfs-discuss at opensolaris.org
> > http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
>
>

Simon wrote:
> So,does mean this is oracle bug ? Or it''s impossible(or
inappropriate)
> to use ZFS/SVM volumes to create oracle data file,instead,should use
> zfs or ufs filesystem to do this.
Oracle can use SVM volumes to hold its data. Unless I am mistaken, it 
should be able to use zvols as well.

However, googling for ''zvol + Oracle'' did not get me anything
useful.
Perhaps it is not a configuration that is very popular. ;)

My $ 0.02.

-Manoj

Manoj Joseph wrote:
> Simon wrote:
>> So,does mean this is oracle bug ? Or it''s impossible(or
inappropriate)
>> to use ZFS/SVM volumes to create oracle data file,instead,should use
>> zfs or ufs filesystem to do this.
> 
> Oracle can use SVM volumes to hold its data. Unless I am mistaken, it 
> should be able to use zvols as well.
Yes.  Though I believe most people will prefer regular file systems or
ASM.  We discuss performance implications on the ZFS wiki at
http://solarisinternals.com
> However, googling for ''zvol + Oracle'' did not get me
anything useful.
> Perhaps it is not a configuration that is very popular. ;)
Sounds like an opportunity... please share your experiences.
  -- richard

Dear All,

I made another testing(the last time:-)) but successful,so happy ?!
and belows are the detail.

*) Requirements:
Create oracle 9i data file over Solaris 10 zfs volume

*) Failure History:
With "dbca" GUI,db creation always failure due to
''/device'' space not
enough,it complains:
You don''t have enough free disk space to create the database,You need
at leat 409600KB on /devices,You have only 0KB available."

First I suspected the oracle couldn''t recognize the zfs device file as
it''s link file,not a particular character deivce file,as:
# zfs create -V 210m ora_pool/controlfile01_200m
# ls -l /dev/zvol/rdsk/ora_pool/controlfile01_200m
lrwxrwxrwx   1 root     root          39 Apr 17 14:58
/dev/zvol/rdsk/ora_pool/controlfile01_200m ->
../../../../devices/pseudo/zfs at 0:1c,raw (a link file pointing to
/device)

And I made many attempts but got same errors when dbca launched,then I
try to use "script" to create db,successful.

*) Procedure of succeeding in creating oracle data file over zfs volumes:

- Create zfs volumes
  # zfs create -V 210m   ora_pool/controlfile01_200m
  # zfs create -V 210m   ora_pool/controlfile02_200m
  # zfs create -V 210m   ora_pool/controlfile03_200m
  ...

  # zfs create ora_pool/oracle
  # zfs set mountpoint=/opt/oracle ora_pool/oracle
  # groupadd dba
  # useradd -d /opt/oracle -g dba -s /bin/csh -m oracle
  # chown oracle:dba /dev/zvol/rdsk/ora_pool/*
  # chmod 600 /dev/zvol/rdsk/ora_pool/*

  # projadd -U oracle -p 1233 -c "oracle parameters setting"
user.oracle
  # projmod -a -K "project.max-shm-memory=(priv,17179869184,deny)"
user.oracle
  # projmod -a -K "project.max-sem-ids=(priv,2000,deny)" user.oracle
  # projmod -a -K "process.max-sem-nsems=(priv,2048,deny)" user.oracle
  # projmod -a -K "project.max-shm-ids=(priv,256,deny)" user.oracle

- After oralce software installation,create database by *scripts*,not
by GUI tool "dbca":

   # cat CreateDBFiles
connect SYS/change_on_install as SYSDBA
set echo on
spool /opt/oracle/product/9.2/assistants/dbca/logs/CreateDBFiles.log
CREATE TABLESPACE "INDX" LOGGING DATAFILE
''/dev/zvol/rdsk/ora_pool/index01_4000m'' SIZE 4000M REUSE
EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
CREATE TABLESPACE "TOOLS" LOGGING DATAFILE
''/dev/zvol/rdsk/ora_pool/tools_1000m'' SIZE 1000M REUSE EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
CREATE TABLESPACE "USERS" LOGGING DATAFILE
''/dev/zvol/rdsk/ora_pool/users01_4000m'' SIZE 4000M REUSE
EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
spool off
exit;

  # cat CeateDB
connect SYS/change_on_install as SYSDBA
set echo on
spool /opt/oracle/product/9.2/assistants/dbca/logs/CreateDB.log
startup nomount pfile="/opt/oracle/admin/ora9/scripts/init.ora";
CREATE DATABASE ora9
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE ''/dev/zvol/rdsk/ora_pool/system_800m'' SIZE 800M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
''/dev/zvol/rdsk/ora_pool/temp01_4000m'' SIZE 4000M REUSE
UNDO TABLESPACE "UNDOTBS1" DATAFILE
''/dev/zvol/rdsk/ora_pool/undotbs1_01_4000m'' SIZE 4000M REUSE
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 (''/dev/zvol/rdsk/ora_pool/redo1_01_400m'') SIZE
400M,
GROUP 2 (''/dev/zvol/rdsk/ora_pool/redo2_01_400m'') SIZE 400M,
GROUP 3 (''/dev/zvol/rdsk/ora_pool/redo3_01_400m'') SIZE 400M;
spool off
exit;

It''s OK,no errors reported during datafile creation,validate it:

SQL> startup
pfile=''/opt/oracle/admin/ora9/scripts/init.ora'';
ORACLE instance started.

Total System Global Area 2402258952 bytes
Fixed Size                   733192 bytes
Variable Size             251658240 bytes
Database Buffers         2147483648 bytes
Redo Buffers                2383872 bytes
Database mounted.
Database opened.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/dev/zvol/rdsk/ora_pool/system_800m
/dev/zvol/rdsk/ora_pool/undotbs1_01_4000m
/dev/zvol/rdsk/ora_pool/index01_4000m
/dev/zvol/rdsk/ora_pool/tools_1000m
/dev/zvol/rdsk/ora_pool/users01_4000m

SQL> create table aa ( a1 varchar(10));

Table created.

SQL> insert into aa values (''aa'');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from aa;

A1
--------------------
aa



It seems that only with the "script",no "dbca" GUI tool,the
oracle
data file can be kept on the zfs volumes.

Any comments on this.

Thanks

Regards,
Simon


On 4/17/07, Richard Elling <Richard.Elling at sun.com>
wrote:
> Simon,
> what error messages did you get?
>   -- richard
>
> Simon wrote:
> > Brothers,
> >
> > I made many times attempts to deploy the oracle data file on zfs
> > volumes,but not successful at all,more and more failure assured me
> > it''s impossible to use zfs volume to hold the oralce data
file,So I
> > gave up.nervertheless I still hope you can share the story when
> > succeed in this.
> >
> > Thanks the repliers.
> >
> > Best Regards,
> > Simon
> >
> > On 4/17/07, Richard Elling <Richard.Elling at sun.com> wrote:
> >> Manoj Joseph wrote:
> >> > Simon wrote:
> >> >> So,does mean this is oracle bug ? Or it''s
impossible(or inappropriate)
> >> >> to use ZFS/SVM volumes to create oracle data
file,instead,should use
> >> >> zfs or ufs filesystem to do this.
> >> >
> >> > Oracle can use SVM volumes to hold its data. Unless I am
mistaken, it
> >> > should be able to use zvols as well.
> >>
> >> Yes.  Though I believe most people will prefer regular file
systems or
> >> ASM.  We discuss performance implications on the ZFS wiki at
> >> http://solarisinternals.com
> >>
> >> > However, googling for ''zvol + Oracle'' did
not get me anything useful.
> >> > Perhaps it is not a configuration that is very popular. ;)
> >>
> >> Sounds like an opportunity... please share your experiences.
> >>   -- richard
> >>
>

Hello Simon,

Wednesday, April 18, 2007, 11:26:39 AM, you wrote:

S> It seems that only with the "script",no "dbca" GUI
tool,the oracle
S> data file can be kept on the zfs volumes.

S> Any comments on this.

You had the same problems without using zfs so I assume it''s not zfs
specific.

-- 
Best regards,
 Robert                            mailto:rmilkowski at task.gda.pl
                                       http://milek.blogspot.com