Oracle 12c CDB database crash with ORA-600 INTERNAL_PLAN

I have installed Oracle 12c recently on linux x86 64bit linux server.  This is using new Container architecture (i.e CDB)

I connect to server and started using SQLplus ,whithin one minute of database open, i got connect lost error message,  when try to connect again, found database instance is no more, it was crashed.

I tried one more time to startup, just to see the behaviour as same results, it got crashed again as soon as database opened.

When checked the Alert.log found the following ORA-600 causing databse crash.

###############
Setting Resource Manager plan SCHEDULER[0x4459]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_dbrm_3506.trc  (incident=16873) (PDBNAME=PDB$SEED):
ORA-00600: internal error code, arguments: [kgskigetelt_subplan], [INTERNAL_PLAN], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdb01/CDB01/incident/incdir_16873/CDB01_dbrm_3506_i16873.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 18 01:33:58 2015
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_dbrm_3506.trc:
ORA-00600: internal error code, arguments: [kgskigetelt_subplan], [INTERNAL_PLAN], [], [], [], [], [], [], [], [], [], []
Fri Sep 18 01:33:58 2015
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_dbrm_3506.trc:
ORA-00600: internal error code, arguments: [kgskigetelt_subplan], [INTERNAL_PLAN], [], [], [], [], [], [], [], [], [], []
Fri Sep 18 01:33:58 2015
USER (ospid: 3506): terminating the instance due to error 56710
Fri Sep 18 01:33:58 2015
System state dump requested by (instance=1, osid=3506 (DBRM)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_diag_3504_20150918013358.trc
Fri Sep 18 01:33:59 2015
Dumping diagnostic data in directory=[cdmp_20150918013358], requested by (instance=1, osid=3506 (DBRM)), summary=[abnormal instance termination].
Fri Sep 18 01:33:59 2015
Instance terminated by USER, pid = 3506


Problem 

ORA-600:[kgskigetelt_subplan] . is due to bug and as of date(18 Sep 15) no patch or workaround available for this bug.

Bug 20479923 : ORA-00600:[KGSKIGETELT_SUBPLAN] FOLLOWED BY INSTANCE CRASH

ORA-01033 when connecting to PDB, Pluggable Database ORACLE initialization or shutdown in progress

ORA-01033: ORACLE initialization or shutdown in progress 

ORA-01033: ORACLE initialization or shutdown in progress
01033. 00000 -  "ORACLE initialization or shutdown in progress"
*Cause:    An attempt was made to log on while Oracle is being started up
           or shutdown.

*Action:   Wait a few minutes. Then retry the operation.

Upload was successful but collections currently disabled - disk full

oracle@dbserver1:/u01/app/oracle/diag> emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 11.1.0.1.0
OMS Version       : 11.1.0.1.0
Protocol Version  : 11.1.0.0.0
Agent Home        : /u01/app/oracle/product/agent11g
Agent binaries    : /u01/app/oracle/product/agent11g
Agent Process ID  : 25212
Parent Process ID : 25185
Agent URL         : https://dbserver1.oraukint.local:3872/emd/main/
Repository URL    : https://oemserver.orauk.local:1159/em/upload
Started at        : 2014-10-16 15:01:19
Started by user   : oracle
Last Reload       : 2014-10-16 15:16:26
Last successful upload                       : 2014-10-16 15:12:51
Total Megabytes of XML files uploaded so far :    25.58
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :     3.97%
Collection Status                            : Disabled by Upload Manager
Last successful heartbeat to OMS             : 2014-10-16 15:17:58
---------------------------------------------------------------
Agent is Running and Ready
oracle@dbserver1:/u01/app/oracle/diag> emctl  upload agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error: Upload was successful but collections currently disabled - disk full

oracle@dbserver1:/>cd $ORACLE_HOME/config

grep -i  DiskUsedPct emd.properties

UploadMaxDiskUsedPct=98

UploadMaxDiskUsedPctFloor=98

oracle@dbserver1:/u01/app/oracle/product/agent11g/sysman/config> bdf .

Filesystem          kbytes    used   avail %used Mounted on

/dev/vg01/lvol1    52424704 45984123 6440581   99% /u01

Solution:-

- As we can clearly see disk utilization is above the defined limit in emd.properties.

Clear space from /u01 filesystem where agent is installed.

restart agent 

emctl stop agent

emctl start agent.

oracle@dbserver1:/u01/app/oracle/product/agent11g/sysman/config> emctl stop agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

Stopping agent ... stopped.

oracle@dbserver1:/u01/app/oracle/product/agent11g/sysman/config> emctl start agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

Starting agent ............................. started.

oracle@dbserver1:/u01/app/oracle/product/agent11g/sysman/config> emctl status agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

---------------------------------------------------------------

Agent Version     : 11.1.0.1.0

OMS Version       : 11.1.0.1.0

Protocol Version  : 11.1.0.0.0

Agent Home        : /u01/app/oracle/product/agent11g

Agent binaries    : /u01/app/oracle/product/agent11g

Agent Process ID  : 350

Parent Process ID : 325

Agent URL         : 

https://dbserver1.oraukint.local:3872/emd/main/

Repository URL    : 

https://oemserver.orauk.local:1159/em/upload

Started at        : 2014-10-16 15:33:17

Started by user   : oracle

Last Reload       : 2014-10-16 15:33:17

Last successful upload                       : 2014-10-16 15:34:23

Total Megabytes of XML files uploaded so far :     0.15

Number of XML files pending upload           :        0

Size of XML files pending upload(MB)         :     0.00

Available disk space on upload filesystem    :    26.32%

Last successful heartbeat to OMS             : 2014-10-16 15:34:14

---------------------------------------------------------------

Agent is Running and Ready

12c Database is it any different when Install ????

Hi All,

12c database is out now from many months and today thought to install the 12c database , just to see how different it look from installation point of view but observed the following so far.

* Installation is exactly same as 11g (only Logo changed to 12c) , Last page got option of clicking Edit to change any configuration you don't like (new )
* New OS group introduced , to make DBA's life more complicated until we all understand what to use them for.
* QOPatch directory scared me, thinking patching i have to learn again from scratch but no Patching is same as before ( Same OPath, here you need 12.0.1.x.x version) , still need to figure out what this QOPatch is used for.


I know there is Long list of changes(New Features) in 12c specially Plugable database etc, i will be reading them and will share with everyone.

Operating system user Groups in 12c Plugable database

Oracle 11g  we saw new OS group called SYSASM , now to add more flexibility (and also complexity ) Oracle introduced few moreOS group for Oracle OS user when working with 12C database.

New Groups for 12c database Installation : OSBACKUPDBA, OSDGDBA , OSKMDBA

The OSDBA group (typically, dba) 

You must create this group the first time you install Oracle Database software on the system. This group identifies operating system user accounts that have database administrative privileges (the SYSDBA privilege). The name used for this group in Oracle code examples is dba.

The OSOPER group for Oracle Database (typically, oper) 

This is an optional group. Create this group if you want a separate group of operating system users to have a limited set of database administrative privileges (the SYSOPER privilege). This group cannot directly connect as SYSOPER, unless explicitly granted. However, they have the privileges granted by the SYSOPER privilege. By default, members of the OSDBA group have all privileges granted by the SYSOPER privilege.

The usual name for this group is oper.

The OSBACKUPDBA group for Oracle Database (typically, backupdba) 

Create this group if you want a separate group of operating system users to have a limited set of database backup and recovery related administrative privileges (the SYSBACKUP privilege). The usual name for this group is backupdba.

The OSDGDBA group for Oracle Data Guard (typically, dgdba) 

Create this group if you want a separate group of operating sytem users to have a limited set of privileges to administer and monitor Oracle Data Guard (the SYSDG privilege). The usual name for this group is dgdba.

The OSKMDBA group for encyption key management (typically, kmdba) 

Create this group if you want a separate group of operating sytem users to have a limited set of privileges for encryption key management such as Oracle Wallet Manager management (the SYSKM privilege). The usual name for this group is kmdba.

APEX LOGIN

http://apex.oracle.com/pls/apex/f?p=6368811

How to Flush Single SQL Plan out of Shared Pool

select ADDRESS, HASH_VALUE from V$sqlarea where SQL_ID ='sqlid';



BEGIN
DBMS_SHARED_POOL.PURGE('C0000008F304C620',2219163920,'C');
END;
/



Set linesize 120

select 'on Inst '||inst_id||'   execute DBMS_SHARED_POOL.PURGE('||''''||ADDRESS||','|| HASH_VALUE ||''''||','||''''||'C'||''''||');' "Execute Below to flush PLAN" from GV$SQLAREA where SQL_ID like 'adpk6gy24bh8h'
/

Execute Below to flush PLAN
----------------------------------------------------------------------------------
on Inst 1   execute DBMS_SHARED_POOL.PURGE('C0000008F304C620,2219163920','C');
on Inst 2   execute DBMS_SHARED_POOL.PURGE('C0000008E71CE550,2219163920','C');