It’s every Netezza user’s nightmare, somebody had unintentionally dropped a business-critical production table on their Netezza system and so it needed to be restored immediately otherwise the proverbial would hit the fan. Luckily we were on hand and immediately started the process of restoring from our client’s EMC storage manager.
What happened next is every DBA’s nightmare - the nzrestore failed for a reason that to this day we will never know. We suspected it may have been corrupt backupset metadata, as we were able to recover the individual table file. So with the clock ticking and our client breathing down our neck, we had to manually reconstruct a new backupset and replace the empty table data file with the one we recovered from the storage manager. This is how it was done:
Troubleshooting the failed nzrestore
-
Show available backups for your database:
[nz@netezza ~]$ nzbackup -history -db MYDB
-
Restore database or tables
[nzrestore -db MYDB -tables MYTABLE -backupset 2019112709360
- Check the status of the restore for your database
[nz@netezza md]$ nzrestore -history -db MYDB
- Examine logs and investigate/resolve any errors
grep -i error /nz/kit/log/restoresvr/restoresvr.4361.2019-11-29.log
Manually restore a table from a backupset with suspected corrupt metadata
- Create a restore database
nzsql -c "create database MYDB_RESTORE;"
- Create table schema (either from existing table or saved DDL)
nzsql -d MYDB_RESTORE -c "create table MYTABLE as select * from MYDB..MYTABLE limit 0;"
- Backup the newly created database
[nz@netezza FULL]$ nzbackup -db MYDB_RESTORE -dir /tmp
- Navigate and list contents of backup dir, which is in the format of
{backupdir}/Netezza/{npshost}/{database}/{backupset}/{increment}/{backuptype}/data/
The files correspond to the object IDs for each table in that particular database e.g. {objid}.full.1.1
[nz@netezza data]$ cd /tmp/Netezza/netezza/MYDB_RESTORE/20191129100508/1/FULL/data
[[nz@netezza data]$ ls -[nz@netezza data]$ ls -l
- Get object ID for the table you need to restore
[nz@netezza md]$ nzrestore -dir /tmp -db MYDB -backupset 20191127093609 -contents
- List the data file available for that object ID
[nz@netezza md]$ nzrestore -dir /tmp -db MYDB -backupset 20191127093609 -extract
- Extract data file to local filesystem
[nz@netezza data]$ nzrestore -dir /tmp -db MYDB -backupset 20191127093609 -extract data/200290.full.1.1
- Replace the current backup data file (of the empty table) with the extracted data file. Note the object IDs are different so this needs to match the backupset metadata
mv 200290.full.1.1 212183.full.1.1
- Now you can restore the table to the new database from the manually updated backupset
[nz@netezza data]$ nzrestore -dir /tmp -db MYDB_RESTORE -tables MYTABLE -backupset 20191129100508 -droptables
Expect this process to take between 1 and 2 days.