RAC数据文件创建到本地的处理办法

mahan

处理办法
先确认数据库开启了归档模式

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +ARCH
Oldest online log sequence     58500
Next log sequence to archive   58502
Current log sequence           58502
确认被放到本地的数据文件位置

SQL> select file#,name from v$datafile where file#='56';

FILE#     NAME
------- -------------------------------------------------------------------------
56       /u01/app/oracle/oracle/product/11.2.0/dbhome_1/dbs/R:ORADATAHTEMRHTBASE01.DBF

1 rows selected.
下线数据文件

SQL> alter database datafile 56 offline;

Database altered.

SQL> select file#,status from v$datafile where file#='56';

     FILE# STATUS
---------- -------
        56 RECOVER
登录rman，把文件复制到ASM存储里

bash-3.2$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 12 09:57:12 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: EMRDB (DBID=xxxxxx)

RMAN> backup as copy datafile 56 format '+DATA/emrdb/datafile/htbase48.ora';

Starting backup at 2025-08-12 09:59:58
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2129 instance=emrdb2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00056 name=/u01/app/oracle/oracle/product/11.2.0/dbhome_1/dbs/R:ORADATAHTEMRHTBASE01.DBF
output file name=+DATA/emrdb/datafile/htbase48.ora tag=TAG20250812T095958 RECID=1 STAMP=1208944798
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2025-08-12 09:59:59
亲自到ASM里查看一下文件是否存在，用grid用户操作

$ asmcmd
ASMCMD> cd data
ASMCMD> cd emrdb
ASMCMD> cd datafile
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
<多余的我这里隐去了>
                                            N    htbase46.ora => +DATA/EMRDB/DATAFILE/HTBASE.319.1170750941
                                            N    htbase47.ora => +DATA/EMRDB/DATAFILE/HTBASE.320.1170751055
                                            N    htbase48.ora => +DATA/EMRDB/DATAFILE/HTBASE.321.1208944799
切回到Oracle用户，登录数据库，执行修改数据文件命令

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 12 10:01:19 2025
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter database rename file '/u01/app/oracle/oracle/product/11.2.0/dbhome_1/dbs/R:ORADATAHTEMRHTBASE01.DBF' to '+DATA/emrdb/datafile/htbase48.ora';

Database altered.
确认修改结果

SQL> select file#,name,status from v$datafile;

FILE#     NAME                                                             STATUS
-------   ---------------------------------          -------
56        +DATA/emrdb/datafile/htbase48.ora     RECOVER
上线数据文件

SQL> alter database recover datafile 56;
Database altered.

SQL> alter database datafile 56 online;
Database altered.
确认数据文件状态

SQL> select file#,name,status from v$datafile;

FILE#     NAME                                                            STATUS
-------   ---------------------------------    -------
56        +DATA/emrdb/datafile/htbase48.ora     ONLINE