Tip: 看不到本站引用 Flickr 的图片? 下载 Firefox Access Flickr 插件 | AD: 订阅 DBA notes --

2017-09-11 Mon

22:06 How to fix an AWR Warehouse after credentials change (12366 Bytes) » Official Pythian Blog

We had an AWR Warehouse (AWRW) installation (OEM 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';


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;


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;


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%'; 

-------------------------------- ------------------------------ ---------------------
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)
-------------------------------- ----------
OMSCredentials                            1
MonitorCreds                              2
DBHostCreds                               1
ASMCreds                                 17
IBSwitchCreds                             8
DBCreds                                 359
HostCreds                                35
IlomCreds                                11
SNMPV1Creds                               3

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
from sysman.em_nc_creds
where CRED_TYPE_NAME in ('DBHostCreds','HostCreds') and CRED_NAME='ORACLE_CRED';

----------- -------------- ----------- --------------------- --------------------------------
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',

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.

2017-09-06 Wed

07:41 Oracle OpenWorld 2017 Bloggers Meetup (4474 Bytes) » Official Pythian Blog

DON’T PANIC. Yes, we are doing the OOW Bloggers Meetup this year. Yes, it’s the same time. Yes, it’s the same location. Yes, it’s more fun every year.

What: Oracle Bloggers Meetup 2017.

When: Wed, 04-Oct-2017, 5:30pm.

Where: Main Dining Room, Jillian’s Billiards @ Metreon, 175 Fourth Street, San Francisco, CA 94103 (street view). Yes, it’s a good time to try taking our blog down with DoS attack of “COUNT ME IN” comments below – if you’re coming, comment away!

As usual Pythian sponsors the venue, drinks and cool fun social stuff. The competition is on — you know what that means (new unique swag this year) and if not, come and learn. All blogger community participants are invited (self qualifying).

As usual, vintage t-shirts, ties, or bandanas from previous meetups will make you look extremely cool — feel free to wear them. The new wearable will be a nice addition to your collection!

For those of you who don’t know the history: The Bloggers Meetup during Oracle OpenWorld was started by Mark Rittman and continued by Eddie Awad. Alex Gorbachev picked up the flag in 2009 and delegated to me in 2017.

The meetups have been a great success for making new friends and catching up with the old, so let’s keep them this way! To give you an idea, here are some photos and a video of past events.

All Oracle technologies — Oracle Database, MySQL, Apps, Sun technologies, Java and ALL THE CLOUD PEEPS — come and meet like-minded “techies”. All bloggers are welcome. We’ve reached 150 attendees in the last few years — maybe another record breaking gathering this year.

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us ensure we have the attendance numbers right. Please provide your blog URL with your comment — it’s a Bloggers Meetup after all! Please do make sure you comment here if you are attending so that we have enough room, food, and (most important) drinks.

And even if you choose to not attend the meetup, check out all Oracle OpenWorld sessions with Pythian speakers and book your seat in these sessions today.

And you can now fill your twitter feeds (linkedin, G+, instagram, mailing lists) by talking about how excited you are to attend, and talk about this year’s meetup with fellow bloggers you’ll meet at the show. See you all there — it will be fun!