SQL Server 2005 doesn’t prompt during install for locations of datafiles and logfiles, meaning they will end up on C:, which you won’t normally want. You can alter the locations during install, but it’s not especially intuitive. Or at least not intuitive enough for me 
To fix them after the event, see http://www.campus.ncl.ac.uk/databases/sqlser/SQL2005/setupsql2005.html and http://msdn2.microsoft.com/en-us/library/ms345408.aspx
and http://msdn2.microsoft.com/en-us/library/ms143547.aspx
The Monty Hall Paradox can’t be right, or so I thought.
Suppose you’re on a game show, and you’re given the choice of three doors: Behind one door is a car; behind the others, goats. You pick a door, say No. 1, and the host, who knows what’s behind the doors, opens another door, say No. 3, which has a goat. He then says to you, “Do you want to pick door No. 2?” Is it to your advantage to switch your choice?
So I wrote the below simulator to find out. It uses the Monte Carlo Technique, which is a nice way of solving math problems numerically.
The results from my run are pretty conclusive:
Twitchy winners : 653
Twitchy losers : 347
Stubborn winners : 318
Stubborn losers : 682
So it really does pay to switch doors when the host gives you the choice. Which is not what I had expected – and means it’s time for me to go eat some humble pie .
Code listing below:
Suspect too much auditing is on on a database? This script will show you what auditing is on:
set pages 9999 select * from DBA_STMT_AUDIT_OPTS ; select * from DBA_PRIV_AUDIT_OPTS ; set termout off lines 192 spool 1.tmp select * from DBA_OBJ_AUDIT_OPTS ; spool off set termout on lines 80 host egrep '(S/|A/|/S|/A)' 1.tmp
Note that the unix command egrep is used to filter the dba_obj_audit_opts rows of interest.
For errors like:
RMAN-03014: implicit resync of recovery catalog failed RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high recid
You can switch the catalog over to a previous incarnation (before an open resetlogs):
RMAN> list incarnation of database <database name>; RMAN> reset database to incarnation <incarnation number>;
But that is only valid where you want to go back in time to have the catalog work with an old incarnation. If you have mutiple copies of the same database, and want them all registered in the same rman catalog, you have to give the copies different DBIDs, usng the Nid utility:
SQL> shutdown immediate SQL> startup mount $ nid target=sys/password@testdb
(you don’t need to use a connect string here, but if you do, a remote login passwordfile is required)
SQL> shutdown immediate
(at least for 9i, is handled automatically with 10g according to documentation)
SQL> startup mount SQL> alter database open resetlogs; $ rman target / catalog=rman/password@catdb RMAN> register database;
Nid comes with release 9iR2 and above, but can be run against older databases (at least with 8174 64 bit on Solaris), provided you use the connect string syntax with a remote login password file. It is
$ORACLE_HOME/bin/nid
. You have to set your full environment to the new (9iR2 or later) ORACLE_HOME before running nid that way – can use . oraenv naming a dummy (non existant) database and then typing in the new ORACLE_HOME when prompted.
Reference: Metalink Note:224266.1 or Utilities doc
Using the below memory listing script, I found two databases with large PGAs. That was strange since pga_aggregate_target was unset, sort_area_size was just 64k, and there were very few sessions. Checking PGA at session level showed the QMN sessions had high memory usage (over 200mb each). Which pointed to bug 2227360 “QMN process leaking memory”
It is fixed in 9.0.1.4/9.2.0.2 and above, but I was on 9.2.0.1. So the workaround is to kill the QMN (=AQ) sessions. Sounds drastic, but oracle restarts the processes apparently cleanly, with just a message in the alert log like:
Restarting dead background process QMN0
QMN0 started with pid=9
And that is the memory then freed up – for a while at least, you have to be prepared to kill those sessions every so often as the memory leaks build up, if, that is, you can’t patch up.
I used the below script to kill the sessions. I didn’t need to use the drastic “kill -9” on this occasion, but sometimes sessions take forever to go without that.
select 'alter system kill session '''||s.sid||','||s.serial#||''' ;', 'kill -9 '||p.spid from v$session s, v$process p where s.paddr = p.addr and s.program like '%QMN%' /
Reference: Metalink Note 233101.1.
What is a death march project? What makes IT organizations create such things? Why would anyone in his right mind agree to participate in such a project?To many grizzled IT veterans, these are rhetorical questions. Everything, in their experience, is a death march project. Why do they happen? Because corporations are insane and, as consultant Richard Sargent commented to me, “Corporate insanity is doing the same thing again and again, and each time expecting different results.” And why do we participate in such projects? Because, as consultant Dave Kleist observed in an e-mail note, “Death march projects are rarely billed as such, and it takes a lot of work when being hired from the outside to discover if your hiring company is prone to creating death march projects.”
Mind-boggling projects— the project has an army of 1,000–2,000 or more (including, in many cases, consultants and subcontractors), and the project is expected to last seven to ten years.
The sm$ views are an easy way of seeing tablespace space usage. There is also an sm$ts_free view.
set pages 9999 col tot_mb form 999,999 col use_mb form 999,999 col pct_used form 999 select t.tablespace_name, t.bytes/1024/1024 tot_mb, u.bytes/1024/1024 use_mb, 100*u.bytes/t.bytes pct_used from sys.sm$ts_avail t, sys.sm$ts_used u where t.tablespace_name = u.tablespace_name(+) order by 4 desc / -- two queries below for ancient dictionary managed tablespaces only select owner, segment_name, extents, pct_increase, max_extents, next_extent/1024/1024, tablespace_name from dba_segments where extents > max_extents - 5 / select owner, segment_name, extents, pct_increase, max_extents, next_extent/1024/1024, tablespace_name from dba_segments s where next_extent > ( select max(f.bytes) from dba_free_space f where f.tablespace_name = s.tablespace_name) /
– *Update* – and also database level space:
select sum(bytes)/1024/1024/1024 allocated_gb from ( select sum(bytes) bytes from dba_data_files union all select sum(bytes) bytes from dba_temp_files union all select sum(l.bytes) bytes from v$log l, v$logfile f where l.group# = f.group# ) / select sum(bytes)/1024/1024/1024 used_gb from sys.sm$ts_used /
– *Update2* – generate script to add 1000m to each datafile:
set pages 9999 lines 112
select 'alter database datafile '''||file_name||''' resize '||(bytes+1000*1024*1024)/1024/1024||' m ;'
from dba_data_files where tablespace_name in ('TS_INDEX1')
order by 1
/
9i on Red Hat Linux (with the exception apparently 32-bit on RH3) won’t install unless you revert the c compilers back to old versions. Horrible. But fixed (I am told) with oracle 10g.
And its not something you necessarily want to do just for the install – because future patchsets and even some one off bugfixes will need to use the old compilers also.
Also note that on Red Hat, you don’t get a choice with oracle word size – if you have built a 64 bit OS kernel, then you have to use 64 bit oracle, and (obviously) vice versa. That’s different to SPARC Solaris, where you can happily run 32 bit oracle on a 64 bit kernel (if for some strange reason you ever wanted to). However it is possible to run a 32 bit kernel OS on 64 bit hardware. “uname -a” tells you whether the OS kernel is 32 bit or 64 bit – if it reports “i386″, its 32 bit; “i386-64″ means its 64 bit.
On RH4, for both 64-bit and 32-bit:
1) Revert back to an old c compiler (as the root userid):
mv /usr/bin/gcc /usr/bin/gcc.orig mv /usr/bin/g++ /usr/bin/g++.orig ln -s /usr/bin/x86_64-redhat-linux-gcc32 /usr/bin/gcc ln -s /usr/bin/x86_64-redhat-linux-g++32 /usr/bin/g++
2) Set environment variable (as the oracle userid before running installer):
export LD_ASSUME_KERNEL=2.4.19
On RH3 64-bit:
1) apply patch 3423540 before running the installer
2) Set environment variable (as the oracle userid before running installer):
export LD_ASSUME_KERNEL=2.4.19
On RH3 32-bit:
1) Revert back to an old c compiler (as the root userid):
mv /usr/bin/gcc /usr/bin/gcc323 mv /usr/bin/g++ /usr/bin/g++323 ln -sf /usr/bin/gcc296 /usr/bin/gcc ln -sf /usr/bin/g++296 /usr/bin/g++
2) apply patch 3006854 before running the installer
3) Set environment variable (as the oracle userid before running installer):
export LD_ASSUME_KERNEL=2.4.19
(Caveat emptor: I’ve only done installs of RH4 64-bit and RH3 32-bit, so the information for the others is from metalink only.)
References:
RH3 32-bit
RH3 64-bit
RH4 32-bit
RH4 64-bit
If archived redo logs are (wrongly) deleted/moved/compressed from disk without being backed up, the rman catalog will not know this has happened, and will keep attempting to backup the missing archived redo logs. That will cause rman archived redo log backups to fail altogether with an error like:
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
If you can, you should bring back the missing archved redo logs to their original location and name, and let rman back them up. But if that is impossible, the workaround is to “crosscheck archivelog all”, like:
rman <<e1
connect target /
connect catalog username/password@catalog
run {
allocate channel c1 type disk ;
crosscheck archivelog all ;
release channel c1 ;
}
e1
You’ll get output like this:
validation succeeded for archived log archive log filename=D:REDOARCHARCH_1038.DBF recid=1017 stamp=611103638
for every archived log as they are all checked on disk. That should be the catalog fixed, run an archivelog backup to make sure.
[Ref: Metalink ]
These 32 are the only commands I ever seem to use in vi:
| Command | Effect |
|---|---|
| :.= | find out the current line number |
| :1 | go to line 1 |
| Ctrl-d | page down |
| Ctrl-u | page up |
| Shift-G | go to end of file |
| i | insert text at current position |
| Shift-A | append text after end of current line |
| Shift-I | insert text before start of current line |
| Esc | get out of edit mode, back into normal vi command mode |
| dd | delete current line |
| 10dd | delete 10 lines from current line on down |
| d Shift-G | delete all lines from current line and below |
| d 1 shift-G | delete all lines from current line and above |
| . | repeat previous command |
| Shift-Y | yank (copy) current line |
| p | paste that copy into line below |
| /data | search forward for occurencies of string "data" |
| / | search forward for next occurrence of remembered search string |
| ? | search backward for next occurrence of remembered search string |
| :set ic | make searches case insensitive |
| :%s/data/index/g | replace all occurrencies of "data" with "index" |
| :%s/"//g | remove all " characters |
| :%s/$/ ;/ | append ";" to the end of every line |
| :%s/^/rem / | insert "rem " to the start of every line |
| :w | write (save) file |
| :q | quit out of vi |
| :q! | quit out of vi without saving changes |
| :wq | write (save) file and quit out of vi |
| shift-Z shift-Z | same as above ":wq" except does not write (change file modification times) if you have not made any changes. |
| :n | next file (when vi'ing a series of files, e.g. with using "vi *" at the command prompt) |
| u | undo last command |
| Shift-J | Join next line onto end of current line |