This discussion is archived
6 Replies Latest reply: Jan 12, 2013 8:06 AM by vk82 RSS

ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier

vk82 Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    You may need to recompile the function,
    alter function sys.scn_to_timestamp compile;
  • 2. Re: ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier
    JohnWatson Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Still getting the same problem after function recompilation on pri side
  • 4. Re: ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier
    JohnWatson Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points