TheBonsai's Blog

You know the need to sometimes manually switch logfiles with

ALTER SYSTEM SWITCH LOGFILE;

In a RAC with some more nodes, it can be quite disturbing to do that on all instances.

When you inspect the protocol files of the right services, you will find another, undocumented method:

ALTER SYSTEM SWITCH ALL LOGFILE;

This works at least on 10.2.0.5. Since it’s undocumented, I can’t tell you under which circumstances this command works or not, but it looks like always possible. Please find out yourself and let me know.

Recently I hacked a on-demand-export for a user. After his (small) data loading, an export is triggered to have his data in a backupable and tranmsportable file. The total amount of all tables was about 7MB only.

The export routines were done with datapump export. I watched a manual run and…

• datapump started and connected to a database server process: 3 seconds
• datapump estimated and analyzed the data to export: 36 minutes
• datapump exported the relevant tables: 4 seconds

Do you see what I see? Yes? Right, datapump played with itself. Over 36 minutes of runtime, where 99% of the time not one bit of data was moved. Reproducable 100%. A datapump TRACE=480300 generated trace set revealed a huge delay in the worker process when calling DBMS_METADATA.FETCH_XML_CLOB (exactly my 36 minutes).

The solution was a patch for bug

• 7710931: DATAPUMP EXPORT IS EXTREMELY SLOW WHEN EXTRACTING SCHEMA

Now datapump does what it’s designed for: Pumping data without playing with itself!

Erlebnis des Tages:

Dienstleister bittet die Datenbankadministration um die Beseitigung des folgenden (Zitat) Datenbankfehlers: ORA-00054: resource busy and acquire with NOWAIT specified

…der manchmal bei einem SELECT FOR UPDATE NOWAIT kommt.

Ist meine Datenbank kaputt?