Accidentally added a tablespace datafile with the wrong name

Products

• Data Loss Prevention Enforce
• Data Loss Prevention Oracle Standard Edition 2

Issue/Introduction

User created a new datafile to extend the tablespace, and altered the correct tablespace, but associated it with the wrong datafile name:

ALTER TABLESPACE LOB_TABLESPACE ADD DATAFILE '<drive>:\ORACLE\PRODUCT\<version>\ORADATA\PROTECT\USERS03.DBF' SIZE 138240K REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M; 

Environment

To verify which file is attached to each tablespace, this query may be used:

SELECT file_name, tablespace_name FROM dba_data_files;

Resolution

To rename the datafile and associate it to the correct tablespace, use the following steps:

1. Stop the SymantecDLP (or Vontu for DLP 15.0 and earlier) services.
2. Login to the Oracle database as the "sys as sysdba" user
3. List the datafiles and their location:  SELECT name FROM v$datafile;
4. Shutdown the database - "shutdown immediate"

   • SQL> SHUTDOWN IMMEDIATE
     Database closed.
     Database dismounted.
     ORACLE instance shut down.

5. Rename the physical file in the Operating System
   • SQL> HOST MOVE <datafile path> (as listed in step 3 above) <misnamed datafile path & datafile> (as listed in step 3 above) 
   • Example: HOST MOVE C:\ORACLE\ORADATA\PROTECT\USERS01.DBF C:\ORACLE\ORADATA\PROTECT\LOB10.DBF
6. ​Startup the database: STARTUP MOUNT

   • ORACLE instance started.
     
     Total System Global Area  167772160 bytes
     Fixed Size                   787968 bytes
     Variable Size              61864448 bytes
     Database Buffers          104857600 bytes
     Redo Buffers                 262144 bytes
     Database mounted.

7. At the SQL prompt, type:  ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PROTECT\USERS01.DBF' TO 'C:\ORACLE\ORADATA\PROTECT\LOB10.DBF';
   • ​​Database altered
8. ​At the SQL prompt, type: ALTER DATABASE OPEN;
   • ​​Database altered
9. ​Repeat the query in step 3 above and make sure the datafile in question has been renamed.
10. Exit SQL
11. Restart the SymantecDLP services on Enforce.