DBA Lounge

Metalink Note on Datafile Recovery Will Corrupt Database

Thinking I had something new, I wrote this article about recovering deleted files. However, it turns out Frits Hoogland had already blogged about recovery of deleted files on linux, as Frits pointed out in a comment on my blog, where he also mentioned a metalink note on this matter. The procedure outlined in the note describes how to recover the deleted file and put it in the same location as the deleted file. The problem is that it doesn’t include offlining/onlining the file, so the database ends up with two distinct copies of the file.

Installing Oracle 11g on Ubuntu Linux 7.10 (Gutsy Gibbon)

After many requests from readers, I’ve put together new, revised version of the Oracle 11g on Ubuntu recipe. This new version is a little different than the first one published: it’s based on a bare-bones install of Ubuntu 7.10 (Gutsy Gibbon) server version instead of the desktop version. As an improvement, I’ve tried to pare down dependencies to a minimal set. Your feedback is more than welcome — it’s the main reason why I wrote a new version of this HOWTO. I’ve also tested and repeated this procedure twice. Even so, it might still have problems, so please let me know so we can improve it 1.

MySQL Toolkit – Archiver

The MySQL Toolkit can be found at http://mysqltoolkit.sourceforge.net/. It is coded and maintained by Baron Schwartz (www.xaprb.com). I’ve been using the archiver tool he wrote lately, and wanted to share this tool. In every web environment I’ve worked in, there is data that is collected for analysis and that grows quite rapidly. User activity logs…

Oracle 11g: New Pivot Table Feature

I was very pleased when I heard about Oracle adding pivot functionality in select statements. Finally — we wouldn’t have to copy the data to a spreadsheet or code a ton of sum(case when col1 = ‘X’ then amount else 0 end) total_X for each column we would want to display. I am basically looking for three things in a pivot-style query: the ability to specify which column will be pivoted as one or more columns in the resulting query, row subtotals, and column subtotals. The first item is the only one that really matters. I can work around the other two, so let’s get started.

Oracle 11g: Unexpected Difference Between count(*) and count(1)

It seems Oracle 11g introduces a difference between count(*) and count(1). The way this happens is just the opposite of what I was thinking would happen. NB: I ran my test using “11.1.0.6 32bits” on Ubuntu Linux 7.04 (Feisty) which is not officially supported1, and which has already lead me to some unexpected behaviors. If this difference with count() is really the 11g way and not buggy behavior related to the Ubuntu install, I’m glad to have found it. Here’s what you can do to observe (or confirm or dispute) this.

Using a Function-Based Index to “Avoid” a Sort

Recently, I was asked to take a look at a query processing part of a client’s application which had started to perform slowly after the application had a maintenance downtime. The idea behind the processing was quite simple.The trick was to add a (redundant) CASE PROCESSED WHEN 0 THEN MOD(SUBS_ID, 7) end expression into ORDER BY clause..In this post I show you how I came to this solution.

Total Recall by DUDE: Extracting Oracle Data After RAID Corruption

Last Friday, a customer paged me to say that they could not open an Oracle 8.0.5 database after the RAID array crashed. It turned out that this database was in NOARCHIVELOG mode and there was not even a cold backup. Furthermore, they didn’t even have a database export. it was definitely the time to recall Kurt Van Meerbeeck and his all-time-hit tool — the DUDE. DUDE stands for Database Unloading by Data Extraction. The client was very impressed by how we were able to recover from a deadly situation like that, and it was only possible thanks to the DUDE and his creator: thanks a lot Kurt.

MERGE Without an INSERT — It’s Not Always Like an UPDATE

Before you proceed with reading this post, I strongly encourage you to read Tom Kyte’s trilogy about write consistency, since I’ll do only a brief introduction to the subject. The way Oracle ensures UPDATE write consistency is through a mechanism called restart. Let’s take a look at an example before we proceed with the main topic of this blog post, Will there be any difference if we substitute the following MERGE for the last UPDATE?

Page 51 of 67« First...102030...4950515253...60...Last »