Insane DBA...

Listener does not currently know of SID given in connect descriptor.  I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. After upgrading Oracle grid infrastructure from 11.2.0.4 to 19c; we have detected that +ASM and +APX(ASM Proxy) instances used for advm are not registered with listener and OEM target status shows down.   They were only accessible from the private network (192.168.*.*). We have checked configuration attributes for the ASM instance with the srvctl command. The easy and first step to take was to set it with "srvctl modify" command. Oops something went wrong and that is not possible.  It looks like this command is not working anymore, searched docs.oracle and checked Oracle support but no easy way out. Checked ASM proxy instance local_liste...

 Too many parse errors - how much do you mean? I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. SQL syntax errors are always normal and commonly dealing with them is up to software developers.  But sometimes, they might bother database administrators too. When an SQL statement is syntactically (e.g., syntax error) or semantically (e.g., projection of a nonexistent column) incorrect, its processing fails at parsing stage and it never gets executed. If that happens too often, it can have a negative impact on overall database performance. With the 12.2 release, these failing SQL statements are recorded in alert.log as below if they are called excessively. In the above example, the "SELECT DU...

Is it possible to get credentials from crs? I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.      In the last two posts, I have been talking about the catastrophic situation we experienced, which was triggered by mirrored disk failures. Up until now, we restored OCR config and recreated the lost ASM disk group which was hosting OCR before, but we could not start the CRS on more than one node.      We got the error "CRS-5019: All OCR locations on ASM disk groups [DATA], and none of these disk groups are mounted". We recreated the asm password file.  (We should have restored it from the offline disk group.) Recreating it was not enough. There were some missing internal users. According to " Doc ID 2341753.1, The users used in Flex ASM ", CR...

OCR lost. Where is OCR backup? On ASM diskgroup. I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. After Mirrored Disk Failure in Normal Redundancy Mode, CRS was down. We could not take the faulty disks and one disk group (DATA) online again and decided to restore OCR config and change VOTING disk location from DATA to RECO. Started CRS in exclusive mode and searched for backup locations, unfortunately we had no backups in local file system. OCR backups were on one of ASM disk group and this disk group could not be online. We mounted that disk group in restricted mode and tried copying the latest OCR backup to a local directory with the commands below, but could not achieve. We searched Oracle support and found " Doc ID ...

 IRON MAN  WAS DOWN I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. Lately, in our DR (Disaster Recovery) site, we have experienced two mirrored disk failures in normal redundancy mode which ended up with recreating of dataguard databases.  I will try to explain our problem in detail.     Our databases were down. CRS state was offline. ASM was down. Iron Man was down. We started diagnosing the issue with manually starting up the ASM instance on one node. We have 3 disk groups. 2 of them got mounted, but  one disk group (+DATA) could not get mounted. This disk group (+DATA) was holding ocrconfig and serving as voting disk.  This is the command and output.  We checked all ASM instances alert.logs to clarify the chronological order of eve...

 Just another manic magic Monday. I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. After a great weekend, we came to the office and performed our daily health checks like every Monday. One of our storage servers (cell) of Exadata X2-2 X4270 M2 had lost 11 ASM disks out of a total of 34 ASM disks. We struck it lucky, all databases were still up despite all the losses. Let's examine what happened to our cell server. When I checked the mailbox, I saw an alert mail from the problematic cell stating that "Disk controller was hung. Cell was power cycled." It looks like the cell disk controller was not performing well (maybe a bug or a peak moment) and forced the server to reboot. But normally reboots do not end up with disk losses. I started by checking the...

What are these strange tables starting with MD*?  I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. Can I drop them?  I will answer first:      Yes, You can drop some of them if your Oracle DB version is 12cR2 or later.  But which ones? There were over 3000 tables starting with 'MD*' letters in one of our production database.  I knew that those tables are related with Spatial indexes. But that was a huge amount. So I took a deep dive into the spatial indexes.  For each spatial index created, one table named like "MDRT_#" is created also. There is a one to one relationship (except the partitioned o...

Be CAREFUL! Before Applying Release Update I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. After applying release update 19.13 to our standby site cluster, consisting of two physical machines (non Oracle-engineered), we started to experience Node Evictions and Reboot Problems. We immediately started to search for the root cause of the issue and followed some steps to make them stable again before applying RU 19.13 to our production sites. * We chose a sample that is reflecting the problem. On Feb 22, at 13:35, the host machine (bltdb02) rebooted. * We looked at the lastgasp log files to get the details why the node got rebooted. Grid cssdagent or cssdmonitor can record node reboots here...