This discussion is archived
7 Replies Latest reply: Apr 23, 2013 8:52 AM by SohilBhavsar RSS

UNDOTBS1 and SYSAUX Tablespace Filename Mismatch in Oracle 11g XE

SohilBhavsar Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    SohilBhavsar Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    SohilBhavsar Newbie
    Currently Being Moderated
    Thanks Balazs.

    I just figured out that.

    Regards,

    Sohil Bhavsar.
  • 6. Re: UNDOTBS1 and SYSAUX Tablespace Filename Mismatch in Oracle 11g XE
    clcarter Expert
    Currently Being Moderated
    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
    SohilBhavsar Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points