A few basic notes on Oracle Database Administration.

Thursday, July 31, 2008

Make it go away! (rman delete)

So much documentation tells you how to set up things for Oracle. It is harder to find information on how to get rid of it once you have it.

I am doing perfectly lovely incremental compressed backupsets of my database. Then one day, playing with EM, I in experimented with the 'Oracle recommended backup stategy'. I may have missed something in my set up, but I ended up with an un needed image copy backup of my database. Help! This thing is devouring my flashback area. So what is the proper way to remove it.

First step, of course, is to kill the Oracle job, so it doesn't do that again!

Then in rman:
RMAN> list copy;

gives me a nice long list - one line of which was:
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name

30428 4 A 29-JUL-08 224478 29-JUL-08 /bkup/flashbk/datafile/o1_mf_users_44y8x123_.dbf

I double check on the file system:
$> ls o1_mf_users_48y8x123_.dbf
o1_mf_users_44y8x123_.dbf
Yes, the copy is there.

To delete it in RMAN I enter
RMAN> delete datafilecopy 30428;

and get:


List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name

30428 4 A 29-JUL-08 224478 29-JUL-08 /bkup/flashbk/datafile/o1_mf_users_44y8x123_.dbf


Do you really want to delete the above objects (enter YES or NO)? y
deleted datafile copy
datafile copy filename=/bkup/flashbk//datafile/o1_mf_users_44y8x123_.dbf recid=30428 stamp=1334123
Deleted 1 objects

I check in the file system and this time my ls returns
ls: 0653-341 The file
o1_mf_users_44y8x123_.dbf does not exist.

Good bye, nusance.

Thursday, July 10, 2008

Something is VERY busy

I have been suspecting that something is causing excess connects/disconnects from our database. To see the number of connects I went in search of the listener.log. By default it is in $ORACLE_HOME/network/log. There was a listener.ora there , but according to it, there hasn't been ANY connections to our database in months!

This is a solaris box. I read that there is a 2G file limit on Solaris. (Maybe that means a limit on log files. Obviously, my data files are much larger than that.).

I issued the lsnrctl command:

lsnrctl >
set log_file listener2.log


Quickly it resumed recording connections, now into listener2.log. It recorded 2 of them EVERY second!!! OK. my suspicion was right. Now to track down exactly WHAT is connecting and disconnecting so much.

Sunday, July 6, 2008

I'm beginning to distrust Oracle

I am learning that I have been trusting Oracle too much. I have figured that Oracle has top rate developers, so when they offer a facility like ASMM or GATHER_STATS_JOB. I assume that it will be done well.

Now I am learning that ASMM has been allocating our shared pool too large, and has been adjusting sizes not once an hour, but once a minute.

I am not happy with the statistics that the 'wonderful' automatic GATHER_STATS_JOB has been collecting. Also the job has been causing contention with some of our production jobs.

The one that seems really strange to me is Log_buffer. I allocated 28M to log_buffer. According to Oracle, LGWR should be writing, at a maximum, when log_buffer is 1M full. Instead, I see that it is writing 2M at a time. I am going to have to figure that one out.

I expect that when Oracle offers to automate these processes, that this is their own product, so the calculations will be well done. I expected, but I am wrong.