RAC本機資料檔案遷移至ASM的方法--非歸檔模式

RAC本機資料檔案遷移至ASM的方法--非歸檔模式

參考：RAC本機資料檔案遷移至ASM的方法 

系統內容：rhel6.2_x64+Oracle RAC11g
 
操作過程：
1.非歸檔模式
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival            Disabled
Archive destination            /u01/oracle/app/oracle/11.2.0/db/dbs/arch
Oldest online log sequence    303086
Current log sequence          303087
SQL>

2.非系統資料表空間（當前為生產環境，節點RAC1可識別本地檔案）
SQL> set line 180
SQL> col file_name for a60
SQL> col tablespace_name for a15
SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files;
FILE_NAME                                                      FILE_ID ONLINE_ TABLESPACE_NAME
------------------------------------------------------------ ---------- ------- ---------------
+DATA/sdgdorcl/datafile/system.259.848099691                          1 SYSTEM  SYSTEM
+DATA/sdgdorcl/datafile/sysaux.260.848099695                          2 ONLINE  SYSAUX
+DATA/sdgdorcl/datafile/undotbs1.261.848099697                        3 ONLINE  UNDOTBS1
+DATA/sdgdorcl/datafile/undotbs2.263.848099707                        4 ONLINE  UNDOTBS2
+DATA/sdgdorcl/datafile/users.264.848099707                          5 ONLINE  USERS
+DATA/sdgdorcl/datafile/data01.268.848183595                          6 ONLINE  data01
+DATA/sdgdorcl/datafile/nnc_index01.269.848183657                    7 ONLINE  INDEX01
+DATA/sdgdorcl/datafile/data0101.268.8481835951.ora                  8 ONLINE  data01
+DATA/sdgdorcl/datafile/data01.271.854940577                          9 ONLINE  data01
+DATA/sdgdorcl/datafile/data0102.268.8481835951.ora                  10 ONLINE  data01
+DATA/sdgdorcl/datafile/nnc_index01.20160308.ora                    11 ONLINE  INDEX01

FILE_NAME                                                      FILE_ID ONLINE_ TABLESPACE_NAME
------------------------------------------------------------ ---------- ------- ---------------
/u01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora                    12 ONLINE  data01
/u01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora                    13 ONLINE  data01
+DATA/sdgdorcl/datafile/data01.274.911612215                        14 ONLINE  data01
+DATA/sdgdorcl/datafile/data01.275.911612497                        15 ONLINE  data01
+DATA/sdgdorcl/datafile/nnc_index01.276.911612519                    16 ONLINE  INDEX01
+DATA/sdgdorcl/datafile/nnc_index01.277.911612529                    17 ONLINE  INDEX01
SQL>

3.乾淨關閉RAC2，RAC1
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

4.將RAC1啟動mount狀態
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.7103E+10 bytes
Fixed Size                  2245480 bytes
Variable Size            7381978264 bytes
Database Buffers        9663676416 bytes
Redo Buffers              55263232 bytes
Database mounted.
SQL>

5.通過RMAN CP命令拷貝資料檔案
[oracle@sdgddb1 ora_data]$ rman target /
RMAN> copy datafile '/u01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora' to '+DATA';
Starting backup at 13-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=433 instance=sdgdorcl1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00013 name=/u01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora
output file name=+DATA/sdgdorcl/datafile/data01.278.911710731 tag=TAG20160513T045849 RECID=1 STAMP=911710866
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:25
Finished backup at 13-MAY-16

RMAN> copy datafile '/u01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora' to '+DATA';
Starting backup at 13-MAY-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=/u01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora
output file name=+DATA/sdgdorcl/datafile/data01.279.911710969 tag=TAG20160513T050248 RECID=2 STAMP=911711045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
Finished backup at 13-MAY-16
RMAN>

6.在sqlplus中將資料庫啟動到mount狀態，rename資料檔案，並查看資訊
SQL> alter database rename file '/u01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora' to '+DATA/sdgdorcl/datafile/data01.278.911710731';
SQL> alter database rename file '/u01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora' to '+DATA/sdgdorcl/datafile/data01.279.911710969';

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

#RAC2
SQL> startup
ORACLE instance started.
Total System Global Area 1.7103E+10 bytes
Fixed Size                  2245480 bytes
Variable Size            7381978264 bytes
Database Buffers        9663676416 bytes
Redo Buffers              55263232 bytes
Database mounted.
Database opened.
SQL>

附：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.

如何將ASM中的資料檔案複製到作業系統中

Oracle 11g RAC ASM磁碟全部丟失後的恢複

Oracle 11g從入門到精通 PDF+光碟片原始碼

RHEL6 ASM方式安裝Oracle 11g R2

Oracle 10g 手工建立ASM資料庫

Oracle 10g R2建立ASM執行個體Step By Step