Database recovery methods

-

 Backup and recovery

Following, there are four utilities supported within Db2 to facilitate backing up and restoring a database:

The backup utility

The restore utility

The roll-forward utility

The recover utility

 The backup utility:

The backup utility is used to create images outside the database that can be used to recover the entire database or a portion of it. If the database is an archive logging enabled database, the backup utility can be executed online with multiple concurrent applications connected to the database. The backup utility uses the utility heap to copy the data from the table spaces to the backup image. The utility heap can be controlled via the database configuration parameter UTIL_HEAP_SZ.

The backup utility can write the backup image to storage devices on the database server, to a tape device, or can copy the data directly to the TSM storage devices or another vendor-supplied routine.

Execute the BACKUP command to start the backup process. The basic syntax is as follows:

BACKUP [DATABASE | DB] [DatabaseAlias]

<USER [UserName] <USING [Password]>>

<TABLESPACE ([TbspNames]) | NO TABLESPACE>

<ONLINE>

<INCREMENTAL <DELTA>>

<TO [Location] | USE TSM <OPTIONS [TSMOptions]>>

<WITH [NumBuffers] BUFFERS>

<BUFFER [BufferSize]>

<PARALLELISM [ParallelNum]>

<COMPRESS>

<UTIL_IMPACT_PRIORITY [Priority]>

<INCLUDE LOGS | EXCLUDE LOGS>

<WITHOUT PROMPTING>

Take a look at the following commands used in the preceding query:

DatabaseAlias: This command identifies the name assigned to the database from which to create a backup image

UserName: This command identifies the name assigned to a specific user who is to perform the backup operation

Password: This command identifies the password that corresponds to the name of the user who is to perform the backup operation

TbspName: This command identifies the name assigned to one or more specific table spaces to create backup images

Location: This command identifies the directory or device in which to store the backup image that's been created

TSMOptions: This command identifies options that the TSM is to use during the backup operation

NumBuffers: This command identifies the number of buffers to use to perform the backup operation

BufferSize: This command identifies the size, in pages, of each buffer used to perform the backup operation

ParallelNum: This command identifies the number of table spaces that can be read in parallel during the backup operation to improve the performance of the operation

Priority: This command is used to throttle the backup utility to control the effects on concurrent database activity; you can assign this parameter a numerical value within the range of 1 to 100, with 100 representing the highest priority and 1 representing the lowest

If you want to back up the entire database SAMPLE online to TSM, use the following command:

 

BACKUP DATABASE sample ONLINE USE TSM WITH BUFFER 8192 PARALLELISM 8 COMPRESS;

If you want to back up two table spaces, TS_DATA_4K and TS_INDX_4K, of the database SAMPLE online to TSM, use the following command:

BACKUP DATABASE sample TABLESPACE (TS_DATA_4K, TS_INDX_4K) ONLINE USE TSM WITH BUFFER 8192 PARALLELISM 8 COMPRESS;

If you only want to backup the database metadata, such as the history file, without backing up any table spaces, you can use the following command:

BACKUP DATABASE sample NO TABLESPACE;

Backup successful. The timestamp for this backup image is: 20180526232357

The following command can then be used to restore the metadata backup image:

RESTORE DATABASE sample TAKEN AT 20180526232357 REPLACE HISTORY FILE;

You can backup and restore from a 32-bit level database to a 64-bit level database without any change to the RESTORE command. If you want to restore a 32-bit database instance's compressed backup image onto a 64-bit instance, you can specify the comprlib libdb2compr.so compression library within the RESTORE DATABASE command. The EXPORT, INSPECT, CREATE INDEX, CREATE TABLE, ALTER TABLE, DROP TABLE, and DROP INDEX commands are compatible with online backups.

IMPORT with REPLACE, LOAD with COPY NO and ALLOW READ ACCESS, ALTER TABLESPACE ... AUTORESIZE, and REORG TABLE are not compatible with online backups. There is no difference between an ESE database backup operation and a pureScale database backup operation. In a database partitioning feature (DPF), also known as a massively parallel processing (MPP) database, you can use the DBPARTITIONNUMS clause within the BACKUP DATABASE command to backup multiple partitions at the same time. If no partition is specified, the backup utility runs on all the partitions.

Incremental and delta backups:

An incremental backup is a backup image that contains only pages that have been updated since the previous backup image was made. Two types of incremental backup images can be produced: incremental and delta. An incremental backup image is a copy of all database data that has changed since the most recent successful full backup image was created. The predecessor of an incremental backup image is always the most recent successful full backup image of the same object. A delta backup image, however, is a copy of all database data that has changed since the last successful backup (full, incremental, or delta) of the database or table space.

Comments

Post a Comment

Popular posts from this blog

Db2 export command example using file format (del , ixf)

-
You want to transfer data from a db2 database table to another db2 database or within the same database, one of the way is to use db2 export / import command. Using db2 export / import you can move your data to excel also.   DB2 export command exports data selected by SQL statement from a table or view to an external files in the format of  del - delimited , wsf - worksheet format , ixf - Integration Exchange Format,asc.   The DEL format uses delimiter and column separator and decimal point. The default string delimiter is double quote " and column separator is comma(,) and Decimal Point is dot(.), you can also specify your own string delimiter & column separator, decimal point. Another format supported by DB2 is   IXF (Integration Exchange Format). It is a generic relational database exchange format which supports an end-of-record delimiter . IXF architecture supports to exchange of relational database structures and data. IXF is used for transferring data ...
Read more

How to fix DB2 Tablespace OFFLINE state issue?

-
Sometimes we face an error where one (or more) of our db2 tablespaces goes offline caused by loads, DB crashes, abnormal db2 services/or server shutdown, etc. So in this blogpost let us see how can we check which tablespace is in OFFLINE state and bring back tablespace’s state to ONLINE.   Finding TableSpace State To check which tablespace is OFFLINE, execute the below command: SYNTAX: db2 connect to <database_name> db2 list tablespaces |egrep -i "ID|Name|State" db2 list tablespaces show detail   If u see Tablespace state with 0x4000, then that tablespace is in Offline state There are 2 methods to resolve the tablespace from offline to online as below. Method1: We can execute below DB2 commands to take off tablespace's OFFLINE state.   db2 terminate db2 force application all db2 connect to <database_name>   Method 2:  Perform ALTER Tablespace command to bring the tablespace up while the rest of the database is still up...
Read more

Phases of a load operation

-
A complete load operation consists of five distinct phases; they are as follows: Analyze phase Load phase Build phase Delete phase Index copy phase Analyze phase:  This phase of the Load process is only utilized when a column-organized table is being loaded and the column compression dictionaries need to be built. This happens during a LOAD REPLACE operation, a LOAD REPLACE RESETDICTIONARY operation, a LOAD REPLACE RESETDICTIONARYONLY operation, or a LOAD INSERT operation (if the column-organized table is empty). For column-organized tables, this phase is followed by the load, build, and delete phases. The following diagram showcases the steps involved in the analyze phase. The analyze phase is invoked if you are loading data for the first time into a column-organized BLU acceleration table using LOAD INSERT, or running a LOAD REPLACE with RESETDICTIONARY or RESETDICTIONARYONLY clause. Load phase:  During this phase, data is loaded into the table, and index keys and table stat...
Read more