欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

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

程序员文章站 2022-05-14 12:37:49
...

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上。

更多相关教程请访问 MySQL视频教程

相关标签： Oracle

上一篇： PHP实现多维数组转字符串和多维数组转一维数组的方法_php技巧

下一篇： 如何利用python读取特定目录下的特定文件的倒数两行？

推荐阅读