Oracle Histograms Simplified: Part 2

In my previous post Oracle Histograms Simplified: Part 1, I focused on what is histogram & why it is used. In this post I will explain different ways to create & drop histograms.

HISTOGRAM CREATION:

Creation of histogram is always depends on parameter method_opt that you specify during gathering statstics.

Typical syntax for method_opt is as follows:

First column part of clause has different options as given below:

1. ALL COULMNS :

As name suggests oracle considers all columns on table for histogram creation.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>’,TABNAME=>'<TABLE_NAME>’,METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’,CASCADE = TRUE);

2. ALL INDEXED COLUMNS :

Oracle will consider only columns with indexes for histogram creation.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO',CASCADE = TRUE);

3. ALL HIDDEN COLUMNS :

Oracle will cosider only hidden columns for histogram creation.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL HIDDEN COLUMNS SIZE AUTO',CASCADE = TRUE);

4. COLUMNS <COLUMN NAMES SEPERATED BY COMMA> :

Oracle will consider only given columns for histogram creation.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR COLUMNS COLUMN1,COLUMN2 SIZE AUTO',CASCADE = TRUE);

Moving to next portion of syntax, which lets you controls histogram creation and its type.

Size clause has different options as given below:

1. INTEGER:

You can specify number of buckets. It must be in range of 1 to 254. 1 denotes no histogram with base column statistics (i.e. minimal information required for the Optimizer) while 254 is highest allowable value.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL COLUMNS SIZE 10',CASCADE = TRUE);

2. AUTO:

This most commonly used size in real time scenarios. The basic process is as follows:

  • You run DBMS_STATS for a table with the METHOD_OPT parameter set to the default SIZE AUTO.
  • You query the table.
  • The database keeps track of the predicates in the preceding query and updates the data dictionary table SYS.COL_USAGE$.
  • Then you run DBMS_STATS again, DBMS_STATS takes into consideration data in SYS.COL_USAGE$ to determine which columns require histograms based on queries ran on table.

This method has following drawbacks:

  • If you gather stats with auto option & do not query table at all, then Oracle will not create any histogram on column.
  • If queries are constantly being changed in particular environment, so even data in table almost the same, can cause changes in execution plans for the queries.
EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',CASCADE = TRUE);

3. REPEAT :

It will just collect histograms on columns which already have histograms.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL COLUMNS SIZE REPEAT',CASCADE = TRUE);

4. SKEWONLY :

Oracle collects histograms on the columns with only skewed data.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY',CASCADE = TRUE);

HISTOGRAM DELETION:

There are different methods for dropping histograms on table columns:

To completely delete all types of column statistics plus histograms for a column including minimal statistics:

EXEC DBMS_STATS.DELETE_COLUMN_STATS(OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>','<COLUMN_NAME>');

This is generally not recommended as the optimizer needs at least the minimal (size 1) information for creating optimal execution plans.

To delete histograms from a column without touching base column statistics i.e. minimal information required for the Optimizer (This is recommended method for pre 11g databases).

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT=>'FOR COLUMNS <COLUMN_NAME> SIZE 1');

From Oracle 11g onwards, there is new syntax to delete the histogram for a column while still leaving behind the base column statistics:

EXEC DBMS_STATS.DELETE_COLUMN_STATS(OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',COLNAME=>'<COLUMN_NAME>',COL_STAT_TYPE=>'HISTOGRAM');

Additionally we can delete histograms for a partition:

EXEC DBMS_STATS.DELETE_COLUMN_STATS('<OWNER>',TABNAME=>'<TABLE_NAME>',COLNAME=>'<COLUMN_NAME>',PARTNAME=>'<PARTITION_NAME>', COL_STAT_TYPE=>'HISTOGRAM');

and to delete column histograms for the table and all its partitions:

 EXEC DBMS_STATS.DELETE_COLUMN_STATS(OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',COLNAME=>'<COLUMN_NAME>',CASCADE_PARTS=>TRUE,COL_STAT_TYPE=>'HISTOGRAM');

Hope so u will find this post very useful:-)

Visit Oracle Histograms Simplified: Part 1, in which I focussed on what is histogram & why it is used.

Cheers

Regards,
Adityanath

Advertisements

Oracle Histograms Simplified: Part 1

Oracle Histogram is very nice feature to help CBO to make right decision while constructing execution plan. But many of DBA’s do not use it, assuming it is complex concept to use. In next few posts, I will try to simplify it, so that we can achieve maximum benefit from it.

What it Histogram & what is its purpose:

A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. In simple words, it is a feature in CBO and it helps optimizer to determine how data are distributed/skewed within column. Predicates with skewed data are good candidates for histogram creation.

Basically CBO assumes uniform data distribution of data inside column across the distinct values. For the columns that contain skewed data, histogram helps to generate more accurate execution plans.

Below example will explain you significance of histograms:

I have a table ADI.SALES_ORDER with 100000 rows with index SALES_ORDER_N1 on column GENERATED.

GENERATED column has 4 distinct values: A, B, Y, N.  CBO will assume uniform data distribution across these values. So any query with column GENERATED as predicate will go for FTS, as CBO will assume it has to select 25% data from table.

But in reality data under column generated in skewed as given below:

I gathered fresh statistics without histogram on table as shown below:

Now will compare execution plan with different values for predicate:

As expected this query is going for FTS, as it has to select approx. 80% of data.

But I will expect query to pick index in case of GENERATED=’N’ as it will just select less than 1% of rows from table.

But NO its still going for FTS 😦 & it is due to CBO’s default behavior.

Now here Histogram should come into picture:

Now I will once again compare plans:

As expected following query is going for FTS, as it has to select approx. 80% of data.

But following query has picked up index due to creation of histogram. 🙂

In this post, we focused on what is histogram & why it is used.

Hope so u will find this post very useful:-)

Stay tuned for Oracle Histograms Simplified: Part 2, in which I will explain what are different ways to create & drop histogram on particular column.

Cheers

Regards,
Adityanath

Drop empty tablespace fails with ORA-14404 or ORA-144047 partitioned table contains partitions/subpartitions in a different tablespace

Recently I was performing database reorg operation on one of pre-prod environment with traditional way of table/lob movement followed by index rebuild. After moving all segments from current tablespace to new, I was ready to drop original one.

SQL> select count(0) from dba_segments where tablespace_name='MY_TABLESPACE';
  COUNT(0)
----------         
0

And it errored out!!!

SQL> DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES
*ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

As first step, I tried purging dba_recyclebin, but it didn’t help.

After searching on metalink, I found following note with similar issue: ORA-14404 or ORA-14407 When Trying to Drop a Tablespace (Doc ID 1674989.1)

which talks about two causes of this issue:

1. An attempt was made to drop a tablespace which contains tables whose partitions or subpartitions are not completely contained in this tablespace. 

OR

2. This can happen due to deferred segment creation introduced in 11.2.

First cause was matching my case:

I tried finding out tables whose partitions were spread across other partitions along with tablespace MY_TABLESPACE & moved to it to new tablespace as well:

SELECT    'alter table '|| table_owner|| '.'|| table_name|| ' move partition '|| partition_name|| '   tablespace MY_TABLESPACE_NEW;'  FROM (SELECT DISTINCT table_name, partition_name          FROM dba_tab_partitions         WHERE tablespace_name = 'MY_TABLESPACE') a,       dba_tab_partitions b WHERE b.tablespace_name <> 'MY_TABLESPACE'       AND a.table_name = b.table_name;

After moving all affected table partitions, I once again tried dropping tablespace: MY_TABLESPACE. It once again failed with same error: ORA-14404.
Then I found same issue with some of index partitions as well, for which I used following command:

select distinct 'alter index ' || index_owner || '.' || a.index_name || ' rebuild partition ' || a.partition_name || ' tablespace MY_TABLESPACE_NEW;'from ( select distinct index_name,partition_namefrom dba_ind_partitionswhere tablespace_name = 'MY_TABLESPACE' ) a, dba_ind_partitions bwhere b.tablespace_name <> 'MY_TABLESPACE' and a.index_name = b.index_name;

Once indexes were rebuild, I was able to drop tablespace MY_TABLESPACE without any issues 🙂

Note: You may also get ORA-14407 is some cases, wherein sub-partitions are the issue, for which you will use above commands just with sub-partitions views.

Just to elaborate seconds possible cause of this error: deferred segment creation:

The Oracle Database 11.2 includes a new space allocation method. When you create a non-partitioned heap table, the table segment creation is deferred to the first row insert. This functionality is enabled by default with the initialization parameter DEFERRED_SEGMENT_CREATION  set to TRUE.So in simple words, if you have set this parameter to value TRUE, particular table will be populated in dba_tables once created, but will not be populated in dba_segments unless we insert a row to it. So in the case of defered segments, you will not be able to drop tablespace.

Hope so u will find this post very useful:-)

Cheers

Regards,
Adityanath

EXPDP/IMPDP has performance issue due to “Streams AQ: enqueue blocked on low memory”

In this post, we are discussing about performance issue which I faced with EXPDP schema backup.

We have a production database, for which we take daily EXPDP schema backup before EOD starts. The control-m job configuration has made in such a way that EOD will not start until schema level EXPDP backup gets complete. From last month, EXPDP backup started taking considerable time. Initially it used to take hardly 15 minutes, and all of sudden it started taking 90 minutes, in turn causing delays in start of EOD. Application team raised problem ticket with us for the same issue.

I did basic tuning, like increasing PGA or excluding statistics, but none of it helped me to minimize backup time. Then I thought of observing wait event for EXPDP master & slave processes. It was “Streams AQ: enqueue blocked on low memory”. Of course it was something to related to performance issue I was observing.

I did check for memory configuration of database & found memory is configured with automatic management:

2
If you see above output, due to sudden growth in shared pool size, other components like large pool, db cache & streams pool have shrunk.

I tried to dig in history backup logfiles & found, till 6th Jan 2017, backups were running fine. So lack of streams pool memory is causing EXPDP to run longer.

So as to resolve this issue, I switched from automatic memory to manual one & allocated 200 MB to streams pool. Post memory configuration changes, EXPDP performance was back to normal.

After searching on MOS for similar issues, I found EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (Doc ID 1596645.1)

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

ORA-01450: maximum key length (6398) exceeded while creating index

Interesting issue happened today, while creating index in database(running 11.2.0.4 DB on Linux), I got ORA-01450: maximum key length (6398) exceeded.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> CREATE INDEX "TEST_USER"."DEMO_INDEX_1" ON "TEST_USER"."DEMO_TABLE" ("SEQUENCEGROUP", "SEQUENCENUMBER")
 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS" ; 2 3 4 5 6
CREATE INDEX "TEST_USER"."DEMO_INDEX_1" ON "TEST_USER"."DEMO_TABLE" ("SEQUENCEGROUP", "SEQUENCENUMBER")
 *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

So I started searching on metalink to get details about ORA-01450 & found: ORA-01450 When Creating an Index (Doc ID 293599.1)

According to MOS note, There is a restriction on index data length. It depends on the db_block_size. For 8K Oracle block size,it is 6398. Also the restriction depends on the actual Oracle data block size rather than the DB_BLOCK_SIZE database initialization parameter. That means that when using tablespaces with a different block size, the restriction depends on the actual block_size of the tablespace used for the index storage.

So I checked table definition to get details about datatype for columns used in table.

SQL> desc TEST_USER.DEMO_TABLE
Name Null? Type
----------------------------------------- -------- ----------------------------
CID VARCHAR2(4000)
RT VARCHAR2(4000)
SEQUENCEGROUP VARCHAR2(4000)
SEQUENCENUMBER VARCHAR2(4000)
CLIENTID VARCHAR2(4000)
COUNTRY VARCHAR2(4000)
KEYTYPE VARCHAR2(4000)

As per table definition creating index on two columns SEQUENCEGROUP & SEQUENCENUMBER would make index key length to 8000 which is more than 6398, which is causing failure in index creation.

So how to resolve this: So we have 2 ways

1. Limit column key length if possible:

So in my case, I altered both column datatypes to VARCHAR2(3000), so that index key length was restricted to 6000.Post table alteration, I was able to created index successfully.

2. Create index in tablespace with bigger block size:

So you can create tablespace with block size 16K/32K, and then create index in this new tablespace.

Now we need to understand why we have this restriction:

As per Oracle 8i documentation, index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. In fact, it is required that any index block must contain at least TWO index entries per block. Therefore, the maximum key length for an index will be less than half of the DB_BLOCK_SIZE. The Oracle 8i Administrator’s Guide states that the maximum size of a single index entry is approximately one-half the data block size. However, when considering that we must also leave space in the block according to PCTFREE, INITRANS, and space for block overhead (Block Header, ROW Directory, Table Directory, etc) the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Oracle 9.2 onward, restriction on index data length has been increased from 3218 to 6398. So from 9.2 – 11g we have scope to accommodate more, but there is still a restriction and we cannot create the index beyond it.

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

OUI-67073:UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed on Windows server

Few days back, I was applying OPatch on one of the database residing on windows server. I tried it multiple times without any luck as everytime when I tried applying patch, I was getting below error:

OUI-67073:UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.

This was due to some of the DLL files related to oracle on server were used by some of the active processes even after I had stopped all oracle related services. Even i tried rebooting whole server, but it didn’t help.

I did check related logfile so as to get more details about the error:

[Oct 6, 2016 6:20:18 PM] Prereq checkPatchApplicableOnCurrentPlatform Passed for patch : 23530402
[Oct 6, 2016 6:20:18 PM] Following files are active :
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oracell11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraasmclnt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orapls11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orageneric11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraclient11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oracommon11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraplp11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraxml11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orannzsbb11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orazt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraztkg11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oran11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orantcp11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oranl11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orancrypt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orahasgen11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocr11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocrb11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocrutl11.dll
[Oct 6, 2016 6:20:18 PM] Prerequisite check "CheckActiveFilesAndExecutables" failed.
 The details are:
 Following files are active :
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oracell11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraasmclnt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orapls11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orageneric11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraclient11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oracommon11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraplp11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraxml11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orannzsbb11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orazt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraztkg11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oran11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orantcp11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oranl11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orancrypt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orahasgen11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocr11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocrb11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocrutl11.dll
[Oct 6, 2016 6:20:18 PM] OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Oct 6, 2016 6:20:18 PM] Finishing UtilSession at Thu Oct 06 18:20:18 CAT 2016
[Oct 6, 2016 6:20:18 PM] Log file location: G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\cfgtoollogs\opatch\opatch2016-10-06_18-20-12PM_1.log
[Oct 6, 2016 6:20:18 PM] Stack Description: java.lang.RuntimeException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.OPatchSessionHelper.runApplyPrereqs(OPatchSessionHelper.java:5765)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:905)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:367)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.opatchutil.NApply.process(NApply.java:362)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1084)
[Oct 6, 2016 6:20:18 PM] StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[Oct 6, 2016 6:20:18 PM] StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[Oct 6, 2016 6:20:18 PM] StackTrace: sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[Oct 6, 2016 6:20:18 PM] StackTrace: java.lang.reflect.Method.invoke(Method.java:597)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.UtilSession.process(UtilSession.java:358)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.OPatchSession.process(OPatchSession.java:2515)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.OPatch.process(OPatch.java:796)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.OPatch.main(OPatch.java:846)

Such errors are very easy to fix on UNIX platform wherein we do have commands like ps, fuser or lsof to get details about the process accessing particular file. But what we can do for Windows platform.

I did check with widows admins to help me out here for identifying active processes. He told me about very intersting command “tasklist”.

Syntax: tasklist.exe /m <filename>

This command will give you details such as process name, PID & filename used by process as shown below:

blog-1
So here I got the culprit. Now how to get rid of these processes. You can go to resource monitor under task manager to identify the process and then kill it as shown below:

blog-2
Now you can apply patch without any issues.

As per my past experience, killing windows process manually can cause some serious issues after some time. So after killing any process manually on windows server, will recommend you to cleanly restart your server.

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

srvctl start database fails with ORA-03113,ORA-27300,ORA-27301,ORA-27302,ORA-27303.

Yesterday I was trying to start DB services using SRVCTL on one of the new environment. I was able to start individual database instances using sqlplus, but it was giving issues for SRVCTL, I was able to start one of the two instances using SRVCTL, but not both, it was giving following error:

[oracle@uat-srv-fin2 dbs] Ora:TESTDB $ srvctl start database -d TESTDB
PRCR-1079 : Failed to start resource ora.testdb.db
CRS-5017: The resource action "ora.testdb.db start" encountered the following error:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.4/grid/log/uat_srv-fin1/agent/crsd/oraagent_oracle//oraagent_oracle.log".
CRS-2674: Start of 'ora.testdb.db' on 'uat-srv-fin1' failed
CRS-2632: There are no more servers to try to place resource 'ora.testdb.db' on that would satisfy its placement policy.

After checking all RAC related init parameters, and making sure everything is right there, I had a look at DB alert log so as to investigate further and I got something useful :

Errors in file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_dia0_28019_base_1.trc:
ORA-27506: IPC error connecting to a port
ORA-27300: OS system dependent operation:proto mismatch failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgxpcon
ORA-27303: additional information: Protocol of this IPC does not match remote (192.168.52.42). SKGXP IPC libraries must be the same version. [local: RDS,remote: UDP]
Errors in file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_lmon_28020.trc:
ORA-27550: Target ID protocol check failed. tid vers=1, type=1, remote instance number=2, local instance number=1
LMON (ospid: 28020): terminating the instance due to error 481
System state dump requested by (instance=1, osid=28020 (LMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_diag_27999_20160815112931.trc
Dumping diagnostic data in directory=[cdmp_20160815112932], requested by (instance=1, osid=28020 (LMON)), summary=[abnormal instance termination].
Instance terminated by LMON, pid = 28020

So messages were pointing to RDS/UDP binary linking.

As per metalink note : ORA-27303: SKGXP IPC libraries must be the same version. [local: RDS,remote: UDP] on Exadata (Doc ID 1574772.1)

RAC instances can’t see communicate with each other. IPC error is due to mismatch in cluster interconnect protocol being in use by these 2 nodes. Node2 used UDP and Node1 uses RDS now. So due to this mismatch the other node is down. Exadata does not support mixing of UDP and RDS ports.

As a solution I did following steps so as to resolve the issue:

1. Shutdown all the instances running on this ORACLE_HOME which was configured for the instance having UDP port
– Execute as oracle user,
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk ipc_rds ioracle
2. Startup the instance using srvctl

Now I was able to start database using SRVCTL without any issues:

[oracle@uat-srv-fin2 ~] Ora:TESTDB $ srvctl start database -d TESTDB
[oracle@uat-srv-fin2 ~] Ora:TESTDB $
[oracle@uat-srv-fin2 ~] Ora:TESTDB $
[oracle@uat-srv-fin2 ~] Ora:TESTDB $ srvctl status database -d TESTDB
Instance TESTDB1 is running on node uat_srv-fin1
Instance TESTDB2 is running on node uat_srv-fin2

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath