Oracle listener spawning child process

Recently, faced an issue in one of our oracle databases running on 12.1, that the listener was spawning a child process every few minutes. Also, we could observe high listener latency of ~5000ms.

At first, stopped the listener and started it again, but it did not help at all. 

Then, checked the listener log and saw connection coming to the order of 100times the normal connections.  Got the application bounced. It helped temporarily and after sometime, got the same problem back.

Finally we got the root cause -the sqlnet.ora was somehow renamed to sqlnet.or and this was causing connections. Once the file was renamed to sqlnet.ora, everything went fine

How to set up EC2 instance in AWS Cloud

Today, I am going to show how to set up EC2 instance on  AWS cloud.


Login to AWS console and go to Services> Compute >EC2

Next, choose the  OS/platform you want to select. I selected Ubuntu which is also free tier eligible.

Next, choose the hardware type-cpu, memory, storage etc

Next, choose other details, like security groups, choose a new key pair if you haven't created.Download your private key here -this is important.It will be in .pem format. Add tags etc and finally review the configuration

Finally, launch the instance. It will take a while-couple of minutes and then instance will come up

Finally your instance us up! and you can get the details-public dns, public IP and other details. 

Now before connect to this instance using putty, Use puttykeygen to convert .pem into .ppk. 

Load . pem and save the private key-.ppk.  Now connect to putty using 

ubuntu@"Public DNS" as give above, remember to give the above .ppk in Connection >SSH >Auth in putty.

In Oracle 12c Sql tuning advisor is not running even though its enabled

Faced a strange issue in oracle 12c. In automated maintenance task, sql tuning advisor was enabled. However, it was still not running. Validated  dba_autotask_operation, that  "sql tuning advisor" is enabled. However, view -dba_scheuler_job_run_details and dba_autotask_task showed that it was NOT running. 

This is a  bug in 12c.  

As per metalink note, either we have to apply a patch"20540751" or as a workaround, run @?/rdbms/admin/execsqlt.sql to create the scheduler program "'AUTO_SQL_TUNING_PROG" that was dropped. This program should then show in in dba_scheduler_programs.

References:
Automatic SQL Tuning Task Not Executing after upgrading to 12.1.0.2 (Doc ID 2171154.1)