This week I migrated our EM12c repository database to a new server as part of its promotion to production status. Just to make it a little more exciting, the migration also involved an in-flight upgrade from 11.2.0.3 to 11.2.0.4. Much of this post is directly inspired by Martin Bach's post on the same subject. I ran into a few other snags that weren't mentioned so I thought it would be worthwhile to document the experience here for your benefit.

I'm assuming you have all the software installed (and patched to the latest PSU, right?). Alright then, let's begin!

Stop OMS

We want to make sure there are no more changes coming, and nothing needs to access the repository database, so be sure to stop all OMS instances:

$ emctl stop oms -all

Backup PFILE

We need to get the pfile for the current repo and copy it into place on new host:

SQL> create pfile='/mnt/emrepo_backup/initemrepo.ora' from spfile;

I use /mnt/emrepo_backup here because that is the directory that I'll be backing the database up to and copying to the new host after. If you create your pfile somewhere else, be sure to copy it to the new host under $ORACLE_HOME/dbs/

Backup Repo Database

Next we backup the repo database. Here's a snippet from my ksh script that I used:


#!/bin/ksh

BACKUPDIR=/mnt/emrepo_backup
LOGFILE=backup_emrepo.log

mkdir -p $BACKUPDIR

rman log=$LOGFILE <<EOF
connect target /
set echo on


run {

        allocate channel c1 device type disk format '$BACKUPDIR/%U';
        allocate channel c2 device type disk format '$BACKUPDIR/%U';
        allocate channel c3 device type disk format '$BACKUPDIR/%U';
        allocate channel c4 device type disk format '$BACKUPDIR/%U';

        backup as compressed backupset database
                include current controlfile
                plus archivelog;
}
EOF

When the backup is finished, review the RMAN log and make note of which backup piece contains the controlfile backup. We'll need to refer to it by name as part of the restore process.

If your backup directory is an NFS mount, then you can simply unmount it from here and mount it to the new server. Otherwise, be sure to copy the files there after the backup is complete, for example:

$ scp -r /mnt/emrepo_backup newhost:/path/to/emrepo_backup

After this, it should be safe to shutdown the old repository database.

$ sqlplus / as sysdba
SQL> shutdown immediate

If you use Oracle Restart:

$ srvctl stop database -d emrepo
$ srvctl disable database -d emrepo

Prepare New Host for Repo DB

Now we need to set things up on the new host for the emrepo DB.

Create oratab Entry

First let's create an entry in /etc/oratab for this DB under the new 11.2.0.4 home. For example:

emrepo:/oracle/app/product/11.2.0.4:N

Edit PFILE and Create SPFILE

Then let's copy that parameter file into place.

$ . oraenv
ORACLE_SID = [oracle] ? emrepo
The Oracle base has been set to /oracle/app
$ cd $ORACLE_HOME/dbs/
$ cp /mnt/emrepo_backup/initemrepo.ora .

Now edit that file and make sure you update the parameters that require updating. In my case, I'm using Oracle Managed Files (OMF) so I set db_create_file_dest and db_create_online_log_dest_1. I also set db_recovery_file_dest for the FRA. I then set the control_files parameter to specify where I want the control file(s) restored to from the backup when I get to that point.

Now, Martin Bach noted in his blog post that he did not have to specify a db_file_name_convert or log_file_name_convert. I was having some difficulty during the restore phase, and added these parameters out of pure speculation. They didn't help the problem, but I left them in for the duration of my process. I only mention this as an FYI if you end up comparing your settings to mine.

Once you have all your parameters set as desired, create the SPFILE:

$ sqlplus / as sysdba
SQL> create spfile from pfile;

Now, let us restore ourselves the database.

Restore Repo DB on New Host

The restore was done largely as part of a ksh script, which I'll reference snippets of here. Let's start by defining some variables:

BACKUPDIR=/mnt/emrepo_backup
DESTDIR=/oracle/app/oradata/data/EMREPO


Restore Controlfile and Mount Database

From the script, we call RMAN to start the instance in nomount mode, restore the controlfile from the specified backuppiece and mount the database:

rman log=$LOGFILE <<EOF
connect target /
set echo on
startup force nomount;
restore controlfile from '$BACKUPDIR/1abcd123_1_1';
alter database mount;
catalog start with '$BACKUPDIR' noprompt;
EOF

We end by cataloging the backup files, as you can see.

Generate SET NEWNAME Script

Here I dip into sqlplus to generate an script for RMAN to call SET NEWNAME for each of the datafiles. Without this, RMAN would try to restore the datafiles to their old paths on the original host. Here I set them for the path that OMF will use:


sqlplus -s /nolog <<EOF
connect / as sysdba
set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.rman
select 'set newname for datafile ' || FILE# || ' to ''' || '$DESTDIR/datafile/' || substr(name,instr(name,'/',-1)+1) || ''';' from v\$datafile;
select 'set newname for tempfile ' || FILE# || ' to ''' || '$DESTDIR/tempfile/' || substr(name,instr(name,'/',-1)+1) || ''';' from v\$tempfile;
spool off
EOF

Restore & Recover Database

Now we're ready to restore the database and perform recovery. Again, we call RMAN and run this:

run {
  allocate channel c1 device type disk;
  allocate channel c2 device type disk;
  allocate channel c3 device type disk;
  allocate channel c4 device type disk;
  @rename_datafiles.rman
  restore database;
  switch datafile all;
  switch tempfile all;
  recover database;
}

At this point we're done with the restore and recovery. Normally I would OPEN RESETLOGS, but remember that we're restoring this to an 11.2.0.4 home, so we still need to UPGRADE the database!


Open and Upgrade Database

First we still call OPEN RESETLOGS, but with the UPGRADE option. This replaces the "STARTUP UPGRADE" command you would find in the manual upgrade instructions.

$ sqlplus / as sysdba
SQL> alter database open resetlogs upgrade;

Now we follow the rest of the manual upgrade instructions, I'll just post the commands here, but you should definitely review the documentation:

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL> spool upgrade.log
SQL> @catupgrd.sql

-- Start database again
SQL> startup;

-- Check status of components, some will be fixed by utlrp.sql
SQL> @utlu112s.sql

-- Rebuild everything
SQL> @catuppst.sql
SQL> @utlrp.sql

-- Confirm everything is OK now
SQL> SELECT count(*) FROM dba_invalid_objects;
SQL> SELECT distinct object_name FROM dba_invalid_objects;
SQL> @utlu112s.sql

The utlu112s.sql should now report all components as VALID. If not, you'll want to refer to the upgrade documentation for troubleshooting.

At the point the database is upgraded and open. Make sure you have a listener running and that the new database is registered. The only thing  left is the tell your OMS servers to look for the repository database in its new location.

Update OMS Repository Settings

First we need to start just the administration server:

$ emctl start oms -admin_only

This is necessary if you used the "-all" option when stopping OMS earlier. If you did not use "-all" then the admin server should still be running.

Now, update the store_repos_details setting in the OMS configuration:

$ emctl config oms -store_repos_details -repos_port 1521 \
  -repos_sid emrepo -repos_host newhost.mydomain.com \
  -repos_user sysman -repos_pwd xxx

Repeat this step for all your OMS servers (emctl should remind you to do so when changing the config). Then on each, completely shutdown and restart OMS:

$ emctl stop oms -all
$ emctl start oms

And that should be it! Don't forget to drop/delete the database from the original server when you're comfortable doing so.