Virtual Access on Database Backup
You can create a virtual access to a database backup to recover data. This method is especially useful and quick when you want to recover only certain objects or small tables from a backup.
This section provides you with information on how to create a virtual access to a database backup and recover data.
- Backup must be online (local archive volume), as virtual access is not supported for remote backups.
- Sufficient free disk space for an EXAStorage Data volume (used for metadata)
- Unused main memory (DB RAM)
- Short duration of downtime in case you do not have sufficient DB RAM and you need to reduce the DB RAM to create a virtual instance
- The number of active nodes in the virtual instance must be the same as the number of nodes in the backup
- Ensure the port for the virtual instance is open, for example, use TCP 9563 (check your firewall settings)
- Recommend at least 4 GiB per node for small amount of data and a maximum of 5% of the node’s memory
The amount of virtual DB RAM depends on the amount of data to be restored.
To create a virtual instance, you need to make sure there is sufficient DB RAM available for it. You can calculate the DB RAM required for a virtual instance using this formula:
Number of Active Nodes * 4 GiB = DB RAM for Virtual Instance
For examples, let us consider the following scenario
- A database is running on 4 active nodes and has 112 GiB of DB RAM
- Each node has 31 GiB of main memory
- The amount of data is very small (small tables), thus 4 GiB DB RAM per node is enough.
Going by the formula, the DB RAM required for the virtual instance is
4 * 4 GiB = 16 GiB DB RAM
Step 1: Reduce / Adjust DB RAM
If your database is configured to use all the available memory (DB RAM), then you need to decrease the DB RAM before you create a virtual instance.
To calculate how much DB RAM you need to reduce or adjust, use this formula:
DB RAM - DB RAM Virtual Instance = Reduced DB RAM
If you consider the values mentioned in the example for Calculate DB RAM for Virtual Instance,
112 GiB - 16 GiB = 96 GiB
You need to reduce your DB RAM to 96 GiB to be able to create a virtual instance.
Follow the below steps to reduce / adjust DB RAM:
- Shutdown the database.
- Once the database is shutdown, select the database and click Edit.
- Set the DB RAM to the desired value (for example, 96 GiB).
- Click Apply to save the changes.
- Start the database.
You need to create a data volume for the virtual instance to store the data recovered from the database backup. Follow these steps to create a data volume for the virtual instance:
Make a note of the number of active nodes for the database. In the EXASolution Instance page, click the database name and see Required Nodes.
- In EXAoperation, go to Services > EXAStorage and click Add Volume.
- Enter the properties for the new node, and set the Volume Type to Data.
- Configure the other volume properties. For examples:
- Redundancy: 1
- Allowed users: Admin
- Read-only Users: None
- Priority: 10
- Volume Type: Data
- Volume Size: 20 GiB
- Nodes List: Same as the number of active database nodes
- Block Size: None
- Disk: d03_storage
- Click Add to create the data volume.
Step 3: Create Virtual Database Instance
Next, you need to create a database instance that uses the data volume created in the previous step. The connection port for this database must be different from the port used by other databases. For example, use port 9563.
Follow these steps to create a virtual database instance:
- In EXAoperation, go to Services > EXASolution and click Add.
- Configure the database properties. For examples:
- DB Name: Name for the virtual database instance
- Active Nodes: Same as the number on the active database nodes
- Node List: Select all the nodes the database will use. Must be same as the active database nodes.
- EXAStorage Data Volume: Choose the data volume created in step 2.
- Network Interfaces: Same the active database
- Connection Port: This must be different to the other database port. For examples, use Port 9563
- DB RAM: Based on the require DB RAM for virtual instance. Refer to Calculate DB RAM for Virtual Instance.
- Click Add to create the virtual database instance.
- Click on the database instance you added, and then select Create from the Actions list box and click Submit to create the database instance.
You do not have to start the database at this point.
Step 4: Select Backup to Restore
Select the backup from which you would like to restore data. This backup must be a local backup (online backup).
- Select the newly created database.
- Click Backups.
- In the EXASolution Database Backup List screen, click Show foreign database backups. This displays backups from the other databases.
- Select the backup from which you want to restore data.
- Select the Restore Type to Virtual access from the drop-down list.
- Click Restore to start the restore process. This will automatically start this database.
The Expiration date field must be left empty for a virtual access restore. If there is a date entered, please clear the date.
Step 5: Import / Export Data
You can connect to an SQL client of your choice and start to import or restore data. The following are sample scripts to import and export data.
CREATE OR REPLACE TABLE SCHEMA.TABLE AS
SELECT * FROM ( import from EXA at 'CONNECTION-STRING:PORT' USER 'myuser' IDENTIFIED BY "mypass" table SCHEMA.TABLE );
INTO EXA at 'CONNECTION-STRING:PORT' USER "myuser" IDENTIFIED BY "mypass" TABLE SCHEMA.TABLE;
Step 6: Cleanup Virtual Database Instance
Once you have the required data imported, you can delete the database created and the data volume. Follow the below steps to do so:
- From the EXASolution Instance screen, shutdown the virtual database instance.
- Select the check box next to it and select Delete.
- Select EXAStorage under Services, and then select check box next to the data volume created for the virtual instance.
- Select Delete.
- Next, increase the DB RAM of your production / active database RAM if you need.