We need to move one or more datafile from our tablespace to one location to another because of the disk space issue.

In this post, I try to explain how We can achieve this process with CP command of asmcmd utility via a demo

The database version is –> 11.2.0.4

The Operating System is –> Linux 6

Let  us check first the existing  status

[oracle@TEST] export ORACLE_SID=+ASM
[oracle@TEST]echo $ORACLE_SID
+ASM
[oracle@TEST]sqlplus “/as sysasm”
SQL> select group_number, name, total_mb, free_mb, state, type from v$asm_diskgroup;

Before start, let us see status of  our databases:

[oracle@TEST]export ORACLE_SID=TEST
[oracle@TEST]sqlplus “/as sysdba”
SQL> select log_mode from v$database;
LOG_MODE
 ————
 ARCHIVELOG

Now we need to  identify which datafile or datafiles we will move. Here is the steps:

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES where tablespace_name=’GUNESTABLESPACE’;
FILE_NAME
 ——————————————————————————–
 +ORADATA/TEST/datafile/gunestablespace.270.747153997
 +ORADATA/TEST/datafile/gunestablespace.271.747154095
 +ORADATA/TEST/datafile/gunestablespace.272.747154201
 +ORADATA/TEST/datafile/gunestablespace.273.747239703
 +ORADATA/TEST/datafile/gunestablespace.274.747934181
 +ORADATA/TEST/datafile/gunestablespace.275.747934253

Now we are going to follow below command to can copy one of datafile from one location to another by using CP command:

1. alter database datafile ‘+ORADATA/TEST/dafafile/gunestablespace.270.747153997’ offline;

2. create or replace directory orcl1 as ‘+ORADATA’;

create or replace directory orcl2 as ‘+ORADATA/Move_here/datafile’;

3. We should use DBMS_FILE_TRANSFER package

BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => ‘ORCL1’,
source_file_name => ‘gunestablespace.270.747153997’,
destination_directory_object => ‘ORCL2’,
destiation_file_name => ‘gunestablespace.270.747153997’);
END;

4. Rename file

ALTER DATABASE RENAME FILE  ‘+ORADATA/gunestablespace.270.747153997’   TO  ‘+ORADATA/Move_here/datafile/gunestablespace.270.747153997”;

5.  alter database datafile ‘+ORADATA/pakb40/datafile/gunestablespace.270.747153997’  online;

Reference:

How to move ASM database files from one diskgroup to another ? [ID 330103.1]