Re-installation of GC repository keeping software tree intact

Some days ago I trashed my 10.2 database when testing conversion of database character sets. This database held the repository of my Grid Control.

So now I had a nice installation of the 10.2.0.1 GC software, with patches to 10.2.0.4, all intact, but no repository. (and no backup :-) I figured that I could re-create the repository, just as easy as it is with Database Control using emca, as that script was present in the oms home, but oh no. Googling and searching Metalink did not give any hints, so finally I gave up at read the documentation, and finally, it was quite easy:

[oracle@localhost oms10g]$ cd $ORACLE_HOME/sysman/admin/emdrep/bin
[oracle@localhost bin]$ ./RepManager localhost 1521 ORCL -sys_password WAS_CHANGED_ON_INSTALL -action create

<.... much output about many strange items like Forms etc (!) ....>

Done.
Repository Creation Successful.

But when going to website, it complained about emkey not being configured correctly, or that it might be corrupted.

[oracle@localhost bin]$ emctl status emkey
Oracle Enterprise Manager 10g Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Please enter repository password:
The Em Key exists in the Management Repository, but is not configured properly or is corrupted in the file system.
Configure the Em Key by running "emctl config emkey -repos".

[oracle@localhost bin]$ emctl config emkey -repos
Oracle Enterprise Manager 10g Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Please enter repository password:
The Em Key has been configured successfully.


TADA.... The website now works. But the targets are gone, and my agent does no upload.

[oracle@localhost agent10g]$ emctl upload agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

Seems reasonable as I used secure OMS and agent, so:

[oracle@localhost agent10g]$ emctl clearstate agent
[oracle@localhost agent10g]$ emctl secure agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Enter Agent Registration password :
Agent successfully stopped... Done.
Securing agent... Started.
Requesting an HTTPS Upload URL from the OMS... Done.
Requesting an Oracle Wallet and Agent Key from the OMS... Done.
Check if HTTPS Upload URL is accessible from the agent... Done.
Configuring Agent for HTTPS in CENTRAL_AGENT mode... Done.
EMD_URL set in /opt/oracle/product/agent10g/sysman/config/emd.properties
Securing agent... Successful.
Agent successfully restarted... Done.
[oracle@localhost agent10g]$ emctl upload agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully


That's it!
:-)
Kim

Installation of Oracle 9.2 on OEL5 / RHEL5

Hi

It has been a while (almost two Years!) since I last blogged. Been busy :-)

For testing purposes at a customer, I have been trying to install Oracle 11.1, 10.2 and 9.2. I choose Oracle Enterprise Linux 5.2, thinking the latest would be the easiest. This was probably a bad idea though.

Anyway... 11.1 and 10.2 went smoothly, but with 9.2, the installer would not start.

I have been trying many things, but the solution appears to be simple: patch cd 1 with Metalink Patch no. 3906173. In effect, it replaces a jar file and setting JRE_LOCATION to a jre1.3.1 (which I had to download).

Also, with 9.2.0.4, we now need to export ORACLE_HOME to a valid (and writeable) destination. (bug 3547724)

There might be some dependencies to some RPM's, as I have installed several in a desperate attempt to get this to work :-)

This I will leave up to You to verify - have fun!

/Kim

MV from single byte to multibyte database, Part II

I think I have found a bug in 10.2.0.3.
- I will try to describe this as short as I can.

I used CAST() to force data from an 8i database into a format that I preferred.

The first FAST refresh was ok, as the mv log was empty, but when data arrived, it failed, as the 8i database could not handle/understand the CAST function.

Strangely, it worked, if there were CLOB's being replicated too.

I traced the two situations, and saw that it in both cases first tries to select CAST()... from ... @remoteDB.

But in the succesfull (CLOB) case, it then does
SELECT * from mlog$_mytable@remoteDB
SELECT * from mytable@remoteDB
SELECT * from mlog$_mytable@remoteDB
SELECT * from mytable@remoteDB (and now MANY waits)
UPDATE localTable...
UPDATE localTable...
....

So it seems that the introduction of the CLOB column somehow enables an exception handling, that causes the refresh to use another approach instead of failing.

We were so lucky, that in the case with the MV without a CLOB, the master table had a CLOB, so when we added this to the MV, everything was fine.

Strange.

MV from single byte to multibyte database

Today I did some interesting things (at work :-)

At a customer, they need to replicate a table from a single byte character database (8.1.7.4.1) to a 10.2.0.3 with UTF8.

And the table contains clobs.

In their existing setup, all character columns are tripled in length (due to character conversion), which means that VARCHAR2(256) translates to VARCHAR2(768) in the MV.
This gives many problems, one being with indexes as the index key cannot contain more then a little over 3000 bytes on this database (8k blocksize).

It is not possible/a good idea to precreate a table with columns matching the origin table, as special characters might be stored with more then one byte, and thus we can end up with values that does not fit into the MV.

This is also the reason for Oracle to automatically expand the columns.

My idea was then to create a table with the (character) column's length specified in CHAR's, not BYTES. This worked beatifully, and then create a MV on this prebuilt table, where I in the select CAST() the columns into lengths specified in CHAR's.

This resulted in ORA-22992: Master table Materialized View references contains a CLOB column. ( This is in fact a bug, 4043461), still not fixed.
Solution is: Create the Materialized View without using the 'build deferred' clause.

OK. This might even be good, I realized after a while.
*Maybe* I could create the MV without a prebuilt table, and only CAST() the coulumns, and sure enough.

First time though it failed with the annoying ORA-12060: shape of prebuilt table does not match definition query.
It was in fact a error-40: Error happening 40 centimeters behind the monitor.
I had accidently cut a "0" away from a colum, so it was defined as 100 CHARS and not 1000 CHARS, as it should.

When I fixed that error, it worked, I thought. But no, not there yet.

The refresh jobs that were created for be by the create MV statement called dbms_refresh(), and they failed consistently with an obscure error:

ORA-12008: error in materialized view refresh path
ORA-00907: missing right parenthesis
ORA-02063: preceding line from DLPROD
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 2

Was it the 8.1.7 database not being able to handle my CAST() workaround?
At the top of my head, I thought it might be time to ask Support, but then I remembered the database version and how they would most likely just close it, unless it reproduced in a supported version.

I tried with dbms_mview.refresh( 'MV', METHOD => 'F'), and got the same error.
After working with it for a while, I found that this worked:

dbms_mview.refresh('MV', METHOD => 'F', NESTED => TRUE);

FINALLY :-)

It refreshed.

Project - plan stability, Oracle 9.2

Recently I was asked to build a solution to analyze the impact of patching/upgrading a database and gathering new statistics on a database.

The (huge) application that runs on this database encorporates some of the best of breed technologies, as hardcoded values, dynamic in lists etc. Also, the SQL's are generated by the client very dynamically, so the sheer number of SQL's are also huge, and it is literally impossible to analyze it by looking at the code.

Great.

So I decided to record the SQL's from v$sql etc. on the production instance, clone the database to a test server and here do the patch/upgrade+statistics. The hope is then that most of the SQL's remain the same, and that we are able to categorize the ones that change execution plans into something digestible. (One tool I plan to use is Tom Kyte's utility to strip hard coded values).

Hope is good.

Now, how do we work out some time estimates for this project - how many hours should we spend tuning the unknown number of statements that perform worse :-)

Learning blogger

This seems like a great tool, but I would like to have more control of the code snippets etc. Fx. are indentation removed, something I find annoying.

Forgot the all important str2branch function

This function was inspired by the str2table function, which can be found on asktom.oracle.com.

CREATE OR REPLACE TYPE node_parent_type AS OBJECT ( lvl NUMBER, node VARCHAR2(200), PARENT VARCHAR2(200) )
/

CREATE OR REPLACE TYPE str2Branch_Type AS TABLE OF node_parent_type
/

CREATE OR REPLACE FUNCTION Str2Branch ( p_string IN VARCHAR2 , p_delim IN VARCHAR2 )
RETURN str2Branch_Type
PIPELINED
AS
l_str LONG;
l_piece LONG;
l_parent LONG := NULL;
l_n NUMBER;
l_lvl NUMBER := 0;
BEGIN
l_str := p_string;
LOOP l_n := INSTR(l_str, p_delim);
IF (l_n = 1) --Starts with a delimiter
THEN l_n := INSTR(SUBSTR(l_str,2), p_delim);
END IF;
EXIT WHEN (NVL(l_n,0) = 0);
l_piece := SUBSTR( l_str, 1, l_n ); --First piece
l_str := SUBSTR( l_str, l_n+1 ); --Rest
l_n := INSTR( l_piece, p_delim, -1 );
EXIT
WHEN (NVL(l_n,0) = 0); --Only needed, if we have to end with a delimiter.
pipe ROW( node_parent_type ( l_lvl, LTRIM(RTRIM(SUBSTR(l_piece,l_n+1))), l_parent ) );
l_parent := LTRIM(RTRIM(SUBSTR(l_piece,l_n+1)));
l_lvl := l_lvl + 1;
END LOOP;
RETURN;
END;
/