RE: sql to check when a last expdp backup was done (In general, if you want to definitely document something, then instrument your code.)

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 23 Aug 2013 10:35:54 -0400
Message-ID: <029201cea00e$139c74d0$3ad55e70$_at_rsiz.com>



Praveen:

You have tangentially brought up one of my favorite topics:

In general, if you want to definitely document something, then instrument your code.

The most basic instrumentation for backups is: I'm about to start a backup :some_unique_id of :what at :datetime. <backup runs>
Backup :some_unique_id of :what [Completed|Failed] at :datetime.

Probably you want to store that in a relational table somewhere you can count on.

For script driven events the instrumentation can be as simple as adding prefix and postfix commands to the script or as job steps of a scheduled job.

If, instead, you try to interpret some values from a running instance or in oracle system tables, your tool may very well break upon releases and patches.

Values that are only instance persistent are particularly perishable. For finding the most recent backup or export the potential for irony on an unexpected instance crash seems high.

I personally have long advocated maintaining a "Warehouse for the DBA" as part of no operationally production database (but treated as a production class environment) for the long haul.

Trends of time and cost to complete, size, and all variety of metrics of both short and long term interest can thus be accumulated on machinery appropriate in performance and cost to the load but isolated from all variety of upgrade and maintenance rhythms and outages. In particular the DBA's warehouse should be highly available for collection (but this may be distributed at time of collection with a reliable pipe and/or messaging system route to the eventual repository.

WHAT goes into each individual corporate "Warehouse for the DBA" is of course open ended. Keeping track of recovery resources (even if only a secure resting place for what RMAN collects, but certainly also for recovery resources that do not appear in RMAN such as location and vintages of exported and unloaded data) is most likely a good thing to keep track of.

Question: Is there need for development of a standard for the minimum requirements to be kept in the DBA Warehouse?

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Praveen Ramanan
Sent: Wednesday, August 21, 2013 2:44 PM To: oracle-l_at_freelists.org
Subject: sql to check when a last expdp backup was done

This is my first post in Oracle-l Freelists group. I have been following this list for quite some time and have learnt a lot about how dba's handle their day to day tasks. Thank You !!
I have a simple dashboard which lists all the backups taken (for the various instances we have), their type and when a successful backup has been completed.
This is reasonably straighforward to get rman backup details. However for few instances i also run a full expdp backup and i would like to check when a last expdp was taken.

This is what i currently have
select opname,target_desc,start_time,elapsed_seconds,message from v$session_longops where OPNAME='NAME OF JOB' order by start_time desc (this expdp backup is scheduled through db control)

While this gives me the right results most times, sometimes it does not. is there another way to get this information?

The database version is Oracle 11gR2 .

Thanks.

--

Teamwork is the fuel that allows common people to produce uncommon results

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 23 2013 - 16:35:54 CEST

Original text of this message