A very unpleasant situation in the daily operation of a database appears if someone unintentionally dropped a table. If this table belongs to the SAP database schema, the table and its data must be recreated so that SAP can work properly, and this means that a restore of the complete system and roll forward to a point in time (PIT) where the table existed is necessary. This is not only time consuming – changes to all other tables after the particular table was dropped are lost.
Beginning with DB2 UDB V8.2 a feature called ‘dropped table recovery’ is available in DB2 which tries to give some relief in this situation. Be aware that dropped table recovery is switched off by default during the SAP system installation. In this post I explain how dropped table recovery works, why it is normally switched off and how you can optionally enable it.
h5. How it works
As prerequisites, archival logging must be enabled (like in any productive SAP system on DB2), you need a valid backup and the log files and dropped table recovery must be switched on (more on this later).
In principle dropped table recovery works as follows. First, you perform a tablespace level restore on the tablespace where the dropped table resided, followed by a complete roll forward to the end of logs. The tablespace is afterwards in the same state as before. During the roll forward DB2 dumps the data of the dropped table into a flat data file in delimited ASCII (DEL) format. After the restore, you recreate the table based on the DDL in the DB2 history file and import the data from the data file.
Here comes an example, but please don’t try this out until reading to the end of this post. I’m performing all this with the database administrator db2dd7 in an ABAP-only Unicode system with the system identifier DD7.
db2 => DROP TABLE SAPDD7.BALSUBT
Table BALSUBT belongs to the application log (SAP transaction SLG1) which is a cross application component. Now I will try to recreate this table and its data. As a first step, to figure out the DDL statement which can be used later to recreate the table and the tablespace in which the table was located, I look up the DROP TABLE entry in DB2’s history file:
list history dropped table all for DD7
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
— — –
D T 20081211150023 000000004e004fff000e00ad
“SAPDD7 “.”BALSUBT” resides in 1 tablespace(s):
Comment: DROP TABLE
Start Time: 20081211150023
End Time: 20081211150023
DDL: CREATE TABLE “SAPDD7 “.”BALSUBT” ( “SPRAS” VARCHAR(3) NOT NULL WITH DEFAULT ‘ ‘ , “OBJECT” VARCHAR(60) NOT NULL WITH DEFAULT ‘ ‘ , “SUBOBJECT” VARCHAR(60) NOT NULL WITH DEFAULT ‘ ‘ , “SUBOBJTXT” VARCHAR(180) NOT NULL WITH DEFAULT ‘ ‘ ) COMPRESS YES VALUE COMPRESSION IN “DD7#POOLD” INDEX IN “DD7#POOLI” ;
In addition to the CREATE TABLE statement the entry contains other important information which I will need later.
Next, I create a directory which will contain the exported data of table BALSUBT.
52> mkdir /db2/DD7/export_data
After that, I need to perform a tablespace restore operation. A tablespace restore can be performed online, which means that all other tablespaces are available during that operation. Unfortunately, in a SAP system this is not helpful. If one tablespace is not accessible the SAP system will not work properly. So I need to shutdown the SAP system now (see below for an idea how to avoid the SAP system shutdown). Then I can perform the tablespace restore (I’m using an offline restore as this is faster). The name of the tablespace that needs to be restored is mentioned in the entry in the history file above.
db2 => RESTORE DATABASE DD7 TABLESPACE (DD7#POOLD) FROM /db2/DD7/backup
TAKEN AT 20081205104151
Very important: the backup image which is used for the restore operation must contain the dropped table! I can verify this easily by comparing the timestamp of the backup (20081205104151, for online backups consider the end time of the backup) with the time of the drop table operation (here 20081211150023).
Now I perform the roll forward. During this operation DB2 writes the data for table BALSUBT to the export directory.
db2 => ROLLFORWARD DATABASE DD7 TO END OF LOGS TABLESPACE (DD7#POOLD)
RECOVER DROPPED TABLE 000000004e004fff000e00ad TO /db2/DD7/export_data
I took the table ID (after RECOVER DROPPED TABLE) from the Backup ID column of the drop table entry in the history file (see above). During this operation DB2 creates subdirectories for every partition under the export_data directory. In each subdirectory a file named ‘data’ contains the data of the table in the respective partition.
66> ls -lR /db2/DD7/export_data
– 2 db2dd7 dbdd7adm 4096 2008-12-05 15:18 NODE0000</p><p> </p><p>/db2/DD7/export_data/NODE0000:</p><p>total 4160</p><p>-rw-r— 1 db2dd7 dbdd7adm 4244490 2008-12-05 15:18 dataA final thought about the required SAP system downtime: If the dropped table is not vital to overall system operation you can also perform a New Features in DB2 UDB V9 – Part 3 to another database. After the roll forward take the data and import it in the original system. This way no downtime is needed, but temporary additional disk space for the redirected restore is required.
The dropped table recovery procedure has some shortcomings which I will discuss now.
First, because delimited ASCII is used by the roll forward as output format for the table data, it is not possible to recreate binary data (at least I was not able to accomplish this). This means that all tables with columns of type BLOB, CLOB or types with the addition FOR BIT DATA (CHAR FOR BIT DATA, VARCHAR FOR BITDATA, LONG VARCHAR FOR BIT DATA) cannot be recreated using the dropped table recovery feature. All of these types appear in the SAP ABAP database schema, and you can expect that approximately 5 – 10% of all tables in a standard SAP installation contain one of these data types. LOB data seems to be ignored and is simply not dumped to the file system. The problem with the FOR BIT DATA types seems to be that the data is actually dumped to the file, but the delimiter character itself can appear within the binary data. This leads to incorrect result during the IMPORT. If anyone has an idea how to perform a dropped table recovery for tables which contain binary data please let me know how to do that.
Second, enabling dropped table can result in a performance penalty for applications that frequently drop tables. Every time a table is dropped DB2 creates a corresponding entry in the history file. Adding entries to the history file is an expensive operation (DB2 always maintains a copy of the history file). Also, the time it takes to create a dropped table entry seems to increase with the size of the history file. This is in fact the reason why SAP decided to switch off dropped table recovery by default (see SAP note 791325 for more information).
One of the applications which frequently drops tables is SAP BI. If you like to find out in a particular system if tables are dropped often, you can go to the DBA cockpit and look in the SQL Cache (Performance -> SQL Cache) for DROP Statements (filter the SQL Text for ‘DROP*’). Here you can also see how many times a DROP statement was executed. If you need to find out the same information for a Java only system, remember that you can use the DBA cockpit of an existing ABAP system to monitor an AS Java by creating a database connection to the Java system.
If you don’t find a lot of DROP statements in the system you may decide to switch on dropped table recovery. This is done on tablespace level. To check the current status for all tablespaces you can use the following SQL statement:
db2 => SELECT CAST(tbspace AS VARCHAR(20)) AS tbspace, drop_recovery FROM syscat.tablespaces