REPVFY Diagnostics, Part II of a Zillion
We are back to REPVFY, (Repository Verification Utility) this week, (first post can be found here…) And I’m onto the next file of substance since looking at the advisor log, (performance data). The next files are the two “details” files. One is a query used to produce the output and the second is the actual output from it.
These two files are clearly named and can be found with the following naming convention:
- details_<timestamp>.sql
- details_<timestamp>.log
If you are curious about what the details_<timestamp>.sql is doing as it produces the output, well you’re in luck, as I’m going to go step by step, through query, then output for REPVFY’s “details” section, as it won’t take to long:
Step #1– Query the ECM, (Enterprise Content Management) Metadata:
SELECT o.name table_name, c.name column_name
FROM sys.obj$ o, sys.tab$ t ,sys.col$ c
WHERE o.owner# = userenv('SCHEMAID')
AND c.name = 'ECM_SNAPSHOT_ID'
AND c.obj# = o.obj#
AND o.obj# = t.obj#
AND BITAND(t.property, 1) = 0
AND BITAND(c.property, 32) = 0
AND BITAND(c.property,512) = 0
AND o.name NOT LIKE 'BIN$%'
AND o.name NOT IN ('EM_ECM_ALL_CONFIG_METRICS','EM_ECM_ALL_CONFIG_SNAPSHOTS','EM_TARGET_PROPERTIES_ECM',
'MGMT_ECM_ASSOCIATIONS','MGMT_ECM_INSTALLED_PRODUCT','MGMT_ECM_SNAPSHOT_ANNOTATION','MGMT_ECM_USER_DEF_PRODUCTS',
'MGMT_STORAGE_REPORT_UI_TARGETS','MGMT_TARGET_PROPERTIES','MGMT_DB_FEATURE_USAGE_ECM')
AND NOT EXISTS
(SELECT 1
FROM mgmt_ecm_snapshot_md_tables m
WHERE m.name = o.name)
ORDER BY table_name, column_name
;
This query is looking for specific ECM, (Enterprise Content Management) tables that aren’t registered in the MGMT_ECM_SNAPSHOT_MD_TABLES, are not in the recycle bin, (If there was a working table dropped, etc. it could show up in the query) and skips the tables used for the feature and are so not registered to begin with.
Our output from this for our example today does show one row returned in the details log file. Most of use will recognize the acronym “EBS”:
Unregistered ECM metadata tables TABLE_NAME COLUMN_NAME -------------------------------- -------------------------------- EBS_PROFILE_WARNINGS_ECM ECM_SNAPSHOT_ID
Step #2– Look for tables that ARE registered, but don’t actually exist:
SELECT DISTINCT e.metadata_id, e.name, m.target_type, m.snapshot_type
FROM mgmt_ecm_snapshot_md_tables e, mgmt_ecm_snapshot_metadata m
WHERE e.metadata_id = m.metadata_id
AND m.target_type NOT LIKE '%beehive%'
AND NOT EXISTS
(SELECT 1
FROM user_objects u
WHERE u.object_name = e.name
AND u.object_type IN ('TABLE','VIEW'))
ORDER BY e.name
;
For our example, we had one too. So not only is it not registered, but it doesn’t exist. Well, that would explain why it’s not registered!
Non-existing registered ECM metadata tablesf those, too:
METADATA_ID NAME TARGET_TYPE SNAPSHOT_TYPE -------------------------------- -------------------------------------------------- ---------------------------------------- EEFC0ACCC59B7F4BE0432014800A5CF4 EBS_PROFILE_WARNING_ECM oracle_ebs oracle_ebs_config
Step #3– ECM Tables missing a Snapshot ID
SELECT DISTINCT e.metadata_id, e.name, m.target_type, m.snapshot_type FROM mgmt_ecm_snapshot_md_tables e, mgmt_ecm_snapshot_metadata m WHERE m.metadata_id = e.metadata_id AND m.target_type NOT LIKE '%beehive%' AND NOT EXISTS (SELECT 1 FROM user_tab_columns u WHERE u.table_name = e.name AND u.column_name = 'ECM_SNAPSHOT_ID') ORDER BY e.name, m.snapshot_type, m.target_type ;
The same object, EBS_PROFILE_WARNING_ECM is to blame for the output again:
ECM tables without ECM_SNAPSHOT_ID column METADATA_ID NAME TARGET_TYPE SNAPSHOT_TYPE -------------------------------- -------------------------------------------------- ---------------------------------------- EEFC0ACCC59B7F4BE0432014800A5CF4 EBS_PROFILE_WARNING_ECM oracle_ebs oracle_ebs_config
Step #4– Are there any columns missing from the ECM tables in the SYSMAN schema?
SELECT DISTINCT e.metadata_id, e.table_name, e.name, m.target_type, m.snapshot_type FROM mgmt_ecm_snapshot_md_columns e, mgmt_ecm_snapshot_metadata m WHERE m.metadata_id = e.metadata_id AND m.target_type NOT LIKE '%beehive%' AND NOT EXISTS (SELECT 1 FROM user_tab_columns u WHERE u.table_name = e.table_name AND u.column_name = e.name) ORDER BY e.table_name, e.name, m.target_type, m.snapshot_type ;
As you’ll note, we are again looking at the EBS_PROFILE_WARNING_ECM object. This table is pinpointed as missing columns, but you are starting the detail this check is looking into.
Non-existing registered ECM columns METADATA_ID TABLE_NAME NAME TARGET_TYPE SNAPSHOT_TYPE -------------------------------- -------------------------------- -------------------------------------------------- EEFC0ACCC59B7F4BE0432014800A5CF4 EBS_PROFILE_WARNING_ECM PROFILE_WARNING_COUNT oracle_ebs oracle_ebs_config
Step #5– Are there any clustered targets that have a misconfiguration between hosts and/or EMD_URLs?
SELECT t.entity_guid, t.entity_name, t.host_name, t.emd_url FROM em_manageable_entities t WHERE t.monitoring_mode = 1 AND t.manage_status = 2 AND t.broken_reason = 0 AND t.emd_url NOT LIKE '%'||t.host_name||'%' ORDER BY t.entity_name ;
Me thinks we see a pattern forming… 🙂
Clustered targets with hostname and EMD_URL mismatches ENTITY_GUID ENTITY_NAME HOST_NAME EMD_URL -------------------------------- ------------------------------------------------------------ - 681FFDDB35CD5A188C6732F095991B4F /Orcl01_ora1_domain/ora1_domain host1.orcl.com https://host1.orcl.com:3872/emd/main/
Step #6– Now we are going to mix it up a little: Are there any missing targets that are identified in the EM Console?
SELECT t.host_name, p.property_value oracle_home, t.entity_guid, t.entity_name, t.entity_type FROM em_manageable_entities t, mgmt_target_properties p, em_current_availability a WHERE p.property_name = 'OracleHome' AND p.target_guid = t.entity_guid AND t.entity_type != 'oracle_emd' AND t.manage_status = 2 AND t.broken_reason = 0 AND t.emd_url IS NOT NULL AND t.entity_guid = a.target_guid AND a.current_status = 1 AND (p.property_value IS NOT NULL OR TRIM(p.property_value) NOT LIKE '') AND NOT EXISTS ( SELECT 1 FROM em_manageable_entities h, mgmt_target_properties q WHERE h.emd_url = t.emd_url AND h.entity_type = 'oracle_home' AND q.target_guid = h.entity_guid AND q.property_name = 'INSTALL_LOCATION' AND RTRIM(q.property_value,'/') = RTRIM(p.property_value,'/') ) ORDER BY t.host_name, p.property_value, t.entity_type, t.entity_name ;
The output was quite long for this one, as the client had brought us in to help them stabilize an installation that onsite DBA had experienced issues with. This is a good example that even though EM12c makes managing, monitoring and enhancing your environment easy, you should ensure that you know what you’re doing before you perform an installation.
HOST_NAME ORACLE_HOME ENTITY_NAME ENTITY_TYPE ------------------------------------------------------------ ----------------------------------------------------------- -------------------------------- orcl21.orcl.com /u02/app/11.2.0 ORCL1 oracle_database orcl24.orcl.com /u01/app/orcl1/11.2.0 ORCL3 oracle_database orcl23.orcl.com /u01/app/orcl3/11.2.0 ORCL4 oracle_database orcl48.orcl.com /u02/app/orcl4/11.2.0 ORCL5 oracle_database orcl60.orcl.com /u01/app/orcl5/11.2.0 ORCL6 oracle_database .... orcl005.orcl.com /u03/appl/Oracle/Middleware/wlserver_10.3 weblogic_j2eeserver orcl005.orcl.com /u03/appl/Oracle/Middleware/wlserver_10.3 weblogic_j2eeserver orcl005.orcl.com /u03/appl/Oracle/Middleware/wlserver_10.3 weblogic_j2eeserver
Now all is not lost, in fact, not even close. As you can see from each of the items above, there are some simple fixes to correct what we are looking at in each of these outputs.
1. If there is an EBS environment on these servers and the plug-in is required, uninstall and re-install it correctly. The original installation is in pretty bad shape.
2. If not, then uninstall the compromised plug-in.
3. For each of the targets and target types shown, go into the console, inspect what error is coming up for the individual target.
– If the ORACLE_HOME isn’t set, correct the entry by going into Setup, Add Targets, Add Targets Manually from the right hand global menu.
– You can set the ORACLE_HOME in the Add Non-Host Targets by Specifying Target Monitoring option.
– If the Agent to monitor the target is incorrect, you can add that manually to the target.
Next up in the REPVFY series is the ERROR log, don’t miss out!
