Oracle CRS with Trunked public interface problem (+ workaround)

I've encountered a problem installing Oracle CRS on a server with trunked public 'ce' interfaces.

Hardware: v890
OS : Solaris 10 update 3
Oracle Version: 10.2.0.3
Trunking : 1.3

The 'vipca' script fails, and when you look at the logfile $CRS_HOME/log//racg/ora..vip.log it's packed full of usage messages from nettr (see extract from log below). Oracle has a bug logged with similar symptoms 5876195 but this is marked as not a bug and they have not published a workaround.

Looking at the script $CRS_HOME/bin/racgvip, it calls nettr -stats with incorrect options if you have a 'ce' interface.

line 305 # _O1=`$NETTR -stats ${_TRUNKHEAD} device=${_DEVICE} | $AWK '/'$_DEVICE'/ { _S += $2 } END { print _S }'`

If you alter this line to read

_O1=`$NETTR -stats ${_TRUNKHEAD} | $AWK '/'$_DEVICE'/ { _S += $2 } END { print _S }'`

Then re-run vipca manually, your vip will create successfully.

Hope this helps stop someone having the same headaches I did,

mel


------ Extract from log starts here

Oracle Database 10g CRS Release 10.2.0.1.0 Production Copyright 1996, 2005 Oracle. All rights reserved.
2007-03-08 13:48:30.063: [ RACG][1] [5638][1][ora.bombay.vip]: Usage: /etc/opt/SUNWconn/bin/nettr -setup device= members=
[ { device= members= }... ]
[ policy= ]
[ mac= ]
[ lacp= [ ptimer=<0|1> ] ]
/etc/opt/SUNWconn/bin/nettr
2007-03-08 13:48:30.070: [ RACG][1] [5638][1][ora.bombay.vip]: -setup device= members=
[ policy= ]

/etc/opt/SUNWconn/bin/nettr -stats
[ type=<1|2> ] [ interval= ]
/etc/opt/SUNWconn/bin/nettr -stats device=
[ type=<1|2> ] [ in
2007-03-08 13:48:30.070: [ RACG][1] [5638][1][ora.bombay.vip]: terval= ]

/etc/opt/SUNWconn/bin/nettr -release
/etc/opt/SUNWconn/bin/nettr -release device=

/etc/opt/SUNWconn/bin/nettr -add
device= members=
[ { device=dev2> members= }.
2007-03-08 13:48:30.070: [ RACG][1] [5638][1][ora.bombay.vip]: .. ]

/etc/opt/SUNWconn/bin/nettr -rem
device= members=
[ { device= members= }... ]

/etc/opt/SUNWconn/bin/nettr -mod
[ policy= ]
[ lacp= ] [ ptimer=<0|1> ]

/etc/opt/S

What causing parser to choose table scan vs index scan

I'm trying to tune a query, and I am surprised by a relatively simple thing. I have a large (~1M rows) table ec_bug which has an index on ( id, snapshot_date ). You would think that it would be faster to get at the IDs by doing an INDEX (FAST FULL SCAN) than by looking at the table itself. In fact, that's what happens for this query:

> select _count(unique id)_ from j2se.ec_bug;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=593 Card=1 Bytes=5)
1 0 SORT (GROUP BY)
2 1 _INDEX (FAST FULL SCAN)_ OF 'EC_ID_IX' (INDEX)
(Cost=593 Card=875305 Bytes=4376525)


But if I want the actual IDs instead of just the count, the optimizer switches to a full scan of the table itself, at a much

greater cost:

> select _unique id_ from j2se.ec_bug;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2262 Card=13054
Bytes=65270)
1 0 SORT (UNIQUE) (Cost=2262 Card=13054 Bytes=65270)
2 1 _TABLE ACCESS (FULL)_ OF 'EC_BUG_TBL' (TABLE)
(Cost=2128 Card=875305 Bytes=4376525)


This is especially surprising since the index is already sorted by ID, so the sort would incur no cost.

Multiple failures to use this index in a more complex query are causing it to time out; does anyone know what might be

causing the optimizer to ignore this index if I want the actual ID value?

Followup 1:

I believe the issue is NULLs. If you allow NULLs in the "id" column, you will
have to sample the whole table to find them. Any function, such as "count"
will ignore rows with NULLs. But select unique id will include the NULL.

For example:

SQL> create table x (id number, dt date);

Table created.

SQL> insert into x values (NULL,sysdate);

1 row created.

SQL> insert into x values (2, sysdate);

1 row created.

SQL> insert into x values (3, sysdate);

1 row created.

SQL> insert into x values (4, sysdate);

1 row created.

SQL> insert into x values (5, sysdate);

1 row created.

SQL> insert into x values (6, sysdate);

1 row created.

SQL> select count (unique id) from x;

COUNT(UNIQUEID)
---------------
5

SQL> select unique id from x;

ID
----------
2
3
4
5
6


6 rows selected.

So the count can use the index and ignore any NULL rows (which are not in
the index, I believe). But the direct select has to include the NULL so it
has to go against the table itself.


Followup 2:

Those fields were part of several indexes, but not the primary key, and weren't otherwise constrained to be null. Changing them to NOT NULL caused the index to be used.

Referance : oracle interest alias

KB : ORACLE_Db\TUNING\Table_scan_vs_index_scan_example1.txt

What to do with this dump file?

I was asked in short notice to check the dump file received from third party can be converted in any meaningful data? The

only information I was given a location of file.

There was no more information available and no contact to get more information.

Here how I proceed:

Check the file type

% type webconfig.dmp

webconfig.dmp: ascii.text

% head webconfig.dmp

UWEBCONFIG
RUSERS
1024
0
28
0
#G#A°#G#A°#CREATE SEQUENCE "SEQ_GUIBASECONFIGTABLE" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 226 CACHE

3 NOORDER NOCYCLE
CREATE SEQUENCE "SEQ_GUIFEATURETABLE" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 921 CACHE 4

NOORDER NOCYCLE

%

This give me that this a oracle dump file from my past experience. Also I know that this is created for user level and the

schema name is WEBCONFIG

Now I have to find a working oracle database to find more information. I use my test database and perform following



$ imp system/***** show=y FILE=webconfig.dmp FULL=Y

Import: Release 9.2.0.1.0 - Production on Wed Jun 28 21:25:37 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by WEBCONFIG, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses US7ASCII character set (possible charset conversion)
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)

. importing WEBCONFIG's objects into SYSTEM
"CREATE TABLE "ATATTRIBUTETYPE" ("NAME" VARCHAR2(80) NOT NULL ENABLE, "DESCR"
"IPTION" VARCHAR2(80) NOT NULL ENABLE, "DATATYPE" NUMBER(10, 0) NOT NULL ENA"
"BLE, "ISSUMMABLE" NUMBER(1, 0) NOT NULL ENABLE, "STATUS" VARCHAR2(1) NOT NU"
"LL ENABLE, "LAST_UPDATED_BY" VARCHAR2(30) NOT NULL ENABLE, "TIME_STAMP" DAT"
"E) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 4"
"0960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREEL"
"IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "WEBCONFIG_DATA""
. . skipping table "ATATTRIBUTETYPE"


The outcome is truncated

From the above outcome I search for TABLESPACE and find out what tablespace are needed to make it successful load and the

character set information.

Now I have information to proceed. I created the required tablespaces and created a new user let’s call it CWP with connect

and resource role.

% imp system/****** FILE=webconfig.dmp FROMUSER=WEBCONFIG TOUSER=NEWUSER

The import terminated successfully without warnings.

One less thinng to worry about.