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.