Database, Errors and Bugs, Migration | Upgrades | Patching, Performance

Adaptive Query Optimization: SQL Plan Directives Causing High CPU after 12c Upgrade

 , , , , ,

Hello all!

As DBAs we are always being recommended by Oracle and also recommending to clients to update their databases, but we have to be aware about new features and their effects. This is the case of Adaptive Query Optimization and in this particular case on SQL Plan Directives.

SQL Plan Directives are one of the functionalities that compose the Adaptive Query Optimization in Oracle Database 12c. The basic idea is pretty interesting: The SQL Optimizer keeps reviewing bad (“suboptimal”, as they like to say) plans, tipically incorrect cardinality estimations and generates SQL plan directives, like for missing histograms or extended statistics.

In my case, just after the upgrade to 12c (made on Jan 27th), the CPU usage increased for the same report always ran in the database:

12c_upgrade

Ok, how to check it?

More“Adaptive Query Optimization: SQL Plan Directives Causing High CPU after 12c Upgrade”

Database, Enterprise Manager, SQL | PLSQL

EM Event: Number of failed login attempts exceeds threshold value [Part 2]

Hello,
As a second part of last week’s post, here is a way to accomplish that without using audit. Using a simple logon trigger! 🙂
Take a look:

First step: Create a table to store the data.

CREATE TABLE SYS.TRG_LOGON (SCHEMA VARCHAR2(50), SERVER VARCHAR2(200), FAILTIME DATE, ERROR VARCHAR2(200));

More“EM Event: Number of failed login attempts exceeds threshold value [Part 2]”

Database, Errors and Bugs, SQL | PLSQL

User EXPIRED(GRACE) – How to Never Expire!

 , ,

Ok, this is a common issue, once it’s like the default behavior of oracle for DEFAULT profile, used as DEFAULT to new users. But it’s really easy to solve. 🙂

Before expiring you should be received some:

ORA-28002: the password will expire within x days

Ok then, but what if you are facing errors to reuse the same password, don’t know the password and yet, how to never let it happen again?

More“User EXPIRED(GRACE) – How to Never Expire!”

Database, Enterprise Manager, Errors and Bugs

EM Repository: ORA-00060: Deadlock detected error in alert log

 , , , , ,

Hello!
Some time ago I found some ORA-00060: Deadlock detected. errors in a client OEM database… Like this:

Thu Dec 22 09:01:55 2016
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1757.trc.
Thu Dec 22 09:02:07 2016
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1759.trc.
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1759.trc.

In summary, after investigating the trace (as per below), found that the issueis caused by the following command:

More“EM Repository: ORA-00060: Deadlock detected error in alert log”

Database, RAC | Data Guard, Shell Scripts

Shellscript: Which database is this service_name from?

 , , ,

Hey!
Strange question, right? You should know you applications and databases and how everything works, right?

Happen that some days ago a client requested me to identify from which database is a service_name. Easy? Not if the server have 46 database instances running…

Ok, now how can I can this information as quick as possible?

I know that the command below give me all distinct databases in CRS:

/u01/app/12.1.0.2/grid/bin/crsctl stat res -t -w "TYPE = ora.database.type"

Now, lets make some sed magic:

[root@nprd01dbadm01 ~]# /u01/app/12.1.0.2/grid/bin/crsctl stat res -t -w "TYPE = ora.database.type" |grep ora. |sed 's/ora./srvctl status service -d /' |sed 's/.db/ |grep \$1/' | head -1
srvctl status service -d dba |grep $1

More“Shellscript: Which database is this service_name from?”

Database, Errors and Bugs, Java | DevOps | APEX | Scripts, Migration | Upgrades | Patching

“java.sql.SQLException: ORA-28040: No matching authentication protocol” After Upgrade Database to 12c

Hello everyone!

I already wrote a post about it but I think I was not clear and easy to find enough. So let me make another post about and add some information…

Ok, are you facing a “java.sql.SQLException: ORA-28040: No matching authentication protocol” after upgrade your database to 12c?

It’s related to old jdbc driver version in your application. Please review matrix available in this post and check your application. The MOS noted below have good information about it…

More““java.sql.SQLException: ORA-28040: No matching authentication protocol” After Upgrade Database to 12c”

grepOra

GrepOra’s 2 Years Commemorative Book: |GREP ORA | sort quality | head 200

 , , ,

Hello all!

We are very proud and happy to share with you our first book!
We made it as a celebration for the GrepOra’s 2 years, completed last Sunday (Jan 29th).

It has the top 200 posts published in this last 2 years for your appreciation. It’s, above all, a good opportunity to refresh some posts that are still actual and relevant. And it’s all free!

We compiled it as a best moments review also to engage new readers with the best past posts and reach that readers that enjoy to read a book in their mobile reading devices. Actually, we believe that writing material for this kind of media is the future (or the present), so if you prefer to read PDF, MOBI or EPUB in you Kindle, Ipad, or similar, specially for those who prefer the offline mode to not being bothered by social media notifications, instant messages and other: This is for you. 🙂

Download PDF
Download MOBI
Download EPUB

There is no chapters of any restrictedly fixed boundaries. However, to give a little sense, we kind of organized the posts by following this (using our blog categories):

  • Oracle Database, RAC and Dataguard;
  • ASM;
  • RMAN, Datapump, Exp/Imp;
  • Enterprise Manager;
  • Application and Middleware;
  • Golden Gate and Data Integrator;
  • Linux and Shellscripts;
  • Cloud Computing;
  • Heterogeneous Databases;
  • Web Development and APEX;
  • PL/SQL and SQL Scripts;
  • Errors and Bugs.

This book is to above all, view, learn and review some curiosities, tips and some useful stuff for daily basis challenges and struggles on working with Oracle tech. But mostly to have fun! This is a book written by Oracle geeks to Oracle geeks.

Enjoy!

GrepOra Team.