Migrating Jobs in Enterprise Manager 12c- not for the faint of heart… :)

These are instructions that allowed me to migrate over a large library of EM12c jobs from an existing, split EM console, (Linux) and OMS Repository(Windows) to a single server environment without requiring me to recreate them new.

Disclaimer**
The migration is a multi-step process that was performed on:
A NEW EM12c 12.0.1.0 with the latest bundle patch, OS 64-bit Linux
11.2.0.3.0 repository
a FULL backup of the OMS repository before performing.

A full documentation of your EM jobs that are up to date!

The source environment the EM jobs were datapumped from:
11.2.0.3.0 OMS repository on Windows 2008
EM12c, without the bundle patch on Linux, 64 bit.

I have not attempted this on any other platform or environment and I strongly recommend that you start with a fresh OMS repository, new EM12c with the bundle patch and secure backups if you do not want to have to go back and recreate repositories or re-install!

If you do not have a lot of jobs, then just recreate them, but this challenge came about because I had 52 complex EM jobs that recreating would have impacted the production environment and I had to have a way to migrate them with as little downtime as possible.

Challenges ahead
Jobs are identified in the internal OMS tables by TARGET_GUID. As this is a brand new EM and no targets exist or will be migrated as part of the process till the final steps, invalidations and constraint issues should be expected.

High Level Steps
____________________________

Create your datapump dir you wish to use on both machines, if you don’t have one.
Run the export using the par file provided from the source EM12c
Transfer the dump file over to the destination server and into the datapump dir.
Shut down the agent and the OMS on the destination server’s EM
Log into the destination EM12c and run the disable constraints script.
Run the import script using the par file provided. DIsregard the metadata errors, the data should be appended…
Bring up the OMS and the agent.
Log into the EM on the destination server and view the job library, verifying all jobs are present

During outage scenario- Shut down the agents on each of the targets and remove the agent homes.

Deploy the agents from the new EM12c server to the targets.

Edit each job, removing the old db listed in the “General” tab and add the correct database, (same name, but the Target GUID has changed underneath in the new EM when the agent was deployed…)

Save to Library.

Submit each job to the system and you have completed the migration.

Detailed Steps———————

Backup the destination database so you have a recovery point if things go wrong…

————————————————————

First challenge- what tables are involved in the Enterprise Manager jobs?

select table_name from dba_Tables
 where owner='SYSMAN'
 and table_name like '%MGMT%JOB%'
 and table_name not like '%BCN%'
 and table_name not like '%GENSVC%'
 ;

Use the results from this query to build a parameter file for a datapump export:
Export Datapump Parfile

On both databases ensure you have a directory to work from:

SQL> !mkdir <dir>/backup/exp

SQL> create directory sysman_dir as '<dir>/exp';

Directory created.

SQL> grant read on directory sysman_dir to sysman;

Grant succeeded.

SQL> grant write on directory sysman_dir to sysman;

Grant succeeded.

Export out the sysman jobs using the par file as the sysman user. The MGMT_JOB table has fine-grain control on it,
but as we only want the sysman jobs brought over, we do not want to write over the metadata that exists in this table, only append to it.

FTP the file over to the new (destination) EM server.

On  the new server, bring down the local agent:

cd $AGENT_HOME
 cd bin
 ./emctl stop agent
 Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
 Stopping agent ..... stopped.

Bring down the console and the OMS:

./emctl stop oms
 Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.1.0
 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
 Stopping WebTier...
 WebTier Successfully Stopped
 Stopping Oracle Management Server...
 Oracle Management Server Successfully Stopped
 Oracle Management Server is Down

Disable the constraints using the script found here:
Disable Constraints
Import in the data to the database using the following parameter file for datapump:
Import Parfile

DIRECTORY=SYSMAN_DIR
TABLE_EXISTS_ACTION=APPEND
DUMPFILE=SYSMAN_DIR:EXPDP_SYSMAN_JOBS.DMP
LOGFILE=SYSMAN_DIR:impdp_sysman_jobs.log
TRANSFORM=oid:n
STATUS=60
TABLES=
( SYSMAN.MGMT_JOB_OUTPUT
, SYSMAN.MGMT_JOB_EXEC_CRED_INFO
, SYSMAN.MGMT_JOB_EXT_TARGETS
, SYSMAN.MGMT_JOB_TARGET
, SYSMAN.MGMT_JOB_LARGE_PARAMS
, SYSMAN.MGMT_NESTED_JOB_TARGETS
, SYSMAN.MGMT_PAF_JOBTYPES
, SYSMAN.MGMT_PAF_JOBTYPE_PARAMS
, SYSMAN.MGMT_USER_JOBS
, SYSMAN.MGMT_JOB_STEP_PARAMS
, SYSMAN.MGMT_JOB_PARAM_SOURCE
, SYSMAN.MGMT_JOB_USER_PARAMS
, SYSMAN.MGMT_JOB_SQL_PARAMS
, SYSMAN.MGMT_JOB_CRED_PARAMS
, SYSMAN.MGMT_JOB_SUBST_PARAMS
, SYSMAN.MGMT_JOB_PROP_PARAMS
…. yeah, the same tables as in the export file!!

Simply execute the following command with your proper information:

impdp sysman@<dbname> parfile=/<dir>/impdb_sysman_jobs.par

If you do not use the parameter in your par file:

TRANSFORM=oid:n

You’ll receive the following upon import:

ORA-31693: Table data object "SYSMAN"."MGMT_JOB_PARAMETER" failed
to load/unload and is being skipped due to error:
 ORA-29913: error in executing ODCIEXTTABLEOPEN callout
 ORA-29400: data cartridge error
 ORA-39779: type "SYSMAN"."MGMT_JOB_VECTOR_PARAMS" not found or
conversion to latest version is not possible

due to the following types:
SYSMAN.MGMT_JOB_VECTOR_PARAMS
SYSMAN.MGMT_JOB_INT_ARRAY
SYSMAN.MGMT_SHORT_STRING_ARRAY

The output from your datapump import should look like the following:

Worker 1 Status:
 Process Name: DW00
 State: EXECUTING
 Object Schema: SYSMAN
 Object Name: MGMT_JOB_HISTORY
 Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
 Completed Objects: 2
 Total Objects: 42
 Completed Bytes: 8,981,768
 Percent Done: 100
 Worker Parallelism: 1
 . . imported "SYSMAN"."MGMT_JOB_PARAMETER" 19.51 MB 254627 rows

Once the import has completed

-Re-enable constraints-  19 will remain invalid due to the Target GUID differences from the old OMS repository data and the new one, (the script is down below!)

-Start OMS and the agent.

-Log into the console and verify the jobs are showing in the library:

Once you have your official window to migrate over, you will need to perform the following:

-Shutdown the agents on the servers that are migrating over.

-De-install the Agent Home:

-Remove the agent home:

-Deploy the agent from the new EM12c.

Once the agent is deployed and the targets are uploading to the new OMS, edit each job to update the Target GUID discrepency from the migration.

• From the summary page, click on ENTERPRISE–> JOB–> LIBRARY

• For each job, click in the radio button, click on EDIT.

• On the general tab, you will need to remove the existing host or database, then add the new one that resides in the OMS repository.  You will view it as removing and re-adding the same target, but the Target GUID is different and so the OMS will react as if a full edit to new host or database has been made.

• Click on “Save to Library”

• Once you’ve done this for each job, you will return to the main screen and you can then click the “Submit” button for the job.

You can verify submissions by looking at the activity view, filtering by “scheduled”.

Once all jobs have been updated with the correct Target GUIDS, then you can compile the last 19 constraints.
Enable Constraints

Did you run into a snag, need to start over?

************************
Use your backup…as this is the OMS you can’t use EM to restore, soooo…

Ensure all the following is shut down

-EM agent.
-The OMS Repository
-The DB Instance, ensuring you pulled your backup tag before you shut it down!

Connect to the new EM throgh RMAN, (RMAN target/)
 startup force mount;
 restore database from tag "";
 restore database from tag "BACKUP_EMREP_000_050912095636";
 recover database;

alter database open resetlogs;

Or go back to a point in time:
run {
 sql "alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''";
 set until time '09-may-2012 10:38:00';
 restore database;
 recover database until time '09-may-2012 12:00:00';
 alter database open resetlogs;}

startup oms
startup agent

Remember to recreate your datapump directory you were using, if not a standard one!

OK, back where we started before everything got out of hand! 🙂
Sometimes the Repository db will not leave a pending state in the EM console after the restore.  If this happens, remove the OMS db from the EM console and simply re-add.  It will then update everything and all targets will again upload correctly.