This discussion is archived
2 Replies Latest reply: May 6, 2013 10:00 AM by 885789 RSS

assignment datafile names < -- > tablespace names is not correct for UNDO

885789 Newbie
Currently Being Moderated
The datafile names assigned to UNDOTBS1 and SYSAUX are interchanged in a newly installed XE11 (Windows):

select tablespace_name, file_name from dba_data_files where tablespace_name in ('SYSAUX','UNDOTBS1');

TABLESPACE_NAME     FILE_NAME
UNDOTBS1               E:\ORACLE\11G_XE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
SYSAUX               E:\ORACLE\11G_XE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF

The settings maybe misleading if you manually adjust of UNDO space.
Be careful.

kind regards
Robert
  • 1. Re: assignment datafile names < -- > tablespace names is not correct for UNDO
    clcarter Expert
    Currently Being Moderated
    Quite a few threads already on that one, i.e. {thread:id=2528082}

    There's an easy fix, shutdown the database, move the datafiles, startup nomount; alter database rename file '<old filename>' to '<new filename>'; alter database open;

    Better to first move the sysaux.dbf file to i.e. undotbs01.dbf first so it won't clash with the existing undotbs1.dbf. then fix the other filename and do the startup mount bit.

    Edited by: clcarter on May 6, 2013 12:53 PM
    fix rename file syntax
  • 2. Re: assignment datafile names < -- > tablespace names is not correct for UNDO
    885789 Newbie
    Currently Being Moderated
    @clcarter
    thanks a lot. I have already fixed the problem. Here's my sequence of actions:

    1.     Offline-Backup der ganzen Datenbank
    2.     create undo tablespace undo2 datafile 'E:\APP\ORACLE\ORADATA\XE\UNDOTBS2.DBF' size 100M autoextend off;
    3.     alter system checkpoint;
    4.     alter system set undo_tablespace='undo2';
    5.     drop tablespace UNDOTBS1 including contents and datafiles;
    6.     shutdown immediate
    7.     cp E:\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF E:\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
    8.     startup mount
    9.     alter database rename file 'E:\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF' to 'E:\APP\ORACLE\ORADATA\XE\SYSAUX.DBF';
    10.     alter database open;
    11.     create undo tablespace UNDOTBS1 datafile 'E:\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF' size 250M autoextend on next 10M maxsize 1000M;
    12.     alter system set undo_tablespace='UNDOTBS1';
    13.     drop tablespace undo2 including contents and datafiles;

Legend

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