Finding the first or second Monday in a month

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;

SYSDATE
---------
18-JUL-06

SQL> select trunc(sysdate, 'MONTH') FROM DUAL;

TRUNC(SYS
---------
01-JUL-06

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;

NEXT_DAY(
---------
03-JUL-06

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;

NEXT_DAY(
---------
10-JUL-06

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+14 FROM dual;

NEXT_DAY(
---------
17-JUL-06

So from here you can change the day you’re looking for or the week number you want it in.

oracle, date functions, sql, database, database development, pl/sql

Recovering spfile with RMAN

I ran into this little bug when trying to restore a databases server parameter file using RMAN.

The scenario

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-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
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?

The solution

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.)

db_name=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.

oracle, database, backup, 9ir2, rman, dba

Oracle Auto Increment Columns – Part 2

Three separate people have commented on my previous article on How to Create Auto Increment Columns in Oracle asking how they can retrieve the value of an auto increment column for use later in their code. Well Daniel, Shaun and Zach, here’s the answer.

After you have referenced sequence.NEXTVAL for a particular sequence (or it is referenced on your behalf by, say, a trigger), you can then reference sequence.CURRVAL to get the value just used for NEXTVAL.

To illustrate this we’ll use the table, sequence, and trigger created in my previous article.

If we insert a row into the table test, the trigger test_trigger automatically calls test_sequence.NEXTVAL.

SQL> insert into test (name) values ('Matt');

1 row created.

We now have test_sequence.CURRVAL available in that session.

SQL> select test_sequence.currval from dual;

CURRVAL
----------
8

In this simple example we can confirm this is the same value just used with this simple query:

SQL> select * from test
where name='Matt';

ID NAME
---------- ------------------------------
8 Matt

Now if we wanted to use this value in another SQL statement, say for an insert on a table which uses this as a foreign key constraint, we can include it on our insert like this:

SQL> insert into tool (owner_id, tool)
values (test_sequence.CURRVAL, 'hammer');

1 row created.

SQL> select * from tool;

OWNER_ID TOOL
---------- ------------------------------
8 hammer

Of course, if we just want to see the value of test_sequence.CURRVAL we can select it from our favorite table dual.

SQL> select test_sequence.currval from dual;

CURRVAL
----------
8

For the table, sequence and trigger used here see my original article on auto increment fields.

Shifting Demand for Database Books

Donald Burleson of Burleson Consulting points out some interesting statistics from Tim O’Reilly on trends in the tech book market.

If we assume that people are buying books because of a market demand, we see Oracle is steep decline and SQL Server book sales up 83%, followed closely by PostgreSQL. We saw this exact same trend in 1992-1995 when Oracle books started to dominate the database book market, displacing DB2 and IDMS/R books.

As a whole, the big news is that database book sales are way-down with the exception of PostgreSQL and SQL Server books, which are up 83% and are now double the size of the Oracle market.

Check out Donald Burleson’s full article

Some of this shift may be due to the recent release of Microsoft SQL Server 2005. Dispite it’s small overall percentage, the growth in PostgreSQL book sales is significant enough to keep an eye on it in the near future.

Also interesting is the stagnation of the MySQL book sales, down 2% from last year. With the number of blogs, wikis and other relatively hot technologies running on MySQL I’m surprised this number is down.

In contrast to the book sales, Alexa, which measures a number of statistics to determine rank among web pages, shows increased web ranking for Oracle, MySQL and PostgreSQL, while showing decreased traffic to Microsoft’s corporate site.

Graph by Alexaholic.com

For the full scoop according to Tim O’Reilly, check out his articles State of the Computer Book Market, Part 1, Part 2, and Part 3.

books, book, tech books, technology, computers, database, dba, database administration, publishing, oracle

Banner/Oracle User Conference

Yesterday I attended a Banner/Oracle user conference at Wellesley College. This annual, one-day event of largely peer presentations was a great opportunity for networking and knowledge exchange. Here are some of the take-homes I got out of this conference in no particular order. These are just my observations and opinions and pertain to the scope of our institution and environment.

Oracle Database

Marc Kelberman, Oracle pre-sales engineer for higher-ed gave a presentation on RAC/Grid control and SQL Developer.

Very few have moved from 9i to 10g. Those who have, or will soon, are going straight to 10gR2.

It is important to go to the latest patchset for 10gR2.

Oracle’s RAC/Grid technology is very cool, but it is unlikely it would offer much to our small (6,000 student) university. Larger universities may benefit from it.

Grid/RAC requires a shared storage architecture.

It would take a great effort between systems, networking and database administrators to implement RAC/Grid.

Around 75% of the institutions I spoke to were running Oracle on Sun Solaris.

None of the institutions I spoke to were running Oracle on Linux.

None of the institutions I spoke to were running clustering/RAC/Grid Control.

Several institutions are running some type of network attached storage (NAS) to store their data files.

One institution (I believe this was the host institution, Wellesley) is running their Oracle home directories on network attached storage. This allows them to maintain only one oracle home per database version saving hours of work per upgrade.

Most institutions are still relying on cold backups as their primary backup method.

Only a couple institutions I talked to have adopted Oracle RMAN for backups.

Oracle SQL Developer

Marc did a good demonstration of SQL Developer, but this product is hard to appreciate until you’ve used it. Thankfully it’s free, so there’s no good reason not to test drive it.

Identity Management

Dan Sterling, Chief Technology Architect for SunGard Higher Education presented on SunGard HE’s plans for identity management. It looks like they will focus on integrating with third party tools via open standards.

Banner Student Information System

One university mentioned that when a user asks for a modification to their student information system they require that the user submit a request for product enhancement with the vendor before a local modification is made. This seems like a good policy.

Final thoughts

The relatively small number of attendees made this conference great for networking. It’s very interesting to interact with universities of different sizes. Some of the universities in attendance had one administrator for their Oracle databases, application servers and application support, while others have a large staff and highly individualized positions.

Though this conference happens near the end of the academic year it was a nice diversion from summer planning. Beyond the networking, the take-homes are more than worth the day out of work.

oracle, banner, sungard he, summit, conference, higher education, database, database administration, database development