Oracle Scratchpad

June 10, 2013

Metadata bug

Filed under: Bugs,Oracle — Jonathan Lewis @ 6:42 pm BST Jun 10,2013

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.

5 Comments »

  1. Jonathan, thanks for this post.
    There are lots of automated tools which rely on dbms_metadata.get_ddl to extract metadata information, this is nice finding, yet another discovery by mistake!

    Best,
    Horia

    Comment by Horia Berca — June 10, 2013 @ 8:02 pm BST Jun 10,2013 | Reply

  2. “full transportable export” (an 11.2.0.3 option) from datapump.
    can u tell more about it ? looking at Oracle® Database Utilities 11g Release 2 (11.2), there are no explanation of this feature

    Comment by Бушмелев Антон (@Djeday84) — June 11, 2013 @ 5:23 am BST Jun 11,2013 | Reply

    • I’ve been looking back at my notes, since I made that comment because it was just something I “happened to know”. It turns out that it’s in my notes of a 12c presentation about pluggable databases that I attended at Openworld last year – I don’t have the full context just a very brief comment about plugging an 11.2 database into a 12c container database.

      Comment by Jonathan Lewis — June 17, 2013 @ 7:44 am BST Jun 17,2013 | Reply

  3. …(most of) the people who work for Oracle are just ordinary people like you and me who make ordinary little mistakes in their programming …

    yes, every programmers make mistakes, but don’t they use computers to test their programming ?

    11.2 for linux was released in September 2010, we started testing in the same month, and on 6/10/2010 I accidently queried

    select dbms_metadata.get_ddl('TABLESPACE', 'SYSTEM') from dual

    against it and saw redo log and temp files included,

    I couldn’t believe that such a bug wasn’t discovered during testing, on the same day I filed
    SR 3-2127443701 : HS:dbms_metadata(‘TABLESPACE’, …) includes wrongly tempfiles and online redo-logfiles

    My testcase was very simple ( 1 datafile for tablespace system ) and reproducible.

    It seems that this bug was introduced by a fix to
    Bug 8467825 – Datapump export very slow

    So I still wonder, how quality assurance works within Oracle. Don’t they have a setup of automated simple testcases which are run when a critical code section within the kernel was changed ? Probably not, who knows.

    btw. thanks for the great Webinar yesterday on smarter statistics !

    Comment by Matthias Rogel — June 11, 2013 @ 2:09 pm BST Jun 11,2013 | Reply

    • Mathias,

      If you have a default database setup then you won’t see the error unless you apply dbms_metadata.get_ddl to SYSTEM, SYSAUX, or UNDO – and any good developer (a) knows that you don’t mess with tablespaces, and (b) will only be interested in checking that the tablespace they create for testing is extracted properly.

      After the event it’s OBVIOUS that there’s a boundary condition that should have been tested – but when you’re working forwards it’s hard to think backwards. This is why good testing involves one person writing the code to meet a specification, and another person to design the test cases from the specification.

      When I was a school teacher I set one of my pupils a “simple” programming problem, and crashed his program on my first attempt every week for six or seven weeks because I knew the boundary conditions that he would forget to trap. (Test 1, by the way, was that the program expected numeric input – so I gave it a character string.)

      Comment by Jonathan Lewis — June 17, 2013 @ 7:51 am BST Jun 17,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,266 other followers