2 Replies Latest reply: Jul 18, 2013 2:40 PM by Dude! RSS

    Drop SYSAUX datafile

    user12209837

      We are facing an issue of DB file corruption in our ERP system.

       

      FILE_NAME TABLESPACE_NAME       ONLINE_STATUS

       

      +PRODDATA/prod/datafile/sysaux.311.725283627 SYSAUX ONLINE

      +PRODDATA/prod/datafile/sysaux.1n SYSAUX RECOVER

      +PRODDATA/prod/datafile/sysaux3.dbf SYSAUX ONLINE

       

       

      DB File sysaux.1n was holding tables/views related to AWR , I had recreated those objects in other SYSAUX datafiles.

      This datafile is not required anymore for DB and DB works perfectly fine without it.

       

      Seeking your assistance in dropping this SYSAUX datafile. As I understand, it cannot be dropped as it is system datafile and recommendation will be recreation of DB.

      But considering the effort involved in recreation, we are looking out for other solution.



      Thanks,

      K

        • 1. Re: Drop SYSAUX datafile
          clcarter

          The sysaux tablespace is not required for the instance, not quite like the system tablespace datafile.

           

          Could be possible to fix by recreating the control file(s) if there truly are no longer any extents in use in the suspect datafile. Get a controlfile trace backup, with a system connection run:

           

            alter database backup controlfile to trace resetlogs;

           

          And locate the new trace file way down in the trace directory under the DIAGNOSTIC_DEST location. Recreating the controlfile requires startup nomount and moving the current control file(s) out of the way, the instance will not overwrite existing controlfile(s). Do recovery, alter database open resetlogs, and (re) add the temp files.

           

          In the create controlfile DDL leave out the suspect datafile.

           

          *Might* be able to take the datafile offline and then drop it, although folks in the ASM forum should be able to offer better help.

           

          Automatic Storage Management

           

          Message was edited by: clcarter and remove bad datafile name

          • 2. Re: Drop SYSAUX datafile
            Dude!

            According to Logical Storage Structures a database must have the SYSTEM and SYSAUX tablespaces. During normal database operation, the database does not allow the SYSAUX tablespace to be dropped or renamed. If the SYSAUX tablespace becomes unavailable, then core database functionality remains operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.