Learning Visually with Examples [Electronic resources]

Raul F. Chong, Clara Liu, Sylvia F. Qi, Dwaine R. Snow

نسخه متنی -صفحه : 312/ 174
نمايش فراداده

13.13. Case Study

You have just been assigned a new task: to clone the production database PROD1 on a Windows server to another Windows test server. These are the criteria:

  • The cloned database name is TEST1.

  • The containers for TEST1 must be redefined. They must reside on the D: drive of the test server.

  • TEST1 must contain the most recent data in PROD1.

  1. First, take a backup of the database PROD1. Because PROD1 is a 24x7 production database, it is not possible to perform an offline backup. Therefore, you decide to perform an online backup:

    db2 backup db prod1 online to d:\temp

    The backup image is created as:

    D:\temp\PROD1.0\DB2\NODE0000\CATN0000\20040414\170803.001

  2. Obtain the table space container information on PROD1. This information is needed to define table space containers for the test database.

    1. First, list all table spaces in PROD1, as shown in Figure 13.19.

      Figure 13.19. Getting table space information from PROD1

      [View full size image]

    2. Then, list the containers for each table space, as shown in Figure 13.20.

      Figure 13.20. Getting container information from PROD1

      [View full size image]

      Since all three table spaces are SMS table spaces, all the containers are directories.

  3. Transfer the backup image over to the test server. FTP the backup image, including the folders and directories starting at D:\temp, over to the test server.

  4. Perform a database redirected restore and define the table space containers for database TEST1, as shown in Figure 13.21.

    Figure 13.21. Performing redirected restore of PROD1 into TEST1

    [View full size image]

  5. Perform a roll forward on the newly restored database TEST.

    1. At this point, you cannot connect to TEST1 yet. A connect attempt will receive the error SQL1117N.

    2. To roll forward, you need to find out which logs are needed. Run the

      ROLLFORWARD DATABASE command with the

      QUERY STATUS option (see Figure 13.22).

      Figure 13.22. Finding out which logs are needed to perform roll forward

      [View full size image]

      Figure 13.22 shows the next log file to be read is S0000000.LOG. This means that you have to transfer all the logs in the active log directory of PROD1, starting at S0000000.LOG, to the active log directory of TEST1.

    3. To find out where the active log directory is for PROD1, issue the

      GET DB CFG FOR prod1 command. The active log directory is indicated as Path to log files.

      Path to log files                = D:\DB2\NODE0000\SQL00003\SQLOGDIR

    4. The logs in this directory are: S0000000.LOG, S0000001.LOG, S0000002.LOG, and S0000003.LOG. Transfer all of these logs to the test server and into the active log directory of TEST1, which is on D:\DB2\NODE0000\SQL00001\SQLOGDIR\. (If you list this directory, you will see that it is currently empty.)

    5. Perform a roll forward operation on TEST1, as shown in Figure 13.23.

      Figure 13.23. Performing the roll forward operation on TEST1

      [View full size image]

  6. Connect to TEST1 and verify that it contains the correct data.