-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|