Search

OakieTags

more tags

Who's online

There are currently 1 user and 31 guests online.

Online users

Recent comments

Affiliations

Home

Oakies Blog Aggregator

Oracle OpenWorld 2010 Agenda (Breakfast In America)

Submitted by Richard Foote on Wed, 2010-07-21 15:09

I was all set and ready to go Oracle OpenWorld this year when things took a turn for the worse and it looked very much as if I wouldn’t be able to make it but things have thankfully changed yet again and it now looks like I will finally be able to make it to OOW this [...]

The Core Performance Fundamentals Of Oracle Data Warehousing – Set Processing vs Row Processing

Submitted by Greg Rahn on Tue, 2010-07-20 10:00

[back to Introduction] In over six years of doing data warehouse POCs and benchmarks for clients there is one area that I frequently see as problematic: “batch jobs”.  Most of the time these “batch jobs” take the form of some PL/SQL procedures and packages that generally perform some data load, transformation, processing or something similar.  The reason these are so problematic is that developers have hard-coded “slow” into them.  I’m generally certain these developers didn’t know they had done this when they coded their PL/SQL, but none the less it happened. So How Did “Slow” Get Hard-Coded Into My PL/SQL? Generally “slow” gets hard-coded into PL/SQL because the PL/SQL developer(s) took the business requirements and did a “literal translation” of each rule/requirement one at a time instead of looking at the “before picture” and the “after picture” and determining the most efficient way to make those data changes.  Many times this can surface as cursor based row-by-row processing, but it also can appear as PL/SQL just running a series of often poorly thought out SQL commands. Hard-Coded Slow Case Study The following is based on a true story. Only the facts names have been changed to protect the innocent. Here is [...]

Fragmentation 3

Submitted by Jonathan Lewis on Mon, 2010-07-19 20:05

This note is part three of a four-part series, and covers Table fragmentation. The whole series is as follows Introduction Disk and Tablespace Fragmentation Table Fragmentation – this bit Index Fragmentation 3. Table “fragmentation”. In the introduction we discussed one type of table fragmentation that doesn’t (usually) matter – the fragmentation of a table into [...]

Index Block Dump: Block Header Part I (Editions Of You)

Submitted by Richard Foote on Mon, 2010-07-19 15:18

I’ve previously looked at how to generate an Oracle block dump, time to now go into a little more detail. As I mentioned, a block dump is a formatted representation of the actual contents of an Oracle block. Producing strategic block dumps can be an extremely useful method of determining what might be going on in Oracle under the [...]

6th Planboard DBA Symposium: Call for Papers

Submitted by harald on Mon, 2010-07-19 13:32

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

Mass Agent Deployments

Submitted by Niall Litchfield on Sun, 2010-07-18 17:32

This is just a short update on the prerequisites, especially firewall port requirements that you will need to ensure agent deployments in Enterprise Manager Grid Control work correctly. This is not well documented in the install manual. Chapter 9 of the advanced installation guide contains the basic requirements. It however misses out some basics. This [...]

Advert: UKOUG 2010 Call for Papers Closing

Submitted by doug on Sun, 2010-07-18 10:32

Had a few beers in London with Carl Dudley last week. As well as being old Learning Tree 'colleagues' he's one of the Directors of the UKOUG. When discussing the Call for Papers for the 2010 UKOUG Tech & EBS conference, it occurred to me that I'd only seen one low-key announcement of the Call for Papers and had promptly forgotten it but was vaguely aware it must be due soon.

The deadline is 2nd August, which is a couple of weeks away, so now is the time to get working on those abstract submissions if you haven't already. Whilst I know it's tough to be accepted for the agenda, I also know that the user group are always keen to hear some real user stories across a wide range of products and subjects.

The link for submitting papers is: http://www.oug.org/techebs. Once on the homepage, please click on the “Call for papers” tab at the top of the page.

I and others have said enough marvellous things about this particular conference in the past that I don't think I need say more than it's one of my favourite events of the year and, even when I try to cut down on conferences, this one is usually a fixture.

DBFS and ORA-64007: invalid store specified

Submitted by fritshoogland on Sat, 2010-07-17 19:20

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!

Submitted by fritshoogland on Sat, 2010-07-17 13:29

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

Submitted by dannorris on Sat, 2010-07-17 05:30

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.

After many searches and finding nothing that matched my need, I turned to my fellow Oakies (thanks, Mark!) who turned up this post from 2 years ago that hinted at a solution. It is driver-specific which is not ideal, but that makes sense given what I’m trying to do.

Here’s the generic version of the solution:

echo "" > /sys/bus/pci/drivers//unbind

Determining the driver_name is pretty simple: check the /etc/modprobe.conf file (on OEL/RHEL 5.x). In that file, you’ll find things like this:

...
alias eth0 igb
alias eth1 igb
alias eth2 igb
alias eth3 igb
...

These lines indicate that the Ethernet driver used on this system by eth[0-3] is the igb driver. Now that you know the driver name, the tricky part is figuring out what the driver wants you to use as the interface name. I’ll give a few examples (and I haven’t figured out the scientific way to determine what the driver expects short of reading source code).

For the bnx2 driver, you can use the relatively simple ethernet interface name, like this:

echo "eth2" > /sys/bus/pci/drivers/bnx2/unbind

For my test system, the igb driver doesn’t use the “simple” Ethernet interface name like the bnx2 driver does. Instead, when trying that, it gives an error that the interface doesn’t exist. Time to dig in a little deeper.

On this system, the igb directory looks like this:

# ls -l /sys/bus/pci/drivers/igb/


total 0
lrwxrwxrwx 1 root root    0 Jul 16 13:12 0000:01:00.0 -> ../../../../devices/pci0000:00/0000:00:01.0/0000:01:00.0
lrwxrwxrwx 1 root root    0 Jul 16 13:12 0000:01:00.1 -> ../../../../devices/pci0000:00/0000:00:01.0/0000:01:00.1
lrwxrwxrwx 1 root root    0 Jul 16 13:12 0000:07:00.0 -> ../../../../devices/pci0000:00/0000:00:02.0/0000:07:00.0
lrwxrwxrwx 1 root root    0 Jul 16 13:12 0000:07:00.1 -> ../../../../devices/pci0000:00/0000:00:02.0/0000:07:00.1
--w------- 1 root root 4096 Jul 16 13:12 bind
lrwxrwxrwx 1 root root    0 Jul 16 13:12 module -> ../../../../module/igb
--w------- 1 root root 4096 Jul 16 13:12 new_id
--w------- 1 root root 4096 Jul 16 13:12 remove_id
--w------- 1 root root 4096 Jul 16 13:12 unbind
#

So, knowing that I have 4 interfaces on the system, I made the  correlation to the 4 addresses that appear as symlinks in the driver’s  directory and expect that they indicate the interface name. Checking a  couple of those (each symlink references a directory), I see this:


# ls -Ll /sys/bus/pci/drivers/igb/0000:01:00.0


total 0
-rw-r--r-- 1 root root   4096 Jul 16 13:15 broken_parity_status
drwxr-xr-x 5 root root      0 Jul 16 13:12 bus
-r--r--r-- 1 root root   4096 Jul 16 12:57 class
-rw-r--r-- 1 root root   4096 Jul 16 12:57 config
-r--r--r-- 1 root root   4096 Jul 16 12:57 device
drwxr-xr-x 2 root root      0 Jul 16 13:12 driver
-rw------- 1 root root   4096 Jul 16 13:15 enable
-r--r--r-- 1 root root   4096 Jul 16 12:57 irq
-r--r--r-- 1 root root   4096 Jul 16 13:00 local_cpus
-r--r--r-- 1 root root   4096 Jul 16 13:15 modalias
drwxr-xr-x 3 root root      0 Jul 16 12:57 net:eth0
drwxr-xr-x 2 root root      0 Jul 16 12:55 power
-r--r--r-- 1 root root   4096 Jul 16 12:57 resource
-rw------- 1 root root 131072 Jul 16 13:15 resource0
-rw------- 1 root root 131072 Jul 16 13:15 resource1
-rw------- 1 root root     32 Jul 16 13:15 resource2
-rw------- 1 root root  16384 Jul 16 13:15 resource3
-r-------- 1 root root 131072 Jul 16 13:15 rom
drwxr-xr-x 5 root root      0 Jul 16 13:12 subsystem
-r--r--r-- 1 root root   4096 Jul 16 13:15 subsystem_device
-r--r--r-- 1 root root   4096 Jul 16 13:15 subsystem_vendor
--w------- 1 root root   4096 Jul 16 13:15 uevent
-r--r--r-- 1 root root   4096 Jul 16 12:57 vendor
# ls -Ll /sys/bus/pci/drivers/igb/0000:07:00.0
total 0
-rw-r--r-- 1 root root   4096 Jul 16 13:13 broken_parity_status
drwxr-xr-x 5 root root      0 Jul 16 13:12 bus
-r--r--r-- 1 root root   4096 Jul 16 12:57 class
-rw-r--r-- 1 root root   4096 Jul 16 12:57 config
-r--r--r-- 1 root root   4096 Jul 16 12:57 device
drwxr-xr-x 2 root root      0 Jul 16 13:12 driver
-rw------- 1 root root   4096 Jul 16 13:13 enable
-r--r--r-- 1 root root   4096 Jul 16 12:57 irq
-r--r--r-- 1 root root   4096 Jul 16 13:00 local_cpus
-r--r--r-- 1 root root   4096 Jul 16 13:13 modalias
drwxr-xr-x 3 root root      0 Jul 16 12:57 net:eth2
drwxr-xr-x 2 root root      0 Jul 16 13:13 power
-r--r--r-- 1 root root   4096 Jul 16 12:57 resource
-rw------- 1 root root 131072 Jul 16 13:13 resource0
-rw------- 1 root root 131072 Jul 16 13:13 resource1
-rw------- 1 root root     32 Jul 16 13:13 resource2
-rw------- 1 root root  16384 Jul 16 13:13 resource3
-r-------- 1 root root 131072 Jul 16 13:13 rom
drwxr-xr-x 5 root root      0 Jul 16 13:12 subsystem
-r--r--r-- 1 root root   4096 Jul 16 13:13 subsystem_device
-r--r--r-- 1 root root   4096 Jul 16 13:13 subsystem_vendor
--w------- 1 root root   4096 Jul 16 13:15 uevent
-r--r--r-- 1 root root   4096 Jul 16 12:57 vendor

You can see the directory with name “net:” as a  subdirectory in each listing above. This tells us which interface from  /sys/bus/pci/drivers/igb/0000* corresponds with which of the Linux  Ethernet interface names. From this, we can see that eth2 is really  0000:07:00.0. So, in order to unbind this interface such that it no  longer appears in the “ifconfig -a” output, we run this command:


echo "0000:07:00.0" > /sys/bus/pci/drivers/igb/unbind
and then it no longer appears in the “ifconfig -a” output. If you  wanted to make this permanent, you should comment out the corresponding  line from /etc/modprobe.conf so that it won’t be configured at boot  time. Using the echo command above takes effect immediately, but won’t  persist through a reboot (after reboot, the interface will return)  unless the /etc/modprobe.conf changes are made.


Now, hopefully the next blog post after this one won’t require 14  more months of preparation!