如何将原来的Oracle表空间重新利用起来，重新加入原来的表空间
背景：
一套Oracle RAC，尝试将另外一套Oracle RAC 的数据通过RMAN备份过来恢复。最后几经辛苦，RMAN恢复过来。实例也发现起来。但无意中发现数据库实例并没有加载原有的表空间，这里是ZYMH用户的表空间，那这个用户下面的数据都没有恢复。需要想办法将这个表空间重新加载起来，可以访问，否则这个RAC 的RMAN 备份恢复没什么意义。
下面几经尝试，终于成功，将操作过程，以及命令记录如下：
ASMCMD> ls
EXAMPLE.271.1095019367
SYSAUX.265.1096310321
SYSAUX.273.1095019365
SYSTEM.274.1095019361
SYSTEM.277.1096310417
UNDOTBS1.272.1095019367
UNDOTBS1.276.1096310323
UNDOTBS2.270.1095019367
UNDOTBS2.275.1096310323
USERS.269.1095019369
USERS.279.1096310419
ZYMH.278.1096310419
这里的ZYMH.278.1096310419就是原有的表空间文件。执行
SQL> select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
发现这个ZYMH.278.1096310419 并没有加载。尝试重建控制文件。但遇到报错：报错ｗｒｏｎｇ ｄａｔａｂａｓｅ ｉｄ ．
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01159: file is not from same database as previous files – wrong database id
ORA-01110: data file 4: ‘+DATA/devdb/datafile/USERS.279.1096310419’
无办法下，就想着以增加数据文件的形式，重新将这个表空间文件加入到ORACLE，重新成为ZYMH用户的表空间。
SQL> ALTER TABLESPACE zymh ADD DATAFILE ‘+DATA/devdb/datafile/ZYMH.278.1096310419’ REUSE AUTOEXTEND on;
ALTER TABLESPACE zymh ADD DATAFILE ‘+DATA/devdb/datafile/ZYMH.278.1096310419’ REUSE AUTOEXTEND on
*
ERROR at line 1:
ORA-00959: tablespace ‘ZYMH’ does not exist
发现直接增加，不成功。有报错。
就新建一个ZYMH表空间。然后，再增加ZYMH.278.1096310419表空间文件。
SQL> create tablespace zymh datafile ‘+DATA’ size 280m autoextend on next 100m maxsize 3000m extent management local segment space management auto;
Tablespace created.
SQL> ALTER TABLESPACE zymh ADD DATAFILE ‘+DATA/devdb/datafile/ZYMH.278.1096310419’ REUSE AUTOEXTEND on;
ALTER TABLESPACE zymh ADD DATAFILE ‘+DATA/devdb/datafile/ZYMH.278.1096310419’ REUSE AUTOEXTEND on
*
ERROR at line 1:
ORA-01276: Cannot add file +DATA/devdb/datafile/ZYMH.278.1096310419. File has
an Oracle Managed Files file name.
提示这个文件是ZYMH.278.1096310419是OMF文件. 就考虑将这个 ZYMH.278.1096310419 改成普通表空间文件，再加入到ZYMH表空间
ASMCMD> cp ZYMH.278.1096310419 zymh.dbf
copying +DATA/DEVDB/DATAFILE/ZYMH.278.1096310419 -> +DATA/DEVDB/DATAFILE/zymh.dbf
ASMCMD> ls
EXAMPLE.271.1095019367
SYSAUX.265.1096310321
SYSAUX.273.1095019365
SYSTEM.274.1095019361
SYSTEM.277.1096310417
UNDOTBS1.272.1095019367
UNDOTBS1.276.1096310323
UNDOTBS2.270.1095019367
UNDOTBS2.275.1096310323
USERS.269.1095019369
USERS.279.1096310419
ZYMH.278.1096310419
ZYMH.285.1096578293
zymh.dbf
然后将这个zymh.dbf表空间文件加入zymh用户表空间
SQL> ALTER TABLESPACE zymh ADD DATAFILE ‘+DATA/devdb/datafile/zymh.dbf’ AUTOEXTEND on;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name=’ZYMH’;
FILE_NAME
——————————————————————————–
+DATA/devdb/datafile/zymh.285.1096578293
+DATA/devdb/datafile/zymh.dbf
查询可以看到，已成功加入，心想能不能将这个zymh.dbf改成OMF文件格式呢。再继续下面尝试：
先将zymh表空间 下线
SQL> alter tablespace zymh offline;
Tablespace altered.
现在借助RMAN来复制文件到ASM
[oracle@rac01 oracle]$ rman target /
Recovery Manager: Release 11.2.0.1.0 – Production on Sun Feb 13 21:51:21 2022
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEVDB (DBID=934441690)
RMAN> copy datafile ‘+DATA/DEVDB/DATAFILE/zymh.dbf’ to ‘+DATA’;
Starting backup at 2022-02-13 21:52:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 instance=devdb1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DATA/devdb/datafile/zymh.dbf
output file name=+DATA/devdb/datafile/zymh.278.1096581141 tag=TAG20220213T215220 RECID=4 STAMP=1096581165
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 2022-02-13 21:52:46
Starting Control File and SPFILE Autobackup at 2022-02-13 21:52:46
piece handle=+FRA/devdb/autobackup/2022_02_13/s_1096581166.292.1096593617 comment=NONE
Finished Control File and SPFILE Autobackup at 2022-02-13 21:52:53
RMAN>
留意到output file文件名为：+DATA/devdb/datafile/zymh.278.1096581141
然后将zymh.dbf改名成zymh.278.1096581141 如下所示：
SQL> alter database rename file ‘+DATA/DEVDB/DATAFILE/zymh.dbf’ to ‘+DATA/DEVDB/DATAFILE/zymh.278.1096581141’;
改完后，将ZYMH表空间上线：
SQL> alter tablespace zymh online;
Tablespace altered.
确认修改成功：
SQL> select file_name from dba_data_files where tablespace_name=’ZYMH’;
FILE_NAME
——————————————————————————–
+DATA/devdb/datafile/zymh.285.1096578293
+DATA/devdb/datafile/zymh.278.1096581141
SQL>
最后在ASM里，将无用的zymh.dbf 和ZYMH.278.1096310419 ｒｍ掉。
ASMCMD> rm zymh.dbf
ASMCMD> rm ZYMH.278.1096310419
ASMCMD> ls
EXAMPLE.271.1095019367
SYSAUX.265.1096310321
SYSAUX.273.1095019365
SYSTEM.274.1095019361
SYSTEM.277.1096310417
UNDOTBS1.272.1095019367
UNDOTBS1.276.1096310323
UNDOTBS2.270.1095019367
UNDOTBS2.275.1096310323
USERS.269.1095019369
USERS.279.1096310419
ZYMH.278.1096581141
ZYMH.285.1096578293
以下文章点击率最高
Loading…