Migrating and/or Upgrading Oracle Databases

Over the years I have been involved in the migration and/or upgrade of many Oracle databases.

From these experiences I have built up a checklist of questions and considerations, which I feel should be properly addressed and answered before the migration or upgrade of any Oracle database. Some of these items are on the list from the bitter experience of having overlooked them.

I feel more of the items to be considered arise from the moving (i.e. re-hosting) of a database, rather than the upgrading of it. So, even if we were moving a database to exactly the same Oracle version on a new hardware platform, we would still have to address most of the items on this list.

I hope it’s useful to you and I will update it in future, every time I’m bitten by something new which I have overlooked.

Please point out in the comments anything you feel should be included.

Freeze of Oracle dbca and “startup nomount” running on Linux on VirtualBox 5

I encountered a problem with database creation freezing at a very early stage, with the process spinning on 100% CPU while not seeming to be doing anything. This happened both when trying to start up the Oracle database configuration assistant (dbca), where it froze on the splash screen, and when using the “startup nomount” command in sqlplus.

My setup is:

  • Oracle 11.2 (both 11.2.0.3 and 11.2.0.4);
  • Oracle Linux 6.7
  • VirtualBox 5.0.10, installed on Windows 7.

The process occupying 100% CPU was similar to that below:

oracleDBUA5305324 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

I unsuccessfully tried a few things to get to the bottom of this or eliminate possible causes: tracing with strace, switching between ASMM (sga_target) and AMM (memory_target), switching between normal pages and huge pages.

My colleague Frits Hoogland offered to help and he figured it out.

I logged into sqlplus as sysdba, which created the following database session:

$ ps -ef
oracle 3638 3637  0 17:42  oracleMAGPIE (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

We traced this session with the ‘perf top’ command, of which I was previously unaware. perf is a great profiling tool, included in the Linux 2.6 kernel. ‘perf top’ gives a dynamic view of the performance of a process, similar, as the name suggests, to the top command. We passed it the process id of the database session:

$ perf top -p 3638

I issued the ‘startup nomount’ command in the sqlplus session. The ‘perf top’ output, after a few seconds, settled on showing the following:

perf_top_skgvm

This shows that the process was looping executing skgvm_cpuid. Frits recognised this meant it was stuck trying to identify the CPU type.

This led us to check the CPU’s definition in VirtualBox.

The problem was caused by a new performance enhancement feature in VirtualBox 5: ‘paravirtualization’. The ‘Paravirtualization Interface’ was set to ‘Default’:

paravirtualization

Shutting down the VM and changing this to ‘Minimal’ resolved the problem.

I experimented further and found that setting it to ‘None’ and ‘Legacy’ also resolved the problem. Setting it to ‘KVM’ caused the same freeze to occur on trying to identify the CPU type.  On a Linux VM I imagine setting this to ‘Default’ and ‘KVM’ is exactly the same thing. The other setting available, ‘Hyper-V’, is intended for Windows VMs.

It looks like this, hopefully great, new feature in Virtualbox 5 is not completely bug-free yet.