Como mover arquivos de dados de um banco de dados Oracle

Neste tutorial vamos ver como realocar os arquivos de dados do banco de dados Oracle offline. Isso pode ser feito quando vai fazer manutenção de disco do S.O.. Isso pode ser feito off-line e também online.

  • 1-Encontre a localização de seus arquivos de dados: 
    • SQL> set pagesize 600
SQL> SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
/u00/oracle/oradata/RCAT/system01.dbf
/u00/oracle/oradata/RCAT/sysaux01.dbf
/u00/oracle/oradata/RCAT/undotbs01.dbf
/u00/oracle/oradata/RCAT/users01.dbf
/u00/oracle/oradata/RCAT/RMAN_CATALOG.dbf
/u00/oracle/oradata/RCAT/ruei.dbf
/u00/oracle/oradata/RCAT/users00.dbf
/u00/oracle/oradata/RCAT/users02.dbf
/u00/oracle/oradata/RCAT/users03.dbf
/u00/oracle/oradata/RCAT/users04.dbf
/u00/oracle/oradata/RCAT/users05.dbf
/u00/oracle/oradata/RCAT/users06.dbf
/u00/oracle/oradata/RCAT/users07.dbf
/u00/oracle/oradata/RCAT/users08.dbf
/u00/oracle/oradata/RCAT/users09.dbf
/u00/oracle/oradata/RCAT/users10.dbf
/u00/oracle/oradata/RCAT/users11.dbf
/u00/oracle/oradata/RCAT/users12.dbf
/u00/oracle/oradata/RCAT/users13.dbf
/u00/oracle/oradata/RCAT/users14.dbf
/u00/oracle/oradata/RCAT/users15.dbf
/u00/oracle/oradata/RCAT/users16.dbf
/u00/oracle/oradata/RCAT/users17.dbf
/u00/oracle/oradata/RCAT/users18.dbf
/u00/oracle/oradata/RCAT/users19.dbf
/u00/oracle/oradata/RCAT/users20.dbf
/u00/oracle/oradata/RCAT/users21.dbf
/u00/oracle/oradata/RCAT/users22.dbf
/u00/oracle/oradata/RCAT/users23.dbf
/u00/oracle/oradata/RCAT/users24.dbf
/u00/oracle/oradata/RCAT/users25.dbf
/u00/oracle/oradata/RCAT/users26.dbf
/u00/oracle/oradata/RCAT/users27.dbf
/u00/oracle/oradata/RCAT/users28.dbf
/u00/oracle/oradata/RCAT/users29.dbf
/u00/oracle/oradata/RCAT/uxconf01.dbf
/u00/oracle/oradata/RCAT/uxstat01.dbf
/u00/oracle/oradata/RCAT/uxtemp01.dbf

38 rows selected.
  • 2-Crie o script de movimento (este script irá ajudá-lo a mover seus arquivos de dados para a nova localização).
    • SQL> select ' mv  '||file_name|| '  /u01/oracle/oradata/RCAT/' from  dba_data_files;

'MV'||FILE_NAME||'/U01/ORACLE/ORADATA/RCAT/'
--------------------------------------------------------------------------------
 mv  /u00/oracle/oradata/RCAT/users01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/undotbs01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/sysaux01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/system01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/RMAN_CATALOG.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/ruei.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users00.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users02.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users03.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users04.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users05.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users06.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users07.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users08.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users09.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users10.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users11.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users12.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users13.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users14.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users15.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users16.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users17.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users18.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users19.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users20.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users21.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users22.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users23.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users24.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users25.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users26.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users27.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users28.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/users29.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/uxconf01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/uxstat01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/uxtemp01.dbf  /u01/oracle/oradata/RCAT/

38 rows selected.
  • 3 - Desligar seu banco de dados.
    • SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
  • 4-Run the "mv" script create at step 2.
    •  mv  /u00/oracle/oradata/RCAT/users01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/undotbs01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/sysaux01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/system01.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/RMAN_CATALOG.dbf  /u01/oracle/oradata/RCAT/
 mv  /u00/oracle/oradata/RCAT/ruei.dbf  /u01/oracle/oradata/RCAT/
 ..............
 ..............
 ..............
  • 5-Iniciar o seu banco de dados em mount mode().
    • [oracle@DCG023 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 9 13:52:10 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size            1006635120 bytes
Database Buffers          587202560 bytes
Redo Buffers                7360512 bytes
Database mounted.
SQL>
  • 6-Renomeie o arquivo dentro do dicionário da Oracle 
    • alter database rename file '/u00/oracle/oradata/RCAT/users01.dbf' to '/u01/oracle/oradata/RCAT/users01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/undotbs01.dbf' to '/u01/oracle/oradata/RCAT/undotbs01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/sysaux01.dbf' to '/u01/oracle/oradata/RCAT/sysaux01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/system01.dbf' to '/u01/oracle/oradata/RCAT/system01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/RMAN_CATALOG.dbf' to '/u01/oracle/oradata/RCAT/RMAN_CATALOG.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/ruei.dbf' to '/u01/oracle/oradata/RCAT/ruei.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users00.dbf' to '/u01/oracle/oradata/RCAT/users00.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users02.dbf' to '/u01/oracle/oradata/RCAT/users02.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users03.dbf' to '/u01/oracle/oradata/RCAT/users03.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users04.dbf' to '/u01/oracle/oradata/RCAT/users04.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users05.dbf' to '/u01/oracle/oradata/RCAT/users05.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users06.dbf' to '/u01/oracle/oradata/RCAT/users06.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users07.dbf' to '/u01/oracle/oradata/RCAT/users07.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users08.dbf' to '/u01/oracle/oradata/RCAT/users08.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users09.dbf' to '/u01/oracle/oradata/RCAT/users09.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users10.dbf' to '/u01/oracle/oradata/RCAT/users10.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users11.dbf' to '/u01/oracle/oradata/RCAT/users11.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users12.dbf' to '/u01/oracle/oradata/RCAT/users12.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users13.dbf' to '/u01/oracle/oradata/RCAT/users13.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users14.dbf' to '/u01/oracle/oradata/RCAT/users14.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users15.dbf' to '/u01/oracle/oradata/RCAT/users15.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users16.dbf' to '/u01/oracle/oradata/RCAT/users16.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users17.dbf' to '/u01/oracle/oradata/RCAT/users17.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users18.dbf' to '/u01/oracle/oradata/RCAT/users18.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users19.dbf' to '/u01/oracle/oradata/RCAT/users19.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users20.dbf' to '/u01/oracle/oradata/RCAT/users20.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users21.dbf' to '/u01/oracle/oradata/RCAT/users21.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users22.dbf' to '/u01/oracle/oradata/RCAT/users22.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users23.dbf' to '/u01/oracle/oradata/RCAT/users23.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users24.dbf' to '/u01/oracle/oradata/RCAT/users24.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users25.dbf' to '/u01/oracle/oradata/RCAT/users25.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users26.dbf' to '/u01/oracle/oradata/RCAT/users26.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users27.dbf' to '/u01/oracle/oradata/RCAT/users27.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users28.dbf' to '/u01/oracle/oradata/RCAT/users28.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/users29.dbf' to '/u01/oracle/oradata/RCAT/users29.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/uxconf01.dbf' to '/u01/oracle/oradata/RCAT/uxconf01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/uxstat01.dbf' to '/u01/oracle/oradata/RCAT/uxstat01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/uxtemp01.dbf' to '/u01/oracle/oradata/RCAT/uxtemp01.dbf';
alter database rename file '/u00/oracle/oradata/RCAT/uxstat01.dbf' to '/u01/oracle/oradata/RCAT/uxstat01.dbf';
  • 7-Abra seu banco de dados
    • SQL>  ALTER DATABASE OPEN;
Database altered.
SQL>

    Ok, so isso, nós apenas alteramos nossos locais de arquivos de dados usando o modo offline.

    Copyright 2020. All Rights Reserved