Stephen's Oracle Blog

This morning someone passed me a link to " The Eight Fallacies of Distributed Computing ". Looks pretty accurate. I'd say that many of them are doubly true of RAC .

Just recieved the following email via one of the Oracle DBA lists I'm on: Hello, I am XXX from Singapore.I am also an Oracle Certified Professional (OCP).Could I know size of any database and what's the size of SGA for that Database and how long it take to complete Logical Backup and Physical Backup.I will be awiting for ur reply. Thanks in Advance Regards XXXX Names have been removed to protect the guilty. Now, I have no particular problems with answering beginner questions (I've asked a fair few myself), even though the list this email appeared on is suposedly an experienced DBA list, not a newbie list. I'm quite happy to ignore the poor standard of English, the questioner probably doesn't speak English as their first language (although in Eric S Raymond's guide to Newbies, on how to ask questions, he does state that they should be asked in clear, grammatical, correctly-spelled language ). The thing is the questioner is an OCP, or at least claims to be, and...

Simon Kelsey commeted on one of my earlier entries on this subject that x$kzsrt may be involved: SQL> select * from v$fixed_view_definition where view_name='GV$PWFILE_USERS'; VIEW_NAME ------------------------------ VIEW_DEFINITION -------------------------------------------------------------------------------- GV$PWFILE_USERS select inst_id,username,decode(sysdba,1,'TRUE','FALSE'), decode(sysoper,1,'TRUE ','FALSE') from x$kzsrt where valid=1 and username != 'INTERNAL' I then got to thinking, does the 'alter user...;' just alter the copy of the password in memory or does it update the password file as well? I constructed a little test case. First I opened two sessions on the server as the Oracle software owner user (i.e. 'oracle'). In the first I connected to the database as 'sys' using the existing SYSDBA password and then changed the password using the 'alter user...;' method: $ sqlplus /nolog SQL...

QUESTION ========= 1) Does Oracle cache the SYSDBA password? 2) Is there a way to force it to re-read the password file to pick up a changed password without havi ng to shutdown the database? RESEARCH ========= Note.1029539.6 - UNIX How to Set up the Oracle Password File Note 185703.1 - How to Avoid Common Flaws and Errors Using Passwordfile Note 114384.1 - WIN: Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues ANSWER ======= 1) Yes. this is cached. The passwordfile is read only at the beginning, when the instance is started. 2) There is no way to force Oracle read the passwordfile, but one can use : alter user sys identified by ; After this the "cache" should be changed to the new password. Guess that answers that question then. I've double checked the course materials from the Oracle DBA courses I've been on and they all say the way to change the SYSDBA password is to use orapwd, none of them mention using 'alter user...'.

A couple of days ago I posted an entry about an issue I'd run into reseting the SYSDBA password . Today I noticed my hits had jumped, checking my refer log I noticed a lot from Peter Finnigan's blog, he'd posted an entry linking to the post . In the entry he says that, if true, it was interesting. I don't know about interesting, but it's definately a true record of what happened. I'd be suprised if I'm the first person to run into this issue. I did recieve a comment to the entry (from an anonymous user) saying that you have to shutdown the database before recreating the password file. I don't recall ever reading that or hearing it mentioned on a course, could be true or it could be one of the many Oracle myths going around. I found an example on orafaq.com that does include shutting down the database (after recreating the password file) but also talks about changing REMOTE_LOGIN_PASSWORDFILE in the init.ora file, which would require a database rest...

I installed HTMLDB (off the 10.2 Companion disk) on Solaris today. Install seemed to run OK and at the end it told me that the HTMLDB instance had started and could be accessed at: http://wintermute:7778 When I tried to access it, no joy. Said it couldn't connect. I tried using opmnctl to check the status and found that opmn itself was up but none of the managed services were. Stopping and starting opmn and all it's managed processes produced: $ opmnctl stopall opmnctl: stopping opmn and all managed processes... $ opmnctl startall opmnctl: starting opmn and all managed processes... ================================================================================ opmn id=wintermute:6200 0 of 1 processes started. ias-instance id=IAS-X-wintermute.6299 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ias-component/process-type/process-set: HTTP_Server/HTTP_Server/HTTP_Server Error --> Process (pid=24282) failed to start a managed pr...

I was just having a play around on one of my test/sandpit databases ("small database on a low power machine that I use for testing out scripts &c, no live data and if the database gets destroyed I just have to wipe out the files and create a new one" type of thing). I'd forgotten the SYSDBA password (for sandpit systems I use the SID as the SYSDBA password so don't bother to write it down, for some reason this time I didn't) but needed it (I'm installing HTMLDB to try it out on that machine) so wanted to change it. I had been getting 1 : SQL> conn sys/sbtest@sbtest as sysdba ERROR: ORA-01017: invalid username/password; logon denied as you might expect. I used orapwd to change the password $ cp orapwsbtest orapwsbtest.20060629.bak $ orapwd file=orapwsbtest password=sbtest entries=16 OPW-00005: File with same name exists - please delete or rename $ rm orapwsbtest $ orapwd file=orapwsbtest password=sbtest entries=16 and thought I would be good to go....

One of the tasks I've recently been given is to collate information about our Oracle and SQLServer databases. Stuff like sizes, init.ora parameters &c. I've developed a script that gets a lot of the stuff we need out of Oracle. Here it is: set echo off set verify off set feedback off set linesize 132 set pagesize 60 set heading off /* This script must be run as SYS (SYSDBA) */ Prompt "Audit Information" select 'Run on: '||to_char(sysdate, 'HH24:MI DD-MON-YYYY') from dual / prompt prompt set heading on column name heading "Database Name" select name from v$database / prompt column banner heading "Database Version Information" select banner from v$version / prompt prompt column log_mode format a16 heading "Archivelog mode" select log_mode from v$database / prompt prompt column option_param format a70 heading "Option" prompt "Options Installed" select parameter option_param from v$option where value=...

Finally got a response from Oracle, turned out they had put it with their US office who don't start work until 13:00 our time. The eventual response was: 23-MAY-06 14:49:43 GMT DATA COLLECTED =============== On alert.log we can see some: ORA-07445: exception encountered: core dump [00000001009B0B04] [SIGSEGV] [Address not mapped to obj ect] [0x000000000] [] [] Trace file uploaded shows: /opt/oracle/app/oracle/admin/dchr/udump/dchr_ora_5662.trc Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production JServer Release 9.2.0.6.0 - Production *** SESSION ID:(37.1317) 2006-05-18 15:34:04.409 Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, PC: [0x1009b0b04, 00000001009B0B04] *** 2006-05-18 15:34:04.410 ksedmp: internal or fatal error ORA-07445: exception encountered: core dump [00000001009B0B04] [SIGSEGV] [Address not mapped to object] [0x000000000 ] [] [] Current SQL statement for this session: select all count(dm_type.r_object_id) "the_cou...

Late yesterday one of our projects passed me information abvout an ORA-07445 error they'd had earlier in the day, apparently this is stopping one of the modules in their application to stop working. Why they couldn't have told me about it earlier I don't know, I would have had time to raise a Service Request with Oracle during our maintenence hours (the why we don't have 24x7 cover is a rant for another time). I raised a call with Oracle before I went home but there's been no updates on it as of yet, other than a note saying they will update it when they have some information (fair enough I suppose). This long silence is worrying, maybe it's something serious? Here's the header of the trace file: *** 2006-05-19 10:57:39.159 *** SESSION ID:(21.4255) 2006-05-19 10:57:39.158 Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, PC: [0x1009b0b04, 00000001009B0B04] *** 2006-05-19 10:57:39.159 ksedmp: internal or fatal error ORA-0744...

Just had a call from a recruitment consultancy offering me an interview with Barclay's bank for a DBA role looking after their share trading systems. Unfortunately it was a production support only role (aka DataBase Baby Sitting), which would bore me silly, so I had to turn it down. The consultant said he'd got my CV off monster.co.uk. I only put my CV up there last Thursday and this is the third recruitment consultant to phone me having seen it there. Fortunately I'm in the position right now where I have a job I can stand so I can afford to be picky about which jobs I go for, unfortunately all the jobs that seem to be coming through are ones that I either couldn't do (I can't drive so any jobs I can't access via public transport are out) or wouldn't want to do (e.g. the Barclay's one above).

Anyone else use Linked-In ? It's kinda a social networking type of site but business/job seeking oriented. The idea being that you might not know anyone who has the skills you need for a job you have on offer or who can offer a job that would suit your skills but someone that you know might know someone or someone you know might know someone who knows someone &c. Of course it does rely on you actually knowing people and knowing that they have a profile on Linked-In. My profile is at : http://www.linkedin.com/pub/0/131/a3b A few people I know are on there but it just seems so crass to invite someone to link to you.

Had a problem last week. About 20 past 4 in the afternnon (I officially finish at 4pm) on Thursday (5th Jan)I had an email telling me that users couldn't run reports on a particular database. This database sits behind an OLTP app written in Oracle Forms 6i and connects, via a database link, to another database which holds metadata for an instance of Documentum (a document management system from EMC). Users login as themselves identified from their network logon and have permissions on the application schema according to their post. It turned out that the reports that wouldn't run were ones that accessed the remote database. The database link logs onto the remote database as a user which has permissions on the Documentum schema, which is owned by a different user. I had, that afternoon, done some work on the remote database to give the username used by the database link select permission on some views that had been created since the link was set up. After much working of th...

A few days ago I brought up some problems I've found with IT application training and trainers in my entry " (Almost) All Users Are Idiots ". Following a comment from Doug Burns I read an entry in his blog entitled " In Praise of Good Trainers ". Doug (an excellent trainer himself, in my opinion based on having attended two courses taught by him) has layed out a pretty much definitive description of what makes a good Oracle DBA trainer, although I'm sure the same principles could be applied to any subject area. A section of the entry that particularly resonates is near the end where Doug describes three particular aspects of trainers who aren't so good, and who's work he does not respect: Won't divert from the course materials; Tends not to know the answers to questions and may make up answers to hide this; Tends to demean or belittle students. I've had a few trainers that hit one, two or all three of those. I'd perhaps edit the firs...

Project Raptor has escaped (software isn't released, it just sometimes escapes). I downloaded it at work and had a quick play. Overall I liked it a lot. People used to products like TOAD won't see much new. The biggest advantage I've seen is that you don't seem to need any extra Oracle software installed. Just copy the software on to the machine, enter the IP address, the port and the SID/Service Name and you've got a connection. Of course, the other big advantage is that it's free. There's been some interesting discussion of Raptor over on Tom Kyte's blog . I agree that it's probably not for the hardcore DBA, not as a primary tool anyway. Where I can see me probably using it is as something I can give to the non-DBAs who happen to do some DB management (we have a lot of them where I work). I'll have to investigate it further to make sure that it's reasonably safe to let them use it though. I haven't had a chance to read much of the...

The organisation I work for has a large number of sites (literaly hundreds) all connected via a WAN. Whilst for some applications everyone who uses that app are in the same office (or two or three offices) for others the users are widely disperesed, so making maintenence when a database changes name or moves to a different machine very difficult and time consuming. In one case we have a thick client application (i.e. there's a connection directly from the desktop to the database, not going through an application server) where the database is due to move in the near future, this application is used by hundreds of users accross almost as many sites. I was asked to come up with a list of options for providing a centrally administered naming service. One key piece of information is that in this organisation most applications (and their databases) are managed by a departmental support team, hence there is currently no central repository of database names. Oracle Names Server Pros Or...

I just thought I'd mention that I've added a bunch more Oracle related blogs to the list down the right hand side of the page. I recommend that you give them a read. If you know of any others I should be reading (if only I had the time to read more) then feel free to comment here.

No, I haven't turned into BOFH . A common comment I've heard in every IT department I've worked in has been about the idiocy of users, or how one particular user has done something really stupid, because they don't understand IT. But why should they? They're users, they do a job which isn't IT based but just happens to use IT. They're finance people, or HR people or social services people, or planning people, or something else people. They have knowledge and skills (we hope) in other areas, areas that we as IT people almost certainly don't have. Why should they know how the network works? Why should they know how the application they're using works under the hood? Why should they know how the database, the application they use connects to, works? There are a couple of groups I do have a problem with the idiocy (although idiocy might be too strong a word) of, IT (especially application) trainers and IT administrators. An example of why can be s...

On my way home this evening I popped into Waterstone's and bought Tom Kyte's book " Expert Oracle: Signature Edition ". Unusually the off-the-shelf price (47.99ukp) was the same as that listed by Amazon. Obviously I haven't had a chance to read it yet, just flicked through and read a few passages. What I have read looks really good, the chapter on materialized views looks particularly interesting. I do have one criticism though, I suspect it's more a criticism of Apress (the publisher) than of Tom. There is very little white space on the pages, the space around the text to the edge of the page is perhaps one centimetre wide all round. Psychologists will tell you that whitespace is important in textbooks to reduce confusion by the reader. I have a more prosaic arguement, I like to make notes. Plenty of white space, especially between the outer edge of the page and the text, is great for making 'notes in the margin' (and the gutter for that matt...

With a final presentation from Connor McDonald (on running Oracle at home), apparently the latest he has ever stayed on the last day of conference, the UKOUG 2005 conference and exhibition drew to a close yesterday. I found this year's conference very useful, lots of excellent presentations. I learned a lot, from the comments I was hearing, I wasn't the only one. One thing I've noticed over the 4 years I've been is that the exhibition part has been getting les and less useful to me as it has become more and more dominated by Applications companies. With the recent aquisitions by Oracle I cannot see it getting any better, the technologies (database &c) seem to be taking a back seat.