[oracle@dbdream DBDREAM]$ sqlplus sys/oracle@localhost/stream as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 15 14:08:08 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBDREAM/datafile/o1_mf_undotbs1_8yd38k7y_.dbf
/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_system_8yd3zf9o_.dbf
/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_sysaux_8yd3zf7g_.dbf
/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_users_8yd42gp8_.dbf
/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_test_90r49mpz_.dbf
在/u01/app/oracle/oradata/DBDREAM目录下创建stream目录,作为数据文件迁移的目的地。
[oracle@dbdream DBDREAM]$ pwd
/u01/app/oracle/oradata/DBDREAM
[oracle@dbdream DBDREAM]$ mkdir stream
[oracle@dbdream DBDREAM]$ ls
controlfile datafile E1ADB87051AE19DDE04362F9A8C04FCA E3EDDC6F9896357AE04362F9A8C04831 onlinelog stream
下面开始演示将test数据文件迁移到stream目录,不再像以前还得先只读表空间、离线数据文件、手动移动数据文件、修改控制文件、online数据文件、修改表空间读写模式,现在只需要一条命令。
SQL> alter database move datafile '/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_test_90r49mpz_.dbf' to '/u01/app/oracle/oradata/DBDREAM/stream/test01.dbf';
Database altered.
如果数据文件不大,很快就迁移完了,再看数据文件路径,已经改了。
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBDREAM/datafile/o1_mf_undotbs1_8yd38k7y_.dbf
/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_system_8yd3zf9o_.dbf
/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_sysaux_8yd3zf7g_.dbf
/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_users_8yd42gp8_.dbf
/u01/app/oracle/oradata/DBDREAM/stream/test01.dbf
而且源路径下的o1_mf_test_90r49mpz_.dbf文件已经不见了,stream目录下生成test01.dbf文件。
[oracle@dbdream datafile]$ ll
-rw-r----- 1 oracle oinstall 650125312 Aug 15 14:39 o1_mf_system_8yd3zf9o_.dbf
-rw-r----- 1 oracle oinstall 650125312 Aug 15 14:44 o1_mf_sysaux_8yd3zf7g_.dbf
-rw-r----- 1 oracle oinstall 20979712 Aug 15 14:37 o1_mf_temp_8yd41lnw_.dbf
-rw-r----- 1 oracle oinstall 5251072 Aug 15 14:05 o1_mf_users_8yd42gp8_.dbf
[oracle@dbdream DBDREAM]$ cd stream/
[oracle@dbdream stream]$ ll
-rw-r----- 1 oracle oinstall 104865792 Aug 15 14:22 test01.dbf
那么如果迁移的文件在目标目录中已经存在了?是否也能迁移过来呢?比如我们要迁移sysaux数据文件,现在stream目录中创建sysaux01.dbf文件。
[oracle@dbdream stream]$ touch sysaux01.dbf
然后尝试迁移数据文件。
SQL> alter database move datafile '/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_sysaux_8yd3zf7g.dbf' to '/u01/app/oracle/oradata/DBDREAM/stream/sysaux01.dbf';
alter database move datafile '/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_sysaux_8yd3zf7g.dbf' to '/u01/app/oracle/oradata/DBDREAM/stream/sysaux01.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file '/u01/app/oracle/oradata/DBDREAM/stream/sysaux01.dbf'
ORA-27038: created file already exists
Additional information: 1
ORA-27038错误,文件已经存在了,ORACLE提供reuse选项,和创建表空间时指定的reuse一样,可以覆盖已存在的数据文件。
SQL> alter database move datafile '/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_sysaux_8yd3zf7g.dbf' to '/u01/app/oracle/oradata/DBDREAM/stream/sysaux01.dbf' reuse;
Database altered.
从上面的实验可以看出,在迁移数据文件时,数据文件被迁移后,源目录就不存在这个文件了,那么如果需要迁移后,元目录和目标目录都存在这个数据文件怎么办?ORACLE提供了KEEP选项。
SQL> alter database move datafile '/u01/app/oracle/oradata/DBDREAM/E1ADB87051AE19DDE04362F9A8C04FCA/datafile/o1_mf_users_8yd42gp8_.dbf' to '/u01/app/oracle/oradata/DBDREAM/stream/user01.dbf' KEEP;
这样,数据文件迁移后,在源目录和目标目录下都有数据文件,源目录下的数据文件就相当于在迁移时的一个备份。
[oracle@dbdream stream]$ ls user*
user01.dbf
[oracle@dbdream datafile]$ ls *user*
o1_mf_users_8yd42gp8_.dbf
ORACLE 12c退出的在线迁移数据文件功能,在数据文件迁移时,支持select和DML、DDL操作,对前端来讲是透明的,对业务基本没什么影响。ORACLE软件越来越智能,操作越来越简化,而大部分公司都不会用高薪养一个高级DBA,而是用很少的钱养一个初级DBA或刚毕业的新人,这些初级的DBA可以完成基本的日常工作,一旦出了复杂的问题,这些公司也不用出太多的钱,第三方维保公司就搞定了,如果一年都没出问题,就节约了不少成本,就算出几次问题,请第三方的钱也和养一个高端DBA没差多少,照此下去,高端DBA的需求将越来越少,DBA们是否该考虑转型呢?