How to move the Datafiles in Rac Database

HI Guys,

We were put in a Situation that we had to Create the New data files  for the tablespaces in the RECO diskgroup temporarily  in one of the RAC databases because we didn’t have much space in the DATA diskgroup.

So We had  Performed the tablespace re-org activity  one of the Big file tablespaces to reclaim the space on the DATA diskgroup.Once we get the space back to the DATA diskgroup ,We had to move back all the datafiles back to DATA diskgroup from RECO diskgroup.Below is the action Plan I am planning to use and of course Tested the same method in one of our QA Envt’s.

Environment:

Database Version: 11.2.0.4 Exadata RAC database

Below is the action Plan I have used.

SQL> create tablespace dropme1 datafile ‘+datac1’ size 10m;

Tablespace created.

SQL> alter tablespace dropme1 add datafile ‘+RECOC1′ size 10m;

Tablespace altered.

SQL> SELECT FILE_NAME,FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=’DROPME1’;

FILE_NAME
——————————————————————————–
FILE_ID
———-
+DATAC1/test01/datafile/dropme1.1316.943012301
128

+RECOC1/test01/datafile/dropme1.7178.943012317
129

  1. Make the tablespace offline:

SQL> alter tablespace dropme1 offline;

Tablespace altered.

  1. Copy the data file to DATAC1 by using the RMAN

RMAN> copy datafile 129 to ‘+DATAC1’;

Starting backup at 03-MAY-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5

using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_DISK_9
using channel ORA_DISK_10
using channel ORA_DISK_11
using channel ORA_DISK_12
using channel ORA_DISK_13
using channel ORA_DISK_14
using channel ORA_DISK_15
using channel ORA_DISK_16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00129 name=+RECOC1/test01/datafile/dropme1.3312.943012489
output file name=+DATAC1/test01/datafile/dropme1.1343.943012565 tag=TAG20170503T115604 RECID=407 STAMP=943012564
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-MAY-17

  1. Switch the datafile to DATAC1

RMAN> SWITCH DATAFILE 129 TO COPY;

datafile 129 switched to datafile copy “+DATAC1/test01/datafile/dropme1.1343.943012565”

  1. Make the tablespace online Again

RMAN> SQL ‘ALTER TABLESPACE DROPME1 ONLINE’;

sql statement: ALTER TABLESPACE DROPME1 ONLINE

  1. Validating the status on the Datafiles

bhdhulip@test01>select file_name,status from dba_data_files where tablespace_name=’DROPME1′;

FILE_NAME STATUS
———————————————————————- ———
+DATAC1/test01/datafile/dropme1.1316.943012301 AVAILABLE
+DATAC1/test01/datafile/dropme1.1343.943012565 AVAILABLE

6.Drop the Datafile present in the RECO diskgroup.

Connect to ASM instance.

[oracle@************:+ASM1:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 3 14:51:43 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

 Drop the Data file present in the RECOC1

SQL> alter diskgroup recoc1 drop file ‘+recoc1/test01/datafile/DROPME1.7178.943012317’;

Diskgroup altered.

 

Leave a Reply

Close Menu