RAC資料庫將資料檔案建立在本地磁碟

parknkjun發表於2015-09-15
資料庫
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章