OEM Alert Log Monitoring Tips

Recently i had a database getting ora-04031- (Shared Pool) errors but were NOT receiving any alerts from OEM – (Grid Control).   After investigation and looking for low hanging fruit (i.e. Missing Incident rule, Agent not running, Check Community and Oracle-L,  etc),i couldn’t see anything that could be causing the Issue.   So I resorted to the last resort – Open Oracle Support Call.

Kudo’s to Brandon at Oracle Support who walked me through alert log monitoring.

Lesson #1 – When you select (Under Database), Monitoring/Metric and Collection Settings/Generic Alert Log Error – Select Edit and then you will see the following screen:



Under Alert Log Filter expression – the default is “.*ORA-0*(54|1142|1146)\D.*”.  This column is for Errors you want to Ignore and NOT  to find – in another words – Oracle Enterprise Manager will ignore any error with those matches / errors entirely.

So only put in this column errors you want to ignore.

#2 – If . you want to search for a particular error – use perl regex type commands and start and end the search with a “.*” (Dot Star).   Example if you want to search for ora-04031 – Put in the critical / Warning – “.*ORA-04031.*”.

#3 – Avoid using “Alert Log” checks and use “DB Alert Log Error Status” Instead, which is turned off by default.  Brandon from Oracle support said that they improved this check and the “Alert Log” will be deprecated in future releases.   BTW, it has all the same metrics as “Alert Log” but code has been upgraded in the “DB Alert Log Error Status” and NOT the “Alert Log” check.   Basically does the same thing but with updated code.

#4 To Test and Generate an error with alert log – run the following as “SYS” with the error you want to check for(Im checking for ORA-04031).

SYS-SQL> exec dbms_system.ksdwrt(3,’ORA-04031: This is a test error message for monitoring and can be ignored.’);   This will put a record in the alert file that you can test.

#5 – To run the check without waiting for the next collection – run the following from the OEM agent home on the database server, this following command will force a collection:

OS>emctl control agent runCollection <DBNAME>:oracle_database db_alertlog_coll_12  – For db alert log

OS> emctl control agent runCollection <DBNAME>:oracle_database alert_log_rollup_12C – for alert log checks

*** Note make sure you have the right DBNAME – to verify the name – run:

OS> emctl status agent scheduler which will show all the metrics collected for this database and host, then find “db_alertlog_coll”

One caveat – is that if you haven’t checked for a ORA- error in the past, the first time it will generate a OEM ticket for each one found.   After that first time, it will only generate for the past duration.


I hope this helps and good luck,








Recently at the RMOUG18 and GLOC18, i was privileged to attend a presentation by Jon Heller.   He presented a open source project called “Method5” – method5.github.io

Method5 is an open source software (github.method5.io) that extends Oracle SQL to allow parallel remote execution. It lets users easily run SQL statements quickly and securely on hundreds of databases at the same time.  Using this tool allows us to query all the databases, some of the databases.  You can run commands this asynchronous or synchronous.  You can also run Shell scripts using Method5 and is a way to gather information about database table spaces, ASH data and other information that can be used for analysis.

The other nice attribute of this tool is that takes Security very serious and added many security items so that it was secure (i.e private DBlinks, No login user).   Lastly, he added a multi-user ability and auditing to this tool that will help auditors with “Compensating Controls” questions.

At this point we have implemented this tool on about 53 databases and plan to install this on about 130 databases in total.   Although this tool is not to replace ansible, python or other tools.   It is a way to run scripts and sql on many databases at one time in a relatively quick time.

I encourage you to visit method5.github.io/  try the product on non-product systems and contact Jon with any questions at jon@jonheller.org.

ORA­00600: internal error code, arguments: [3665], [1025], [16], [], [], [],[], [], [], [], [], [] – During Hot PDB Clone

Recently during a refresh / hot clone of a PDB, we received an Oracle error after the “System” tablespace got corrupted during the clone.  At first sight and much of our initial research, this error above indicates a RMAN issue.   Well it is NOT a RMAN issue, when hot cloning a PDB. We got additional errors:

ORA­00600: internal error code, arguments: [3665], [1025], [16], [], [], [],[], [], [], [], [], []   Pdb XXXXXXPDB hit error 704 during open read write (5) and will be closed. ORA­00704: bootstrap process failure ORA­00376: file 987 cannot be read at this time ORA­01110: data file 987.


Basically the System tablespace in this new PDB was corrupted during the hot PDB clone.

The main problem was that due to this error the Container Database (CDB) kept crashing. When ever we started the database we kept getting : Database mounted. ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], [] and the database would only stay up for 30 minutes before crashing.

When the database was up for the 30 – 40 minutes, we would try to drop it and got the following error:



When we tried to drop it – we got:

drop pluggable database XXXXXPDB including datafiles
ERROR at line 1:
ORA­65104: operation not allowed on an inactive pluggable database

So basically we couldn’t drop this PDB nor could we recover it.

After many days of support with Oracle, the unfortunate news was that the CDB was corrupted:


The only way to recover the CDB / PDB’s was to create a new CDB and unplug and Plug into a new CDB.   Once we got all the “Good” PDB’s moved, we renamed the CDB to the original name and re-setup the backups, etc.

At this point, Oracle doesn’t consider this a bug, but i did file a enhancement request to allow users to drop a corrupted PDB.

One lesson learned was that to keep the # of PDB’s to 8 – 10, due to this kind of an issue.    Thank goodness we only had 6 good PDB’s in this CDB, so it it didnt take long to unplug and plug into new CDB.

Please contact me with any questions.


NOTE – 30-May-18 – Great news – Enh 28102734 – ENH: PROVIDE DROP FORCE FOR PDBS WHERE DATAFILES ARE GONE BUT PDB NOT UNPLUGGED” .   Lets hope it doesn’t take long to provide.

GLOC18 – Cleveland – May 15 – May 17 – Hope to see you there

GLOC18 – Cleveland – May 15 – May 17 – Hope to see you there

I will be presenting an explanation of Oracle’s Multi-tenant (CDB) at this years Great Lake Oracle Conference (2018) -gloc.neooug.org – May 16 at 4:15pm. 
This presentation demonstrate advantages, disadvantages, cost implications and a Total Cost of ownership of using CDBs with both Multi-tenant and default license. This session will discuss the tips, tricks and solutions that provided the ability to overcome the challenges of new technology when failure isn’t an option.
I placed the presentation in the media section of my word press (Under Media)
The information learned from this presentation will help you navigate the difficult world of using Multi-tenant Container (CDB’s). Hope to see you at the conference as there are 3 days of great presentations.  https://gloc.neooug.org

Collaborate 18 – Las Vegas

  • Time is running out to save up to $660 on COLLABORATE 18 – IOUG Forum! Remember: when you register with your team of four or more, everyone receives an additional group rate discount of $250. There’s no better price for a week packed with Oracle training – plan to connect with product experts and like-minded users in April, and register for the best savings! http://collaborate.ioug.org/register#
  • Hope to see you in Las Vegas
IOUG18, GLOC18 and ODTUG18

IOUG18, GLOC18 and ODTUG18

Its gonna be a busy 2nd quarter as i I am privileged and honored to speak at Collaborate – #C18LV in Las Vegas,  Great Lakes Oracle Conference – #GLOC18 in Cleveland and ODTUG KSCOPE18 –  #ODTUG in Orlando

I will be presenting at Collaborate(Papers Posted at Collaborate and This site).

 Migration of data to the Cloud – #1169 – Jasmine E => Tue, Apr 24, 2018 (01:15 PM – 02:15 PM)


Upgrading to the Cloud – tales from the battlefield – #1170 – Jasmine F => Mon, Apr 23, 2018 (11:00 AM – 12:00 PM)


A Case Study of Oracle Database Upgrade and Consolidation – #1435 – Banyan C => Wed, Apr 25, 2018 (02:30 PM – 03:30 PM)


Great Lakes Oracle Conference (GLOC) –

To CDB or not to CDB, That is the question ? Multitenet option explained…


Thanks to all the conference organizers for allowing me to present these topics and share my experiences.

Note – Both presentations will be online in first part of April.


Oracle Cloud Tech Day – Wednesday – 28-Mar-18 – Schoolcraft College – 1pm – 5pm

Come join Oracle ACE Directors, Charles Kim and Rich Niemiec as he speaks about his favorite new features on Oracle Database 12c Release 2 (12cR2), 18c. Oracle professionals fret about their future and the state of direction for the industry. Learn how you can prepare to evolve and adapt to the Changing Role of the DBA with Oracle 12cR2 and 18c as we discuss pertinent new features to help innovate at a time when companies must change to survive. Through this half-day, interactive deep-dive, you will learn upgrade approaches, methods, and best practices for a successful upgrade to Oracle 12cR2 as 11gR2 is going off of error-correction support in 2018. Happy Hour to follow from 5:00pm-7:00pm

TO REGISTER – http://tips.viscosityna.com/techdaydetroit/