Oracle数据库和本地磁盘文件,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.