TNS-12560: TNS:protocol adapter error

This is one of the most common and generic errors you may face while trying to connect with the database. There could be a lot of reasons for this. To troubleshoot this, you may want to go through following checklist

Check your TNS entry in TNSNAMES.ORA file, if it is set correctly and there is no syntax error and it is pointing to correct server and instance

ORA-24247: network access denied by access control list (ACL)

This error message means that no access control list has been assigned to the host you (your application) are trying to access, or no required privileges have been granted to the user by adding user to the ACL. Following is an example executed on 12c (12.1.0.2) to show how we create ACL and how to test it.  

Using PGA_AGGREGATE_LIMIT to Limit PGA Memory

Before 12c, there was no hard limit to restrict PGA memory usage by an Oracle session. It means that if a session keeps growing in memory, it would eventually allocate all available memory which would lead to a system slowness/hang and eventually system crash, I have discussed this scenario here.

Generating Bulk of Data for Testing Purpose

Sometimes we need to generate a huge amount of data to perform some testing. Type of data to be generated depends on the scenario you want to test. I will show here a very simple example to create a very huge table. I usually use it if I need to have a huge tablespace or table to perform some test. For example, to test backup

Changing AWR Snapshot Retention and Interval

By default AWR snapshot interval is set to 60 minutes and retention of snapshots is set to 8 days. For better and precise investigation of problems, I recommend to use an interval of 15 minutes and retention of 35 days. If database is huge in size and operations, also RAC, your SYSAUX tablspace would certainly grow in size with these settings. I have observed size of SYSAUX tablespace for a 3 nodes RAC database, around 14G with aforementioned settings.

Oracle Deferred Segment Creation

Starting 11g Release 2, we can create a table/index/LOB without space allocated to it (no segment creation) until first row is inserted into the table. Init parameter DEFERRED_SEGMENT_CREATION is used to setup the default behavior. By default value of this parameter is set to TRUE, which means that whenever a table will be created,

Checkpoint not Complete

This is one of the most common messages a DBA could see in alert log file of a database. Exact message should be similar to the following

Wed Mar 04 12:02:01 2015 Thread 1 cannot allocate new log, sequence 201 Checkpoint not complete

Finding Sessions with High CPU Usage

If database server CPU usage is showing 100%, or high 90%, DBA needs to find out which session is hogging the CPU(s) and take appropriate action. I will explain in the following that how can we find out the session(s) which are excessively using CPU.

ORA-03135: connection lost contact

This error may be accompanied by ORA-03113: end-of-file on communication channel. This actually means that your session has been dropped and there could be different reasons which might have caused this. Most commonly; a firewall between client and server could have caused this session/connection drop, if firewall senses some session to

Testing RMAN Restore

Starting 10g, we can test our backups whether backups are valid and can be used for a successful restore/recovery of the database or not. We can do so by using “RESTORE VALIDATE” command from RMAN. RESTORE VALIDATE command would simulate a restore of the backups by reading backup pieces. If it completes successfully, it would mean that backup is good and can be used for restoration and recovery of database in case of media failure or a disaster.