Menu
I'm using Oracle Database 11.2.
I have a scenario where I issue FLASHBACK DATABASE quite often.
It seems that a FLASHBACK DATABASE cycle does a reboot of the database instance which takes approx. 7 seconds on my setup.
Database is small (~ 1 GB tablespace), all files should be in I/O caches/buffers. Therefore I think that the bottleneck is not I/O based.
I'm looking for tuning advices in order to save user time and/or CPU time for doing a flashback.
UPDATE:
Flashback sequence (and timing of each step) is the following:
1. Get SYSDBA connection with prelim_auth=true [15 ms]
2. SHUTDOWN ABORT; [1034 ms]
3. STARTUP (unrestricted) [1241 ms]
4. Close SYSDBA connection [2 ms]
5. Get SYSDBA connection with prelim_auth=false [18 ms]
6. ALTER DATABASE MOUNT [4073 ms]
7. FLASHBACK DATABASE TO RESTORE POINT <restore_point_name> [306 ms]
8. ALTER DATABASE OPEN RESETLOGS [1652 ms]
9. CLOSE SYSDBA connection [2 ms]
476
The difference between the two is that guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available (assuming you have enough space in the flash recovery area).
We use the startup restrict command to open the database in restricted mode as seen in this example. SQL> startup restrict. You can take the database in and out of restricted mode with the alter database command as seen in this example: -- Put the database in restricted session mode.
Confirm that the database is in ARCHIVELOG mode, which is required for Flashback Database, and enable ARCHIVELOG mode if needed. For a RAC database, you must locate the FRA on ASM or a clustered file system.
startup
a. Implement hugepages
b. Set statistics_level = basic
mount
a. Use only one control file. Do not multiplex.
b. Minimize the number of data files and tempfiles. Use bigfile tablespaces.
c. Re-create your control file from a trace. Omit nonessential items.
resetlogs
a. Use only one logfile per redo log group. Do not multiplex.
b. Create only 2 redo log groups.
c. Minimize the size of each redo log.
These options would compromise reliability and manageability, though.
102
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
