Wednesday, September 28, 2022

ORA-19821: an intentionally corrupt log file was found

 If this error appears while opening your database, it means that an instance crash happened before the database start and instance recovery needs redo data to be applied to the database, but redo data is not available in the redo log file. The reason of missing data is that redo data was not written to the log file when database instance crashed. The data flushing to the redo logs was probably stopped because of setting parameter “_disable_logging” to TRUE to speed up the data loading. Alert log file would show entries similar to the following.

Wednesday, August 24, 2022

ORA-00093: pga_aggregate_limit must be between...

Starting 18c, pga_aggregate_limit setting has also been made dependent on PROCESSES initialization parameter. You might face ORA-00093 during startup of instance if value of pga_aggregate_limit is below the required value. For my 19c instance I faced same issue as follows.

SQL> startup nomount

ORA-00093: pga_aggregate_limit must be between 45000M and 100000G

ORA-01078: failure in processing system parameters

SQL>

Monday, August 22, 2022

Direct Path Read and Full Table Scan

 Before reading this article, you might want to read this article about poor buffer cache hit ratio because of full table scans. In this article I will explain a feature of Oracle introduced after 10 to avoid flooding of database buffer cache because of full table scans. Database block is the unit of IO in Oracle, which means that whenever some data is read from the disks, one copy of data block is put in the buffer cache so that this data can be reused later to avoid reading same block from the disk in future. After that, the copy of data is used for user’s SQL. 

Thursday, July 21, 2022

Finding Size and Number of Rows in Each Table of a Schema

 Sometimes DBAs need to fulfill requirements form development teams or the customer and provide different kind of information regarding database. One of them is to find size of the tables in a schema and number of rows in the tables. This information my be required for future planning or capacity sizing, or this could also be used during investigating a performance issue. There could be different ways of fetching this information; one of them that I feel quite simple is being explained here.

Saturday, May 14, 2022

Low or Poor Buffer Cache Hit Ratio and Full Table Scans

 Normally DBAs do not much feel bothered by poor buffer cache hit ratio especially in data warehouse environments where full table scans may be quire frequent and acceptable. However, if you have OLTP environment, I still believe that buffer cache hit ratio should be taken care of and DBAs should try to keep it as high as they can. In OLTP environments, full table scans would cause cache hit ratio to be low because frequent full table scans of different tables would cause entire tables to be read in to buffer cache even if only a few block are needed for processing.

Sunday, April 17, 2022

ORA-16012: database identifier mismatch

If this error message is appearing in the alert log file, most of the time it is related to log_archive_dest_1 for standby database’s local archive generation and/or log_archive_dest_2, that points to the primary database. You should check if every parameter is set properly and TNS string used to point to the primary database is set properly. For further information, you can see this article that explains how to properly configure dataguard.

Friday, March 11, 2022

PX Deq: Signal ACK RSG

PX Deq: Signal ACK RSG wait event does not have much information available even on My Oracle Support portal. I was not able to find much information about this. I noticed this wait event at the top of wait events in my AWR report as can be seen bellow that made me do some investigation. First and the foremost, this wait event is related to parallel execution of SQLs.

Friday, January 28, 2022

ORA-15073: diskgroup DATA is mounted by another ASM instance


SQL> drop diskgroup data;

drop diskgroup data
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15073: diskgroup DATA is mounted by another ASM instance

If you are not able to drop a diskgroup, and ORA-15039 is returned along with ORA-15039: diskgroup not dropped, it means the diskgroup needs to be dismounted on all instances except current instance where you are issuing DROP DISKGROUP command.

Popular Posts - All Times