RAC环境下创建本地数据文件的解决方法

最新推荐文章于 2025-12-31 19:19:25 发布

转载最新推荐文章于 2025-12-31 19:19:25 发布 · 191 阅读

文章标签：

本文介绍了一种在Oracle RAC环境中误创建本地数据文件后的修复方法。通过离线数据文件、复制到共享磁盘（ASM）、重命名并恢复在线状态等一系列步骤，解决了节点间数据文件不可访问的问题。

同事不小心，在RAC环境下创建了本地数据文件，这个肯定会出问题的，节点2不能访问此数据文件。其实发现做错了，立马删掉应该没有问题。数据文件还没有数据。
下面演示一下错误的解决方法，思路就是，把本地文件COPY到共享磁盘中（ASM），在rename即可。

点击(此处)折叠或打开

SQL> alter tablespace USERS add datafile '/home/oracle/user01.dbf' size 10m;//创建错误过程
Tablespace altered.
SQL> alter database datafile 8 offline;
Database altered.
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
oracle@zbdb1:~/product/10.2.0/db_1/bin> rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Feb 18 16:17:54 2014
connected to target database: QT0000J2 (DBID=3109660417)
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> copy datafile '/home/oracle/user01.dbf' to '+RACDG';
Starting backup at 18-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1060 instance=qt0000j21 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00008 name=/home/oracle/user01.dbf
output filename=+RACDG/qt0000j2/datafile/users.270.839866683 tag=TAG20140218T161802 recid=1 stamp=839866682
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-FEB-14
RMAN> exit
Recovery Manager complete.
oracle@zbdb1:~/product/10.2.0/db_1/bin> sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Feb 18 16:19:02 2014
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
SQL>
SQL> alter database rename file '/home/oracle/user01.dbf' to '+RACDG/qt0000j2/datafile/users.270.839866683';
Database altered.
SQL>
SQL>
SQL>
SQL> select name from v$datafiles;
select name from v$datafiles
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+RACDG/qt0000j2/datafile/system.259.730655367
+RACDG/qt0000j2/datafile/undotbs1.260.730655371
+RACDG/qt0000j2/datafile/sysaux.261.730655371
+RACDG/qt0000j2/datafile/undotbs2.263.730655377
+RACDG/qt0000j2/datafile/users.264.730655377
+RACDG/qt0000j2/datafile/ciimss_ts.268.733087301
+RACDG/qt0000j2/datafile/ciimss_idx_ts.269.733087365
+RACDG/qt0000j2/datafile/users.270.839866683
8 rows selected.
SQL> select file_name,file_id,ONLINE_STATUS from dba_data_files;
+RACDG/qt0000j2/datafile/users.270.839866683 8 RECOVER
SQL> recover datafile 8;
ORA-00279: change 225618407 generated at 02/18/2014 16:17:27 needed for thread
1
ORA-00289: suggestion :
/home/oracle/product/10.2.0/db_1/dbs/arch1_2667_730655361.dbf
ORA-00280: change 225618407 for thread 1 is in sequence #2667
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> select file_name,file_id,ONLINE_STATUS from dba_data_files;
+RACDG/qt0000j2/datafile/users.270.839866683 8 OFFLINE
SQL> alter database datafile \'+RACDG/qt0000j2/datafile/users.270.839866683\' online;