Rebuild Oracle Text indexes for Portal

The job portal.wwv_context.sync stopped working with the following error message:

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 71
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2530
ORA-20000: Oracle Text error:
DRG-10502: index WWSBR_CORNER_CTX_INDX does not exist

We opened a Service Request at Oracle support but never figured out how the index disappeared, if that was the case and not a corruption somewhere. Support suggested that we executed the procedure given in Doc ID 340439.1; reinstall Oracle Text, then connect as the portal user from Portal server and execute the three scripts from middle tier home:

@?/portal/admin/plsql/wws/ctxdrind.sql
@?/portal/admin/plsql/wws/inctxgrn.sql
@?/portal/admin/plsql/wws/ctxcrind.sql

But the last script failed with:

SQL> @?/portal/admin/plsql/wws/ctxcrind.sql
Creating Context Preferences and Indexes...

PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 164
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2189
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 71
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2118
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 164
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2077
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 71
ORA-06512: at "PORTAL.WWV_CONTEXT", line 1705
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtab.create_index_tables
ORA-00955: name is already used by an existing object
ORA-06512: at line 6

The Application Server Portal Configuration Guide section 8.3.4.1 shows that the last script executes wwv_context.createindex and the error was isolated first to this procedure. The same section shows that wwv_context.createindex is equivalent to:

wwv_context.drop_prefs;  /* Drop all Oracle Text preferences for the indexes, except Lexer preferences */
wwv_context.drop_invalid_indexes; /* Drop all invalid indexes */
wwv_context.create_prefs; /* Create all Oracle Text preferences,except Lexer preferences */
wwv_context.create_missing_indexes(l_indexes);  /* Create missing indexes and record them in l_indexes */
wwv_context.touch_index(l_indexes); /* Mark all rows for created indexes as requiring synchronization */
wwv_context.sync;     /* Synchronize indexes */
wwv_context.optimize; /* Optimize indexes */

By executing each procedure in steps the error was isolated to wwv_context.create_missing_indexes. All of the indexes in question are listed in 8.3.4.2 and can be created one by one with procedure wwv_context.create_index.

To get around the error I executed the following procedure in stead of the script ctxcrind.sql, that is, after I executed the scripts inctxgrn.sql and ctxcrind.sql:

EXEC WWV_CONTEXT.CREATE_PREFS

EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.PAGE_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.DOC_TEXT_INDEX)
exec WWV_CONTEXT.CREATE_INDEX(wwv_context.PERSPECTIVE_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.ITEM_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.CATEGORY_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.URL_TEXT_INDEX)

EXEC  WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.PAGE_TEXT_INDEX)
EXEC  WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.DOC_TEXT_INDEX)
EXEC  WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.PERSPECTIVE_TEXT_INDEX)
EXEC  WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.ITEM_TEXT_INDEX)
EXEC  WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.CATEGORY_TEXT_INDEX)
exec  wwv_context.touch_index(WWV_CONTEXT.URL_TEXT_INDEX)

exec wwv_context.sync; 
exec wwv_context.optimize;

We did not verified if it was really necessary to reinstall Oracle Text, you may try the procedure above before reinstall Oracle Text if you encounter the same error with wwv_context.sync.

ORA-32012

You may get error ORA-32012 if you are on 11g, but have the compatible parameter set to pre-11g in the database you are cloning from, and the spfile for the source database is stored in ASM when you do an RMAN duplicate from active database.

To get around this error, I have found two workarounds:

1) Set the value for compatible to at least 11.1
2) Skip transferring of the spfile during the clone process.

Option 1 is a big change for the database (it affects the CBO among other things), but say you need a clone and are going to change this parameter anyway it is the simplest one since you don't have to create the parameterfile manually. Option 2 means you create the spfile before the cloning starts and remove the clause with spfile from the duplicate command.

An example of duplicate for option 1 is:

run {
set newname for datafile 1 to NEW ;
set newname for datafile 2 to NEW ;
set newname for datafile 3 to NEW ;
set newname for datafile 4 to NEW ;
set newname for datafile 5 to NEW ;
set newname for datafile 6 to NEW ;
set newname for tempfile 2 to NEW ;
duplicate target database to FOOTEST
from active database
spfile
parameter_value_convert='FOOPROD','FOOTEST'
set compatible='11.1.0.0'
set diagnostic_dest='/u01/app/oracle'
;
}

An example for option 2:

run {
set newname for datafile 1 to NEW ;
set newname for datafile 2 to NEW ;
set newname for datafile 3 to NEW ;
set newname for datafile 4 to NEW ;
set newname for datafile 5 to NEW ;
set newname for datafile 6 to NEW ;
set newname for tempfile 2 to NEW ;
duplicate target database to WINTIDST
from active database;
}

It looks as the transfer of spfile from ASM to normal file system is perceived as a downgrade, though there is no change when compatible is 10.2.3 on both databases. The format of the spfile in ASM seems to be different when stored in ASM as explained in Database Administrator's Guide 11g. The change from HARD-enabled storage to normal is not supported then if you are below 11g. Makes sense when one thinks about it, but I did a few tests back and forth to understand the connection, and it looks as if this new format was introduced in 11g. Exactly what format is used for the spfile in ASM is not clear. The ASM installation in this case is on version 11.2.0.2 and the database is on 11.1.0.7 (with the compatible left on 10.2.0.3 for some unknown reason). Maybe the new format is used when the database is on version 11g even though the compatible parameter is pre-11g, if so it looks like a bug when RMAN duplicate looks only at the compatible parameter and not on version of Oracle.

When RMAN creates the spfile it will be created in $ORACLE_HOME/dbs, I could not find any way of having it created in ASM directly.

Anyway, probably not a frequent error, but decided to write it up since I didn't find much when googling.

Hidden updates - another reason to trace

An insert or update statement that is taking a long time to complete is often working on the indexes belonging to the table rather than the table itself. If you look at the plan and finds nothing wrong, it is easy to forget that the indexes have to be updated as well.

The solution as always when something takes time is to enable trace. When updating the indexes the trace file will usually show waits of type db file sequential read, and usually one block at the time.

Full table scan normally causes db file scattered read with many blocks at the time. The number of blocks it fetches is limited, but not exclusively, by the parameter db_file_multiblock_read_count.

In other words, with lots of full table scans you expect db file scattered read but your GUI gives you db file sequential read. The trace file will show you what objects are causing waits and may remind you that the table has an index or two that you can drop and add later.

This command counts the number of waits of one type grouped by object_id (as in dba_objects):

grep "db file sequential read" FOOPROD_ora_6237.trc |awk '{ print $12}' |sort | uniq –c

You can find total elapsed time (in seconds) for a specific object_id (106836 in this example) with:

awk '/106836/ { ela += $8} END {print ela/1000000}' FOOPROD_ora_6237.trc

Repeat this for the object ids from the first list to find where most time is spent.

Then you can find the type and name of the object:

select object_type, object_name
from dba_objects
where object_id=106836;

Of course a decent profiler will do this for you. I wrote this post to remind myself that the trace file will tell me what the fancy GUI hides for me. And it is kind of cool that on a mature OS a string of simple commands allow me to make my own ad-hoc profiler.

VirtualBox on Fedora 15

This is what I did to install Oracle VM VirtualBox on a new Fedora 15 installation.


The documentation at the bottom of download page VirtualBox for Linux gives you the repos-file for yum on Fedora. Find the repos-file and save it to /etc/yum.repos.d/virtualbox.repo, it should look like this:

[virtualbox]
name=Fedora $releasever - $basearch - VirtualBox
baseurl=http://download.virtualbox.org/virtualbox/rpm/fedora/$releasever/$basearch
enabled=1
gpgcheck=1
gpgkey=http://download.virtualbox.org/virtualbox/debian/oracle_vbox.asc

Then you can search for VirtualBox:

[root@favela ~]# yum search VirtualBox
Loaded plugins: langpacks, presto, refresh-packagekit
=========================== N/S Matched: VirtualBox ============================
VirtualBox-4.0.x86_64 : Oracle VM VirtualBox

  Name and summary matches only, use "search all" for everything.

To install:

yum install VirtualBox-4.0.x86_64

Users need to be included in the OS group vboxusers before they can run it:

usermod -a -G vboxusers oisene

VirtualBox is now ready to run from menu.

In fact this was not what I did first time, because I didn't read the fine manual to the end. I download it from here and installed the missing packages something like this:

yum install qt qt-x11 SDL
yum install kernel-headers-$(uname -r) kernel-devel-$(uname -r)
yum install binutils gcc patch glibc-headers glibc-devel
yum install dkms
rpm -ip /home/oisene/Downloads/VirtualBox-4.0-4.0.8_71778_fedora15-1.x86_64.rpm 

Without the dkms package VirtualBox will run, but it is recommended to ensure VirtualBox host kernel modules will be update during next kernel update.

Today

I have made a few changes to my blog... think I want to blog again.

So far this year in a few words; new job at Keystep - smart move, if you try two years in the wrong company it is time to move on; met quite a few smart oracle folks at the yearly conference of Oracle User Group Norway - got elected as a board member; met more smart guys at Miracle Open World in Denmark the week after. And been thinking about moving back to Brazil quite a lot lately.

Removing orphan DataPump jobs and filtering included tables with a query

Say you define a DP job from the API (DBMS_DATAPUMP) you may end up with jobs with status NOT RUNNING until you get it right. Verify if you have such a job with

select job_name,state 
from user_datapump_jobs;

Then you may try to remove it with:

declare 
 l_h number;
begin
  l_h:=dbms_datapump.attach('YOUR_JOB');
  dbms_datapump.stop_job(l_h,immediate=>1);
  commit;
end;
/

If the query above still reports the job with the same status it can be removed by dropping the master table, according to Note 336014.1:

drop table YOUR_JOB;

If the database is using a recycle bin the table has been renamed to a name starting with 'BIN'; if you rerun the query above the job is renamed to reflect this, but if you purge the recycle bin the job will be finally removed:

purge recyclebin;

If your PL/SQL code fails during definitions before you do a detach you cannot do a second attach. Since you have no reference to the job the only solution I'm aware of is to reconnect (i.e. create another database session). Better yet is to define an exception on ORA-39001 and retrieve the error message with dbms_datapump.get_status, and dbms_datapump.detach(l_handle) in the exception handler:

declare
  invalid_arg exception;
  pragma exception_init(invalid_arg,-39001);
:
:
begin
:
:
exception when invalid_arg then
    dbms_datapump.detach(l_h);
end;

If you drop the master table that is in the defining state, you will not be able to attach to the job, but if you try to attach to such a job the job will be removed for the user_datapump_jobs table.

If you try to add a metadata filter of type NAME_EXPR and you add a subquery in the expression, make sure that any table name in the subquery is prefixed with table owner or else you may receive ORA-00942 in the logfile:

dbms_datapump.metadata_filter(handle => l_h, name => 'NAME_EXPR', value =>'IN (SELECT TABLE_NAME FROM SCOTT.CHANGE_TABLE)'  ); 

Typical error in logfile when schema is missing:

Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6307
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x7bfb80b0     15032  package body SYS.KUPW$WORKER
:
:

Slow performance with TDE and CHAR data type on 10.2.0.5

In version 10.2.0.5 of the database there is a bug when using Transparent Data Encryption (TDE) on a column with the CHAR datatype. In the following test a table is created where the primary key is defined as CHAR(11) and then encrypted with NO SALT. A simple lookup on this PK works as expected by using the respective index on 10.2.0.4. But on 10.2.0.5 a trace on event 10053 shows that an INTERNAL_FUNCTION is wrapped around the PK-column and therefore impedes use of the index.

The following test was performed on an OVM guest created from a template downloaded from edelivery.oracle.com. It contains Oracle Server EE 10.2.0.4 running on Oracle Enterprise Linux 5, 64-bit. Configuration of TDE and wallet is not shown.

create table foo (
   id char(11) encrypt no salt primary key ,
   msg varchar2(1000)
  );

insert into  foo ( select * from (
  with generator as (
      select      rownum      rn
      from        dual
      connect by
                  rownum <= 100
  )
  select
      dbms_random.string('x',11) id,
      dbms_random.string('x',1000) msg
  from
      generator   g1,
      generator   g2
  where
    rownum <= 10000
  )
);
commit;
exec dbms_stats.gather_table_stats(user,'FOO', cascade=>true,method_opt=>'for all indexed columns size 1',estimate_percent=>100)
variable lid char(11)
exec :lid:='abc123def45';
alter session set events='10053 trace name context forever, level 1';
select msg from foo where id=:lid;

The query returns immediately since the index is used and the trace file shows the good plan (a simple explain plan will show the same):

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |            |       |       |     2 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID | FOO        |     1 |   514 |     2 |  00:00:01 |
| 2   |   INDEX UNIQUE SCAN          | SYS_C007236|     1 |       |     1 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("ID"=:LID)

Now, the database is patched to version 10.2.0.5 (patch 8202632). There are no invalid objects in the database after the upgrade. Then repeating the last query from above together with the trace on event 10053, the query takes a long time and the trace file shows this plan:

-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |   388 |           |
| 1   |  TABLE ACCESS FULL | FOO     |   100 |   50K |   388 |  00:00:05 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(INTERNAL_FUNCTION("ID")=:LID)

The INTERNAL_FUNCTION is used among other situations when oracle has to perform an implicit data conversion. This may happen when the data type of a bind variable is different from the data type used in the table.

This kind of mismatch may happen during upgrades of JDBC drivers where the mapping may change; Java had support for TimeStamp before it was introduced in the Oracle database. Later when Oracle supported TimeStamp this mapping changed, leading to this kind of errors. There are other reasons for such mismatches; a trace on event 10053 will reveal the data type of the bind variable:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
 Bind#0
  oacdty=96 mxl=32(11) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=32 off=0
  kxsbbbfp=2b19acc082a0  bln=32  avl=11  flg=05
  value="abc123def45"

The part "oacdty=96" shows the type is CHAR or NCHAR. This did not change between the tests, so there has to be another reason why the INTERNAL_FUNCTION is used. Also in this case the only change is the upgrade, even the parameter compatible remained the same (10.2.0.3). The tests where performed from sqlplus on the database server.

This is bug 10296606 on MOS.

Update: It was discovered that if you use a variable of VARCHAR type the INTERNAL_FUNCTION is not applied, meaning that columns of CHAR type are converted to VARCHAR after encryption, but that USER_TAB_COLUMNS continues to report CHAR for the column.