TheBonsai's Blog

About the days and nights of TheBonsai

Archive for the 'Oracle' Category

Zero downtime storage migration operations

January 27th, 2011 by TheBonsai

For some systems a downtime is complicated to organize, or bad at all. We have such a system, a RAC database.

In the past, all storage migration operations there had two problems that lead to downtime:

  • CRS votedisk migration at best only with offline CRS
  • kick old devices from the device management in the operating system

In December I migrated the system to something more modern

  • SLES11
  • Oracle Grid Infrastructure (CRS/ASM) 11.2
  • Oracle Database 10.2.0.5 (yes, still quite old, but it has to be a 10.2 for now)

The use of a modern Linux kernel (especially a modern SCSI stack) and 11gR2 infrastructure fixed all my trouble from the past.

Votedisk

With 11gR2 Grid Infrastructure, the clusterware manages its vital files (VD, OCR) using an ASM instance. A migration of the ASM diskgroups holding these files is now as easy as a migration of a normal diskgroup. ASM takes care of moving the votedisks and the OCR and collaborates with CRS here.

Linux devices

The new Linux kernels with a finally sane SCSI stack and native multipathing helps with the second problem. It’s not a problem anymore to remove old device references from the stack:

  • deconfigure the devices from multipathd (not needed technically, since multipathd itself holds no device references)
  • remove the device references from the Linux device mapper
    # see /dev/mapper/* for the name
    # an alternative and probably more safe is: multipath -F (flush all unused maps)
    dmsetup remove mpathX
  • remove the LUNs from the SCSI stack
    # X:X:X:X LUN number
    echo 1 > /sys/bus/scsi/X:X:X:X/delete

After that, you can safely edit the FC zone – no errors should occur in any logs.

Category: english, Oracle, Work | No Comments »

Make all instances in a RAC environment switching the logfile with one single command

December 2nd, 2010 by TheBonsai

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.

Category: english, Oracle | 3 Comments »

Slow datapumping on 10.2.0.4

August 10th, 2010 by TheBonsai

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!

Category: english, Oracle | No Comments »