Meaning of Oracle “I” “G” and “C”

Meaning of “I” and “G” in Oracle and the Release format number of version

Meaning of I in Oracle:

The Oracle version starting of I. The starting in 1999 with version 6i, 8i and 9i, I signify “Internet” means stands for “Internet” and Oracle added the “I” to the version name to reflect support for the Internet with its built-in Java Virtual Machine (JVM). Oracle 9i added more support for XML in 2001.

Meaning of G in Oracle:

The starting in 2003 with version 10g and 11g, G signifies “Grid Computing” with the release of Oracle10g in 2003. Oracle 10g was introduced with emphasis on the “g” for grid computing, which enables clusters of low-cost, industry standard servers to be treated as a single unit. Upgrade Enterprise Manager 10g Grid Control Release 4 (10.2.0.4.0) or higher to Enterprise Manager 11g Grid Control Release 1 (11.1.0.1.0).

Meaning of C in Oracle:

In the Oracle Database 12c the "c" stands for "cloud". In addition to many new features, this new version of the Oracle Database implements a multitenant architecture, which enables the creation of pluggable databases (PDBs) in a multitenant container database (CDB). The Oracle Database 12c is a high-performance, enterprise-class database. Oracle released Oracle Database 12c into general availability July 1, 2013. According to Oracle, this is "the first database designed for the cloud." Oracle Database 12c also introduces 500 new features to the database, most notably pluggable databases and multitenant architecture. The Oracle Database 12c release 12.0.1.2 also features the Oracle Database 12c In-Memory, an optional add-on that provides in-memory capabilities. The in-memory option makes Oracle Database 12c the first Oracle database to offer real-time analytics.

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

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.

Oracle 11g architecture Quick Reference

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.

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)

How to get PASSWORD changed time for an Oracle user??

SELECT name, ctime, ptime
FROM sys.user$
WHERE name = 'TEST_SUH';