IBM Support

Offline database backup and restore using the ESS FlashCopy product on AIX platforms

Question & Answer


Question

How do you take a backup and restore an offline database using the IBM Enterprise Storage Server (ESS) FlashCopy product ?

Cause

There is documentation available on how to perform online backup and restore operations on databases using the ESS FlashCopy product, but there is no documentation available on how to perform offline backup and restore operations using the ESS FlashCopy software.

Answer

This document describes how to take a backup and restore an offline database in the following two scenarios:

1) An offline backup and restore to the same server using the ESS FlashCopy software
2) An offline backup and restore to a different server using the ESS FlashCopy software

Notes:

A) Make sure all the database file systems are backed up. If multiple databases are sharing the same set of file systems, then the backup and restore of the file system for one particular database might cause other databases to become inconsistent. In this case, all databases must be suspended, backed up and then restored at the same time to prevent inconsistency from occurring.

B) You can apply newer transactions to the restored database through a rollforward recovery operation with new logs copied over from the primary database server. Make sure you do not add any transactions to the restored database before you perform the rollforward operation.

C) For offline backup using the ESS Flashcopy software, you must copy the following paths and files into ESS:

  • DMS Tablespace containers
    To determine which containers to copy:
    1. Issue the command: LIST TABLESPACES.
    2. Make note of the table space IDs.
    3. For each table space ID, issue the command: LIST TABLESPACE CONTAINERS FOR <id>.
       
  • SMS Tablespace directory
    To determine which containers to copy, issue the same commands as described above.
     
  • Database directory path
    This path can be determined as follows:
    1. Connect to the database using the command: CONNECT TO <database name> .
    2. Issue the command: LIST ACTIVE DATABASES.
    3. Refer to the "Database path" values in the output.
       
  • System database directory (sqldbdir)
    1. You can get this information by replacing the string "SQLnnnnn" of the above "database directory path" with "sqldbdir".
       
  • AutoStorage Path
    1. "Automatic storage path" of database snapshot output.
       
  • If you use Version 9.1 of the DB2 product , you can get the values for database paths required for tasks such as split mirror backups except "archive log path", with the following command.
    - db2 "select * from SYSIBMADM.DBPATHS"
     
  • Archive log path
    This path is found in the LOGARCHMETH1 and LOGARCHMETH2 database configuration parameter which can be obtained using the get db cfg or db2pd command, or in your user exit program.
     

Note:

   The active log path which is represented with "Path to log files" or "MIRRORLOGPATH" in the database configuration parameter or "LOGPATH" in SYSIBMADM.DBPATHS should not be included in the data to be copied because any transaction log records are not remained there during the Db2 instance is not activated and transaction log files to be archived may be remained, which should not be copied back.

  And the archive log path should be copied into the other image rather than the database backed up image because the latest archive log path should not be overwrite when the database image is copied back.


D) You need to backup user programs such as applications, stored procedures, user-defined functions (UDFs) and userexit, because you will be using the same programs after restoring.

E) When you restore images to a different server, you must restore them into an instance that has the same name both on the original server and the target server. Backing up the database is recommended after the restore operation.
Of course, the mount points on the target server must be identical otherwise the container paths might not work, and the same permission settings are required on all files in the backed up file system between two servers.


Procedures:

1) An offline backup and restore to the same server using the ESS FlashCopy software

 

1-1) Take an offline backup using the ESS FlashCopy software

 

[on Server A]

1-1-1) Stop the database manager by issuing the command: db2stop force.
1-1-2) Unmount the file systems using the FlashCopy software.
For example, you can issue the command # umount /ess1, where /ess1 is a file system name which was once created on ESS logical volume.
1-1-3) Copy image for backing up database by using the FlashCopy software.
1-1-4) Mount the file systems.
For example, issue the command # mount /ess1, where /ess1 is a file system name which was unmounted.
1-1-5) Start the database manager by issuing the command: db2start.

1-2) Restore to the same server (offline copy)

[on Server A]

1-2-1) Stop the database manager by issuing the command: db2stop force
1-2-2) Unmount the file systems which you plan on targeting for the following the FlashCopy and exportvg commands.
For example, issue the command # umount /ess1, # exportvg vgess1 , where vgess1 is a volume group name which was once created on ESS disks.
1-2-3) Copy back the file systems for restoring the database by using the FlashCopy software.
1-2-4) Issue the commands Importvg and varyonvg.
For example, run the command # importvg -y'vgess1' hdisk2 where vgess1 is a volume group name which was once exportvg rom hdisk2. , # varyonvg vgess1.
1-2-5) Mount the same mount point name as the original one.
For example, you can run the command # mount /ess1, where /ess1 is a file system name which was unmounted.
1-2-6) Restore user programs which are backed up on Note D) and check them.
1-2-7) Start the database manager by issuing the command: db2start
1-2-8) Connect to the database by using the command: db2 connect to <dbname>.

1-3) Restore to the same server (offline copy and rollforward)

[on Sever A]

1-3-1) Stop the database manager by issuing the command: db2stop
1-3-2) Save newer logs which are updated after being copied in Step 1-1) to other directories than FlashCopy mount points, so as not to be overwritten.
1-3-3) Umount the file systems which you plan on tagetting for the following the FlashCopy and exportvm commands.
1-3-4) Copy back image for restoring database by the FlashCopy software.
1-3-5) Issue the commands: Importvg and varyonvg.
1-3-6) Mount the same mount point name as the original one.
1-3-7) Make sure if the latest archive logs exist or restore the archive logs which were backed up in the steps above.
1-3-8) Put your database in rollforward pending state by issuing: db2rfpen on <dbname> .
1-3-9) Restore user programs which are backed up on Note D) and check them.
1-3-10) Start the database manager by issuing the command: db2start .
1-3-11) Perform rollforward recovery operation by using the command: db2 rollforward db <dbname> to end of logs.
1-3-12) Issue the command: db2 rollforward db <dbname> stop .
1-3-13) Connect to the database by using the command: db2 connect to <dbname> .


2) An offline backup and restore to a different server using the ESS FlashCopy software

 

2-0) Setup on another server
- You must set up DB2 with the same db2level and instance name as the original server.
- You also need to make sure the mount points are identical, otherwise container paths will be invalid on the target server.

 

2-1) Prepare the filesystem on another server for the FlashCopy software

[on Server B]

2-1-1) Stop the database manager by issuing the command: db2stop force.
2-1-2) Umount the file systems which you plan on targeting using the FlashCopy software.
2-1-3) Issue the command: exportvg.

2-2) Backup/restore to another server (offline copy)

[on Server A]

2-2-A1) Stop the database manager by issuing the command: db2stop force.
2-2-A2) Umount the file systems which you plan to target using the FlashCopy software.
2-2-A3) Copy image for backing up database by using the FlashCopy software.
2-2-A4) Mount the file systems.
2-2-A5) Start the database manager by issuing the command: db2start.

[on Server B]

2-2-B1) Copy back the image for restoring database by using the FlashCopy software.
2-2-B2) Issue the command: Importvg and varyonvg.
For example, you can issue the command # importvg -y'vgess1' hdisk2, where vgess1 is a volume group name which was once exportvg from hdisk2., and # varyonvg vgess1, where vgess1 is a volume group name which was once done varyoffvg.
2-2-B3) Mount the same mount point name as the original one.
2-2-B4) Catalog the database by issuing the command: db2 catalog database <dbname> on <path>.
2-2-B5) Restore user programs which are backed up on Note D) and check them.
2-2-B6) Start the database manager by issuing the command: db2start.
2-2-B7) Connect to the database by issuing the command: db2 connect to <dbname>.

2-3) Backup or restore to another server (offline copy and rollforward)

[on Server A]

2-3-A1) Stop the database manager by issuing the command: db2stop force.
2-3-A2) Save newer logs which are updated after being copied on 2-1) to other directories than FlashCopy mount points so as not to be overwritten.
2-3-A3) Umount the file systems which you plan on targeting using the FlashCopy software.
2-3-A4) Copy image for backing up database by using the FlashCopy software.
2-3-A5) Mount the file systems.
2-3-A6) Start the database manager by issuing the command: db2start.

[on Server B]

2-3-B1) Issue the commands: importvg and varyonvg.
2-3-B2) Mount the same mount point name as the original one.
2-3-B3) Make sure if the latest archive logs exist or restore the archive logs which were backed up in the steps above.
2-3-B4) Change the directory owner to instance owner
2-3-B5) Catalog the database by issuing the command: db2 catalog database <dbname> on <path>
2-3-B6) Put your database in rollforward pending state by issuing: db2rfpen on <dbname>.
2-3-B7) Restore user programs which are backed up on Note D) and check them.
2-3-B8) Start the database manager by issuing the command: db2start .
2-3-B9) Perform rollforward recovery operation by using the command: db2 rollforward db <dbname> to end of logs.
2-3-B10) Issue the command: db2 rollforward db <dbname> stop.
2-3-B11) Connect to the database by using the command: db2 connect to <dbname>.

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"Recovery - Backup","Platform":[{"code":"PF002","label":"AIX"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
24 October 2018

UID

swg21269231

Page Feedback