11G Oracle RAC添加新表空间时数据文件误放置到本地文件系统的修正
今天看了一篇文章说是误将新创建的表空间的数据文件放置在了本地系统而不是共享存储上。是Oracle的中文技术支持博客题目是：RAC中误将数据文件创建在本地盘时的
今天看了一篇文章说是误将新创建的表空间的数据文件放置在了本地系统而不是共享存储上。
是Oracle的中文技术支持博客题目是：RAC中误将数据文件创建在本地盘时的修正
于是我想11G 也兼容这些操作的方法，但是11G的新特性有一点就是可以直接支持ASM文件系统直接可以和本地文件系统进行文件的拷贝了，也就是有三种方法。
1. RMAN的 copy datafile 方式
2. dbms_file_transfer方式
3. ASMCMD中的直接cp方式了
分别演示三种方式：
RMAN
1)创建好本地开始的表空间
SQL> create tablespace andy datafile '/home/oracle/andy.dbf' size 100M;
Tablespace created.
2）查看一下状态
SQL> select name,status from v$datafile;
NAME
--------------------------------------------------------------------------------
STATUS
-------
+DATA/racdb/datafile/system.256.823634487
SYSTEM
+DATA/racdb/datafile/sysaux.257.823634489
ONLINE
+DATA/racdb/datafile/undotbs1.258.823634489
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
+DATA/racdb/datafile/users.259.823634491
ONLINE
+DATA/racdb/datafile/example.264.823634653
ONLINE
+DATA/racdb/datafile/undotbs2.265.823634943
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
+DATA/racdb/datafile/undotbs3.266.823634945
ONLINE
+DATA/racdb/datafile/andylhz.272.823709117
ONLINE
+DATA/racdb/datafile/archivedata.274.824748169
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
+DATA/racdb/datafile/archivedata01.275.824749235
ONLINE
/home/oracle/andy.dbf
ONLINE
3）在本地表空间上创建表 andy01
SQL> create table andy01 tablespace andy as select * from dba_objects;
Table created.
4）在第二个实例上去创建表也放在andy这个表空间上，注意红色字体部分，报告无法定位andy.dbf数据文件
[oracle@racdb2 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 12 17:00:30 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create table andy02 tablespace andy as select * from dba_objects;
create table andy02 tablespace andy as select * from dba_objects
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/home/oracle/andy.dbf'
5）然后进行修正操作，先将andy表空间进行offline
然后进行RMAN 拷贝 在将数据文件进行移动，online即可
SQL> alter database rename file '/home/oracle/andy.dbf' to '+data/racdb/datafile/andy.dbf';
第二种方式
1）先创建两个目录，offline表空间andy
SQL> create directory andy01 as '/home/oracle/';
Directory created.
SQL> create directory andy02 as '+data/racdb/datafile/';
Directory created.
SQL>
2）进行文件传输
SQL> exec dbms_file_transfer.copy_file('andy01','andy.dbf','andy02','andy.dbf');
PL/SQL procedure successfully completed.
SQL>
进入asmcmd查看一下
ASMCMD> pwd
+data/racdb/datafile
ASMCMD> ls
ANDYLHZ.272.823709117
ARCHIVEDATA.274.824748169
ARCHIVEDATA01.275.824749235
COPY_FILE.279.839351419
EXAMPLE.264.823634653
SYSAUX.257.823634489
SYSTEM.256.823634487
UNDOTBS1.258.823634489
UNDOTBS2.265.823634943
UNDOTBS3.266.823634945
USERS.259.823634491
andy.dbf
ASMCMD>
已经成功拷贝过去了
移动数据文件，online数据文件
SQL> alter database rename file '/home/oracle/andy.dbf' to '+data/racdb/datafile/andy.dbf';
Database altered.
SQL> alter tablespace andy online;
Tablespace altered.
第三种：通过grid用户进入到asmcmd环境下然后直接进入到RAC环境下的数据文件所在路径因为是数据文件创建在了oracle的家目录下，，grid没有权限读取，所以使用root用户将数据文件移动到grid家目录下同时变更权
[root@racdb1 grid]#mv andy.dbf /home/grid/
[root@racdb1 grid]#chown grid.asmadmin andy.dbf
[root@racdb1 grid]# su - grid
[grid@racdb1 ~]$ asmcmd
ASMCMD> cd data/racdb/datafile/
ASMCMD> ls
ANDYLHZ.272.823709117
ARCHIVEDATA.274.824748169
ARCHIVEDATA01.275.824749235
EXAMPLE.264.823634653
SYSAUX.257.823634489
SYSTEM.256.823634487
UNDOTBS1.258.823634489
UNDOTBS2.265.823634943
UNDOTBS3.266.823634945
USERS.259.823634491
ASMCMD> cp /home/grid/andy.dbf ./
copying /home/grid/andy.dbf -> +data/racdb/datafile/andy.dbf
ASMCMD> ls
ANDYLHZ.272.823709117
ARCHIVEDATA.274.824748169
ARCHIVEDATA01.275.824749235
EXAMPLE.264.823634653
SYSAUX.257.823634489
SYSTEM.256.823634487
UNDOTBS1.258.823634489
UNDOTBS2.265.823634943
UNDOTBS3.266.823634945
USERS.259.823634491
andy.dbf
ASMCMD>
移动数据文件到共享存储
SQL> alter database rename file '/home/oracle/andy.dbf' to '+data/racdb/datafile/andy.dbf';
Database altered.
SQL>
将数据文件online。
验证数据文件状态
SQL> select name,status from v$datafile;
NAME
--------------------------------------------------------------------------------
STATUS
-------
+DATA/racdb/datafile/system.256.823634487
SYSTEM
+DATA/racdb/datafile/sysaux.257.823634489
ONLINE
+DATA/racdb/datafile/undotbs1.258.823634489
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
+DATA/racdb/datafile/users.259.823634491
ONLINE
+DATA/racdb/datafile/example.264.823634653
ONLINE
+DATA/racdb/datafile/undotbs2.265.823634943
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
+DATA/racdb/datafile/undotbs3.266.823634945
ONLINE
+DATA/racdb/datafile/andylhz.272.823709117
ONLINE
+DATA/racdb/datafile/archivedata.274.824748169
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
+DATA/racdb/datafile/archivedata01.275.824749235
ONLINE
+DATA/racdb/datafile/andy.dbf
ONLINE
11 rows selected.
本文出自 “影子骑士” 博客，请务必保留此出处