Como mover arquivos de dados a partir de um banco de dados Oracle

In acest tutorial, vom se cum să se mute fișierele de date de baze de date offline. Aceasta se poate face atunci când faci o întreținere disc S.O. Aceasta se poate face, de asemenea, conectat și on-line.

  • 1-Găsiți locația fișierelor de date: 
    • 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-Creați script de mutare (acest script va ajuta să vă mutați fișierele de date în noua locație).
    • 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-Shutdown baza de date.
    • 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-Porniți baza de date în modul mount.
    • [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-Redenumiți fișierul în dicționarul 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-Deschide baza de date
    • SQL>  ALTER DATABASE OPEN;
Database altered.
SQL>

    Ok, asta a fost, pur și simplu ne-am schimbat locațiile noastre de fișiere de date.

    Copyright 2020. All Rights Reserved