OOW 2011 Day 3

Went to five presentations, two of them stood out. Doug Burn spoke about SQL plan management in 11g and I learned some new tricks. The material and the content was quite good, also I like these comments that may not be in the slides, e.g. he quoted Jonathan Lewis who said that if you have a narrow problem you should apply a narrow solution, an advice against changing a system wide parameter just because you have one SQL statement gone astray.

The other presentation I liked a lot was about mining the AWR data with SQL, by Yury Velikanov from Pythian. The presentation was packed with useful examples and based on experience he had with customers. When he started almost all seats were taken, and then some people left, maybe it was over their head, but for me, I like these kind of presentations. Yes, it is cool with fancy colorful slides where rule number one is max six words pr slide, but I don't mind seeing code in the slides once in a while. I think that if you strive to understand the AWR repository and analyze it the way Yury did you will have a better understanding on how things are connected, in a way you cannot have when looking on nice graphs in EM. Also you see the limitations in AWR, most data are aggregated which creates other limitations; see Cary's presentation about skew. He made a point in the beginning that there are different concepts to use when attacking optimization and troubleshooting, he gave pointers to tools and methods. All in all a presentation that I have to download and study for a while.

Another great day.

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.