Martins Blog

Trying to explain complex things in simple terms

Move the EM12c repository database

Posted by Martin Bach on October 17, 2011

I have made a little mistake creating a RAC database for the OEM 12c repository-I now need a little more lightweight solution, especially since I’m going to do some fancy failover testing with this cluster soon! An 11.2.0.3 single instance database without ASM, that’s what I’ll have!

Now how to move the repository database? I have to admit I haven’t done this before, so the plan I came up with is:

  1. Shut down the OMS
  2. Create a backup of the database
  3. Transfer the backup to the destination host
  4. Restore database
  5. Update OEM configuration
  6. Start OMS

Sounds simple enough, and it actually was! To add a little fun to it I decided to the use a NFS volume to backup to. My new database host is called oem12db, and it’s running Oracle 11.2.0.3 64bit on Oracle Linux 6.1 with UEK. I created the NFS export using the following entry in /etc/exports:

/u01/oradata/oraback   192.168.99.0/255.255.255.0 (rw,sync)

Simples! After an “exportfs -a” as root the directory was exported. The mount options are a little stricter, I combined a number of MOS notes here to end up with these mount options:

  • rw,
  • bg,
  • hard,
  • nointr,
  • rsize=65536,
  • wsize=65536,
  • tcp,
  • vers=3,
  • timeo=600,
  • actimeo=0

Previously documentation suggested a {r,w}size of only 32k but one MOS document I found for 11.2.0.2 on Linux x86-64 recommends 64k. The command to mount the file system then turns into this lengthy string:

[root@rac11203node1#] mount -t nfs oem12db:/u01/oradata/oraback on /u01/oradata/oraback \
> -o rw,bg,hard,nointr,rsize=65536,wsize=65536,tcp,vers=3,timeo=600,actimeo=0

Note that I mount the directory at the same place in the tree as it’s exported. That way I am saving me the trouble of registering the backup on the destination host. Now take the backup. This is my lab: my OEM repository is in NOARCHIVELOG mode (something that you should NOT do in real life!) so I have to start the database in mount mode before backing it up:

[oracle@rac11203node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Oct 17 14:54:33 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: EMREP (DBID=3987577832, not open)

RMAN> run {
allocate channel c1 device type disk format '/u01/oradata/oraback/%U';
backup as compressed backupset database;
backup current controlfile;
}
2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=248 instance=emrep1 device type=DISK

Starting backup at 17-OCT-11
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATA/emrep/datafile/mgmt_tablespace.269.764589287
input datafile file number=00001 name=+DATA/emrep/datafile/system.256.764546085
input datafile file number=00002 name=+DATA/emrep/datafile/sysaux.257.764546087
input datafile file number=00005 name=+DATA/emrep/datafile/undotbs2.264.764546211
input datafile file number=00008 name=+DATA/emrep/datafile/mgmt_ad4j_ts.270.764589289
input datafile file number=00003 name=+DATA/emrep/datafile/undotbs1.258.764546089
input datafile file number=00006 name=+DATA/emrep/datafile/mgmt_ecm_depot_ts.268.764589285
input datafile file number=00004 name=+DATA/emrep/datafile/users.259.764546089
channel c1: starting piece 1 at 17-OCT-11
channel c1: finished piece 1 at 17-OCT-11
piece handle=/u01/oradata/oraback/02mpb7hn_1_1 tag=TAG20111017T145446 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:35
Finished backup at 17-OCT-11

Starting backup at 17-OCT-11
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 17-OCT-11
channel c1: finished piece 1 at 17-OCT-11
piece handle=/u01/oradata/oraback/03mpb7km_1_1 tag=TAG20111017T145622 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 17-OCT-11

Starting Control File and SPFILE Autobackup at 17-OCT-11
piece handle=/u01/oradata/oraback/c-3987577832-20111017-00 comment=NONE
Finished Control File and SPFILE Autobackup at 17-OCT-11
released channel: c1

RMAN> exit

That takes care of almost everything. Since I’m lazy I also used SQL plus to create a backup of the controlfile as well as a textual representation of the spfile:

SQL> alter database backup controlfile to '/u01/oradata/oraback/controlfile';

Database altered.

SQL> create pfile='/u01/oradata/oraback/pfile' from spfile;

File created

Now after all that NFS setup and backing up across the network I can simply restore the database without having to use scp or similar to transfer the database. The initial work is to create a pfile and password file on oem12db. I’ll spare you the boring detail, the important facts to remember are:

  • Remove references to emrep1 and emrep2-these are RAC specific!
  • Where applicable replace emrep1 with emrep, i.e. for undo_tablespace=UNDOTBS1
  • You don’t need to map online redo log threads to instances-it’s done for you automatically
  • You don’t need to reference the second undo tablespace
  • Change db_create_file_dest to a file system location (I’m not usign ASM on the destination host!)

With that done, and all required directories created, it’s time to start the new database instance in “nomount mode” and fire up an RMAN session. I exported my ORACLE_SID to emrep to denote it’s a single instance database.

[oracle@oem12db dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Oct 17 15:02:09 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: EMREP (not mounted)

RMAN> restore controlfile from '/u01/oradata/oraback/controlfile';

Starting restore at 17-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=246 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/u01/oradata/EMREP/controlfile/o1_mf_79rfcgh8_.ctl
Finished restore at 17-OCT-11

RMAN> alter database mount
2> ;

database mounted
released channel: ORA_DISK_1

RMAN> restore database
2> ;

Starting restore at 17-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=246 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/emrep/datafile/system.256.764546085
channel ORA_DISK_1: restoring datafile 00002 to +DATA/emrep/datafile/sysaux.257.764546087
channel ORA_DISK_1: restoring datafile 00003 to +DATA/emrep/datafile/undotbs1.258.764546089
channel ORA_DISK_1: restoring datafile 00004 to +DATA/emrep/datafile/users.259.764546089
channel ORA_DISK_1: restoring datafile 00005 to +DATA/emrep/datafile/undotbs2.264.764546211
channel ORA_DISK_1: restoring datafile 00006 to +DATA/emrep/datafile/mgmt_ecm_depot_ts.268.764589285
channel ORA_DISK_1: restoring datafile 00007 to +DATA/emrep/datafile/mgmt_tablespace.269.764589287
channel ORA_DISK_1: restoring datafile 00008 to +DATA/emrep/datafile/mgmt_ad4j_ts.270.764589289
channel ORA_DISK_1: reading from backup piece /u01/oradata/oraback/02mpb7hn_1_1
channel ORA_DISK_1: piece handle=/u01/oradata/oraback/02mpb7hn_1_1 tag=TAG20111017T145446
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 17-OCT-11

RMAN> alter database open resetlogs;

database opened

RMAN> exit

This is pretty straight forward and should be familiar to every database administrator:

  1. Restore the controlfile from the backup created earlier
  2. Mount the database
  3. Restore the database
  4. Open the database resetlogs

Did you notice the messages stating “restoring datafile xxx to +DATA”? Oracle is lying to you about it, it’s restoring the db_create_file_dest. This is the beauty about OMF, you tell it in generic terms where you want the file to go to, and don’t bother about the details. Nowhere did I specify db_file_name_convert or log_file_name_convert!

Now it’s time to update the OMS configuration. This operation is vi-less, the configuration is now viewed and modified via emctl (since Grid Control 11.1):

[oracle@oem12oms ~]$ emctl config oms -list_repos_details
Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
Repository Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac11203scan)(PORT=1521)))(LOAD_BALANCE=ON)(CONNECT_DATA=(SERVICE_NAME=emrep)))
Repository User : SYSMAN

The next command updates the configuration. Keep the warning in mind: if you have multiple management servers you need to run this command on each.

[oracle@oem12oms ~]$ emctl config oms -store_repos_details -repos_port 1521 -repos_sid emrep -repos_host oem12db.localdomain -repos_user SYSMAN -repos_pwd secret
Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
Successfully updated datasources and stored repository details in Credential Store.
If there are multiple OMSs in this environment, run this store_repos_details command on all of them.
And finally, restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.

[oracle@oem12oms ~]$ emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
Starting WebTier...
WebTier Successfully Started
Starting Oracle Management Server...
Oracle Management Server Successfully Started
Oracle Management Server is Up
[oracle@oem12oms ~]$

Job done-the OMS has come up and I can free my RAC nodes for other work.

About these ads

One Response to “Move the EM12c repository database”

  1. [...] Thinking about moving the EM12c repository database? Read this post by Martin Bach. [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,076 other followers

%d bloggers like this: