Wednesday, May 15, 2013

OLTP on Exadata and ODA



Интересная презентация на тему пригдоности Экзадаты и ODA для OLTP-задач:


Презентация немного устаревшая – еще про Х2-2 и не учитывает WriteBack Smart Flash Cache, но тем не менее весьма полезная.

Monday, May 13, 2013

error: Zip file too big (greater than 4294959102 bytes)

The customer send us the dump for Exadata POC and this dump is unavailable to unzip :

[root@ed01db01 ]# unzip dmp.zip
error:  Zip file too big (greater than 4294959102 bytes)
Archive:  dmp.zip
warning [dmp.zip]:  10149171547 extra bytes at beginning or within zipfile
  (attempting to process anyway)
error [dmp.zip]:  start of central directory not found;
  zipfile corrupt.
  (please check that you have transferred or created the zipfile in the
  appropriate BINARY mode and that you have compiled UnZip properly)

Our unzip version is:

[root@ed01db01 ]# unzip
UnZip 5.52 of 28 February 2005, by Info-ZIP.  Maintained by C. Spieler.  Send
bug reports using
http://www.info-zip.org/zip-bug.html; see README for details.

The solution 1:

[root@ed01db01 ttelek]# funzip dmp.zip > dmp.dmp
[root@ed01db01 ttelek]# ls -l

-rw-r--r-- 1 root   root 65195868160 May 13 11:24 dmp.dmp
-rw-r----- 1 oracle dba  14444138961 May 12 00:03 dmp.zip

[root@ed01db01 ttelek]# funzip
fUnZip (filter UnZip), version 3.94 of 17 February 2002
usage: ... | funzip [-password] | ...
       ... | funzip [-password] > outfile
       funzip [-password] infile.zip > outfile
       funzip [-password] infile.gz > outfile
Extracts to stdout the gzip file or first zip entry of stdin or the given file.

Our conclusion: the Exadata has the funzip by default, we don't install it intentionally.

The solution 2:

[root@ed01db01 ]# zcat dmp.zip > dmp.dmp

Exadata has zcat by default.

 

Wednesday, May 8, 2013

ORA-30094: failed to find the time zone data file for version ... in $ORACLE_HOME/oracore/zoneinfo

We have the one 11.2.0.3 DB + latest time zone installed on it - version 19.
Imagine our surprise when we've got the message during the import:

ORA-39097: Data Pump job encountered unexpected error -30094
ORA-39065: unexpected master process exception in DISPATCH
ORA-30094: failed to find the time zone data file for version 17 in $ORACLE_HOME/oracore/zoneinfo



Why ? How it is possible ? Why Oracle not used latest time zone ?

We don't know, but we have the solution :



download the appropriate version of the DST (version 17 for our case, p12949905_112030_Linux-x86-64.zip) and unzip it somewhere:

[oracle@ed01db01 DST]$ unzip p12949905_112030_Linux-x86-64.zip
Archive:  p12949905_112030_Linux-x86-64.zip
   creating: 12949905/
  inflating: 12949905/README.txt
  inflating: 12949905/README.html
   creating: 12949905/files/
   creating: 12949905/files/oracore/
   creating: 12949905/files/oracore/zoneinfo/
  inflating: 12949905/files/oracore/zoneinfo/readme_17.txt

   creating: 12949905/files/oracore/zoneinfo/little/
  inflating: 12949905/files/oracore/zoneinfo/little/timezone_17.dat
  inflating: 12949905/files/oracore/zoneinfo/little/timezlrg_17.dat
  inflating: 12949905/files/oracore/zoneinfo/timezone_17.dat

   creating: 12949905/files/oracore/zoneinfo/big/
  inflating: 12949905/files/oracore/zoneinfo/big/timezone_17.dat
  inflating: 12949905/files/oracore/zoneinfo/big/timezlrg_17.dat
  inflating: 12949905/files/oracore/zoneinfo/timezlrg_17.dat


   creating: 12949905/etc/
   creating: 12949905/etc/config/
  inflating: 12949905/etc/config/inventory.xml
  inflating: 12949905/etc/config/actions.xml
  inflating: 12949905/etc/config/deploy.xml
   creating: 12949905/etc/xml/
  inflating: 12949905/etc/xml/GenericActions.xml
  inflating: 12949905/etc/xml/ShiphomeDirectoryStructure.xml



 

As we know, Oracle hold the time zone files in $OH/oracore/zoneinfo.
Therefore, simply copy red files from patch into $OH/oracore/zoneinfo directory.
Don't forget about directory structure and save latest $OH/oracore/zoneinfo/readme_19.txt (don't copy old readme_17.txt file from patch into your $OH)

For example:

[oracle@ed01db01 zoneinfo]$ rm /store/DST/12949905/files/oracore/zoneinfo/readme_17.txt
[oracle@ed01db01 zoneinfo]$ ls -l /store/DST/12949905/files/oracore/zoneinfo/*
-rw-rw-rw- 1 oracle oinstall 779003 Oct 14  2011 /store/DST/12949905/files/oracore/zoneinfo/timezlrg_17.dat
-rw-rw-rw- 1 oracle oinstall 341718 Oct 14  2011 /store/DST/12949905/files/oracore/zoneinfo/timezone_17.dat

/store/DST/12949905/files/oracore/zoneinfo/big:
total 1108
-rw-rw-rw- 1 oracle oinstall 779003 Oct 14  2011 timezlrg_17.dat
-rw-rw-rw- 1 oracle oinstall 341718 Oct 14  2011 timezone_17.dat

/store/DST/12949905/files/oracore/zoneinfo/little:
total 1108
-rw-rw-rw- 1 oracle oinstall 779003 Oct 14  2011 timezlrg_17.dat
-rw-rw-rw- 1 oracle oinstall 341718 Oct 14  2011 timezone_17.dat
 

[oracle@ed01db01 zoneinfo]$  cd $OH/oracore/zoneinfo
[oracle@ed01db01 zoneinfo]$ cp -r /store/DST/12949905/files/oracore/zoneinfo/* .
[oracle@ed01db01 zoneinfo]$ ls -l
total 15680
drwxr-xr-x 2 oracle oinstall   4096 May  8 12:33 big
drwxr-xr-x 2 oracle oinstall   4096 May  8 12:33 little
-rwxrwxrwx 1 oracle oinstall   7395 Dec 31 12:11 readme_19.txt
-rw-r--r-- 1 oracle oinstall   6068 Jun  7  2010 readme.txt
-rw-r--r-- 1 oracle oinstall  31525 Jun  7  2010 timezdif.csv
-rw-r--r-- 1 oracle oinstall 792894 Aug 18  2011 timezlrg_10.dat
-rw-r--r-- 1 oracle oinstall 787272 Aug 18  2011 timezlrg_11.dat
-rw-r--r-- 1 oracle oinstall 785621 Aug 18  2011 timezlrg_12.dat
-rw-r--r-- 1 oracle oinstall 782475 Aug 18  2011 timezlrg_13.dat
-rw-r--r-- 1 oracle oinstall 791430 Aug 18  2011 timezlrg_14.dat
-rw-r--r-- 1 oracle oinstall 779003 May  8 12:33 timezlrg_17.dat
-rwxr-xr-x 1 oracle oinstall 785786 Dec 31 12:11 timezlrg_19.dat
-rw-r--r-- 1 oracle oinstall 493675 Aug 18  2011 timezlrg_1.dat
-rw-r--r-- 1 oracle oinstall 507957 Aug 18  2011 timezlrg_2.dat
-rw-r--r-- 1 oracle oinstall 527717 Aug 18  2011 timezlrg_3.dat
-rw-r--r-- 1 oracle oinstall 531137 Aug 18  2011 timezlrg_4.dat
-rw-r--r-- 1 oracle oinstall 587487 Aug 18  2011 timezlrg_5.dat
-rw-r--r-- 1 oracle oinstall 586750 Aug 18  2011 timezlrg_6.dat
-rw-r--r-- 1 oracle oinstall 601242 Aug 18  2011 timezlrg_7.dat
-rw-r--r-- 1 oracle oinstall 616723 Aug 18  2011 timezlrg_8.dat
-rw-r--r-- 1 oracle oinstall 801410 Aug 18  2011 timezlrg_9.dat
-rw-r--r-- 1 oracle oinstall 345637 Aug 18  2011 timezone_10.dat
-rw-r--r-- 1 oracle oinstall 345356 Aug 18  2011 timezone_11.dat
-rw-r--r-- 1 oracle oinstall 345024 Aug 18  2011 timezone_12.dat
-rw-r--r-- 1 oracle oinstall 344425 Aug 18  2011 timezone_13.dat
-rw-r--r-- 1 oracle oinstall 344448 Aug 18  2011 timezone_14.dat
-rw-r--r-- 1 oracle oinstall 341718 May  8 12:33 timezone_17.dat
-rwxr-xr-x 1 oracle oinstall 343167 Dec 31 12:11 timezone_19.dat
-rw-r--r-- 1 oracle oinstall 274427 Aug 18  2011 timezone_1.dat
-rw-r--r-- 1 oracle oinstall 274900 Aug 18  2011 timezone_2.dat
-rw-r--r-- 1 oracle oinstall 286651 Aug 18  2011 timezone_3.dat
-rw-r--r-- 1 oracle oinstall 286264 Aug 18  2011 timezone_4.dat
-rw-r--r-- 1 oracle oinstall 286310 Aug 18  2011 timezone_5.dat
-rw-r--r-- 1 oracle oinstall 286217 Aug 18  2011 timezone_6.dat
-rw-r--r-- 1 oracle oinstall 286815 Aug 18  2011 timezone_7.dat
-rw-r--r-- 1 oracle oinstall 302100 Aug 18  2011 timezone_8.dat
-rw-r--r-- 1 oracle oinstall 351525 Aug 18  2011 timezone_9.dat
[oracle@ed01db01 zoneinfo]$




Does DEALLOCATE UNUSED or SHRINK SPACE will free space occupied by LOB segment?

Lets check how it works. My env is DB 19.20@Linux-x64 1) I created the table with 4 LOB columns of 4 different LOB types: BASICFILE BLOB, BA...