Author Archives: alesk
ORA-600 … While Upgrading Or Patching Databases To 10.2.0.3
If you’re upgrading (or patching) database created as 32-bit to Oracle 10.2.0.3 (64-bit) make sure you read and understand the Metalink note: 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading Or Patching Databases To 10.2.0.3.
Without installing the necessary patch, on top of 10.2.0.3 and before you start your upgrade, you’ll likely hit the bug which will end in corrupted database.
To check, if your database was created as 32-bit, Oracle is suggesting examining string returned by query:
sql> select metadata from sys.kopm$ ;
It you find B023 in the string then database was created as 32-bit, otherwise you’ll find B047.
Metalink note is also telling us that this bug will happen if we patch the 32-bit 10.2.0.2 release to 10.2.0.3 (64-bit). I performed several tests (on Windows x64, with Oracle EE 10.2.0.3 and without any Patch on top of that!) and could not reproduce the ORA-600 error.
Btw. performing the word size change and patching at the same time is not recommended by Oracle – but nevertheless a valid option, otherwise I would expect from Oracle to prevent us from doing that in the first place, by including check in upgrade script or something similar.
Despite of my test results that are suggesting that ORA-600 will not happen during upgrade of ours 32-bit 10.2.0.2 databases, I’ll (of course) follow the recommendation from Oracle and install the Patch 5 (or higher) on top of 10.2.0.3, before I’ll run upgrade/patch script.
For a long time, I thought Oracle will never provide the patch for Windows platform and that we’ll have to wait for 10.2.0.4 release.
(Un)fortunately, Oracle updated the Metalink note with the information about the fix for the bug being included in Patch 5 (and higher) for Windows platform on 14th of September 2007 – four months after the release of Patch 5. May I say, that I’m not a happy camper! :-(
Regards,
Ales
Steps to upgrade Oracle XE Apex to 3.0.1
A short recipe for upgrading Apex in Oracle XE based on official OTN guide:
1) Download Apex 3.0.1 from OTN and unzip the archive into some directory; let’s assume H:\APEX
2) open command prompt with proper ORACLE_HOME and PATH pointing to Oracle XE instance:
cmd> cd H:\APEX cmd> sqlplus /nolog sql> connect / as sysdba sql> @apexins password SYSAUX SYSAUX TEMP /i/ Where positional parameters are: @apexins | admin_password | apex_tablespace | files_tablespace | temp_tablespace | images Post installation (assuming we're still in H:\APEX): sql> @apxldimg.sql H: sql> @apxxepwd.sql password (where password is the password of the Application Express internal ADMIN account)
ArcServe – how to reset forgotten caroot password
Another “lost” sticky note that I needed today :)
1) stop the ArcServe services with cstop utility:
cd C:\Program Files\CA\BrightStor ARCserve Backup
cmd> cstop
2) Find and rename directory with the SERVER NAME under directory named Discovery:
Rename directory:
?:\Program Files\CA\BrightStor ARCserve Backup\Data\discovery\SERVERNAME
to something like:
?:\Program Files\CA\BrightStor ARCserve Backup\Data\discovery\SERVERNAME.BAK
3) Start ArcServe services with cstart
4) Change caroot password:
cmd> authsetup /p newpwdforcaroot
That's it. From now on use the new password to authenticate as caroot.
Exceptional High Memory consumption of Oracle 10.2.0.x on Windows EMT64
While testing 64-bit Oracle EE 10.2.0.2 on Windows 2003 SP1, EMT64 I noticed exceptional high Virtual Memory usage of oracle.exe process during the startup (for example if SGA was sized at 1.5GB, then 4-6GB of VM memory will be reported by Task Manager at instance startup) .
I thought Oracle fixed the problem with memory management on EMT64 in patchset 10.2.0.3 where they claim they fixed the bug 5205552 “EXCEPTIONAL HIG VM SIZE USAGE FOR ORACLE.EXE ON 64 BIT X86 WINDOWS PLATFORM”.
Perhaps they did, but there is another bug left in the code and (at least in my case) quite easily reproduced.
Some facts:
– reproduced on Windows 2003 x64, Enterprise Edition with SP1/SP2
– reproduced with 64-bit Oracle 10.2.0.2/10.2.0.3/10.2.0.3 + Patch 5 bundle for Windows x64
– tested on three different servers (one Dell and two IBM servers)
Steps:
1) make sure you don’t have the following line in the SQLNET.ORA (server side of course):
NAMES.DIRECTORY_PATH= (TNSNAMES)
2) try to create some dummy database link with no TNS entry present in TNSNAMES.ORA, such as:
sql> connect scott/tiger
sql> create database link dummy connect to dummy identified by dummy using 'dummy';
3) the above statement will “hang” for awhile; at this time observe the “Virtual Memory Size”, “Peak Memory Usage” and “Page Faults” within Task Manager for the oracle.exe process. You’ll likely see the excessive growth of Memory usage. Roughly three times the SGA will be used during create database link statement, before Oracle returns control to the user. Imagine this happening on production server where two or three users at the same time send rogue create database link command to the server? They can easily bring the server down.
The only workaround known to me is to make sure that NAMES.DIRECTORY_PATH is present in sqlnet.ora (server side), such as:
NAMES.DIRECTORY_PATH= (TNSNAMES)
I also noticed that this bug is semi reproducible, for example in my case the first create db link will show excessive memory growth, then two or three similar statements will go smoothly, then again, one statement will cause excessive memory allocation etc.
Regards,
Ales
Practical Best Programming with Steven Feuerstein
Today, I attended a free, half day presentation given by Steven Feuerstein. Steven stopped for a day in Slovenia on his way from Zagreb to Bratislava. Event was sponsored by Quest (MRI d.o.o) as part of the promotion of their new tool, called Code Tester for Oracle.
The last time I had a chance to listen to his presentation was 10 years ago, at EOUG in Amsterdam. Back then, he was already a well known for his in-depth PL/SQL knowledge, writing skills and his amusing presentation style. Today, it seems to me, that he is still as enthusiastic about PL/SQL as he was ten years ago, not to mention that he is still one of the best technical speakers that I know of. You simply can’t be bored while listening to him, even if you don’t have a clue about the PL/SQL. Wonderful.
The main topic was test driven development, a trend in software development in the recent years, that we all hear/read about it quite often, but rarely practice it. Why? Because it’s usually hard to do it right. Right? In reality our beloved users test our code. ;-)
Steven mentioned Gartner group reasearch that indicate that for every line of code you have to write ten lines of test code. Ah, yes – don’t forget that test code need testing too ;-), so you really have to add some overhead to that figure. Who has time to do that?
If you’re PL/SQL developer trying to write PL/SQL test code first (personally, I don’t know anyone!) and real code second, you should really give it a try and test out the Code tester for Oracle (Steven himself devoted last two years of his time to this project!). Don’t take this as a cheap marketing plug from my part, it’s just that I have a feeling that Quest has a really unique tool, unmatched on the market and it’s not expensive (580€/developer at present, with 30% discount if ordered before September 25th, 2007)).
If you’re from Slovenia (or nearby), mark the December 3th-4th, 2007 – Steven will be back, with a two day seminar.
Now, I just want to write down some useful links if you wish to read the presentation material, play a game of SET etc.:
- if you’re existing Toad user, bookmark Toad World, even if you’re not, you should at least check Steven Fuerenstein page at ToadWorld
- you can find at ToadWorld.com/SF all his presentation material (including the one I attended today)
- Steven offers his numerous scripts for free at http://oracleplsqlprogramming.com/downloads/demo.zip.
- You can freely use scripts/code and presentation material for your own internal training and application development.
- Steven is suggesting regularly playing a SET game (http://www.setgame.com/set/puzzle_frame.htm), as often as you can, and if at all possible on the company time. If you’re caught, just tell the boss that you’re trying to become a better PL/SQL programmer. And frankly speaking, I have a feeling that I should really play this game 5-10 years ago, extensively :-)
- another recommended game by Steven is Mastermind
- are you trying to systematize error management in PL/SQL? Make sure you check freeware http://www.oracleprogramming.com/downloads/qem.zip
- another freeware, if you wish to hide your tables behind the table API visit http://www.qcgu.net
dbaportal.eu 