• 15. Re: RAC database will not open after enabling DB Vault
      toonie

      Sorry for the delay in responding. Also - its worth emphasising that this train of thought may not lead anywhere in relation to a resolution (But we'll see)...

       

      This is how I'd do it for 12c on a Linux platform.

       

      -- Set the trace
      SYS @ SANDPRD > alter system set events '942  trace name ERRORSTACK level 3';
      System altered.
      Elapsed: 00:00:00.15
      SYS @ SANDPRD
      
      
      -- Alert log should be located somewhere like here: $ORACLE_BASE/diag/rdbms/<sid>/<sid>/trace
      -- cd to the directory where your alert log exists
      oracle@oralinux7 trace]$ pwd
      /u01/app/oracle/diag/rdbms/sandprd/SANDPRD/trace
      
      
      -- Try something like this, create an empty file (This is just to make your search and results easier to find/distinguish)
      [oracle@oralinux7 trace]$ touch justafile
      [oracle@oralinux7 trace]$ ls -lrt justafile
      -rw-r--r--. 1 oracle oinstall 0 Aug 13 00:35 justafile
      
      
      -- Now from a different session - recreate the 942 error
      APP @ SANDPRD > select * from thistabledoesnotexist;               
      select * from thistabledoesnotexist
                    *
      ERROR at line 1:
      ORA-00942: table or view does not exist
      APP @ SANDPRD
      
      
      -- Now back to the first session, exit back out to the OS and perform a quick grep command
      -- This just looks for files in the current directory and sub-directories which are "newer" than our empty justafile
      -- and performs a grep for the text '942' on the result set and returns the filename
      [oracle@oralinux7 trace]$ find ./* -newer justafile -exec grep -l '942'  {} \;
      ./alert_SANDPRD.log
      grep: ./cdmp_20160813003809: Is a directory
      ...
      ./cdmp_20160813003809/SANDPRD_q003_27668_bucket.trc
      ./cdmp_20160813003809/SANDPRD_ora_75920_bucket.trc
      ./SANDPRD_ora_36871.trc
      
      
      -- Its likely the last trace file above is our guy
      -- Now veiwing the file below... Bingo. We've found the Query which caused the ORA error
      [oracle@oralinux7 trace]$ more ./SANDPRD_ora_36871.trc
      Trace file /u01/app/oracle/diag/rdbms/sandprd/SANDPRD/trace/SANDPRD_ora_36871.trc
      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
      ORACLE_HOME = /u01/app/oracle/product/12102
      System name: Linux
      Node name: oralinux7
      Release: 3.8.13-118.4.1.el7uek.x86_64
      Version: #2 SMP Tue Mar 8 10:22:24 PST 2016
      Machine: x86_64
      Instance name: SANDPRD
      Redo thread mounted by this instance: 1
      Oracle process number: 29
      Unix process pid: 36871, image: oracle@oralinux7 (TNS V1-V3)
      *** 2016-08-13 00:38:07.809
      *** SESSION ID:(67.15562) 2016-08-13 00:38:07.809
      *** CLIENT ID:() 2016-08-13 00:38:07.809
      *** SERVICE NAME:(SYS$USERS) 2016-08-13 00:38:07.809
      *** MODULE NAME:(SQL*Plus) 2016-08-13 00:38:07.809
      *** CLIENT DRIVER:(SQL*PLUS) 2016-08-13 00:38:07.809
      *** ACTION NAME:() 2016-08-13 00:38:07.809
      dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
      ----- Error Stack Dump -----
      ----- Current SQL Statement for this session (sql_id=7sfhau6j0vkvn) -----
      select * from thistabledoesnotexist
      

       

      Regards,

      Ruan

      • 16. Re: RAC database will not open after enabling DB Vault
        Junaidullah.Khan-Oracle

        Thanks Toonie! The process should be virtually the same for 11.2.04 correct?

        • 17. Re: RAC database will not open after enabling DB Vault
          Junaidullah.Khan-Oracle

          The issue is occurring on 11.2.0.4 Oracle Solaris on SPARC (64-BIT)

          • 18. Re: RAC database will not open after enabling DB Vault
            toonie

            Yes, very much so. The same logical process applies. The destination of your alert log may differ slightly. You should be able to confirm it using the below:

             

            select value from v$parameter where name='background_dump_dest';

             

            Obviously, you don't need to do what I did in order to recreate the actual error, but just allow it to happen as you have been encountering it...

             

            Regards

            Ruan

            • 19. Re: RAC database will not open after enabling DB Vault
              Junaidullah.Khan-Oracle

              Ok, finally here is said trace:

               

              Trace file /u01/app/oracle/diag/rdbms/lnkdbee/lnkdbee1/trace/lnkdbee1_ora_119700.trc

              Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

              With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

              OLAP, Data Mining and Oracle Database Vault options

              ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1

              System name: Linux

              Node name: LNKI941

              Release: 3.0.101-77-default

              Version: #1 SMP Tue Jun 14 20:33:58 UTC 2016 (a082ea6)

              Machine: x86_64

              VM name: VMWare Version: 6

              Instance name: lnkdbee1

              Redo thread mounted by this instance: 1

              Oracle process number: 32

              Unix process pid: 119700, image: oracle@LNKI941 (TNS V1-V3)

               

               

              *** 2016-08-22 14:04:47.239

              *** SESSION ID:(17.5) 2016-08-22 14:04:47.239

              *** CLIENT ID:() 2016-08-22 14:04:47.239

              *** SERVICE NAME:() 2016-08-22 14:04:47.239

              *** MODULE NAME:(sqlplus@LNKI941 (TNS V1-V3)) 2016-08-22 14:04:47.239

              *** ACTION NAME:() 2016-08-22 14:04:47.239

               

              2016-08-22 14:04:47.239357 : Start recovery for domain=0, valid=0, flags=0x4

              Successfully allocated 2 recovery slaves

              Using 67 overflow buffers per recovery slave

              Thread 1 checkpoint: logseq 5348, block 2, scn 319206661

                  on-disk rba: logseq 5348, block 3, scn 319206663

                start recovery at logseq 5348, block 3, scn 319206663

               

              *** 2016-08-22 14:04:47.468

              Started resilvering redo thread 1 seq 5348 blocks 3-47

               

              *** 2016-08-22 14:04:47.468

              Completed resilvering redo thread 1 seq 5348

               

              *** 2016-08-22 14:04:47.469

              Started writing zeroblks thread 1 seq 5348 blocks 48-55

               

              *** 2016-08-22 14:04:47.469

              Completed writing zeroblks thread 1 seq 5348

              ==== Redo read statistics for thread 1 ====

              Total physical reads (from disk and memory): 4096Kb

              -- Redo read_disk statistics --

              Read rate (ASYNC): 22Kb in 0.06s => 0.37 Mb/sec

              Longest record: 0Kb, moves: 0/55 (0%)

              Longest LWN: 1Kb, moves: 0/25 (0%), moved: 0Mb

              Last redo scn: 0x0000.1306b543 (319206723)

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

              ----- Recovery Hash Table Statistics ---------

              Hash table buckets = 262144

              Longest hash chain = 1

              Average hash chain = 15/15 = 1.0

              Max compares per lookup = 1

              Avg compares per lookup = 85/100 = 0.8

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

               

              *** 2016-08-22 14:04:47.471

              KCRA: start recovery claims for 15 data blocks

               

              *** 2016-08-22 14:04:47.476

              KCRA: blocks processed = 15/15, claimed = 15, eliminated = 0

               

              *** 2016-08-22 14:04:47.499

              Recovery of Online Redo Log: Thread 1 Group 2 Seq 5348 Reading mem 0

               

              *** 2016-08-22 14:04:47.502

              Completed redo application of 0.01MB

               

              *** 2016-08-22 14:04:47.504

              Completed recovery checkpoint

              ----- Recovery Hash Table Statistics ---------

              Hash table buckets = 262144

              Longest hash chain = 1

              Average hash chain = 15/15 = 1.0

              Max compares per lookup = 1

              Avg compares per lookup = 100/100 = 1.0

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

              Recovery sets nab of thread 1 seq 5348 to 48 with 8 zeroblks

              2016-08-22 14:04:47.622599 : Validate domain 0

              2016-08-22 14:04:47.623013 : Validated domain 0, flags = 0x0

               

              *** 2016-08-22 14:04:49.442

              ORA-00604: error occurred at recursive SQL level 1

              ORA-00942: table or view does not exist

              ORA-00604: error occurred at recursive SQL level 1

              ORA-00942: table or view does not exist

               

              *** 2016-08-22 14:04:49.443

              USER (ospid: 119700): terminating the instance due to error 604

              • 20. Re: RAC database will not open after enabling DB Vault
                1518718

                Hi, I am also facing the same issue.. Is this fixed?

                • 21. Re: RAC database will not open after enabling DB Vault
                  Junaidullah.Khan-Oracle

                  No, I'm still trying to get to the bottom of it as well.  Any additional info/guidance would be greatly appreciated!

                   

                  This latest trace file is a weird trace......doesn't even show us the SQL they're using.

                  I can see from the errorstack trace it gives us some information, the SQL there is:

                   

                  select * from thistabledoesnotexist

                   

                  Which, if the table doesn't exist will give a 942 error which is what we see in the trace.

                  a colleague did share that "XDB is not required per say.......if you have any applications using the packages that require it...we can see this message.....So you need to get XDB validated.......see this note:

                   

                  ORA-00604 And ORA-00942 In Alert.log With Database Vault and Without XDB (Doc ID 952645.1)

                   

                  That is likely the issue.......since it's during startup"

                   

                  This course of action was tried a while back but maybe I missed somethingI'm going to go through this and give it another try

                  1 2 上一个 下一个