OOW 2011 Day 2

Went to the keynote and five presentations. Keynote was a lot of show, but not much to bring home. Two presentations regarding database migration assistant for unicode, and Real Application Testing were relevant, but so uninspiring and lacked the extra stuff that it was like attending someone reading from the manual. Not much of a take away there either. Also went to a presentation on PL/SQL - Divide and Conquer, modularizing your code. I had hoped to hear more from Bryn Llewellyn, Oracle, but it was really Martin Büchi from Avaloq that had the whole show. Lots of usefull info, interesting real life case on how to structure enormous amount of code. Will remember this next time I get a chance to work with developers and have to look into refactoring of PL/SQL. It does seem to be something one can apply elsewhere in any large environment. Bryn is an excellent speaker, by the way. The two were clear that they did not want to sell consulting or anything else, just to share some goals and passion. I liked that, Bryn talks in way that could fool any DBA into write some code; wish he had a bigger part of it.

Next presentation was of course the highlight of the day. Cary Millsap talked about skew. I've heard some of this before, but again there was something new to learn and reconsider. A reminder of how skew occurs in many levels and groupings and is really the reason why he and most of us has a job. (Just that Method R gets the job done sooner, I think). Cary is the best presenter I can think of in the Oracle world, and made the room laugh many times at a potentially boring subject. Two of my friends heard him speak for the first time and was impressed. One of them considered to skip the remaining presentations for the day so the day would have a good ending. Couldn't have made a better compliment myself.

I finished of with a presentation regarding best practices for Oracle on Windows. I'm not much a fan of best practices, but this was a full packed check-list on what can go wrong on Windows; a unix/linux guy would suspect that there is enough to keep you busy. Very useful to use later since I have been sent to windows land on an assignment for 6-12 months.

I also met briefly Dan Norris from Oracle and Alex Gorbatchev from Pythian; two guys I've been following on Twitter for a while. Always great to see people IRL once in a while (MOW 2011 and Hotsos 2008 in that order). These guys remind me why I like working with this (the database), people outside, especially the open source java cool coding guys, wonder how can one have a life at all working with a comercial database that is anything than simple and inexpensive. Lots of smart people work with this and create an atmosphere beside the work - e.g. OOW has become a great event. Since they don't compete in my market it does not cost much to write this; but Method R and Pythian are two companies I admire; they create and share a lot with others, and appearantly have lots of fun at work and outside.

Now breakfast, no time to spell check. Love my new android tablet, by the way.

Update:
-Breakfast over, some spell checking done.
-Power tip for the Android tablet: Check the Wi-Fi disconnection policy, set it to disconnect when screen is turned off; battery lasts much longer.
-I remember that I met a smart guy on Rac Attack yesterday; Martin Nash. From UK, good for us we have such experts we can ask to come over to OUGN.

OOW 2011 Day 1

First day at OOW 2011 started with interesting presentations arranged by IOUG, very usefull stuff and I have to read the presentations again to get all the details regarding Oracle on VMware, and deploying APEX with security in mind.

Highlight of the day was OUGN's encounter for Norwegians at OOW 2011 and invited guests (invited VIP speakers for next years OUGN 2012 conference). This took place at The Norwegian Church Abroad. The church has a spectacular view over the city. Among the guests were Jonathan Lewis and Cary Millsap, two of my heroes in the Orace World. I had a long conversation with Cary regarding work, relationship between DBAs and developers, something we both have thought a lot about . I also tried to convince him to come next year and speak at OUGN 2012. Hopefully we can arrange for one of his one day classes a day before the conference.

OOW 2011 Day 0

This year when I was on my way to MOW 2011 in Denmark, a few months after I started at Keystep, my manager called me and asked if I wanted to go to Oracle Open World. That has not happened before in any other job I've had and of course I said yes, so here I am in San Francisco. Arrived late Friday, and spent Saturday adjusting to new timezone and walk around. First observations: really nice city, want to bring my family and see more. Also lots of nice people, with some exceptions from those driving car through the crossing of Lombart street and Hyde Street; they were pointing to the sky with wrong finger.

Going to San Francisco...

...tomorrow to meet a lot of fellow Oracle nerds at Oracle Open World. Looking especially forward to Pythian's bloggers meetup on Wednesday, though my blog has been quite dormant for a while and I might not deserve it that much.  We are planning for next year's OUGN conference, and hopefully we'll have a chance to meet some of them we want to invite. All I know is that we'll have more of what has been a success so far. I might bring a cuddly toy to hand over to that guy who knows so much about partioning.... Until another time zone.

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.