Oracle 11g 移动数据文件路径

浅浅的花香味﹌ 2022-06-05 06:39 387阅读 0赞

注意：下面在移动文件的时候一定要确定新路径下是否已存在相同的数据文件名。  
  
一、移动表空间文件  
1、修改表空间为离线状态  
SQL> alter tablespace bsskzy offline;  
  
Tablespace altered.  
2.复制表空间对应文件到新的位置  
cp bsskzy01.dbf /home/oracle/oradata/orcl/datafile/  
3、修改表空间数据文件路径  
SQL> alter tablespace bsskzy rename datafile '/opt/oracle/oradata/orcl/bsskzy01.dbf' to '/home/oracle/oradata/orcl/datafile/bsskzy01.dbf';  
4、修改表空间为在线状态  
SQL> alter tablespace sysaux online;  
  
Tablespace altered.  
  
5、删除表空间对应的原来的数据库文件  
rm -rf bsskzy01.dbf  
  
二、移动数据库文件  
1.关闭数据库  
SQL> shutdown immediate  
Database closed.  
Database dismounted.  
ORACLE instance shut down.  
2.复制数据库文件  
cp users01.dbf undotbs01.dbf sysaux01.dbf system01.dbf /home/oracle/oradata/orcl/datafile/  
3.启动到mount  
SQL> startup mount  
  
Total System Global Area 4275781632 bytes  
Fixed Size            2260088 bytes  
Variable Size         1459618696 bytes  
Database Buffers     2801795072 bytes  
Redo Buffers           12107776 bytes  
Database mounted  
4.修改数据库文件路径  
SQL> alter database rename file '/opt/oracle/oradata/orcl/system01.dbf' to '/home/oracle/oradata/orcl/datafile/system01.dbf';  
  
Database altered.  
  
SQL> alter database rename file '/opt/oracle/oradata/orcl/sysaux01.dbf' to '/home/oracle/oradata/orcl/datafile/sysaux01.dbf';  
  
Database altered.  
  
SQL> alter database rename file '/opt/oracle/oradata/orcl/undotbs01.dbf' to '/home/oracle/oradata/orcl/datafile/undotbs01.dbf';  
  
Database altered.  
  
SQL> alter database rename file '/opt/oracle/oradata/orcl/users01.dbf' to '/home/oracle/oradata/orcl/datafile/users01.dbf';  
  
Database altered.  
  
5、打开数据库  
SQL> alter database open;  
  
Database altered.