作者 | 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 8g
sql> 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_158
sql> !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 targetrecovery manager: release 10.2.0.4.0 - production on sun nov 15 20:31:07 2020米乐app官网下载 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-20starting 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-20rman> exitrecovery manager complete.--数据库里 rename 和 recover[/home/oracle]$sqlplus as sysdbasql*plus: release 10.2.0.4.0 - production on sun nov 15 20:32:13 2020米乐app官网下载 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 optionssql> set line 345sql> col file_name for a99sql> 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> 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 existsql> 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 recoversql> 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 selectedsql> 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 selectedsql> alter tablespace jieke001 add datafile '/dev/rora_32g_259' size 30g;tablespace altered.sql> col tablespace_name for a28select 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_filesgroup by tablespace_name) b where a.tablespace_name=b.tablespace_name order by 4;sql> 2 3tablespace_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.088 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 21 active 41 inactive 142 inactive 16sql> set line 345col instance_name for a15col host_name for a30select 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-202 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> pwddata/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';
多次切换日志，检查数据库告警日志有无报错，发现无异常，另一节点也可以正常访问了，说明问题已解决，本地文件系统的数据文件后期可清理了。
好咯，今天的分享就到这里了，如果本文对您有一丁点儿帮助，请多支持“在看”与转发，不求小费了哪怕是一个小小的赞，您的鼓励都将是我熬夜写文章最大的动力，让我有一直写下去的动力，最后一起加油,奥利给！
文章转载自，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。