3 Replies Latest reply: May 18, 2012 7:07 AM by Udo RSS

    XE11: sysaux & undo tablespaces and files

    717136
      Hello,

      does anyone recognized the mixed names of sysaux and undo tablespaces?

      If i use:
      SQL> SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
      SQL> FROM dba_data_files
      SQL> ORDER BY 1;

      then i will get:
      FILE_NAME TABLESPACE MB
      ---------------------------------------------------------------------- --------------- ----------
      C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF UNDOTBS1 26
      C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF SYSTEM 369
      C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF SYSAUX 666
      C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF USERS 102

      The file SYSAUX.DBF points to tablespace UNDOTBS1 and
      the file UNDOTBS1.DBF points to tablespace SYSAUX

      Could this cause a Problem ?
      Can this be solved?

      This is only in the windows-version, not in the linux-version.

      Best reagrds
      AK
        • 1. Re: XE11: sysaux & undo tablespaces and files
          jariola
          Hi,

          I have seen similar post in this forum. And if I remember there is some instructions to fix this. Try search that post.

          But what I understanding, this is not causing any problem, it is just confusing tablespace naming=)


          Regards,
          Jari

          -----
          My Blog: http://dbswh.webhop.net/dbswh/f?p=BLOG:HOME:0
          Twitter: http://www.twitter.com/jariolai

          Edited by: jarola on May 17, 2012 2:54 PM

          See this post also
          SYSAUX and UNDOTBS1 are reversed
          • 2. Re: XE11: sysaux & undo tablespaces and files
            717136
            Hello,

            i did the following:

            SQLPLUS:
            SHUTDOWN IMMEDIATE;

            AS ADMINISTRATOR:
            COPY C:\oraclexe\app\oracle\oradata\XE\SYSAUX.DBF C:\oraclexe\app\oracle\oradata\XE\UNDOTBS2.DBF
            COPY C:\oraclexe\app\oracle\oradata\XE\UNDOTBS1.DBF C:\oraclexe\app\oracle\oradata\XE\SYSAUX2.DBF

            SQLPLUS:
            STARTUP MOUNT:
            ALTER DATABASE RENAME FILE 'C:\oraclexe\app\oracle\oradata\XE\SYSAUX.DBF' TO 'C:\oraclexe\app\oracle\oradata\XE\UNDOTBS2.DBF';
            ALTER DATABASE RENAME FILE 'C:\oraclexe\app\oracle\oradata\XE\UNDOTBS1.DBF' TO 'C:\oraclexe\app\oracle\oradata\XE\SYSAUX2.DBF';
            ALTER DATABASE OPEN;
            SHUTDOWN IMMEDIATE;

            AS ADMINISTRATOR:
            COPY C:\oraclexe\app\oracle\oradata\XE\SYSAUX2.DBF C:\oraclexe\app\oracle\oradata\XE\SYSAUX.DBF (overwrite y)
            COPY C:\oraclexe\app\oracle\oradata\XE\UNDOTBS2.DBF C:\oraclexe\app\oracle\oradata\XE\UNDOTBS1.DBF (overwrite y)

            SQLPLUS:
            STARTUP MOUNT:
            ALTER DATABASE RENAME FILE 'C:\oraclexe\app\oracle\oradata\XE\SYSAUX2.DBF' TO 'C:\oraclexe\app\oracle\oradata\XE\SYSAUX.DBF';
            ALTER DATABASE RENAME FILE 'C:\oraclexe\app\oracle\oradata\XE\UNDOTBS2.DBF' TO 'C:\oraclexe\app\oracle\oradata\XE\UNDOTBS1.DBF';
            ALTER DATABASE OPEN;
            SHUTDOWN IMMEDIATE;

            AS ADMINISTRATOR:
            DEL C:\oraclexe\app\oracle\oradata\XE\SYSAUX2.DBF
            DEL C:\oraclexe\app\oracle\oradata\XE\UNDOTBS2.DBF

            SQLPLUS:
            STARTUP

            RMAN:
            REPORT SCHEMA;

            Spooling started in log file: switch.txt

            Recovery Manager11.2.0.2.0

            RMAN>
            Report of database schema for database with db_unique_name XE

            List of Permanent Datafiles
            ===========================
            File Size(MB) Tablespace RB segs Datafile Name
            ---- -------- -------------------- ------- ------------------------
            1 360 SYSTEM *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
            2 650 SYSAUX *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
            3 25 UNDOTBS1 *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
            4 100 USERS *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

            List of Temporary Files
            =======================
            File Size(MB) Tablespace Maxsize(MB) Tempfile Name
            ---- -------- -------------------- ----------- --------------------
            1 20 TEMP 32767 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF

            RMAN>

            Recovery Manager complete.

            It's not looking elegant but it's working.
            Is there a other way?

            Best regards.
            • 3. Re: XE11: sysaux & undo tablespaces and files
              Udo
              Hello Achim,

              the bug had already been detected in the beta phase, but it seems it hasn't been fixed for the final release as it's no show stopper.
              It has been discussed shortly in this forum as well: {thread:id=2291609}
              There is no real "elegant" solution - you have to stop the database, move/copy the actual files, bring up the database again and rename the references.
              If you want the original (intended) file names, you could get that as well by taking another step of renaming...
              The only "more elegant" solution would be to patch the installer. I personally preferred to stick with the original package and created a custom post-install script to do the renaming (and some other things...)

              -Udo