Tune Oracle Database for faster startup (flashback)

Question

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]

Answer 1

You could use a storage snapshot. In that case take a snapshot at t0. Do your operations. Once you are ready to rollback stop the database and mount your t0 snapshot as your data files. This should take you the same time than starting your database at t0.

Answer 2

You are spending most of the response time on I/O related operations:

Access control files: ALTER DATABASE MOUNT [4073 ms]

Reset redo logs: ALTER DATABASE OPEN RESETLOGS [1652 ms]

Try to put control files and redo log files on very fast storage, like NVMe based SSD.

Also, how many redo log files and log file groups do you have? - reducing them would help as well - if you can allow it.

Answer 3

3. startup

   a. Implement hugepages

   b. Set statistics_level = basic

4. 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.

5. 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.