数据文件从系统文件迁移到asm的操作

sys@RAC> select file_name ,tablespace_name from dba_data_files ;

FILE_NAME                      TABLESPACE_NAME
--------------------------------------------- ------------------------------
+DATA/rac/datafile/system.259.926647219       SYSTEM
+DATA/rac/datafile/sysaux.260.926647323       SYSAUX
+DATA/rac/datafile/undotbs1.261.926647405     UNDOTBS1
+DATA/rac/datafile/undotbs2.263.926647467     UNDOTBS2
+DATA/rac/datafile/users.264.926647499          USERS
+DATA/rac/datafile/admin_tbs.274.927927081    ADMIN_TBS
+DATA/rac/datafile/admin_tb2.275.927927171    ADMIN_TB2
+DATA/rac/datafile/tbs_low_freq.276.927939255 TBS_LOW_FREQ
/oracle/app/oracle/11.2/db_1/dbs/q1_orders    Q1_ORDERS
/oracle/app/oracle/11.2/db_1/dbs/q2_orders    Q2_ORDERS
/oracle/app/oracle/11.2/db_1/dbs/q3_orders    Q3_ORDERS
/oracle/app/oracle/11.2/db_1/dbs/q4_orders    Q4_ORDERS
/oracle/app/oracle/11.2/db_1/dbs/2006_orders  2006_ORDERS
/oracle/app/oracle/11.2/db_1/dbs/2005_orders  2005_ORDERS
/oracle/app/oracle/11.2/db_1/dbs/2004_orders  2004_ORDERS
/oracle/app/oracle/11.2/db_1/dbs/old_orders   OLD_ORDERS
/oracle/app/oracle/11.2/db_1/dbs/cc_this_mont CC_THIS_MONTH

数据文件都节点1本地。

案例重演：
sys@RAC> show parameter db_create_file_dest

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest             string     +DATA

采用了omf管理
sys@RAC> create tablespace qn datafile size 20m ;

Tablespace created.

sys@RAC> select file_name ,tablespace_name from dba_data_files ;

FILE_NAME                      TABLESPACE_NAME
--------------------------------------------- ------------------------------
.........
+DATA/rac/datafile/qn.277.928807173          QN
此时发现数据文件qn建在ASM存储上

sys@RAC> create tablespace qn2 datafile 'qn2' size 20m ;
Tablespace created.
sys@RAC> select file_name ,tablespace_name from dba_data_files ;

FILE_NAME                      TABLESPACE_NAME
--------------------------------------------- ------------------------------
/oracle/app/oracle/11.2/db_1/dbs/cc_prev_12   CC_PREV_12MTH
/oracle/app/oracle/11.2/db_1/dbs/cc_old_tran  CC_OLD_TRAN
+DATA/rac/datafile/qn.277.928807173          QN
/oracle/app/oracle/11.2/db_1/dbs/qn2          QN2
此时发现数据文件qn2建在系统文件上,这样会出问题的，其节点报错。

那么如何把这个数据库的系统文件迁移到ASM上呢？

在线迁移数据文件至ASM上：

1. offline数据文件
   select file_name ,tablespace_name from dba_data_files  where tablespace_name in ('Q1_ORDERS','Q2_ORDERS','Q3_ORDERS','Q4_ORDERS','2006_ORDERS','2005_ORDERS','2004_ORDERS','OLD_ORDERS','CC_THIS_MONTH','CC_PREV_MONTH','CC_PREV_12MTH','QN2' ) ;
   FILE_NAME                             TABLESPACE_NAME
   ------------------------------------------------------------ ------------------------------
   /oracle/app/oracle/11.2/db_1/dbs/q1_orders             Q1_ORDERS
   /oracle/app/oracle/11.2/db_1/dbs/q2_orders             Q2_ORDERS
   /oracle/app/oracle/11.2/db_1/dbs/q3_orders             Q3_ORDERS
   /oracle/app/oracle/11.2/db_1/dbs/q4_orders             Q4_ORDERS
   /oracle/app/oracle/11.2/db_1/dbs/2006_orders             2006_ORDERS
   /oracle/app/oracle/11.2/db_1/dbs/2005_orders             2005_ORDERS
   /oracle/app/oracle/11.2/db_1/dbs/2004_orders             2004_ORDERS
   /oracle/app/oracle/11.2/db_1/dbs/old_orders             OLD_ORDERS
   /oracle/app/oracle/11.2/db_1/dbs/cc_this_month             CC_THIS_MONTH
   /oracle/app/oracle/11.2/db_1/dbs/cc_prev_month             CC_PREV_MONTH
   /oracle/app/oracle/11.2/db_1/dbs/cc_prev_12             CC_PREV_12MTH
   /oracle/app/oracle/11.2/db_1/dbs/qn2                 QN2

2. rman copy
   alter tablespace Q1_ORDERS offline ;

   backup as copy datafile '/oracle/app/oracle/11.2/db_1/dbs/q1_orders'  format '+DATA';
   or
   copy datafile  '/oracle/app/oracle/11.2/db_1/dbs/q1_orders' to '+DATA';


  RMAN>  copy datafile  '/oracle/app/oracle/11.2/db_1/dbs/q1_orders' to '+DATA';

  Starting backup at 2016-11-25 02:55:42
  using target database control file instead of recovery catalog
  allocated channel: ORA_DISK_1
  channel ORA_DISK_1: SID=28 instance=rac1 device type=DISK
  channel ORA_DISK_1: starting datafile copy
  input datafile file number=00009 name=/oracle/app/oracle/11.2/db_1/dbs/q1_orders
  output file name=+DATA/rac/datafile/q1_orders.278.928810547 tag=TAG20161125T025546 RECID=1 STAMP=928810550
  channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
  Finished backup at 2016-11-25 02:55:50

3. rename
  alter database rename file 'D:\NSOADP\SYSTEM01.DBF' to '+ORADATA/nsoa/datafile/system.260.877082571';
  or
  switch datafile 37 to copy;

  RMAN> switch datafile '/oracle/app/oracle/11.2/db_1/dbs/q1_orders' to copy ;
  datafile 9 switched to datafile copy "+DATA/rac/datafile/q1_orders.278.928810547"


4. recover
    recover datafile '+DATA/rac/datafile/q1_orders.278.928810547';

5. online
  alter tablespace Q1_ORDERS online

  sys@RAC> select file_name ,tablespace_name from dba_data_files  where tablespace_name in ('Q1_ORDERS')
    2  /

  FILE_NAME                             TABLESPACE_NAME
  ------------------------------------------------------------ ------------------------------
  +DATA/rac/datafile/q1_orders.278.928810547             Q1_ORDERS


快速恢复:
  sys@RAC> select file_name ,tablespace_name from dba_data_files  where tablespace_name in ('Q2_ORDERS','Q3_ORDERS','Q4_ORDERS','2006_ORDERS','2005_ORDERS','2004_ORDERS','OLD_ORDERS','CC_THIS_MONTH','CC_PREV_MONTH','CC_PREV_12MTH','QN2' ) ;


  FILE_NAME                             TABLESPACE_NAME
  ------------------------------------------------------------ ------------------------------
  /oracle/app/oracle/11.2/db_1/dbs/q2_orders             Q2_ORDERS
  /oracle/app/oracle/11.2/db_1/dbs/q3_orders             Q3_ORDERS
  /oracle/app/oracle/11.2/db_1/dbs/q4_orders             Q4_ORDERS
  /oracle/app/oracle/11.2/db_1/dbs/2006_orders             2006_ORDERS
  /oracle/app/oracle/11.2/db_1/dbs/2005_orders             2005_ORDERS
  /oracle/app/oracle/11.2/db_1/dbs/2004_orders             2004_ORDERS
  /oracle/app/oracle/11.2/db_1/dbs/old_orders             OLD_ORDERS
  /oracle/app/oracle/11.2/db_1/dbs/cc_this_month             CC_THIS_MONTH
  /oracle/app/oracle/11.2/db_1/dbs/cc_prev_month             CC_PREV_MONTH
  /oracle/app/oracle/11.2/db_1/dbs/cc_prev_12             CC_PREV_12MTH
  /oracle/app/oracle/11.2/db_1/dbs/qn2                 QN2


sys@RAC>    select 'alter tablespace '|| tablespace_name||' offline;'  from dba_data_files  where tablespace_name in ('Q2_ORDERS','Q3_ORDERS','Q4_ORDERS','2006_ORDERS','2005_ORDERS','2004_ORDERS','OLD_ORDERS','CC_THIS_MONTH','CC_PREV_MONTH','CC_PREV_12MTH','QN2' ) ;


'ALTERTABLESPACE'||TABLESPACE_NAME||'OFFLINE;'
--------------------------------------------------------
alter tablespace Q2_ORDERS offline;
alter tablespace Q3_ORDERS offline;
alter tablespace Q4_ORDERS offline;
alter tablespace 2006_ORDERS offline;
alter tablespace 2005_ORDERS offline;
alter tablespace 2004_ORDERS offline;
alter tablespace OLD_ORDERS offline;
alter tablespace CC_THIS_MONTH offline;
alter tablespace CC_PREV_MONTH offline;
alter tablespace CC_PREV_12MTH offline;
alter tablespace QN2 offline;


sys@RAC> sys@RAC> alter tablespace Q2_ORDERS offline;
alter tablespace Q3_ORDERS offline;
alter tablespace Q4_ORDERS offline;
alter tablespace 2006_ORDERS offline;
alter tablespace 2005_ORDERS offline;
alter tablespace 2004_ORDERS offline;
alter tablespace OLD_ORDERS offline;
alter tablespace CC_THIS_MONTH offline;
alter tablespace CC_PREV_MONTH offline;
alter tablespace CC_PREV_12MTH offline;
alter tablespace QN2 offline;

sys@RAC> alter tablespace "2006_ORDERS" offline
                 *
ERROR at line 1:
ORA-02140: invalid tablespace name


sys@RAC> alter tablespace 2005_ORDERS offline
                 *
ERROR at line 1:
ORA-02140: invalid tablespace name


sys@RAC> alter tablespace 2004_ORDERS offline
                 *
ERROR at line 1:
ORA-02140: invalid tablespace name

alter tablespace "2004_ORDERS" offline;
alter tablespace "2005_ORDERS" offline;
alter tablespace "2006_ORDERS" offline;


 select 'copy datafile '''||file_name||''' to ''+DATA'';'  from dba_data_files  where tablespace_name in ('Q2_ORDERS','Q3_ORDERS','Q4_ORDERS','2006_ORDERS','2005_ORDERS','2004_ORDERS','OLD_ORDERS','CC_THIS_MONTH','CC_PREV_MONTH','CC_PREV_12MTH','QN2' ) ;

'COPYDATAFILE'''||FILE_NAME||'''TO''+DATA'';'
----------------------------------------------------------------------------------------------------------------------------------------------------------
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/q2_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/q3_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/q4_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/2006_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/2005_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/2004_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/old_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_this_month' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_prev_month' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_prev_12' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/qn2' to '+DATA';

RMAN> copy datafile '/oracle/app/oracle/11.2/db_1/dbs/q2_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/q3_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/q4_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1
/dbs/2006_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/2005_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/2004_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/old_orders' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_this_month' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_prev_month' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_prev_12' to '+DATA';
copy datafile '/oracle/app/oracle/11.2/db_1/dbs/qn2' to '+DATA';Starting backup at 2016-11-25 03:20:57
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/oracle/app/oracle/11.2/db_1/dbs/q2_orders
output file name=+DATA/rac/datafile/q2_orders.279.928812061 tag=TAG20161125T032059 RECID=3 STAMP=928812062
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2016-11-25 03:21:02

RMAN>
Starting backup at 2016-11-25 03:21:08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/oracle/app/oracle/11.2/db_1/dbs/q3_orders
output file name=+DATA/rac/datafile/q3_orders.280.928812071 tag=TAG20161125T032109 RECID=4 STAMP=928812073
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2016-11-25 03:21:17

RMAN>
Starting backup at 2016-11-25 03:21:22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=/oracle/app/oracle/11.2/db_1/dbs/q4_orders
output file name=+DATA/rac/datafile/q4_orders.281.928812085 tag=TAG20161125T032124 RECID=5 STAMP=928812088
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2016-11-25 03:21:28

RMAN>
Starting backup at 2016-11-25 03:21:33
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00013 name=/oracle/app/oracle/11.2/db_1/dbs/2006_orders
output file name=+DATA/rac/datafile/2006_orders.282.928812095 tag=TAG20161125T032134 RECID=6 STAMP=928812099
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2016-11-25 03:21:42

RMAN>
Starting backup at 2016-11-25 03:21:46
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=/oracle/app/oracle/11.2/db_1/dbs/2005_orders
output file name=+DATA/rac/datafile/2005_orders.283.928812109 tag=TAG20161125T032148 RECID=7 STAMP=928812112
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2016-11-25 03:21:56

RMAN>
Starting backup at 2016-11-25 03:22:00
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/oracle/app/oracle/11.2/db_1/dbs/2004_orders
output file name=+DATA/rac/datafile/2004_orders.284.928812123 tag=TAG20161125T032202 RECID=8 STAMP=928812126
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2016-11-25 03:22:10

RMAN>
Starting backup at 2016-11-25 03:22:14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=/oracle/app/oracle/11.2/db_1/dbs/old_orders
output file name=+DATA/rac/datafile/old_orders.285.928812137 tag=TAG20161125T032216 RECID=9 STAMP=928812141
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2016-11-25 03:22:24

RMAN>
Starting backup at 2016-11-25 03:22:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=/oracle/app/oracle/11.2/db_1/dbs/cc_this_month
output file name=+DATA/rac/datafile/cc_this_month.286.928812151 tag=TAG20161125T032231 RECID=10 STAMP=928812154
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2016-11-25 03:22:34

RMAN>
Starting backup at 2016-11-25 03:22:45
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=/oracle/app/oracle/11.2/db_1/dbs/cc_prev_month
output file name=+DATA/rac/datafile/cc_prev_month.287.928812167 tag=TAG20161125T032246 RECID=11 STAMP=928812167
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2016-11-25 03:22:47

RMAN>
Starting backup at 2016-11-25 03:22:52
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=/oracle/app/oracle/11.2/db_1/dbs/cc_prev_12
output file name=+DATA/rac/datafile/cc_prev_12mth.288.928812175 tag=TAG20161125T032253 RECID=12 STAMP=928812175
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2016-11-25 03:22:57

Starting backup at 2016-11-25 03:26:09

RMAN>
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00022 name=/oracle/app/oracle/11.2/db_1/dbs/qn2
output file name=+DATA/rac/datafile/qn2.289.928812371 tag=TAG20161125T032611 RECID=13 STAMP=928812376
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2016-11-25 03:26:18


 sys@RAC> select 'switch  datafile '''||file_name||''' to copy;'  from dba_data_files  where tablespace_name in ('Q2_ORDERS','Q3_ORDERS','Q4_ORDERS','2006_ORDERS','2005_ORDERS','2004_ORDERS','OLD_ORDERS','CC_THIS_MONTH','CC_PREV_MONTH','CC_PREV_12MTH','QN2' ) ;

'SWITCHDATAFILE'''||FILE_NAME||'''TOCOPY;'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/q2_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/q3_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/q4_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/2006_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/2005_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/2004_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/old_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_this_month' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_prev_month' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_prev_12' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/qn2' to copy;

RMAN> switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/q2_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/q3_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/q4_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/
dbs/2006_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/2005_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/2004_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/old_orders' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_this_month' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_prev_month' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/cc_prev_12' to copy;
switch    datafile '/oracle/app/oracle/11.2/db_1/dbs/qn2' to copy;
datafile 10 switched to datafile copy "+DATA/rac/datafile/q2_orders.279.928812061"
RMAN>
datafile 11 switched to datafile copy "+DATA/rac/datafile/q3_orders.280.928812071"
RMAN>
datafile 12 switched to datafile copy "+DATA/rac/datafile/q4_orders.281.928812085"
RMAN>
datafile 13 switched to datafile copy "+DATA/rac/datafile/2006_orders.282.928812095"
RMAN>
datafile 14 switched to datafile copy "+DATA/rac/datafile/2005_orders.283.928812109"
RMAN>
datafile 15 switched to datafile copy "+DATA/rac/datafile/2004_orders.284.928812123"
RMAN>
datafile 16 switched to datafile copy "+DATA/rac/datafile/old_orders.285.928812137"
RMAN>
datafile 17 switched to datafile copy "+DATA/rac/datafile/cc_this_month.286.928812151"
RMAN>
datafile 18 switched to datafile copy "+DATA/rac/datafile/cc_prev_month.287.928812167"
RMAN>
datafile 19 switched to datafile copy "+DATA/rac/datafile/cc_prev_12mth.288.928812175"
RMAN>
datafile 22 switched to datafile copy "+DATA/rac/datafile/qn2.289.928812371"




alter tablespace Q3_ORDERS online;
alter tablespace Q4_ORDERS online;
alter tablespace "2006_ORDERS" online;
alter tablespace "2005_ORDERS" online;
alter tablespace "2004_ORDERS" online;
alter tablespace OLD_ORDERS online;
alter tablespace CC_THIS_MONTH online;
alter tablespace CC_PREV_MONTH online;
alter tablespace CC_PREV_12MTH online;
alter tablespace QN2 online;


sys@RAC>  select file_name ,tablespace_name from dba_data_files  where tablespace_name in ('Q1_ORDERS','Q2_ORDERS','Q3_ORDERS','Q4_ORDERS','2006_ORDERS','2005_ORDERS','2004_ORDERS','OLD_ORDERS','CC_THIS_MONTH','CC_PREV_MONTH','CC_PREV_12MTH','QN2' ) ;
FILE_NAME                             TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
+DATA/rac/datafile/q1_orders.278.928810547             Q1_ORDERS
+DATA/rac/datafile/q2_orders.279.928812061             Q2_ORDERS
+DATA/rac/datafile/q3_orders.280.928812071             Q3_ORDERS
+DATA/rac/datafile/q4_orders.281.928812085             Q4_ORDERS
+DATA/rac/datafile/2006_orders.282.928812095             2006_ORDERS
+DATA/rac/datafile/2005_orders.283.928812109             2005_ORDERS
+DATA/rac/datafile/2004_orders.284.928812123             2004_ORDERS
+DATA/rac/datafile/old_orders.285.928812137             OLD_ORDERS
+DATA/rac/datafile/cc_this_month.286.928812151             CC_THIS_MONTH
+DATA/rac/datafile/cc_prev_month.287.928812167             CC_PREV_MONTH
+DATA/rac/datafile/cc_prev_12mth.288.928812175             CC_PREV_12MTH
+DATA/rac/datafile/qn2.289.928812371                 QN2

至此系统上的数据文件都迁移到asm上。