Virtual Access on Backup

This article explains how to configure virtual access to a database backup.

You can access a database backup using a virtual database instance. This method is especially useful when you want to quickly recover specific objects from a backup without doing a full restore.

Virtual database instances do not count toward the license limit.

Prerequisites

  • The backup must be stored in a local archive volume (online backup). Virtual access is not supported for remote backups.

  • The backup must not have an expire date.

  • You must have sufficient free disk space for an EXAStorage Data volume (used for metadata).

  • You must have sufficient free main memory in your database for the virtual instance. If the database is configured to use all available memory, you must prepare for a short downtime while adjusting DB RAM. For more details, see Calculate DB RAM .

  • Your network settings must allow traffic on the port that you configure for the virtual instance.

Calculate DB RAM 

You must have sufficient DB RAM available to be able to create the virtual instance that you will use to access the backup. To calculate the DB RAM that will be required for the virtual instance, use this formula: 

Number of active nodes * 4 GiB = DB RAM for virtual instance
Example:
  • The 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), which means 4 GiB DB RAM per node is enough.

Using our formula, the DB RAM required for the virtual instance is: 

4 * 4 GiB = 16 GiB DB RAM

If your database is configured to use all the available memory as DB RAM, you will not be able to create a virtual instance. You must therefore decrease the amount of DB RAM used by the database. To calculate how much you need to reduce it, use this formula: 

DB RAM - Virtual Instance DB RAM = Reduced DB RAM

Using the values from our example, you must reduce the DB RAM used by the database to 96 GiB to be able to create a virtual database instance: 

112 GiB - 16 GiB = 96 GiB

To adjust DB RAM you must stop and restart the database, which means a short downtime is required.

Set up virtual access

Step 1: Adjust DB RAM (if needed)

  1. Stop the database.
  2. Once the database is stopped, select the database and click on Edit.

  3. Set the DB RAM to the desired value (for example, 96 GiB).
  4. Click on Apply to save the changes.
  5. Start the database.

Step 2: Create a data volume for the virtual instance

You must create a data volume for the virtual database instance. This volume will be used to temporarily store data during the restore process.

The number of active nodes in the virtual instance must be exactly the same as the number of nodes in the backup. To see the number of active nodes for the database, click on the database name on the EXASolution Instance page. The number of nodes is shown in Required Nodes.

  1. In EXAoperation, go to Services > EXAStorage and click Add Volume.

  2. Enter the properties for the new node, and set the Volume Type to Data.
  3. 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
  4. Click on Add to create the data volume.

Step 3: Create a virtual database instance

The next step is to create a virtual database instance that uses the data volume that you created in the previous step.

  • The connection port for the virtual database must be different from the port used by other databases. In this example, we use port 9563 for the virtual database instance.

  • The number of nodes in the virtual instance must be exactly the same as the number of active nodes in the backup.

  • The required amount of virtual DB RAM depends on the amount of data to be restored. We recommend minimum 4 GiB per node for a small amount of data, and maximum 5% of the memory capacity of the node. See also Calculate DB RAM .

  1. In EXAoperation, go to Services > EXASolution and click on Add.

  2. Configure the database properties. For example: 
    • 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 required DB RAM for the virtual instance
  3. Click on Add to create the virtual database instance.
  4. Click on the database instance you added, and then select Create from the Actions list box and click on Submit to create the database instance.

You do not have to start the database at this point.

Step 4: Select the backup to restore

Select the backup from which you want to restore data. The backup must be a local backup (online backup).

  1. Select the newly created database.

  2. Click on Backups.
  3. In the EXASolution Database Backup List screen, click on Show foreign database backups. This displays backups from the other databases.

  4. Select the backup from which you want to restore data.

    5. The Expiration date field must be empty for a virtual access restore. If there is a date entered, clear the date.

  5. Select the Restore Type to Virtual access from the drop-down list.

Step 5: Start the restore process

In the EXASolution Database Backup List screen, click on Restore to start the restore process. This will automatically start the database.

Import/export data

You can now connect to the virtual database instance using an SQL client and start to import or restore data.

Example: Import 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 );
Example: Export data
EXPORT SCHEMA.TABLE
INTO EXA AT 'CONNECTION-STRING:PORT' USER "myuser" IDENTIFIED BY "mypass" TABLE SCHEMA.TABLE;

Clean up

Once you have imported the required data, you can delete the virtual database and the data volume.

  1. From the EXASolution Instance screen, shutdown the virtual database instance.
  2. Select the check box next to it and select Delete.
  3. Select EXAStorage under Services, and then select check box next to the data volume created for the virtual instance.
  4. Select Delete.

After you have finished the cleanup step, you can increase the DB RAM in your production database again if needed. For more details on how to do this, see Step 1: Adjust DB RAM (if needed).

To adjust DB RAM you must stop and restart the database, which means a short downtime is required.