linux上用rm命令误将oracle表空间文件删除

系统管理员 2022-11-21 14:54 332阅读 0赞

\[oracle@db1 ~\]$ sqlplus / as sysdba

1.查看数据文件：

SQL> select name from v$datafile;

2.查看数据文件状态  
SQL> select file\#,status,name from v$datafile where name like ‘%SMS%’;

3.确认操作系统上SMS数据文件已经不存在，u01文件夹都没有  
ll /oracle/

4.此时查看数据文件状态，状态没有异常  
select file\#,status,name from v$datafile where name like ‘%SMS%’;

5.手工将误删除的文件offline drop，成功  
SQL> alter database datafile ‘/oracle/u01/app/oracle/oradata/orcl/SMS.dbf’ offline drop;

Database altered.

6.此时，该文件的状态为“RECOVER”  
SQL> select file\#,status,name from v$datafile where name like ‘%SMS%’;

7.演示删除表空间，成功。  
SQL> alter database datafile ‘/oracle/u01/app/oracle/oradata/orcl/SMS.dbf’ offline drop;

8.最后确认，删除得很彻底。

SQL>select file\#,status,name from v$datafile where name like ‘%SMS%’;

SQL> select name from v$datafile;

--------------------

备注：  
先停库 shutdown immediate 然后备份文件 tar -zcvf database.tar.gz /sgb/orcl/base\_data01.dbf /sgb/orcl/base\_idx01.dbf /sgc/orcl/base\_data01.dbf 把excel里这些都列进来

--------------------

start mount完操作是可以的  
show parameter user\_dump\_dest

--------------------

SELECT a.tablespace\_name “表空间名”,  
total “表空间大小”,  
free “表空间剩余大小”,  
(total - free) “表空间使用大小”,  
total / (1024 \* 1024 \* 1024) “表空间大小(G)”,  
free / (1024 \* 1024 \* 1024) “表空间剩余大小(G)”,  
(total - free) / (1024 \* 1024 \* 1024) “表空间使用大小(G)”,  
round((total - free) / total, 4) \* 100 “使用率 %”  
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

select file\#,status,name from v$datafile where name like ‘%SMS%’;  
alter tablespace BASE\_DATA add datafile ‘/xxx/orcl/base\_data02.dbf’ size 30G;  
alter tablespace BASE\_DATA add datafile ‘/xxx/orcl/base\_data03.dbf’ size 30G;

run\{  
set newname for datafile 9 to ‘/sgg/orcl/base\_data31.dbf’ ;  
restore datafile 9;  
switch datafile all;  
recover datafile 9;  
\}