8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Flashback Database in Oracle Database 10g

The FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery, also known as a point-in-time recovery (PITR). In order to flashback the database you must have SYSDBA privilege and the flash recovery area must have been prepared in advance.

Enable Flashback Database
Flashback Database
Restore Points
ORA-38760

Enable Flashback Database

The FLASHBACK_ON column of the V$DATABASE view shows the current status of flashback database.

If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode.

conn / as sysdba
alter system set log_archive_dest_1='location=d:\oracle\oradata\DB10G\archive\' scope=spfile;
alter system set log_archive_format='ARC%S_%R.%T' scope=spfile;
shutdown immediate
startup mount
alter database archivelog;
alter database open;

Flashback must be enabled before any flashback operations are performed. In Oracle 11gR2, this process was simplified, allowing flashback database to be enabled/disabled without the need for a database restart. As a result, you could just issue the following.

CONN / AS SYSDBA
alter database flashback on;
--alter database flashback off;

For 10g and 11gR1 releases, the database needs to be restarted to achieve this, as follows.

conn / as sysdba
shutdown immediate
startup mount exclusive
alter database flashback on;
alter database open;

The flashback logs are placed in the fast recovery area, which is described here.

Flashback Database

With flashback enabled the database can be switched back to a previous point-in-time or SCN without the need for a manual incomplete recovery. In the following example a table is created, the database is then flashbacked to a time before the table was created.

Create a test table.

conn test/test

create table flashback_database_test (
  id  number(10)
);

Flashback the database to 5 minutes previous, to before the test table was created.

conn / as sysdba
shutdown immediate
startup mount exclusive
flashback database to timestamp sysdate-(1/24/12);
alter database open resetlogs;

Check that the test table is gone.

conn test/test

desc flashback_database_test

Some other variations of the flashback database command include the following.

flashback database to timestamp {my_date};
flashback database to before timestamp {my_date};
flashback database to scn {my_scn};
flashback database to before scn {my_scn};

The window of time that is available for flashback is determined by the DB_FLASHBACK_RETENTION_TARGET parameter. The maximum flashback can be determined by querying the V$FLASHBACK_DATABASE_LOG view. It is only possible to flashback to a point in time after flashback was enabled on the database and since the last resetlogs command.

Restore Points

In addition to using SCNs and timestamps, we can create restore points and guaranteed restore points. A restore point is just a text alias representing a SCN. A guaranteed restore point means the database doesn't delete any flashback logs between that point and the current time. You should delete all unnecessary guaranteed restore points.

create restore point before_changes;
create restore point before_changes guarantee flashback database;

Existing restore points can be displayed using the V$RESTORE_POINT view.

select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
BEFORE_CHANGES

SQL>

To flashback to this restore point, we would issue the following command.

flashback database to restore point before_changes;

Existing restore points can be dropped as follows.

drop restore point before_changes;

ORA-38760

On some occasions you will get ORA-38760 errors and the database won't start.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database


SQL>

This is what you need to do to fix it.

If you can, query the restore points from the V$RESTORE_POINT view.

select name from v$restore_point;

You can then drop the restore points, which will allow you to open the database.

drop restore point b4_reinstalls;

If flashback logs are missing, you may find the V$RESTORE_POINT view is not working.

SQL> select * from v$restore_point;
select * from v$restore_point
              *
ERROR at line 1:
ORA-38701: Flashback database log 1 seq 1 thread 1:
"/u01/app/oracle/fast_recovery_area/CDB/flashback/o1_mf_hg6h7dyn_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL>

You can get the restore point information in the following way.

conn / as sysdba
oradebug setmypid
alter session set events 'immediate trace name controlf level 9';
oradebug tracefile_name

The last command give you a trace file name.

/u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_6393.trc

Open that trace file and search for "RESTORE POINT RECORDS". You will see a section like this.

***************************************************************************
RESTORE POINT RECORDS
***************************************************************************
 (size = 212, compat size = 212, section max = 2048, section in-use = 1,
  last-recid= 1, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 237, numrecs = 2048)
RESTORE POINT #1:
 restore point name: B4_REINSTALLS guarantee flag: 1 incarnation: 2next record 0
 restore point scn: 0x0c45.bd1de417 06/12/2020 09:29:32

You now know the restore point name(s), so you can drop them.

drop restore point b4_reinstalls;

The database should now open normally.

alter database open;

Database altered.

SQL>

You can now decide what you want to do about flashback database. Turn it off or on as required.

alter database flashback off;
alter database flashback on;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.