概述:日常运维管理数据库中,难免有同事会在asm环境中将文件建立在本地系统中,如果是rac环境将严重影响到业务,就有需求需要将本地的文件平滑的迁移至asm磁盘中;
实验:表空间级使用 asmcmd cp命令迁移
(一)
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/cube/datafile/system.256.979473859
+DATA/cube/datafile/sysaux.257.979473867
+DATA/cube/datafile/undotbs1.258.979473873
+DATA/cube/datafile/users.260.979473875
SQL> create tablespace cube datafile '/u01/app/oracle/data/cube01.dbf' size 10M autoextend on ;
Tablespace created.
SQL> alter tablespace cube add datafile '/u01/app/oracle/data/cube02.dbf' size 10M autoextend on ;
Tablespace altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/cube/datafile/system.256.979473859
+DATA/cube/datafile/sysaux.257.979473867
+DATA/cube/datafile/undotbs1.258.979473873
+DATA/cube/datafile/users.260.979473875
/u01/app/oracle/data/cube01.dbf
/u01/app/oracle/data/cube02.dbf
6 rows selected.
SQL> ALTER TABLESPACE cube offline;
[root@cube2 ~]# su - grid
[grid@cube2 ~]$ asmcmd -p
ASMCMD [+data/cube/datafile] > ls
SYSAUX.257.979473867
SYSTEM.256.979473859
UNDOTBS1.258.979473873
USERS.260.979473875
ASMCMD [+data/cube/datafile] > cp /u01/app/oracle/data/cube01.dbf +data/cube/datafile/
copying /u01/app/oracle/data/cube01.dbf -> +data/cube/datafile/cube01.dbf
ASMCMD [+data/cube/datafile] > cp /u01/app/oracle/data/cube02.dbf +data/cube/datafile/
copying /u01/app/oracle/data/cube02.dbf -> +data/cube/datafile/cube02.dbf
ASMCMD [+data/cube/datafile] > ls
SYSAUX.257.979473867
SYSTEM.256.979473859
UNDOTBS1.258.979473873
USERS.260.979473875
cube01.dbf
cube02.dbf
SQL> ALTER TABLESPACE cube RENAME DATAFILE '/u01/app/oracle/data/cube01.dbf','/u01/app/oracle/data/cube02.dbf' to '+data/cube/datafile/cube01.dbf','+data/cube/datafile/cube02.dbf';
Tablespace altered.
SQL> ALTER TABLESPACE cube online;
Tablespace altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/cube/datafile/system.256.979473859
+DATA/cube/datafile/sysaux.257.979473867
+DATA/cube/datafile/undotbs1.258.979473873
+DATA/cube/datafile/users.260.979473875
+DATA/cube/datafile/cube01.dbf
+DATA/cube/datafile/cube02.dbf
6 rows selected.
(二)、文件级使用asmcmd cp 示例:
SQL> alter tablespace cube add datafile '/u01/app/oracle/data/cube03.dbf' size 10M autoextend on ;
Tablespace altered.
SQL> col name for a60
SQL> /
FILE# NAME
---------- ------------------------------------------------------------
1 +DATA/cube/datafile/system.256.979473859
2 +DATA/cube/datafile/sysaux.257.979473867
3 +DATA/cube/datafile/undotbs1.258.979473873
4 +DATA/cube/datafile/users.260.979473875
5 +DATA/cube/datafile/cube01.dbf
6 +DATA/cube/datafile/cube02.dbf
7 /u01/app/oracle/data/cube03.dbf
7 rows selected.
SQL> alter database datafile '/u01/app/oracle/data/cube03.dbf' offline;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
ASMCMD [+data/cube/datafile] > cp /u01/app/oracle/data/cube03.dbf +data/cube/datafile/
copying /u01/app/oracle/data/cube03.dbf -> +data/cube/datafile/cube03.dbf
SQL> alter database rename file '/u01/app/oracle/data/cube03.dbf' to '+data/cube/datafile/cube03.dbf';
Database altered.
SQL> alter database datafile '+data/cube/datafile/cube03.dbf' online;
alter database datafile '+data/cube/datafile/cube03.dbf' online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '+DATA/cube/datafile/cube03.dbf'
SQL> recover datafile '+data/cube/datafile/cube03.dbf';
ORA-00279: change 1056280 generated at 09/12/2018 14:47:08 needed for thread 1
ORA-00289: suggestion :
+DATA/cube/archivelog/2018_09_12/thread_1_seq_26.272.986655025
ORA-00280: change 1056280 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database datafile '+data/cube/datafile/cube03.dbf' online;
Database altered.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
+DATA/cube/datafile/system.256.979473859
+DATA/cube/datafile/sysaux.257.979473867
+DATA/cube/datafile/undotbs1.258.979473873
+DATA/cube/datafile/users.260.979473875
+DATA/cube/datafile/cube01.dbf
+DATA/cube/datafile/cube02.dbf
+DATA/cube/datafile/cube03.dbf
7 rows selected.
(三)、表空间级使用 rman backup as copy 示例:
SQL> create tablespace cube datafile '/u01/app/oracle/data/cube01.dbf' size 10M autoextend on ;
Tablespace created.
SQL> alter tablespace cube add datafile '/u01/app/oracle/data/cube02.dbf' size 10M autoextend on ;
Tablespace altered.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
+DATA/cube/datafile/system.256.979473859
+DATA/cube/datafile/sysaux.257.979473867
+DATA/cube/datafile/undotbs1.258.979473873
+DATA/cube/datafile/users.260.979473875
/u01/app/oracle/data/cube01.dbf
/u01/app/oracle/data/cube02.dbf
6 rows selected.
SQL> alter tablespace cube offline;
Tablespace altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
[oracle@cube2 data]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Sep 12 15:25:04 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: CUBE (DBID=3162950534)
RMAN> backup as copy tablespace cube format '+data';
Starting backup at 12-SEP-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/data/cube01.dbf
output file name=+DATA/cube/datafile/cube.270.986657127 tag=TAG20180912T152527 RECID=23 STAMP=986657127
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/data/cube02.dbf
output file name=+DATA/cube/datafile/cube.282.986657129 tag=TAG20180912T152527 RECID=24 STAMP=986657128
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-SEP-18
RMAN> switch tablespace cube to copy;
datafile 5 switched to datafile copy "+DATA/cube/datafile/cube.270.986657127"
datafile 6 switched to datafile copy "+DATA/cube/datafile/cube.282.986657129"
SQL> alter tablespace cube online;
Tablespace altered.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
+DATA/cube/datafile/system.256.979473859
+DATA/cube/datafile/sysaux.257.979473867
+DATA/cube/datafile/undotbs1.258.979473873
+DATA/cube/datafile/users.260.979473875
+DATA/cube/datafile/cube.270.986657127
+DATA/cube/datafile/cube.282.986657129
6 rows selected.
(四)、文件级使用 rman backup as copy 示例:
SQL> alter tablespace cube add datafile '/u01/app/oracle/data/cube03.dbf' size 10M autoextend on ;
Tablespace altered.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
+DATA/cube/datafile/system.256.979473859
+DATA/cube/datafile/sysaux.257.979473867
+DATA/cube/datafile/undotbs1.258.979473873
+DATA/cube/datafile/users.260.979473875
+DATA/cube/datafile/cube.270.986657127
+DATA/cube/datafile/cube.282.986657129
/u01/app/oracle/data/cube03.dbf
7 rows selected.
SQL> alter database datafile '/u01/app/oracle/data/cube03.dbf' offline;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
RMAN> backup as copy datafile '/u01/app/oracle/data/cube03.dbf' format '+data';
Starting backup at 12-SEP-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/data/cube03.dbf
output file name=+DATA/cube/datafile/cube.286.986657925 tag=TAG20180912T153844 RECID=27 STAMP=986657925
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-SEP-18
RMAN> switch datafile '/u01/app/oracle/data/cube03.dbf' to copy;
datafile 7 switched to datafile copy "+DATA/cube/datafile/cube.286.986657925"
RMAN> RECOVER DATAFILE "+DATA/cube/datafile/cube.286.986657925";
Starting recover at 12-SEP-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 35 is already on disk as file +DATA/cube/archivelog/2018_09_12/thread_1_seq_35.283.986657893
archived log for thread 1 with sequence 36 is already on disk as file +DATA/cube/archivelog/2018_09_12/thread_1_seq_36.284.986657893
archived log for thread 1 with sequence 37 is already on disk as file +DATA/cube/archivelog/2018_09_12/thread_1_seq_37.285.986657897
archived log file name=+DATA/cube/archivelog/2018_09_12/thread_1_seq_35.283.986657893 thread=1 sequence=35
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-SEP-18
SQL> alter database datafile 7 online;
Database altered.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
+DATA/cube/datafile/system.256.979473859
+DATA/cube/datafile/sysaux.257.979473867
+DATA/cube/datafile/undotbs1.258.979473873
+DATA/cube/datafile/users.260.979473875
+DATA/cube/datafile/cube.270.986657127
+DATA/cube/datafile/cube.282.986657129
+DATA/cube/datafile/cube.286.986657925
7 rows selected.
总结:系统文件级数据文件至asm磁盘中简单就有这四种方法,由asm至asm asm至文件系统也是同理,自行测试吧。