Handling Oracle Files created outside of the Delphix Filesystem (KBA1133)

Issue

SnapSync fails on an Oracle virtual database (VDB) due to files existing outside of the Delphix File system.

Troubleshooting

This can be verified by logging intoSQLPLUS and reviewing the datafile locations

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/mnt/provision/VPTE_AD4/datafile/+EXT/PTEST/DATAFILE/system.592.909630307
/mnt/provision/VPTE_AD4/datafile/+EXT/PTEST/DATAFILE/x1.708.910846319
...
+EXT/VPTE_AD4/DATAFILE/users.969.913427077

206 rows selected.

SQL>

Note that most datafiles are listed correctly in the Delphix filesystem "/mnt/provision/VPTE_AD4/datafile", however there is at least 1 datafile that has been provisioned onto ASM, "+EXT/VPTE_AD4/DATAFILE/users.969.913427077"

Resolution

Prior to being able to perform a successful Snapshot on this VDB, the datafiles need to be moved back onto the Delphix filesystem.

For Oracle <=12.1.x

These operations require the database to be offline. If this is a RAC database, ensure all instances are shutdown.

1. Shutdown the instance and restart in mount mode 

   SQL> startup mount;
   ORACLE instance started.
   
   Total System Global Area  838860800 bytes
   Fixed Size		    2929936 bytes
   Variable Size		  662702832 bytes
   Database Buffers	  167772160 bytes
   Redo Buffers		    5455872 bytes
   Database mounted.
   SQL> exit

2. Use RMAN to copy the datafile onto the filesystem 

   $ rman target=/
   
   Recovery Manager: Release 12.1.0.2.0 - Production on Thu Jun 2 02:27:07 2016
   
   Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
   connected to target database: VPTE_AD4 (DBID=1828962445, not open)
   
   RMAN> copy datafile '+EXT/VPTE_AD4/DATAFILE/users.969.913427077' to '/mnt/provision/VPTE_AD4/datafile/+EXT/PTEST/DATAFILE/users.969.913427077';
   
   Starting backup at 02-JUN-16
   using target database control file instead of recovery catalog
   allocated channel: ORA_DISK_1
   channel ORA_DISK_1: SID=1 instance=VPTEAD41 device type=DISK
   channel ORA_DISK_1: starting datafile copy
   input datafile file number=00206 name=+EXT/VPTE_AD4/DATAFILE/users.969.913427077
   output file name=/mnt/provision/VPTE_AD4/datafile/+EXT/PTEST/DATAFILE/users.969.913427077 tag=TAG20160602T022717 RECID=1 STAMP=913429639
   channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
   Finished backup at 02-JUN-16
   
   RMAN>exit;

3. Use SQLPLUS to rename the file 

   SQL> alter database rename file '+EXT/VPTE_AD4/DATAFILE/users.969.913427077' to '/mnt/provision/VPTE_AD4/datafile/+EXT/PTEST/DATAFILE/users.969.913427077';
   
   Database altered.
   
   SQL>

4. Open the database (if the database is RAC, then also restart the other instances).

   SQL> alter database open;
   
   Database altered.
   
   SQL>

5. Wait for the Delphix Engine GUI to register that the database is online, then manually initiate a SnapSync via GUI using the camera icon. This should now successfully complete.
   

For Oracle >=12.2.x

With Oracle 12.2 and greater you can make use of the ALTER DATABASE MOVE DATAFILE statement.

1. Connect to the database via sqlplus.

# sqlplus / as sysdba

2. Move the Datafile to a location within the Delphix Filesystem eg.

SQL> alter database move datafile '+EXT/VPTE_AD4/DATAFILE/users.969.913427077' to '/mnt/provision/VPTE_AD4/datafile/+EXT/PTEST/DATAFILE/users.969.913427077';

Database altered.

SQL>

3. Run a manual SnapSync to test it is successful.