Demonstração simples de como copiar um datafile de um diskgroup para outro sem indisponibilidade. Neste exemplo, estou movendo 2 datafiles do diskgroup DATA02 para o diskgroup DATA.
Pesquisando datafiles no diskgroup DATA02:
SQL> select name from v$datafile where name like '+DATA02%';
NAME
--------------------------------------------------------------------------------
+DATA02/prd/datafile/mv2000_d.338.1009984659
+DATA02/prd/datafile/mv2000_d.339.1009984677
Pegando os ID dos datafiles:
SQL> SELECT FILE# from v$datafile where name like '+DATA02%';
FILE#
----------
63
64
Colocando o datafile OFFLINE:
RMAN> sql 'ALTER DATABASE DATAFILE 63 OFFLINE';
using target database control file instead of recovery catalog
sql statement: ALTER DATABASE DATAFILE 63 OFFLINE
Criado um backup do tipo IMAGE COPY, usando o novo diskgroup como destino da cópia do arquivo:
RMAN> BACKUP AS COPY DATAFILE 63 FORMAT '+DATA';
Starting backup at 28/05/2021 21:25:21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1366 instance=prd2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00063 name=+DATA02/prd/datafile/mv2000_d.338.1009984659
output file name=+DATA/prd/datafile/mv2000_d.41175.1073769923 tag=TAG20210528T212522 RECID=7 STAMP=1073769938
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 28/05/2021 21:25:47
Agora temos 2 cópias identicas do datafile 63.
Fazendo a troca do datafile no controlfile, a partir de agora a cópia criada no diskgroup DATA é usada como datafile, e a cópia original armazenada no diskgroup DATA02 é catalogada como um backup do tipo IMAGE COPY.
RMAN> switch datafile 63 to copy;
datafile 63 switched to datafile copy "+DATA/prd/datafile/mv2000_d.41175.1073769923"
Agora basta colocar o datafile ONLINE novamente, mas se faz necessário fazer recover para aplicar as alterações realizadas enquanto ele estava OFFLINE.
RMAN> sql 'ALTER DATABASE DATAFILE 63 ONLINE';
sql statement: ALTER DATABASE DATAFILE 63 ONLINE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 05/28/2021 21:26:11
RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE DATAFILE 63 ONLINE
ORA-01113: file 63 needs media recovery
ORA-01110: data file 63: '+DATA/prd/datafile/mv2000_d.41175.1073769923'
RMAN> recover datafile 63;
Starting recover at 28/05/2021 21:26:18
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 28/05/2021 21:26:19
RMAN> sql 'ALTER DATABASE DATAFILE 63 ONLINE';
sql statement: ALTER DATABASE DATAFILE 63 ONLINE
Repetindo o mesmo processo para o datafile 64:
RMAN> sql 'ALTER DATABASE DATAFILE 64 OFFLINE';
sql statement: ALTER DATABASE DATAFILE 64 OFFLINE
RMAN> BACKUP AS COPY DATAFILE 64 FORMAT '+DATA';
Starting backup at 28/05/2021 21:27:00
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00064 name=+DATA02/prd/datafile/mv2000_d.339.1009984677
output file name=+DATA/prd/datafile/mv2000_d.37369.1073770021 tag=TAG20210528T212700 RECID=9 STAMP=1073770035
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 28/05/2021 21:27:15
RMAN> switch datafile 64 to copy;
datafile 64 switched to datafile copy "+DATA/prd/datafile/mv2000_d.37369.1073770021"
RMAN> recover datafile 64;
Starting recover at 28/05/2021 21:27:33
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 28/05/2021 21:27:34
RMAN> sql 'ALTER DATABASE DATAFILE 64 ONLINE';
sql statement: ALTER DATABASE DATAFILE 64 ONLINE
RMAN>
Checando o caminho dos datafiles no controlfile:
SQL> select name from v$datafile where file# in (63,64);
NAME
--------------------------------------------------------------------------------
+DATA/prd/datafile/mv2000_d.41175.1073769923
+DATA/prd/datafile/mv2000_d.37369.1073770021