RMAN cannot restore from backupset copied to disk from tape

Attempting to work around an issue with our backup system, I copied the backup pieces from a known-good backup from tape to disk. Then I attempted to catalog those pieces for a recovery attempt. Cataloging those pieces failed.

RMAN> catalog backuppiece '/u01/app/oracle/admin/scripts/restore/filetest/DB_3rn2tp0j_1_1';

using target database control file instead of recovery catalog
ORA-19870: error while restoring backup piece /u01/app/oracle/admin/scripts/restore/filetest/DB_3rn2tp0j_1_1
ORA-19587: error occurred reading 0 bytes at block number 1
ORA-27067: size of I/O buffer is invalid
Additional information: 2

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 02/17/2012 09:37:20
RMAN-06209: List of failed objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    /u01/app/oracle/admin/scripts/restore/filetest/DB_3rn2tp0j_1_1

Eventually found Oracle Support Doc ID 1111603.1 on the subject. Essentially, no, you can't catalog a backup piece that's been restored from tape to disk because the header information will be different than the file at rest on tape.

A bad use of find in a nightly script

I was going through cleaning up files on one of the database servers. I happened to find this script.

> cat /home/oracle/scripts/rm_old_alogstrc.shl
cd /u03/oradata/SID/archivelog
find . -mtime +2 -exec rm {} \; 
cd /u01/app/oracle/admin/SID/adump
find . -mtime +7 -exec rm {} \;
cd /u01/app/oracle/admin/SID/bdump
find . -mtime +7 -exec rm {} \;
cd /u01/app/oracle/admin/SID/cdump
find . -mtime +7 -exec rm {} \;
cd /u01/app/oracle/admin/SID/udump
find . -mtime +7 -exec rm {} \;
cd /home/oracle/datapump/SID
find . -mtime +7 -exec rm {} \;
cd /home/oracle/exports/SID
find . -mtime +7 -exec rm {} \;

For the benefit of those not cringing as you read that. Two major things I see wrong with it. The first is that someone had entrusted the OS to clean up old archivelog files older than 2 days. While it's not so common with all the safeguards we put in place for backups, but I can still imagine scenarios where archivelogs don't get backed up off the server in 2 days. This script runs and poof there goes recoverability.

And the second which makes me cringe is the two step file removal. Specifically cd to some directory and then rm all files in that directory. Just imagine what would happen if that script didn't change directory. Next you know whatever the working directory was is effectively wiped out. It was likely the home directory.

The much better way is to combine it into one command, that way there's no accidental deletions.

find /home/oracle/exports/SID -mtime +7 -exec rm {} \;

In a way I wish I didn't see this. Now I wonder what other gems are out there on this server that someone left me.

Manually remove oracle server software on Microsoft Windows Platforms

What happens if Oracle deinstall.bat doesn't cleanly remove all the windows components? Try using doc id 1069034.1: 11.2: How to Manually Remove Oracle Server Software on Microsoft Windows Platforms [ID 1069034.1]

Nice try

Tired of looking this up... so I'm posting it. I don't use the unix nice command very often, but when I do, the man page seems a little confusing. Here's the format I typically use the command:

nice -n +19

That runs the command at the lowest possible priority, helpful if you're going to do some big file op and don't want to cause anyone else any grief.

What service has oci.dll open?

Attempting to use deployment procedure's to install patch bundles on windows hosts, sometimes you run into issues with services keeping dll's (especially oci.dll) open.

Here's a trick to determining what service is keeping a specific dll open.

From a command prompt:

tasklist /m > tasks.txt 

notepad tasks.txt 

The look for the specified dll in tasks.txt and find the process. From there it should be somewhat obvious which service to shut down in order to get the patch to continue.

ORA-01406: fetched column value was truncated

I just got a dump file of our data from a vendor that is hosting an application for us and I was making it available to my developers and since we will be getting files on a somewhat frequent basis, initially I created an external table to read the data.

I had to get over the first hurdle of version incompatibility of the datapump file, which wasn't too bad, but after I cleared up those issues (lots of references on google, this chart helped the most) I received the following error in Toad:

ORA-01406: fetched column value was truncated

with further details

fetched column value was truncated

Cause: In a host language program, a FETCH operation was forced to truncate a
character string. The program buffer area for this column was not large enough to
contain the entire string. The cursor return code from the fetch was +3. 

Action: Increase the column buffer area to hold the largest column value or perform
other appropriate processing.

Google wasn't as helpful with this (hence the post here), but essentially this is a client side setting that needs to be adjusted. In version 10.6.1.3 of Toad there is one setting that needs to be changed. Search for "OCI Array Buffer size" or find it under "Oracle - General". You need to change it to be large enough to fetch the largest column in the query. In my case I had a column of size 4000, so setting this to 4000 returned results.

Fun with Logon Triggers

Sometimes a process happens too fast to be traced effectively, but it's happening slowly enough to make your users angry.

Here's an example of a logon trigger setup to make a trace happen, we're using it to diagnose an issue with slow response from an ODBC connection, using user account ADMIN_JOSH:

DROP TRIGGER SYSTEM.JOSH_LOGINTRIGGER;
 
CREATE OR REPLACE TRIGGER SYSTEM.Josh_LoginTrigger AFTER LOGON ON ADMIN_JOSH.SCHEMA
BEGIN
   execute immediate 'ALTER SESSION SET tracefile_identifier = MYTRACEFILE';
   execute immediate 'alter session set sql_trace=true';
END;
/

Note if you get the following error, the owner of the trigger needs the alter session priv (in this case system).

ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 2