Tutorial on restoring a deleted table instance in DB2

DB2 restore deleted table test: (Test Platform: RHEL 5.4 DB2 V9.7.0)

-- Haha, a newbie! I just read Master Niu's book and made an example. I am familiar with it. By the way, I will show myself up to satisfy my vanity!

-- The following is an example of dropped table recovery.
-- * (1) perform full database tutorial backup. Pay attention to the time stamp of the backup image.
(Instance: DB2INST1, Database: TEST1): backup db test1 to/home/db2inst1/db2_backup

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

(Instance: DB2INST1, Database: TEST1 ):

-- * (2) connect to the database and create a table, perform the log generation operation, and insert several records:
Connect to test1
Create table tab1 (no INTEGER) IN tbsp1
Insert into tab1 VALUES (1), (2), (3), (4), (5)

-- * (3) simulate unexpected table discard:
Drop table tab1
COMMIT
SELECT * FROM tab1
-- The following error message is returned:
Error: SQL0204N "Administrator. TAB1" is an undefined name

-- * (4) restore the database.
-- To restore a discarded table, first restore the database backup, and then perform the rollforward operation:
Restore database test1 FROM/home/db2inst1/db2_backup taken at 20100522140304 INTO test1
-- The following message is returned:
......

-- * (5) retrieve the ID of the discarded object.
-- Use the following command to retrieve the Object ID of an accidentally discarded table:
List history dropped table all for database test1
-- You can copy the returned information (for example, the example shown in Table 7-3) to a file for future reference.
(Instance: DB2INST1, Database: TEST1): list history dropped table all for database test1

List History File for test1

Number of matching file entries = 1

Op Obj Timestamp + Sequence Type Dev Earliest Log Current Log Backup ID
--------------------------------------------------------------------
D t 20100522141107 000000000000684300080004
----------------------------------------------------------------------------
"DB2INST1". "TAB1" resides in 1 tablespace (s ):

00001 TBSP1
----------------------------------------------------------------------------
Comment: DROP TABLE
Starting Time: 20100522141107
Endtime: 20100522141107
Status:
----------------------------------------------------------------------------
EID: 33

DDL: create table "DB2INST1". "TAB1" ("NO" INTEGER) IN "TBSP1 ";
----------------------------------------------------------------------------
(Instance: DB2INST1, Database: TEST1 ):

-- The Backup ID column in table 7-3 shows the ID of the discarded table as 000000000000684300080004. This information is very important for table recovery.

-- * (6) restore the database forward.
-- Now the ID of the discarded table has been obtained. In the next step, you need to use the backup ID of the table to restore the database so that the table data can be imported. Before resuming a database,
-- Make sure that there is a directory for storing imported data, such as/home/db2inst1/test1/exporttab1. Run the following command to restore the database:
Mkdir-p/home/db2inst1/test1/exporttab1

-- Rollforward database test1 to end of logs and stop recover dropped table 000000000000684300080004 TO/home/db2inst1/test1/exporttab1

(Instance: DB2INST1, Database: TEST1): rollforward database test1 to end of logs and stop
(Instance: DB2INST1, Database: TEST1): recover dropped table 000000000000684300080004
(Instance: DB2INST1, Database: TEST1): to/home/db2inst1/test1/exporttab1

Rollforward Status

Input database alias = test1
Number of nodes have returned status = 1

Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000007.LOG-S0000007.LOG
Last committed transaction = 2010-05-22-06.11.07.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.

 

The -- end of logs option enables DB2 to apply all available log files after performing the backup operation.

-- * (7) Check the imported data file.
-- After the database is recovered, check the path specified in the ROLLFORWARD command. You should be able to find a. TXT file,
-- Open the file and verify that the data contained in the file is the same as the data before the accidentally discarded table.

-- * (8) connect to the database and recreate the discarded table.
-- After verifying the exported file, we need to re-create the discarded table and re-enter the data. The definition of the discarded table is included in the output of the list history command in step (5.
-- Connect to the database and execute the create table statement:
Connect to test1
Create table "DB2INST1". "TAB1" ("NO" INTEGER) IN "TBSP1 ";

-- * (9) import data
-- After creating a new table, run the following command to re-import the database to the table:
Import from/home/db2inst1/test1/exporttab1/NODE0000/data of del insert into "DB2INST1". "TAB1"

-- IMPORT tool imports all data in the exported file back to the table and sends a report (not displayed) after the export is successful ).

-- * (10) Verify the recovered data
-- Make sure there are no errors or alarms during the IMPORT process, and all data has been imported back to the table:
SELECT * FROM tab1

-- If everything runs normally, all the data before the accidental discard point should be in the table.