RDBMS: 10.2.0.4
Situation:
I hat deadlock on a partitoned table between a parallel insert and a parallel update statement. On the SR at Oracle statet first that this is not possibel. But on my testcase a have seen exclusive TM locks on partitons.
In the oracle documentation can be seen that locking works differently on parallel DML.
SR Result:
Oracle stated that the dynamic_sampling hint of the update statement is does not improfe the query perfomance, which was wrong. It does imporve the query speed in my case.
The more interesting statement was that dynamic sampling aquires a eclusive TM lock. - OK, that not good.
Today, I want to share a short but genius script with you.
The script restored a backup pice from vertias netbackup and moves it to another server.
Simple, but very useful if you can just restore to production host, but no space for all files is availiable.
After the restore I need to do a dupicate of the production database.
FILE=filelist
while read line
do
# change namfe file
#grep "$line" renamefile > renamefile2
echo "get file $line from tape"
# restore datafile
bprestore -B -R /u05/db_backup/TXXXX01/dup/renamefile -p XXXX_F3d -C hostnbname-bck -s 04/12/09 -e 04/20/09 -L /tmp/restore.$$.log $line
# new filename
file2=`echo $line |sed -e 's/TXXXX01\//TXXXX01\/dup\//'`
# wait for file exists
echo "exists file2 check .."
while [ ! -f $file2 ]; do sleep 15; done
# wait for no process is accessing the file
echo “no process access on $file2 check ..”
while [ $(fuser $file2 2>&1| awk '{FS=":"; print $2}' | wc -m) -gt 1 ]; do sleep 15; done
# copy to remote site
echo “copy file $file2 ”
scp $file2 oracle@hostnameXY:/u00/app/oracle/admin/TXXXX01/backup/
# delete file
echo “delete file $file2 ”
rm $file2
done < $FILE
exit 0
On tricky thing is to read the man page of bprestore and understand the format for the renamefile
The content of the file has to be formated like this:
change /u05/bacukp/TXXXX01/ctl_TXXXX01_2009-04-12_22-01-26_s107189_p1 to /u05/db_backup/TXXXX01/dup/ctl_TXXXX01_2009-04-12_22-01-26_s107189_p1
Answer: No!
Why:
- There are lot of things to tune, to get ZFS running with similar perfomance as on vxfv or ASM
- We hat frequent system hanging issues on several high load Oracle 10g Databases (only reboot hepled)
- Sun doe not really publish a real bug list, everthing you find form sun are maketing paper which tell you how cool ZFS is
- To find the truth you have search for blogs, forums, mailinglist with thousands of people reporting problems with ZFS
- No advantage of using ZFS. vxfs runs stable for more than 10 year, ASM runs stable for more than 5 years, ZFS still full of bugs
Summary:
ZFS = Code + lots of Marketing
Recomondation:
Run Oracle on vxfs or ASM (if you use solaris)
Update: The official bug list from Sun is here, http://opensolaris.org/os/community/zfs/version/4/
There are currently two bug listet (where are alle the others ?)