Specifying external data directories for Oracle dSources and VDBs

This topic describes the process for including external data files with dSource snapshots and VDBs.

In the following places, you can specify the directory for any external data files that should be included with dSource snapshots:

• During the dSource linking process click on the Advanced section of the Data Management screen.

• After you have created the dSource, the path can be updated from CLI by setting the externalFilePath field in the dSource with this command:

  CODE

  source 'TEST_SOURCE' update *> set externalFilePath=/tmp

External file import for the Delphix engine and VDBs

The Delphix Engine will not fetch external tables or external data types such as BFILE. Instead, in order to link external data files to the source database and make it available to the Delphix Engine, you must create a directory in the file system and the database. Any data files in the directory you specify will be applied, recursively, to the dSource.

External data will be provisioned to each VDB that is created from this dSource. You will need to update the external file/data type definition to point to the new location after creating VDBs. Provisioning a VDB with external data creates a directory named external in the VDB mount point location.

Configuring the rsync command location for an environment

Files from the external data directory are fetched using the rsync command (via rsyncd) installed in the source environment. In order to SnapSync a dSource with an external data directory, rsync must be installed in the source environment. If rsync is installed in a non-standard location, the path to the rsync command can be configured in the Environment Details for the source environment on the Environment Management screen.

Example of attaching and redirecting external data files for Oracle databases

This example uses two environments:

1. 172.16.200.446 as the source environment dinosaur as the source database

2. 172.16.200.447 as the target environment vdino as the target database

Linking a dSource

1. Create an external data directory and an external data file, and attach the directory to the source database.
   

   1. Log into 172.16.200.446 as the environment user.

   2. Create a physical directory on the source environment.  $ mkdir /work/extdata

   3. Create a directory in Oracle.

      CODE

      $ sqlplus / as sysdba
        SQL> create or replace directory extdata as '/work/extdata';

   4. Create a text file /work/extdata/exttab.dat.

      CODE

      $ cat > /work/extdata/exttab.dat
          1, aaa
          2, bbb
          3, ccc
          ^C

   5. Create an external table exttab.

      CODE

      $ sqlplus / as sysdba
          SQL> create table exttab (id number, text varchar2(10))
            2  organization external (default directory extdata location('exttab.dat'));

   6. Query the table.

      CODE

      SQL> select * from exttab;
              ID TEXT
      ---------- ----------
               1  aaa
               2  bbb
               3  ccc

2. During the process of linking the dSource to the Dinosaur database, or in the dSource's Configuration tab after creating the link, enter /work/extdata in the External Data Directory field.

Provisioning a VDB

1. Provision vdino from Dinosaur.

2. Modify the directory extdata in vdino

   1. Log into the target environment 172.16.200.447

   2. Set SID to vdino

      CODE

      $ export ORACLE_SID=vdino

   3. A query to exttabwill fail.

      CODE

      $ sqlplus / as sysdba
          SQL> select * from exttab
      select * from exttab
      *
      ERROR at line 1:
      ORA-29913: error in executing ODCIEXTTABLEOPEN callout
      ORA-29400: data cartridge error
      KUP-04063: unable to open log file EXTTAB_23394.log
      OS error No such file or directory
      ORA-06512: at "SYS.ORACLE_LOADER", line 19

3. Modify directory to the new location.

   CODE

   SQL> create or replace directory extdata as '/mnt/provision/vdino/external';

4. Query exttabagain.

   CODE

   SQL> select * from exttab;
           ID TEXT
   ---------- ----------
            1  aaa
            2  bbb
            3  ccc