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

In this article i try to explain how we can achive this process wiht CP command of asmcmd utulity  via demo

If you want to do this process wiht RMAN utulity please check my other post by click here

How to move ASM database files from one location to another by CP command

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

In this article i try to explain how we can achive this process wiht CP command of asmcmd utulity  via demo

The database version is :

11.2.0.1

The Operating System is:

AIX 6.1

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*Plus: Release 11.2.0.1.0 Production on Fri Apr 8 15:21:14 2011
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Automatic Storage Management option

Let us see first our diskgroup :

SQL> select group_number, name, total_mb, free_mb, state, type from v$asm_diskgroup;
Group_Number
Name
Total_MB
Free_MB
State
Type
1
ORADATA
204800
128
CONNECTED

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 usingf 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’,

destination_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]

Advertisements