Dani's IT Blog

…moving from Oracle to Software Development

Exadata Präsentation

leave a comment »

Am 25. November 2010 werde ich an einer Veranstaltung der SOUG SIG Systems Management über Exadata SmartScan sprechen. Die Veranstaltung findet in Thalwil, in der Nähe von Zürich statt.

Interessierte können sich unter http://www.soug.ch/events/sig-101125-agenda.html für den Anlass anmelden.

Written by danirey

November 11, 2010 at 12:46

The gains and pains of dbms_datapump. Part III

with 3 comments

Imran Khan, a visitor of my blog, asked me how to export a schema using dbms_datapump. While preparing a little example for him, I once again came around an ugly detail in dbms_datapump, which I didn’t mention in my earlier posts.

You might receive the following error message when you execute your code the second time.

ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at line 42

If it happens the second time you execute your code, then it might be your DBMS_DATAPUMP.ADD_FILE statement which fails.

But what’s the reason? dbms_datapump fails to add the file, because it already exists. Unfortunately the error message “ORA-39001: invalid argument value” doesn’t help much to find the problem. It makes sense, that Oracle doesn’t just overwrite an existing file, but they could at least come up with an error message which leads towards the problem.

So in the following code snippet it would be line 3 causing the problem. To solve it simply delete the file which should be used as destination for the export. In my case it’s the file hr.dmp in the DATA_PUMP_DIR.


If you wrote a logfile and did this before adding the destination file, it’s quite easy to find the problem. The logfile will contain the following lines.

ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/u00/app/oracle/admin/DAR112/dmp/hr.dmp"
ORA-27038: created file already exists
Additional information: 1

To write a logfile, you can add the following lines in your script, directly after the job creation.

SELECT 'datapump_'||to_char(SYSDATE,'YYYYMMDD')||'.log'
  INTO logfileName
  FROM dual;
  handle =>     handle,
  filename =>   logfileName,
  directory =>  'DATA_PUMP_DIR',
  filetype =>   dbms_datapump.ku$_file_type_log_file);

If your interested in more details about dbms_datapump, have a look at my other posts about dbms_datapump.

Written by danirey

June 28, 2010 at 16:57

OCOB – OPITZ CONSULTING Oracle Base – in English

leave a comment »

I’m glad to inform that OCOB – OPITZ CONSULTING Oracle Base is now available!

[Edit: June 24th 2011]
Unfortunately OPTIZ CONSULTING Schweiz GmbH had to declare bankruptcy a few months ago. The German Holding and it’s GmbHs still exist. But before you consider to take OCOB as your new standard tool for database administration, you should ask OPITZ whether they are able and want to keep up with support and improvement of OCOB.

Trivadis has a good and similar product which is available through http://www.trivadis.com/en/produkte/datenbank-tools/tvd-basenvtm.html

Unfortunately I’m not aware of existing open source projects in this area. If you know a good one please tell me about it, I’ll then post a link in this post.

OCOB is a management framework for Oracle server products. It’s mainly used for easy handling of Oracle Databases. It can easily show the current status of your database, listener, asm or application server.

oracle@ltdar2:/u00/app/oracle/product/11.1.0/dbs/ [DAR112] up
Status of Oracle Products on ltdar2.opitz-consulting.int

DB-Inst.  DB2            : down    (/u00/app/oracle/product/10.2.0)
DB-Inst.  DAR102B        : down    (/u00/app/oracle/product/10.2.0_2)
DB-Inst.  DAR920         : down    (/u00/app/oracle/product/9.2.0)
DB-Inst.  DAR102         : open    (/u00/app/oracle/product/10.2.0)
DB-Inst.  DB1            : down    (/u00/app/oracle/product/10.2.0)
DB-Inst.  DAR11BETA      : down    (/u00/app/oracle/product/11.1.0_beta2)
DB-Inst.  DAR111         : down    (/u00/app/oracle/product/11.1.0)
DB-Inst.  DAR112         : up      (/u00/app/oracle/product/11.2.0)

Lsnr                     : down    
oracle@ltdar2:/u00/app/oracle/product/11.1.0/dbs/ [DAR112] 

It can be used on Unix, Linux and Windows.

You can download a copy, free of charge, at http://www.opitz-consulting.com/?id=810

But why do I bore you with this advertising post? First of all I’m a big fan of it and I was personally involved in the last development steps.

Written by danirey

May 10, 2010 at 12:20

Posted in English, Oracle, Tipps&Tricks

Tagged with , , ,


leave a comment »

Dies ist nur ein kurzer Hinweis das OCOB – OPITZ CONSULTING Oracle Base nun erhältlich ist!

[Edit: 24. Juni 2011]
Leider ist die OPITZ CONSULTING Schweiz GmbH inzwischen Konkurs gegangen, die Deutsche Holding und ihre GmbHs gibt es aber immer noch. OCOB ist dort weiterhin erhältlich und bestimmt auch ein gutes Produkt. Bevor aber jemand auf OCOB umsattelt, sollte er/sie gut abklären OPITZ weiterhin den Support und die Wartung bereitstellen kann und will.

OCOB wurde fast ausschliesslich in der Schweiz entwickelt. Vom ursprünglichen Entwikckler Team arbeitet niemand mehr bei Opitz.

Mit TVD-BasEnv ist ein gutes und vergleichbares Produkt bei der Firma Trivadis erhältlich http://www.trivadis.com/de/produkte/datenbank-tools/tvd-basenvtm.html. Über Open Source Projekte in diesem Bereich bin ich leider nicht im Bild, wer aber eines kennt kann mir das gerne mitteilen. Ich werde das Projekt dann hier verlinken.

OCOB kann als Management Framework für Oracle Server Produkte betrachtet werden. Es eignet sich im speziellen für die bequeme Administration von Oracle Datenbanken. So kann zum Beispiel auf einfachste Art der Status von Datenbank, Listener, ASM und Application Server angezeigt werden.

oracle@ltdar2:/u00/app/oracle/product/11.1.0/dbs/ [DAR112] up
Status of Oracle Products on ltdar2.opitz-consulting.int

DB-Inst.  DB2            : down    (/u00/app/oracle/product/10.2.0)
DB-Inst.  DAR102B        : down    (/u00/app/oracle/product/10.2.0_2)
DB-Inst.  DAR920         : down    (/u00/app/oracle/product/9.2.0)
DB-Inst.  DAR102         : open    (/u00/app/oracle/product/10.2.0)
DB-Inst.  DB1            : down    (/u00/app/oracle/product/10.2.0)
DB-Inst.  DAR11BETA      : down    (/u00/app/oracle/product/11.1.0_beta2)
DB-Inst.  DAR111         : down    (/u00/app/oracle/product/11.1.0)
DB-Inst.  DAR112         : up      (/u00/app/oracle/product/11.2.0)

Lsnr                     : down
oracle@ltdar2:/u00/app/oracle/product/11.1.0/dbs/ [DAR112]

OCOB kann unter Unix, Linux und Windows installiert werden. Es kommt mit einem intelligenten Installer, welcher den grössten Teil der Konfiguration selbst übernimmt.

Sie können sich ihre gratis Kopie unter http://www.opitz-consulting.com/?id=810 herunterladen.

Und wieso langweile ich Sie mit diesem Werbepost? Zum einen bin ich ein Fan von OCOB zum anderen war ich in den letzten Entwicklungsschritten persönlich involviert.

Written by danirey

May 10, 2010 at 12:19

Posted in Deutsch, Oracle, Tipps&Tricks

Tagged with , , ,

use menus in Vim without having a GUI

leave a comment »

While applying changes to sqplus.vim I was looking for the option to execute code only if Vim is running in a GUI ( if has("gui_running") ) While searching for this information I changed my mind, because I found out that you can use the menu also when running Vim on command line.

You just have to add the following lines to you .vimrc

:source $VIMRUNTIME/menu.vim
:set wildmenu
:set cpo-=<
:set wcm=<C-Z>

To use the newly added feature write :emenu plus a SPACE in command mode and press TAB. A menu appears above the command bar. You can change between the menu points with TAB and choose one with ENTER. After this you are in the sub menu, again you can select with TAB and ENTER.

You can also write a part of a menu point and complete it with tab. Or you type :emenu <C-D> (<C-D> means Ctrl+D for those not used this notation) which shows you a list of all possible menu entries.

The cool thing about wildmenu is, it brings you command completion for more than just menus. For example you could use it to show a list of available help entries. To try this type :h spl <C-D> and you will see a list of available help documents about split, vsplit, isplit, display …

Written by danirey

October 27, 2009 at 10:05

improve your working speed… with old technology

leave a comment »

You can massively improve your working speed by using Vim instead of other editors. Full stop.

I think most people will agree, that using Vim is the fastest way to edit files, after they have learned to use it (I’ve decided to write Vim instead of Vi here, because of the visual mode). But what’s about the rest of my working process?

Usually you have to execute and test the scripts you’re writing. Most IDEs are capable of doing this, what about Vim? Although Vim is not really an IDE by itself, it’s quite easy to add functionalities. This is usually done by adding plugins to your Vim. One of the plugins which massively improved my working speed is sqlplus.vim from Jamis Buck.

You can use it to directly execute parts of the script your writing at the moment, or just execute a query on the database without even adding it to your script. You can find the full description on www.vim.org.

While I was writing my last post, I was rethinking my whole working process and decided that some stuff was still missing in sqlplus.vim. And that’s the point where another cool feature of Vim kicks in. All plugins are written in a script language and you can simply edit them yourself, just remember to contact the original author afterwards, for that everybody can profit from your improvements.

After a short discussion with Jamis, I uploaded the newest version of the plugin to sqlplus.vim. Unfortunately I had to fork the script, because www.vim.org doesn’t allow you to change the maintainer of a already published plugin. If you have any questions, ideas or problems with sqlplus.vim feel free to contact me.

Written by danirey

October 24, 2009 at 16:01

Everybody writes about new stuff from OpenWorld… I write about old fashioned VIM

leave a comment »

Besides trying to beat Kevin Closson in the length of my blog post titles, I try to write about stuff which makes life as a DBA easier and more fun. This time I write about old fashioned VIM (maybe a part of the tricks works also with VI, but I had none handy to test. Sorry, I’m one of those “oh so modern” Linux guys with VIM behind their VI command).

Let’s have a look at the .vimrc file which is in your home directory. In this file you can configure the behavior of your VIM installation (Windows users can find _vimrc in the folder of the GVIM Installation). It is mainly used to define some stuff like:

  • colorscheme
  • font
  • search behavior
  • tabstop width
  • This sounds very simple, but why do I use a .vimrc with more than 200 lines? Because you can even code in a simple scripting language and call this scripts with shortcuts from within VIM.

    One very simple example of this, is a little alias which I use to execute the file, which I’m editing at the moment, with sqlplus. The 2 code lines look like the following.

    let con_str='"/ as sysdba"'
    map  :execute ":w"  :execute "!sqlplus " . con_str . " @%"

    When I now press F2 in the Command Mode, the current File gets executed in sqlplus as sysdba. You can, of course, change the value of the con_str variable to execute it as a different user. I think /nolog might also be a useful default value, especially if you use it directly on your database server. To change it within VIM use the following command

    :let con_str='/nolog'

    For the GUI guys, there is a possibility to add a menu entry for this. Just add the following two lines to your .vimrc

    :menu Oracle.SQLPLUS<Tab>F2 :execute ":w" <CR> :execute "!sqlplus " . con_str . " @%"<CR>
    :menu Oracle.SQLPLUSW :execute ":w" <CR> :execute "!sqlplusw " . con_str . " @%"<CR>

    This will create a new menu entry called Oracle with buttons for SQL*Plus and SQL*PlusW.

    If you like this simple tricks, you will love the sqlplus plugin from Jamis Buck. http://www.vim.org/scripts/script.php?script_id=97 It’s a powerful and easy to use plugin for your vim.

    While working on this post, I started using the sqlplus plugin again and I ended up adding a functionality which I missed for a long time and making some minor changes. At the moment I’m contacting Jamis to get his opinion about my changes. As soon as we agreed on them, the new version will be published on http://www.vim.org. I will then write about the sqlplus plugin.

    Written by danirey

    October 20, 2009 at 06:26

    Posted in English, Oracle, Tipps&Tricks, VIM

    Tagged with , , , , , ,