A must quality of a DBA is to know the recovery strategy first before entering the battlefield .That is the reason I have started the Accidents happen series so that we can discuss further recovery strategies in this series of posts.
I have not found many search results where somebody who could show in detail how to recover an accidentally dropped table . So I thought of giving an example scenario as it will help at least some people who are dealing with it for the first time.
The first precaution you can take is to add WITH RESTRICT ON DROP clause to the CREATE TABLE
Say you created a table as shown below
CREATE TABLE "DB2INST1"."EMPLOYEE" ( "EMPNO" CHAR(6 OCTETS) NOT NULL , "FIRSTNME" VARCHAR(12 OCTETS) NOT NULL , "MIDINIT" CHAR(1 OCTETS) , "LASTNAME" VARCHAR(15 OCTETS) NOT NULL , "WORKDEPT" CHAR(3 OCTETS) , "PHONENO" CHAR(4 OCTETS) , "HIREDATE" DATE , "JOB" CHAR(8 OCTETS) , "EDLEVEL" SMALLINT NOT NULL , "SEX" CHAR(1 OCTETS) , "BIRTHDATE" DATE , "SALARY" DECIMAL(9,2) , "BONUS" DECIMAL(9,2) , "COMM" DECIMAL(9,2) ) IN "USERSPACE1" WITH RESTRICT ON DROP ORGANIZE BY ROW ;
Doing this db2 will not allow you to drop table , it will throw SQL0672N error
db2 "DROP TABLE DB2INST1.EMPLOYEE" DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0672N Operation DROP not allowed on table "DB2INST1.EMPLOYEE". SQLSTATE=55035
Say for some reason you dropped a table inspite of the warning db2 provided or say you did not set up WITH RESTRICT ON DROP while creating or alterning table , this is how you recover it
Say you have the following table's in your database
db2 "list tables" Table/View Schema Type Creation time ------------------------------- --------------- ----- -------------------------- CATALOG DB2INST1 T 2014-01-29-13.17.45.998001 CL_SCHED DB2INST1 T 2014-01-29-13.17.21.530001 CUSTOMER DB2INST1 T 2014-01-29-13.17.45.231001 DEPARTMENT DB2INST1 T 2014-01-29-13.17.25.671001 DEPT DB2INST1 A 2014-01-29-13.17.26.289000 EMP DB2INST1 A 2014-01-29-13.17.26.492000 EMPACT DB2INST1 A 2014-01-29-13.17.28.126000 EMPLOYEE DB2INST1 T 2014-01-29-13.17.26.290001 EMPMDC DB2INST1 T 2014-01-29-13.17.37.760000 EMPPROJACT DB2INST1 T 2014-01-29-13.17.28.062001 EMP_ACT DB2INST1 A 2014-01-29-13.17.28.127002 EMP_PHOTO DB2INST1 T 2014-01-29-13.17.26.494000 EMP_RESUME DB2INST1 T 2014-01-29-13.17.27.369001 INVENTORY DB2INST1 T 2014-01-29-13.17.45.011000
We will try dropping the EMPLOYEE table and try to recover it.
Note:ARCHIVAL logging to should be enabled for this recovery process.
Before starting let's check the backup images of the database we have
01/29/2014 12:28 PM 178,638,848 SAMPLE.0.DB2.DBPART000.20140129122806.001 01/29/2014 12:29 PM 178,638,848 SAMPLE.0.DB2.DBPART000.20140129122919.001 01/29/2014 12:31 PM 20,111,360 SAMPLE.0.DB2.DBPART000.20140129123137.001
First 2 were Offline backup's and 3rd one I have taken a FULL ONLINE COMPRESS backup image
Note:All the 3 backup images I have taken are before dropping a table.
Let's drop the table EMPLOYEE
db2 "drop table EMPLOYEE" DB20000I The SQL command completed successfully.
Check if the table exists or not
db2 "list tables" Table/View Schema Type Creation time ------------------------------- --------------- ----- -------------------------- CATALOG DB2INST1 T 2014-01-29-13.17.45.998001 CL_SCHED DB2INST1 T 2014-01-29-13.17.21.530001 CUSTOMER DB2INST1 T 2014-01-29-13.17.45.231001 DEPARTMENT DB2INST1 T 2014-01-29-13.17.25.671001 DEPT DB2INST1 A 2014-01-29-13.17.26.289000 EMP DB2INST1 A 2014-01-29-13.17.26.492000 EMPACT DB2INST1 A 2014-01-29-13.17.28.126000 EMPMDC DB2INST1 T 2014-01-29-13.17.37.760000 EMPPROJACT DB2INST1 T 2014-01-29-13.17.28.062001 EMP_ACT DB2INST1 A 2014-01-29-13.17.28.127002 EMP_PHOTO DB2INST1 T 2014-01-29-13.17.26.494000 EMP_RESUME DB2INST1 T 2014-01-29-13.17.27.369001 INVENTORY DB2INST1 T 2014-01-29-13.17.45.011000
You see EMPLOYEE table is not there,
- what should I do now ? It's simple just RESTORE AND ROLLFORWARD and you are done.
- From which BACKUP should I RESTORE ? RESTORE from the BACKUP prior dropping table
- Shall I RESTORE ALL TABLESPACES ? Just the TABLESPACE where the TABLE is residing
- How will I know the TABLESPACE after I have dropped the TABLE ? You have the HISTORY file
- Holy Crap just show me how ... K Fine I can understand
1. Query the history file
db2 "list history dropped table all for sample"
List History File for sample
Number of matching file entries = 1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
D T 20140129125353 0000000000000ae500020006
----------------------------------------------------------------------------
"DB2INST1 "."EMPLOYEE" resides in 1 tablespace(s):
00001 USERSPACE1
----------------------------------------------------------------------------
Comment: DROP TABLE
Start Time: 20140129125353
End Time: 20140129125353
Status: A
----------------------------------------------------------------------------
EID: 25
DDL: CREATE TABLE "DB2INST1 "."EMPLOYEE" ( "EMPNO" CHAR(6 OCTETS) NOT NULL , "FIRSTNME" VARCHAR(12 OCTETS) NOT NULL , "MIDINIT" CHAR(1 OCTETS) , "LASTNAME" VARCHAR(15 OCTETS) NOT NULL
L , "WORKDEPT" CHAR(3 OCTETS) , "PHONENO" CHAR(4 OCTETS) , "HIREDATE" DATE , "JOB" CHAR(8 OCTETS) , "EDLEVEL" SMALLINT NOT NULL , "SEX" CHAR(1 OCTETS) , "BIRTHDATE" DATE , "SALARY" D
ECIMAL(9,2) , "BONUS" DECIMAL(9,2) , "COMM" DECIMAL(9,2) ) IN "USERSPACE1" ORGANIZE BY ROW;
----------------------------------------------------------------------------
Make a note of the Backup ID and IN tablespace of the CREATE TABLE clause
2. Restore the tablespace where the table resides i.e., USERSPACE1 in our case
db2 "restore db sample tablespace (USERSPACE1) taken at 20140129123137" DB20000I The RESTORE DATABASE command completed successfully.
3. Rollfoward your database
db2 "rollforward db sample to end of logs tablespace (USERSPACE1) recover dropped table 0000000000000ae500020006 to /home/sample/export"
Rollforward Status
Input database alias = sample
Number of members have returned status = 1
Member ID = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = -
Last committed transaction = 2014-01-29-07.23.57.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
/home/sample/export is the directory where the ROLLFORWARD places the table data . db2 adds another directory NODE0000 with data file in it on the top of mentioned directory .
So if you mention the path /home/sample/export a file named data will be created with the table data with the following data structure /home/sample/export/NODE0000/data
So now the table data is in the file /home/sample/export/NODE0000/data
4. CREATE TABLE from the DDL we got from the LIST HISTORY COMMAND
CREATE TABLE "DB2INST1 "."EMPLOYEE" ( "EMPNO" CHAR(6 OCTETS) NOT NULL , "FIRSTNME" VARCHAR(12 OCTETS) NOT NULL , "MIDINIT" CHAR(1 OCTETS) , "LASTNAME" VARCHAR(15 OCTETS) NOT NULL , "WORKDEPT" CHAR(3 OCTETS) , "PHONENO" CHAR(4 OCTETS) , "HIREDATE" DATE , "JOB" CHAR(8 OCTETS) , "EDLEVEL" SMALLINT NOT NULL , "SEX" CHAR(1 OCTETS) , "BIRTHDATE" DATE , "SALARY" DECIMAL(9,2) , "BONUS" DECIMAL(9,2) , "COMM" DECIMAL(9,2) ) IN "USERSPACE1" ORGANIZE BY ROW DB20000I The SQL command completed successfully.
5. Import the table data from the file /home/sample/export/NODE0000/data
db2 "import from /home/sample/export/NODE0000/data of del insert into DB2INST1.EMPLOYEE" SQL3109N The utility is beginning to load data from file "/home/sample/export/NODE0000/data". SQL3110N The utility has completed processing. "42" rows were read from the input file. SQL3221W ...Begin COMMIT WORK. Input Record Count = "42". SQL3222W ...COMMIT of any database changes was successful. SQL3149N "42" rows were processed from the input file. "42" rows were successfully inserted into the table. "0" rows were rejected. Number of rows read = 42 Number of rows skipped = 0 Number of rows inserted = 42 Number of rows updated = 0 Number of rows rejected = 0 Number of rows committed = 42
That's it that table you have dropped is back in action.
Have a Nice Day
No comments:
Post a Comment