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 […]
Read More SQL Server 2005The 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 […]
Read More Of Goats and Porsches: A Monty Hall Paradox SimulatorSuspect 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 […]
Read More Identify what auditing is onFor 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 […]
Read More NID to fix RMAN-20035: invalid high recidUsing 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” […]
Read More Pre 9202/9014: Advanced queuing runs away with server memory.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 […]
Read More Death MarchThe 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 / […]
Read More Tablespace space with sm$ views + database level space9i 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 […]
Read More Old c compilers needed for 9i on Red Hat LinuxIf 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 […]
Read More rman crosscheck archivelog allThese 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 […]
Read More useful simple vi commands for DBAs