Oracle10g Text, new Features

Oracle 10g Text (an db-integrated full text search engine with document classification, theme seaching and viewing capabilities) offers some new features. oracle Text becomes more and more integrated within the database. in former versions (intermedia text) an external process synchronized all text indexes and has to be running if you wanted to query new data. These are some new characteristics:

no separate job or process has to sync the index any more. A new SYNC parameter allows control this. SYNC MANUAL is the old way, you have to implement an explicit logic to sync the indexes. SNYC EVERY INTERVALSPEC is for cyclic sync and SYNC ON COMMIT maintains the affected index at the end of a transaction. Commit returns not until the sync is done. Syncing is implemented internally in an autonomous transaction, in case of an error, but does not lead the main transaction to fail.

TRANSACTIONAL is to control whether changes on a text index column are queryable immediate after the corresponding DML statement returns.

create index online is possible (indextype CONTEXT only). DML Changes are allowed during Oracle creates the text index.

Query logs. With this its possible to run reports about search term usages

a BASIC_LEXER for new german spelling is available. Written word in new and old style are treated equally.

it is still not supported to create an index on a IOT Table (index organized table, tabledata is stored sorted and moves on DML activity)

a new indextype CTXXPATH is introduced to speedup Indexes on XMLTYPE columns , especially when existsNode() is used.

Some tests are following, i want to check out some weak points (if they are still ... implemented)

Oracle 10g schema migration

With Version 10G, Oracle introduces the so called Data Pump for doing data exports an imports. given an oracle 9.0.2.4 DB (solaris) and an oracle 10g (Win2000) i wanted to copy a schema's metadata. Unfortunately this seems to be not possible with the "data pump".

First approach:
9.2 Schema export with "exp", then dumpfile import using Data Pump. Oracle documentation ("Oracle 10g Database Utilities" Manual) says Data Pump does not support old dumpfiles (exp generated).

Second approach:
Data Pump supports a direct export-import via network (Database link) without dumpfile generation.

>impdp mydata/no NETWORK_LINK=mydb3e.world SCHEMAS=KNO_DATA CONTENT=METADATA_ONLY

Import: Release 10.1.0.2.0 - Production on Montag, 23 August, 2004 13:56
Copyright (c) 2003, Oracle. All rights reserved.
Angemeldet bei: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining options
ORA-39006: Interner Fehler
ORA-39022: Datenbankversion 9.2.0.4.0 wird nicht unterst³tzt.

Third try:
Data Pump export (expdp) to a dump file, then impdp into 10G.

>EXPDP mydata/no NETWORK_LINK=mydb3e.world DUMPFILE=dpump_dir:no3enodata.dmp SCHEMAS=MYDATA CONTENT=METADATA_ONLY
Export: Release 10.1.0.2.0 - Production on Montag, 23 August, 2004 14:11
Copyright (c) 2003, Oracle. All rights reserved.
Angemeldet bei: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining options
ORA-39006: Interner Fehler
ORA-39065: Unerwartete Master-Prozess-Exception in DISPATCH
ORA-00904: "SYS"."KUPM$MCP"."GET_ENDIANNESS": ung³ltiger Bezeichner

ORA-39097: Bei Data Pump-Job ist ein unerwarteter Fehler -904 aufgetreten

What to try next ? Server documentation "Database Utilities" Chapter 20 is about old exp and imp tools. They are still supported. It says you have to install it (catexp.sql). But this is not needed, the Oracle 10G setup already installed the required exp/imp dictionary tables.

finally a exp (exp version 9.0.2) on DB 9.2 to a dumpfile and an imp (10G) into Oracle 10G worked fine.

Oracle 10g Win32 Installation

I thought its worth testing some Oracle xdb Features. So i downloaded Oracle 10g ( Version 10.1.0.2 ) and tried to install. i tried, it was annoying as it left my pc behind in a oracle-unusable state. The story goes like this:

install notes says that no other oracle home or installation is allowed. if an ORACLE_HOME is present, installer wont work. I first tried without deinstalling some oracle products but as the note said, the installer does not appear after starting it. so i deinstalled oracle 9i client software, further i deleted all ORACLE entries in the registry.

But the installer still does not work. After some searching i found install.exe for the 10g universal installer, a direct invocation was successful. (install guide is wrong about this). But after some steps some errors occurred, first it was possible to go on with installation ( unable to install OCR ), but then an installation or starting of a CSS service failed. so i aborted the installation. Oracle seems to be not able to release a working installation for the public. this is very daunting.

But to mention some positive words, oracle's instant client (only core dll's für client access) seems to work. To connect using TOAD, you have to decompress those instant client files, and supply PATH with this new location. Using regedit, you have to set TNS_ADMIN ( software => ORACLE ) to point to your tnsnames.ora.

###

Info: an installation on another PC shows that error "unable to install OCR" is still present, good luck the error is skippable. A little bit later, a CSS-Service signals an error. oracle universal installer allows to ignore it. Installation goes on after this and leads to an working instance. i used a slow Pentium3 system @ 256 MB ram, but this is too slow. you have to be very patient during installation.

JVM Thread Dump, System.setErr() System.setOut()

when you issue a QUIT signal ( kill -s QUIT pid ) against a java process, the VM dumps out a thread dump. This is valueable e.g. for thread blocking or deadlock analysis. For each thread a stacktrace is generated. To find out where processing hangs, its wise to initiate two thread dumps. Usual it dumps to unix channel 2 (std error output) but my JVM 1.3.1 dumps to std. out. if output is redirected into a logfile, these files are getting soon very large. we prefer to avoid problems like "no space left on device". a solution is using System.setOut(pw), System.setErr(). As the dump generating part of the VM seems to be a platform dependent handler, a redirection of output with the help of System.setOut/setErr does not affect the location of the file containing our thread dumps. usually output is redirected to /dev/null if your application e.g. tomcat has to discard std output because of large data amounts. you can preserve this but have your thread dumps logged in a file. Just redirect all VMs output in a file and when your server application started up, use System.setOut(pw)/setErr(pw) to redirect all output. with the help of a dummy outputstream class - which does nothing - you can instanciate a printwriter. with this, all logging stops, but thread dumps are still logged as the System.setOut calls have no effect on them.

Unix, Solaris Process list without truncation

Thanks to John McClain from Sun. Sometimes i had problems identifing a specific process id. the standard ps command truncates the commandline arguments. To avoid this its possible to log the process id after having started a process:
echo §! > /tmp/myApplication.pid
Further, its easy to "calculate" uptimes with such an approach. If this is not present, try using another ps Command. Try:

/usr/ucb/ps -auxwww grep java

If its present, this ps dumps out processes without truncating commandlines. e.g.

o92 15294 0.0 2.614516026024 ? S Aug 05 0:54 /usr/j2se/bin/../bin/sparc/native_threads/java -Xms64m -Xmx256m -classpath ./../bin/bootstrap.jar:/usr/j2se/lib/tools.jar -Dcatalina.base=./.. -Dcatalina.home=./.. -DPROPERTIES_PATH=/oe/app/k3/tdk/bin org.apache.catalina.startup.Bootstrap start