This is a nice compliment to the Tusc V$Views poster. You pay $5.99 for shipping, but believe me, the first time you use this in a pinch to find the name of one of those pesky dictionary tables it will have paid itself off.
Syam asked a while ago how we could find something like the first Monday or third Saturday in a month. Well Syam, it’s taken me a while to respond but here we go.
For this example we’ll use
sysdate as input but any Oracle date will work. You can also substitute any other day of the week for Monday.
The first day of the month is probably a good place to start:
SQL> select sysdate from dual;
SQL> select trunc(sysdate, 'MONTH') FROM DUAL;
Now that we’ve got that we can find the first Monday with the
next_day function. Of course we need to remember the
next_day function looks for the next named day after the date provided so we subtract 1 day from the date in case the first is a Monday.
SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday') from dual;
Now that we have the first Monday of the month we can add 7 days to find the second Monday or 14 to find the third.
SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+7 FROM dual;
SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+14 FROM dual;
So from here you can change the day you’re looking for or the week number you want it in.
Despite new “self tuning” features in recent versions of Oracle, database tuning continues to be an essential part of the DBA skill set, but where do we acquire these skills? There is no substitute for experience, but once in a while there’s a roadmap for it.
In their new book Oracle Tuning: The Definitive Reference, Alexey B. Danchenkov and Donald K. Burleson reveal a holistic, platform agnostic approach to tuning the Oracle RDBMS. Both proactive and reactive tuning are given ample treatment while always conveying the “why” and not just the “how”. The techniques presented are complimented by a free copy of the Workload Interface Statistics Engine (WISE) tool (available via download), written by Danchenkov, which provides an interface into the tuning tables and views in Oracle.
The authors, clearly tempered by years of experience, take a very realistic approach to database tuning. They acknowledge that the DBA may not have the time, ability or influence to bring upon an application rewrite or change in server architecture. The bulk of the book focuses on tuning methods within the realm of the database administrator (though all areas affecting Oracle performance are covered.) While focusing on Oracle Database 10g the authors present tuning concepts and techniques in a way that many of the techniques and nearly all the concepts are applicable to all Oracle RDBMS versions.
Thoroughly covering everything from disk to SQL the book is littered with the exact commands you will be running in the field including example output and analysis. The authors have also included several pages of “Silver Bullet” tuning examples. These examples demonstrate how a quick diagnosis and the right tweak can save the day.
Testing a hypothesis on a large active database is like trying to tune a car while it’s flying down the freeway at 75 miles per hour.
This book is not for the beginner. If you do not feel confident about your knowledge of the Oracle architecture you will feel overwhelmed by this book. Of course if you do not feel confident about your knowledge of the Oracle architecture you should not be tuning a database.
For those comfortable with Oracle but new to tuning there will be many paragraphs you will read, re-read, then read again, but Danchenkov and Burleson have not missed a step. On almost every topic there are a couple notes on common pitfalls and how to avoid them. The authors have really taken great care to shepherd you safely through all steps of tuning the database.
In barely less than 1,000 pages, Danchenkov and Burleson have compiled the definitive reference for Oracle tuning. Coupled with a good background in Oracle, this book contains everything you need to tune almost every aspect of the Oracle database. I highly recommend it to the Oracle professional looking to learn about tuning or the experienced tuner looking for a good reference. The type of tuning presented in this book could easily lower your hardware costs and make you a rock-star DBA.
Oracle Tuning: The Definitive Reference
By Donald K. Burleson and Alexey B. Danchenkov
Copyright 2005 by Rampart TechPress. All rights reserved.
Kittrell, North Carolina, USA.
Available at Rampart-Books.com
I ran into this little bug when trying to restore a databases server parameter file using RMAN.
Oracle 9iR2, RMAN using a recovery catalog, incremental level 0 backup of the complete database.
I shut down my test database and removed the spfile. I should be able to recover it easily with the following commands:
$ rman target=backup_admin/password catalog=rcat_user/password@rman
RMAN> startup nomount;
RMAN> restore spfile from autobackup;
But instead I get this error:
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-03002: failure of restore command at 06/29/2006 16:48:26
RMAN-12010: automatic channel allocation initialization failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog
Now my ORACLE_SID is set and dandy, the target most certainly is in the recovery catalog, so why do I get this error?
Since I was only using an spfile (no init.ora at all) there was nothing to set the db_name as the database started up. To resolve this I created an initdoomed.ora in the default location with only the following line in it (my database is named doomed.)
Returned to RMAN and tried all this again:
RMAN> restore spfile from autobackup;
Starting restore at 30-JUN-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20060630
channel ORA_DISK_1: autobackup found: c-2546195804-20060630-01
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 30-JUN-06
Success! You can now shutdown the database, remove the spfile if desired and startup the database with the spfile in place.
The webcasts are on different services and in different formats but they are all fairly short. Some of the topics are best suited to advanced audiences, but there’s plenty there for everyone.