Is anyone actually using this database? - How to tell whether or not you can delete an old database

When you log into a database server that has been running for several years, you will often find lots of files from databases that may not have been used for some time.

The people who created them probably left the company long ago and there is little documentation on the databases. Nobody is certain whether or not they are still being used, so they just sit around using up disk space.  If the databases are started up, then they’re also using memory, even though they may not be doing anything productive.

Rather than prolonging this situation, it’s a good idea to remove these databases - once you’ve confirmed that no-one uses them anymore.  Unfortunately, within a large business it may not be easy finding out who within the company is using which databases, if documentation has not been kept up to date.

As a starting point you can look in the following places:
(i) Database alert log
If the database is currently shut down, then what date was it shut down? If this was a long-time ago (i.e. more than 1 year), then the chances are that the database is no longer used, or is so out of date with current business data that it would need to be refreshed/updated before it could be of any use. 

Note: Just because things such as configuration changes (i.e. extending datafiles) are shown in the alert log, it doesn’t mean to say that it’s actually in use.  A DBA can carry out maintenance tasks on a database, whether or not there are any business users carrying out work.

Similarly, you may find that a backup tool such as RMAN regularly connects to the database.  This also doesn’t mean to say that the database is being used by the business - just that it’s being backed up.

(ii) Listener log
Search in the listener log for the last entries for that database. Even if the database is shutdown, this will let you know the last time that anyone was using it.  Be careful that you don’t think that someone is using the database just because there is an automated application process or reporting tool that connects to the database regularly.  If individual users are not connecting to the database, then it may not be in use anymore. 

Note: Double-check with any reports or application support teams as well, because the database may just be a repository, in which case individual user connections could be rare.

(iii) Datafile timestamps
If the database is shutdown, What is the last modified date shown in the filesystem for the datafiles?  I’ve come across situations where you can’t find any entries for the SID in the listener log and the alert log for the database has been removed, so this is a good way to find out when the database was last open.

Note: This is assuming that someone hasn’t just copied the files from another location, without preserving the original file timestamps.
(iv) Run AWR/ASH or Statspack reports
These will let you know if anyone has been using the database within the last week or longer - depending on the retention period configured for the database.  (If performance snapshots are not configured, then you may also find useful information on long-running transactions in V$SESSION_LONGOPS).

Note: Just because a database hasn’t been used in the last week, doesn’t mean to say it’s not required anymore.  It could be used for monthly, weekly or annual reporting, so you may not see regular activity.
 (v) Database Auditing
If database auditing has been enabled, it will give you an idea who has been accessing the database. (e.g. query views such as DBA_AUDIT_SESSION and DBA_AUDIT_TRAIL).
(vi) Standby databases
It’s worth running the query SELECT DATABASE_ROLE FROM V$DATABASE; If this returns “PHYSICAL STANDBY” or “LOGICAL STANDBY”, then it could mean that someone has deleted a primary database in the past, but neglected to remove the associated standby database. 

Before deleting any databases, it would be advisable to email anyone that may have an interest in the database and then ensure that a copy of the database files and configuration information is archived to tape or other storage for a pre-defined period of time.  Ensure that you have approval from all interested parties before carrying out any work like this, which could have an impact on the business.

Leave a Reply

You must be logged in to post a comment.

Buy Lortab Buy Diflucan Buy Adderall Buy Prozac Generic Viagra Glucophage Phentermine Buy Lexapro Buy Seroquel Ephedrine Buy Lorazepam Buy Darvocet Buy Tramadol Didrex Buy Line Xanax Fioricet Buy Zocor Xanax Online Zyrtec Buy Valium Online Buy Clonazepam Buy Flexeril Ativan Buy Propecia Cheap Phentermine Buy Darvocet Buy Valium Buy Lipitor Lipitor Buy Vicodin Online Buy Biaxin Buy Xanax Buy Vicodin Buy Codeine Buy Adipex Paxil Effexor Buy Ambien Buy Cialis Xanax Online Phentermine Online Buy Clonazepam Ephedrine Hydrocodone Buy Line Xanax Buy Nexium Zyban Ambien Zyprexa Norco Lipitor Buy Prozac Ultracet Buy Tenuate Meridia Buy Zyban Oxycontin Xanax Paxil Clonazepam Acyclovir Seroquel Butalbital Levitra Buy Acyclovir Buy Norco Buy Biaxin Buy Hydrocodone Online Buy Cipro Buy Generic Viagra Xanax Buy Percocet Buy Ambien Buy Hydrocodone Butalbital Buy Bontril Zocor Buy Xanax Online Phentermine Online Buy Ultram Buy Lipitor