Friday, December 28, 2012

ORA-17612: Failed to discover Oracle Disk Manager Library, return value 2

DBA’s you must have come across below error post cloning a RAC database to single instance node

RAC database shared server storage must have been managed by third party cluster vendor tools like VERITAS or so

Refer below steps to resolve it 

Issue:

ORA-17612: Failed to discover Oracle Disk Manager Library, return value 2



Solution: 

Step 1: Make sure proper .profile/environment variables & pointing to new/cloned ORACLE_HOME
-ORACLE_HOME
-ORACLE_SID
-LD_LIBRARAY_PATH
-LIBPATH
-PATH

Step 2: Perform below on destination server where cloning has taken place

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk rac_off





Step 3: 
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ioracle


Step 4: After detaching binaries from RAC configuration, move below 4 RAC libraries to backup

#cd /oracle_temp/product/9.2.0/lib
#ls -lrt libodm9.a
-rw-r--r--    1 oracle9i oinstall       7386 May 13 2002  libodm9.a
#mv libodm9.a libodm9.a_old
#ls -lrt libodm9.so
-rw-r--r--    1 oracle9i oinstall      48077 Jun 14 2008  libodm9.so
#mv libodm9.so libodm9.so_old
#ls -lrt libodmd9.a
-rw-r--r--    1 oracle9i oinstall       7386 May 13 2002  libodmd9.a
#mv libodmd9.a libodmd9.a_old
#ls -lrt libodmd9.so
-rw-r--r--    1 oracle9i oinstall       6470 Oct 26 2004  libodmd9.so
#mv libodmd9.so libodmd9.so_old





Step 5: Now copy the above 4 library files from the single node binary of similar OS & DB Version

#cp /oracle/product/9.2.0/lib/libodm9.a  .

#cp /oracle/product/9.2.0/lib/libodm9.so  .

#cp /oracle/product/9.2.0/lib/libodmd9.a  .

#cp /oracle/product/9.2.0/lib/libodmd9.so  .



Step 6:

-Copy/Ensure init.ora , listener ,tnsnames , password have been copied to cloned ORACLE_HOME

-Remove/unset Cluster parameters from parameter file e.g.

cluster_database=FALSE

thread=0

-Make sure all dump/audit location folders are accessible

Step 7:

Relink database binaries

#relink all
 



 Step 8:

Startup the database
 

Thursday, December 27, 2012

Improving FTS oracle



This blog is written after resolving sev1 performance issue post DB migration to new setup

Issue: Sql Full table Scan (FTS) Slowness due to change in execution plan post db migration

Background:
Recently we have migrated 11gr2 Single Node prod. DB (ASM/AIX) to 11gr2 RAC 2 node (Linux) using Data Pump, since then started facing the below issue

Query:
SELECT C1000000159,
       C1000000161,
       C1000000170,
       C1000002134,
       C536871133,
       substr(C1000000151, :"SYS_B_0", :"SYS_B_1") as C1000000151,
       substr(C1000000000, :"SYS_B_2", :"SYS_B_3") as C1000000000,
       C1000000001,
       C1,
       C3,
       C6
  FROM ARADMIN.T2108;

Table: Data-type CLOB/varchar2, Number of Rows 5701535, Size of the Table Segment 12 GB
Execution Plan: Old DB (Single Instance, AIX)
Elapsed Time:    2 Hrs



Execution Plan: New DB (RAC, Linux)
Elapsed Time:    4.5 Hrs



Workarounds:
The application team reluctant to do any alteration in the query (like adding where clause) as it was responding in 2 Hrs before migration
We have done below to make it work as before:

1. Gathered system stats for the interval of 48 Hrs
exec  dbms_stats.gather_system_stats ('INTERVAL',2880);

2. Gathered dictionary stats
exec dbms_stats.gather_dictionary_stats;

3. Gather fixed object stats
execute dbms_stats.gather_fixed_objects_stats;

4. Export/ import of old db stats into new db

--creating stat table in source
exec dbms_stats.create_stat_table('ARADMIN','STAT_T2108');

--collecting stats in stat table
begin
dbms_stats.export_table_stats(
ownname => 'ARADMIN',
tabname => 'T2108',
stattab => 'STAT_T2108'
);
end;
/
--exporting stats from source
exp file=exp_STAT_T2108.dmp log= exp_STAT_T2108.log tables=aradmin.stat_t2108

--importing stat table on source
Imp file=exp_STAT_T2108.dmp log= imp_STAT_T2108.log fromuser=aradmin touser=aradmin

--Importing/Populating stats into stat views
exec dbms_stats.IMPORT_TABLE_STATS('ARADMIN','T2108',STATTAB => 'STAT_T2108' , NO_INVALIDATE => 'FALSE');

5. Applied parallel hint giving slight performance by reduction in 30 min.

SELECT /*+ PARALLEL (T1, 20) */
 C1000000159,
 C1000000161,
 C1000000170,
 C1000002134,
 C536871133,
 substr(C1000000151, :"SYS_B_0", :"SYS_B_1") as C1000000151,
 substr(C1000000000, :"SYS_B_2", :"SYS_B_3") as C1000000000,
 C1000000001,
 C1,
 C3,
 C6
  FROM ARADMIN.T2108 T1;

6. Tried RULE, FIRST_ROWS hints

SELECT /*+ RULE */...
SELECT /*+ FIRST_ROWS */...
SELECT /*+ FIRST_ROWS_1000 */...

7. Gathered stats on table using different methods (with/ without histograms)

exec  dbms_stats.gather_table_stats('ARADMIN','T2108',degree => 10 , no_invalidate => false , cascade => true);
exec  dbms_stats.gather_table_stats('ARADMIN','T2108',degree => 10 ,estimate_percent =>100, no_invalidate => false , cascade => true);
exec  dbms_stats.gather_table_stats('ARADMIN','T2108',degree => 10 ,estimate_percent =>100, block_sample => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1',no_invalidate => false , cascade => true);

However all above actions failed to improve execution plan or response time of the query

Generally, all above actions should have lead to good sql performance 

Solution:

New RAC DB system stats parameters value have been set to that of Old DB manually
Immediately after setting below values we got the old db execution plan for the query in new db

select * from sys.aux_stats$;
exec dbms_stats.set_system_stats( pname => 'CPUSPEEDNW', pvalue => 978);
exec dbms_stats.set_system_stats( pname => 'IOSEEKTIM', pvalue => 10);
exec dbms_stats.set_system_stats( pname => 'IOTFRSPEED', pvalue => 4096);
exec dbms_stats.set_system_stats( pname => 'SREADTIM', pvalue => 3);
exec dbms_stats.set_system_stats( pname => 'MREADTIM', pvalue => 7);
exec dbms_stats.set_system_stats( pname => 'CPUSPEED', pvalue => 981);
exec dbms_stats.set_system_stats( pname => 'MBRC', pvalue => 11);
exec dbms_stats.set_system_stats( pname => 'MAXTHR', pvalue => 27578368);
exec dbms_stats.set_system_stats( pname => 'SLAVETHR', pvalue => 0);

 

Note:

Setting system stats manually is not recommended it should be gathered manually or allow auto stats collection job to collect it on new system to compute proper values of system resources & take benefit of the CPU, Io, threads, slaves etc.

In our case it was severity 1 issue to get o/p of above query (only one time), above scenario has worked but later we have collected new system stats.

We had 5000 MHz * 5 CPU configuration in Old DB Server but in New DB server it was 1861 MHz * 24 CPU
The per CPU reduction in speed have triggered this issue, however due to parallelism/multithreading & gathering system stats have stabilized the performance issue later