Disk Space

Reduce Database Disk Requirements

The ORACLE2 server has run out of disk space. I take responsibility for the problem because I thought the ORACLE2 server had a 160G disk drive when it actually has a 30G disk drive. Some students (student groups) are consume about 2G of disk space and this has caused us to consume all available space on the computer. An additional disk drive that is 160G in size is on order, but has not yet arrived.

All students need to reduce the amount of disk space they are consuming. This document provides directions for helping clear up our space limitation problem. All student groups are expected to complete the requirements outlined in this document.

Delete Backup Copies of Your Database

Because space is limited, you will need to delete any backup copies of your database that are stored on the ORACLE2 server. You may wish to create a cold backup to another computer such as a home computer. We also maintain a backup of the server's disk drive to another backup device (tape) so your database files can be restored from that backup if necessary. Maintaining your own physical cold backup of the database on a personal computer will facilitate you restoring the database quickly if necessary, but this is not essential.

ALTER TABLESPACES

Another way to save space is to reduce the size of the existing tablespaces in your database. Initially I had you create tablespaces with meaningful sizes for small real-world databases, but most of the size allocated to these tablespaces will never be used during the term.

Modify the Size of the DATA, INDEXES, and USERS Tablespaces

The objective here is to save space by reducing the size of the DATA01, INDEX01, and USERS tablespaces in your database, and by dropping the READONLY01 tablespace that you created in Lab3. You should resize these tablespaces according to the table shown below. These sizes should be sufficient for the remaining lab assignments in the course. If these sizes generate errors such as: ORA-03297: file contains used data beyond requested RESIZE value, then try a successively large value in 500K increments.

Tablespace
New Size
USERS
2M
DATA01
512K
INDEX01
512K
READONLY01
DROP this tablespace from the database.

Example commands:

ALTER DATABASE
DATAFILE '/a01/student/user350/oradata/user350users.dbf'
RESIZE 2M;

ALTER DATABASE
DATAFILE '/a01/student/user350/oradata/user350data01.dbf'
RESIZE 512K;

ALTER DATABASE
DATAFILE '/a01/student/user350/oradata/user350index01.dbf'
RESIZE 512K;

DROP TABLESPACE READONLY01 INCLUDING CONTENTS AND DATAFILES;


Modify the Size of the SYSTEM, SYSAUX and TEMP Tablespaces

Modifying these two tablespaces will require some trial and error.

Attempt to reduce the SYSTEM tablespace to 225M from the current size. If this size is too small, use a slightly larger size, e.g., 230M or 235M until you reach a data file size that the SYSTEM tablespace will fit into.

ALTER DATABASE
DATAFILE '/a01/student/user350/oradata/user350system01.dbf'
RESIZE 225M;

The SYSAUX tablespace can be reduced in size significantly. In this example, the data filefor the tablespace was reduced to 75M in size.

ALTER DATABASE
DATAFILE '/a01/student/user350/oradata/user350sysaux01.dbf'
RESIZE 75M;

Here the TEMP tablespace's tempfile is reduced to 6M in size. Again, the size will depend on how much of the tablespace has been used recently for segment storage. Notice the use of the TEMPFILE clause instead of a DATAFILE clause.

ALTER DATABASE
TEMPFILE '/a01/student/user350/oradata/user350temp01.dbf'
RESIZE 6M;


Create a New (Smaller) Undo Tablespace (2M in Size)

The objective here is to save space that will not be used by your database in the UNDO tablespace. You can accomplish this by creating a new, smaller UNDO tablespace (named UNDO02), switching to the use of the new UNDO02 tablespace, and then dropping the old UNDO01 tablespace. The new UNDO02 tablespace will be 2M in size.

Step 1. Create a new UNDO tablespace named UNDO02. Ensure you use your $HOME/oradata location.

CREATE UNDO TABLESPACE undo02
DATAFILE '/a01/student/user350/oradata/user350undo02.dbf'
SIZE 2M REUSE AUTOEXTEND ON;

Step 2. With the database open, switch to the new UNDO02 tablespace.

ALTER SYSTEM SET undo_tablespace = UNDO02;

Step 3. DROP the old UNDO tablespace named UNDO01.

DROP TABLESPACE undo01
INCLUDING CONTENTS AND DATAFILES;

Shutdown your database and alter the init.ora file to reflect the name of the new UNDO tablespace, then startup the database again if you have additional tasks to perform.


RESIZE REDO LOG FILES

In order to resize the Redo log files, you need to check the status of the redo log groups to determine which ones are current, active, and inactive. Only inactive groups can be dropped.

Your goal is to reduce the size of the Redo Log Files to 4M each. This is the minimum allowable size.

Step 1. If you have two groups numbered 1 and 2, then add a third group as shown in this command. Three log groups are required in order to accomplish resizing easily.

ALTER DATABASE ADD LOGFILE GROUP 3
'$HOME/oradata/user350redo03a.log' SIZE 4M;

Step 2. Check the status of the log groups again. Our goal here is to drop and recreate Group 1, however, it must be "inactive" first.

SELECT group#, status from v$log;

If it is not inactive, then switch logfiles and recheck the status as shown here.

ALTER SYSTEM SWITCH LOGFILE;

SELECT group#, status from v$log;


When Group 1 is inactive, drop the logfile group and recreate the group with its single logfile member as shown here.

ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE ADD LOGFILE GROUP 1
'$HOME/oradata/user350redo01a.log' SIZE 4M;

Step 3. Now our goal is to drop and recreate Group 2. Switch logfiles and recheck the status of the log files. Continue switching and checking until Group 2 is inactive.

ALTER SYSTEM SWITCH LOGFILE;

SELECT group#, status from v$log;

When Group 2 is inactive, drop the logfile group and recreate the group with its single logfile member as shown here.

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE ADD LOGFILE GROUP 2
'$HOME/oradata/user350redo02a.log' SIZE 4M REUSE;

If you already had three Redo Log Groups, then you now need to drop and recreate Group 3.

Step 4. Verify within your database the Redo log files you have. This SELECT command will list all of the redo log files by filename.

SELECT member, status FROM v$logfile;

Step 5. At the operating system level that you can now delete any redo log files that are not listed by the above SELECT statement.

Reduce Potential Memory Paging to Disk

Change the db_recovery_file_dest_size parameter in your init.ora file to the following value, then shutdown your database. On restart the potential disk space allocated to recovery will be reduced by 75% from that previously allocated.

db_recovery_file_dest_size=536870912

End of Notes.