Exporting an Oracle dataset from a snapshot or a timeflow-point to a physical filesystem

Overview  

This article describes how to perform an export from a snapshot or a timeflow-point belonging to a non-multitenant database or pluggable database to create a physical database stored on a physical filesystem. No intermediate storage is needed; a temporary virtual pluggable database is provisioned, and the database files are moved directly from Delphix onto the physical filesystem.

This procedure can be used to export snapshots of an Oracle non-multitenant dSource or a VDB. It can also be used to export snapshots of a PDB dSource or a vPDB to a pluggable database in a linked CDB. The physical database is created on the filesystem.

This procedure can be performed using the CLI only and applies to all Oracle RDBMS Versions supported by Delphix. For CLI commands, refer to the CLI cookbook: export a snapshot or a Timeflow point of a non-multitenant Oracle database to ASM or Physical Filesystem and and the CLI cookbook: export a snapshot or a Timeflow point of a multitenant pluggable Oracle database to ASM or Physical Filesystem articles.

Furthermore, it is also fully supported with TDE-enabled databases.

Limitation

On V2P, to perform export to filesystem, select a directory location which supports direct IO operations.

Don’t use /tmp as this directory doesn’t support direct IO on all OSs.

If exporting to /tmp is unavoidable, set the following initialization parameter:

Copy 
ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=ASYNCH SCOPE=SPFILE;

With asynchronous I/O, the OS itself performs I/O on behalf of Oracle software in a highly efficient and parallel manner. OS buffer cache is used for I/O instead of Oracle’s own buffer cache within the SGA.

This value, however, may not be what Oracle recommends for this parameter.

Prerequisites  

  1. Sufficient storage space must be available on the filesystem for datafiles, tempfiles.

  2. While exporting from a PDB snapshot, the new PDB name:  

    1. must meet all the naming constraints as defined in the Oracle documentation.  

    2. must be different from the existing PDBs in the target CDB.   

  3. While exporting a non-multitenant snapshot, the new database unique name:

    1. must meet all the naming constraints as defined in the Oracle documentation.

    2. must be different from the database unique name of any other database on the same host.

    3. must be different from the database unique name of any RAC database which has a node on this host even if the node is down.

  4. Offline tablespaces can exist; however, offline datafiles must not exist in an online tablespace.

  5. Export of a non-multitenant snapshot (or point-in-time) in a RAC environment must be performed as the Oracle software owner user account, otherwise the export will fail. This is required because Delphix issues srvctl commands to configure the resulting physical database in RAC and these commands can only be run with Oracle software owner user account privileges.

  6. When running export of non-multitenant or multitenant snapshot in a RAC environment, ensure that the states of all the cluster nodes are displayed as Enabled in the Delphix management GUI.

Procedure  

  1. Delphix provisions a temporary virtual database or pluggable database, from the provided snapshot or timeflow point, which would be converted in-place to a physical database.  

  2. Delphix takes a new snapshot of the temporary virtual database or pluggable database before starting export.  

  3. The virtual database or pluggable database is converted in-place to a physical database on filesystem. The temporary virtual source is then deleted.  

  4. Refer to CLI cookbook: export a snapshot or a Timeflow point of a non-multitenant Oracle database to ASM or Physical Filesystem or CLI cookbook: export a snapshot or a Timeflow point of a multitenant pluggable Oracle database to ASM or Physical Filesystem for the procedure.

Performance considerations before running the export  

  1. When deciding the number of RMAN channels to use, there are tradeoffs between speed and resource consumption on the host.

  2. The number of RMAN channels must not be more than the number of data files.

  3. Similar to selecting the number of RMAN channels to perform backup, if impact to other databases is not a concern, then setting the number of channels should be increased to the point of diminished returns. Otherwise, it is a compromise between what the system can handle and how fast we want the export to finish.

  4. By default, it is set to 8, but this value might be too large for some environments and should be adjusted down appropriately.