Recover dropped tables with Virtual Access Restore in #Exasol

Uwe Hesse's picture

The technique to recover only certain objects from an ordinary backup is called Virtual Access Restore. Means you create a database from backup that contains only the minimum elements needed to access the objects you request. This database is then removed afterwards.

Let’s see an example. This is my initial setup:

EXAoperation Database pagehttps://uhesse.files.wordpress.com/2019/02/virt_access1.png?w=1240&h=270 1240w, https://uhesse.files.wordpress.com/2019/02/virt_access1.png?w=150&h=33 150w, https://uhesse.files.wordpress.com/2019/02/virt_access1.png?w=300&h=65 300w, https://uhesse.files.wordpress.com/2019/02/virt_access1.png?w=768&h=167 768w, https://uhesse.files.wordpress.com/2019/02/virt_access1.png?w=1024&h=223 1024w" sizes="(max-width: 620px) 100vw, 620px" />

One database in a 2+1 cluster. Yes it’s tiny because it lives on my notebook in VirtualBox. See here how you can get that too.

It uses the data volume v0000 and I took a backup into the archive volume v0002 already.

EXAoperation volumeshttps://uhesse.files.wordpress.com/2019/02/virt_access2.png?w=1238&h=480 1238w, https://uhesse.files.wordpress.com/2019/02/virt_access2.png?w=150&h=58 150w, https://uhesse.files.wordpress.com/2019/02/virt_access2.png?w=300&h=116 300w, https://uhesse.files.wordpress.com/2019/02/virt_access2.png?w=768&h=298 768w, https://uhesse.files.wordpress.com/2019/02/virt_access2.png?w=1024&h=397 1024w" sizes="(max-width: 620px) 100vw, 620px" />

I have a schema named RETAIL there with the table SALES:

RETAIL.SALEShttps://uhesse.files.wordpress.com/2019/02/virt_access3.png?w=150&h=61 150w, https://uhesse.files.wordpress.com/2019/02/virt_access3.png?w=300&h=121 300w, https://uhesse.files.wordpress.com/2019/02/virt_access3.png 649w" sizes="(max-width: 620px) 100vw, 620px" />

By mistake, that table gets dropped:

drop tablehttps://uhesse.files.wordpress.com/2019/02/virt_access4.png?w=150 150w, https://uhesse.files.wordpress.com/2019/02/virt_access4.png?w=300 300w" sizes="(max-width: 430px) 100vw, 430px" />

And I’m on AUTOCOMMIT, otherwise this could be rolled back in Exasol. Virtual Access Restore to the rescue!

First I need another data volume:

second data volumehttps://uhesse.files.wordpress.com/2019/02/virt_access5.png?w=1240&h=240 1240w, https://uhesse.files.wordpress.com/2019/02/virt_access5.png?w=150&h=29 150w, https://uhesse.files.wordpress.com/2019/02/virt_access5.png?w=300&h=58 300w, https://uhesse.files.wordpress.com/2019/02/virt_access5.png?w=768&h=149 768w, https://uhesse.files.wordpress.com/2019/02/virt_access5.png?w=1024&h=198 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Notice the size of the new volume: It is smaller than the overall size of the backup respectively the size of the “production database”! I did that to prove that space is not much of a concern here.

Then I add a second database to the cluster that uses that volume. The connection port (8564) must be different from the port used by the first database and the DB RAM in total must not exceed the licensed size, which is limited to 4 GB RAM in my case:

second databasehttps://uhesse.files.wordpress.com/2019/02/virt_access6.png?w=1238&h=296 1238w, https://uhesse.files.wordpress.com/2019/02/virt_access6.png?w=150&h=36 150w, https://uhesse.files.wordpress.com/2019/02/virt_access6.png?w=300&h=72 300w, https://uhesse.files.wordpress.com/2019/02/virt_access6.png?w=768&h=184 768w, https://uhesse.files.wordpress.com/2019/02/virt_access6.png?w=1024&h=245 1024w" sizes="(max-width: 620px) 100vw, 620px" />

I did not start that database because for the restore procedure it has to be down anyway. Clicking on the DB Name and then on the Backups button gets me here:

Foreign database backupshttps://uhesse.files.wordpress.com/2019/02/virt_access7.png?w=150&h=59 150w, https://uhesse.files.wordpress.com/2019/02/virt_access7.png?w=300&h=117 300w, https://uhesse.files.wordpress.com/2019/02/virt_access7.png?w=768&h=300 768w, https://uhesse.files.wordpress.com/2019/02/virt_access7.png 979w" sizes="(max-width: 620px) 100vw, 620px" />

No backup shown yet because I didn’t take any backups with exa_db2. Clicking on Show foreign database backups:

Backup choicehttps://uhesse.files.wordpress.com/2019/02/virt_access8.png?w=1238&h=424 1238w, https://uhesse.files.wordpress.com/2019/02/virt_access8.png?w=150&h=51 150w, https://uhesse.files.wordpress.com/2019/02/virt_access8.png?w=300&h=103 300w, https://uhesse.files.wordpress.com/2019/02/virt_access8.png?w=768&h=263 768w, https://uhesse.files.wordpress.com/2019/02/virt_access8.png?w=1024&h=351 1024w" sizes="(max-width: 620px) 100vw, 620px" />

The Expiration date must be empty for a Virtual Access Restore, so I just remove it and click Apply. Then I select the Restore Type as Virtual Access and click Restore:

Virtual Access Restorehttps://uhesse.files.wordpress.com/2019/02/virt_access9.png?w=1240&h=414 1240w, https://uhesse.files.wordpress.com/2019/02/virt_access9.png?w=150&h=50 150w, https://uhesse.files.wordpress.com/2019/02/virt_access9.png?w=300&h=100 300w, https://uhesse.files.wordpress.com/2019/02/virt_access9.png?w=768&h=257 768w, https://uhesse.files.wordpress.com/2019/02/virt_access9.png?w=1024&h=342 1024w" sizes="(max-width: 620px) 100vw, 620px" />

This will automatically start the second database:

Two databases in one clusterhttps://uhesse.files.wordpress.com/2019/02/virt_access10.png?w=1240&h=308 1240w, https://uhesse.files.wordpress.com/2019/02/virt_access10.png?w=150&h=37 150w, https://uhesse.files.wordpress.com/2019/02/virt_access10.png?w=300&h=74 300w, https://uhesse.files.wordpress.com/2019/02/virt_access10.png?w=768&h=190 768w, https://uhesse.files.wordpress.com/2019/02/virt_access10.png?w=1024&h=254 1024w" sizes="(max-width: 620px) 100vw, 620px" />

I connect to exa_db2 with EXAplus, where the Schema Browser gives me the DDL for the table SALES:

ExaPlus Schema Browser get DDLhttps://uhesse.files.wordpress.com/2019/02/virt_access11.png?w=150&h=79 150w, https://uhesse.files.wordpress.com/2019/02/virt_access11.png?w=300&h=157 300w, https://uhesse.files.wordpress.com/2019/02/virt_access11.png?w=768&h=402 768w, https://uhesse.files.wordpress.com/2019/02/virt_access11.png?w=1024&h=536 1024w, https://uhesse.files.wordpress.com/2019/02/virt_access11.png 1041w" sizes="(max-width: 620px) 100vw, 620px" />

I take that to exa_db1 and run it there, which gives me the table back but empty. Next I create a connection from exa_db1 to exa_db2 and import the table

create connection exa_db2 
to '192.168.43.11..13:8564' 
user 'sys' identified by 'exasol';

import into retail.sales 
from exa at exa_db2 
table retail.sales;

This took about 2 Minutes:

Importhttps://uhesse.files.wordpress.com/2019/02/virt_access12.png?w=150 150w, https://uhesse.files.wordpress.com/2019/02/virt_access12.png?w=300 300w" sizes="(max-width: 362px) 100vw, 362px" />

The second database and then the second data volume can now be dropped. Problem solved!

 

To prevent automated spam submissions leave this field empty.