Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Can we track instance ontape (or for that matter onbar too) restores?

  • 1.  Can we track instance ontape (or for that matter onbar too) restores?

    Posted Fri February 28, 2020 10:06 AM

    Greetings and salutations...
    I got these instances that need to be restored every night for regression testing, which got me thinking...

    Is there a way to check if the instance has been restored from backup? and if it did, when?

    I can't remember ever seen that info or if it is even possible. Looking around Google I've found that MS SQLServer does track restores.
    Thanks in advance!
    Cheers!

    Ramon 



  • 2.  RE: Can we track instance ontape (or for that matter onbar too) restores?

    Posted Fri February 28, 2020 05:51 PM

    Ramon,

     

     

    after an ontape restore is complete a "Physical Restore of rootdbs .....  Completed" msg is posted in the MSG log.  So you can grep the file for that msg.

     

    also, if you are restoring  an ontape image  time  oncheck -pr  | grep "Real Time Archive Began" will reveal the date that particular archive was taken that was used to restore the instance (providing you do not take another level 0 archive after the restore.).  For onbar the $INFORMIXDIR/tmp/bar_act.log should have messages  indicating the time and date  each dbspace was restored along with other pertinent information.

     

    Mark

     

     

     

     






  • 3.  RE: Can we track instance ontape (or for that matter onbar too) restores?

    Posted Sun March 01, 2020 08:01 AM
    What you cannot know from all of that though is the point to which the logs were rolled forward. For  an onbar restore the onbar log should have that info, For ontape however I am not sure that the message log records that. Haven't done a restore in a while

    ------------------------------
    Art Kagel
    ------------------------------



  • 4.  RE: Can we track instance ontape (or for that matter onbar too) restores?

    Posted Sun March 01, 2020 09:12 PM
    Here's a simple test I did.
    As a result of progressing to roll forward with ontape, the following message is displayed in the online message log.

    In the rollforward recovery phase, the keyword 'Rollforward' is not displayed in the message log, but a message of the form 'Logical Log xxx Complete, timestamp ...' appears.

    --- ontape restore output
    
    /work2/INFORMIX/1170FC8W1/backups]ontape -r
    Restore will use level 0 archive file /work2/INFORMIX/1170FC8W1/backups/host01_4_L0. Press Return to continue ...
    
    
    Archive Tape Information
    
    Tape type:      Archive Backup Tape
    Online version: IBM Informix Dynamic Server Version 11.70.FC8W1
    Archive date:   Mon Mar  2 09:13:39 2020
    User id:        informix
    Terminal id:    /dev/pts/2
    Archive level:  0
    Tape device:    /work2/INFORMIX/1170FC8W1/backups/
    Tape blocksize (in k): 32
    Tape size (in k): system defined for directory
    Tape number in series: 1
    ...
    
    Continue restore? (y/n)y
    Do you want to back up the logs? (y/n)y
    File created: /work2/INFORMIX/1170FC8W1/backups/host01_4_Log0000000250
    File created: /work2/INFORMIX/1170FC8W1/backups/host01_4_Log0000000251
    File created: /work2/INFORMIX/1170FC8W1/backups/host01_4_Log0000000252
    File created: /work2/INFORMIX/1170FC8W1/backups/host01_4_Log0000000253
    File created: /work2/INFORMIX/1170FC8W1/backups/host01_4_Log0000000254
    File created: /work2/INFORMIX/1170FC8W1/backups/host01_4_Log0000000255
    Log salvage is complete, continuing restore of archive.
    Restore a level 1 archive (y/n) n
    Do you want to restore log tapes? (y/n)y
    
    Roll forward should start with log number 252
    Restore will use log backup file /work2/INFORMIX/1170FC8W1/backups/host01_4_Log0000000252. Press Return to continue ...
    
    Rollforward log file /work2/INFORMIX/1170FC8W1/backups/host01_4_Log0000000252 ...
    Rollforward log file /work2/INFORMIX/1170FC8W1/backups/host01_4_Log0000000253 ...
    Rollforward log file /work2/INFORMIX/1170FC8W1/backups/host01_4_Log0000000254 ...
    Rollforward log file /work2/INFORMIX/1170FC8W1/backups/host01_4_Log0000000255 ...
    
    Program over.
    
    
    
    --- online log messages
    
    09:15:28  Physical Restore of rootdbs, physdbs, logdbs, datadbs, sbspace started.
    
    ...
    
    09:15:33  Physical Restore of rootdbs, physdbs, logdbs, datadbs, sbspace Completed.
    09:15:33  Checkpoint Completed:  duration was 0 seconds.
    09:15:33  Mon Mar  2 - loguniq 252, logpos 0x7c54, timestamp: 0x172e764 Interval: 20242
    
    09:15:33  Maximum server connections 0
    09:15:50  Logical Recovery Started.
    09:15:50  10 recovery worker threads will be started.
    09:15:50  Checkpoint Completed:  duration was 0 seconds.
    09:15:50  Mon Mar  2 - loguniq 252, logpos 0x7c54, timestamp: 0x172e76b Interval: 20242
    
    09:15:50  Maximum server connections 0
    09:15:50  Start Logical Recovery - Start Log 252, End Log ?
    09:15:50  Starting Log Position - 252 0x7c54
    09:15:50  Clearing the physical and logical logs has started
    09:15:52  Cleared 107 MB of the physical and logical logs in 2 seconds
    09:16:51  Logical Log 252 Complete, timestamp: 0x1734bbe.
    09:16:52  Logical Log 253 Complete, timestamp: 0x1734bbe.
    09:16:53  Logical Log 254 Complete, timestamp: 0x1734cf0.
    
    ...
    
    09:16:55  Logical Recovery Complete.
              1347 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks
    
    09:16:55  Logical Recovery Complete.
    09:16:55  Quiescent Mode​


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 5.  RE: Can we track instance ontape (or for that matter onbar too) restores?

    Posted Mon March 02, 2020 07:55 AM

    I think the question is more is this information in sysmaster anywhere.

     

    Cheers

    Paul

     






  • 6.  RE: Can we track instance ontape (or for that matter onbar too) restores?

    Posted Fri February 28, 2020 11:19 PM
    Hi Ramon,
    You can use the oncheck command or query the sysmaster: sysdbstab table to view the most recent restore of the current instance.
    As Mark mentioned, it may be helpful to refer to the log files specified in onconfig's MSGPATH and BAR_ACT_LOG.

    Below is the result of running it on my test server.

    /work2/INFORMIX/1210FC13/skjeong]more /work2/INFORMIX/1210FC13/ol_informix1210_3.log
    ...
    
    Tue Dec 24 01:01:20 2019
    
    ...
    
    17:05:41  Level 0 Archive started on rootdbs, plog, llog, datadbs1, datadbs2, datadbs3, data8dbs1, data8dbs2, data8dbs3, dbs1, sbspace1
    17:05:54  Archive on rootdbs, plog, llog, datadbs1, datadbs2, datadbs3, data8dbs1, data8dbs2, data8dbs3, dbs1, sbspace1 Completed.
    17:06:24  Checkpoint Completed:  duration was 0 seconds.
    17:06:24  Tue Dec 24 - loguniq 900, logpos 0x183018, timestamp: 0x18066cc Interval: 250
    
    ...
    
    Sat Feb 29 13:11:29 2020
    
    ...
    
    13:11:38  Physical Restore of rootdbs, plog, llog, datadbs1, datadbs2, datadbs3, data8dbs1, data8dbs2, data8dbs3, dbs1, sbspace1 started.
    
    ...
    
    13:12:17  Physical Restore of rootdbs, plog, llog, datadbs1, datadbs2, datadbs3, data8dbs1, data8dbs2, data8dbs3, dbs1, sbspace1 Completed.
    
    
    /work2/INFORMIX/1210FC13/skjeong]oncheck -pr | egrep 'name|restore'
        DBspace name                   rootdbs
        Time of last physical restore  12/24/2019 17:05:40
        DBspace name                   plog
        Time of last physical restore  12/24/2019 17:05:40
        DBspace name                   llog
        Time of last physical restore  12/24/2019 17:05:40
        DBspace name                   datadbs1
        Time of last physical restore  12/24/2019 17:05:40
        DBspace name                   datadbs2
        Time of last physical restore  12/24/2019 17:05:40
        DBspace name                   datadbs3
        Time of last physical restore  12/24/2019 17:05:40
        DBspace name                   data8dbs1
        Time of last physical restore  12/24/2019 17:05:40
        DBspace name                   data8dbs2
        Time of last physical restore  12/24/2019 17:05:40
        DBspace name                   data8dbs3
        Time of last physical restore  12/24/2019 17:05:40
        DBspace name                   tmpdbspace
        DBspace name                   sbspace1
        Time of last physical restore  12/24/2019 17:05:40
        DBspace name                   tmpsbspace
        DBspace name                   dbs1
        Time of last physical restore  12/24/2019 17:05:40
        DBspace name                   blobdbs1
    /work2/INFORMIX/1210FC13/skjeong]echo "select name[1,12], dbinfo('utc_to_datetime',rest_time) last_rest_time,logid from sysdbstab where rest_time <> 0" | dbaccess sysmaster
    
    Database selected.
    
    
    
    name         last_rest_time            logid
    
    rootdbs      2019-12-24 17:05:40         900
    plog         2019-12-24 17:05:40         900
    llog         2019-12-24 17:05:40         900
    datadbs1     2019-12-24 17:05:40         900
    datadbs2     2019-12-24 17:05:40         900
    datadbs3     2019-12-24 17:05:40         900
    data8dbs1    2019-12-24 17:05:40         900
    data8dbs2    2019-12-24 17:05:40         900
    data8dbs3    2019-12-24 17:05:40         900
    sbspace1     2019-12-24 17:05:40         900
    dbs1         2019-12-24 17:05:40         900
    
    11 row(s) retrieved.
    
    
    
    Database closed.​


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------