Posted by Ceri Davies
Wed, 14 Jun 2006 19:52:00 GMT
I recently had cause to arrange for Oracle’s RDBMS 10g to be started at boot time. This led me to despair somewhat at the state of shell scripting in general, and I will rant a little on that subject. This isn’t really intended as an attack on this particular script, but these are the issues that arose from it.
The Oracle installation provides a couple of scripts named dbshut and dbstart that look like they’ll do that job. Indeed, the top of dbstart states:
# This script is used to start ORACLE from /etc/rc(.local).
The corresponding RCS log from the import tells a different tale:
$ svn log -r99 dbstart
Password for 'ceri':
-------------------------------------------------------------------
r99 | ceri | 2006-06-09 15:27:33 +0100 (Fri, 09 Jun 2006) | 5 lines
Add a bunch of scripts used for looking after our databases.
Mainly culled from our live systems, with the notable exception
of the Oracle provided utilities dbshut and dbstart, which are
as out of the box here (and therefore do not work).
-------------------------------------------------------------------
Read more...
Posted in Oracle, Software, Solaris | 4 comments
Posted by Ceri Davies
Fri, 28 Apr 2006 19:57:00 GMT
Our T2000 arrived. We went for the 8-core 1.0GHz, 16GB version to allow us to use the Solaris resource management tools to carve it up in various configurations.
So far I’ve used it to great effect to test out an Oracle Data Guard configuration that I’m building, and particularly to understand how you are supposed to set up your clients in order to benefit from the redundancy goodness — some notes on that will be forthcoming real soon as I couldn’t find a single useful document on that.
Michael Bushkov’s Summer of Code project, cached(8), which adds caching for nsswitch along with enabling nsswitch for the services, protocols and rpc databases finally got committed today. This is really interesting work, similar to nscd(1M) on Solaris1, but with each user having their own cache.
Also, I discovered that a post on BSDNews is a good way to saturate a crappy cable modem link. :-)
Oh yeah, and John Birrell is making superb progress on a DTrace port to FreeBSD.
1 Yes, nscd(1M) has a bad reputation, but I strongly believe that’s because people don’t understand how to work it.
Posted in FreeBSD, Oracle, Solaris, Sun | no comments | no trackbacks
Posted by Ceri Davies
Sun, 05 Feb 2006 22:15:00 GMT
A few months ago I complained about ropey parameter handling in SQL*Plus. I found a solution.
Read more...
Posted in Oracle | no comments | no trackbacks
Posted by Ceri Davies
Fri, 13 Jan 2006 11:54:00 GMT
I continue to learn about Oracle, and that includes finding tasks that should be simple but that seem intolerably difficult. I’m sure that this is a result of my ignorance, so please do correct me if you know better.
So long as you have a basic understanding of Oracle, you’ll know that filling up a tablespace is bad. Here’s a bit of SQL that tells you which tablespaces are getting full:
SELECT t.tablespace_name,
decode(t.status, 'ONLINE', t.status, nls_initcap(t.status)) AS status,
(tsa.bytes / 1024) AS ts_size,
((tsa.bytes - decode(tsf.bytes, null, 0, tsf.bytes)) / 1024) AS used,
(decode(tsf.bytes, null, 0, tsf.bytes) / 1024) AS free,
(round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) AS percent
FROM sys.dba_tablespaces t, sys.sm$ts_avail tsa, sys.sm$ts_free tsf
WHERE t.tablespace_name = tsa.tablespace_name
AND t.tablespace_name = tsf.tablespace_name (+)
ORDER BY percent DESC, t.status, t.tablespace_name
/
which gives output somewhat like:
TABLESPACE_NAME STATUS TS_SIZE USED FREE PERCENT
------------------------------ ------ ---------- ---------- ---------- ----------
XDB ONLINE 46080 45824 256 99
CWMLITE ONLINE 20480 15168 5312 74
SYSTEM ONLINE 512000 358656 153344 70
DRSYS ONLINE 20480 4928 15552 24
WHATEVER ONLINE 2097152 120768 1976384 6
TOOLS ONLINE 10240 64 10176 1
INDX ONLINE 25600 64 25536 0
UNDOTBS1 ONLINE 1048576 1856 1046720 0
USERS ONLINE 25600 64 25536 0
9 rows selected.
This gives some pretty good stats, but there is a problem. These statistics only count the blocks that are currently allocated, and tablespaces can be set to autoextend.
This means that the statistics above don’t actually indicate whether you’re about to run out of space or not, since a tablespace that looks %99 full above might be allowed to autoextend another 5GB[1]. Basically, you don’t want to get out of bed on the basis of this script, because by the time you do, the tablespace might have autoextended and fixed the problem all by itself. So let’s try to work around that.
Strictly speaking, tablespaces do not autoextend; the data files that act as a tablespace’s backing store do. Oracle maintains a table called DBA_DATA_FILES which contains file names, internal file ids, and the AUTOEXTENSIBLE flag indicating whether they may autoextend or not. So we can qualify the output of the previous script a little with an AUTOEXTEND column:
SELECT t.tablespace_name,
decode(t.status, 'ONLINE', t.status, nls_initcap(t.status)) AS status,
(tsa.bytes / 1024) AS ts_size,
((tsa.bytes - decode(tsf.bytes, null, 0, tsf.bytes)) / 1024) AS used,
(decode(tsf.bytes, null, 0, tsf.bytes) / 1024) AS free,
(round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) AS percent,
dbf.autoextensible AS autoextend
FROM sys.dba_tablespaces t, sys.sm$ts_avail tsa,
sys.sm$ts_free tsf, dba_data_files dbf
WHERE t.tablespace_name = tsa.tablespace_name
AND t.tablespace_name = dbf.tablespace_name (+)
AND t.tablespace_name = tsf.tablespace_name (+)
ORDER BY percent DESC, t.status, t.tablespace_name
/
which looks like:
TABLESPACE_NAME STATUS TS_SIZE USED FREE PERCENT AUT
------------------------------ ------ ---------- ---------- ---------- ---------- ---
XDB ONLINE 46080 45824 256 99 YES
CWMLITE ONLINE 20480 15168 5312 74 YES
SYSTEM ONLINE 512000 358656 153344 70 YES
DRSYS ONLINE 20480 4928 15552 24 YES
WHATEVER ONLINE 2097152 120768 1976384 6 NO
TOOLS ONLINE 10240 64 10176 1 YES
INDX ONLINE 25600 64 25536 0 YES
UNDOTBS1 ONLINE 1048576 1856 1046720 0 YES
USERS ONLINE 25600 64 25536 0 YES
9 rows selected.
As you can see, where the XDB tablespace looked in trouble before, we can now safely ignore (assuming that the disk that the data file resides on has space, but that’s another article - see footnote 1).
Unfortunately, this is broken too. A tablespace may be made up of multiple data files, and we haven’t made any consideration for that at all here; in fact the above extract will just give multiple rows if a tablespace has more than one data file. What’s worse, those rows may contain different values in the AUTOEXTEND column, since one data file may be set to autoextend while another is not.
I don’t actually have any production databases that contain tablespaces comprising of multiple data files, so for now, I’ve given up at this point. Someone must be monitoring this properly though, so please let me know how you work out whether to get out of bed or not in this situation.
[1] The effect that having 15 tablespaces all set to autoextend has on the interpretation of the amount of free disk reported by df(1) is an exercise for an entirely different day…
Note: Apologies for the whitespace here. I can’t get markdown to stop fiddling with it for some reason.
Posted in Oracle | 1 comment | no trackbacks
Posted by Ceri Davies
Mon, 03 Oct 2005 19:47:00 GMT
My foray into Oracle continues; today I got to see the horrors of the DBMS_JOB package, which is used for scheduling periodic tasks (think cron(8)).
This particular database had a couple of jobs scheduled, but they were not being run. I knew how to sort that out; in 9i there is a database parameter called job_queue_processes which governs how many job runners are allowed concurrently, and this was set to zero:
SQL> show parameters job
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0
There were, therefore, no job runners:
SQL> !ps -ef|grep j
oracle 6183 6182 0 14:13:15 pts/1 0:00 grep j
oracle 6182 6166 0 14:13:15 pts/1 0:00 /bin/sh -c ps -ef|grep j
This is a dynamic parameter, so I changed it:
SQL> alter system set job_queue_processes = 2;
System altered.
SQL> !ps -ef|grep j
oracle 6226 6166 0 14:17:35 pts/1 0:00 /bin/sh -c ps -ef|grep j
oracle 6219 1 0 14:17:30 ? 0:00 ora_cjq0_WXYZ
oracle 6220 1 0 14:17:30 ? 0:00 ora_j000_WXYZ
oracle 6222 1 0 14:17:30 ? 0:00 ora_j001_WXYZ
Since there were jobs that needed running, the ora_cjq controller instantly fired off a pair of ora_jnnn processes to run them. Unfortunately, one of them was an analyze of the SYS schema, which immediately caused deadlocks since this was the middle of the day. And this despite the job not being scheduled until what I thought was 3am:
SQL> select job,last_date,next_date,
substr(interval,1,24) interval,
substr(what,1,48) what
from dba_jobs
where job = '2';
JOB LAST_DATE NEXT_DATE INTERVAL WHAT
--- --------- --------- ------------------------ ------------------------------------------------
2 03-OCT-05 17-OCT-05 trunc(sysdate+14)+27/24 dbms_utility.analyze_schema('SYS','COMPUTE');
However, Oracle insisted on playing catchup there and then, which made me doubt whether trunc(sysdate+14)+27/24 really did mean “14 days from now at 3am”.
Unfortunately the docs suck big time; the definition of INTERVAL doesn’t exist in the Oracle9i Database Administrator’s Guide, and the excerpt below tells you everything you need to know in order to understand my pain (my emphasis):
If you always want to automatically execute a job at a specific time, regardless of the last execution (for example, every Monday), […]
It just strikes me that “Monday” isn’t all that specific…
Posted in Oracle | 1 comment | no trackbacks
Posted by Ceri Davies
Fri, 23 Sep 2005 12:01:00 GMT
In my new job, I’ve inherited the dubious pleasure of babysitting some Oracle 9i databases. Though I am somewhat familiar with Oracle, I really only know enough to know that I don’t know enough, so when I wanted to see where all the space was going, having already identified the tablespace that I was interested in, I issued the following, perhaps naive, statement:
SELECT segment_name, segment_type, bytes/1024/1024 MB
FROM dba_segments
WHERE tablespace_name = 'WIBBLE_DATA'
ORDER BY bytes;
That turned out to be a lot of typing for something that I wanted to do a lot, so I decided to throw it in a script, named segmentsbytablespace.sql (yes, that *is* quite a long file name – your point?). The next logical step was obviously to parameterise the tablespace name. I simply cannot believe the can of worms that this opened.
If you’ve run a script in SQL*Plus before, you’ll probably know that arguments to scripts can be accessed by the positional parameters &1, &2, …, &n. So the script just becomes:
SELECT segment_name, segment_type, bytes/1024/1024 MB
FROM dba_segments
WHERE tablespace_name = '&1'
ORDER BY bytes
/
and is run like this:
SQL> @segmentsbytablespace WIBBLE_DATA
That would be too easy; there are a couple of problems with this.
The first is simply that if you leave out the parameter, SQL*Plus prompts you for its value, like this:
SQL> @segmentsbytablespace
Enter value for 1:
Enter value for what now? I’d like it to say Enter value for tablespace. It looks like it is impossible for me to do that (unless I throw that variable at the database server and ask if it is NULL or not; I’m not doing that). This issue is, at least, cosmetic.
The second problem is that running the script with an argument defines the same argument for the next invocation, i.e. in this example:
SQL> @segmentsbytablespace WIBBLE_DATA
SQL> @segmentsbytablespace
both scripts believe that they have been passed WIBBLE_DATA as an argument. I have to explicitly undefine the positional parameter 1 at the end of my script to avoid inadvertently passing it to another script executed in the same session.
For those who know SQL*Plus, you also benefit from this “feature” if you use the &&1 form of variable dereferencing in order to avoid prompting your users for the same value more than once.
Perhaps I missed something, but this is documented behaviour and looks like an accident waiting to happen to me.
Posted in Oracle | no comments | no trackbacks
