Use DB2 V9 for non-incremental redirection restoration (4)

Backup Test Database

Now let's back up and test the database so that we can recover to this state in the following example, as shown in listing 10.

-- List 10. Back up the database

Backup database DB2TEST1 TO/db2/databases/backup/

After the command is executed, it is displayed as follows:

The backup is successful. The time stamp of this backup image is: 20070911175125

Create example table

Next, create an example table. In the/home/db2inst1/directory, write the script "employee. SQL", as shown in listing 11.

-- Listing 11. employee. SQL content

Create table "DB2INST1". "EMPLOYEE "(
"EMPNO" CHAR (6) not null,
"FIRSTNME" CHAR (12) not null,
"MIDINIT" CHAR (1 ),
"LASTNAME" CHAR (15) not null,
"PHOTO" BLOB (10 M) not null logged not compact,
"WORKDEPT" CHAR (3 ),
"PHONENO" CHAR (4 ),
"HIREDATE" DATE,
"JOB" CHAR (8 ),
"EDLEVEL" smallint not null,
"SEX" CHAR (1 ),
"BIRTHDATE" DATE,
"SALARY" DECIMAL (9, 2 ),
"BONUS" DECIMAL (9, 2 ),
"COMM" DECIMAL (9, 2 ))
IN "TABLESPACE1" long in "TABLESPACE2 ";

Run the "employee. SQL" script:

$ Db2-tvf/home/db2inst1/employee. SQL

After successful execution, user data of table DB2INST1. EMPLOYEE is stored in TABLESPACE1, large fields are stored in TABLESPACE2, where TABLESPACE1 uses automatic storage and TABLESPACE2 uses non-automatic storage.

Back up the test database again

Now let's back up and test the database again, so that we can recover to this state in the following example, as shown in listing 12.
-- List 12. Back up the database

Backup database DB2TEST1 TO/db2/databases/backup/

After the command is executed, it is displayed as follows:

The backup is successful. The time stamp of this backup image is: 20070911175656

Restore test database using full backup

Use the restore database command to RESTORE the DATABASE or table space after a problem occurs (such as a media or storage failure, power failure, or application failure. If you have backed up databases or tablespaces, You can recreate them when they are damaged for some reason.

Note that the automatic storage path included in our backup media must exist in the environment we want to restore. This restore will use the backup file of the first full backup, the timestamp is 20070911175125. The restore database command is issued through the DB2 command line, as shown in listing 13.

-- List 13. Restore the database

Restore database DB2TEST1 FROM/db2/databases/backup/taken at 20070911175125

Because the DATABASE DB2TEST1 exists and will be replaced when the restore database command is issued, a prompt message is returned. The content is as follows:

SQL2539W warning! Restoring to the same existing database as the backup image database. The database file will be deleted.

Do you want to continue? (Y/n)

We specify y to complete the restoration operation.

The database restoration operation requires an exclusive connection: After the task is started, the restore utility prevents other applications from accessing the database until the restoration operation is completed successfully. Therefore, you cannot run any applications on the database, however, the tablespace restoration operation can be completed online, provided that the database is an archive database.

After the command is successful, connect to the database and report that the database is in the ROLL-forward pending state. This is because after the archive database is restored, rollback and recovery are required. The returned message is as follows:

SQL1117N cannot connect to or activate the Database "DB2TEST1" due to ROLL-forward pending ". SQLSTATE = 57019

Next, we will continue to roll back and restore DB2TEST1 and issue the ROLLFORWARD Command, as shown in listing 14.

-- List 14. Roll Back and restore the database

Rollforward database DB2TEST1 TO END OF LOGS AND STOP

After the execution is successful, you can access the database normally. From the returned message, you can see that two log files are processed, S0000001.LOG and S0000002.LOG. The specific message is shown in listing 15.

Listing 15. rollback and database recovery return messages

Roll-forward status

Input Database alias = DB2TEST1

Number of nodes returned status = 1

Node number = 0

Roll Forward status = not suspended

Next log file to be read =

Processed log file = S0000001.LOG-S0000002.LOG

Last implemented transaction = 2007-09-11-09.56.54.000000 UTC

The DB20000I ROLLFORWARD command is successfully completed.
Non-incremental redirection restore Test Database

The preceding section describes how to restore and roll back a database. Next, let's take a look at how to perform non-incremental redirection restoration for databases that do not allow Incremental backup.

During the database backup operation, a record is retained, which records all tablespace containers related to the tablespace being backed up. During the restore operation, all containers listed in the backup image are checked to determine whether they exist and are accessible. If one or more of these containers are inaccessible due to a media fault (or for any other reason), the restore operation will fail. In this case, the restore operation needs to be redirected to different containers.

You can call the restore database command and specify the REDIRECT parameter to redefine the tablespace container. Note that you need to re-specify the Automatic Storage path for the tablespace that uses the automatic storage function, instead of redirecting to different containers. During the Redirect restore operation, if the Directory and file container do not exist, they are automatically created. The database manager does not automatically create a device container. Container redirection provides considerable flexibility for managing tablespace containers.

We use a full-backup image with a timestamp of 20070911175656 for redirection and restoration. First, connect to the DB2TEST1 database (if you want to restore another machine, you can create a DATABASE named DB2TEST1 on it and connect it again.) issue the restore database command and use the REDIRECT option, as shown in listing 16.

-- List 16. First step of database redirection

Restore database DB2TEST1 taken at 20070911175656 ON/db2/databases/new_db2test1/auto_storage dbpath on/db2/databases/new_db2test1 REPLACE EXISTING REDIRECT

In this redirect, we restored the database and changed both the database path and the automatic storage path through the on and dbpath on parameters, the database path is changed from/db2/databases/db2test1 to/db2/databases/new_db2test1, change the Automatic Storage path from/db2/databases/db2test1/auto_storage to/db2/databases/new_db2test1/auto_storage.

The dbpath on parameter indicates the target database directory. If the utility is restored to an existing database, the parameter is ignored. The drive and directory must be local. If the backup image includes a database with Automatic Storage enabled and the on parameter is not specified, this parameter is synonymous with the TO parameter and only the database directory is changed, the storage path associated with the database is not changed.

The TO target-directory parameter is not used here. This parameter is briefly introduced because it is often used in practical application. This parameter indicates the directory of the target database. If the utility is restored to an existing database, this parameter is ignored. The specified drive and directory must be local. If the backup image includes a database with Automatic Storage enabled, only the database directory is changed, but the storage path associated with the database is not changed.

The ON Parameter re-defines the storage path associated with the automatic storage area database. This parameter can only be used in databases with Automatic Storage enabled. If you set this parameter to a database without automatic storage enabled, an error occurs (SQL20321N ). After this parameter is used, the existing storage path defined in the backup image is no longer used, and the automatic storage tablespace is automatically redirected to the new path. If this parameter is not specified for the automatic storage database, the storage path is still the path defined in the backup image. At this time, the path defined in the backup image must exist on the server we want to restore, otherwise, the entire redirection will eventually fail.

One or more paths can be specified, separated by commas. Each path must have an absolute path name and must be local. If the database does not exist ON the disk and the dbpath on parameter is not specified, the first path is used as the target database directory.

Issue the set tablespace containers command for the TABLESPACE TABLESPACE2 of the container that does not use automatic storage. The table space ID of TABLESPACE2 can be obtained through list tablespaces show detail. Table spaces such as TABLESPACE1, SYSCATSPACE, TEMPSPACE1, and USERSPACE1 use automatic storage management. Therefore, you do not need to issue the set tablespace containers command. As shown in listing 17.

-- List 17. Step 2 of database redirection

Set tablespace containers for 4 USING (FILE/databases/tablespace1/tablespace_32 K 640)

After completing step 1 and step 2, issue the restore database DB2TEST1 CONTINUE Command, as shown in listing 18.

-- Listing 18. Step 3 of database redirection

Restore database DB2TEST1 CONTINUE

If Step 3 fails, or the restore operation has been terminated abnormally, You can restart the restoration of the redirection from step 1.

After the command is successful, connect to the database and report that the database is in the ROLL-forward pending state. This is because after the archive database is restored, rollback and recovery are required. The returned message is as follows:

SQL1117N cannot connect to or activate the Database "DB2TEST1" due to ROLL-forward pending ". SQLSTATE = 57019

Next, we will continue to roll back and restore DB2TEST1 and issue the ROLLFORWARD Command, as shown in listing 19.

-- List 19. Roll Back and restore the database

Rollforward database DB2TEST1 TO END OF LOGS AND STOP

After the execution is successful, you can access the database normally. From the returned message, you can see that the processed log file contains S0000003.LOG. The specific message is shown in list 20.

Listing 20. rollback and database recovery return messages

Roll-forward status

Input Database alias = DB2TEST1

Number of nodes returned status = 1

Node number = 0

Roll Forward status = not suspended

Next log file to be read =

Processed log files = S0000003.LOG-S0000003.LOG

Last implemented transaction = 2007