6 Maintaining and Troubleshooting the Repository

6.2.1 Management Repository Default Aggregation and Purging Policies

Enterprise Manager aggregates your management data by hour and by day to minimize the size of the Management Repository. Before the data is aggregated, each data point is stored in a raw data table. Raw data is rolled up, or aggregated, into a one-hour aggregated metric table. One-hour records are then rolled up into a one-day table.

After Enterprise Manager aggregates the data, the data is then considered eligible for purging. A certain period of time has to 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.

One-hour aggregate data records are purged 31 days after they are rolled up to the one-day data table. The highest level of aggregation, one day, is kept for 365 days.

The default data retention policies are summarized in Table 6-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 6-2.

6.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. These other types of data, such as severities, availability records, and string metric history are retained indefinitely.

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

6.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 disk-space requirements for the repository. As a result, you should not modify these policies to improve performance or increase your available disk space. Modifying these default policies can affect the performance of the repository and have adverse reactions on the scalability of your Enterprise Manager installation.

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 repository. You can accomplish this by increasing the retention times for the raw or aggregated data.

To modify the default retention time for each level of management data in the repository, you must insert additional rows into the MGMT_PARAMETERS table in the repository database. Table 6-3 shows the parameters you must insert into the MGMT_PARAMETERS table to modify the retention time for each of the raw data and aggregate data tables.

Table names that contain "_RT_" indicate tables used for Application Performance Monitoring response time data. In the Table Name column, replace datatype with one of the three response time data types: DOMAIN, IP, or URL.

For example, to change the default retention time for the table MGMT_METRICS_RAW from seven days to 14 days:

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

   The default Management Repository user is sysman.

2. Enter the following SQL to insert the parameter and change the default value:

   INSERT INTO MGMT_PARAMETERS (PARAMETER_NAME, PARAMETER_VALUE)  VALUES ('mgmt_raw_keep_window','14');
   
   

Similarly, to change from the default retention time for all of the MGMT_RT_datatype_1DAY tables from 31 days to 100 days:

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

   The default Management Repository user is sysman.

2. Enter the following SQL to insert the parameter and change the default value:

   INSERT INTO MGMT_PARAMETERS (PARAMETER_NAME, PARAMETER_VALUE)  VALUES ('mgmt_rt_day_keep_window', '100');
   

6.2.4 Modifying Data Retention Policies When Targets Are Deleted

By default, when you delete a target from the Grid Control, Enterprise Manager automatically deletes all target data, including raw metric data and aggregated 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 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 repository as the Management Repository user.

   The default repository user is SYSMAN. For example:

   SQL> connect sysman/oldpassword;
   
   

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

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

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

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

   The default repository user is SYSMAN. For example:

   SQL> connect sysman/oldpassword;
   
   

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

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

6.5.1 Dropping the Management Repository

To recreate the Management Repository, you first remove the Enterprise Manager schema from your 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 Oracle Application Server Home where you have installed and deployed the Oracle Management Service:

   IAS_HOME/sysman/admin/emdrep/bin
   
   

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

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

   In this syntax example:

   • repository_host is the machine name where the repository database is located

   • repository_port is the repository database listener port address, usually 1521 or 1526

   • repository_SID is the 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 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_DATE=(SERVICE_NAME=servicename)))
-sys_password efkl34lmn -action drop

6.5.2 Recreating the Management Repository

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

However, if you need to recreate the repository in an existing database, you can use the RepManager script, which is installed when you install the Oracle 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_DATE=(SERVICE_NAME=servicename)))
-sys_password efkl34lmn -action create

6.6.1 "Package Body Does Not Exist" Error While Creating the 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 Repository".

6.6.2 "Server Connection Hung" Error While Creating the Repository

If you receive an error such as the following when you try to connect to the 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 Basic Installation and Configuration.

6.6.3 General Troubleshooting Techniques for Creating the 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 repository again.

If you encounter errors while dropping the repository, do the following:

1. Connect to the database as SYSDBA using SQL*Plus.

2. Check to see if the SYSMAN database user exists in the repository database.

   For example, use the following command to see if the SYSMAN user exists:

   prompt> SELECT username FROM DBA_USERS WHERE username='SYSMAN';
   
   

3. If the SYSMAN user exists, drop the user by entering the following SQL*Plus command:

   prompt> DROP USER SYSMAN CASCADE;
   
   

4. Check to see if the following triggers exist:

   SYSMAN.EMD_USER_LOGOFF
   SYSMAN.EMD_USER_LOGON
   
   

   For example, use the following command to see if the EMD_USER_LOGOFF trigger exists in the database:

   prompt> SELECT trigger_name FROM ALL_TRIGGERS 
           WHERE trigger_name='EMD_USER_LOGOFF';
   
   

5. If the triggers exist, drop them from the database using the following commands:

   prompt> DROP TRIGGER SYSMAN.EMD_USER_LOGOFF;
   prompt> DROP TRIGGER SYSMAN.EMD_USER_LOGON;