Solaris

This is only one database instance (probably called "SMART"). What you see here are are just different filesystems, holding tablespaces, redo-logs and controlfiles.

A little explanation about what an Oracle database comprises. Sorry in advance if some parts are only vague. I am a Sysadmin, not a DBA, and have only a passing knowledge of databases:

An Oracle database (more correctly: database instance) first has space to store data and index them. This is the DB equivalent of a filesystem. It is called "tablespaces" and from an OS POV it is one or several file(s). Usually one creates - one or more - filesystem(s) for these files because if I/O issues arise one wants to be able to balance the I/O-operations across the involved hardware (disks, adapters, ...).

Where (in the filesystem) all these tablespaces are to be found, how big they are and several other information - name of the instance, used codepage, .... - is stored in a set of "controlfiles". In fact these files are created first when the instance is initially created. These control files are relatively small (just a few MB), but are vital to the operation of the database. Without a fitting set of control files backups of the tablespaces are unusable.

This all is completed by a third class of files: redo-logs. As the database is under load, data get stored in it. Every such transaction is written to the redo log as it happens. Once such a redo-log is full it is moved to a certain location and called "archive-log" and a new redo-log is started. It is possible to have several redo-logs and use them round-robin (seems to be the case here), but the principle is always the same. With these logs it is possible to either reverse already done transactions (="roll back") or, using a previous version of the tablespaces and reapplying the transactions to duplicate them (="roll forward"). The redo-log in principle consists of 4 informations for every transaction:

1) What was the data before the operation;
2) What was the data after the operation;
3) a timestamp;
4) a transaction-ID so that the succession of the transactions is distinct.

OK, so far a (very incomplete) introduction to Oracle workings.

What you can do to free space:

1) check the tablespaces. It might be that they are not full at all. It is possible to resize them, just like filesystems, thereby freeing space. By moving them around you can maybe empty one of the filesystems in use so that you can scratch it. Ask a DBA about how you do that inside Oracle, i just know this is possible but not how it is done.

2) check the archive logs. Archive-logs are not necessary for the running database. Backup them and remove them afterwards, freeing space. If this is not a productive DB instance you might not need them altogether. It is possible to switch off the loging completely, which is done typically in test instances and for initial loads of new database instances, so that archive-logs and redologs are not created at all.

I hope this helps.

bakunin

bakunin thanks for your response!

At the moment I dont care for anything inside this database. Dropping it will automatically free all the space used?

If you don't care for the database then just scratch the filesystems it takes. This way you create space for your new application, no?

Shutdown th database first, fastest way (if you delete it anyways, afterwards) is with

Code:

lsnrctl stop # stop all listeners, then, "connect / as sysdba" and
shutdown abort;

I hope this helps

bakunin

I'll give some further details for the shutdown, as it may be important (there are some posts on this site that demonstrate what happens when you delete a file at os level with processes still having it open).

1) Check if a database is running by looking if there are database processes. The most important process for a Oracle-database is pmon, and looking at the process owner you find out which user you should use to perform the actions below.

Code:

ps -eaf |grep pmon

If there is no pmon process the database is already shut down. If there is more than one you have to do the steps below for each of them. (select * from v$database does NOT show how many databases are installed)


2) Identify the system-ID
There should be a file /etc/oratab (on solaris maybe /var/opt/oracle/oratab). This file tells you how many databases are installed on your system. The structure of this file is:

Code:

<ORACLE_SID>:<ORACLE_HOME>:<AutomaticStart>

<ORACLE_SID> is the "databasename" and <ORACLE_HOME> is the place where the software is installed. There should be a line in this file for each database the server houses.


3) Set up the environment
Switch user to the software owner and set the environment variables ORACLE_HOME and ORACLE_SID (with the values found in /etc/oratab)

Code:

su - oracle
ORACLE_HOME=/path/shown/in/etc/oratab
ORACLE_SID=most_likely_smart

4) Shut down the database:

Code:

lsnrctl stop
sqlplus /nolog
connect / as sysdba;
shutdown abort;
exit

Bakunin thanks for your explanation! You have no idea how it helped me!!

Now... I have another question. The ex-server admin (that I hope he is in a special place in hell for not documenting ANYTHING!) has DB control files in ora1, ora2 and ora3 and redo files in their respective redo pools. What I would like to do is to destroy ora4-ora7 and create just 1 zfs pool from all to them. I was checking zfs commands and in order to create a new pool I need to know which disks will be used to create it. Problem is I dont know which disks form ora4-7 pools. How can I find that out? How can I find out which disks are available after destroying a zfs pools?

Thanks in advance

Code:

 
app/oradata_smart_ora1   1.09G  11.9G  1.09G  /oradata/SMART/ora1
app/oradata_smart_ora2   7.07G  10.9G  7.07G  /oradata/SMART/ora2
app/oradata_smart_ora3   19.8G  16.2G  19.8G  /oradata/SMART/ora3
app/oradata_smart_ora4    136G  30.1G   136G  /oradata/SMART/ora4
app/oradata_smart_ora5   11.8G  43.2G  11.8G  /oradata/SMART/ora5
app/oradata_smart_ora6   47.0G  28.0G  47.0G  /oradata/SMART/ora6
app/oradata_smart_ora7    122G  24.9G   122G  /oradata/SMART/ora7
app/oradata_smart_redo1   601M  2.41G   601M  /oradata/SMART/redo1
app/oradata_smart_redo2   601M  2.41G   601M  /oradata/SMART/redo2
app/oradata_smart_redo3   601M  2.41G   601M  /oradata/SMART/redo3