oracle之 RAC本機資料檔案遷移至ASM

標籤：ace   comm   because   hand   oracle   xxx   ann   prot   errors   

系統內容：
CentOS release 6.7 (Final)
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

操作過程：

1、添加資料檔案或者建立資料表空間時，誤操作將路徑指定為單節點本地

SQL> alter tablespace users add datafile ‘/home/oracle/test.dbf‘ size 4m;
Tablespace altered.

SQL>
set line 180
col file_name for a60
col tablespace_name for a15
select file_name,file_id,online_status,tablespace_name from dba_data_files;

FILE_NAMEFILE_ID ONLINE_ TABLESPACE_NAME
------------------------------------------------------------ ---------- ------- ---------------
+DATA/devdb/datafile/users.259.936769201 4 ONLINEUSERS
+DATA/devdb/datafile/undotbs1.258.936769201 3 ONLINEUNDOTBS1
+DATA/devdb/datafile/sysaux.257.936769199 2 ONLINESYSAUX
+DATA/devdb/datafile/system.256.936769199 1 SYSTEMSYSTEM
+DATA/devdb/datafile/example.265.936769441 5 ONLINEEXAMPLE
/home/oracle/test.dbf 6 ONLINEUSERS
6 rows selected.

2.乾淨關閉RAC2，RAC1
srvctl stop database -d XXX

3.將RAC1啟動mount狀態
SQL> startup mount;

4.通過RMAN CP命令拷貝資料檔案
node1-> rman target /
connected to target database: DEVDB (DBID=841499351, not open)

RMAN> copy datafile ‘/home/oracle/test.dbf‘ to ‘+data‘;

Starting backup at 2017/09/17 02:13:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 instance=devdb1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/home/oracle/test.dbf
output file name=+DATA/devdb/datafile/users.273.954900787 tag=TAG20170917T021305 RECID=3 STAMP=954900786
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2017/09/17 02:13:07

Starting Control File and SPFILE Autobackup at 2017/09/17 02:13:07
piece handle=+FLASH/devdb/autobackup/2017_09_17/s_954900552.304.954900789 comment=NONE
Finished Control File and SPFILE Autobackup at 2017/09/17 02:13:10

5.在 ASM 中尋找 /home/oracle/test.dbf 映射過來對應的 ASM 檔案名稱
ASMCMD> cd data/devdb/datafile
ASMCMD> pwd
+data/devdb/datafile
ASMCMD> ls -lt user*
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE SEP 17 03:00:00 Y USERS.273.954900787
DATAFILE UNPROT COARSE SEP 17 03:00:00 Y USERS.259.936769201
說明： /home/oracle/test.dbf 對應為 USERS 資料表空間， 所以這裡為 user*

6.在sqlplus中將資料庫啟動到mount狀態，rename資料檔案
SQL> alter database rename file ‘/home/oracle/test.dbf‘ to ‘+DATA/devdb/datafile/USERS.273.954900787‘;

7.將rac1，rac2啟動
#RAC1
SQL> alter database open;
Database altered.
SQL>

#RAC2
SQL> startup;

附：SYSTEM資料檔案移植步驟（過程說明）：
1. Stop DB.
2. Move the datafile using asmcmd.
3. Mount the DB.
4. Rename the datafile.
5. Open the DB.
6. On other RAC nodes you still need to bounce the database because it is SYSTEM tablespace, otherwise you will keep getting errors ORA-01516 or original error ORA-01157: cannot identify/lock data file.

oracle之 RAC本機資料檔案遷移至ASM