Restoring Database Instances 

Use

After a hardware or database error, you restore the data in the affected database instance by importing the data backups and log backups, if any, of that database instance.

The first step is to display the backup history and determine the most recent complete data backup (see: backup_history_open, backup_history_list) as well as any incremental data backups that have been made since. Pay attention to the information in the lines of the backups, which tell you when the respective backup was made and which pages it contains. The most recent entries are at the end of the file.

The next step is to import the complete data backup followed by the incremental data backups (see: recover_start). After each data backup that is imported, the system tells you the next page to be imported after this data backup.

After importing the data backups, you display the restart information and determine whether the database instance is ready to be restarted and from which page the log entries in the log area are still present (see db_restartinfo).

If the next page to be imported following a data backup is still in the log area of the database instance, you now only have to restart the database instance: db_restart). The system imports the log entries from the log area and thus restores the database instance to its last consistent state. If you do not want to restore a database instance to its last consistent state but only up to a certain point in the past, enter this point in time as an option at when you restart the database instance.

If the page to be imported after a data backup is lower than the lowest page number in the log area, look at the backup history to determine which backup contains the page. Import the relevant data or log backup and all subsequent backups successively in chronological order up to and including the one that contains - at least - the predecessor of the lowest page number in the log area (see: recover_replace). As soon as this page has been imported, the system ends the import of the backup and starts the database instance using the entries still in the log area (restart).

See also:

Importing Log Backups up to a Specific Time

 

You import the first backup with the DBM command recover_start. You import all subsequent backups with the DBM command recover_replace.

If for any reason you cancel the import of backups or make a mistake while entering the next command, the system automatically transfers the database instance to the OFFLINE operational state.

This means that you have to transfer the database instance back to the ADMIN operational state and reopen the database session. At this point you re-execute db_restartinfo to find out for certain which of the imported pages have already been persistently saved in the data are and which pages need to be imported again. In this case, proceed with the import using recover_start to import the first page expected by the system as per the restart information and recover_replace to import all subsequent backups.

 

To read the prerequisites, see Database Manager CLI, recover_start, recover_replace

See also:

Evaluating the Restart Information and Backup History for Importing Backups

Concepts of the Database System, Backing Up and Restoring, Restoring the Database Instance

Glossary, Backup History

Scenario

You want to restore the database instance after a hardware error.

To simulate this example, your database instance must have at least two data volumes. You can display all available volumes with the DBM command param_getvolsall. If necessary, create an additional volume with the name DISKD0002 under the path c:\documents and settings\all users\application data\sdb\data\demodb\data (see: Enlarging a Database Instance, Scenario 3).

Now transfer the database instance to the ADMIN operational state. Simulate a hardware error by deleting data volume DISKD0002 using the operating system command.

The database instance is still in the ADMIN operational state, but all data previously in the data area has been lost. The database instance needs to be restored.

Procedure

In the following example, the relevant values in the output are in bold print; explanations of the output are marked as comments with the number sign (#).

Displaying Backup Information

The first step is to display and evaluate information about the available backups.

       1.      Call the Database Manager CLI in session mode, log on as operator OLEG with the password MONDAY, connect to the database instance DEMODB:

>dbmcli -u OLEG,MONDAY -d DEMODB

dbmcli on DEMODB>

       2.      Open the backup history:

dbmcli on demodb>backup_history_open

OK

       3.      Display the backup history:

dbmcli on demodb>backup_history_list

OK

END

…

# The most recent complete data backup is the one with the label DAT_000000002. This one must be imported first.

# The next page must be 9531:

|                                        |

43C7BBD1000B|DAT_000000002|SAVE WARM|2006-01-13 15:40:17|2006-01-13 15:40:17|200

6-01-13 15:40:17|2006-01-13 15:40:17|      9531|          |NO |DemoDataCompl

                                               |       384|         1|         0

# Page 9531 is in the log backup with the label LOG_000000007. This one must therefore be imported directly after the complete data backup.

# This import includes pages 9531 -11828; the next page to be imported is 11829:

|                                        |

43C7BBE5000C|LOG_000000007|SAVE WARM|2006-01-13 15:40:23|2006-01-13 15:40:37|200

6-01-13 15:40:37|2006-01-13 15:40:38|      9531|     11828|   |DemoFileLog

                                               |      2096|         1|         0

# Page 11829 is in the log backup with the label LOG_000000008. This one must therefore be imported directly after the log backup with the label LOG_000000007.

# This import includes pages 11829 -14182; the next page to be imported is 14183:

|                                        |

43C7BC01000D|LOG_000000008|SAVE WARM|2006-01-13 15:40:42|2006-01-13 15:40:46|200

6-01-13 15:41:05|2006-01-13 15:41:06|     11829|     14182|   |DemoFileLog

                                               |      2152|         1|         0

# Page 14183 is in the log backup with the label LOG_000000009. This one must therefore be imported directly after the log backup with the label LOG_000000008.

# This import includes pages 14183 – 14358; the next page to be imported is 14359:

|                                        |

43C7BC01000D|LOG_000000009|SAVE WARM|2006-01-13 15:40:46|2006-01-13 15:41:05|200

6-01-13 15:41:06|2006-01-13 15:41:07|     14183|     14358|   |DemoFileLog

                                               |       176|         1|         0

# Page 14359 is in the log backup with the label LOG_000000010. This one must therefore be imported directly after the log backup with the label LOG_000000009.

# This import includes pages 14359 -16712; the next page to be imported is 16713:

|                                        |

43C7BC24000E|LOG_000000010|SAVE WARM|2006-01-13 15:41:17|2006-01-13 15:41:21|200

6-01-13 15:41:40|2006-01-13 15:41:42|     14359|     16712|   |DemoFileLog

                                               |      2152|         1|         0

# Thereafter all log backups including the one with the label LOG_000000013 must be imported in chronological succession.

|                                        |

43C7BC24000E|LOG_000000011|SAVE WARM|2006-01-13 15:41:21|2006-01-13 15:41:25|200

6-01-13 15:41:42|2006-01-13 15:41:43|     16713|     19065|   |DemoFileLog

                                               |      2152|         1|         0

|                                        |

43C7BC24000E|LOG_000000012|SAVE WARM|2006-01-13 15:41:25|2006-01-13 15:41:40|200

6-01-13 15:41:43|2006-01-13 15:41:43|     19066|     19770|   |DemoFileLog

                                               |       656|         1|         0

# The log backup with the label LOG_000000013 contains the pages 19771 to 22124 and thus includes the predecessor to the first page in the log area as shown in the restart information (21452).

# During the restore process, the system will import this log backup in its entirety. The subsequent log backups do not need to be imported since the pages in them are still in the log area and can be restored by the system from the log area.

|                                        |

43C7C497000F|LOG_000000013|SAVE WARM|2006-01-13 16:17:14|2006-01-13 16:17:17|200

6-01-13 16:17:43|2006-01-13 16:17:45|     19771|     22124|   |DemoFileLog

                                               |      2152|         1|         0

 

|                                        |

43C7C497000F|LOG_000000014|SAVE WARM|2006-01-13 16:17:17|2006-01-13 16:17:22|200

6-01-13 16:17:45|2006-01-13 16:17:46|     22125|     24478|   |DemoFileLog

                                               |      2152|         1|         0

 

|                                        |

43C7C497000F|LOG_000000015|SAVE WARM|2006-01-13 16:17:22|2006-01-13 16:17:43|200

6-01-13 16:17:46|2006-01-13 16:17:48|     24479|     26748|   |DemoFileLog

                                               |      2080|         1|         0

 

|                                        |

43C7C5240010|LOG_000000016|SAVE WARM|2006-01-13 16:17:45|2006-01-13 16:20:04|200

6-01-13 16:20:04|2006-01-13 16:20:05|     26749|     28510|   |DemoFileLog

                                               |      1616|         1|         0

|                                        |

Restoring Data and Log Entries

You now have the information about the backups and can restore DEMODB.

...

       1.      Transfer the database instance to the ADMIN operational state:

dbmcli on demodb>db_admin

OK

       2.      Open a database session:

dbmcli on demodb>db_connect

OK

       3.      Import the most recent complete data backup:

dbmcli on demodb>recover_start DemoDataCompl

OK

Returncode              0

Date                    20060117

Time                    00114627

Server                  GENUA

Database                DEMODB

Kernel Version          Kernel    7.6.00   Build 016-123-109-428

Pages Transferred       384

Pages Left              0

Volumes                 1

Medianame               DemoDataCompl

Location                Backups 2005\data\completeDataJan

Errortext

Label                   DAT_000000002

Is Consistent           true

First LOG Page                  9531

# After the complete data backup, the system expects the log page with the number 9531.

Last LOG Page

DB Stamp 1 Date         20060113

DB Stamp 1 Time         00154017

DB Stamp 2 Date

DB Stamp 2 Time

Page Count              362

Devices Used            1

Database ID             GENUA:DEMODB_20060113_153806

Max Used Data Page      0

       4.      Display the restart information:

dbmcli on demodb>db_restartinfo

OK

Used LOG Page           28513

First LOG Page          21452

# The log area still contains pages 21452 to 28513. Thus to restart the database, pages 9531 to 21451 must still be imported from backups.

Restartable             1

# The database instance is ready to be restarted. The log area is undamaged.

Id Restart Record       GENUA:DEMODB_20060113_153806

Id LOG Info             GENUA:DEMODB_20060113_153806

Consistent                      1

       5.      Import the log backups

                          a.      Import the log backup with the label LOG_000000007:

dbmcli on demodb>recover_start DemoFileLog LOG 007

OK

Returncode              -8020

# Return code -8020 tells you that the chronologically subsequent backup must be imported using the command recover_replace.

Date                    20060117

Time                    00143406

Server                  GENUA

Database                DEMODB

Kernel Version          Kernel    7.6.00   Build 016-123-109-428

Pages Transferred       2096

Pages Left              0

Volumes                 1

Medianame               DemoFileLog

Location                Backups 2005\data\logBackup.007

Errortext

Label                   LOG_000000007

Is Consistent

First LOG Page                  9531

Last LOG Page           11828

DB Stamp 1 Date         20060113

DB Stamp 1 Time         00154023

DB Stamp 2 Date         20060113

DB Stamp 2 Time         00154037

Page Count              2297

Devices Used            1

Database ID             GENUA:DEMODB_20060113_153806

Max Used Data Page

 

                            b.      Import the log backup with the label LOG_000000008 using the command recover_replace:

dbmcli on demodb>recover_replace DemoFileLog "C:\Documents and Settings\All Users\Application Data\sdb\data\wrk\DEMODB\Backups 2005\Data\logBackup" 008

OK

Returncode              -8020

Date                    20060117

Time                    00143406

Server                  GENUA

Database                DEMODB

Kernel Version          Kernel    7.6.00   Build 016-123-109-428

Pages Transferred       2152

Pages Left              0

Volumes                 1

Medianame               DemoFileLog

Location                C:\Documents and Settings\All Users\Application Data\sdb\data\wrk\DEMODB\Backups 2005\Data\logBackup.008

Errortext

Label                   LOG_000000008

Is Consistent

First LOG Page                  11829

Last LOG Page           14182

DB Stamp 1 Date         20060113

DB Stamp 1 Time         00154042

DB Stamp 2 Date         20060113

DB Stamp 2 Time         00154046

Page Count              2353

Devices Used            1

Database ID             GENUA:DEMODB_20060113_153806

Max Used Data Page

 

                            c.      Import the log backup with the label LOG_000000009 using the command recover_replace:

dbmcli on demodb>recover_replace DemoFileLog "C:\Documents and Settings\All Users\Application Data\sdb\data\wrk\DEMODB\Backups 2005\Data\logBackup" 009

OK

Returncode              -8020

Date                    20060117

Time                    00143406

Server                  GENUA

Database                DEMODB

Kernel Version          Kernel    7.6.00   Build 016-123-109-428

Pages Transferred       176

Pages Left              0

Volumes                 1

Medianame               DemoFileLog

Location                C:\Documents and Settings\All Users\Application Data\sdb\data\wrk\DEMODB\Backups 2005\Data\logBackup.009

Errortext

Label                   LOG_000000009

Is Consistent

First LOG Page                  14183

Last LOG Page           14358

DB Stamp 1 Date         20060113

DB Stamp 1 Time         00154046

DB Stamp 2 Date         20060113

DB Stamp 2 Time         00154105

Page Count              175

Devices Used            1

Database ID             GENUA:DEMODB_20060113_153806

Max Used Data Page

 

                            d.      to g: Import the rest of the log backups with the labels LOG_000000010 - LOG_000000013 using the command recover_replace (see sections a – c).

# When the last required log backup has been imported and the database system therefore has all the pages whose number is lower than the first page in the log area, the system automatically imports the rest of the pages from the log area and finally transfers the database instance to the ONLINE operational state.

Result

The database instance DEMODB has been restored; the manually deleted volume has been restored according to the information in the database parameter file and can again be found at its original location on your hard disk.

All pages that were in the data and log areas before the hardware error are again in the database instance and can be accessed.

The database instance is ready for operation and is in the ONLINE operational state.