Montag, 20. Dezember 2010

create database might fail with 11.2.0.2

I hit a nice bug at a test migration from 10.2.0.5 to 11.2.0.2.
As it's easier to convince Oracle Support of a problem if the testcase is just simple, a colleague reduced the testcase to


cretate a small pfile:


db_name=test
db_block_size=2048
db_create_file_dest=/tmp

and run
create database character set al32utf8;

the result is:

create database character set al32utf8
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/doptim.bsq' near line 416
ORA-00604: error occurred at recursive SQL level 1
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
Process ID: 16723
Session ID: 47 Serial number: 1


For Orale Support this really was enough to file Bug:10410249 towards development.

If you have a Database with blocksize of 2k and a multibyte characterset, beware!

So far enough for Oracle Support. Just to provide some more insights:
the upgrade failed at
@catupgrd.sql
with

SQL> begin
2 dbms_stats.delete_table_stats('SYS', 'OBJ$MIG');
3 dbms_stats.delete_table_stats('SYS', 'USER$MIG');
4 dbms_Stats.gather_table_stats('SYS', 'OBJ$MIG', estimate_percent => 100,
5 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
6 dbms_Stats.gather_table_stats('SYS', 'USER$MIG', estimate_percent => 100,
7 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
8 end;
9 /
begin
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 2



I did not know why dbms_stats seems to be broken. So I tried to run catupgrd.sql again - same problem.

Next step: recompile dbms_stats manually.
at running

@prvtstas.plb

it showed me there was an error at line 2175.
If you are curious what's going on there:

2175 CURSOR GET_COL_GROUP_USAGE(OWNER VARCHAR2, TABNAME VARCHAR2) IS
2176 SELECT CU.OBJ# OBJN, CU.COLS,
2177 (CASE WHEN BITAND(CU.FLAGS, 1) = 1 THEN 'FILTER ' ELSE '' END) ||
2178 (CASE WHEN BITAND(CU.FLAGS, 2) = 2 THEN 'JOIN ' ELSE '' END) ||
2179 (CASE WHEN BITAND(CU.FLAGS, 4) = 4 THEN 'GROUP_BY ' ELSE '' END) USAGE,
2180 CU.FLAGS USAGEFLG
2181 FROM SYS.COL_GROUP_USAGE$ CU
2182 WHERE CU.OBJ# = (SELECT O.OBJ# FROM SYS.OBJ$ O, SYS.USER$ U
2183 WHERE O.OWNER# = U.USER#
2184 AND U.NAME = OWNER
2185 AND O.NAMESPACE = 1
2186 AND O.REMOTEOWNER IS NULL
2187 AND O.LINKNAME IS NULL
2188 AND O.SUBNAME IS NULL
2189 AND O.TYPE# = 2
2190 AND O.NAME = TABNAME)
2191 ORDER BY ...



I checked COL_GROUP_USAGE$ - but it did not exist!

Some grep in $ORACLE_HOME/rdbms/admin brought me to c1102000.sql
There I can read:

-- #(9577300) Column group usage
create table col_group_usage$
(
obj# number, /* object number */
/*
* We store intcol# separated by comma in the following column.
* We allow upto 32 (CKYMAX) columns in the group. intcol# can be
* upto 1000 (or can be 64K in future or with some xml virtual columns?).
* Assume 5 digits for intcol# and one byte for comma.
* So max length would be 32 * (5+1) = 192
*/
cols varchar2(192 char), /* columns in the group */
timestamp date, /* timestamp of last time this row was changed */
flags number, /* various flags */
constraint pk_col_group_usage$
primary key (obj#, cols))
organization index
storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/


As I tried to create that table manually, I recieved my ORA-01429.

We discussed some workarounds internally.
I created the IOT (without approval from Oracle Support) with cols varchar2(192 byte). Guess what, it worked! Also @catupgrd.sql was fine afterwards.
As I'm sure in this particular DB there are only column names with plain ASCII, this will not be an issue in this particular DB. But some other suggestions like creating a heap table or IOT without the cols as part of the index definition. This might result in performance problems of dbms_stats, but does not affect any logic (as my WA does).

UPDATE:
Patch:10410249 is provided for this issue now. It's labeled generic, as it contains only some sql files for $ORACLE_HOME/rdbms/admin and the proper patch (and rollback) scripts.
Kommentar veröffentlichen