Thursday, January 24, 2013

RMAN hanging up

Recently I've had to verify or look into our RMAN process potentially hanging up, so I thought I'd share how I've been finding it in our system.

If you look in the table v$rman_backup_job_details, you can see details such as the status, start time, duration, device type, backup type, and even how large the backup job was; but it also shows the current running session(s) as well (the word backup made me initially think it was a history table).  If this doesn't show the information for some reason, you can also look in gv$session by the program column looking for %rman% to find the active sessions.  (Sometimes the module column will have rman as well, but it will change into something that doesn't have rman at certain points so I find the program column much more reliable for my system since it is rman@system and I assume that's the natural setup for all implementations!)

As you can see the common thread running through a lot of posts recently, we have access to the data behind some of our processes and now we need to come up with ways to monitoring this for when RMAN is thrashing against the DB and taking up critical resources that should be available instead.

A blog's first steps

I checked in tonight before writing a new post, hoping that 2 new visitors had hit the site in the last day or two since that would mean I had 50 hits in the past month which is just insane and awesome and incredible all rolled up into one, yet was astounded to see I had 20 hits from Russia yesterday to push me way over the edge and make this month the largest this blog has seen (so far).  I'll keep going, if you keep visiting!

-The Oracle EMT

Thursday, January 17, 2013

v$sesstat - A new tool for the toolbox

While I think I have a handle on some of the techniques Tanel puts into the first of his three steps, I need to gain more understanding about what v$sesstat is and how I can use it the next time I get into a situation where I am out of ideas as to what's going wrong with our systems (and why I found it in the first place, but the problem went away before I could use this too extensively).  This pretty much sums it up:

Only looking into #1 is enough in 90% of problem cases. But for advanced troubleshooting of complex and exotic problems I regularly have to dive into the #2 – V$SESSTAT performance counters (That’s the reason why I wrote Snapper for example).

If you aren't following Tanel, you need to be.

Thursday, January 10, 2013

Recycle bin?

You could've knocked me over with a feather when I found out that an Oracle DB has a recycle bin!  So I'm curious about it obviously, and query the table and lo and behold there are items in there which a. aren't being dropped and b. being created every day as a result of our team's automation.  The good news is that there isn't a whole lot of space being taken up by what's in here, but here again is something we aren't monitoring on so now I'm going to be designing a new alert to let us know what's being dumped in here.

http://connormcdonald.wordpress.com/2012/11/23/why-cant-i-resize-my-datafile/

Thursday, January 3, 2013

duration not active: A Tale of Two Articles

From time to time I snoop around our DB log files to see if anything is going on with the system, and the other day I found this:

Errors in file <filename>:
ORA-21779: duration not active

Now, I've never seen this before so I go to the Oracle authority MetaLink (or My Oracle Support as it's known now) to find two completely different articles which illustrate probably how many people they have working at Oracle more than anything else.

A helpful article:

What Does Error "ORA-21779 Duration Not Active" Mean? [ID 1084474.1]

Goal
What Does Error "ORA-21779 Duration Not Active" Mean?

Solution
The error "ORA-21779 Duration Not Active" is caused by some process trying to clean up an object from memory.

Duration is the amount of time a object is in memory.  If an object in memory is accessed and its duration is inactive state or not found then the ORA-21779 message is returned.

Each memory chunk is associated with a duration. This allows clients to automatically free all memory associated with a duration (at the end of the duration).  The duration determines the heap that is used to allocate the memory.

The allocation duration is set when an object is created with OCIObjectNew(), and the pin duration is set when an object is pinned with OCIObjectPin().  The datatype of the duration value is OCIDuration.

When an object reaches the end of its allocation duration, it is automatically deleted and its memory can be reused.  The pin duration indicates when an object's memory can be reused, and memory is reused when the cache is full.

There are various predefined types of durations supported on memory and context management calls.

OCI_DURATION_CALL. The duration of this operation is that of a callout.

OCI_DURATION_STATEMENT. The duration of this operation is the external row source.

OCI_DURATION_SESSION. The duration of this operation is the user session.

OCI_DURATION_PROCESS. The duration of this is agent process.

See following docs with regards to durations
Oracle Call Interface Programmer's Guide, 10g Release 2 (10.2), Part Number B14250-02
Oracle Database Data Cartridge Developer's Guide,10g Release 2 (10.2), Part Number B14289-02

A not so helpful article:

OERR: ORA-21779 duration not active [ID 49397.1]
Error: ORA-21779
Text: duration not active
---------------------------------------------------------------------------
Cause: User is trying to use a duration that has been terminated.
Action: User should avoid performing such operation.

Seriously, that's all the second article has for content!  And.......ANNNND.....it was last updated 6 months ago.  So it was.....added to?

Annnnyways, the first article does give me some idea of what was going on but not why or what was happening so I'll need to keep my eyes peeled to try and see this in action next time!

Site changes!

Over the holiday break I've had some time to browse a bunch of blogs hosted by Oracle, and I've added about 20 of them at this time to follow on the sidebar!  I'm sure that I'll eventually weed some of them out over the next few months, but I think I'll also do a random deep dive on an article archive so that's at least 20 blog posts you can look forward to.  :}

-The Oracle EMT