Oracle: MOVE de DATAFILE ONLINE em Filesystem com OMF

Nada de mais por aqui pessoal.
A alguns dias tive que mover DATAFILEs que utilizam OMF (que pode ser utilizado também em Filesystem, além de ASM). Para fzer isto, basta alterar o parâmetro db_create_file_dest para o novo diretório, e executar então o MOVE do DATAFILE (que pode ser ONLINE a partir da versão 12cR1), sem especificar o destino.

11:10:42 SYS@ORCL> SHOW PARAMETER DB_CREATE

NAME									TYPE 		VALUE
------------------------------------	----------- ------------------------------
db_create_file_dest 					string 		/u01/app/oracle/oradata
db_create_online_log_dest_1 			string
db_create_online_log_dest_2 			string
db_create_online_log_dest_3 			string
db_create_online_log_dest_4 			string
db_create_online_log_dest_5 			string
11:10:50 SYS@ORCL> ALTER SYSTEM SET db_create_file_dest='/home/oracle/oradata';

Sistema alterado.

Decorreram: 00:00:00.06
11:11:26 SYS@ORCL> SELECT FILE_ID, FILE_NAME FROM DBA_DATA_FILES;

FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
13 /u01/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_system_fxtyjkw7_.dbf
14 /u01/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_sysaux_fxtyjkwr_.dbf
15 /u01/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_undotbs1_fxtyjkws_.dbf
19 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_dados_fxtys0p4_.dbf
20 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_dados_fxtyszmq_.dbf
21 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_dados_fxtyt151_.dbf
22 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_dados_fxtyt38q_.dbf
23 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_dados_fxtyt5g8_.dbf
24 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_indices_fxtytp1z_.dbf
25 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_indices_fxtytvxx_.dbf
26 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_indices_fxtytxop_.dbf
27 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_indices_fxtytz27_.dbf
28 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_indices_fxtyv0d7_.dbf

13 linhas selecionadas.

Decorreram: 00:00:00.04
11:11:39 SYS@ORCL> ALTER DATABASE MOVE DATAFILE 19;

Base de dados alterada.

Decorreram: 01:00:02.92
12:12:15 SYS@ORCL> SELECT FILE_ID, FILE_NAME FROM DBA_DATA_FILES;

FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
13 /u01/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_system_fxtyjkw7_.dbf
14 /u01/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_sysaux_fxtyjkwr_.dbf
15 /u01/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_undotbs1_fxtyjkws_.dbf
19 /home/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_dados_fy0jfd5c_.dbf
20 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_dados_fxtyszmq_.dbf
21 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_dados_fxtyt151_.dbf
22 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_dados_fxtyt38q_.dbf
23 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_dados_fxtyt5g8_.dbf
24 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_indices_fxtytp1z_.dbf
25 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_indices_fxtytvxx_.dbf
26 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_indices_fxtytxop_.dbf
27 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_indices_fxtytz27_.dbf
28 /u02/app/oracle/oradata/ORCL/79C137DDA17F40B7E053020FA8C0770E/datafile/o1_mf_indices_fxtyv0d7_.dbf

13 linhas selecionadas.

Decorreram: 00:00:00.09
12:24:21 SYS@ORCL>

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.