What about DBA_AUDIT_TRAIL, or whoever you write your audits? Oracle audits
quite a bit by default.
Ian A MacGregor
On Apr 12, 2016, at 5:33 PM, Oracle DBA
<justanotheroracledba@xxxxxxxxx<mailto:justanotheroracledba@xxxxxxxxx>> wrote:
Question – How to determine if a database is in use.
Now that may appear to be a newbie stupid question but let me elaborate. We are
a global company with 500+ offices along with about 6 largish datacentres
around the globe. We have over 1000 instances running in various capacities on
around 260 servers (some physical, some Virtual) with a 60% Windows, 25% Sun
and the remaining Linux. Some of our larger ones are generating 70gb a day in
redo, while others barely kick over 2mb a day. They range in versions from
(embarrassed to say but its starts with a 7) to 12.1.0.2 EE. We do run a Global
installation of Cloud Control 12.1.0.4 and pretty much all databases are in
there.
With the ever present threat of audits from that magical team called LMS we are
trying to identify those instances that are not being used, or haven’t been
used in 6+ months with the goal to archive and then promptly decommission the
database and uninstall the binaries. New databases are coming online weekly,
but it appears that the business has a difficulty in telling anyone that they
are not used anymore. They don’t realise or care, that for every instance,
there is a cost, whether it be CPU, memory, storage, tapes, my time and the big
costs of licences.
So, given that I have 1000+ instances, how would you identify which of those
haven’t been used for 6+ months?
My initial thoughts
1) I’ve used Cloud Control repository to mine the wonderful data and got
a list of databases ordered by redo generation, but just because a database
generates 2mb a day does not mean that its not being used. So while it may
highlight low use databases, its by no mean perfect.
2) Parse the classic listener.log file using SED to strip the noise and
then grep on the SID and see what users are connecting, and what dates they
connected. This sounds like what I’m after thinking there might be a better
way like creating an external table on the 11g diag listener.xml files and
using some magic query ? Anyone done that and like to share?
3) There is an option going forward to create a few tables and create a
custom login trigger to record logins, but obviously this does require some
work, a few RFC’s and 6+ months to get the data.
4) Then again, there is probably some out of the box auditing that can be
turned on that will have this, but again a few (lots) of RFC and then work out
how to query this audit data. Actually does the seeded DBSNMP have read access
into the audit trails? As I can generate a global Tnsnames.ora file that lists
all our servers.
5) Is there anything in the Cloud Control repo that has this information?
My perfect solution would be use the info in Cloud Control to automate/script
this and was wondering if anyone had any insights.
