Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-388

Virtual-access on database backups

    XMLWordPrintable

    Details

    • Solution:
      Hide

      Scenario

      • Extract tables/data from backup
      • In case the database is configured to use all available memory, the DB RAM needs to be decreased before creating a VR instance. If your system has enough free memory available proceed with step 2.
      • Reducing the DB RAM requires a short downtime of the production database

      Prerequisites

      • Online backup, VR access is not supported for remote backups.
      • Enough free disk space for an EXAStorage DATA volume (used for metadata)
      • Unused Main Memory (DB RAM)
      • The VR instance needs the same amount of active nodes as the backup
      • VR instance database port, e.g. TCP 9563 (check your firewall!)
        • Recommendation at least 4GiB per node for a small amount of data and max. 5% of the nodes memory
        • Amount of VR DB RAM depends on the amount of data to be restored

      Calculate DB RAM for the VR instance:

      In this example:

      • each node has 31GiB of Main Memory
      • The amount of data is very small (small table), thus 4GiB DB RAM per node are sufficient (see Prerequisites)
      • The database is running on 4 active nodes and has 112GiB DB RAM

      Amount of active nodes * 4GiB = DB RAM VR instance

      4 * 4GiB = 16GiB DB RAM VR instance

      1. Reducing the DB RAM to allocate RAM for the VR instance

      DB RAM - DB RAM VR instance = reduced DB RAM

      112GiB - 16GiB = 96GiB

      1.2 Shutdown database

      1.3 Edit/Adjust DB RAM

      Wait for the database to be shut down. Edit database settings:

      • Click on the "Edit" Button
      • Set the DB RAM to 96GiB
      • Apply changes
      • Start the database

      2. Create DATA volume for the VR instance

      • Go to the database properties

      • Note down amount of active database nodes

      2.1 Create EXAStorage DATA volume
      • Click on EXAStorage and then click on "Add Volume"

      • Redundancy 1
      • Allowed Users e.g. Admin
      • Read-only Users: None
      • Priority 10
      • Volume Type: DATA
      • Volume Size: 20GiB
      • Nodes List: Active database nodes
      • Number of Master Nodes: Same amount as active database nodes
      • Block Size: None
      • Disk: e.g. d03_storage

      3. Create VR database instance

      • EXASolution - Add
      • DB name: VR
      • Active Nodes: Same amount as active database nodes
      • Node List: Active database nodes
      • EXAStorage DATA Volume: Choose the previous created volume ID
      • Network Interfaces: Same as the active database
      • Connection Port: e.g. 9563 it must differ from production
      • DB RAM: 16GiB

      4. Select the Backup to Restore

      • Click on the newly created database
      • Click on "Backups"
      • Click on "Show foreign database backups"
      • Select backup you want to restore Ensure backup dependencies are fulfilled

      • Restore Type: Virtual Access
      • Click on Restore, this will start the VR instance
      • Wait for the database to become online

      5. Import/Export data (Example)

      IMPORT:

          CREATE OR REPLACE TABLE SCHEMA.TABLE AS
          SELECT *
          FROM ( import from EXA at 'CONNECTION-STRING:PORT' USER 'myuser' IDENTIFIED BY "mypass" table SCHEMA.TABLE );
      

      EXPORT:

          EXPORT SCHEMA.TABLE
          INTO EXA at 'CONNECTION-STRING:PORT' USER "myuser" IDENTIFIED BY "mypass" TABLE SCHEMA.TABLE;
      

      6. Cleanup VR database instance

      • Shutdown VR instance
      • Delete VR instance
      • Delete EXAStorage DATA volume of the VR instance
      • Afterwards increase DB RAM for production
      Show
      Scenario Extract tables/data from backup In case the database is configured to use all available memory, the DB RAM needs to be decreased before creating a VR instance. If your system has enough free memory available proceed with step 2. Reducing the DB RAM requires a short downtime of the production database Prerequisites Online backup, VR access is not supported for remote backups. Enough free disk space for an EXAStorage DATA volume (used for metadata) Unused Main Memory (DB RAM) The VR instance needs the same amount of active nodes as the backup VR instance database port, e.g. TCP 9563 (check your firewall!) Recommendation at least 4GiB per node for a small amount of data and max. 5% of the nodes memory Amount of VR DB RAM depends on the amount of data to be restored Calculate DB RAM for the VR instance: In this example: each node has 31GiB of Main Memory The amount of data is very small (small table), thus 4GiB DB RAM per node are sufficient (see Prerequisites ) The database is running on 4 active nodes and has 112GiB DB RAM Amount of active nodes * 4GiB = DB RAM VR instance 4 * 4GiB = 16GiB DB RAM VR instance 1. Reducing the DB RAM to allocate RAM for the VR instance DB RAM - DB RAM VR instance = reduced DB RAM 112GiB - 16GiB = 96GiB 1.2 Shutdown database 1.3 Edit/Adjust DB RAM Wait for the database to be shut down. Edit database settings: Click on the "Edit" Button Set the DB RAM to 96GiB Apply changes Start the database 2. Create DATA volume for the VR instance Go to the database properties Note down amount of active database nodes 2.1 Create EXAStorage DATA volume Click on EXAStorage and then click on "Add Volume" Redundancy 1 Allowed Users e.g. Admin Read-only Users: None Priority 10 Volume Type: DATA Volume Size: 20GiB Nodes List: Active database nodes Number of Master Nodes: Same amount as active database nodes Block Size: None Disk: e.g. d03_storage 3. Create VR database instance EXASolution - Add DB name: VR Active Nodes: Same amount as active database nodes Node List: Active database nodes EXAStorage DATA Volume: Choose the previous created volume ID Network Interfaces: Same as the active database Connection Port: e.g. 9563 it must differ from production DB RAM: 16GiB 4. Select the Backup to Restore Click on the newly created database Click on "Backups" Click on "Show foreign database backups" Select backup you want to restore Ensure backup dependencies are fulfilled Restore Type: Virtual Access Click on Restore, this will start the VR instance Wait for the database to become online 5. Import/Export data (Example) IMPORT: CREATE OR REPLACE TABLE SCHEMA . TABLE AS SELECT * FROM ( import from EXA at ' CONNECTION - STRING :PORT' USER 'myuser' IDENTIFIED BY "mypass" table SCHEMA . TABLE ); EXPORT: EXPORT SCHEMA . TABLE INTO EXA at ' CONNECTION - STRING :PORT' USER "myuser" IDENTIFIED BY "mypass" TABLE SCHEMA . TABLE ; 6. Cleanup VR database instance Shutdown VR instance Delete VR instance Delete EXAStorage DATA volume of the VR instance Afterwards increase DB RAM for production
    • Category 1:
      Cluster Administration - DB Instance Management
    • Category 2:
      Cluster Administration - Backup

      Attachments

      1. AddEXAStorage.png
        AddEXAStorage.png
        46 kB
      2. CreateStorage.png
        CreateStorage.png
        42 kB
      3. CreateVRDatabase.png
        CreateVRDatabase.png
        39 kB
      4. NodeRam.png
        NodeRam.png
        31 kB
      5. reCreateDB.png
        reCreateDB.png
        39 kB
      6. RestoreableBackups.png
        RestoreableBackups.png
        62 kB
      7. RestoreBackups.png
        RestoreBackups.png
        68 kB
      8. SelectDB.png
        SelectDB.png
        11 kB
      9. ShutdownDB.png
        ShutdownDB.png
        22 kB
      10. ViewRequiredNodes.png
        ViewRequiredNodes.png
        30 kB

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: