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 […]
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 DBAsVery useful SQL, from Tom Kyte: select decode(count(*), 1, ‘spfile’, ‘pfile’ ) from v$spparameter where rownum=1 and isspecified=’TRUE’ ; If it it using an spfile, an easy way to see the parameters is to generate an init.ora copy: SQL> conn / as sysdba (or connect as sysoper) SQL> create pfile = ‘/tmp/init.ora’ from spfile;
Read More Is database using pfile (init.ora), or spfile?Thanks to Laurent Schneider for tips on how to handle listener passwords in scripts. [Update 12-Jan-2007 – ammended script following Laurent’s comment, and update 09-Feb-2007 – ammended script following Kevin’s comment] A system startup/shutdown shell script I just used that copes with listener passwords is below. The location of the listener.ora file was /etc in […]
Read More 9i unix startup / shutdown with listener passwordThis shell script runs against every database that is up and running (has an entry in the ps list). Which I think is better than searching through oratab (since not all of those may be up and running, esp. on dev machines). You can run any SQL within it, but this particular version prints out […]
Read More Script run against every running database, listing total oracle memory (sga+pga)