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

2017-09-13 Wed

00:36 Log Buffer #521: A Carnival of the Vanities for DBAs (3275 Bytes) » Official Pythian Blog

While cloud technologies are roaring ahead in full force; the traditional RDBMS like Oracle, Microsoft SQL Server and MySQL are adapting pretty fast. This Log Buffer Edition covers blogs related to that and more.


Oracle JET Simple Table with Fake JSON Server

Every time any page is requested from your website by a human or another program or an automated bot, the event is tracked in a log file that is stored on the web server.

Gitora 2 enabled developers to manage database objects that support the CREATE OR REPLACE command, namely PACKAGES, PROCEDURES, FUNCTIONS, TRIGGERS, VIEWS, SYNONYMS and TYPES in a Git repository.

Oracle just released its first REST JDBC driver on OTN, in conjunction with the 17.3.0 Oracle REST Data Services Beta release.

When you are restrained in the JRE that you can use with SQLcl, you can embed your own in the sqlcl directory tree.

SQL Server:

Understanding Azure Data Factory – A Cloud Based Integration Service

The Shortcomings of Predictive Accuracy

Setting Variables in Calling T-SQL Code While Using sp_executesql

Azure DWH part 15: PolyBase and Azure Data Lake

Creating Basic Indexes on the Stack Overflow Public Database


Heads Up: The List of Replication Defaults That Have Changed in 8.0.2

IO-bound table scan performance for MyRocks, InnoDB and TokuDB

MyRocks Experimental Now Available with Percona Server for MySQL 5.7.19-17

Timing load & index for sysbench tables

Automatic Partition Maintenance in MariaDB

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.