Oracle 将文件系统中的数据文件在线迁移到ASM磁盘组

一、节点一创建数据文件到文件系统

SQL> create tablespace xxx datafile 'data' size 10M; 

Tablespace created.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/or11g/datafile/users.268.1011351479
+DATA/or11g/datafile/undotbs1.267.1011351479
+DATA/or11g/datafile/sysaux.266.1011351477
+DATA/or11g/datafile/system.265.1011351477
+DATA/or11g/datafile/example.273.1011351649
+DATA/or11g/datafile/undotbs2.274.1011351929
+DATA/or11g/datafile/goldengate.dbf
+DATA/or11g/datafile/wireless_site.279.1015844369
+DATA/or11g/datafile/test.280.1015844455
/u01/app/oracle/product/11.2.0/db_1/dbs/data

10 rows selected.

SQL> 

二、节点2查询

[oracle@or11g2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 20 15:42:51 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> 
SQL> select file_name from dba_data_files;
ERROR:
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/app/oracle/product/11.2.0/db_1/dbs/data'



no rows selected

SQL> 

三、查询数据文件名和文件编号

SQL> set linesize 200
SQL> col file_name format a50
SQL> select file_name,file_id from dba_data_files;

FILE_NAME					      FILE_ID
-------------------------------------------------- ----------
+DATA/or11g/datafile/users.268.1011351479		    4
+DATA/or11g/datafile/undotbs1.267.1011351479		    3
+DATA/or11g/datafile/sysaux.266.1011351477		    2
+DATA/or11g/datafile/system.265.1011351477		    1
+DATA/or11g/datafile/example.273.1011351649		    5
+DATA/or11g/datafile/undotbs2.274.1011351929		    6
+DATA/or11g/datafile/goldengate.dbf			    7
+DATA/or11g/datafile/wireless_site.279.1015844369	    8
+DATA/or11g/datafile/test.280.1015844455		    9
/u01/app/oracle/product/11.2.0/db_1/dbs/data		   10

10 rows selected.

SQL> 

四、拷贝数据文件到ASM磁盘组

[oracle@or11g1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 20 15:46:35 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: OR11G (DBID=776612080)

RMAN> backup as copy datafile 10 format '+data';

Starting backup at 20-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=139 instance=or11g1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/u01/app/oracle/product/11.2.0/db_1/dbs/data
output file name=+DATA/or11g/datafile/xxx.281.1019490421 tag=TAG20190920T154701 RECID=2 STAMP=1019490421
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-SEP-19

RMAN> 

五、更新控制文件

RMAN> switch datafile 10 to copy;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 09/20/2019 15:52:54
RMAN-06572: database is open and datafile 10 is not offline

RMAN> 

提示数据文件没有离线

SQL> set linesize 200
SQL> col file_name format a50
SQL> select file_name,file_id from dba_data_files;

FILE_NAME					      FILE_ID
-------------------------------------------------- ----------
+DATA/or11g/datafile/users.268.1011351479		    4
+DATA/or11g/datafile/undotbs1.267.1011351479		    3
+DATA/or11g/datafile/sysaux.266.1011351477		    2
+DATA/or11g/datafile/system.265.1011351477		    1
+DATA/or11g/datafile/example.273.1011351649		    5
+DATA/or11g/datafile/undotbs2.274.1011351929		    6
+DATA/or11g/datafile/goldengate.dbf			    7
+DATA/or11g/datafile/wireless_site.279.1015844369	    8
+DATA/or11g/datafile/test.280.1015844455		    9
/u01/app/oracle/product/11.2.0/db_1/dbs/data		   10

10 rows selected.

SQL> 
SQL> alter database datafile 10 offline;

Database altered.

SQL> 

[oracle@or11g1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 20 15:55:41 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: OR11G (DBID=776612080)

RMAN> switch datafile 10 to copy;

using target database control file instead of recovery catalog
datafile 10 switched to datafile copy "+DATA/or11g/datafile/xxx.281.1019490421"

RMAN> 

六、恢复数据文件10

RMAN> recover datafile 10;

Starting recover at 20-SEP-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 instance=or11g1 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 20-SEP-19

七、将数据文件上线

SQL> alter database datafile 10 online;

Database altered.

SQL> 

八、节点2再次查询

SQL> set linesize 200
SQL> col file_name format a50
SQL> select file_name,file_id from dba_data_files;

FILE_NAME					      FILE_ID
-------------------------------------------------- ----------
+DATA/or11g/datafile/users.268.1011351479		    4
+DATA/or11g/datafile/undotbs1.267.1011351479		    3
+DATA/or11g/datafile/sysaux.266.1011351477		    2
+DATA/or11g/datafile/system.265.1011351477		    1
+DATA/or11g/datafile/example.273.1011351649		    5
+DATA/or11g/datafile/undotbs2.274.1011351929		    6
+DATA/or11g/datafile/goldengate.dbf			    7
+DATA/or11g/datafile/wireless_site.279.1015844369	    8
+DATA/or11g/datafile/test.280.1015844455		    9
+DATA/or11g/datafile/xxx.281.1019490421 		   10

10 rows selected.

SQL>