oracle统一命名文件,oracle 各类文件的重命名及移动

最近在作一个银行的数据库迁移及升级的case,因为该系统为统计跑批，原系统配置已经没法知足跑批性能要求，故要购买新的主机采用ssd及hd两种混合型的存储，中间过程型的表对应的表空间放入ssd对应目录，历史结果表放入hd对应目录。因此须要在迁移过程当中对数据文件进行移动和重命名。如下是从网上搜索到的一篇文章，比较细致的作了测试！记录如下数据库

同时这次，从oracle 11.2.0.4 迁移到12.2.0.01上，重点采用了oracle  12c的In_memory特性。对跑批性能提高有很大的帮助！特此记录一下oracle

下面介绍移动Oracle数据文件的两种方法。app

1.alter database方法

该方法，能够移动任何表空间的数据文件。性能

***关闭数据库***

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

***移动数据文件，用oracle用户操做***

[oracle@test ~]$ mv /u01/app/oracle/oradata/test/system01.dbf /oracledb/test/system01.dbf

[oracle@test ~]$ mv /u01/app/oracle/oradata/test/sysaux01.dbf /oracledb/test/sysaux01.dbf

[oracle@test ~]$ mv /u01/app/oracle/oradata/test/undotbs01.dbf /oracledb/test/undotbs01.dbf

[oracle@test ~]$ mv /u01/app/oracle/oradata/test/users01.dbf /oracledb/test/users01.dbf

[oracle@test ~]$ mv /u01/app/oracle/oradata/test/temp01.dbf /oracledb/test/temp01.dbf

[oracle@test ~]$ mv /u01/app/oracle/oradata/test/redo03.log /oracledb/test/redo03.log

[oracle@test ~]$ mv /u01/app/oracle/oradata/test/redo02.log /oracledb/test/redo02.log

[oracle@test ~]$ mv /u01/app/oracle/oradata/test/redo01.log /oracledb/test/redo01.log

***启动到mount状态***

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.0122E+10 bytes

Fixed Size 2237088 bytes

Variable Size 1610616160 bytes

Database Buffers 8489271296 bytes

Redo Buffers 19468288 bytes

Database mounted.

SQL> alter database rename file '/u01/app/oracle/oradata/test/system01.dbf' to '/oracledb/test/system01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/test/sysaux01.dbf' to '/oracledb/test/sysaux01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/test/undotbs01.dbf' to '/oracledb/test/undotbs01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/test/users01.dbf' to '/oracledb/test/users01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/test/temp01.dbf' to '/oracledb/test/temp01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/test/redo01.log' to '/oracledb/test/redo01.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/test/redo02.log' to '/oracledb/test/redo02.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/test/redo03.log' to '/oracledb/test/redo03.log';

Database altered.

SQL> alter database open;

Database altered.

***重启验证***

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1.0122E+10 bytes

Fixed Size 2237088 bytes

Variable Size 1610616160 bytes

Database Buffers 8489271296 bytes

Redo Buffers 19468288 bytes

Database mounted.

Database opened.

2.alter tablespace方法

该方法，不能移动system表空间，回滚段表空间和临时段表空间的数据文件。测试

***offline system表空间时报错***

SQL> alter tablespace system offline;

alter tablespace system offline

*

ERROR at line 1:

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

报错：说明system表空间不能offline

***由此说明一下system表空间的特性--不能脱机offline

--不能置为只读read only

--不能重命名

--不能删除

SQL> alter tablespace sysaux offline;

Tablespace altered.

[oracle@test ~]$ cp /oracledb/test/sysaux01.dbf /u01/app/oracle/oradata/test/sysaux01.dbf

SQL> alter tablespace sysaux rename datafile '/oracledb/test/sysaux01.dbf' to '/u01/app/oracle/oradata/test/sysaux01.dbf';

Tablespace altered.

SQL> alter tablespace sysaux online;

Tablespace altered.

***offline UNDO表空间时报错***

SQL> alter tablespace UNDOTBS1 offline;

alter tablespace UNDOTBS1 offline

*

ERROR at line 1:

ORA-30042: Cannot offline the undo tablespace

***offline TEMP表空间时报错***

SQL> alter tablespace TEMP offline;

alter tablespace TEMP offline

*

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

#把须要移动的数据文件对应的表空间offline

SQL> alter tablespace USERS offline;

Tablespace altered.

#移动数据文件至目标位置

[oracle@test ~]$ cp /oracledb/test/users01.dbf /u01/app/oracle/oradata/test/users01.dbf

#修改表空间中数据文件的位置

SQL> alter tablespace USERS rename datafile '/oracledb/test/users01.dbf' to '/u01/app/oracle/oradata/test/users01.dbf';

Tablespace altered.

#把表空间online

SQL> alter tablespace users online;

Tablespace altered.

3.总结

alter database方法能够移动任何表空间的数据文件，但其要求数据库必须处于mount状态，故此种方法更适合作总体数据库的迁移。

alter tablespace方法须要数据库处于open状态，表空间在offline的状态下才可更改。但其不能移动system表空间，undo表空间和temp表空间的数据文件，故此种方法更适合于作用户数据文件的迁移。 原做地址：http://blog.csdn.net/snowying97/article/details/52576028

原做做者：风夏了白雪yispa