如何將原來的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…