乾坤大挪移-移动所有数据文件

如题，最近在一个开发库上存在硬盘空间紧张的问题，新添加了一块盘，准备把所有的数据文件挪到新盘上。
首先列出需要移动的数据文件，数据文件隶属于表空间，我们从表空间用途可以如下分门别类：
控制文件
System表空间
undo表空间
temporary表空间
redo日志文件
user_data表空间

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
GTLIONS
GTLIONSTMP

SQL> select file_name,file_id,tablespace_name from dba_data_Files;
FILE_NAME                          FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
/u01/oracle/10g/oradata/gt10g/users01.dbf            4 USERS
/u01/oracle/10g/oradata/gt10g/sysaux01.dbf            3 SYSAUX
/u01/oracle/10g/oradata/gt10g/undotbs01.dbf            2 UNDOTBS1
/u01/oracle/10g/oradata/gt10g/system01.dbf            1 SYSTEM
/u01/oracle/10g/oradata/gt10g/gtlions01.ora            5 GTLIONS

SQL> select file_name,file_id,tablespace_name from dba_temp_Files;

FILE_NAME                          FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
/u01/oracle/10g/oradata/gt10g/temp01.dbf            1 TEMP
/u01/oracle/10g/oradata/gt10g/gtlionstmp01.ora            2 GTLIONSTMP

SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/oracle/10g/oradata/gt10g/control01.ctl
/u01/oracle/10g/oradata/gt10g/control02.ctl
/u01/oracle/10g/oradata/gt10g/control03.ctl

SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/oracle/10g/oradata/gt10g/redo03.log
/u01/oracle/10g/oradata/gt10g/redo02.log
/u01/oracle/10g/oradata/gt10g/redo01.log

针对undo表空间，我们可以在打开数据的状态下直接操作：
SQL> create undo tablespace undotbs2 datafile '/u01/oracle/10g/oradata/gt10gnew/undotbs01.dbf' size 20m autoextend on;

Tablespace created.

SQL> show parameter undo_tablespace;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace              string     UNDOTBS1
SQL> alter system set undo_tablespace='undotbs2';

System altered.

SQL> show parameter undo_tablespace;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace              string     undotbs2
SQL> drop tablespace undotbs1;

Tablespace dropped.

针对temp表空间，我们可以在打开数据的状态下直接操作：
SQL> create temporary tablespace temp1 tempfile '/u01/oracle/10g/oradata/gt10gnew/temp01.dbf' size 20M autoextend on;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

SQL> create temporary tablespace gtlionstmp1 tempfile '/u01/oracle/10g/oradata/gt10gnew/gtlionstmp01.dbf' size 20M autoextend on;

Tablespace created.

SQL> alter user gtlions temporary tablespace gtlionstmp1;

User altered.

SQL>  drop tablespace gtlionstmp  including contents and datafiles;

Tablespace dropped.

针对redo日志文件，我们可以在打开数据的状态下直接操作：
SQL> alter database add logfile group 4 ('/u01/oracle/10g/oradata/gt10gnew/redo04a.log','/u01/oracle/10g/oradata/gt10gnew/redo04b.log') size 20M;

Database altered.

SQL> alter database add logfile group 5 ('/u01/oracle/10g/oradata/gt10gnew/redo05a.log','/u01/oracle/10g/oradata/gt10gnew/redo05b.log') size 20M;
\

Database altered.

SQL> SP2-0042: unknown command "\" - rest of line ignored.
SQL> alter system switch logfile;

System altered.
SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 3;

Database altered.

现在还剩下控制文件、system和用户数据文件。
针对控制文件我们可以直接复制文件副本，然后修改参数文件即可：
SQL> create pfile='/u01/oracle/10gpfile.ora' from spfile;

File created.

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

[oracle@gtlions oracle]$ cp 10gpfile.ora  10gpfile.ora.bak
[oracle@gtlions oracle]$ cp /u01/oracle/10g/oradata/gt10g/control0* /u01/oracle/10g/oradata/gt10gnew/
[oracle@gtlions oracle]$ ll /u01/oracle/10g/oradata/gt10gnew/con*
-rw-r-----  1 oracle oinstall 7061504  5月 29 14:30 /u01/oracle/10g/oradata/gt10gnew/control01.ctl
-rw-r-----  1 oracle oinstall 7061504  5月 29 14:30 /u01/oracle/10g/oradata/gt10gnew/control02.ctl
-rw-r-----  1 oracle oinstall 7061504  5月 29 14:30 /u01/oracle/10g/oradata/gt10gnew/control03.ctl
vi 10gpfile.ora编辑新的控制文件位置。
[oracle@gtlions oracle]$ cat 10gpfile.ora | grep control
*.control_files='/u01/oracle/10g/oradata/gt10gnew/control01.ctl','/u01/oracle/10g/oradata/gt10gnew/control02.ctl','/u01/oracle/10g/oradata/gt10gnew/control03.ctl'
然后使用pfile启动数据库，并创建spfile，然后重启使用spfile：
sys@GT10G> startup pfile='/u01/oracle/10gpfile.ora';
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size            2094800 bytes
Variable Size          104859952 bytes
Database Buffers       54525952 bytes
Redo Buffers            6291456 bytes
Database mounted.
Database opened.

sys@GT10G> create spfile from pfile='/u01/oracle/10gpfile.ora';

File created.

sys@GT10G> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@GT10G> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size            2094800 bytes
Variable Size          104859952 bytes
Database Buffers       54525952 bytes
Redo Buffers            6291456 bytes
Database mounted.
Database opened.
sys@GT10G> show parameter spfile;

NAME                     TYPE     VALUE
------------------------------------ -------- ------------------------------
spfile                     string   /u01/oracle/10g/product/10.2.0
                          /db_1/dbs/spfilegt10g.ora
现在就生效用户表空间和系统表空间了；首先对用户表空间进行处理：
sys@GT10G> alter tablespace gtlions offline;

Tablespace altered.

sys@GT10G> !
[oracle@gtlions ~]$ cp /u01/oracle/10g/oradata/gt10g/gtlions01.ora /u01/oracle/10g/oradata/gt10gnew/gtlions01.ora
[oracle@gtlions ~]$ exit
exit

sys@GT10G> alter tablespace gtlions rename datafile '/u01/oracle/10g/oradata/gt10g/gtlions01.ora' to '/u01/oracle/10g/oradata/gt10gnew/gtlions01.ora';

Tablespace altered.

sys@GT10G> alter tablespace gtlions online;

Tablespace altered.
现在还剩下3个：
/u01/oracle/10g/oradata/gt10g/users01.dbf
/u01/oracle/10g/oradata/gt10g/sysaux01.dbf
/u01/oracle/10g/oradata/gt10g/system01.dbf
针对users和sysaux可以同样按照用户表空间的处理方式：
sys@GT10G> alter tablespace users offline;

Tablespace altered.

sys@GT10G> !
[oracle@gtlions ~]$ cp /u01/oracle/10g/oradata/gt10g/users01.dbf /u01/oracle/10g/oradata/gt10gnew/users01.dbf
[oracle@gtlions ~]$ exit
exit

sys@GT10G> alter tablespace users rename datafile '/u01/oracle/10g/oradata/gt10g/users01.dbf' to '/u01/oracle/10g/oradata/gt10gnew/users01.dbf';

Tablespace altered.

sys@GT10G> alter tablespace users online;

Tablespace altered.
最后的就是system，这个表空间无法在数据库运行的情况下offline：
sys@GT10G> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
sys@GT10G> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@GT10G> !
[oracle@gtlions ~]$ cp /u01/oracle/10g/oradata/gt10g/system01.dbf /u01/oracle/10g/oradata/gt10gnew/system01.dbf
[oracle@gtlions ~]$ exit
exit

sys@GT10G> startup nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size            2094800 bytes
Variable Size          104859952 bytes
Database Buffers       54525952 bytes
Redo Buffers            6291456 bytes
sys@GT10G> alter database rename file '/u01/oracle/10g/oradata/gt10g/system01.dbf'  to '/u01/oracle/10g/oradata/gt10gnew/system01.dbf';
alter database rename file '/u01/oracle/10g/oradata/gt10g/system01.dbf'  to '/u01/oracle/10g/oradata/gt10gnew/system01.dbf'
*
ERROR at line 1:
ORA-01507: database not mounted

--要求在mount下处理
sys@GT10G> alter database mount;

Database altered.

sys@GT10G> alter database rename file '/u01/oracle/10g/oradata/gt10g/system01.dbf'  to '/u01/oracle/10g/oradata/gt10gnew/system01.dbf';

Database altered.

sys@GT10G> alter database open;

Database altered.

最后来检验下，看看我们的劳动成果吧！：

sys@GT10G>  select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
UNDOTBS2
GTLIONS
TEMP1
GTLIONSTMP1

7 rows selected.

sys@GT10G> select file_name,file_id,tablespace_name from dba_data_Files;

FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME
---------- ------------------------------
/u01/oracle/10g/oradata/gt10gnew/users01.dbf     4 USERS

/u01/oracle/10g/oradata/gt10gnew/sysaux01.dbf     3 SYSAUX

/u01/oracle/10g/oradata/gt10gnew/system01.dbf     1 SYSTEM

/u01/oracle/10g/oradata/gt10gnew/gtlions01.ora     5 GTLIONS

/u01/oracle/10g/oradata/gt10gnew/undotbs01.dbf     6 UNDOTBS2


sys@GT10G> select file_name,file_id,tablespace_name from dba_temp_Files;

FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME
---------- ------------------------------
/u01/oracle/10g/oradata/gt10gnew/gtlionstmp01.dbf     1 GTLIONSTMP1

/u01/oracle/10g/oradata/gt10gnew/temp01.dbf     3 TEMP1


sys@GT10G> select name from v$controlfile;

NAME
--------
/u01/oracle/10g/oradata/gt10gnew/control01.ctl

/u01/oracle/10g/oradata/gt10gnew/control02.ctl

/u01/oracle/10g/oradata/gt10gnew/control03.ctl


sys@GT10G> select member from v$logfile;

MEMBER
----------------------------------------------------------------------------------------------------------------------------------
/u01/oracle/10g/oradata/gt10gnew/redo04a.log
/u01/oracle/10g/oradata/gt10gnew/redo04b.log
/u01/oracle/10g/oradata/gt10gnew/redo05a.log
/u01/oracle/10g/oradata/gt10gnew/redo05b.log

sys@GT10G>
可以看到，大功告成了！
-The End-