This discussion is archived
12 Replies Latest reply: May 17, 2013 1:55 AM by 1009530 RSS

database recovery status

rlarios Newbie
Currently Being Moderated
Hi:


Yesterday night my database brokedown. it had 20 GB used on memory at crash time.

I started database and it took more than 20 minutes on load database to memory... but I could not see how fast or a rate it was recovering to memory...

Is there any way to monitor the rate which database is recovering?
Is thre any way to accelerate this operation?

Regards.
  • 1. Re: database recovery status
    ChrisJenkins Guru
    Currently Being Moderated
    Status messages relating to the progress of the recovery process are logged in the TimesTen daemon log - ttmesg.log in <tt_install_dir>/info.

    Chris
  • 2. Re: database recovery status
    jspalmer Journeyer
    Currently Being Moderated
    You may also be able to use the RecoveryThreads DSN attribute to speed up recovery times:

    http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21643/attribute.htm#BABIDEDA
  • 3. Re: database recovery status
    rlarios Newbie
    Currently Being Moderated
    Hi Jspalmer:

    Let me show you my dsn configuration in order to identify if increasing recovery threads parameter could help.

    [SnapShotDSN]
    Driver=/home/sentratt/TimesTen/SENTRA_TT/lib/libtten.so
    DataStore =/TT_data02/Sentra/SnapShotDSN
    LogDir=/TT_logs02/Logs
    PermSize=36864
    TempSize=3072
    AUTHENTICATE=0
    OraclePWD=snapshot_admin
    OracleID=BMVGROUP
    SMPOptLevel=1
    logbuffsize=131072
    logFileSize=128
    RecoveryThreads=8
    MemoryLock=4
    Connections=1000
    DatabaseCharacterSet=WE8MSWIN1252
    #DatabaseCharacterSet=WE8ISO8859P1

    Regards.
  • 4. Re: database recovery status
    rlarios Newbie
    Currently Being Moderated
    Actually we have 12 processors, so if I set recovery_threads to 24 it could be posible I got better perfornance at recovery from crash.

    regards.
  • 5. Re: database recovery status
    jimtong - oracle Explorer
    Currently Being Moderated
    Hi,

    Our Database Reference documentation explains Recovery Threads well:

    The RecoveryThreads attribute determines the number of threads used to rebuild indexes during recovery.

    If RecoveryThreads=1, during recovery, indexes that must be rebuilt are done serially. If you have enough processors available to work on index rebuilds on your computer, setting this attribute to a number greater than 1 can improve recovery performance. The performance improvement occurs only if different processors can work on different indexes. There is no parallelism in index rebuild within the same index.

    The value of RecoveryThreads can be any value up to the number of CPUs available on your system.


    Jim
  • 6. Re: database recovery status
    rlarios Newbie
    Currently Being Moderated
    Jim:

    I think I understood this point, but my database is 36 GB declared, it is using 22GB, I set recovery_thread=8 and I got close to 20 minutes in order to load 22GB to memory.

    It means recovery_thread=8 I will get a rate on 1 GB per minute to load to memory...
    so if I set recovery_thread=16 I must get 2GB per minute ?

    The question is, Is there any way to load database to memory could be faster than 1 GB per minute?

    Regards.
  • 7. Re: database recovery status
    ChrisJenkins Guru
    Currently Being Moderated
    Database recovery in TimesTen consists of several steps:

    1. Read the most recent checkpoint file from disk and load it into the database shared memory segment. This is primarily an I/O bound activity. The time taken will depend on the size of the checkpoint file and the speed of your storage subsystem. This is a sequential activity (not parallelised in any way)

    2. Replay all pending transactions from the transaction logs. This is also a sequential activity. This step is both CPU and I/O intensive. The time taken depends on how many pending transactions need to be replayed.

    3. When the end of the log stream is reached, rollback any open transactions. This step is usually short but sometimes it may take significant time (e.g. if it has to roll back an uncomiitted update of 1M rows). Same comments apply as for (2).

    4. Any indexes that were modified during steps 2 and 3 are dropped and re-built. This is mainly a CPU intensive operation. The time taken depends on the number of indexes to be rebuilt, the size of the underlying tables, the setting of the RecoveryThreads parameter and the number of CPUs in the system.

    For startup after a clean shutdown steps 2, 3 and 4 are effectively no-ops and take virtually no time.

    So, modifying RecoveryThreads can only help with step (4) and only in the case where recovery is needed. Whether it will actually help depends on the factors I have listed above. To spped up step (1) you need to use faster storage. To speed up (2) you need faster CPUs and maybe faster storage.

    Chris
  • 8. Re: database recovery status
    rlarios Newbie
    Currently Being Moderated
    Chirs:

    Thanks for this data, I will talk with sysadmin in order to get technical information and check on disk.

    I am very worried because yesterday the server where my database is running brokedown, so to restart server took close to 20 minutes adding to load database to memory 20 more minutes, I lost close to 40 minutes on production.

    I will review all external factors which could affect my database load.

    regards.
  • 9. Re: database recovery status
    slaw Explorer
    Currently Being Moderated
    +>>I lost close to 40 minutes on production.+

    Have you considered setting up a standby database for HA?

    Simon
  • 10. Re: database recovery status
    rlarios Newbie
    Currently Being Moderated
    Hi Simon:

    It is nice to know about you....

    thanks for recomendation, we are in process to implement it.

    About Chris recomendation, I installed timesten in a diferent machine with a new Linux version (well actually we have Linux 5.4 in production), I tested on Linux 5.7

    Those are my metrics:

    According to logs after my server crash and my database recover:

    Timesten 7.0.5/ Linux 5.4

    Checkpoint file read status: 427.5 mb read in 10 sec (42.8 mb/sec); 36436.5 mb remain, estimate completion in 852 sec
    Checkpoint file read status: 810.2 mb read in 20 sec (40.5 mb/sec); 36053.7 mb remain, estimate completion in 889 sec
    Checkpoint file read status: 1262.0 mb read in 30 sec (42.1 mb/sec); 35602.0 mb remain, estimate completion in 846 sec
    Checkpoint file read status: 1753.8 mb read in 40 sec (43.8 mb/sec); 35110.2 mb remain, estimate completion in 800 sec
    Checkpoint file read status: 2262.0 mb read in 50 sec (45.2 mb/sec); 34602.0 mb remain, estimate completion in 764 sec
    Checkpoint file read status: 2761.8 mb read in 60 sec (46.0 mb/sec); 34102.2 mb remain, estimate completion in 740 sec

    fiber channel size from disk ( 2GB )

    We tested on Timesten 7.0.5/Linux 5.7 (Simulating a server crash)


    2013-05-15 17:32:35.83 Info: : 9991: 9994/0xf4b2010: Reading checkpoint file 0; data segment = 18528.4 mb (note: may be less than permsize)
    2013-05-15 17:32:45.00 Info: : 9991: 9994/0xf4b2010: Checkpoint file read status: 2133.5 mb read in 10 sec (213.3 mb/sec); 16394.9 mb remain, estimate completion in 76 sec
    2013-05-15 17:32:55.00 Info: : 9991: 9994/0xf4b2010: Checkpoint file read status: 4354.2 mb read in 20 sec (217.7 mb/sec); 14174.2 mb remain, estimate completion in 65 sec
    2013-05-15 17:33:05.00 Info: : 9991: 9994/0xf4b2010: Checkpoint file read status: 6683.2 mb read in 30 sec (222.8 mb/sec); 11845.2 mb remain, estimate completion in 53 sec
    2013-05-15 17:33:15.00 Info: : 9991: 9994/0xf4b2010: Checkpoint file read status: 8893.8 mb read in 40 sec (222.3 mb/sec); 9634.7 mb remain, estimate completion in 43 sec
    2013-05-15 17:33:25.00 Info: : 9991: 9994/0xf4b2010: Checkpoint file read status: 11425.2 mb read in 50 sec (228.5 mb/sec); 7103.2 mb remain, estimate completion in 31 sec
    2013-05-15 17:33:35.00 Info: : 9991: 9994/0xf4b2010: Checkpoint file read status: 13626.5 mb read in 60 sec (227.1 mb/sec); 4901.9 mb remain, estimate completion in 21 sec
    2013-05-15 17:33:45.00 Info: : 9991: 9994/0xf4b2010: Checkpoint file read status: 15854.0 mb read in 70 sec (226.5 mb/sec); 2674.4 mb remain, estimate completion in 11 sec
    2013-05-15 17:33:55.00 Info: : 9991: 9994/0xf4b2010: Checkpoint file read status: 18188.8 mb read in 80 sec (227.4 mb/sec); 339.7 mb remain, estimate completion in 1 sec

    fiber channel size from disk ( 8GB )

    This shows a considerable improving on performance.

    Regards.
  • 11. Re: database recovery status
    1009530 Newbie
    Currently Being Moderated
    Hi,
    It seems that your database is corrupted and you need database recovery software. I have used [http://www.recoverdatatools.com/oracle-recovery.html] from website recoverdatatools.com/oracle-recovery.html to recover corrupted database. They provide free download sample also.
  • 12. Re: database recovery status
    1009530 Newbie
    Currently Being Moderated
    Hi,
    It seems that your database is corrupted and you need database recovery software. You should use [Oracle database recovery software|http://www.recoverdatatools.com/oracle-recovery.html] to recover corrupted database. They provide free download sample also.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points