Metadata bug

Metadata bug:
Here’s a funny little bug – which probably won’t cause any damage – that may remind you that (most of) the people who work for Oracle are just ordinary people like you and me who make ordinary little mistakes in their programming. It’s a bug I discovered by accident because I just wanted to check something about how a particular undo tablespace had been defined, and I called dbms_metadata instead of querying dba_tablespaces. Here’s the cut-n-paste from an SQL*Plus session on 11.2.0.2:

SQL> select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') text from dual;

TEXT
----------------------------------------------------------------------------------------------------

  CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
  SIZE 209715200
  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M,
  '/u01/app/oracle/oradata/TestDB11/redo03.log' SIZE 209715200
  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M,
  '/u01/app/oracle/oradata/TestDB11/undotbs01.dbf' SIZE 209715200
  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   ALTER DATABASE DATAFILE
  '/u01/app/oracle/fast_recovery_area/TESTDB11/flashback/o1_mf_8v4qrxy1_.flb' RESIZE 959447040
   ALTER DATABASE DATAFILE
  '/u01/app/oracle/oradata/TestDB11/redo03.log' RESIZE 959447040
   ALTER DATABASE DATAFILE
  '/u01/app/oracle/oradata/TestDB11/undotbs01.dbf' RESIZE 959447040

SQL> select file_name from dba_data_files where tablespace_name = 'UNDOTBS1';

FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/TestDB11/undotbs01.dbf

SQL> select member from v$logfile;

MEMBER
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/TestDB11/redo01.log
/u01/app/oracle/oradata/TestDB11/redo02.log
/u01/app/oracle/oradata/TestDB11/redo03.log

As you can see, a call to dbms_metadata.get_ddl() is telling me that my undo tablespace is made up of two files (and then wants to alter three (!) of them). One of which looks suspiciously like it might be one of my redo log files, and the third file is one of the flashback logs in my fast recovery area! So I queried dba_tablespaces to find that (as expected) I had only one file in my undo tablespace, and then I queried v$logfile to check whether I really did have a log file called redo03.log and I checked the fast recovery area to see when that last file had appeared.
I don’t suppose this error in dbms_metadata will have any serious side effects – although I did wonder if there might be some ramifications for a “full transportable export” (an 11.2.0.3 option) from datapump.
A quick check on MOS revealed this to be a known bug fixed in 12.1: “Bug 10177856 : DBMS_METADATA(‘TABLESPACE’, …) INCLUDES WRONGLY TEMPFILES AND ONLINE REDO-LOGF” The specific bug (and there were three reported by my search) described the SYSTEM tablespace report also reporting the temporary tablespace(s) – so I did a quick check on my SYSTEM and SYSAUX to see what would happen, and the results made it easy to see how the problem had happened – when it was collecting filenames the code in dbms_metadata had failed to check file types properly.
My query for SYSTEM reported system.dbf along with temp01.dbf and redo01.log and an OMF-named flashback log.

My query for SYSAUX report sysaux.dbf along with temp_special.dbf and redo02.log and an OMF-named flashback log.
Here’s a query (runnable only by SYS) showing why those particular extra files appeared on my system:

SQL> select fnfno, fntyp,fnnam from x$kccfn where fnfno <= 3 order by fnfno, fntyp;

     FNFNO      FNTYP FNNAM
---------- ---------- ---------------------------------------------------------------------------
         0        200 /u01/app/oracle/oradata/TestDB11/bct.dbf

         1          3 /u01/app/oracle/oradata/TestDB11/redo01.log
                    4 /u01/app/oracle/oradata/TestDB11/system01.dbf
                    7 /u01/app/oracle/oradata/TestDB11/temp01.dbf
                   24 /u01/app/oracle/fast_recovery_area/TESTDB11/flashback/o1_mf_8v439xsc_.flb

         2          3 /u01/app/oracle/oradata/TestDB11/redo02.log
                    4 /u01/app/oracle/oradata/TestDB11/sysaux01.dbf
                    7 /u01/app/oracle/oradata/TestDB11/temp_special.dbf
                   24 /u01/app/oracle/fast_recovery_area/TESTDB11/flashback/o1_mf_8v43b0hp_.flb

         3          3 /u01/app/oracle/oradata/TestDB11/redo03.log
                    4 /u01/app/oracle/oradata/TestDB11/undotbs01.dbf
                   24 /u01/app/oracle/fast_recovery_area/TESTDB11/flashback/o1_mf_8v4qrxy1_.flb
 

The code is reporting the names of the corresponding files of types 3, 7 and 24 whenever it reports a file of type 4 (which is the data files). There are other possible file types, of course, and if I had other features enabled perhaps I would see some of them. Running dbms_metadata.get_ddl with tracing enabled showed me that it queries view ku$_tablespace_view, which includes a join to view ku$_file_view, which includes a three-part union all of x$kccfn including subqueries for files of type 4 and 7 specifically, and a subquery with no restriction on file type but a join on file number to the flashback file list x$ktfthc.
Although the bug is reported as fixed in 12.1, it seems to be fixed in 11.2.0.3 (so that deals with my worry about datapump) – it was just unlucky (or lucky, depending on your viewpoint) that I happened to run my first query on version 11.2.0.2.



DIGITAL JUICE