RAC数据库将数据文件创建在本地磁盘

1、节点2：创建测试表空间test，放在本地磁盘
SQL> create tablespace test datafile '/home/oracle/test01.dbf' size 10m;
Tablespace created.
SQL> select name,status from v$datafile;
NAME                                               STATUS
-------------------------------------------------- -------
+DATA/jzh/datafile/system.271.888400945            SYSTEM
+DATA/jzh/datafile/sysaux.272.888400963            ONLINE
+DATA/jzh/datafile/undotbs1.273.888400973          ONLINE
+DATA/jzh/datafile/undotbs2.275.888400999          ONLINE
+DATA/jzh/datafile/users.276.888401003             ONLINE
/home/oracle/test01.dbf                            ONLINE
6 rows selected.
2、节点2：在test表空间创建表t没有问题
SQL> create table t tablespace test as select * from dba_objects;
Table created.
3、节点1：能查到表空间test，但创建表失败
SQL> select name,status from v$datafile;
NAME                                               STATUS
-------------------------------------------------- -------
+DATA/jzh/datafile/system.271.888400945            SYSTEM
+DATA/jzh/datafile/sysaux.272.888400963            ONLINE
+DATA/jzh/datafile/undotbs1.273.888400973          ONLINE
+DATA/jzh/datafile/undotbs2.275.888400999          ONLINE
+DATA/jzh/datafile/users.276.888401003             ONLINE
/home/oracle/test01.dbf                            ONLINE
6 rows selected.
SQL> create table t1 (id int) tablespace test;
create table t1 (id int) tablespace test
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/test01.dbf'
4、将test表空间offline
RMAN> sql 'alter tablespace test offline';
using target database control file instead of recovery catalog
sql statement: alter tablespace test offline
5、copy文件到disk group
RMAN> copy datafile '/home/oracle/test01.dbf' to '+DATA/jzh/datafile/test01.dbf';
Starting backup at 15-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 instance=jzh2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/home/oracle/test01.dbf
output file name=+DATA/jzh/datafile/test01.dbf tag=TAG20150915T062514 RECID=1 STAMP=890461515
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 15-SEP-15
SQL> alter database rename file '/home/oracle/test01.dbf' to '+DATA/jzh/datafile/test01.dbf';
Database altered.
SQL> alter tablespace test online;
Tablespace altered.
6、查询test表空间位置
SQL> select name,status from v$datafile;
NAME                                               STATUS
-------------------------------------------------- -------
+DATA/jzh/datafile/system.271.888400945            SYSTEM
+DATA/jzh/datafile/sysaux.272.888400963            ONLINE
+DATA/jzh/datafile/undotbs1.273.888400973          ONLINE
+DATA/jzh/datafile/undotbs2.275.888400999          ONLINE
+DATA/jzh/datafile/users.276.888401003             ONLINE
+DATA/jzh/datafile/test01.dbf                      ONLINE
6 rows selected.
7、节点1：创建测试表t1没有问题
SQL> create table t1 (id int) tablespace test;
Table created.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10271187/viewspace-1800138/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10271187/viewspace-1800138/