Note: Scripts contained within work for 11g OEM repository databases.
How many times has Oracle requested or suggested that you should be running the latest version of Oracle Enterprise Manager? How many times has your organization just wanted to upgrade versions to be on the latest of Oracle Enterprise Manager? The answer to these questions are really the same…. way to often or not often enough (depends on your view of the world)!
For the most part, many of the things in Oracle Enterprise Manager (OEM) can be exported and imported between environments (check compatibilities prior migrations). There are only a few functionalities of OEM that cannot be exported. This would be the OEM job library and associated jobs! Now the question is, if I have to migrate from my current version of OEM to a newer version, how do I do this without loosing my jobs? The standard answer from Oracle would be to upgrade your OEM environment; which is the correct response. However, when looking at the upgrade paths for OEM, there is a chance of losing visibility of the enterprise during the upgrade. For many of the customers I work with this is not a good thing or an option. What other solutions are there if I just want to install a new OEM environment and then move my jobs?
At this point, I have posed a few questions dealing with moving jobs between OEM environments. Since export/import is off the table, what options do we have to move jobs to a new OEM environment? After discussing this with a few friends and looking through a lot of MOS notes, I can honestly say the best way is to manually move the jobs (until Oracle decides implement export/import for jobs).
I have come to find that there are two ways that we can migrate jobs between OEMs. The first is documented at DBAKevlar’s blog (www.dbakevlar.com). After talking with DBAKevlar, her approach works but is not/will not be supported by Oracle. The second way is documented here. If you have a lot of jobs, this may become a time consuming project for you or your department.
The first things we need to identify are the tables that relate to OEM jobs. The below script will help you in identifying tables used for jobs.
select count(*) from dba_tables
where owner = ‘SYSMAN’
and table_name like upper(‘%mgmt%job%’)
and table_name not like upper(‘%bcn%’)
and table_name not like upper(‘%gensvc%’);
Next I wanted to find how many of these tables had data associated with them. If the table had no data, I didn’t want them listed. This limited the pool of tables I had to look at.
declare
v_owner varchar2(30);
v_table varchar2(30);
countval pls_integer;
tabcount pls_integer;
v_limit integer := 0;
cursor tab_count_cur is
select owner, table_name from dba_tables
where owner = ‘SYSMAN’
and table_name like upper(‘%mgmt%job%’)
and table_name not like upper(‘%bcn%’)
and table_name not like upper(‘%gensvc%’)
order by table_name asc;
begin
for i in tab_count_cur loop
execute immediate ‘select count(1) from ‘ || i.owner||’.'||i.table_name into countval;
if countval > v_limit then
dbms_output.put_line(i.owner||’.'||i.table_name ||’ has a count of ‘||countval);
end if;
end loop;
end;
/
After identifying the tables for jobs, it looks to be a lot of tables to deal with. After consulted the Oracle documentation; Oracle provides views that reference these tables for easier identification of jobs. Next, I wanted to find out what views had the data I needed. I was looking to see if there was any with identical counts (identical counts typically indicate these items can go together). The below script can be used to get the views that has information associated with jobs.
declare
countval pls_integer;
v_limit integer := 0;
cursor tab_count_cur is
select owner, view_name, read_only
from dba_views
where owner = ‘SYSMAN’
and view_name like upper(‘%mgmt%job%’)
order by view_name asc;
begin
for i in tab_count_cur loop
execute immediate ‘select count(1) from ‘ || i.owner||’.'||i.view_name into countval;
if countval > v_limit then
dbms_output.put_line(i.owner||’.'||i.view_name ||’ has a count of ‘||countval);
end if; end loop;
end;
/
Notice, the list of views is much smaller than the list of tables. Making identifying jobs a bit easier. Lets start getting the information we need to move jobs.
In order to move jobs, we need to identify the general information for the jobs. This information can be found by joining the MGMT$JOBS and MGMT$JOB_TARGETS. Depending on the number of jobs you have, this will either be a short or long list of jobs.
select t1.job_id, substr(nvl(t2.target_name, ‘OEM Targetless’),0,20), t1.job_name, t1.job_type, t1.job_owner, substr(t1.job_description,0,30) description, t1.target_type, t1.timezone_type, t1.timezone_region, t1.schedule_type, t1.is_library, t1.start_time
from sysman.mgmt$jobs t1, sysman.mgmt$job_targets t2
where t1.job_id = t2.job_id
and t1.job_name = t2.job_name
and t1.target_type is not null;
In this case, we are looking for backup jobs. The job type identified above is “Backup”. In order to find the internal id for backup jobs, we will need to query the MGMT_JOB_TYPE_INFO table.The key thing to look for here is the JOB_TYPE from the MGMT$JOBS view. This will tell you the type of job it is. Also, you will need this to find the internal id for the job in the next SQL statement.
select job_type_id, minor_version2, last_modified_date
from mgmt_job_type_info
where job_type = ‘Backup’;
Now that we have the internal id for the backup type jobs, we can find the parameters that are required for this job type. This query will not give you the current values of the parameters just the name of the parameters associated with the internal id.
select parameter_name
from mgmt_job_type_param_dsply_info
where job_type_id in (
select job_type_id
from mgmt_job_type_info
where job_type = ‘Backup’
)
order by parameter_name asc;
Almost done. With the job_name, internal id, and the parameters; we can now identify the values of the parameters for the jobs we want to migrate. This will give us the information needed to ensure that we have all the correct values for the migration. The important thing to remember at this point is that PARAMETER_TYPE will tell us where we need to look for the information. The below SQL will help you in identifying what needs to be gathered.
select job_id, parameter_name,
case parameter_type
when 0 then ‘Vector’
when 1 then ‘Scalar’
when 2 then ‘Large’
end as parameter_type,
scalar_value, vector_value, large_value
from mgmt_job_parameter
where job_id in ( select t1.job_id from sysman.mgmt$jobs t1 where t1.target_type is not null )
order by job_id asc;
Depending on the parameters and values of the parameters, you will have to look at a few different areas. For the purpose of this post, we are focusing on the parameter type of large. The PARAM_ID located in the LARGE_VALUE column will point us to the SQL/RMAN statement that was placed in the job when it was created. The below query will pull the PARAM_VALUE, based on the LARGE_VALUE from previously SQL statement, from the MGMT_JOB_LARGE_PARAMS table.
select param_value
from mgmt_job_large_params
where param_id = ‘<values from LARGE_VALUE>’;
Once you have all the information, which has been outlined in this document, you should be able to reproduce your jobs from in the new OEM environment.
Moving jobs from one OEM environment to another is not as easy as I would have hoped. I truly hope that Oracle listens to the end users and eventually get around to producing an export/import process for jobs. Is an XML export/import process that difficult for jobs?
