Last day, while trying to flashback a test database (12.2) to a restore point, I got the following errors:
SQL> startup mount; SQL> flashback database to restore point LAST_RESTORE_POINT;
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 37583880134 to SCN 37583927400
ORA-38761: redo log sequence 9 in thread 2, incarnation 2 could not be accessed
I know that somehow, the flashback technology needs the archivelogs as well to recover to desire point in time. And if some archivelogs are not available at the time of the flashback, that kind of error might arise. I say “it might”, but to be honest, I am not sure because the Oracle documentation about this error is not that clear. The suggested solution in this case is to use RMAN:
$> rman target / catalog <catalog_user>/<password>@<TNS_NAME> RMAN> flashback database to restore point LAST_RESTORE_POINT;
But, it still gets the error:
Starting flashback at 08-OCT-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=14 instance=perf1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=318 instance=perf1 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=621 instance=perf1 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=927 instance=perf1 device type=DISK allocated channel: ORA_DISK_5 channel ORA_DISK_5: SID=1229 instance=perf1 device type=DISK allocated channel: ORA_DISK_6 channel ORA_DISK_6: SID=1535 instance=perf1 device type=DISK allocated channel: ORA_DISK_7 channel ORA_DISK_7: SID=15 instance=perf1 device type=DISK allocated channel: ORA_DISK_8 channel ORA_DISK_8: SID=319 instance=perf1 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of flashback command at 10/08/2019 10:26:06 RMAN-06823: flashback command failed due to low value of DB_FLASHBACK_RETENTION_TARGET
The MOS search about the RMAN-06823 error is far from satisfactory…
RMAN-06823: flashback command failed due to low value of DB_FLASHBACK_RETENTION_TARGET
Cause: An attempt to flashback failed due to the NULL value for OLDEST_FLASHBACK_SCN or OLDEST_FLASHBACK_TIME in V$FLASHBACK_DATABASE_LOG. This failure can happen when the initial creation of standby is not consistent or when there is not enough space in FRA for 1 hours worth of flashback data.
Action: Increase the value for DB_FLASHBACK_RETENTION_TARGET to a higher value and check the value of OLDEST_FLASHBACK_SCN and OLDEST_FLASHBACK_TIME in V$FLASHBACK_DATABASE_LOG.
Ok, let’s do it then:
SQL> alter system set db_flashback_retention_target=1000000000;
I also checked the FRA and found out that it was full! This might also be the reason of the error:
SQL> sho parameter db_recov
NAME TYPE VALUE db_recovery_file_dest string ….FRA….. db_recovery_file_dest_size big integer 950G
$> cd / $> du -sh 951G .
SQL> alter system set db_recovery_file_dest_size=1200G sid='*'; SQL> shu immediate; SQL> startup;
RMAN> list incarnation; starting full resync of recovery catalog full resync complete
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time 1 20 …. xxxxx xxxxx jjjj 1 2 …. xxxxx xxxxx jjjj 436 437 …. xxxxx xxxxx jjjj
RMAN> flashback database to restore point LAST_RESTORE_POINT;
Starting flashback at 08-OCT-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1533 instance=perf1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=14 instance=perf1 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=316 instance=perf1 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=620 instance=perf1 device type=DISK allocated channel: ORA_DISK_5 channel ORA_DISK_5: SID=926 instance=perf1 device type=DISK allocated channel: ORA_DISK_6 channel ORA_DISK_6: SID=1229 instance=perf1 device type=DISK allocated channel: ORA_DISK_7 channel ORA_DISK_7: SID=1534 instance=perf1 device type=DISK allocated channel: ORA_DISK_8 channel ORA_DISK_8: SID=15 instance=perf1 device type=DISK starting media recovery channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=27 channel ORA_DISK_1: restoring archived log … .. . Finished flashback at 08-OCT-19
SQL> alter database open resetlogs;