Distributed Computing

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.

Oracle Certified What?

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 those questions are definite newbie type questions. They're also questions that a little thought and looking at the manual should answer if basic common sense doesn't.

Taking the last one first: "how long it take to complete Logical Backup and
Physical Backup"

It depends. Size is just one factor. Machine disk speed, competing jobs, speed of target media &c have a much bigger affect. Try it and see. As you get more experienced you'll be able to estimate how long it will take by taking all the factors into consideration. There's probably a formula somewhere that will produce a precise, supportable, validatable and utterly wrong answer.

Moving back up the mail we get to: "what's the size of SGA for
that Database"

Querying v$sga would be a start. If it's an older version of the database then they'll have to dig into v$parameter and add up the various pools.

And finally: "Could I know size of any database"

I am kinda assuming throughout that the questioner wants to be able to find the size and other factors of a database they manage, rather than for people to pick a database at random and pass on the information on that.

This one is quite hard to answer, not that the information is hard to get to but because there are so many possibe measures of the size of a database. Files on disk? Data volume? Transactions per second? Number of concurrent users?

One of the first two is most likely (given the later questions about backups). For files on disk just query dba_data_files and dba_temp_files and add up the BYTES column then query v$log and sum the BYTES column. If you want you can look at the controlfiles, pfile, spfile and archived logs, it depends how you're measuring (controlfile, pfile and spfile will constitute a tiny proportion of the size of any non-trivial database and space taken by archived logs is likely to vary according to transaction load and your management procedures).

Data volume can be obtained from querying dba_extents and summing the bytes column for all table extents. It won't give a spot on answer due to partially filled extents but should be close enough. No doubt there are scripts out there to measure down to the individual block, frankly I've never needed to be that precise.

Does make on wonder why the OCP is thought of as a desirable qualification if mails like that are flying around.

Another SYSDBA password entry

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*Plus: Release 9.2.0.1.0 - Production on Thu Jul 6 14:30:23 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn sys/sbtest@sbtest as sysdba
Connected.
SQL> alter user sys identified by foobar;

User altered.

In the second session I attempted to connect to the database with the old password and with the new:

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 6 14:31:21 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn sys/sbtest@sbtest as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn sys/foobar@sbtest as sysdba 
Connected.

This confirms that 'alter user...;' does indeed change the password. I then quit SQL*Plus in that session and back in the first session shutdown and attempted to restart the database:

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
ORA-01017: invalid username/password; logon denied

Note that the instance appears to be started (i.e. the database is in no mount state) before the credentials of the user are checked.

I then attempted to shutdown the instance and, when that failed, I tried to start it up (to see if I'd get a insufficient priviledge error or a cannot start already-running ORACLE error):

ORA-01031: insufficient privileges
SQL> startup
ORA-01031: insufficient privileges

. In the second session I recreated the password file with the new password:

$ cp orapwsbtest orapwsbtest.20060706.bak
$ rm orapwsbtest
$ orapwd file=orapwsbtest password=foobar entries=16

and back in the first session tried to connect with the new password and start up the database:

SQL> conn sys/foobar@sbtest as sysdba
Connected.
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first

Note that this time I get the "cannot start already-running ORACLE" error, demonstrating that the instance is at least partly up. I shutdown the instrance then restarted it and changed the SYSDBA password back to the original value using the 'alter user...;' method:

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  555451816 bytes
Fixed Size                   730536 bytes
Variable Size             285212672 bytes
Database Buffers          268435456 bytes
Redo Buffers                1073152 bytes
Database mounted.
Database opened.
SQL> alter user sys identified by sbtest;

User altered.

I then bounced the instance again (using shutdown abort to avoid any possibility of a normal shutdown updating the password file, reconnecting with the changed password to start up). I then went to the second session and tried to connect with the old password, this failed but connecting with the new password worked.

From this it does look like changing the sys password with 'alter user...;' does indeed update the password file.

Oracle answers on the SYSDBA password question

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...'.

SYSDBA password

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 restart.

It is troubling that changing the password in the password file isn't immediately reflected in a running instance. If you're changing the password then, unless it's a scheduled prophylactic change, it's likely because you know or believe someone has the password who shouldn't have or you need to set a throwaway password to allow a consultant short term access then change back when they're done. In either case you want the password to be changed immediately and not requiring a database restart. In my experience getting the business to agree a database restart on a production system is like trying to get a straight answer from Oracle Marketing.