9

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]
Improve this question
10
  • 2
    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. Commented Jun 13, 2019 at 4:11
  • 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? Commented Jun 13, 2019 at 21:45
  • Can you describe the scenario that necessitates frequently flashing back the database? Commented Jun 13, 2019 at 21:58
  • @JonHeller I've updated the questions with steps and timing. Commented Jun 14, 2019 at 11:58
  • 2
    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. Commented Jun 18, 2019 at 9:46

3 Answers 3

Reset to default
1

  1. startup

    a. Implement hugepages

    b. Set statistics_level = basic

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

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

The complete database resides within a ramdisk. As mentioned in the comments, the operation is mainly cpu bound, as the I/O is an almost no-cost operation on a ramdisk. BTW, Oracle 19 increased time slightly on the same machine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.