Friday, July 24, 2020

CORRUPTION DETECTED: In redo blocks starting at block in dataguard


If this error comes in dataguard (physical standby) database, oracle will automatically delete the corrupted archived log file and then we would require to copy the affected archived log from the primary site and so that managed recovery could proceed. I was thinking that oracle should automatically fetch the affected/corrupted archived log itself from the primary site, but then I realized the reason of not happening this automatically and I will explain the reason later.
Once this type of corruption is detected, following type of message is shown in the alert log file.

Friday, July 17, 2020

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

For me, the reason of this error was that I tried to open a standby database and then tried to start the managed recovery in order to utilize active dataguard feature. First, I cancelled the managed recovery, then opened the standby database, and as soon as I execute the command to start managed recovery process, ORA-01093 popped up; as you can see below.

Sunday, June 28, 2020

ddl_lock_timeout to avoid ORA-00054

I have a previous article available here that explains about ORA-00054 and the reason of this error, and alto the enhancement starting 12c to avoid this error. In this article I would further explain ORA-00054 and how to use an initialization parameter DDL_LOCK_TIMEOUT to avoid this error. Instead of returning this error because of a lock on the object, your DDL would keep waiting for the locks to be released and get executed automatically when object is available for the DDL operation.

Sunday, June 7, 2020

ASM Disks not Reachable After Reboot - Manually Loading ASM Driver

After a system reboot if you see ASM disks are not available, and scanning ASM disk returns “Unable to instantiate disk”, as follows, it could be because oracleasm driver was not loaded and hence ASM did not work as expected. As a result, ASM disks will not be visible to ASM instance and ASM diskgroups would not mounted. Following is the example output of if you execute “oracleasm scandisks” command.

Monday, May 18, 2020

Not All Endpoints Registered


If issuing “crsctl status resource” command show the status of listener as “Not All Endpoints Registered”, it is usually because of contradictions in the Listener settings. It means that probably you have a manually setup listener (by editing listener.ora file) with different settings than registered with the CRS/HAS. Or, there could be multiple listeners configured from multiple oracle homes – one from Grid Infrastructure home, and other listener with same configuration running from other home and started manually (using lsnrctl command).

Tuesday, May 5, 2020

DBMS_AW_EXP: BIN$ZRGxDTT9TumIWFOmM0qIHQ==$0 not AW$

If you see any similar messages during expdp, you may ignore these messages. Alternatively, you may purge the recyclebin and initiate the expdp again. Issue “purge dba_recyclebin” from SQLPUS. The messages displayed in expdp log are as follows.

Saturday, May 2, 2020

Initialization parameter setting back to original value


I faced a very strange situation whereby I was setting my log_archive_dest_1 parameter using ALTER SYSTEM command with SCOPE=BOTH, which meant that this parameter value change should have been consistent across the instances reboot as this change was also being made in the spfile (SPFILE was in use for that database/instance) as well as in memory. However, it was not happening actually. After every instance restart, the value of this parameter would again set to the old value and this was causing problems for us.

Saturday, March 28, 2020

ORA-00972: identifier is too long


In Oracle, the maximum length for the name of an object (Table, View, Procedure, Package, Function, Materialized View etc.) is 30 characters. Likewise, the name of a column of a table or view should also not exceed 30 characters. In the following I am providing a few examples where we will see ORA-00972 error.

Friday, March 20, 2020

SQL Plan Management and SQL Baseline

Before you read this article, I would suggest you to read my articles about bind variable peeking and adaptive cursor sharing.
Starting 11g release, adaptive cursor sharing makes optimizer to generate and select multiple execution plans based on the bind variable values passed during the execution. Oracle also introduced SQL Plan Management feature starting 11g whereby the history of plans of an SQL is maintained in SQL Baseline and out of which one or more plans can become “accepted” and can be used for that SQL. This is our choice to make plan(s) “accepted” if think that plan is optimal. We may ignore sub-optimal plans. In this will explain SQL Plan Management using an example.

Friday, March 6, 2020

gc buffer busy acquire and gc buffer busy release


These two wait events are similar to ”buffer busy wait” that you might have observed which is recorded when a session tries to access a data block/buffer which is already being accessed by another session that is connected to the same instance and buffer is also in the current instance’s buffer cache. In RAC environment, if a session tries to read a buffer from a remote instance’s buffer cache but the buffer is already being read by a different session, a “gc buffer busy acquire” wait event is recorded. If a session tries to read a buffer from the buffer cache of local instance, but buffer is already being read into remote instance’s buffer cache from local cache by a session connected to that remote instance, a “gc buffer busy release” wait event is recorded.  

Popular Posts - All Times