DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Flashback database to date back and forth

  1. #1
    Join Date
    Mar 2018
    Posts
    2

    Flashback database to date back and forth

    Code:
     SELECT NAME, TIME FROM v$restore_point order by time;
    
    NAME                                     TIME
    ---------------------------------------- -----------------------------------
    RST_PNT_20180306_1135                    06-MAR-18 11.33.03.000000000 AM        Got flashbacked to this 6th Mar
    RST_PNT_20180308_0132                    08-MAR-18 01.32.54.000000000 AM
    RST_PNT_20180315_0332                    15-MAR-18 03.32.36.000000000 AM
    RST_PNT_20180315_2350                    15-MAR-18 11.49.11.000000000 PM       Supposed to flashback to this
    Now when I try to flash back the database back to 15th Mar 2018, I am getting error below;

    Code:
    SQL> flashback database to restore point RST_PNT_20180315_2350;
    flashback database to restore point RST_PNT_20180315_2350
    *
    ERROR at line 1:
    ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
    ORA-38762: redo logs needed for SCN 6076403732969 to SCN 6076403895681
    ORA-38761: redo log sequence 2 in thread 1, incarnation 88 could not be accessed
    When I tried using RMAN, got the error below;
    Code:
    RMAN> run {
    allocate CHANNEL dev_2  TYPE 'SBT_TAPE' format '%U' PARMS  'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8)';
    allocate CHANNEL dev_3  TYPE 'SBT_TAPE' format '%U' PARMS  'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8)';
    allocate CHANNEL dev_4  TYPE 'SBT_TAPE' format '%U' PARMS  'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8)';
    flashback database to restore point RST_PNT_20180315_2350;
    sql "alter database open resetlogs";
    release channel dev_2;
    release channel dev_3;
    release channel dev_4;
    }
    show all;
    2> 3> 4> 5> 6> 7> 8> 9> 10>
    
    allocated channel: dev_2
    channel dev_2: SID=1443 instance=DB920P1 device type=SBT_TAPE
    channel dev_2: Data Protector A.09.00/107
    
    allocated channel: dev_3
    channel dev_3: SID=1060 instance=DB920P1 device type=SBT_TAPE
    channel dev_3: Data Protector A.09.00/107
    
    allocated channel: dev_4
    channel dev_4: SID=1156 instance=DB920P1 device type=SBT_TAPE
    channel dev_4: Data Protector A.09.00/107
    
    Starting flashback at 19-MAR-18
    
    
    starting media recovery
    
    archived log for thread 1 with sequence 1 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_06/thread_1_seq_1.457.970054529
    archived log for thread 1 with sequence 4 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_08/thread_1_seq_4.9548.970190597
    archived log for thread 1 with sequence 5 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_08/thread_1_seq_5.5306.970190633
    archived log for thread 1 with sequence 6 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_08/thread_1_seq_6.7402.970190669
    archived log for thread 1 with sequence 7 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_08/thread_1_seq_7.8888.970190705
    archived log for thread 1 with sequence 8 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_08/thread_1_seq_8.7853.970191493
    archived log for thread 1 with sequence 4 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_15/thread_1_seq_4.10077.970819211
    archived log for thread 2 with sequence 2 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_15/thread_2_seq_2.4300.970875589
    archived log for thread 1 with sequence 1 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_15/thread_1_seq_1.10592.970875947
    archived log for thread 1 with sequence 2 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_15/thread_1_seq_2.9487.970875951
    archived log for thread 1 with sequence 3 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_15/thread_1_seq_3.3403.970875951
    archived log for thread 1 with sequence 4 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_16/thread_1_seq_4.8489.970905611
    archived log for thread 2 with sequence 1 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_15/thread_2_seq_1.9459.970875629
    archived log for thread 2 with sequence 2 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_15/thread_2_seq_2.1262.970875951
    archived log for thread 2 with sequence 3 is already on disk as file +DG_PERF_FLASH_01/DB920P/ARCHIVELOG/2018_03_16/thread_2_seq_3.568.970905611
    unable to find archived log
    archived log thread=1 sequence=1
    released channel: dev_2
    released channel: dev_3
    released channel: dev_4
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of flashback command at 03/19/2018 03:00:50
    RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 6076403732970
    Please advise if we can flashback the database to back date and flashback the database again to a forward date.

  2. #2
    Join Date
    Mar 2018
    Posts
    1
    Hi

    I am following some examples from ILT guide and I get this error while working with Oracle SQL: Is there any issue working with this code:

    SQL> CREATE TABLE times (
    2 ts TIMESTAMP
    3 , tsz TIMESTAMP WITH TIME ZONE
    4 , tlz TIMESTAMP WITH LOCAL TIME ZONE
    5 , oldtime DATE );

    SQL> INSERT INTO times VALUES
    2 ( '21-MAY-01 10:00:00.25 AM'
    3 , '21-MAY-01 10:00:00.25 AM CET'
    4 , '21-MAY-01 10:00:00.25 AM'
    5 , '21-MAY-01' );
    ( '21-MAY-01 10:00:00.25 AM'
    *
    ERROR at line 2:
    ORA-01830: date format picture ends before converting entire input string

    SQL> SELECT DBTIMEZONE, SESSIONTIMEZONE from dual;

    DBTIME SESSIONTIMEZONE
    ------ ---------------------------------------
    +02:00 Europe/London

    SQL> select * from nls_session_parameters;

    PARAMETER VALUE
    ------------------------------ -------------------------------
    NLS_LANGUAGE ENGLISH
    NLS_TERRITORY UNITED KINGDOM
    NLS_CURRENCY £
    NLS_ISO_CURRENCY UNITED KINGDOM
    NLS_NUMERIC_CHARACTERS .,
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT YYYY-MON-DD HH24:MI:SS
    NLS_DATE_LANGUAGE ENGLISH
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH24.MI.SSXFF
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
    NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
    NLS_DUAL_CURRENCY €
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE

    Similar error for me and followed asktom but couldnot resolve the issue.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    It would help if you have backups of the database, especially the archive logs. You could then restore any logs that are needed. You might need to
    catalog the backups that you have with the catalog script if the control file is older and can't see the backup of the archive logs that it needs.
    You probably can't flash to the point in time that you want to go to without a backup of the archive log files.

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