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]

7 seconds sounds incredibly fast - does that time include all of the steps? It might help to list every step and how long it takes. For example, on my small system: shutdown immediate (29 seconds), startup mount (28 seconds), flashback database to timestamp systimestamp - interval '5' minute; (2 seconds), alter database open resetlogs (5 seconds). For a total of 64 seconds, not counting time to type the commands. — Jon Heller
Have you considered the possibility of taking a different approach to whatever you are doing that would require flashing back the entire database; such as doing several flashback table commands on specific tables which does not require bouncing the database? — Mark Stewart
Can you describe the scenario that necessitates frequently flashing back the database? — cdub
@JonHeller I've updated the questions with steps and timing. — MRalwasser
This timing is really awsome I think. For automated testing we used in memory db-s on the java side (H2) for speed. If you really need it to be Oracle I think it could be a good alternative to have multiple schema, and reset multiple of those if necessary at the same time. You can run as well multiple oracle instances on the same machine if necessary and switch between those with listeners. I do not know any method that you can speed up the current system with. — Hash

Mauricio Mauricio · Accepted Answer · 2021-06-26T23:13:42

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.

Tune Oracle Database for faster startup (flashback)

2 Answers