其他
案例|RAC 添加表空间误将数据文件放本地处理办法
作者 | JiekeXu
来源 | JiekeXu之路（ID: JiekeXu_IT）
转载请联系授权 | (微信ID：xxq1426321293)
大家好，我是 JiekeXu,很高兴又和大家见面了，今天分享一篇案例 RAC 添加表空间误将数据文件放本地处理办法。本文首发于微信公众号【JiekeXu之路】，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！
[/oracle/admin/Jieke/bdump]$tail -5000 alert_Jieke1.log | grep JiekeXualter tablespace JiekeXu ADD datafile '/dev/rora_8g_157' SIZE 8GORA-1537 signalled during: alter tablespace JiekeXu ADD datafile '/dev/rora_8g_157' SIZE 8G...alter tablespace JiekeXu ADD datafile '/dev/rora_4g_26' SIZE 4GORA-1537 signalled during: alter tablespace JiekeXu ADD datafile '/dev/rora_4g_26' SIZE 4G...alter tablespace JiekeXu ADD datafile '/dev/rora_4g_26' SIZE 4GORA-1537 signalled during: alter tablespace JiekeXu ADD datafile '/dev/rora_4g_26' SIZE 4G...alter tablespace JiekeXu ADD datafile '/dev/rora_30g_256' SIZE 30GORA-1119 signalled during: alter tablespace JiekeXu ADD datafile '/dev/rora_30g_256' SIZE 30G...alter tablespace JiekeXu ADD datafile '/dev/rora_8g_256' SIZE 8GORA-1119 signalled during: alter tablespace JiekeXu ADD datafile '/dev/rora_8g_256' SIZE 8G...alter tablespace JiekeXu ADD datafile '/dev/rora_8g_256' SIZE 8GORA-1119 signalled during: alter tablespace JiekeXu ADD datafile '/dev/rora_8g_256' SIZE 8G...alter tablespace JiekeXu ADD datafile 'ora_8g_158' SIZE 8GCompleted: alter tablespace JiekeXu ADD datafile 'ora_8g_158' SIZE 8GSQL> select tablespace_name,file_id,file_name from dba_data_files where file_name not like '/dev/rora%';
TABLESPACE_NAME FILE_ID FILE_NAME------------------------------ ---------- --------------------------------------------------------JiekeXu 239 /oracle/product/10.2.0/db_1/dbs/ora_8g_158SQL> !ls -lrt /dev/rora_*g_25*crw-rw-rw- 1 oracle dba 39,289 Nov 15 20:40 /dev/rora_16g_256crw-rw-rw- 1 oracle dba 39,290 Nov 15 20:45 /dev/rora_16g_257crw-rw-rw- 1 oracle dba 39,291 Nov 15 20:48 /dev/rora_32g_258crw-rw-rw- 1 oracle dba 39,292 Nov 15 20:50 /dev/rora_32g_259-- offline 此数据文件 SQL> alter database datafile '/oracle/product/10.2.0/db_1/dbs/ora_8g_158' offline;Database altered.SQL> select tablespace_name,file_id,file_name,online_status from dba_data_files where file_name not like '/dev/rora%';
TABLESPACE_NAME FILE_ID FILE_NAME ONLINE_------------------------------ ---------- ---------------------------------------------------------------------- -------JiekeXu 239 /oracle/product/10.2.0/db_1/dbs/ora_8g_158 RECOVERSQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options--rman copy [/home/oracle]$rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sun Nov 15 20:31:07 2020
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: Jieke (DBID=1237164921)
RMAN> copy datafile '/oracle/product/10.2.0/db_1/dbs/ora_8g_158' to '/dev/rora_16g_256';
Starting backup at 15-NOV-20using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=643 instance=Jieke1 devtype=DISKchannel ORA_DISK_1: starting datafile copyinput datafile fno=00239 name=/oracle/product/10.2.0/db_1/dbs/ora_8g_158output filename=/dev/rora_16g_256 tag=TAG20201115T203119 recid=186 stamp=1056573097channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25Finished backup at 15-NOV-20
Starting Control File and SPFILE Autobackup at 15-NOV-20piece handle=/oracle/product/10.2.0/db_1/dbs/c-1237164921-20201115-0b comment=NONEFinished Control File and SPFILE Autobackup at 15-NOV-20
RMAN> exit
Recovery Manager complete.--数据库里 rename 和 recover[/home/oracle]$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Nov 15 20:32:13 2020
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options
SQL> set line 345 SQL> col FILE_NAME for a99 SQL> select tablespace_name,file_id,file_name,online_status from dba_data_files where file_name not like '/dev/rora%';
TABLESPACE_NAME FILE_ID FILE_NAME ONLINE_------------------------------ ---------- --------------------------------------------------------------------------------------------------- -------JiekeXu 239 /oracle/product/10.2.0/db_1/dbs/ora_8g_158 RECOVER
SQL> select tablespace_name,file_id,file_name,online_status from dba_data_files where file_name like '/dev/rora_16g_256%';
no rows selected
--这里 recover 报错是由于需要先做 rename 才可以。SQL> recover datafile '/dev/rora_16g_256';ORA-01179: file /dev/rora_16g_256 does not exist
SQL> alter database rename file '/oracle/product/10.2.0/db_1/dbs/ora_8g_158' to '/dev/rora_16g_256';
Database altered.
SQL> select tablespace_name,file_id,file_name,online_status from dba_data_files where file_name like '/dev/rora_16g_256%';
TABLESPACE_NAME FILE_ID FILE_NAME ONLINE_------------------------------ ---------- --------------------------------------------------------------------------------------------------- -------JiekeXu 239 /dev/rora_16g_256 RECOVER
SQL> recover datafile '/dev/rora_16g_256';Media recovery complete.SQL> select tablespace_name,file_id,file_name,online_status from dba_data_files where file_name not like '/dev/rora%';
no rows selected
SQL> alter database datafile '/dev/rora_16g_256' online;
Database altered.
SQL> select tablespace_name,file_id,file_name,online_status from dba_data_files where file_name like '/dev/rora_16g_256%';
TABLESPACE_NAME FILE_ID FILE_NAME ONLINE_------------------------------ ---------- --------------------------------------------------------------------------------------------------- -------JiekeXu 239 /dev/rora_16g_256 ONLINE
三、添加表空间数据文件
迁移完成后另一节点便可以正常访问此数据文件中的数据了，最后要说的一点就是这个裸设备该怎么添加数据文件呢？一是查看 dba_data_files.file_name 确认以前的数据文件位置，或者查看 alert 日志查看添加成功的记录，两者均可：
-- alert 日志中搜索关键字 Jieke001 查看以前添加成功的日志即可确认。[/app/oracle/admin/Jieke/bdump]$tail -5000 alert_Jieke1.log | grep Jieke001alter tablespace Jieke001 add datafile '/dev/rora_30g_251' size 29gCompleted: alter tablespace Jieke001 add datafile '/dev/rora_30g_251' size 29galter tablespace Jieke001 add datafile '/dev/rora_30g_252' size 29gCompleted: alter tablespace Jieke001 add datafile '/dev/rora_30g_252' size 29galter tablespace Jieke001 add datafile '/dev/rora_30g_253' size 29gCompleted: alter tablespace Jieke001 add datafile '/dev/rora_30g_253' size 29galter tablespace Jieke001 add datafile '/dev/rora_30g_254' size 29gCompleted: alter tablespace Jieke001 add datafile '/dev/rora_30g_254' size 29galter tablespace Jieke001 add datafile '/dev/rora_30g_255' size 29gCompleted: alter tablespace Jieke001 add datafile '/dev/rora_30g_255' size 29g如下是一个成功添加的案例，可参考：
SQL> alter tablespace Jieke001 ADD datafile '/dev/rora_16g_257' SIZE 15G;
Tablespace altered.
SQL> alter tablespace Jieke001 ADD datafile '/dev/rora_32g_258' SIZE 30G;
Tablespace altered.
SQL> select tablespace_name,file_id,file_name,online_status from dba_data_files where file_name not like '/dev/rora%';
no rows selected
SQL> alter tablespace Jieke001 ADD datafile '/dev/rora_32g_259' SIZE 30G;
Tablespace altered.
SQL> col TABLESPACE_NAME for a28 SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g",round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%" FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name ) a,(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b WHERE a.tablespace_name=b.tablespace_name ORDER BY 4; SQL> 2 3 TABLESPACE_NAME Total g Free g USED%---------------------------- ---------- ---------- ----------JKXSYSTEM 24 24 .09SYSTEM 8 7 12.2USERS 12 10 19.08SYSAUX 4 1 78.25JiekeXu 59 12 79.05UNDOTBS2 12 2 86.3Jieke001 2657 356 86.6UNDOTBS1 23 3 87.08
8 rows selected.
SQL> select inst_id,status, count(*) from gv$session where type <> 'BACKGROUND' group by inst_id,status order by 3;
INST_ID STATUS COUNT(*)---------- -------- ---------- 2 ACTIVE 2 1 ACTIVE 4 1 INACTIVE 14 2 INACTIVE 16
SQL> set line 345 col instance_name for a15 col host_name for a30 select inst_id,INSTANCE_NAME,HOST_name,status,version,STARTUP_TIME from gv$instance; SQL> SQL> SQL> INST_ID INSTANCE_NAME HOST_NAME STATUS VERSION STARTUP_TIME---------- --------------- ------------------------------ ------------ ----------------- ------------ 1 Jieke1 JiekeXu1 OPEN 10.2.0.4.0 23-OCT-20 2 Jieke2 JiekeXu2 OPEN 10.2.0.4.0 15-NOV-20四、11g RAC 如何做？
11g 或者以上可以使用 asmcmd copy 本地文件到磁盘组，这是一个很不错的新功能，那么通常在 11g 及以上 RAC 中，由于忘记写盘号 "+" 导致出现问题。
SQL> alter tablespace idx_space add datafile 'DATA' size 20G autoextend off;
Tablespace altered.
--当另一个节点访问此数据时也会报错 SQL> select count(*) from table_XXXs;select count(*) from table_XXXs *ERROR at line 1:ORA-01157: cannot identify/lock data file 222 - see DBWR trace fileORA-01110: data file 222: '/app/product/11.2.0/db/dbs/DATA'这样便添加到本地文件系统了，还没有任何报错，下面我们来看看具体的操作步骤：
1、切换日志、offline 要迁移的数据文件
---多次切换日志SQL> alter system switch logfile;SQL> alter system switch logfile;SQL> alter system switch logfile;SQL> alter system checkpoint;SQL> alter system checkpoint;--查看要迁移的文件号select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='&tablespace_name';-- offline 要迁移的 datafileSQL> alter database datafile &file_ID offline; --》这里写数据文件 ID 或者全路径文件均可--查看该数据文件是否 offline：col NAME for a55select file#,name,status from gv$datafile where FILE#='&FILE_ID';2、拷贝本地文件到ASM
--切换到 grid 用户su - grid--asmcmd 复制本地数据文件到 asm diskgroupASMCMD> lsJIEKEDBS/ASMCMD> cd DATAASMCMD> cd datafileASMCMD> pwd+DATA/JIEKEDBS/datafile--ASMCMD> cp '/app/product/11.2.0/db/dbs/DATA' ./ASMCMD> cp '/app/product/11.2.0/db/dbs/DATA' '+DATA/JIEKEDBS/datafile/idx_space222.dbf'copying /app/product/11.2.0/db/dbs/DATA -> +DATA/JIEKEDBS/datafile/idx_space222.dbfASMCMD> 3、修改控制文件信息，online 数据文件
su - oraclesqlplus / as sysdba-- 重命名 datafileSQL> alter database rename file '/app/product/11.2.0/db/dbs/DATA' to '+DATA/JIEKEDBS/datafile/idx_space222.dbf';-- recover and online 数据文件SQL> alter database recover datafile &FILE_ID;-- online 数据文件SQL> alter database datafile &FILE_ID online;4、检查验证
-- 查看是否迁移成功，并查看迁移后数据文件的状态select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='&tablespace_name';
select file#,name,status from gv$datafile where FILE#='&FILE_ID';
多次切换日志，检查数据库告警日志有无报错，发现无异常，另一节点也可以正常访问了，说明问题已解决，本地文件系统的数据文件后期可清理了。
好咯，今天的分享就到这里了，如果本文对您有一丁点儿帮助，请多支持“在看”与转发，不求小费了哪怕是一个小小的赞，您的鼓励都将是我熬夜写文章最大的动力，让我有一直写下去的动力，最后一起加油,奥利给！
Oracle 12c 及以上版本补丁更新说明及下载方法（收藏版）
Oracle 11.2.0.4 RAC 最新补丁下载（11.2.0.4.200714）