Thursday, April 28, 2016

How to find failed login attempts??

Enable auditing for failed login attempts
-- # 1 - Modifiy initialization parameters
alter system set audit_trail=db scope=spfile;
-- # 2 - Restart the database 
shutdown immediate
startup
-- # 3 - Enable auditing for failed login attempts
audit create session whenever not successful;
-- # 4 - Query the dba_audit_trail view to see failed login attempts 
select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail 
where returncode > 0

Monday, April 25, 2016

ORA-00020: maximum number of processes (5000) exceeded

Symptom
After an ORA-00020 "maximum number of processes (%s) exceeded" occurs ... no new connections (even SYSDBA or SYSOPER) may be made to the instance.

Solution
An ORA-20 "maximum number of processes (%s) exceeded" occurs when the number of OS processes for the instance exceeds the PROCESSES parameter 
There are a couple of 'workarounds' for this issue
WORKAROUNDS:
UNIX: kill -9 one or more of the client connections 
WINDOWS : using ORAKILL ... kill one or more of the client connections 
* Use an existing connection with sufficient privileges (if one is logged on) to view V$SESSION / V$PROCESS and 
         alter system kill session 'SID, SERAL#';
In my case I don't have any active sessions with sufficient privileges
As of 10.1.x, there is a new option with SQL*Plus to allow access to an instance to generate traces.
You can shut abort and then restart your database, instead of rebooting your server.
This is the ultimate chance before reboot. Before doing this, consider disconnecting or killing some user session to get a regular sqlplus / as sysdba.

Wednesday, April 13, 2016

How to find all child tables of a table in Oracle??

Sometimes it’s useful to find out all the dependent/child tables for a particular table. The SQL statement below will work for an Oracle database:

SELECT
a.owner,a.table_name, a.constraint_name
FROM
sys.all_constraints a,
(SELECT owner,constraint_name from sys.all_constraints
WHERE
owner = 'OWNER' and
table_name = 'TABLE_NAME' and
constraint_type in ('P','U')
) b
WHERE
a.constraint_type = 'R' and
a.r_constraint_name = b.constraint_name and
a.r_owner = b.owner

Where  OWNER is the owner/schema to which the table belongs andTABLE_NAME is the table to be reported on.

Sunday, April 3, 2016

HUNG IN AUTO SQL TUNING TASK

Issue-
In 11.2.0.3 database , we get alert in EM related to metric “Generic Operational Error” or "Generic Operational Error Status ". Occasionally when running Automatic SQL Tuning the following messages may appear in the alert log:
Error-
These messages indicate that an auto kill of a "hung"/long running tuning task has taken place.

This is a protective measure purely to avoid the task from over-running its time limit because of a single task and  protects a the system from harm caused by such over-running.

Since this is an expected activity to prevent over-running there is no fix as such.  
Instead as a workaround, you could:
FIX 1 :Give the task more time to complete (the following example would set the per statement timeout to 6 hours (21600 seconds)):
BEGIN
  DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 21600);
 END;

/
Note
If you increase the per-statement time limit (LOCAL_TIME_LIMIT) then you need to stay within the bounds of the time limit for the entire task (TIME_LIMIT). The duration of the TIME_LIMIT parameter must be at least equal or greater than the LOCAL_TIME_LIMIT. When the maintenance window closes the SQL Tuning Advisor is stopped.
FIX 2 :
Disable the automatic tuning process and the messages will not appear anymore (though obviously the auto tuning will also no longer occur - you could manually execute the job as desired later). 
To disable the job:
connect / as sysdba

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
To re-enable in future:
connect / as sysdba

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;

/
Source : 
How to Avoid or Prevent "Process 0x%p appears to be hung in Auto SQL Tuning task" Messages (Doc ID 1344499.1)