移动 oracle 数据文件和日志文件的方法

移动 oracle 数据文件和日志文件的方法

数据库在运行过程中，由于某种原因（比如磁盘空间不足）需要移动数据文件或日志文件的位置。移动文件位置可以采用两种方法：
（1）关闭数据库，移动数据文件或日志文件的位置。然后启动数据库到 mount 状态，使用 alter database 命令更改文件的位置信息。
（2）不关闭数据库，修改表空间为 offline 状态，移动数据文件的位置。使用 alter tablespace 命令修改表空间对应的数据文件的位置信息。

一、使用 alter database 命令修改日志文件的位置

1、查看日志文件

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
F:\APP\ORADATA\ORCL\REDO03.LOG
F:\APP\ORADATA\ORCL\REDO02.LOG
F:\APP\ORADATA\ORCL\REDO01.LOG

2、关闭数据库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3、移动日志文件

F:\>copy F:\APP\ORADATA\ORCL\REDO01.LOG e:\oracle-data
已复制         1 个文件。

F:\>copy F:\APP\ORADATA\ORCL\REDO02.LOG e:\oracle-data
已复制         1 个文件。

F:\>copy F:\APP\ORADATA\ORCL\REDO03.LOG e:\oracle-data
已复制         1 个文件。

4、启动到数据库到 mount 状态

SQL> startup mount;
ORACLE instance started.

Total System Global Area 3357155328 bytes
Fixed Size                  2180384 bytes
Variable Size            1828719328 bytes
Database Buffers         1509949440 bytes
Redo Buffers               16306176 bytes
Database mounted.

5、使用 alter database 命令更改日志文件的位置信息

SQL> alter database rename file 'F:\APP\ORADATA\ORCL\REDO01.LOG' to 'e:\oracle-data\REDO01.LOG';

Database altered.

SQL> alter database rename file 'F:\APP\ORADATA\ORCL\REDO02.LOG' to 'e:\oracle-data\REDO02.LOG';

Database altered.

SQL> alter database rename file 'F:\APP\ORADATA\ORCL\REDO03.LOG' to 'e:\oracle-data\REDO03.LOG';

Database altered.

6、启动数据库到 open 状态

SQL> alter database open;

Database altered.

7、查看日志文件的信息

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
E:\ORACLE-DATA\REDO03.LOG
E:\ORACLE-DATA\REDO02.LOG
E:\ORACLE-DATA\REDO01.LOG

二、使用 alter database 命令修改数据文件的位置

1、查看数据文件

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
F:\APP\ORADATA\ORCL\SYSTEM01.DBF
F:\APP\ORADATA\ORCL\SYSAUX01.DBF
F:\APP\ORADATA\ORCL\UNDOTBS01.DBF
F:\APP\ORADATA\ORCL\USERS01.DBF

SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME               FILE_NAME
--------------------------------------------------------------------------------
USERS                        F:\APP\ORADATA\ORCL\USERS01.DBF
UNDOTBS1                     F:\APP\ORADATA\ORCL\UNDOTBS01.DBF
SYSAUX                       F:\APP\ORADATA\ORCL\SYSAUX01.DBF
SYSTEM                       F:\APP\ORADATA\ORCL\SYSTEM01.DBF

2、关闭数据库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3、移动表空间 user01 对应的数据文件

F:\>copy F:\APP\ORADATA\ORCL\USERS01.DBF e:\oracle-data
已复制         1 个文件。

4、启动到数据库到 mount 状态

SQL> startup mount;
ORACLE instance started.

Total System Global Area 3357155328 bytes
Fixed Size                  2180384 bytes
Variable Size            1828719328 bytes
Database Buffers         1509949440 bytes
Redo Buffers               16306176 bytes
Database mounted.

5、使用 alter database 命令更改数据文件的位置信息

SQL> alter database rename file 'F:\APP\ORADATA\ORCL\USERS01.DBF' to 'e:\oracle-data\USERS01.DBF';

Database altered.

6、启动数据库到 open 状态

SQL> alter database open;

Database altered.

8、查看数据文件的信息

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
F:\APP\ORADATA\ORCL\SYSTEM01.DBF
F:\APP\ORADATA\ORCL\SYSAUX01.DBF
F:\APP\ORADATA\ORCL\UNDOTBS01.DBF
E:\ORACLE-DATA\USERS01.DBF

三、使用 alter tablespace 命令修改数据文件的位置

注意：不能移动 system 表空间、undo 表空间和 temp 表空间对应的数据文件。

1、查看表空间对应的数据文件

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME           FILE_NAME
--------------------------------------------------------------------------------
USERS                     E:\ORACLE-DATA\USERS01.DBF
UNDOTBS1                  F:\APP\ORADATA\ORCL\UNDOTBS01.DBF
SYSAUX                    F:\APP\ORADATA\ORCL\SYSAUX01.DBF
SYSTEM                    F:\APP\ORADATA\ORCL\SYSTEM01.DBF

2、查看某个表所在的表空间

##############################################################################################
####  查看某个表所在的表空间  ############################################  
SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES;
##############################################################################################
SQL> SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES where table_name='DEPT';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS

3、修改表空间为 offline 状态

SQL> alter tablespace users offline;

Tablespace altered.

######################################################################
######### 说明：system，undo，temp 表空间不能设置为offline，因此使用 
#########  alter tablespace 命令不能移动以上表空间对应的数据文件
######################################################################
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

SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace

4、移动表空间对应的数据文件的位置

F:\>copy E:\ORACLE-DATA\USERS01.DBF E:\data\users\USERS01.DBF
已复制         1 个文件。

5、使用 alter tablespace 命令修改表空间

SQL> alter tablespace users rename datafile 'E:\ORACLE-DATA\USERS01.DBF' to 'E:\data\users\USERS01.DBF';

Tablespace altered.

6、修改表空间为 online 状态

################################################################################
####  修改为 online 时出现错误
################################################################################
SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'E:\DATA\USERS\USERS01.DBF'
################################################################################
#### 使用 recover datafile 恢复数据文件
################################################################################
SQL> recover datafile 'E:\data\users\USERS01.DBF';
Media recovery complete.
################################################################################
#### 表空间修改为 online
################################################################################
SQL> alter tablespace users online;
Tablespace altered.

7、查看表空间对应的数据文件

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
--------------------------------------------------------------------------------
USERS                E:\DATA\USERS\USERS01.DBF
UNDOTBS1             F:\APP\ORADATA\ORCL\UNDOTBS01.DBF
SYSAUX               F:\APP\ORADATA\ORCL\SYSAUX01.DBF
SYSTEM               F:\APP\ORADATA\ORCL\SYSTEM01.DBF