ORACLE RAC ASM資料檔案遷移OMF檔案報錯ORA-01276解決

       ORACLE RAC ASM資料檔案遷移中，如果表空間資料檔案使用OMF自動管理，遷移到新位置時需要重新命名，否則會遇到報錯ORA-01276；解決方法很簡單，就是重新命名取消OMF命名規則即可。

1、建立測試環境

SQL> create bigfile tablespace tbigs datafile '+DATA' size 500m autoextend on;

Tablespace created.

SQL>

SQL> select tablespace_name,file_name from dba_data_files where tablespace_name=upper('tbigs');

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

TBIGS

+DATA/hxcs/datafile/tbigs.412.993211437

SQL>

SQL> create user zhul identified by zhul default tablespace tbigs;

User created.

SQL> 

SQL> grant resource to zhul;

Grant succeeded.

SQL> grant create session to zhul;

Grant succeeded.

SQL> conn zhul/zhul

Connected.

SQL> 

SQL> insert into t values(1,'aaaaa');

1 row created.

SQL> commit;

SQL> conn / as sysdba

Connected.

SQL> select tablespace_name,file_name ,status from dba_data_files where tablespace_name=upper('tbigs');

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

STATUS

---------

TBIGS

+DATA/hxcs/datafile/tbigs.412.993211437

AVAILABLE

SQL>

2、 測試將tbigs從data磁碟組遷移到fra磁碟組

SQL> select group_number,name from v$asm_diskgroup;

GROUP_NUMBER NAME

------------ ------------------------------

           1 DATA

           2 FRA

           3 OCR

SQL>

3、確定要遷移的表空間和資料檔案

SQL>select name,file#,vd.status from v$datafile vd,dba_data_files ddf where tablespace_name=upper('tbigs') and vd.file#=ddf.file_id

NAME

--------------------------------------------------------------------------------

     FILE# STATUS

---------- -------

+DATA/hxcs/datafile/tbigs.412.993211437

        26 ONLINE

SQL> 

4、將tbigs表空間下線

SQL> alter tablespace tbigs offline;

Tablespace altered.

SQL> select name,file#,vd.status from v$datafile vd,dba_data_files ddf where tablespace_name=upper('tbigs') and vd.file#=ddf.file_id;

NAME

--------------------------------------------------------------------------------

     FILE# STATUS

---------- -------

+DATA/hxcs/datafile/tbigs.412.993211437

        26 OFFLINE

SQL>

5、 另開會話oracle登陸rman複製資料檔案到新的位置

Ccbsdb@oracle[/home/oracle]export ORACLE_SID=hxcs1

Ccbsdb@oracle[/home/oracle]rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Nov 26 12:32:55 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HXCS (DBID=1189523002)

RMAN> copy datafile '+DATA/hxcs/datafile/tbigs.412.993211437' to '+fra/hxcs/tbigs_01.dbf';

Starting backup at 26-NOV-18

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00026 name=+DATA/hxcs/datafile/tbigs.412.993211437

output file name=+FRA/hxcs/tbigs_01.dbf tag=TAG20181126T123515 RECID=1 STAMP=993213316

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 26-NOV-18

注意：原資料檔案如果是omf自動管理的，到新位置需要重新命名資料檔名字取消omf，否則報錯：

RMAN> copy datafile '+DATA/hxcs/datafile/tbigs.412.993211437' to '+fra/hxcs/tbigs.412.993211437';

Starting backup at 26-NOV-18

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4468 instance=hxcs1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00026 name=+DATA/hxcs/datafile/tbigs.412.993211437

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/26/2018 12:34:13

ORA-01276: Cannot add file +fra/hxcs/tbigs.412.993211437.  File has an Oracle Managed Files file name.

6、將tbigs的資料檔案重定向到新位置

SQL> alter database  rename file '+DATA/hxcs/datafile/tbigs.412.993211437'  to '+fra/hxcs/tbigs_01.dbf';

Database altered.

SQL> 

7、將tbigs表空間上線

SQL> alter tablespace tbigs online;

Tablespace altered.

SQL>  select name,file#,vd.status from v$datafile vd,dba_data_files ddf where tablespace_name=upper('tbigs') and vd.file#=ddf.file_id;

NAME

--------------------------------------------------------------------------------

     FILE# STATUS

---------- -------

+FRA/hxcs/tbigs_01.dbf

        26 ONLINE

SQL>

8、檢查資料

SQL> conn zhul/zhul  

Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

T                              TABLE

SQL> select * from t;

        N1 C1

---------- ----------

         1 aaaaa