What Are Virtual Warehouses?

A virtual warehouse is an Oracle database that is created through a Delphix Engine and has no antecedents. Virtual warehouses allow you to move/copy/clone parts of one Oracle database to another. As a result, the virtual warehouse sits on Delphix storage and is used as a container to bring different sources together into a single instance for processing. The illustration below visualizes how two databases are linked to Delphix and are moved to a virtual data warehouse consolidating A and B sources.

Use Cases

Virtual warehouses allow you to consolidate databases for a variety of use cases related to business analytics, including:   

• Create a brand-new Oracle database sitting on Delphix storage for end-of-quarter reporting. You can bring together production databases spread geographically or in different instances, consolidate them into one instance for reporting purposes.

• Compare multiple points of time from a collection of the same databases as a time series comparison.

• Populate virtual warehouses from a variety of other Oracle database sources, whether linked to the system or provisioned as virtual databases (VDBs). In this way, you can test third-party integrations together with production data.

How Do Virtual Warehouses Work?

Co-hosting multiple Oracle databases in a single instance becomes possible through a bulk export and import operation that brings the data from one database into another. As a result, all data is joined and accessible in the same database. The image below illustrates the feature functionality using NFS Mount Points. In this example, the warehouse mount point named /mnt/provision/MyWarehouse is used to house system datafiles and online redo log files as sources that can be used for the warehouse, whereas the other mount points will house the user data from multiple sources such as tablespaces from additional databases. An example of another database source with a separate mount point is /mnt/provision/MyWarehouse_ORACLE_DB_CONTAINER-40.

Getting Started

Prerequisites and Restrictions

Creating a virtual warehouse as an environment requires Oracle 10 through 12 Enterprise Edition binaries.

• • User data in the Source databases must not have dependencies on system tablespaces.
    
  • There is no multitenant 12c support. Data dependency restrictions are similar to cross-platform provisioning and there is no automatic namespace collision resolution. However, you can use prefixes to avoid collisions between users/schemas and tablespaces independently. 

You might need to use prefixes for name conflict resolution. For example, in the following scenario there exists a warehouse containing the user CRM. The CRM user uses the following tablespaces:

• CRM_IDX
• CRM_DATA

If you want to populate the warehouse with an additional source containing the user ERP and the tablespaces CRM_IDX and CRM_DATA, you can avoid this namespace collision by providing a tablespace prefix, rendering the final names in the warehouse <PREFIX>CRM_IDX and <PREFIX>CRM_DATA, while leaving the user name intact. Conversely, the same operation is supported for user names and is independent of the tablespace conflict resolution mechanism. 

Activate Feature

Login as a sysadmin in the command line interface (CLI). Run the following CLI commands:

1. cd system
2. enableWarehouseFeature
3. commit
4. restart
5. commit

Creating a Virtual Warehouse

Creating the virtual warehouse will create a brand-new database instance running in your selected environment on Delphix storage. Once created, you can access it using normal Oracle tools such as sql*plus or your JDBC client of choice.

1. Click Manage.
2. Select Create Virtual Warehouse.
3. The Create Oracle Warehouse wizard will appear.
4. Enter Warehouse Name, System Password, Database Credentials, and DB Unique Name. Be sure to select the appropriate version of the warehouse for the databases you will add to it. The same compatibility criteria that is used for normal Oracle databases applies to Virtual Warehouses. Oracle sources of the same version or lower can be populated into the warehouse, but never the other way around. 
5.  Enter the appropriate Character Set and select the National Character Set.
   The character set you choose will determine what source databases you can add to the warehouse. All source databases must have the same character set as the warehouse or have a character set that is a binary subset of the warehouse's character set. Refer to Oracle documentation for character set information.
6. For warehouses 11.2 and above, also specify the Timezone Version.
   You cannot add 11.2.0.1, 11.2.0.2 and 11.2.0.3 databases unless they are using the same timezone file version; 11.2.0.4 will allow different timezone file versions as long as the database does not have TIMESTAMP WITH TIMEZONE columns. You can also customize the name and size of the SYSTEM data files of the warehouse by clicking advanced settings.  
7. Click Next.    
8.  Select the Environment and Users. Pay special attention to the oracle home selection, if the warehouse version was selected as 11g in the first screen, and there are 11.1 and 11.2 environments, make sure you pick the right environment.  
9. Click Next.  

10. Customize the .ora properties. Memory settings are controlled through the config template as well. Ensure that you leave enough memory for the sources that you intend to populate and consolidate into the warehouse. Consider other resources as well, as the warehouse will house data from many databases. If you do not specify the compatible initialization parameter, it will use the default value of the Oracle version. 

    Databases with the same compatible version or lower can be populated into the warehouse, but never the other way around.

    Default values

    For 10.2, the default value is 10.2.0.0.

    For 11.1, the default value is 11.0.0.0.

    For 11.2, the default value is 11.2.0.0.

    
    
    

11. Select the group to which the warehouse will belong. A new group can be created by using the green plus icon in the wizard. Click Next again on step 5 of the wizard.  

12.  If needed, configure your warehouse with hooks. The functionality works like a VDB. For more information about hooks, see Oracle Hook Operation Types.

13.  Click Next.

14.  Click Next again on step 6 of the wizard.  

15.  Click Create  to create the Warehouse.  

You can follow the progress of the virtual warehouse creation job by opening the Actions sidebar panel. After completion, the virtual warehouse card will show a status indicating that the warehouse is running.

Adding Data to a Virtual Warehouse

Adding data to a consolidated warehouse populates the contents of one database (a dSource or VDB) into a warehouse. Before adding a database to the virtual data warehouse, ensure that an environment identical to the databases being added has been marked with Use as Staging. See Designating a Staging Host.

Exporting users and tablespaces from a compatible source database (either dSource or VDB) happens automatically. You can check that the data does hot have SYSTEM tablespace dependencies by running the following:

1. Run the stored procedure sys.dbms_tts.transport_set_check and pass it all non-system tablespaces in the database. For example, for a database with two user tablespaces CRM_DATA and CRM_IDX, the command will look like the following:

   • sqlplus / as sysdba

   • SQL> execute sys.dbms_tts.transport_set_check('CRM_DATA, CRM_IDX', true, true);
2. In the same sqlplus session where the procedure was called, select violations from sys.transport_set_violations.
3. If any message is returned, it will not be possible to add the database to the warehouse.

 Procedure to Add Data

1. On the left hand side panel under databases groups, locate the virtual warehouse card.

2. Click the green plus next to Added Databases.

3. Open the Virtual Warehouse wizard. A popup will appear to allow you to add a database to the warehouse.

4. In the Add Database to Warehouse wizard, select a source and a point in time. In this window, you can also handle namespace conflicts through prefixing. Select schema and tablespace prefixes if necessary so the source will not create a namespace conflict. Any schema name or tablespace name collision with the warehouse's current schemas/tablespaces will prevent the database from being added to the warehouse.

   Schema Prefixes and Passwords

   If a Schemas Prefix is used for a 10g database being added to the Warehouse (of any version), or for an 11g or above database being added to an 11g or above Warehouse with sec_case_sensitive_logon set to false, the password must manually be changed for the schema on the Warehouse once the operation is complete.

   For example, if an 11gR2 database contains a user named SCOTT that is brought into an 11gR2 Warehouse, and the Schemas Prefix field is set to "PROD", you would need to change the PRODSCOTT password on the target Warehouse after the operation is complete using the command: ALTER USER prodscott IDENTIFIED BY password_here;

5. Click OK.  

   The functionality acts just like a VDB. View progress under the Actions sidebar. The image below shows the Delphix Engine exporting the source metadata and migration from storage.

6. When the wizard completes, it automatically ends the process by taking a snapshot. You will see your new sources in the Timeflow. In the snapshot cards, you can see how many databases have been added to the warehouse.