11g RAC添加用户表空间（数据文件）至文件系统（File System）的修正

前提：非TEMP、UNDO和SYSTEM表空间，这仨是大爷，您得搂着点。来自博客园AskScuti 。客户是添加临时表空间数据文件时，不小心 ADD 到了文件系统中，然后发现，后悔了，还在OS层面 RM 了，重建调整吧。

实验就拿着普通的用户表空间练手吧。

目录

1. 创建用户表空间

2. 故意添加错误路径的数据文件

3. 查询报错

4. 表空间脱机

5. 通过RMAN进行COPY

6. 数据文件重命名

7. 数据文件RECOVER

8. 表空间联机

 

1. 创建用户表空间

SQL> create tablespace henry datafile '+ASMDATA' size 1m;

Tablespace created.

SQL> select name from v$datafile;

NAME
-------------------------------------------------------
+ASMSYSTEM/racerp/datafile/system.260.1005224067
+ASMSYSTEM/racerp/datafile/sysaux.261.1005224093
+ASMSYSTEM/racerp/datafile/undotbs1.262.1005224115
+ASMSYSTEM/racerp/datafile/undotbs2.264.1005224141
+ASMSYSTEM/racerp/datafile/users.265.1005224147
+ASMDATA/racerp/datafile/test.256.1005234027
+ASMDATA/racerp/datafile/henry.257.1010151449

2. 故意添加错误路径的数据文件

SQL> alter tablespace henry add datafile '/u01/app/oracle/henry02.dbf' size 1m;

Tablespace altered.

SQL> select name from v$datafile;

NAME
-------------------------------------------------------
+ASMSYSTEM/racerp/datafile/system.260.1005224067
+ASMSYSTEM/racerp/datafile/sysaux.261.1005224093
+ASMSYSTEM/racerp/datafile/undotbs1.262.1005224115
+ASMSYSTEM/racerp/datafile/undotbs2.264.1005224141
+ASMSYSTEM/racerp/datafile/users.265.1005224147
+ASMDATA/racerp/datafile/test.256.1005234027
+ASMDATA/racerp/datafile/henry.257.1010151449
/u01/app/oracle/henry02.dbf

3. 查询报错

SQL> select tablespace_name,file_id from dba_temp_files;
select tablespace_name,file_id from dba_temp_files
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/henry02.dbf'

发现路径错了。

4. 表空间脱机

SQL> alter tablespace henry offline;

Tablespace altered.

5. 通过RMAN进行COPY

[oracle@erpn2:/home/oracle]$rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 5 13:45:31 2019

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

connected to target database: RACERP (DBID=1820589684)

RMAN> copy datafile '/u01/app/oracle/henry02.dbf' to '+ASMDATA';

Starting backup at 2019-06-05 13:45:33
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 instance=RACERP_2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u01/app/oracle/henry02.dbf
output file name=+ASMDATA/racerp/datafile/henry.261.1010151935 tag=TAG20190605T134535 RECID=4 STAMP=1010151935
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2019-06-05 13:45:36

Starting Control File and SPFILE Autobackup at 2019-06-05 13:45:36
piece handle=/backup/RACERP_c-1820589684-20190605-03.ctl comment=NONE
Finished Control File and SPFILE Autobackup at 2019-06-05 13:45:43

6. 数据文件重命名

SQL> alter database rename file '/u01/app/oracle/henry02.dbf' to '+ASMDATA/racerp/datafile/henry.261.1010151935';

Database altered.

7. 数据文件RECOVER

SQL> recover datafile 8;
Media recovery complete.

8. 表空间联机

SQL> alter tablespace henry online;

Tablespace altered.

SQL> select name from v$datafile;

NAME
------------------------------------------------------
+ASMSYSTEM/racerp/datafile/system.260.1005224067
+ASMSYSTEM/racerp/datafile/sysaux.261.1005224093
+ASMSYSTEM/racerp/datafile/undotbs1.262.1005224115
+ASMSYSTEM/racerp/datafile/undotbs2.264.1005224141
+ASMSYSTEM/racerp/datafile/users.265.1005224147
+ASMDATA/racerp/datafile/test.256.1005234027
+ASMDATA/racerp/datafile/henry.257.1010151449
+ASMDATA/racerp/datafile/henry.261.1010151935