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]
MRalwasser
  • 15,605
  • 15
  • 101
  • 147
  • 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. – Jon Heller Jun 13 '19 at 04: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? – Mark Stewart Jun 13 '19 at 21:45
  • Can you describe the scenario that necessitates frequently flashing back the database? – cdub Jun 13 '19 at 21:58
  • @JonHeller I've updated the questions with steps and timing. – MRalwasser Jun 14 '19 at 11:58
  • @cdub automated testing – MRalwasser Jun 14 '19 at 11:58
  • Are you manually typing or copying/pasting these steps? If yes, then you could save some time by replacing steps 2-6 with: `STARTUP FORCE MOUNT`. – cdub Jun 14 '19 at 19:55
  • @cdub these steps are invoked automatically by a java application using JDBC (the times are nearly identical when using sqlplus) – MRalwasser Jun 15 '19 at 10:35
  • Have you considered alternating the automated testing between two copies of the same database? So, when when db #1 is going through steps #1-9, the automated tests are running against db #2. Similarly, when db#2 is going through the same steps, the tests are running against db #1. Additional database copies could be added if needed. This approach doesn't scale well, but could work for a small database like yours. – cdub Jun 17 '19 at 23:51
  • @cdub: This what I'm doing. However, this doesn't help for single testcase runs with a flashback at begin (and it still may require more cpu/io time in total for parallel runs) – MRalwasser Jun 18 '19 at 09:26
  • 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. – Hash Jun 18 '19 at 09:46

3 Answers3

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.

Brian Fitzgerald
  • 634
  • 7
  • 14
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.

Mauricio
  • 117
  • 4
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.

Ron Warshawsky
  • 314
  • 2
  • 11
  • 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. – MRalwasser Sep 06 '21 at 23:56