effect on changing system clock
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: effect on changing system clock

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    effect on changing system clock

    I am trying to build an Oracle application with time-sensitive data. Since this is a demo system and the data set only works correctly if the system time is set to certain date, I am trying to figure out what the effects on Oracle are if I keep rolling back system clock to a certain date without stopping Oracle/reboot the system. For one thing, I know if the tables were created after the date I roll back to, export will complain because the tables should not exist yet.

    As an example why I need to roll back the system clock: when user clicks on 'quarter to date' button in my page, it should show the data since the beginning of this current quarter. If I don't put in new data, as time goes by, the 'quarter to date' will not return anything after the next quarter starts. Thus I need to roll back the system clock in order to show some results.

    Does anybody know any other effects when system clock got rolled back?

    Any inputs are appreciated!

  2. #2
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Dear friend : the Oracle database per se will ** IGNORE ** the system clock : see, the database controls itself using an internal number, a "sequence" called SCN, not the system clock. So, if you change the system clock, the data WILL be rigorously THE SAME, and the database WILL be self-consistent.
    So, what will be your problems/difficulties if you change the clock ? Basically things/actions based in date/time - JOBs, RECOVERs UNTIL time, things like that.

    Now : your allegation about IMP being time/date dependent is , in the best of my knowledge, PURE and TOTAL b.s. : see the example (ignore the text messages in another language, it┤s just the routine sql*plus and MS-DOS messages) :


    system:SQL>select TO_CHAR(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') from dual;

    TO_CHAR(SYSDATE,'DD
    -------------------
    04/03/2004 21:39:06

    1 linha selecionada.


    system:SQL>create table TAB_TEST_EXP (c1 number, c2 varchar2(40));

    Tabela criada.

    system:SQL>insert into TAB_TEST_EXP values(1, 'Line 1');

    1 linha criada.

    system:SQL>insert into TAB_TEST_EXP values(2, 'Line 2');

    1 linha criada.

    system:SQL>commit;

    ValidašŃo completa.

    system:SQL>show user
    USER Ú "SYSTEM"

    ==> table created in 2004, march, ok ?

    Microsoft Windows 2000 [VersŃo 5.00.2195]
    (C) Copyright 1985-2000 Microsoft Corp.

    C:\>date /t
    qui 04/03/2004

    ==> ok, let┤s export ....

    C:\>exp system/manager@o8ir3 file=system_exp.dmp direct=y compress=n statistics=none tables=TAB_TEST_EXP

    Export: Release 8.1.7.0.0 - Production on Qui Mar 4 21:44:10 2004

    (c) Copyright 2000 Oracle Corporation. All rights reserved.


    Conectado a: Personal Oracle8i Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production
    Exporta■Ďo executada no conjunto de caracteres de WE8ISO8859P1 e no conjunto de caracteres de WE8ISO8859P1 NCHAR

    Sobre exportar tabelas especificadas ... via Caminho Direto ...
    . . exporting table TAB_TEST_EXP 2 linhas exportadas
    Exporta■Ďo encerrada com █xito, sem advert█ncias.

    ===>>> now I will change the system clock, see the proofs :

    system:SQL>select TO_CHAR(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') from dual;

    TO_CHAR(SYSDATE,'DD
    -------------------
    04/03/2003 21:47:47

    1 linha selecionada.

    system:SQL>drop table TAB_TEST_EXP;

    Tabela eliminada.


    C:\>date /t
    ter 04/03/2003

    ===> the key point : I will try to import a .dmp with a table created AFTER the system clock date ...


    C:\>imp system/manager@o8ir3 file=system_exp.dmp analyze=n full=y

    Import: Release 8.1.7.0.0 - Production on Ter Mar 4 21:49:28 2003

    (c) Copyright 2000 Oracle Corporation. All rights reserved.


    Conectado a: Personal Oracle8i Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production

    Arquivo de exporta■Ďo criado por EXPORT:V08.01.07 via caminho direto
    importa■Ďo realizada nos conjuntos de caracteres WE8ISO8859P1 e NCHAR WE8ISO8859P1
    . importando objetos de SYSTEM para SYSTEM
    . . importando table "TAB_TEST_EXP" 2 linhas importadas
    Importa■Ďo encerrada com █xito, sem advert█ncias.


    system:SQL>select TO_CHAR(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') from dual;

    TO_CHAR(SYSDATE,'DD
    -------------------
    04/03/2003 21:50:25

    1 linha selecionada.

    system:SQL>select * from TAB_TEST_EXP;

    C1 C2
    ---------- ----------------------------------------
    1 Line 1
    2 Line 2

    2 linhas selecionadas.

    system:SQL>

    ===>>>WOW!! TOTAL and ABSOLUTE success : case dismissed......

    SUGGESTION : I suppose that the reason for you wanting to change the system clock is to do something like that :

    select datecolumn - SYSDATE from sometable

    and you DO NOT want the correct/real day/time in SYSDATE, so think about writing a MY_SYSDATE function returning the desired date (reading it from a parameter/auxiliar table, maybe)

    Regards,

    Chiappa

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Sounds like you're creating a monster. I'm not sure I understand why you're "rolling back" the system clock. Why not just execute your query with the criteria "sysdate - value"?

    But then again .. I may be wrong.
    - Dennis Miller
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    Apr 2001
    Posts
    257
    JChiappa,

    Thank you for the elaborated example showing me your point. However, if I am not mistaken, you rolled back your clock AFTER the export. My original post said: "For one thing, I know if the tables were created after the date I roll back to, export will complain because the tables should not exist yet.". Maybe I did not make it clear the export was done AFTER the clock is rolled back to prior table creation date.

    Here is the sequence of events for my scenerio:

    1. 1/2/2004: tables are created
    2. 1/3/2004: data inserted into the tables
    3. I roll back the clock to 1/1/2004
    4. do an export on 1/1/2004, during the export, exp will complain. (pardon me for not posting the exact messages. I will do it once the demo is system is not needed for demo)

    As for why I need to do this: the key point here is it's a DEMO system (not a PRODUCTION system) with a fixed set of data, which are entered only once. As an example,

    1. Data entered on 1/2/2004
    2. In January 2004, when I query 'month-to-date', I get the data above returned.
    3. This is a demo system with a fixed set of data entered in Step 1 above. And I don't enter any more data ever.
    2. Now it's 3/4/2004. and I try to do a query 'month-to-date', I get nothing back because it's already March 2004.

    Thus when I need to do a demo, I need to roll back to January 2004 in order to show the data.

    Does this make sense or do I miss something here?
    Last edited by a128; 03-04-2004 at 11:19 PM.

  5. #5
    Join Date
    Feb 2004
    Posts
    50
    As JChiappa points out, point in time recovery is an issue, so beware.

    For one thing, I know if the tables were created after the date I roll back to,
    export will complain because the tables should not exist yet.
    You may well get an error from export, but how do you know it is "because the tables
    should not exist yet."?

    I bet you can select from them, even though they shouldn't exist yet.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width