20 Maintaining and Troubleshooting the Management Repository

20.2.1 Management Repository Default Aggregation and Purging Policies

Enterprise Manager aggregates collected metric data by hour and by day to enhance query performance and help minimize the size of the Management Repository. Before the data is aggregated, each data point is stored in a raw metric data table. Once a day, the previous day's raw metric data is rolled up, or aggregated, into a one-hour and a one-day table. These hourly and daily records will have hourly and daily metric data averages, minimums, maximums and standard deviations respectively.

After Enterprise Manager aggregates the data, the data is then considered eligible for purging. A certain period of time must pass for data to actually be purged. This period of time is called the retention time.

The raw data, with the highest insert volume, has the shortest default retention time, which is set to 7 days. As a result, 7 days after it is aggregated into a one-hour record, a raw data point is eligible for purging.

Hourly aggregate metric data records are purged after 31 days. The highest level of aggregation, one day, is kept for 12 months (roughly 365 days).

The default data retention policies are summarized in Table 20-1.

If you have configured and enabled Application Performance Management, Enterprise Manager also gathers, saves, aggregates, and purges response time data. The response time data is purged using policies similar to those used for metric data. The Application Performance Management purging policies are shown in Table 20-2.

20.2.2 Management Repository Default Aggregation and Purging Policies for Other Management Data

Besides the metric data and Application Performance Monitoring data, other types of Enterprise Manager data accumulates over time in the Management Repository.

For example, the last availability record for a target will also remain in the Management Repository indefinitely, so the last known state of a target is preserved.

20.2.3 Modifying the Default Aggregation and Purging Policies

The Enterprise Manager default aggregation and purging policies were designed to provide the most available data for analysis while still providing the best performance and least disk-space requirements for the Management Repository. As a result, you should not modify these policies to improve performance or increase your available disk space.

However, if you plan to extract or review the raw or aggregated data using data analysis tools other than Enterprise Manager, you may want to increase the amount of raw or aggregated data available in the Management Repository. You can accomplish this by increasing the retention times for the raw or aggregated data.

A PL/SQL API has been provided to modify the default retention time for the core metric data tables in the Enterprise Manager repository. Table 20-3 shows the default number of partitions retained for each of the three tables and the size of the partitions for each table. The API will allow you to change the number of partitions retained only.

To modify the retention period for any of the above tables, execute the following command:

SQL> execute gc_interval_partition_mgr.set_retention('SYSMAN', <table name>, <number of partitions to retain>);

Replace the <table name> by name of table as listed above. The API will allow you to change the number of partitions retained only.

For example, to modify the default retention time for the table EM_METRIC_VALUES from 7 partitions to 14 partitions, follow these steps:

1. Use SQL*Plus to connect to the repository database as the SYSMAN user.

2. Check the current value of the retention periods:

   SQL> select table_name, partitions_retained
   from em_int_partitioned_tables
   where table_name in ('EM_METRIC_VALUES','EM_METRIC_VALUES_HOURLY','EM_METRIC_VALUES_DAILY');
    
   TABLE_NAME                 PARTITIONS_RETAINED
   -------------------------  -------------------
   EM_METRIC_VALUES                             7
   EM_METRIC_VALUES_HOURLY                     32
   EM_METRIC_VALUES_DAILY                      12
   

3. To modify the default retention time for the table EM_METRIC_VALUES from 7 partitions to 14, execute the following command:

   SQL> execute gc_interval_partition_mgr.set_retention('SYSMAN', 'EM_METRIC_VALUES', 14);

4. Verify that the retention period has been modified:

   SQL> select table_name, partitions_retained
   from em_int_partitioned_tables
   where table_name in ('EM_METRIC_VALUES','EM_METRIC_VALUES_HOURLY','EM_METRIC_VALUES_DAILY');
    
   TABLE_NAME                PARTITIONS_RETAINED
   ------------------------- -------------------
   EM_METRIC_VALUES                           14
   EM_METRIC_VALUES_HOURLY                    32
   EM_METRIC_VALUES_DAILY                     12
   

20.2.4 Modifying Data Retention Policies When Targets Are Deleted

By default, when you delete a target from the Cloud Control console, Enterprise Manager automatically deletes all target data from the Management Repository.

However, deleting raw and aggregated metric data for database and other data-rich targets is a resource consuming operation. Targets can have hundreds of thousands of rows of data and the act of deleting this data can degrade performance of Enterprise Manager for the duration of the deletion, especially when several targets are deleted at once.To avoid this resource-consuming operation, you can prevent Enterprise Manager from performing this task each time you delete a target. When you prevent Enterprise Manager from performing this task, the metric data for deleted targets is not purged as part of target deletion task; instead, it is purged as part of the regular purge mechanism, which is more efficient.

In addition, Oracle strongly recommends that you do not add new targets with the same name and type as the deleted targets within 24 hours of target deletion. Adding a new target with the same name and type will result in the Grid Control console showing data belonging to the deleted target for the first 24 hours.

To disable raw metric data deletion:

1. Use SQL*Plus to connect to the Management Repository as the Management Repository user.

   The default Management Repository user is SYSMAN. For example:

   SQL> connect sysman/sysman_password;
   

2. To disable metric deletion, run the following SQL command.

   SQL> EXEC MGMT_ADMIN.DISABLE_METRIC_DELETION();
   SQL> COMMIT;
   

To enable metric deletion at a later point, run the following SQL command:

1. Use SQL*Plus to connect to the Management Repository as the Management Repository user.

   The default Management Repository user is SYSMAN. For example:

   SQL> connect sysman/oldpassword;
   

2. To enable metric deletion, run the following SQL command.

   SQL> EXEC MGMT_ADMIN.ENABLE_METRIC_DELETION();
   SQL> COMMIT;
   

20.2.5 How to Modify the Retention Period of Job History

Enterprise Manager Cloud Control has a default purge policy which removes all finished job details which are older than 30 days. This section provides details for modifying this default purge policy.

The actual purging of completed job history is implemented via a DBMS_SCHEDULER job that runs once a day in the repository database. When the job runs, it looks for finished jobs that are 'n' number of days older than the current time (value of sysdate in the repository database) and deletes these jobs. The value of 'n' is, by default, set to 30 days.

The default purge policy cannot be modified via the Enterprise Manager console, but it can be changed using SQL*Plus.

To modify this purge policy, follow these steps:

1. Log in to the repository database as the SYSMAN user, via SQL*Plus.

2. Check the current values for the purge policies using the following command:

   SQL> select * from mgmt_job_purge_policies;

   POLICY_NAME                      TIME_FRAME
   -------------------------------- ----------
   SYSPURGE_POLICY                          30
   REFRESHFROMMETALINKPURGEPOLICY            7
   FIXINVENTORYPURGEPOLICY                   7
   OPATCHPATCHUPDATE_PAPURGEPOLICY           7
   

   The purge policy responsible for the job deletion is called SYSPURGE_POLICY. As seen above, the default value is set to 30 days.

3. To change the time period, you must drop and recreate the policy with a different time frame:

   SQL> execute MGMT_JOBS.drop_purge_policy('SYSPURGE_POLICY');

   PL/SQL procedure successfully completed.
   

   SQL> execute MGMT_JOBS.register_purge_policy('SYSPURGE_POLICY', 60, null);

   PL/SQL procedure successfully completed.
   

   SQL> COMMIT;

   Commit complete.
   

   SQL> select * from mgmt_job_purge_policies;

   POLICY_NAME                      TIME_FRAME
   -------------------------------- ---------- 
   SYSPURGE_POLICY                          60
   ....
   

The above commands increase the retention period to 60 days. The time frame can also be reduced below 30 days, depending on the requirement.

You can check when the purge job will be executed next. The actual time that the purge runs is set to 5 AM repository time and can be verified using these steps:

1. Login to the Repository database using the SYSMAN account.

2. Execute the following command:

   SQL> select job_name,
               to_char(last_start_date, 'DD-MON-YY HH24:MI:SS') last_run,
               to_char(next_run_date,   'DD-MON-YY HH24:MI:SS') next_run
   from all_scheduler_jobs
   where job_name ='EM_JOB_PURGE_POLICIES';
    
   JOB_NAME              LAST_RUN           NEXT_RUN
   --------------------- ------------------ ------------------
   EM_JOB_PURGE_POLICIES                    07-SEP-11 05:00:00
   

   The schedule can also be verified from the Enterprise Manager console by following these steps:

   1. From the Setup menu, select Management Service, the select Repository.

   2. Click the Repository Operations tab.

   3. Find the Next Scheduled Run and Last Scheduled Run information for Job Purge in the list.

   Please note that the time of the next scheduled execution of the Job Purge does not represent the cutoff time for the retention period; the cutoff time is determined by the purge policy at the time the Job Purge runs.

20.2.6 DBMS_SCHEDULER Troubleshooting

Enterprise Manager uses the database scheduler (dbms_scheduler) to run various processes in the repository. When the dbms_scheduler is stopped or has insufficient resources to operate, the Enterprise Manager processes do not run or are delayed. The following is a list of common causes that may prohibit the dbms_scheduler from running normally.

Job Queue Processes

The dbms_scheduler uses a separate job-queue process for each job it runs. The maximum number of these processes is controlled by the database parameter, job_queue_processes. If all processes are in use, no new jobs will be started.

The following query returns the number of currently running jobs.

SQL> SELECT count(*)
FROM dba_scheduler_running_jobs;

If the count is close to the setting of job_queue_processes, it could mean that Enterprise Manager dbms_scheduler jobs cannot be started (on time). Determine if any of the running dbms_scheduler jobs are stuck and consider increasing the setting for job_queue_processes.

Job Slave Processes

The dbms_scheduler also depends on the setting of the dbms_scheduler property MAX_JOB_SLAVE_PROCESSES. If the number of running dbms_scheduler jobs exceeds this setting, no new jobs will be started. This attribute can be checked using this query.

SQL> SELECT value
FROM dba_scheduler_global_attribute
WHERE attribute_name='MAX_JOB_SLAVE_PROCESSES';

If the count equals the number of running dbms_scheduler jobs, then determine if any of the running dbms_scheduler jobs are stuck and consult the dbms_scheduler documentation about how to adjust this attribute.

DBMS_SCHEDULER Program Disabled

The dbms_scheduler has an attribute that can be set to disable this feature in the database. When set, the Enterprise Manager dbms_scheduler jobs will not run. To check if this attribute has been set (inadvertently), run this query.

SQL> SELECT *
FROM dba_scheduler_global_attribute
WHERE attribute_name = 'SCHEDULER_DISABLED';

When a row is returned, the dbms_scheduler is disabled. Execute dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');

Consult the dbms_scheduler documentation about how to remove this attribute.

Too Many Database Sessions

Each dbms_scheduler job requires two database sessions. When no more sessions are available, Enterprise Manager dbms_scheduler jobs will not run. The following two queries give the maximum number of allowed sessions and the current number of active sessions:

SQL> SELECT value
FROM v$parameter
WHERE name='sessions';

SQL> SELECT count(*)FROM v$session;

When the current number of sessions approaches the maximum, then you should determine if any of the sessions are stuck and consult the Oracle Database documentation about how to increase the maximum number of sessions.

Also the high water mark of the number of sessions may indicate that this issue has played a role in the past:

SQL> select * 
from v$resource_limit 
where resource_name = 'sessions' ;

If the MAX_UTILIZATION column indicates a value that is close the maximum number of sessions, it could explain why some of the Enterprise Manager dbms_scheduler jobs may not have run (on time) in the past.

Insufficient Memory

The database may not be able to spawn a new job queue process when there is insufficient memory available. The following message in the database alert file, Unable to spawn jobq slave processes, in combination with, (free memory = 0.00M), would be indicative of this problem. Please consult the Oracle Database documentation about how to diagnose this memory problem further.

20.4.1 Dropping the Management Repository

To recreate the Management Repository, you first remove the Enterprise Manager schema from your Management Repository database. You accomplish this task using the -action drop argument to the RepManager script, which is described in the following procedure.

To remove the Management Repository from your database:

1. Locate the RepManager script in the following directory of the Middleware Home where you have installed and deployed the Management Service:

   ORACLE_HOME/sysman/admin/emdrep/bin
   

   Note:

   Do not use the database version of the Repmanager script. It does not delete all components which will result in a failed re-installation.

2. At the command prompt, enter the following command:

   $PROMPT> RepManager repository_host repository_port repository_SID 
   -sys_password password_for_sys_account -action dropall
   

   In this syntax example:

   • repository_host is the machine name where the Management Repository database is located

   • repository_port is the Management Repository database listener port address, usually 1521

   • repository_SID is the Management Repository database system identifier

   • password_for_sys_account is the password of the SYS user for the database. For example, change_on_install

   • -action drop indicates that you want to drop the Management Repository

Alternatively, you can use a connect descriptor to identify the database on the RepManager command line. The connect descriptor identifies the host, port, and name of the database using a standard Oracle database syntax.

For example, you can use the connect descriptor as follows to create the Management Repository:

$PROMPT> ./RepManager -connect "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=host1)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=servicename)))"
-sys_password efkl34lmn -action dropall

20.4.2 Recreating the Management Repository

The preferred method for creating the Management Repository is to create the Management Repository during the Enterprise Manager installation procedure, which is performed using Oracle Universal Installer.

However, if you need to recreate the Management Repository in an existing database, you can use the RepManager script, which is installed when you install the Management Service. Refer to the following sections for more information:

• Using the RepManager Script to Create the Management Repository

• Using a Connect Descriptor to Identify the Management Repository Database

$PROMPT> ./RepManager -connect "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=host1)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=servicename)))" -sys_password efkl34lmn -action create

$PROMPT> ./RepManager -connect "(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521)
(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521)
(CONNECT_DATA=(SERVICE_NAME=servicename)))"
-sys_password efkl34lmn -action create

20.5.1 Package Body Does Not Exist Error While Creating the Management Repository

If the creation of your Management Repository is interrupted, you may receive the following when you attempt to create or drop the Management Repository at a later time:

SQL> ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "SYSMAN.MGMT_USER" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYSMAN.SETEMUSERCONTEXT", line 5
ORA-06512: at "SYSMAN.CLEAR_EMCONTEXT_ON_LOGOFF", line 4
ORA-06512: at line 4

To fix this problem, see "General Troubleshooting Techniques for Creating the Management Repository".

20.5.2 Server Connection Hung Error While Creating the Management Repository

If you receive an error such as the following when you try to connect to the Management Repository database, you are likely using an unsupported version of the Oracle Database:

Server Connection Hung

To remedy the problem, upgrade your database to the supported version as described in Oracle Enterprise Manager Cloud Control Installation and Basic Configuration.

20.5.3 General Troubleshooting Techniques for Creating the Management Repository

If you encounter an error while creating the Management Repository, drop the repository by running the -drop argument to the RepManager script.

If the RepManager script drops the repository successfully, try creating the Management Repository again.

If the RepManager -action drop/dropall fails for any reason, perform the following steps:

1. Apply the Bundle Patch to the 12c OMS home. Refer to My Oracle Support Note 1393173.1: Enterprise Manager Cloud Control Installation Instructions for Bundle Patch 1 and 12.1.0.2 Plugins for instructions.

2. Stop the OMS and verify that all the WLS / OMS processes have been stopped in the OMS home:

   cd <OMS_HOME>/bin
   emctl stop oms -all
   

   Note:

   You should use the -all option so that the Admin Server is stopped as well

   Verify that there are no WLS / OMS processes still running:

   $ ps -ef | grep EMGC
   $ ps -ef | grep java
   

3. Drop the repository objects using "Repmanager dropall" command:

   cd <OMS_HOME>/sysman/admin/emdrep/bin
   RepManager <database hostname> <database listener port> <database sid> -action dropall -dbUser sys -dbPassword <sys user password> -dbRole sysdba -mwHome <Middleware Home> -mwOraHome <Middleware Home> -oracleHome <OMS Home>
   

   For example:

   RepManager repomachine.domain 1521 orcl -action dropall -dbUser sys -dbPassword oracle123 -dbRole sysdba -mwHome /home/oracle/Middleware 
   -mwOraHome /home/oracle/Middleware -oracleHome /home/oracle/Middleware/oms
   

4. Log in to the Repository Database as sys or any DBA user and verify that all the repository objects have been dropped:

   SQL> select username,account_status from dba_users where username in ('SYSMAN', 'SYSMAN_MDS','MGMT_VIEW','SYSMAN_BIP','SYSMAN_APM','BIP','SYSMAN_OPSS','SYSMAN_RO') ;
    
   SQL> select owner,synonym_name from dba_synonyms where table_owner in ('SYSMAN', 'SYSMAN_MDS','MGMT_VIEW','SYSMAN_BIP','SYSMAN_APM','BIP','SYSMAN_OPSS','SYSMAN_RO') ;
    
   SQL> select tablespace_name from dba_tablespaces where tablespace_name like 'MGMT%';
    
   SQL> select comp_name from SCHEMA_VERSION_REGISTRY;
   

   None of the above queries should return any rows. If any of the above queries return any rows, then raise an SR with Oracle Support.

   Note:

   The above solution is applicable if the OMS is in working condition. If the OMS home is not available or not intact, raise an SR with Oracle Support.

20.6.1 Common Prerequisites

The following lists the common prerequisites for a repository migration:

• Source and target database must use the same character set and should be at same version.

• The target database should meet all the pre-requisites for the Enterprise Manager Repository software requirements mentioned in the Oracle Enterprise Manager Installation Guide.

• If the source and target database are on release 10gR2 and higher rdbms versions, and provided they are meeting other prerequisites, cross platform transportable database migration can be used for cross platform repository migration.

• You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.

• To plug a transportable tablespace set into an Oracle Database on a different platform, both databases must have compatibility set to at least 10.0.

• Most of the platforms (but not all) are supported for cross-platform tablespace transport. You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine their platform IDs and their endian format (byte ordering).

• Source and Destination host should have Enterprise Manager Management Agent running and configured to the instance which is to be migrated.

• If target database has Enterprise Manager repository installed, it should be first dropped using RepManager before target database related steps are carried out.

20.6.2 Methodologies

The following sections discuss two methodologies for a repository migration:

• Cross Platform Transportable Database

• Migration Using Physical Standby

20.6.3 Post Migration Verification

These verification steps should be carried out post migration to ensure that the migration was completely successful:

• Verify any discrepancy in objects by comparing source and target databases through Enterprise Manager.

• Verify the migrated database through Enterprise Manager to determine whether the database is running without any issues.

• Verify the repository operations, dbms jobs and whether any management system errors are reported.

• Verify that all Enterprise Manager functionalities are working correctly after the migration.

• Make sure Management Services and the Repository target is properly relocated by verifying it through Enterprise Manager.