?
Solved

How should I redesign database to handle heavy transactions and reporting on Oracle Database

Posted on 2008-02-05
3
Medium Priority
?
700 Views
Last Modified: 2013-12-27
I tried to connect /as sysdba and I got the following error:

ERROR:
ORA-09817: Write to audit file failed.
SVR4 Error: 28: No space left on device
ORA-09945: Unable to initialize the audit trail file
SVR4 Error: 28: No space left on device


I am running Oracle 10g on Solaris 9 OS. I checked the partition where the Oracle binaries are stored and it is at 100% capacity. I don't know where to begin to fix this problem. I asked the SA to add more space to that partition and I was going to hopefully move some database files around on other partitions but I think whatever filled the /uol partition will fill any other partition I place the database files on. I know there is a lot of activity on one database. Here is what the files and their sizes look like:

-rw-r--r--   1 oracle   dba      7061504 Feb  2 22:34 control01.ctl
-rw-r--r--   1 oracle   dba      7061504 Feb  2 22:34 control02.ctl
-rw-r--r--   1 oracle   dba      7061504 Feb  2 22:34 control03.ctl
-rw----r--   1 oracle   dba      26222592 Feb  2 01:30 indx01.dbf
-rw-r--r--   1 oracle   dba      52429312 Feb  2 01:00 redo01.log
-rw-r--r--   1 oracle   dba      52429312 Feb  2 22:31 redo02.log
-rw-r--r--   1 oracle   dba      52429312 Feb  1 12:00 redo03.log
-rw-r--r--   1 oracle   dba      178266112 Feb  2 22:31 sysaux01.dbf
-rw-r--r--   1 oracle   dba      398467072 Feb  2 22:31 system01.dbf
-rw-r--r--   1 oracle   dba      20979712 Feb  2 06:02 temp01.dbf
-rw----r--   1 oracle   dba      10493952 Feb  2 01:30 tools01.dbf
-rw-r--r--   1 oracle   dba      209723392 Feb  2 22:31 undotbs01.dbf
-rw-r--r--   1 oracle   dba      5251072 Feb  2 01:30 users01.dbf

Any suggestions on how to better manage this space and the database would be much appreciated.
I spoke with the person performing work on this database and she is doing the following:

CREATE INDEX
SELECT ... ORDER BY
SELECT DISTINCT ...
SELECT ... GROUP BY
SELECT . . . UNION
SELECT ... INTERSECT
SELECT ... MINUS

This also includes importing millions of records daily and performing the above queries. The table These millions of records are imported into is truncated prior to the import.
0
Comment
Question by:sikyala
3 Comments
 
LVL 2

Assisted Solution

by:SmilingPixie
SmilingPixie earned 800 total points
ID: 20823777
The errors that you are getting are related to the audit trails that have been set up on your system.  Can you tell me what the DB_AUDIT_TRAIL parameter is set to?  It looks like it might be pointing to  DB which would create all of your audit trail records into the system tablespace.  One suggestion, if possible is to change the audit trail to write your data to an external directory that is separate (but protected) from the rest of your database files.  
0
 
LVL 13

Accepted Solution

by:
sonicefu earned 1200 total points
ID: 20824478
The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size.


http://www.oracle-base.com/articles/8i/Auditing.php
http://www.oracle-base.com/articles/10g/DatabaseSecurityEnhancements10g.php#audit_trail_contents

-----------------------------------------------------------

AUDIT_TRAIL = { db | os | none | true | false | db_extended }

http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10755/initparams015.htm#i1125027
0
 

Author Closing Comment

by:sikyala
ID: 31428129
When I emptied the audit files from the audit directory under /u01 where the audit logs were being written to I was able to regain enough space to log into the database.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month6 days, 20 hours left to enroll

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question