How to fix an AWR Warehouse after credentials change

We had an AWR Warehouse (AWRW) installation (OEM 12.1.0.5 on Linux) collecting data from +50 databases for a few months.

A few days ago we tried to connect and found it was not working, with the AWRW console showing an empty page and this legend in top of the page: “AWR Warehouse Database connection could not be established”.

That page is accessed using the performance menu from any database target in OEM:

After a quick sanity check, we found the AWRW repository database working, registered with the listener and upload jobs running, so it was not a connectivity problem.

But the most alarming problem was the configuration page showing that AWRW was not configured:

Documentation from Oracle was not helpful for this particular issue, as nothing appeared in public docs nor MOS.

Checking OMS logs we found this interesting messages in emoms.log file ($OMS_HOME/gc_inst/em/EMGC_OMS1/sysman/log/):

   2017-08-25 11:41:24,084 [RJob Step 7006584] ERROR emdrep.jobs logp.251 - checkDumpSpace: aborting, error in reading warehouse credentials

That was a nice clue pointing to a credential issue.

With that MOS offered a better help this time with the note “How to Determine AWR Warehouse EM Configuration Properties Specified During Configuration Using SQL (Doc ID 2022411.1)”.

There we have a query to find out AWRW configuration that is not visible on OEM web console:

SQL> select * from sysman.DB_CAW_REPOS;

 EM_ID TARGET_GUID                      DB_CRED_GUID                     HOST_CRED_GUID                   S
 ----- -------------------------------- -------------------------------- -------------------------------- - 
     1 CB769F04D032741A6B7268AB41EDA07C 4892AB68BE1B7D86E043FBAD628AF732 4892AB68BEAC7D86E043FBAD628AF732 1

The OEM security model includes several authentication schemes, and we can dig into the repository tables where that information is stored. The following steps show how we can use that information to troubleshoot an OEM configuration issue, being this AWRW problem just an example.

Using the internal codes found in the AWRW configuration we see that effectively those credentials don’t exist in the repository:

SQL> select count(*) from sysman.em_nc_creds 
where CRED_GUID='4892AB68BE1B7D86E043FBAD628AF732';

  COUNT(*)
----------
	 0

There are several other tables containing references to credentials, so it is not really fast to check them all:

SQL> select count(*) from dba_tables
where owner='SYSMAN' and table_name like '%CRED%'
order by table_name;

  COUNT(*)
----------
	50

Another interesting finding is the target referenced by that configuration doesn’t exist in OEM repository:

SQL> col target_name for a30
select target_name, target_type
from mgmt_targets
where target_guid='CB769F04D032741A6B7268AB41EDA07C';

no rows selected

SQL> select count(1) from mgmt_targets;

  COUNT(1)
----------
       437

At this point is clear that some configuration was changed in OEM for our AWRW repository target. Probably the target was re-discovered and some credentials cleanup was done by some OEM admin user, that is some forensic we could explore in the future.

The possible options to fix this problem now are:

• use OEM to configure AWRW again pointing to an existing database. The problem with this is metadata gets updated and all the data we already have there could be no longer visible, as some quick google search reveals.
• update AWRW config directly in OEM repo (sysman.DB_CAW_REPOS) to use valid credentials

The latest option looks a quick thing to do.

Before continuing, please remember to have in hand a working database backup, just in case things don’t work as we expect. A quick approach for that is to enable flashback and use guaranteed restore points.

Now, let’s gather the internal codes we will need to update OEM repository. It is:

• existing target for AWRW database
• OEM credentials for database
• OEM credentials for host

Our database is called AWRWH. First, let’s get the internal target name:

SQL> select target_guid, target_name, target_type
from mgmt_targets
where upper(target_name) like '%AWRWH%'; 

TARGET_GUID			 TARGET_NAME			TARGET_TYPE
-------------------------------- ------------------------------ ---------------------
5184F7120DEA9B9E00A71C5B0D90D3BC AWRWH_AWRWH_1                  oracle_database
3C8F26DF7DF8BE797996F31BB86E6A30 AWRWH                          rac_database
ED3413D3236FF663FAF42F023EF77904 AWRWH_cellsys                  oracle_exadata_dbsys
B352D0CE6D7145CADD8DE12855538419 AWRWH_sys                      oracle_dbsys

Second, internal credential codes in use for those targets:

SQL> select cred_guid, cred_name, target_guid, CRED_TYPE_NAME
from sysman.em_nc_creds
where target_guid in 
    (select target_guid from mgmt_targets
     where upper(target_name) like '%AWRWH%');

CRED_GUID                        CRED_NAME                           TARGET_GUID                      CRED_TYPE_NAME
-------------------------------- ----------------------------------- -------------------------------- --------------
450AB9A10C26A65FE50F3AE6C914BCAD MC_450AB9A1026C5F6A53FE0EAC169B4ACD 5184F7120DEA9B9E00A71C5B0D90D3BC DBCreds
7E053FEAC619BD13E5411E63836B119F NC_AWRWH_2017-07-11-150301          3C8F26DF7DF8BE797996F31BB86E6A30 DBCreds

We are almost there, only the host credential is missing (as it is not directly attached to this target).

But we have a lot of host credentials defined in our OEM:

SQL> select CRED_TYPE_NAME, count(1)
from sysman.em_nc_creds
group by CRED_TYPE_NAME; 

CRED_TYPE_NAME                     COUNT(1)
-------------------------------- ----------
                                        186
OMSCredentials                            1
MonitorCreds                              2
DBHostCreds                               1
ASMCreds                                 17
IBSwitchCreds                             8
DBCreds                                 359
HostCreds                                35
IlomCreds                                11
SNMPV1Creds                               3
ExaCreds

To help us identify which one of the existing host credentials we need, we have to go to OEM named credentials page to test them with our host target.

That is easy in this case as we have only a few available, and the first test was successful:

As we can see the credential name on this page, it is easier to find on OEM repo adding that as a filter:

SQL>

col CRED_NAME for a20 col CRED_TYPE_NAME for a20 col TARGET_TYPE for a20 select CRED_NAME, CRED_TYPE_NAME, TARGET_TYPE, TARGET_GUID, CRED_GUID from sysman.em_nc_creds where CRED_TYPE_NAME in (‘DBHostCreds’,’HostCreds’) and CRED_NAME=’ORACLE_CRED’; CRED_NAME CRED_TYPE_NAME TARGET_TYPE TARGET_GUID CRED_GUID ———– ————– ———– ——————— ——————————– ORACLE_CRED HostCreds host EFECB916CAEF350E58D5670985648900

Now we have the three values we need to update our AWRW configuration:

SQL> update sysman.DB_CAW_REPOS
set db_cred_guid='450AB9A10C26A65FE50F3AE6C914BCAD',
TARGET_GUID='5184F7120DEA9B9E00A71C5B0D90D3BC',
HOST_CRED_GUID='EFECB916CAEF350E58D5670985648900';

1 row updated.

SQL> commit;

Commit complete.

Connecting now to OEM AWRW console shows us the usual landing page, so the configuration problem is fixed.