Plan Stability: SQLs switching to Bad Plans (HPV) and Load Very High (12c+)

Hi all,

That’s not the first post about this topic. Please read this one first.

Thing is: I access this post soooo often, as I face this sort of issue sooo frequently I decided to make a new one with some updated info. Even because, 3 years ago I wrote this article for 12.1 databases, now we have some relevant changes to 12.2 onwards.

Ok, so, for a little bit of context from the previous post:

  • After some critical issues with Plan Stability (new HSP being created out of nowhere very often) I mapped this to the usage of Adaptive Query Features.
  • At that point, I had a match to the latchfree events and mapped it specifically to latch misses on “Result Cache: RC Latch”.
  • As per MOS at the time, when Automatic Dynamic Sampling is used for the SQL statements, it can decide, based upon these statistics, that a better response time could be achieved by using the result cache for those queries. This can cause heavy usage of the result cache leading to the contention on latch free for “Result Cache: RC Latch”
  • So the villain was the Automatic Dynamic Sampling, as part to the SQL Plan Directives.
  • On the following months and migrations I started seeing more and more issues related to the SQL Plan Directives as part of the Adaptive Query Optimization features. So, I have been following the referred steps more and more.
  • Happens this means setting a few underscore parameters, which we should avoid unless approved/recommended by Oracle, so I’ve been trying to be less intrusive and simply disabling the whole Adaptive Features.
  • How to do it? That’s the tricky part and the thing to keep in mind, as we had changes from 12.1 to 12.2

Some words about the parameters settings:

  • The parameter “optimizer_adaptive_features” is a valid parameter for 12.1 and it’s enabled by default. I recommend leaving it as FALSE if you are facing this sort of issues.
  • This parameter is obsolete con 12.2 onwards though. It was replaced by both optimizer_adaptive_statistics and optimizer_adaptive_plans.
    • 12.1:

adap1

    • 12.2+:

adap2

(images from https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2)

  • Also, keep one eye on all %optim% parameters, as specific mechanisms can be enabled (for some reason) as I recently faced in a client ( _optimizer_adaptive_plan = TRUE).

When Adaptive Queries are good?

Ok Matheus, but if we should disable it, why did Oracle created in the first place?

Well, if something would be always bad, it would be disabled by default… There are some good usage cases: Dynamic Sampling is typically recommended when you know you are getting a bad execution plan due to complex predicates.

oh, noice, so I’ll change on my database I have a lot of complex queries” . Don’t, I suggest you to try and use an alter session statement to set the value for optimizer_dynamic_sampling parameter as it can be extremely difficult to come up with a system-wide setting. And you can mess up with other things.

When Adaptive Queries are bad?

If the queries compile times need to be as fast as possible, for example, unrepeated OLTP queries where you can’t amortize the additional cost of compilation over many executions. Serial statements are typically short running and any Dynamic Sampling overhead at compile time can have a large impact on overall system performance (if statements are frequently hard parsed).

For systems that match this profile, setting OAF=FALSE is recommended. For Oracle Database 12c Release 2 onwards, using the default OAS=FALSE is recommended.

I hope this helps you!

DBCA “Recovery manager failed to restore datafiles”

Hi all,

If that’s the 5th Blog Post with the same title one open, don’t close, READ THIS ONE!

This one is different.

It’s actually another case about Oracle throwing generic errors for DBCA where 99% of times it’s the same error, so all blogs are different but the same in essence, and none resolve your problem. So, let’s go by parts:

The Error from Client:

DBCA_Error

 

Generic Case (if this is the first blog you open about the subject):

  • Make sure the file $ORACLE_HOME/bin/oracle has privilege set to chmod 6751 in both (ASM and DB) homes. it should look like this:
[oracle@PROD01 bin]$ cd /u01/app/oracle/product/19c/db/bin
[oracle@PROD01 bin]$
[oracle@PROD01 bin]$ ls -ltr oracle*
-rwsr-s--x. 1 oracle asmadmin 441253104 Aug 27 22:29 oracle
  • If you are not sure, set it accordingly:
cd $ORACLE_HOME/bin && chmod 6751 oracle
  • Not yet? Check the disks assigned to ASM privileges and groups:
kfod status=TRUE asm_diskstring='/dev/asm*' disk=ALL
  • Ater all this, still not working? Go for the atypical case below:

 

Atypical Case (Exception)

After some struggle and no success, I started validating everything I could. Some piece of words before the silver bullet here:

Noticed I knew you do have ASM? How come that? Well, most likely the error below the ASM happens at the point the DBCA runs a RMAN restore to create the database base files and metadata. This most likely happens at the time the write is happening on the ASM, once this is the most unstable process involved.

By looking deeper on the installation logs I could see:

[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=channel ORA_DISK_1: restoring datafile 00001 to +DATA
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=channel ORA_DISK_1: reading from backup piece /ora01/app/oracle/product/19c/db/assistants/dbca/templates/Seed_Database.dfb
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=channel ORA_DISK_1: ORA-19870: error while restoring backup piece /ora01/app/oracle/product/19c/db/assistants/dbca/templates/Seed_Database.dfb
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=ORA-19504: failed to create file "+DATA"
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=ORA-17502: ksfdcre:4 Failed to create file +DATA
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=ORA-15001: diskgroup "DATA" does not exist or is not mounted
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=ORA-01017: invalid username/password; logon denied

Bingo, so it’s a password issue?

Well, I’m creating the database and this actually matches with all the chmod 6751 thing…

What then?

Well, after a while going crazy validating passwd files and so one, realized something about the oracle user:

[oracle@PROD01 bin]$ id -a
uid=500(oracle) gid=501(oinstall) groups=501(oinstall),10(wheel),203(dba),503(asmadmin),504(asmoper),525(madhoc) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@PROD01 bin]$ grep oracle /etc/group
wheel:x:10:oracle
asmadmin:x:503:oracle
asmoper:x:504:oracle
madhoc:x:525:oracle

Can you see the oracle user is part of the oinstall group but not really appearing on /etc/group? Also not in osusergroup dba.

Well, let’s force it?

[oracle@PROD01 bin]$ sudo su -
Last login: Fri Aug 28 14:13:21 PDT 2020 on pts/3
[root@DMSDB1PA ~]# usermod -g oinstall -G oinstall,dba,asmadmin,asmoper,madhoc oracle
[root@PROD01 ~]# id oracle
uid=500(oracle) gid=501(oinstall) groups=501(oinstall),10(wheel),203(dba),503(asmadmin),504(asmoper),525(madhoc)
[root@PROD01 ~]# grep oracle /etc/group
wheel:x:10:oracle
dba:x:203:oracle
asmadmin:x:503:oracle
asmoper:x:504:oracle
oinstall:x:501:oracle
madhoc:x:525:oracle
[root@PROD01 ~]#

Well done!

Now try running DBCA again. That was a very tricky issue to find.

Know something else? At the point I was writing this I decided to have another look and ended up finding this MOS note: “ORA-17502 /ORA-01017: invalid username/password; logon denied ” While Creating 19c Database (Doc ID 2545858.1). We have a bug for it: BUG:29821687 – ORA-17502 /ORA-01017: INVALID USERNAME/PASSWORD; LOGON DENIED ” WHILE CREATING 19C DATABASE

You have the workaround already though. Go champs!

Hope it helps you, cheers!

OPatch Auto Failing to Apply Datapatch for a Removed Database

Hi all,

So, just a few days ago, during a client support, crossed the following case.

A few database creations initially failed with DBCA due to other issues, but it seems the DBCA didn’t cleread all creation steps after the failure and theoretical rollback.

As a consequence, whenever running DBCA with GUI client was seeing an old database. When trying to remove it with DBCA, the removal fail as the database can’t be brought up (creation has failed, remember?). Ok, we can leave with it, right?

Yes until we reached the point where Opatchauto failed with the following:

Verifying SQL patch applicability on home /u01/app/oracle/product/19c/db

"/bin/sh -c 'cd /u01/app/oracle/product/19c/db; ORACLE_HOME=/u01/app/oracle/product/19c/db ORACLE_SID=DB1 /u01/app/oracle/product/19c/db/OPatch/datapatch -prereq -verbose'" command failed with errors. Please refer to logs for more details. SQL changes, if any, can be analyzed by manually retrying the same command.

The reason? See the complete log about the failing step:

Executing command as oracle: 
 /bin/sh -c 'cd /u01/app/oracle/product/19c/db;ORACLE_HOME=/u01/app/oracle/product/19c/db ORACLE_SID=DB1 /u01/app/oracle/product/19c/db/OPatch/datapatch -verbose'
2020-09-02 16:26:56,362 INFO  [438] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'cd /u01/app/oracle/product/19c/db;ORACLE_HOME=/u01/app/oracle/product/19c/db ORACLE_SID=DB1 /u01/app/oracle/product/19c/db/OPatch/datapatch -verbose'
2020-09-02 16:26:57,662 INFO  [438] com.oracle.glcm.patch.auto.db.product.executor.GISystemCall - Is retry required=false
2020-09-02 16:26:57,662 INFO  [438] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 1
2020-09-02 16:26:57,662 INFO  [438] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - 
COMMAND EXECUTION FAILURE :
SQL Patching tool version 19.8.0.0.0 Production on Wed Sep  2 16:26:57 2020
Copyright (c) 2012, 2020, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/base/cfgtoollogs/sqlpatch/sqlpatch_25218_2020_09_02_16_26_57/sqlpatch_invocation.log

Connecting to database...
Error: prereq checks failed!
Database connect failed with: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4376
Additional information: 1275019259 (DBD ERROR: OCISessionBegin)

Please refer to MOS Note 1609718.1 and/or the invocation log
/u01/app/oracle/base/cfgtoollogs/sqlpatch/sqlpatch_25218_2020_09_02_16_26_57/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Wed Sep  2 16:26:57 2020

Clearly, the database is still in place.
As per MOS (ORA-01078 Can Not Delete Database From Dbca on Linux (Doc ID 1559634.1)) -> See the /etc/oratab!

Thing is, the doesn’t have the DB1 line. Also, all related files, logs directory structure, passwd, init, etc… all wiped out. What else?

Here is goes what seems Oracle forgot to tell:

[oracle@PRODB01 dbca]$ srvctl status database -d DB1
Database is not running.
[oracle@DMSDB1PA dbca]$ srvctl config database -d DB1
Database unique name: DB1
Database name: DB1
Oracle home: /u01/app/oracle/product/19c/db
Oracle user: oracle
Spfile: 
Password file: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services: 
OSDBA group: oinstall
OSOPER group: 
Database instance: DB1
Ohhh, that took me a while to realize, this was a Standalone server. Once understood, the fix is straight forward:
[oracle@PRODB01 dbca]$ srvctl remove database -d DB1
Remove the database dms? (y/[n]) y
[oracle@PRODB01 dbca]$

I hope this can buy you some minutes of MOS, in case you are googling it first.
Or save you in case you gave up MOS already.

Cheers!

LAOUC 2020: DevOps: PLSQL Development Best Practices for DBAs

Hi all!

This year I’ll be again speaking on the LAOUC (Latin America Oracle User Community) Groundbreakers Tour with a new topic: “DevOps: PLSQL Development Best Practices for DBAs“.

Different from the previous years, on this edition, we’ll have a consolidated agenda of conferences, instead of the tour on the continent. So we’ll have people speaker all sorts of languages all day long during the week of the conference, from August 17th to August 21th.

I’ll be speaking on the first day, as per states my card below.

Complete agenda: http://www.laouc.org/2020/06/29/oracle-groundbreakers-tour-2020-latam/

Details about my session: http://www.laouc.org/equipo/795/

Subscribe to my session! 

MatheusLAOUC

See you there!

grepOra 2.0 is Online! What’s New?

Hi all!

We are glad to announce our version 2.0 is online!

We have been working with the same blog layout since 2015, as being we HAD to make a few improvements to accommodate all changes we had on our structure and also some things to make YOUR experience even better.

We did try to keep the layout as clean as possible, as you are used to. In case you find any big issue, broken link or similar, let us know!

WHAT’S NEW?

1. The HOME page:

  • We made some quick buttons for the main features and we hope you like it!
  • We have now a quick panel with the latest posts on our main categories.

NewScreenshot 2020-07-28 às 15.12.46

2. The AGENDA

  • We know some of you get annoyed about the number of events we announce on the blog feed.
  • However, we know some of you come here specifically for this.
  • To accommodate both, we have set an AGENDA to have in a much more modern fashion the list of upcoming events from either the Community around the Globe and also Oracle official events and webinars.
  • We are still getting things around and you’ll keep seeing improvements in this regard soon!
  • You have now the LIST and SCHEDULE view too!

NewScreenshot 2020-07-28 às 18.16.42

NewScreenshot 2020-07-28 às 18.18.10

3. The FORUM

  • YES! You have now an open Forum to ask quick questions and have answers not only from the Authors but from the whole community. ENJOY and USE IT!
  • I have filled with some questions I heard recently and answers, just as an example. But we await YOUR interaction there!

NewScreenshot 2020-07-28 às 18.20.37

 

4. A LEARNING Space

  • As many of you asked, we started a learning space where we recommend some of the books and trainings we have read/tested/validated/like for your development.
  • Among the items we have on the roadmap is start including reviews on trainings and books.
  • By buying the books from the given links you’ll be helping grepOra to keep alive and growing. Thanks!
  • You can expect new sessions, recommendations and reviews soon!

NewScreenshot 2020-07-28 às 18.23.59

 

There are of course tons of quick and tiny improvements here and there all over the pages and spaces. We have rebuilt ALL the pages, like the MEMBERS page. But those are the 4 main improvements.

You can expect for more soon:

  • A Monthly Newsletter will start soon. Subscribe yourself and stay sharp!
  • Book and Trainings Reviews will start to be published soon.
  • The Agenda will become more integrated and have additional sources soon.
  • Expect for more surprises soon!

 

We are glad to have you here. This is all for YOU!

I want to take some space here to also thank Gabriel Morás and Jociéli Maleico for helping out on this reimagination process. Thank you!

Thank you and Enjoy!

 

 

GUORS Webinar: Automating Daily DBA Tasks with Ansible? [PT-BR]

Sem MIMIMI, você está realmente usando o que Ansible pra melhorar a sua eficiência ou é só pro chefe ver?

No próximo dia 23/7 as 19:00 o Ricardo Martins estará apresentando um case real de como utilizar o Ansible para automatizar as suas atividades.

É gratuito! Basta inscrever-se aqui: https://guorsiiencontro2020.eventize.com.br

WhatsApp Image 2020-06-23 at 18.35.47 (1)

Appreciation Note: YATRA 2020 Online + Tips and Trick from 20k+ Hours Oncall

Hi all!

First of all, it’s not over! We still have great sessions going on on the Yatra Online, check them HERE!

Before going through it, for the regular grepOra readers: you know I use to make reviews about events. But this is not a simple review, is an appreciation not for the AIOUG members and organization for such a great organization and engagement.

So this post is about my participation in this year’s edition, which has ended at this point. In topics:

1) Meet & Greet with Oracle ACE/GA/JC Community Session

It was a great pleasure to be speaking about the Oracle Community Programs with my fellow colleagues and friends Biju Thomas, Liron Amitzi and Basheer Khan last Saturday!

Thank you guys and AIOUG for the partnership and for the great discussions and motivation burst!

2) Tips & Tricks from 20K+ hours DBA oncall,

Let’s say the main item of this list, my session delivered yesterday. Such a great pleasure and a marvelous audience! We feel really glad and useful when we see a lot of questions and contributions. I’d like to say a warm and personal thank you for all the about 180 who attended this session and say it was a great experience for me!

My Slides are available here!
Along with the rest of my other conference slides, here.

About the questions: I was able to answer 10-12 questions during the conference session, but there were more not replied. I’m awaiting the organization to send me the list of all questions and I’ll make a new blog post dedicated to it. Stay sharp!

NOTE: I big thanks to Jure Bratina for pointing out an issue with the slides (regarding /*+ GATHER_PLAN_STATISTICS */). It is fixed!

3) Interview with Jim Grisanzio (Oracle Community)

Last but not least, I just had an interview with Jim Grisanzio for the YATRA Tour Online series. I can only say it was a pleasure and an honor to talk a bit with Jim on behalf of the Oracle Community in APAC about the ACE Program, career, Community in general, and about my session on this Yatra. Have a look and leave a LIKE if you enjoyed it!

 

Final Notes

I know Yatra means “Journey”. So what a great Journey we are having with all the awesome names and sessions, from which I could attend just a few because of my shift limitations. I’m sure we’ll all meet and repeat this experience in person really soon!

In this while, I’d like to use this opportunity to thank:

  • Sai Penumuru and Aishwarya Kala on behalf of the AIOUG team: Such a great event you are hosting! Congratulations on your organization, professionalism, and quality material and speakers. Well done!
  • All ACE Community to allow us to be in such great events, and specifically Biju, Liron and Basheer for the good and friendly conversation on Saturday.
  • Jim and the great conversation as you can see in the video above.
  • Especially and mostly to the whole Indian Community for the great reception and engagement. Thank you and congratulations!

Some memories I keep from this Yatra’s edition:

YATRA 2020: Meet & Greet with Oracle ACE/GA/JC Community Session – It’s Tomorrow!

Hi all,

Just a reminder to you all about tomorrow’s session!

I’ll be sharing about the community engagements and career with the friends Biju Thomas, Liron Amitzi and Basheer Khan on the 3rd and last session of Meet, Greet, Chat with Oracle ACE Directors and Groundbreaker Ambassadors at Yatra 2020 Online!

Click on the image below to register and attend or through this link: https://www.aioug.org/ogyatra-2020?day=11#

yatraonline

See you there!