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:
Copy
```
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:

172.16.200.446 as the source environment dinosaur as the source database
172.16.200.447 as the target environment vdino as the target database

Linking a dSource

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

Log into 172.16.200.446 as the environment user.
Create a physical directory on the source environment. $ mkdir /work/extdata

Create a directory in Oracle.

Copy

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

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

Copy

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

Create an external table exttab.

Copy

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

Query the table.

Copy

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

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

Provision vdino from Dinosaur.

Modify the directory extdata in vdino

Log into the target environment 172.16.200.447
Set SID to vdino
Copy
```
$ export ORACLE_SID=vdino
```

A query to exttabwill fail.

Copy

$ 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

Modify directory to the new location.

Copy

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

Query exttabagain.

Copy

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