oracle 11g rac创建数据文件到本地

1.节点一创建数据文件到本地
SQL> alter tablespace test add datafile '/u01/app/oracle/test01.dbf' size 10m autoextend off;

Tablespace altered.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +DATADG1/racdb/datafile/system.259.1090271295
         2 +DATADG1/racdb/datafile/sysaux.260.1090271329
         3 +DATADG1/racdb/datafile/undotbs1.261.1090271353
         4 +DATADG1/racdb/datafile/undotbs2.263.1090271415
         5 +DATADG1/racdb/datafile/users.264.1090271427
         6 +DATADG1/racdb/datafile/test.268.1102087241
         7 +DATADG2/racdb/datafile/test.261.1102087283
         8 +DATADG3/racdb/datafile/test.256.1102087313
         9 /u01/app/oracle/test01.dbf

9 rows selected.


2.节点二查询数据文件报错
SQL> select tablespace_name,file_name from dba_data_files;
ERROR:
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/u01/app/oracle/test01.dbf'

3.节点二创建表失败
SQL> create table tt as select * from dba_objects;
create table tt as select * from dba_objects
                                 *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/u01/app/oracle/test01.dbf'

4.数据文件offline
SQL> alter database datafile 9 offline drop;

Database altered.

5.将数据文件cp到共享存储
ASMCMD> cp '/u01/app/oracle/test01.dbf' +datadg1/racdb/datafile/test01.dbf
copying /u01/app/oracle/test01.dbf -> +datadg1/racdb/datafile/test01.dbf

SQL> alter database rename file '/u01/app/oracle/test01.dbf' to '+datadg1/racdb/datafile/test01.dbf';

Database altered. 

6.恢复数据文件
SQL> recover datafile 9;
Media recovery complete.

7.数据文件online
SQL> alter database datafile '+datadg1/racdb/datafile/test01.dbf' online;

Database altered.

8.节点二验证
SQL> col file_name for a50
SQL>
SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM                         +DATADG1/racdb/datafile/system.259.1090271295
SYSAUX                         +DATADG1/racdb/datafile/sysaux.260.1090271329
UNDOTBS1                       +DATADG1/racdb/datafile/undotbs1.261.1090271353
UNDOTBS2                       +DATADG1/racdb/datafile/undotbs2.263.1090271415
USERS                          +DATADG1/racdb/datafile/users.264.1090271427
TEST                           +DATADG1/racdb/datafile/test.268.1102087241
TEST                           +DATADG2/racdb/datafile/test.261.1102087283
TEST                           +DATADG3/racdb/datafile/test.256.1102087313
TEST                           +DATADG1/racdb/datafile/test01.dbf

9 rows selected.

SQL> create table tt as select * from dba_objects;

Table created.