Hi all,
I want to first say thanks for Gilson and his whole team in GoldenGateBR for the very pleasant time on last Thursday’s conversation!
We spoke a bit of everything, but mostly over career, my historic on the community, and of course the grepOra blog.
Also many thanks for the ones who could connect and stay connected up to the end of the video. Here is the recording, if you will (it’s in Portuguese!).
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:
Some words about the parameters settings:
(images from https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2)
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!
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:
Generic Case (if this is the first blog you open about the subject):
[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
cd $ORACLE_HOME/bin && chmod 6751 oracle
kfod status=TRUE asm_diskstring='/dev/asm*' disk=ALL
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!
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!
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/
See you there!
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!
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:
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!
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
Passando pra lembrar do Webinar no canal do GUOB dessa semana: “Um DBA Oracle olhando para o Apache Cassandra“, com o Adriano Bonacin. Imperdível!
Onde? Canal do GUOB no Youtube!
Quando? Nesta quarta-feira (22/7) as 20:00!
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!
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:
Some memories I keep from this Yatra’s edition:
Hi all!
Tomorrow I’ll be at the Yatra 2020 Online presenting my session Tips & Tricks from 20K+ hours DBA oncall, and sharing the day sessions with some friends from the community as per below.
Click on any of the images to register, or here: https://www.aioug.org/ogyatra-2020?day=13
My Session Card:
Speakers of the day:
