Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    Austin
    Posts
    62

    Question Unanswered: Keeping users out for a backup

    Hello,

    I've recently been working on a backup script for DB2. I've tried a few different things and ended up with determining that DEACTIVE DATABASE after
    FORCE APPLICATIONS ALL would be a good way to keep users off of the database while performing a backup.

    I haven't had a good way to test if this would work well, I'd like to know if DEACTIVATE will actually keep users out of the database during the backup.

    I tried QUIESCE DATABASE, but apparently you can't perform a backup or restore, can't remember which, when in this mode. This is why I resorted to
    DEACTIVE DATABASE, but I'm unclear who can ACTIVATE or establish a connection once DEACTIVATED.

    Also, I'd like to know a good way to ensure all users have been booted off of the database using command line command.

    Any info. is much appreciated.

    Regards,

    Michael

  2. #2
    Join Date
    Nov 2004
    Posts
    374

    backup

    we always use quiesce database force for offline backup
    we never had a problem for backup/restore
    for online backup this is not needed....
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  3. #3
    Join Date
    Sep 2004
    Posts
    111

    Arrow

    Deactiate database is the best choice for an offline backup.
    Users will be thrown away with message "Database is in use" when they try to connect to the database at the time of offline database backup.

    -RJ

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Wouldn't it be less intrusive to revoke access followed by the force?

    One pit fall when using diactivate is you need to keep in mind that id that issues this command can not be connected to any db.

    Also, youa re talking about doing an ofline back ups, correct? Other wise users can be connected
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Jan 2004
    Location
    Austin
    Posts
    62

    Offline backup indeed

    Yes, it will be an offline backup. I'd just like to know the best way to guarantee that users will not be able to connect. My concern is the time between the kicking off users and gettting to start the backup. My concern is that a process setup to automatically connect will cause a problem.

    I'm trying to make this a hands off backup process.

    Regards,

    Mike

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I just looked up our old script when we were doing an offline backups and you do need to diactivate the db, however it wont keep users out. Users still can get connected until you actually start a back up.

    What you need to do is to revoke connect on the db followed by diactivating this db followed by forcing everyone off the system.

    Before you force everyone off it would be worth checking to make sure that you do not have any of your other processes still running i.e. runstats or loads. It is one thing killing someone elses processes, it is a shame killing your own
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    As an alternative, after forcing the existing connections you could just put up a firewall for the duration of backup to reject new remote connections.

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    BTW, are you on a EEE?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Sep 2003
    Posts
    237
    As I read somewhere, howabout cataloging a database with a USERALIAS and a DBAALIAS. When ready to take a backup, uncatalog USERALIAS; force applications; take the backup; then recatalog USERALIAS. As long as cold(offline) backup is active nobody can connect. So you can recatalog USERALIAS once backup is active.
    mota

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by dbamota
    As I read somewhere, howabout cataloging a database with a USERALIAS and a DBAALIAS. When ready to take a backup, uncatalog USERALIAS; force applications; take the backup; then recatalog USERALIAS. As long as cold(offline) backup is active nobody can connect. So you can recatalog USERALIAS once backup is active.
    Good point, but what do you do if they know the real name
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Sep 2003
    Posts
    237
    follow the standard operating procedure; deal with them with "extreme prejudice"
    mota

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •