FAQ
Hi DBA gurus,



I need to flashback our EBS database to 10 hours ago, also hope the last
five hours data can be recovered with export/import. Is there a way to
find which table's/schema's data has been changed in last five hours, so
that I could export the schemas/tables before flashback and import them
after flashback?



Thanks,

Lu

Search Discussions

  • Roger Xu at Aug 3, 2009 at 6:28 pm
    Which Oracle version? It depends on the undo you have ... What is the undo
    retention setting?
    On Mon, Aug 3, 2009 at 1:15 PM, Jiang, Lu wrote:

    Hi DBA gurus,



    I need to flashback our EBS database to 10 hours ago, also hope the last
    five hours data can be recovered with export/import. Is there a way to find
    which table's/schema's data has been changed in last five hours, so that I
    could export the schemas/tables before flashback and import them after
    flashback?



    Thanks,

    Lu
    --
    http://www.freelists.org/webpage/oracle-l
  • Jiang, Lu at Aug 3, 2009 at 6:29 pm
    Version 10.2.0.3. Undo retention should be 24 hours I think, let me
    double check.



    Thanks,

    Lu



    From: Roger Xu
    Sent: Monday, August 03, 2009 2:28 PM
    To: Jiang, Lu
    Cc: ORACLE-L
    Subject: Re: Find which table's data has been changed in last five
    hours.



    Which Oracle version? It depends on the undo you have ... What is the
    undo retention setting?

    On Mon, Aug 3, 2009 at 1:15 PM, Jiang, Lu wrote:

    Hi DBA gurus,



    I need to flashback our EBS database to 10 hours ago, also hope the last
    five hours data can be recovered with export/import. Is there a way to
    find which table's/schema's data has been changed in last five hours, so
    that I could export the schemas/tables before flashback and import them
    after flashback?



    Thanks,

    Lu
  • Jiang, Lu at Aug 3, 2009 at 6:52 pm
    Thanks Julio.

    dba_tab_modifications returns no rows with today's timestamp :)





    From: Julio Aguilar-Chang
    Sent: Monday, August 03, 2009 2:39 PM
    To: Jiang, Lu
    Cc: ORACLE-L
    Subject: Re: Find which table's data has been changed in last five
    hours.



    Try dba_tab_modifications. It has columns such as the table_owner,
    table_name, number of inserts/updates/deletes, and the timestamp.

    Jiang, Lu wrote:

    Hi DBA gurus,



    I need to flashback our EBS database to 10 hours ago, also hope the last
    five hours data can be recovered with export/import. Is there a way to
    find which table's/schema's data has been changed in last five hours, so
    that I could export the schemas/tables before flashback and import them
    after flashback?



    Thanks,

    Lu

    --

    *****************************************
    Julio Aguilar-Chang
    jachang_at_lanl.gov
    (505) 667-1004 - work
    *****************************************

    --
    http://www.freelists.org/webpage/oracle-l
  • Roger Xu at Aug 3, 2009 at 6:56 pm
    DBA_TAB_MODIFICATIONS describes modifications to all tables in the database
    that have been modified since the last time statistics were gathered on the
    tables. Try flashback_transaction_query ...
    On Mon, Aug 3, 2009 at 1:52 PM, Jiang, Lu wrote:

    Thanks Julio.

    dba_tab_modifications returns no rows with today's timestamp :)





    *From:* Julio Aguilar-Chang
    *Sent:* Monday, August 03, 2009 2:39 PM
    *To:* Jiang, Lu
    *Cc:* ORACLE-L
    *Subject:* Re: Find which table's data has been changed in last five
    hours.




    Try dba_tab_modifications. It has columns such as the table_owner,
    table_name, number of inserts/updates/deletes, and the timestamp.



    Jiang, Lu wrote:

    Hi DBA gurus,



    I need to flashback our EBS database to 10 hours ago, also hope the last
    five hours data can be recovered with export/import. Is there a way to find
    which table's/schema's data has been changed in last five hours, so that I
    could export the schemas/tables before flashback and import them after
    flashback?



    Thanks,

    Lu



    --



    *****************************************

    Julio Aguilar-Chang

    jachang_at_lanl.gov

    (505) 667-1004 - work

    *****************************************
    --
    http://www.freelists.org/webpage/oracle-l
  • Jiang, Lu at Aug 3, 2009 at 7:35 pm
    Thanks Roger. Run query against flashback_transaction_query takes a
    while, it returns over 12 hours transaction.

    Our problem is caused by an Oracle apps patch, now Oracle ask us to
    apply another patch to fix it. If this will not work then I would do the
    recovery.

    Thanks again for the help.





    From: Roger Xu
    Sent: Monday, August 03, 2009 2:56 PM
    To: Jiang, Lu
    Cc: Julio Aguilar-Chang; ORACLE-L
    Subject: Re: Find which table's data has been changed in last five
    hours.



    DBA_TAB_MODIFICATIONS describes modifications to all tables in the
    database that have been modified since the last time statistics were
    gathered on the tables. Try flashback_transaction_query ...

    On Mon, Aug 3, 2009 at 1:52 PM, Jiang, Lu wrote:

    Thanks Julio.

    dba_tab_modifications returns no rows with today's timestamp :)





    From: Julio Aguilar-Chang
    Sent: Monday, August 03, 2009 2:39 PM
    To: Jiang, Lu
    Cc: ORACLE-L
    Subject: Re: Find which table's data has been changed in last five
    hours.



    Try dba_tab_modifications. It has columns such as the table_owner,
    table_name, number of inserts/updates/deletes, and the timestamp.

    Jiang, Lu wrote:

    Hi DBA gurus,



    I need to flashback our EBS database to 10 hours ago, also hope the last
    five hours data can be recovered with export/import. Is there a way to
    find which table's/schema's data has been changed in last five hours, so
    that I could export the schemas/tables before flashback and import them
    after flashback?



    Thanks,

    Lu



    --

    *****************************************
    Julio Aguilar-Chang

    jachang_at_lanl.gov
    (505) 667-1004 - work
    *****************************************

    --
    http://www.freelists.org/webpage/oracle-l
  • Adam Musch at Aug 3, 2009 at 9:31 pm
    If the table is being monitored (ALTER TABLE .... MONITORING, which
    the default statistics job sets on your behalf) and one executes
    dbms_stats.flush_database_monitoring_info, then all the tables which
    appear in the
    *_TAB_MODIFICATIONS view have had DML or been truncated since the last
    time statistics were gathered. If one doesn't run
    DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO, the information in the
    *_TAB_MODIFICATIONS views can be stale.
    On Mon, Aug 3, 2009 at 1:52 PM, Jiang, Lu wrote:
    Thanks Julio.

    dba_tab_modifications returns no rows with today's timestamp :)





    From: Julio Aguilar-Chang
    Sent: Monday, August 03, 2009 2:39 PM

    To: Jiang, Lu
    Cc: ORACLE-L
    Subject: Re: Find which table's data has been changed in last five hours.



    Try dba_tab_modifications.  It has columns such as the table_owner,
    table_name, number of inserts/updates/deletes, and the timestamp.



    Jiang, Lu wrote:

    Hi DBA gurus,



    I need to flashback our EBS database to 10 hours ago, also hope the last
    five hours data can be recovered with export/import. Is there a way to find
    which table's/schema's data has been changed in last five hours, so that I
    could export the schemas/tables before flashback and import them after
    flashback?



    Thanks,

    Lu

    --



    *****************************************

    Julio Aguilar-Chang

    jachang_at_lanl.gov

    (505) 667-1004 - work

    *****************************************
    --
    Adam Musch
    ahmusch_at_gmail.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Jiang, Lu at Aug 4, 2009 at 2:57 pm
    Thanks Adam for the info. I will look into this. It seems that by default system flush table monitoring info everything 15 minutes automatically, but I cannot remember it clearly.

    Fortunately our issue has been fixed by a new patch, but it is always good to be well prepared for the worst case :)


    -----Original Message-----
    From: Adam Musch
    Sent: Monday, August 03, 2009 5:31 PM
    To: Jiang, Lu
    Cc: Julio Aguilar-Chang; ORACLE-L
    Subject: Re: Find which table's data has been changed in last five hours.

    If the table is being monitored (ALTER TABLE .... MONITORING, which
    the default statistics job sets on your behalf) and one executes
    dbms_stats.flush_database_monitoring_info, then all the tables which
    appear in the
    *_TAB_MODIFICATIONS view have had DML or been truncated since the last
    time statistics were gathered. If one doesn't run
    DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO, the information in the
    *_TAB_MODIFICATIONS views can be stale.
    On Mon, Aug 3, 2009 at 1:52 PM, Jiang, Lu wrote:
    Thanks Julio.

    dba_tab_modifications returns no rows with today's timestamp :)





    From: Julio Aguilar-Chang
    Sent: Monday, August 03, 2009 2:39 PM

    To: Jiang, Lu
    Cc: ORACLE-L
    Subject: Re: Find which table's data has been changed in last five hours.



    Try dba_tab_modifications.  It has columns such as the table_owner,
    table_name, number of inserts/updates/deletes, and the timestamp.



    Jiang, Lu wrote:

    Hi DBA gurus,



    I need to flashback our EBS database to 10 hours ago, also hope the last
    five hours data can be recovered with export/import. Is there a way to find
    which table's/schema's data has been changed in last five hours, so that I
    could export the schemas/tables before flashback and import them after
    flashback?



    Thanks,

    Lu

    --



    *****************************************

    Julio Aguilar-Chang

    jachang_at_lanl.gov

    (505) 667-1004 - work

    *****************************************
    --
    Adam Musch
    ahmusch_at_gmail.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Roger Xu at Aug 3, 2009 at 6:55 pm
    DBA_TAB_MODIFICATIONS describes modifications to all tables in the database
    that have been modified since the last time statistics were gathered on the
    tables. Try flashback_transaction_query ...
    On Mon, Aug 3, 2009 at 1:39 PM, Julio Aguilar-Chang wrote:


    Try dba_tab_modifications. It has columns such as the table_owner,
    table_name, number of inserts/updates/deletes, and the timestamp.



    Jiang, Lu wrote:

    Hi DBA gurus,



    I need to flashback our EBS database to 10 hours ago, also hope the last
    five hours data can be recovered with export/import. Is there a way to find
    which table's/schema's data has been changed in last five hours, so that I
    could export the schemas/tables before flashback and import them after
    flashback?



    Thanks,

    Lu


    --

    *****************************************
    Julio Aguilar-Changjachang_at_lanl.gov
    (505) 667-1004 - work
    *****************************************
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 3, '09 at 6:15p
activeAug 4, '09 at 2:57p
posts9
users3
websiteoracle.com

3 users in discussion

Jiang, Lu: 5 posts Roger Xu: 3 posts Adam Musch: 1 post

People

Translate

site design / logo © 2022 Grokbase