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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29357786/viewspace-2221649/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29357786/viewspace-2221649/