Re: How to determine if a database is in use?
Date: Thu, 14 Apr 2016 15:52:15 +0800
Message-ID: <CAPgdRPR2Y3gKcWvKm_85KnN5nSQeTW-0bw37Q0kyJMu5kqwWag_at_mail.gmail.com>
For anyone who's interested, here is the query I put together which uses the AUD$. Does some rollups on the week, looks back 12 months and only shows the most recent 99 lines
SELECT *
FROM ( SELECT (SELECT v.host_name
FROM v$instance v)
Host_Name,
(SELECT v.instance_name
FROM v$instance v)
DB_Name,
x_Weeks_ago,
COUNT (OS_USERNAME) occurances,
OS_USERNAME,
USERHOST,
Weekly_Rollup_Date,
ACTION_NAME,
USERNAME
FROM (SELECT ROUND (ROUND (SYSDATE - TRUNC (NEXT_DAY
(A.TIMESTAMP, 'Sun') - 7), 0) / 7, 0) x_Weeks_ago,
A.OS_USERNAME,
A.USERHOST,
TRUNC (NEXT_DAY (A.TIMESTAMP, 'Sun') - 7)
Weekly_Rollup_Date,
CASE A.ACTION_NAME WHEN 'LOGOFF' THEN
'LOGON/LOGOFF' WHEN 'LOGON' THEN 'LOGON/LOGOFF' ELSE a.action_name END
ACTION_NAME,
A.USERNAME
FROM DBA_AUDIT_TRAIL A
WHERE TRUNC (A.EXTENDED_TIMESTAMP) > TRUNC (SYSDATE)
- 365
AND action_name NOT IN ('LOGOFF BY CLEANUP',
'LOGOFF BY CLEANUP')
AND username NOT IN ('DBSNMP',
'SYS',
'SYSTEM',
'OEMGC',
'SYSMAN')) sexy_internal
GROUP BY x_Weeks_ago,
OS_USERNAME,
USERHOST,
Weekly_Rollup_Date,
ACTION_NAME,
USERNAME
ORDER BY Weekly_Rollup_Date DESC) sexier_external
WHERE ROWNUM < 100;
On 14 April 2016 at 05:46, Oracle DBA <justanotheroracledba_at_gmail.com> wrote:
> Thanks everyone,
> I won't do individual replies but some good info.,
>
> Mladen, I had thought of your idea and I like it very much, but upper
> management will take that as a cowboy tactic and in the event of actual
> downtime if its still being used then they will get stoppy, its already
> hard enough to promote IT as having good service, but that will give the
> end users more ammo against IT. But yes switch off and wait for the screams.
>
> Iggy is also correct there are a few edge cases that for legal reasons
> (contracts) some are required for 7 years after project close, but I'm sure
> there are more and it will be a slog to once identify a unused database to
> then locate the correct people who know about the contract. Can always
> backup and restore if they need it down the track.
>
> Ian, DBA_AUDIT_TRAIL is great, but does miss 40% of our estate. Work is
> now in progress to turn on DB Auditing on those remaining also our 9i
> databases appear to be empty AUD$ table
>
> Niall already using logons_Current but some only have a single App Server
> user connected so while its a good indicator of how many connections are
> connected, it does miss a bit, but combined with the redo generation does
> give us some candidates.
>
> Raza, a lot of the database accounts cannot be mapped back to their
> Windows accounts so thats a bit tricky, I suppose I could start digging
> into the application tables and hope to find internal app logins and email
> addresses and then start emailing the users for clarification.
> Documentation is being kept for new install, but our Company has gone
> through a number of acquisitions and has also acquired a number of other
> companies over the last 10 years and through redundancies etc MOST of that
> local knowledge is now gone. Documentation is wonderful given you have
> enough time to do it.
>
>
>
>
>
>
> On 14 April 2016 at 05:00, Iggy Fernandez <iggy_fernandez_at_hotmail.com>
> wrote:
>
>> Some companies (e.g. Big Pharma) keep some databases around for
>> compliance reasons.
>>
>> Iggy
>>
>> ------------------------------
>> From: mark.powell2_at_hpe.com
>> To: oracle-l_at_freelists.org
>> Subject: RE: How to determine if a database is in use?
>> Date: Wed, 13 Apr 2016 19:12:28 +0000
>>
>>
>> I think it might be wiser to leave the database in restricted mode for a
>> while before removing it permanently. Some databases are only kept around
>> for historic use and as such may only be accessed infrequently. I would
>> want to get through a month-end / quarter-end period as part of my removal
>> process. It depends on how well you were able to monitor the database to
>> get a feel for usage.
>>
>>
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Mladen Gogala
>> *Sent:* Wednesday, April 13, 2016 1:52 PM
>> *To:* oracle-l_at_freelists.org
>> *Subject:* Re: How to determine if a database is in use?
>>
>>
>>
>> On 04/12/2016 08:33 PM, Oracle DBA wrote:
>>
>> Question – How to determine if a database is in use.
>>
>>
>>
>>
>> Well, here is a simple way:
>>
>> 1. Ask around.
>> 2. Take a full offline backup. If someone objects, the database is in
>> use.
>> 3. If nobody complains by time the backup is finished, start it up
>> using the following command: "startup mount restrict" and when the database
>> comes up, execute "drop database" command. That will provide immediate
>> savings in space and machine resources.
>> 4. If nobody complains, the database is not used.
>> 5. If somebody does complain, you have a full offline backup and can
>> bring it back.
>>
>>
>>
>> --
>>
>> Mladen Gogala
>>
>> Oracle DBA
>>
>> http://mgogala.freehostia.com
>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 14 2016 - 09:52:15 CEST