4 Replies Latest reply on Jan 19, 2016 3:33 AM by 3153929

    pdb stuck in mount state over ora-1405 but can't see null in trace sql

    3153929

      i'm getting...

       

      SQL> alter pluggable database all open;

      alter pluggable database all open

      *

      ERROR at line 1:

      ORA-00604: error occurred at recursive SQL level 1

      ORA-01405: fetched column value is NULL

       

      the trace file says...

      EXEC #139923562890504:c=15000,e=22077,p=14,cr=289,cu=0,mis=1,r=0,dep=2,og=4,plh=3765558045,tim=1097391140564

      ORA-01405: fetched column value is NULL

      PARSING IN CURSOR #139923562890504 len=418 dep=1 uid=0 oct=3 lid=0 tim=1097390715788 hv=3759961951 ad='36b8c9418' sqlid='14d7z6mh1sxuz'

      select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0), nvl(evaledition#,1),nvl(unusablebefore#,0),nvl(unusablebeginning#,0) from col$ where obj#=:1 order by intcol#

      END OF STMT

      PARSE #139923562890504:c=0,e=285,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1097390715788

      BINDS #139923562890504:

      Bind#0

        oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

        oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0

        kxsbbbfp=7f427e80a8a8  bln=22  avl=02  flg=05

        value=68

       

      but the sql doesn't show nulls...

      SQL> select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0), nvl(evaledition#,1),nvl(unusablebefore#,0),nvl(unusablebeginning#,0) from col$ where obj#=68;

       

       

      NAME

      --------------------------------------------------------------------------------------------------------------------------------

         INTCOL#    SEGCOL#   TYPE#     LENGTH NVL(PRECISION#,0)

      ---------- ---------- ---------- ---------- -----------------

      DECODE(TYPE#,2,NVL(SCALE,-127/*MAXSB1MINAL*/),178,SCALE,179,SCALE,180,SCALE,181,SCALE,182,SCALE,183,SCALE,231,SCALE,0)    NULL$

      ---------------------------------------------------------------------------------------------------------------------- ----------

      FIXEDSTORAGE NVL(DEFLENGTH,0) DEFAULT$       ROWID

      ------------ ---------------- -------------------------------------------------------------------------------- ------------------

            COL#   PROPERTY NVL(CHARSETID,0) NVL(CHARSETFORM,0)     SPARE1 SPARE2 NVL(SPARE3,0) NVL(EVALEDITION#,1)

      ---------- ---------- ---------------- ------------------ ---------- ---------- ------------- -------------------

      NVL(UNUSABLEBEFORE#,0) NVL(UNUSABLEBEGINNING#,0)

      ---------------------- -------------------------

      OBJ#

        1    1       2 22    0

         -127 1

          0    0       AAAAACAAAAAAACVAAB

        1    0     0 0   0      0    0 0

            0       0

       

       

      COL#

        2    2       2 22    0

         -127 1

          0    0       AAAAACAAAAAAACVAAC

        2    0     0 0   0      0    0 0

            0       0

       

       

      BUCKET_CNT

        3    3       2 22    0

         -127 1

          0    0       AAAAACAAAAAAACVAAD

        3    0     0 0   0      0    0 0

            0       0

       

       

      ROW_CNT

        4    4       2 22    0

         -127 1

          0    0       AAAAACAAAAAAACVAAE

        4    0     0 0   0      0    0 0

            0       0

       

       

      CACHE_CNT

        5    5       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAF

        5    0     0 0   0      0    0 0

            0       0

       

       

      NULL_CNT

        6    6       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAG

        6    0     0 0   0      0    0 0

            0       0

       

       

      TIMESTAMP#

        7    7      12  7    0

            0 0

          0    0       AAAAACAAAAAAACVAAH

        7    0     0 0   0      0    0 0

            0       0

       

       

      SAMPLE_SIZE

        8    8       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAI

        8    0     0 0   0      0    0 0

            0       0

       

       

      MINIMUM

        9    9       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAJ

        9    0     0 0   0      0    0 0

            0       0

       

       

      MAXIMUM

        10   10       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAK

        10    0     0 0   0      0    0 0

            0       0

       

       

      DISTCNT

        11   11       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAL

        11    0     0 0   0      0    0 0

            0       0

       

       

      LOWVAL

        12   12      23       1000    0

            0 0

          0    0       AAAAACAAAAAAACVAAM

        12    0     0 0   0      0    0 0

            0       0

       

       

      HIVAL

        13   13      23       1000    0

            0 0

          0    0       AAAAACAAAAAAACVAAN

        13    0     0 0   0      0    0 0

            0       0

       

       

      DENSITY

        14   14       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAO

        14    0     0 0   0      0    0 0

            0       0

       

       

      INTCOL#

        15   15       2 22    0

         -127 1

          0    0       AAAAACAAAAAAACVAAP

        15    0     0 0   0      0    0 0

            0       0

       

       

      SPARE1

        16   16       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAQ

        16    0     0 0   0      0    0 0

            0       0

       

       

      SPARE2

        17   17       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAR

        17    0     0 0   0      0    0 0

            0       0

       

       

      AVGCLN

        18   18       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAS

        18    0     0 0   0      0    0 0

            0       0

       

       

      SPARE3

        19   19       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAT

        19    0     0 0   0      0    0 0

            0       0

       

       

      SPARE4

        20   20       2 22    0

         -127 0

          0    0       AAAAACAAAAAAACVAAU

        20    0     0 0   0      0    0 0

            0       0

      20 rows selected.


      btw container$ and pdb_spfile$ don't show any nulls either

       

      Is this a bug? It worked for months and then after a pwr outage it's been stuck.

      Any advice will be appreciated.

       

      Tks

      terry

        • 2. Re: pdb stuck in mount state over ora-1405 but can't see null in trace sql
          3153929

          hi vit,

           

          pdb was created as a pdb for the get go, and was added to with a new schema in the pdb using it's own tablespaces.

           

          I was wondering as such if dbms_pdb.recover would help?

          • 3. Re: pdb stuck in mount state over ora-1405 but can't see null in trace sql
            Franck Pachot

            Hi Terry,

            Your query didn't return any nulls but you didn't run it in the container that fails to open.

            >> Is this a bug? It worked for months and then after a pwr outage it's been stuck.

            Did you try to unplug the failing PDB an plug it elsewhere to see if it's the same?

            Did you open a SR? You can get more info in the trace with setting:

            alter system set events '01405 trace name errorstack level 3';

            Regards,

            Franck.

            • 4. Re: pdb stuck in mount state over ora-1405 but can't see null in trace sql
              3153929

              hi Franck,

               

              The trace file says...

               

              PARSING IN CURSOR #139923563194720 len=95 dep=1 uid=0 oct=3 lid=0 tim=1097391215817 hv=2084256870 ad='368bdc600' sqlid='0ywhw2ty3qg36'

              select version from registry$ where status not in (9, 99) and namespace='SERVER' and cid='APEX'

              END OF STMT

              PARSE #139923563194720:c=0,e=664,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1523033141,tim=1097391215816

              EXEC #139923563194720:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1523033141,tim=1097391215870

              FETCH #139923563194720:c=0,e=10,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=1523033141,tim=1097391215901

              STAT #139923563194720 id=1 cnt=1 pid=0 pos=1 obj=1508 op='TABLE ACCESS BY INDEX ROWID REGISTRY$ (cr=2 pr=0 pw=0 time=11 us cost=1 size=27 card=1)'

              STAT #139923563194720 id=2 cnt=1 pid=1 pos=1 obj=1509 op='INDEX UNIQUE SCAN REGISTRY_PK (cr=1 pr=0 pw=0 time=5 us)'

              CLOSE #139923563194720:c=0,e=26,dep=1,type=0,tim=1097391215958

               

               

              *** 2016-01-13 10:52:32.560

              dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)

              ----- Error Stack Dump -----

              ORA-00604: error occurred at recursive SQL level 1

              ORA-01405: fetched column value is NULL

              kpdbaKillPdbSessions: Starting kill.

               

              SQL in the cdb says...

               

              SQL> select version from registry$ where status not in (9, 99) and namespace='SERVER' and cid='APEX'

                2  /

               

               

              no rows selected

               

               

              SQL> select version from registry$ where  cid='APEX';

               

               

              no rows selected

               

              So I'm thinking doing a re-install of APEX 5.0 might just resolve the null issue. Not sure why it's looking for APEX when it's not installed/registered.