6 Replies Latest reply: Jan 12, 2013 10:06 AM by vk82 RSS

    ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier

    vk82
      Hi All,


      My DB version is 10gr2
      OS Version Windows

      I created a physical standby on the same machine on which production DB is running for testing purpose. I follow the Arup Nanda Blog to find out the Gaps in Data Guard.

      http://arup.blogspot.in/2009/12/resolving-gaps-in-data-guard-apply.html


      I checked the SCN no on pri DB s follows:

      SQL> select scn_to_timestamp(1447102) from dual;


      and gets

      SCN_TO_TIMESTAMP(1447102)
      -------------------------------
      18-DEC-09 08.54.28.000000000 AM

      but when i do the same thing on standby :

      SQL> select scn_to_timestamp(1301571) from dual;

      I am getting below mentioned error:

      ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier


      Can anyone help me in understanding what exactly the problem is.
        • 1. Re: ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier
          JohnWatson
          You may need to recompile the function,
          alter function sys.scn_to_timestamp compile;
          • 2. Re: ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier
            JohnWatson
            See this sequence of events:
            orcl> select scn_to_timestamp(1447102) from dual;
            
            SCN_TO_TIMESTAMP(1447102)
            ---------------------------------------------------------------------------
            12-JAN-13 08.35.13.000000000
            
            orcl> shu abort;
            ORACLE instance shut down.
            orcl> startup mount;
            ORACLE instance started.
            
            Total System Global Area  669581312 bytes
            Fixed Size                  1386736 bytes
            Variable Size             503318288 bytes
            Database Buffers          159383552 bytes
            Redo Buffers                5492736 bytes
            Database mounted.
            orcl> select scn_to_timestamp(1447102) from dual;
            select scn_to_timestamp(1447102) from dual
                   *
            ERROR at line 1:
            ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier
            
            
            orcl> alter database open;
            
            Database altered.
            
            orcl> select scn_to_timestamp(1447102) from dual;
            select scn_to_timestamp(1447102) from dual
                   *
            ERROR at line 1:
            ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier
            
            
            orcl> alter function sys.scn_to_timestamp compile;
            alter function sys.scn_to_timestamp compile
            *
            ERROR at line 1:
            ORA-04043: object SCN_TO_TIMESTAMP does not exist
            
            
            orcl> alter function sys.scn_to_timestamp compile;
            
            Function altered.
            
            orcl> select scn_to_timestamp(1447102) from dual;
            
            SCN_TO_TIMESTAMP(1447102)
            ---------------------------------------------------------------------------
            12-JAN-13 08.35.13.000000000
            
            orcl>
            • 3. Re: ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier
              vk82
              Still getting the same problem after function recompilation on pri side
              • 4. Re: ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier
                JohnWatson
                It is very disappointing when one tries to help, and gets no sort of "thank you" in return. Doesn't encourage any further attempt to assist. Perhaps someone else will try.
                • 5. Re: ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier
                  mseberg
                  Hello;

                  John did go out of his way and gave you great detail on the recompile. ( So thanks for the details John, I did not know this )

                  I would try a minimum value for the argument of the function. The SCN used cannot be be smaller than the minimal value given by :
                  SELECT MIN( SCN )FROM SYS.SMON_SCN_TIME;
                  All my Oracle 10 databases are gone so I have no way to test.

                  Later

                  You show :
                   select scn_to_timestamp(1301571) from dual;
                  Which is the number shown here :

                  http://arup.blogspot.in/2009/12/resolving-gaps-in-data-guard-apply.html

                  What is your s SCN that is throwing the error?

                  select current_scn from v$database;

                  Best Regards

                  mseberg

                  Edited by: mseberg on Jan 12, 2013 6:36 AM
                  • 6. Re: ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier
                    vk82
                    Thanks for the details mseberg,


                    As your require the scn for my pri db that is

                    SQL> select current_scn from v$database;

                    CURRENT_SCN
                    -----------
                    611259


                    and for standby DB is :


                    SQL> select current_scn from v$database;

                    CURRENT_SCN
                    -----------
                    568890



                    Thanks john and mseberg both for your nice help i came to know where i am mistaking i need to run the above mentioned command on pri db instead of running on standby.

                    Edited by: vk82 on Jan 12, 2013 9:34 PM