7 Replies Latest reply: Apr 23, 2013 10:52 AM by Sohil Bhavsar RSS

    UNDOTBS1 and SYSAUX Tablespace Filename Mismatch in Oracle 11g XE

    Sohil Bhavsar
      Hi,

      We have installed Oracle 11g XE 32 bit on Windows and found that Tablespace name and filenames are mismatched for UNDOTBS1, SYSAUX.

      If you run following query:
      SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES; 
      You will get below output:
      TABLESPACE_NAME                    FILE_NAME 
      ============================================================================
      USERS                C:\ORACLE\ORACLE11GXE\APP\ORACLE\ORADATA\XE\USERS.DBF
      UNDOTBS1           C:\ORACLE\ORACLE11GXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
      SYSAUX                C:\ORACLE\ORACLE11GXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
      SYSTEM                C:\ORACLE\ORACLE11GXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
      Notice the difference between UNDOTBS1 and SYSAUX.

      UNDOTBS1 tablespace has filename SYSAUX.DBF while
      SYSAUX tablespace has filename UNDOTBS1.DBF

      Is this a bug or just a wrong name mapping?

      Will this affects the internal behavior of tablespace as well for undo tablespace and APEX Installation?


      Regards,

      Sohil Bhavsar.
        • 1. Re: UNDOTBS1 and SYSAUX Tablespace Filename Mismatch in Oracle 11g XE
          Balazs Papp
          No, it wont cause any problem.

          Known issue, see below threads (solution also written in them):

          XE11: sysaux & undo tablespaces and files
          Express edition 11g datafiles Issue
          • 2. Re: UNDOTBS1 and SYSAUX Tablespace Filename Mismatch in Oracle 11g XE
            clcarter
            Will this affects the internal behavior of tablespace
            Nope. Doesn't matter. A name is just a name. A filename is ... just a filename.

            Could cause some confusion when restoring from a backup, so if you want to fix it will take a little juggling. You could name your datafiles anything that fits withing the OS file naming rules, and leave off the .dbf extension from the filename as well, doesn't matter.
            • 3. Re: UNDOTBS1 and SYSAUX Tablespace Filename Mismatch in Oracle 11g XE
              Sohil Bhavsar
              Thanks Balazs and clcarter for clearing my doubt.

              I have managed to solve the other issues.

              Regards,

              Sohil Bhavsar.

              Edited by: Sohil Bhavsar on Apr 23, 2013 8:30 PM
              • 4. Re: UNDOTBS1 and SYSAUX Tablespace Filename Mismatch in Oracle 11g XE
                Balazs Papp
                Seems the datafile 'C:\ORACLE\ORACLE11GXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF' is already bigger then 300M, and the database tried to decrease it.
                select bytes/1024/1024 from dba_data_files where file_name='C:\ORACLE\ORACLE11GXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF';
                • 5. Re: UNDOTBS1 and SYSAUX Tablespace Filename Mismatch in Oracle 11g XE
                  Sohil Bhavsar
                  Thanks Balazs.

                  I just figured out that.

                  Regards,

                  Sohil Bhavsar.
                  • 6. Re: UNDOTBS1 and SYSAUX Tablespace Filename Mismatch in Oracle 11g XE
                    clcarter
                    Error: ORA-03297 ... related to this mismatch of UNDOTBS1 with SYSAUX
                    Actually its related to trying to resize a file smaller than the existing extents in use, so yes, restores aren't the only place that could cause confusion from the "incorrect" datafile names.

                    Fixing it could be done in one pass if you don't mind adjusting filenames so they don't clash, i.e. shut down your database instance and move the files at the OS, startup mount and rename the files in the database.

                    Don't use the services applet, the instance has to be shutdown with sqlplus, leave the database service running. If you try to open the database with OS files renamed but not corrected in the instance, the database won't open if there are troubles with the undo datafile.
                    sqlplus /nolog
                    conn /as sysdba;
                      ... connected ...
                    col name format a60
                    col tsname format a10
                    set lines 120
                    select t.name tsname,  d.name, d.BYTES / 1024 / 1024 mb from v$tablespace t, v$datafile d where t.ts#  = d.ts#;
                     system .../system.dbf  <n>
                     ...
                    shutdown immediate;
                    ... database closed ...
                    exit
                    cd <datafile directory>  
                    move sysaux.dbf undotbs01.dbf
                    move UNDOTBS1.DBF sysaux.dbf
                    # or use file explore GUI after shutdown
                    sqlplus /nolog
                    conn /as sysdba;
                      ... connected to idle instance ...
                    startup mount;
                      ... SGA, instance size info ...
                    alter database rename file 'C:\...<full path>\SYSAUX.DBF' to 'C:\...<full path>\UNDOTBS01.DBF';
                    alter database rename file 'C:\...<full path>\UNDOTBS1.DBF' to 'C:\...<full path>\SYSAUX.DBF';
                    select t.name tsname,  d.name, d.BYTES / 1024 / 1024 mb from v$tablespace t, v$datafile d where t.ts#  = d.ts#;
                    ... make sure you get what was asked for ...
                    TSNAME     NAME                                                       MB
                    ---------- -------------------------------------------------- ----------
                    SYSTEM     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF              360
                    SYSAUX     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF              660
                    UNDOTBS1   C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS01.DBF            25
                    USERS      C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF               100
                    alter database open;
                      ... database altered ...  
                    • 7. Re: UNDOTBS1 and SYSAUX Tablespace Filename Mismatch in Oracle 11g XE
                      Sohil Bhavsar
                      Thanks clcarter for detailed information.

                      I had allocated more space to both tablespace and it solved the issue.

                      I will check if there is a need to change the filenames but your steps will help other users as well.

                      Regards,

                      Sohil Bhavsar.