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.
Related articles.
- Flashback New Features and Enhancements in Oracle Database 10g
- Multitenant : Flashback of a Container Database (CDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : Flashback Pluggable Database (PDB) in Oracle Database 12c Release 2 (12.2)
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:
- Enabling Flashback Database
- Flashback New Features and Enhancements in Oracle Database 10g
- Multitenant : Flashback of a Container Database (CDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : Flashback Pluggable Database (PDB) in Oracle Database 12c Release 2 (12.2)
- STARTUP Database failed ORA-38760 to turn on Flashback Database (Doc ID 1554596.1)
Hope this helps. Regards Tim...