Hello Friends,
This post will go over online datafile movement, which was introduced in 12c and later.
The following actions can now be performed by Oracle 12C or greater while the datafile is online and being accessed:
Issue : BR0382E File/Directory name '/oracle/DBL/sapdata1/sysaux_1/sysaux01.dbf ' contains white space / caret character -file cannot be processed
Check file status :
TABLESPACE_NAME Tot size MB Tot Free MB %FREE %USED MAX_FREE CHUNKS_FREE
------------------------- ----------- ----------- ---------- ---------- ---------- -----------
SYSAUX 34815 7887 22.6538489 77.3461511 3968 90
FILE_ID FILE_NAME TABLESPACE_NAME ALLOCATED FREE_SPACE USED_SPACE
---------- ------------------------------------------------------------ ------------------------- ---------- ---------- ----------
3 /oracle/DBL/sapdata1/sysaux_1/sysaux01.dbf SYSAUX 32767 6148 26619
4 /oracle/DBL/sapdata2/sysaux_2/sysaux02.dbf SYSAUX 2048 1739 309
Check file name with and without space
SQL> select file_name from dba_data_files where file_name like '/oracle/DBL/sapdata2/sysaux_2/sysaux02.dbf';
no rows selected
SQL> select file_name from dba_data_files where file_name like '/oracle/DBL/sapdata2/sysaux_2/sysaux02.dbf ';
FILE_NAME
------------------------------------------------------------
/oracle/DBL/sapdata2/sysaux_2/sysaux02.dbf
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name 'SYSAUX';
FILE_NAME TABLESPACE_NAME ONLINE_
------------------------------------------------------------ ------------------------- -------
/oracle/DBL/sapdata1/sysaux_1/sysaux01.dbf SYSAUX ONLINE
/oracle/DBL/sapdata2/sysaux_2/sysaux02.dbf SYSAUX ONLINE
Move datafile using below command from with space to without space.
SQL> alter database move datafile '/oracle/DBL/sapdata2/sysaux_2/sysaux02.dbf ' to '/oracle/DBL/sapdata2/sysaux_2/sysaux02.dbf';
Database altered.
check files with space and without space after datafile movement .
SQL> select file_name from dba_data_files where file_name like '/oracle/DBL/sapdata2/sysaux_2/sysaux02.dbf';
FILE_NAME
------------------------------------------------------------
/oracle/DBL/sapdata2/sysaux_2/sysaux02.dbf
SQL> select file_name from dba_data_files where file_name like '/oracle/DBL/sapdata2/sysaux_2/sysaux02.dbf ';
no rows selected
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name 'SYSAUX';
FILE_NAME TABLESPACE_NAME ONLINE_
------------------------------------------------------------ ------------------------- -------
/oracle/DBL/sapdata1/sysaux_1/sysaux01.dbf SYSAUX ONLINE
/oracle/DBL/sapdata2/sysaux_2/sysaux02.dbf SYSAUX ONLINE
This resolves the issue .
If you need to Move datafile on ASM :
Steps : check the datafile no for below given example file in my case it was 41 and 42
Error : Example
DATAFILE UNPROT COARSE APR 07 19:00:00 N A2I_UPMCUSTOMER6_Z000.DBF => +DATA/CDBFINDB/DATAFILE/A2I_Findb_Z000.273.1097338569 41
DATAFILE UNPROT COARSE APR 07 19:00:00 N A2I_UPMCUSTOMER6_Z000_Ix.DBF => +DATA/CDBFIND/DATAFILE/A2I_Findb_Z000_IX.280.1097338569 42
Set the db_create_file_dest to +DATA
SQL> alter session set db_create_file_dest='+DATA'
2 ;
Session altered.
Move datafile using file number
SQL> alter database move datafile 41;
Database altered.
Move datafile on ASM using absolute path and datafile name .
ALTER DATABASE MOVE DATAFILE '+DATA/CDBFINDB/user1.dbf'
TO '+DATA';
Hope this helps
ConversionConversion EmoticonEmoticon