【Oracle】How to Rename or Move Datafiles and Logfiles 之二-白红宇

在数据库open状态下进行重命名或者迁移数据文件：

注意：迁移system 表空间里的数据文件或者redo log 必须在mount状态下进行操作。

在数据库open 模式进行数据文件的移动时，数据文件所在的表空间必须是只读的。用户只可以进行select 查询操作而不能进行DML 修改。此期间持续的时间由迁移的数据文件大小和copy到新的目录的时间决定！

具体步骤如下：

1 确定有多少数据文件要进行迁移, 确保所有文件的status 为 AVAILABLE

SYS@yangdb-rac3> col file_name for a50

SYS@yangdb-rac3> SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME STATUS

-------------------------------------------------- ---------

/opt/oracle/oradata/yangdb_test/users01.dbf AVAILABLE

2设置表空间为只读,确保表空间已经在数据库字典里面设置为只读

SYS@yangdb-rac3> alter tablespace users read only;

Tablespace altered.

SYS@yangdb-rac3> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE_NAME STATUS

------------------------------ ---------

USERS READ ONLY

3 移动数据文件到新的位置

oracle@rac3:/opt/oracle/oradata/yangdb_test>ls

control01.ctl control02.ctl control03.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

oracle@rac3:/opt/oracle/oradata/yangdb_test>cp users01.dbf ../yangdb/users01.dbf

oracle@rac3:/opt/oracle/oradata/yangdb_test>cd ../yangdb

oracle@rac3:/opt/oracle/oradata/yangdb>ls

users01.dbf

4 当数据文件已经被迁移到新的位置，将表空间offline

SYS@yangdb-rac3> ALTER TABLESPACE USERS OFFLINE;

Tablespace altered.

查询表空间的status 信息

SYS@yangdb-rac3> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE_NAME STATUS

------------------------------ ---------

USERS READ ONLY

数据文件的status

SYS@yangdb-rac3> SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME STATUS

-------------------------------------------------- ---------

/opt/oracle/oradata/yangdb_test/users01.dbf AVAILABLE

SYS@yangdb-rac3> select name,status from v$datafile;

NAME STATUS

-------------------------------------------------- -------

/opt/oracle/oradata/yangdb_test/system01.dbf SYSTEM

/opt/oracle/oradata/yangdb_test/sysaux01.dbf ONLINE

/opt/oracle/oradata/yangdb_test/undotbs01.dbf ONLINE

/opt/oracle/oradata/yangdb_test/users01.dbf OFFLINE

/opt/oracle/oradata/yangdb_test/example01.dbf ONLINE

5 重命名数据文件，此步骤将修改控制文件里的相关信息

SYS@yangdb-rac3> alter database rename file '/opt/oracle/oradata/yangdb_test/users01.dbf' to '/opt/oracle/oradata/yangdb/users01.dbf';

Database altered.

6 将数据文件重命名后，将表空间设置为online，并且将其设置为read write

SYS@yangdb-rac3> ALTER TABLESPACE USERS ONLINE;

Tablespace altered.

SYS@yangdb-rac3> select name,status from v$datafile;

NAME STATUS

-------------------------------------------------- -------

/opt/oracle/oradata/yangdb_test/system01.dbf SYSTEM

/opt/oracle/oradata/yangdb_test/sysaux01.dbf ONLINE

/opt/oracle/oradata/yangdb_test/undotbs01.dbf ONLINE

/opt/oracle/oradata/yangdb/users01.dbf ONLINE

/opt/oracle/oradata/yangdb_test/example01.dbf ONLINE

SYS@yangdb-rac3> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE_NAME STATUS

------------------------------ ---------

USERS READ ONLY

SYS@yangdb-rac3> ALTER TABLESPACE USERS READ WRITE;

Tablespace altered.

SYS@yangdb-rac3> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE_NAME STATUS

------------------------------ ---------

USERS ONLINE

SYS@yangdb-rac3> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE_NAME STATUS

------------------------------ ---------

USERS ONLINE

7 测试结果，查询users 表空间中的数据：

SYS@yangdb-rac3> SELECT COUNT(*) FROM SCOTT.EMP;

COUNT(*)

----------

成功！