Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 | SQL> create tablespace dropme1 datafile ‘+datac1’ size 10m; Tablespace created. |
1 2 | SQL> alter tablespace dropme1 add datafile ‘+RECOC1′ size 10m; Tablespace altered. |
1 2 3 4 5 | 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:
1 2 | SQL> alter tablespace dropme1 offline; Tablespace altered. |
2- Copy the data file to DATAC1 by using the RMAN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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 <strong>output file name=+DATAC1/test01/datafile/dropme1.1343.943012565 tag=TAG20170503T115604 RECID=407 STAMP=943012564</strong> <strong>channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01</strong> Finished backup at 03-MAY-17 |
3- Switch the datafile to DATAC1
1 2 | RMAN> SWITCH DATAFILE 129 TO COPY; datafile 129 switched to datafile copy “+DATAC1/test01/datafile/dropme1.1343.943012565” |
4- Make the tablespace online Again
1 2 | RMAN> SQL ‘ALTER TABLESPACE DROPME1 ONLINE’; sql statement: ALTER TABLESPACE DROPME1 ONLINE |
5- Validating the status on the Datafiles
1 2 3 4 5 | 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.
1 2 3 4 5 6 | [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
1 2 | SQL> alter diskgroup recoc1 drop file ‘+recoc1/test01/datafile/DROPME1.7178.943012317’; Diskgroup altered. |
