Search

Top 60 Oracle Blogs

Recent comments

linux

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

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. [...]

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.

Installing systemtap on OEL5, update 5

Systemtap is a scripting language for analyzing linux systems. Systemtap needs debuginformation to be able to know what is going on. Systemtap is considered the “answer” to Sun/Oracle’s DTrace. Systemtap and DTrace differ, most notably because DTrace doesn’t need additional software (debug information) for both kernel and userspace. Let’s see how Systemtap can be installed [...]

Cloning prior to patching/upgrading/CPU: what do you do?

In addition to my previous post about cloning, please tell me your experiences and practices. What do you do when altering your Oracle database software? I think a home name should include a FULL version number (so 11.2.0.1 instead of 11.2.0) to indicate the version of the home. If you strictly clone the home before [...]

Where are the logs for the SCAN listeners?

Quick post and note to self. Where are the SCAN listener log files? A little bit of troubleshooting was required, but I guess I could have read the manuals too. In the end it turned out to be quite simple!

First of all, I needed to find out where on my four node cluster I had a SCAN listener. This is done quite easily by asking Clusterware:

[grid@rac11gr2node2 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac11gr2node2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac11gr2node4
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac11gr2node3

I was initially on the first node, so had to ssh to the second. From there on I thought that the proc file system might have the answer. I needed to get the PID of the SCAN listener first:

[grid@rac11gr2node2 ~]$ ps -ef | grep -i scan
grid      4738     1  0 Jun03 ?        00:00:13 /u01/app/grid/product/11.2.0/crs/bin/tnslsnr LISTENER_SCAN1 -inherit
grid     24694 24147  0 20:55 pts/0    00:00:00 grep -i scan

Now /proc/4738/fd lists all the open file descriptors used by the SCAN listener. Surely the log.xml file would be there somewhere:

[grid@rac11gr2node2 ~]$ ll /proc/4738/fd
total 0
lrwx------ 1 grid oinstall 64 Jun 16 20:46 0 -> /dev/null
lrwx------ 1 grid oinstall 64 Jun 16 20:46 1 -> /dev/null
lrwx------ 1 grid oinstall 64 Jun 16 20:46 10 -> socket:[20906]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 11 -> socket:[20908]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 12 -> socket:[20927]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 13 -> socket:[20957]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 14 -> socket:[20958]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 15 -> socket:[22991]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 16 -> socket:[10712179]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 17 -> socket:[10173760]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 18 -> socket:[10176036]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 19 -> socket:[9106216]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 2 -> /dev/null
lr-x------ 1 grid oinstall 64 Jun 16 20:46 3 -> /u01/app/grid/product/11.2.0/crs/rdbms/mesg/diaus.msb
lr-x------ 1 grid oinstall 64 Jun 16 20:46 4 -> /proc/4738/fd
lr-x------ 1 grid oinstall 64 Jun 16 20:46 5 -> /u01/app/grid/product/11.2.0/crs/network/mesg/nlus.msb
lr-x------ 1 grid oinstall 64 Jun 16 20:46 6 -> pipe:[20893]
lr-x------ 1 grid oinstall 64 Jun 16 20:46 7 -> /u01/app/grid/product/11.2.0/crs/network/mesg/tnsus.msb
lrwx------ 1 grid oinstall 64 Jun 16 20:46 8 -> socket:[20904]
l-wx------ 1 grid oinstall 64 Jun 16 20:46 9 -> pipe:[20894]

Well maybe not. Next option is to query the listener itself via lsnrctl. Nothing easier that that:

LSNRCTL> set current_listener LISTENER_SCAN1
Current Listener is LISTENER_SCAN1
LSNRCTL> show log_file
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
LISTENER_SCAN1 parameter "log_file" set to /u01/app/grid/product/11.2.0/crs/log/diag/tnslsnr/rac11gr2node2/listener_scan1/alert/log.xml
The command completed successfully
LSNRCTL>

Aha, it uses the ADR as well. So back there, change the base and query the file:

[grid@rac11gr2node2 ~]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Wed Jun 16 20:58:17 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> set base /u01/app/grid/product/11.2.0/crs/log
adrci> show home
ADR Homes:
diag/tnslsnr/rac11gr2node2/listener_scan1
diag/tnslsnr/rac11gr2node2/listener_scan3
diag/tnslsnr/rac11gr2node2/listener_scan2
adrci> set home diag/tnslsnr/rac11gr2node2/listener_scan1
adrci> show alert -tail
2010-06-16 20:58:25.021000 +01:00
16-JUN-2010 20:58:25 * service_update * polstdby_1 * 0
2010-06-16 20:58:27.441000 +01:00
16-JUN-2010 20:58:27 * service_update * poldb_2 * 0
2010-06-16 20:58:30.444000 +01:00
16-JUN-2010 20:58:30 * service_update * poldb_2 * 0
16-JUN-2010 20:58:30 * service_update * poldb_1 * 0
2010-06-16 20:58:33.442000 +01:00
16-JUN-2010 20:58:33 * service_update * poldb_2 * 0
2010-06-16 20:58:35.784000 +01:00
16-JUN-2010 20:58:35 * service_update * prod1 * 0
16-JUN-2010 20:58:36 * service_update * poldb_2 * 0
16-JUN-2010 20:58:36 * service_update * poldb_1 * 0
2010-06-16 20:58:39.546000 +01:00
16-JUN-2010 20:58:39 * service_update * poldb_2 * 0
16-JUN-2010 20:58:39 * service_update * poldb_1 * 0
2010-06-16 20:58:42.574000 +01:00
16-JUN-2010 20:58:42 * service_update * poldb_2 * 0
2010-06-16 20:58:45.574000 +01:00
16-JUN-2010 20:58:45 * service_update * poldb_2 * 0
2010-06-16 20:58:48.576000 +01:00
16-JUN-2010 20:58:48 * service_update * poldb_2 * 0
16-JUN-2010 20:58:48 * service_update * poldb_1 * 0
2010-06-16 20:58:51.575000 +01:00
16-JUN-2010 20:58:51 * service_update * poldb_2 * 0
16-JUN-2010 20:58:51 * service_update * poldb_1 * 0
2010-06-16 20:58:54.578000 +01:00
16-JUN-2010 20:58:54 * service_update * poldb_2 * 0

Job done.

crsctl status resource – state details are really useful

A very short post about a cool new feature I noticed today. RAC 11.2 has moved a lot of commands previously having their own syntax into crsctl. One of the cool new things is the fact that crsctl status resource -t (“tabular”) reports state details. Here I could see that my lab environment had a stuck archiver. Other state details include information about the cluster time synchronisation daemon ctss, or ASM instances. Have a look at my 4 node cluster:

[oracle@rac11gr2node2 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  INTERMEDIATE rac11gr2node4                                
ora.LISTENER.lsnr
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.OCRVOTE.dg
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  INTERMEDIATE rac11gr2node4                                
ora.asm
 ONLINE  ONLINE       rac11gr2node1            Started             
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  INTERMEDIATE rac11gr2node4                                
ora.eons
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.gsd
 OFFLINE OFFLINE      rac11gr2node1                                
 OFFLINE OFFLINE      rac11gr2node2                                
 OFFLINE OFFLINE      rac11gr2node3                                
 OFFLINE OFFLINE      rac11gr2node4                                
ora.net1.network
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.ons
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.registry.acfs
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
 1        ONLINE  ONLINE       rac11gr2node2                                
ora.LISTENER_SCAN2.lsnr
 1        ONLINE  ONLINE       rac11gr2node4                                
ora.LISTENER_SCAN3.lsnr
 1        ONLINE  ONLINE       rac11gr2node3                                
ora.oc4j
 1        OFFLINE OFFLINE                                                   
ora.poldb.db
 1        ONLINE  INTERMEDIATE rac11gr2node3            Stuck Archiver      
 2        ONLINE  INTERMEDIATE rac11gr2node4            Stuck Archiver      
ora.poldb.drcp.svc
 1        ONLINE  ONLINE       rac11gr2node3                                
 2        ONLINE  INTERMEDIATE rac11gr2node4                                
ora.poldb.nondrcp.svc
 1        ONLINE  INTERMEDIATE rac11gr2node4                                
ora.polstdby.db
 1        ONLINE  INTERMEDIATE rac11gr2node4            Stuck Archiver      
 2        OFFLINE OFFLINE                                                   
ora.prod.batchserv.svc
 1        ONLINE  ONLINE       rac11gr2node2                                
 2        ONLINE  ONLINE       rac11gr2node1                                
ora.prod.db
 1        ONLINE  ONLINE       rac11gr2node1            Open                
 2        ONLINE  ONLINE       rac11gr2node2                                
ora.prod.reporting.svc
 1        ONLINE  ONLINE       rac11gr2node2                                
 2        ONLINE  ONLINE       rac11gr2node1                                
ora.rac11gr2node1.vip
 1        ONLINE  ONLINE       rac11gr2node1                                
ora.rac11gr2node2.vip
 1        ONLINE  ONLINE       rac11gr2node2                                
ora.rac11gr2node3.vip
 1        ONLINE  ONLINE       rac11gr2node3                                
ora.rac11gr2node4.vip
 1        ONLINE  ONLINE       rac11gr2node4                                
ora.scan1.vip
 1        ONLINE  ONLINE       rac11gr2node2                                
ora.scan2.vip
 1        ONLINE  ONLINE       rac11gr2node4                                
ora.scan3.vip
 1        ONLINE  ONLINE       rac11gr2node3                                

Nice!

Display scheduler class for a process in linux

The ps command in the ways I use it most (ps -ef and ps auxwww) doesn’t display the scheduling class for a process. Oracle have cunningly released a patchset to update Grid Infrastructure that changes the scheduling class from the VKTM and LMSn ASM processes to “Timeshare” instead of Realtime.

So far so good, but I had no idea how to display the scheduling class of a process so some man page reading and Internet research were in order. After some digging around I found out that using the BSD command line syntax combined with the “–format” option does the trick. The difficult bit was in figuring out which format identifiers to use. All the information ps can get about a process are recorded in /proc/pid/stat. Parsing this with a keen eye however proves difficult due to the sheer number of fields in the file. So back to using ps (1).

Here’s the example. Before applying the workaround to the patch, Oracle ASM’s VKTM (virtual keeper of time) and LMSn (global cache services process) run with TS priority:

[oracle@rac11gr2node2 ~]$ ps ax --format uname,pid,ppid,tty,cmd,cls,pri,rtprio \
>| egrep "(vktm|lms)" | grep asm
grid      4296     1 ?        asm_vktm_+ASM2               TS  24      -
grid      4318     1 ?        asm_lms0_+ASM2               TS  24      -

After applying the workaround the scheduling class changed:

[oracle@rac11gr2node1 ~]$ ps ax --format uname,pid,ppid,tty,cmd,cls,pri,rtprio | egrep "(vktm|lms)" | grep asm
grid      2352     1 ?        asm_vktm_+ASM1               RR  41      1
grid      2374     1 ?        asm_lms0_+ASM1               RR  41      1

Notice how the cls field changed, and also that the rtprio is now populated. I have learned something new today.

5th Planboard DBA Symposium: Registration now open

On June 8 Planboard will run her 5th Dutch DBA Symposium and the registration is now open. This “for Dutch DBA’s, by Dutch DBA’s” symposium has become the place to be for the serious DBA who wants to share his or her knowledge with other DBA’s in an open environment with plenty of networking time […]

5th Planboard DBA Symposium Featuring Jonathan Lewis

I am pleased to announce that we, the program committee members, just finalized the program for the 5th Planboard DBA Symposium to be held at June 8 in Driebergen. Because this is the 5th edition we wanted to bring you something special and I am proud to announce that Jonathan Lewis is going to talk [...]