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.

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.

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.

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.

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.

enq: TX - row lock contention - Row Lock Wait

This wait event appears if continuous row locking and session blocking is happening in the in the database. If your end user complains about the application hang or slowness, excessive row locking could be one of the reason of it. Sometime an ad-hoc process executes for purging of data or bulk update of data that would cause huge locking whereby application users may face application slowness or hang. To identify the problem, you can generate AWR report to find out what is going on and find out what is causing locking. Following is an image of AWR report that shows row locking (eventually causing the sessions blocking) is responsible for more than 90% of database time.

ORA-16053: DB_UNIQUE_NAME my_dr, is not in the Data Guard

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST DB_UNIQUE_NAME=my_d, valid_for=(ALL_LOGFILES, ALL_ROLES)' sid='*'; alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST DB_UNIQUE_NAME=my_dr valid_for=(ALL_LOGFILES, ALL_ROLES)' sid='*' * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-16053: DB_UNIQUE_NAME my_dr, is not in the Data Guard Configuration

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST DB_UNIQUE_NAME=my_dg,valid_for=(ALL_LOGFILES,ALL_ROLES)' sid='*'; alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST DB_UNIQUE_NAME=my_dg,valid_for=(ALL_LOGFILES,ALL_ROLES)' sid='*' * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

Pluggable Database Cloning on Local Host (Local CDB)

 Cloning a pluggable database locally or remotely has become a routine task for DBAs. Different DBAs use different methods to clone a pluggable database. The cloning may be needed on the local host where source pluggable database exists, or requirement could be to clone it on a different host. In this article, I will explain how to clone a pluggable database on the local host (local container database). Click here to learn about how to clone a pluggable database from a remote host using database link.

Pluggable Database Remote Cloning Using Database Link

Cloning a pluggable database locally or remotely has become a routine task for DBAs. Different DBAs use different methods to clone a pluggable database. The cloning may be needed on the local host, where source pluggable database exists, or requirement could be to clone to a different host. In this article, I will explain how to clone a pluggable database remotely using a database link. Click here to learn about how to clone a pluggable database on the local host (local container database).