linux

fritshoogland's picture

Introduction to the Oracle database machine

For those of you who haven’t followed all the Oracle Exadata and database machine information, and want a brief introduction to the database machine: here it is!

The confusion

There is some confusion about ‘exadata’ and ‘the database machine’. If we look at the official product names, ‘Exadata’ is the storage server, and the ‘Database machine’ is the complete boxed machine, including exadata for storage. But…in the real world, in different kinds of papers (technical, advertising) exadata sometimes is used as an alias for the database machine.

martin.bach's picture

The renamdg command revisited-ASMLib

I have already written about the renamedg command, but since then fell in love with ASMLib. The use of ASMLib introduces a few caveats you should be aware of.

USAGE NOTES

This document presents research I performed with ASM on a lab environment. It should be applicable to any environment, but you should NOT use this for production-the renamedg command still is buggy, and you should not mess with ASM disk headers in an important system such as production or staging/UAT. You set the importance here!  The recommended setup for cloning disk groups is to use a data guard physical standby database on a different storage array to create a real time copy of your production database on that array. Again, do not use you production array for this!

Walking through a renamdg session

Oracle ASMLib introduces a new value to the ASM header, called the provider string as the following example shows:

[root@asmtest ~]# kfed read /dev/oracleasm/disks/VOL1 | grep prov
kfdhdb.driver.provstr:     ORCLDISKVOL1 ; 0x000: length=12

This can be verified with ASMLib:

[root@asmtest ~]# /etc/init.d/oracleasm querydisk /dev/xvdc1
Device "/dev/xvdc1" is marked an ASM disk with the label "VOL1"

The prefix “ORCLDISK” is automatically added by ASMLib and cannot easily be changed.

The problem with ASMLib is that the renamedg command does NOT update the provider string, which I’ll illustrate by walking through an example session. Disk group “DATA”, setup with external redundancy and two disks, DATA1 and DATA2, is to be cloned to “DATACLONE”.

The renamedg command requires the disk group to be cloned to be stopped. To prevent nasty surprises, you should stop the databases using that diskgroup manually.

[grid@rac11gr2drnode1 ~]$ srvctl stop database -d dev
[grid@rac11gr2drnode1 ~]$ ps -ef | grep smon
grid      3424     1  0 Aug07 ?        00:00:00 asm_smon_+ASM1
grid     17909 17619  0 15:13 pts/0    00:00:00 grep smon
[grid@rac11gr2drnode1 ~]$ srvctl stop diskgroup -g data
[grid@rac11gr2drnode1 ~]$

You can use the new “lsof” command of asmcmd to check for open files:

ASMCMD> lsof
DB_Name  Instance_Name  Path
+ASM     +ASM1          +ocrvote.255.4294967295
asmvol   +ASM1          +acfsdg/APACHEVOL.256.724157197
asmvol   +ASM1          +acfsdg/DRL.257.724157197
ASMCMD>

So apart from files from other disk groups no files are open, especially not referring to disk group DATA.

Now comes the part where you copy the LUNs, and this entirely depends on your system. The EVA series of storage arrays I worked with in this particular project offered a “snapclone” function, which used COW to create an identical copy of the source LUN, with a new WWID (which can be an input parameter to the snapclone call). When you are using device-mapper-multipath then ensure that your sys admins add the newly created LUNs to the /etc/multipath.conf file on all cluster nodes!

I am using Xen in my lab, which makes it simpler-all I need to do is to copy the disk containers on the domO and then add the new block devices to the running domU (“virtual machine” in Xen language). This can be done easily as the following example shows:

Usage: xm block-attach     

xm block-attach rac11gr2drnode1 file:/var/lib/xen/images/rac11gr2drShared/oradata1.clone xvdg w!
xm block-attach rac11gr2drnode2 file:/var/lib/xen/images/rac11gr2drShared/oradata1.clone xvdg w!

xm block-attach rac11gr2drnode1 file:/var/lib/xen/images/rac11gr2drShared/oradata2.clone xvdh w!
xm block-attach rac11gr2drnode2 file:/var/lib/xen/images/rac11gr2drShared/oradata2.clone xvdh w!

In the example, rac11gr2drnode{1,2} are the domU, the backend device is the copied file on the file system, the front end device in the domU is xvd{g,h}, and the mode is read/write, shareable. The exclamation mark here is crucial or else the second domU can’t mount the new block device-it is already exclusively mounted to another domU.

The fdisk command in my example immediately “sees” the new LUNs, with device mapper multipathing you might have to go through iterations of restarting multipathd and discovering partitions using kpartx. It is again very important to have all disks presented to all cluster nodes!

Here’s the sample output from my system:

[root@rac11gr2drnode1 ~]# fdisk -l | grep Disk | sort
Disk /dev/xvda: 4294 MB, 4294967296 bytes
Disk /dev/xvdb: 16.1 GB, 16106127360 bytes
Disk /dev/xvdc: 5368 MB, 5368709120 bytes
Disk /dev/xvdd: 16.1 GB, 16106127360 bytes
Disk /dev/xvde: 16.1 GB, 16106127360 bytes
Disk /dev/xvdf: 10.7 GB, 10737418240 bytes
Disk /dev/xvdg: 16.1 GB, 16106127360 bytes
Disk /dev/xvdh: 16.1 GB, 16106127360 bytes

I cloned /dev/xvdd and /dev/xvde to /dev/xvdg and /dev/xvdh.

Do NOT run /etc/init.d/oracleasm scandisks yet! Otherwise the renamedg command will complain about duplicate disk names, which is entirely reasonable.

I dumped all headers for disks /dev/xvd{d,e,g,h}1 to /tmp to be able to compare.

[root@rac11gr2drnode1 ~]# kfed read /dev/xvdd1 > /tmp/xvdd1.header
# repeat with the other disks

Start with phase one of the renamedg command:

[root@rac11gr2drnode1 ~]# renamedg phase=one dgname=DATA newdgname=DATACLONE \
> confirm=true verbose=true config=/tmp/cfg

Parsing parameters..

Parameters in effect:

 Old DG name       : DATA
 New DG name          : DATACLONE
 Phases               :
 Phase 1
 Discovery str        : (null)
 Confirm            : TRUE
 Clean              : TRUE
 Raw only           : TRUE
renamedg operation: phase=one dgname=DATA newdgname=DATACLONE confirm=true
  verbose=true config=/tmp/cfg
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA1 with
  disk number:0 and timestamp (32940276 1937075200)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA2 with
  disk number:1 and timestamp (32940276 1937075200)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA1 with
  disk number:0 and timestamp (32940276 1937075200)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA2 with
  disk number:1 and timestamp (32940276 1937075200)
Checking if the diskgroup is mounted
Checking disk number:0
Checking disk number:1
Checking if diskgroup is used by CSS
Generating configuration file..
Completed phase 1
Terminating kgfd context 0x2b7a2fbac0a0
[root@rac11gr2drnode1 ~]#

You should always check “$?” for errors-the message “terminating kgfd context” sounds bad, but isn’t. At the end of stage 1, there is no change to the header. Only at phase two there is:

[root@rac11gr2drnode1 ~]# renamedg phase=two dgname=DATA newdgname=DATACLONE config=/tmp/cfg

Parsing parameters..
renamedg operation: phase=two dgname=DATA newdgname=DATACLONE config=/tmp/cfg
Executing phase 2
Completed phase 2

Now there are changes:

[root@rac11gr2drnode1 tmp]# grep DATA *header
xvdd1.header:kfdhdb.driver.provstr:    ORCLDISKDATA1 ; 0x000: length=13
xvdd1.header:kfdhdb.dskname:                   DATA1 ; 0x028: length=5
xvdd1.header:kfdhdb.grpname:               DATACLONE ; 0x048: length=9
xvdd1.header:kfdhdb.fgname:                    DATA1 ; 0x068: length=5
xvde1.header:kfdhdb.driver.provstr:    ORCLDISKDATA2 ; 0x000: length=13
xvde1.header:kfdhdb.dskname:                   DATA2 ; 0x028: length=5
xvde1.header:kfdhdb.grpname:               DATACLONE ; 0x048: length=9
xvde1.header:kfdhdb.fgname:                    DATA2 ; 0x068: length=5
xvdg1.header:kfdhdb.driver.provstr:    ORCLDISKDATA1 ; 0x000: length=13
xvdg1.header:kfdhdb.dskname:                   DATA1 ; 0x028: length=5
xvdg1.header:kfdhdb.grpname:                    DATA ; 0x048: length=4
xvdg1.header:kfdhdb.fgname:                    DATA1 ; 0x068: length=5
xvdh1.header:kfdhdb.driver.provstr:    ORCLDISKDATA2 ; 0x000: length=13
xvdh1.header:kfdhdb.dskname:                   DATA2 ; 0x028: length=5
xvdh1.header:kfdhdb.grpname:                    DATA ; 0x048: length=4
xvdh1.header:kfdhdb.fgname:                    DATA2 ; 0x068: length=5

Although the original disks (/dev/xvdd1 and /dev/xvde1) had their disk group name changed, the provider string remained untouched. So if we were to issue a scandisks command now through /etc/init.d/oracleasm, there’d still be duplicate disk names. This is a bug in my opinion, and a bad thing.

Renaming the disks is straight forward, the difficult bit is to find out which have to be renamed. Again, you can use kfed to figure that out. I knew the disks to be renamed were /dev/xvdd1 and /dev/xvde1 after consulting the header information.

[root@rac11gr2drnode1 tmp]# /etc/init.d/oracleasm force-renamedisk /dev/xvdd1 DATACLONE1
Renaming disk "/dev/xvdd1" to "DATACLONE1":                [  OK  ]
[root@rac11gr2drnode1 tmp]# /etc/init.d/oracleasm force-renamedisk /dev/xvde1 DATACLONE2
Renaming disk "/dev/xvde1" to "DATACLONE2":                [  OK  ]

I then performed a scandisks operation on all nodes just to be sure… I had corruption of the disk group before :)

[root@rac11gr2drnode1 tmp]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@rac11gr2drnode1 tmp]#

[root@rac11gr2drnode2 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@rac11gr2drnode2 ~]#

The output on all cluster nodes should be identical, on my system I found the following disks:

[root@rac11gr2drnode1 tmp]# /etc/init.d/oracleasm listdisks
ACFS1
ACFS2
ACFS3
ACFS4
DATA1
DATA2
DATACLONE1
DATACLONE2
VOL1
VOL2
VOL3
VOL4
VOL5

Sure enough, the cloned disks were present. Although everything seemed ok at this point, I could not start disk group DATA and had to reboot the cluster nodes to rectify that problem. Maybe there is some not so transient information stored somewhere about ASM disks. After the reboot, CRS started my database correctly, and with all dependent resources:

[oracle@rac11gr2drnode1 ~]$ srvctl status database -d dev
Instance dev1 is running on node rac11gr2drnode1
Instance dev2 is running on node rac11gr2drnode2

fritshoogland's picture

Exadata has arrived, part 2

The second installation step of the database machine aka Exadata by Oracle ACS (Advanced Customer Support) is configuring the database and storage (‘cell’) nodes/servers. The blades are delivered with default IP addresses, during this step they are configured to the IP addresses which fit in our environment. Also the cellservers are configured (‘LUN’s are carved’) to have storage for ASM.

The cellservers are configured with three diskgroups during a normal installation: DATA for data, RECO for the flash recovery area and a diskgroup for the clusterware (voting disks, cluster registry) called SYSTEMDG.

A RAC database is configured too. We have a half rack, which means 4 database nodes, so a 4 node RAC database is configured, called ‘dbm’. The database has no data in it, besides the data dictionary (obviously), and is using a ‘humble’ amount of memory (8GB on a 64GB machine).

Now it’s up to me to automate the creation (and deletion) RAC databases, adding and deleting instances of the RAC database, modifying the storage (to be able to test both half rack and quarter rack configurations) and also some optimising/configuration, like enabling hugepages, add rlwrap etc.

Busy, busy, busy :)

Tagged: oracle database machine exadata

oraclebase's picture

Oracle RAC on VirtualBox…

With the recent news that the latest version of VirtualBox now supports shared disks, I thought I better give it a go and see if I could do a RAC installation on it. The good news is it worked as expected. You can see a quick run through here:

This is pretty good news as that was the last feature that tied me to VMware Server. I’ve now moved pretty much everything I do at home on to VirtualBox and it’s working fine.

It’s worth taking a little time looking at the VBoxManage command line. Some of the operations, like creating the shared disks, have to be done from the command line at the moment. It’s also handy for running VMs in headless mode if you don’t want the GUI screen visible all the time.

Cheers

Tim…

chenshap's picture

Cloning Oracle Home from RAC to Stand-Alone

This post originally appeared over at Pythian. There are also some very smart comments over there that you shouldn’t miss, go take a look!

This should have been the easiest task on my todo list: Install Oracle 10.2.0.3 EE standalone on a new Linux RHEL 5 server, later to be used as a standby for a production RAC system. This means 2 lines of “runinstall -silent …”, less than 5 minutes of DBA work and maybe 20 minutes of waiting. I did not expect to spend over 5 hours doing this.

Problems started when I discovered that I don’t have the 10.2.0.3 patchset and another patch that exists on production and should be installed on the standby. I had to wait for my Metalink credentials to be approved for this customer CSI before I could download the patches for them.

“Why don’t you just clone the software from production?” asked a helpful colleague.

fritshoogland's picture

Doing synchronous IO on ASM

One of the advantages of ASM is doing non (operating system) buffered IO (also known as ‘DIO’ or Direct IO), and doing asynchronous IO (also known as ‘AIO’ or ‘asynch IO’).

This is an excerpt from “ASM 10gr2 Best Practices“:

The database file level access (read/write) of ASM files is similar to pre-10g, except that any database file name that begins with a “+”, will automatically be handled and managed using the ASM code path. However, with ASM files, the database file access inherently has the characteristics of raw devices; i.e., un- buffered (direct IO) with kernelized asynchronous IO (KAIO).

What does this quote say?
  • If a database file name begins with a “+”, it will be automatically be handled and managed using the ASM code path.

I think this is true.

  • With ASM files (I read that as ‘database files in ASM’) the database file access inherently has the characteristics of raw device; i.e., un-buffered (direct IO)

I am confident this is true too: there is no filesystem involved during the reading of ASM file, so it can’t be buffered in a filesystem cache.

  • with kernelized asynchronous IO (KAIO)

This is mostly true. If asynchronous IO is setup correctly, most IO’s are done asynchronous, but some are synchronous.

How do you measure if asynchronous IO is being used?

The note on MOS (My Oracle Support) on asynchronous IO (‘How To Check if Asynchronous I/O is Working On Linux, Doc ID 237299.1) has changed. It used to point to the linux slabinfo (visible in /proc/slabinfo; these are kernel “slabs”), to look for allocations of “kiocb” (kernel IO callback) and “kioctx” (kernel IO context) which would allow someone to see if *some* process has initiated the usage of these slabs, which indicates asynchronous IO is initialised. Mind “some process”: this doesn’t have to be the database. Not an extremely reliable way of telling asynchronous IO is used.

Now, the note also includes more accurate ways to detect the kind of IO’s done:
-An example of a ‘strace’ of a process (the databasewriter, dbwr) doing asynchronous IO, which is (easy) visible by the asynchronous IO system calls: io_submit(), which issues an IO request, and io_getevents(), which reads the completion queue to verify the status of submitted IO’s.
-An example of a ‘strace’ of the same process doing the same calls using synchronous IO, which also is easy visible by the synchronous write call: pwrite().

When ASMLib is used, it gets a little more difficult: processes use synchronous IO (read()) to issue calls to ASMLib meta-devices, which do asynchronous IO, depending on Oracle database settings, on the behalf of the calling process. Please mind I haven’t investigated this more in-depth, this is what the metalink document says!

So: the IO method is visible by tracing the systemcalls of a process:
io_submit, io_getevents = asynchronous IO
pread, pwrite = synchronous IO

disk_asynch_io

The usage of asynchronous IO depends on the database parameter ‘disk_asynch_io’. If set to ‘false’, the database uses synchronous IO, even when ASM is used. So the above mentioned note is NOT true. With ASM and ‘disk_asynch_io’ set to ‘false’, it means the IO calls will be unbuffered, but synchronous. I have no databases on raw devices, but I am confident it will behave the same.

But whilst disk_asynch_io=false always means the IO is synchronous, when set to ‘true’ it does not mean asynchronous IO is always used.

filesystemio_options

By default, the parameter ‘filesystemio_options’ is empty after a database is created. On linux (2.6 kernel) both 32 and 64 bit, a database (I’ve tested 11.2.0.1, but assume it’s the same for lower versions) is doing synchronous IO if it’s using the ‘ext3′ filesystem. In order to activate unbuffered (direct) IO, the parameter filesystemio_options needs to be set to ‘directio’, in order to use asynchronous IO it needs to be set to ‘asynch’, in order to use both use ‘setall’. To list all options: set it to ‘none’ to disable both DIO and AIO.

If a database is created which uses ASM for storage, asynchronous IO is done (and direct IO, by design) whilst the parameter ‘filesystemio_options’ is empty.

Mostly asynchronous

I’ve mentioned ‘mostly asynchronous’ a number of times. What does that mean? In the current version (11.2.0.1) of the database, physical reads of data dictionary objects are ALWAYS done synchronously, regardless of options set and storage types.

Below samples done on a VM in VMWare fushion, OEL5u5 x64, database 11.2.0.1.

1. Database on ext3 filesystem, filesystemio_options=none (or empty)

1.a. logon to the database, list the PID of the server process
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 21 16:24:26 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select spid from v$session a, v$process b where a.paddr=b.addr and a.sid = (select distinct sid from v$mystat);

SPID
------------------------
6775

1.b. open a second session (on the database server), and start a strace:
$ strace -cp 6775
Process 6775 attached - interrupt to quit

1.c. switch back to the sqlplus session, and execute ‘select count(*) from dba_extents’ and exit:
SQL> select count(*) from dba_extents;

COUNT(*)
----------
7963

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

1.d. now switch to the strace session, it will summarise the systemcalls:

Process 7165 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
94.38 1.550058 304 5091 pread
2.57 0.042190 0 295641 getrusage
2.52 0.041446 364 114 munmap
0.45 0.007470 22 346 mmap
0.07 0.001130 0 10408 times

It tells us we spend 1.55 seconds on pread systemcalls. This is expected. But: this is synchronous IO. I guess most Oracle databases on linux just run synchronous and buffered.

2. Database on ext3 filesystem, filesystemio_options=setall

2.a logon to the database, list the PID of the server process
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 21 16:24:26 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select spid from v$session a, v$process b where a.paddr=b.addr and a.sid = (select distinct sid from v$mystat);

SPID
------------------------
7586

2.b. open a second session (on the database server), and start a strace:
$ strace -cp 7586
Process 7586 attached - interrupt to quit

2.c. switch back to the sqlplus session, and execute ‘select count(*) from dba_extents’ and exit:
SQL> select count(*) from dba_extents;

COUNT(*)
----------
7963

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2.d. now switch to the strace session for the summary:

Process 7586 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
93.42 1.412650 278 5089 pread
3.26 0.049300 0 295641 getrusage
2.99 0.045252 393 115 munmap
0.23 0.003473 10 344 mmap
0.10 0.001463 0 10408 times

This tells us we still did pread(), alias synchronous IO calls, despite filesystemio_options being set to ‘setall’.

3. Reading a regular table.

3.a. login to the database and create a table:
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 21 16:48:54 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test as select * from dba_source;

Table created.

3.b. list PID
SQL> select spid from v$session a, v$process b where a.paddr=b.addr and a.sid = (select distinct sid from v$mystat);

SPID
------------------------
15716

3.c. start a strace session in another window:
$ strace -cp 15716
Process 15716 attached - interrupt to quit

3.d. issue ‘select count(*) from test’ in the sqlplus session, and exit:
SQL> select count(*) from test;

COUNT(*)
----------
623146

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

3.e. now look at the strace session for the summary:
Process 14284 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
57.39 0.023942 73 326 io_submit
35.43 0.014781 336 44 pread
7.18 0.002997 9 317 io_getevents
0.00 0.000000 0 2 read
0.00 0.000000 0 2 write

Ah, now we have done asynchronous IO (io_submit, io_getevents), but there’s also ‘pread’, thus synchronous IO?
The pread() is the lookup in the data-dictionary the session needed to do to be able to get all the needed metadata for reading the ‘TEST’ table.

Let’s look into a database using ASM:

4.a. logon, verify ASM usage:
$ sq

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 21 17:12:50 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/bioinfo1/datafile/system.256.721389685
+DATA/bioinfo1/datafile/sysaux.257.721389685
+DATA/bioinfo1/datafile/undotbs1.258.721389685
+DATA/bioinfo1/datafile/users.259.721389685
+DATA/bioinfo1/datafile/original.266.721394641
+DATA/bioinfo1/datafile/dbfs.267.724598427

6 rows selected.

SQL>
This database is using ASM!
4.b. start a ‘strace -cp ‘ in another session.
4.c. execute ‘select count(*) from dba_extents’; exit
4.d. investigate summary of strace:
Process 5269 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
94.23 1.559458 279 5599 pread
3.89 0.064443 0 323685 getrusage
1.54 0.025487 167 153 munmap
0.28 0.004667 23 207 mmap
0.06 0.000955 0 11394 times

So, we just done synchronous IO, despite being on ASM.

Lets create the table from dba_source again, and look if that will invoke AIO:
5.a. login, create table
$ sq

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 21 17:18:38 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create table test as select * from dba_source;

Table created.

5.b. set ‘strace -cp ‘ again in another session
5.c. execute ‘select count(*) from test;’, then exit
5.d. investigate strace summary:

Process 5742 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
44.59 0.023152 71 327 io_submit
26.93 0.013983 215 65 pread
18.16 0.009431 63 149 munmap
9.62 0.004994 17 292 io_getevents
0.55 0.000283 283 1 mmap

Ah, now we are mostly doing asynchronous IO, again not for the data-dictionary lookups, but we do for the data itself!

Tagged: oracle aio dio ASM asynchronous IO direct performance

harald's picture

6th Planboard DBA Symposium: Call for Papers

After the successful 5th edition of the DBA Symposium, which featured Jonathan Lewis, Planboard just begun planning the 6th edition of this well-known Dutch DBA Symposium and the Call for Papers is now open. If you are a Dutch DBA who wants to share his or her experiences with other Dutch DBA’s you definitely want […]

fritshoogland's picture

DBFS and ORA-64007: invalid store specified

I made a post about creating and using Oracle’s DBFS. One thing I’ve done is just dropping the user which was the owner of a database filesystem (drop user cascade), with the intention to get rid of the database filesystem.

During the creation of a filesystem with the same name as I’ve used with the previous (dropped) user, I got this error message:

....much more other messages....
create stmt: begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_FS1',
tbl_name => 'T_FS1', tbl_tbs => 'dbfs', lob_tbs => 'dbfs', do_partition => true,
partition_key => 1, do_compress => false, compression => '', do_dedup => false,
do_encrypt => false); end;
ERROR: -64007 msg: ORA-64007: invalid store specified
ORA-06512: at
"SYS.DBMS_DBFS_SFS_ADMIN", line 473
ORA-00001: unique constraint
(SYS.SYS_C003185) violated
declare
*
ERROR at line 1:
ORA-64007: invalid store specified
ORA-06512: at "SYS.DBMS_DBFS_SFS_ADMIN", line 473
ORA-00001: unique constraint (SYS.SYS_C003185) violated
ORA-06512: at line 63

SQL> show errors;
No errors.
SQL>
SQL> commit;

Commit complete.

SQL>
SQL> drop procedure fsCreate;

Procedure dropped.

SQL>
SQL> undefine ts_name
SQL> undefine fs_name
SQL> undefine fs_compress
SQL> undefine fs_dedup
SQL> undefine fs_encrypt
SQL> undefine fs_partition
SQL>
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
$

This means the name specified as filesystem name (fs1 here). Is already present in the data dictionary.

How to solve this?

The best way is to drop the database filesystem with the script ‘dbfs_drop_filesystem’ in $ORACLE_HOME/rdbms/admin.

If the harm is already done:

1) Check if the filesystem you want to create not just exists. Do this by searching for a table with the name of the filesystem, prefixed with ‘T_’, so for ‘fs1′: T_FS1; as DBA:
SQL> select owner, table_name from dba_tables where table_name like 'T_FS1';

2) Get the table identifier from DBFS_SFS$_TAB:
SQL> select tabid from sys.dbfs_sfs$_tab where table_name = 'T_FS1';

TABID
-----
57

3) Drop records belonging to filesystem FS1 in the data dictionary:
SQL> delete from sys.dbfs_sfs$_vol where tabid = 57;
SQL> delete from sys.dbfs_sfs$_tab where tabid = 57;
SQL> commit;

4) Now the ‘old’ information of filesystem ‘fs1′ is removed. Now it’s possible to create a filesystem with the name ‘fs1′ again.

Tagged: oracle database dbfs filesystem ora 64007 ora-64007

fritshoogland's picture

Oracle database filesystem (DBFS) done the easy way!

I was introduced to the database filesystem by Kevin Closson during an Exadata seminar. At first it struck me as a little odd. What could be a valid reason to store files or a filesystem inside the database? When doing research on the internet, I found a description for setup on Tim’s Oracle Base website. [...]

dannorris's picture

unplumb (or unbinding) NICs on Linux

I’ve been quiet for a long time now, but this entry hopefully will shake the cobwebs off and get me back into the habit.

I recently had a need to “unplumb” (from Solaris fame) or make interfaces on Linux “disappear” from the ifconfig list. It could be that I don’t know how to completely deconfigure an interface, but I didn’t find any methods to unassign an IP address from a Linux Ethernet interface after it was assigned. You can take interfaces down (ifconfig eth3 down) and reconfigure them to assign different addresses, but not remove the address completely.

To prevent automated spam submissions leave this field empty.
Syndicate content