做系統(tǒng)管理的都是這樣,難免會(huì)誤刪文件,某天要是把某個(gè)Oracle數(shù)據(jù)文件刪除,那該如何恢復(fù)呢?(這里數(shù)據(jù)庫是OPEN的,并且未關(guān)閉)
10年積累的成都做網(wǎng)站、網(wǎng)站制作經(jīng)驗(yàn),可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你,你也不認(rèn)識(shí)我。但先網(wǎng)站設(shè)計(jì)后付款的網(wǎng)站建設(shè)流程,更有睢陽免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
建立測試表空間
創(chuàng)建測試用戶
插入測試數(shù)據(jù)
刪除數(shù)據(jù)文件
恢復(fù)數(shù)據(jù)庫文件
建立測試表空間
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/member/system01.dbf /opt/oracle/oradata/member/sysaux01.dbf /opt/oracle/oradata/member/undotbs01.dbf /opt/oracle/oradata/member/users01.dbf SQL> create tablespace test datafile '/opt/oracle/oradata/member/test01.dbf' size 10m; Tablespace created. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/member/system01.dbf /opt/oracle/oradata/member/sysaux01.dbf /opt/oracle/oradata/member/undotbs01.dbf /opt/oracle/oradata/member/users01.dbf /opt/oracle/oradata/member/test01.dbf
2.創(chuàng)建測試賬戶
SQL> create user test identified by test default tablespace test; SQL> grant connect,resource to test;
3.插入測試數(shù)據(jù)
SQL> conn test/test SQL> create table t1(id int); SQL> insert into t1 values(1); SQL> select * from t1; ID ---------- 1
4.刪除數(shù)據(jù)文件
[oracle@db2 ~]$ rm -f /opt/oracle/oradata/member/test01.dbf [oracle@db2 ~]$ sqlplus test/test SQL> create table t2 as select * from t1; create table t2 as select * from t1 * ERROR at line 1: ORA-01116: error in opening database file 5 ORA-01110: data file 5: '/opt/oracle/oradata/member/test01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> select table_name,tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ T1 TEST
數(shù)據(jù)文件被刪除了,這怎么辦呢?這個(gè)時(shí)候千萬別重啟數(shù)據(jù)庫,否則數(shù)據(jù)就丟失了
5.數(shù)據(jù)文件恢復(fù)
[oracle@db2 ~]$ ps -ef |grep dbw0 oracle 3309 1 0 12:07 ? 00:00:00 ora_dbw0_member oracle 6217 5105 0 15:29 pts/0 00:00:00 grep dbw0 #找到ora_dbw0_SID的進(jìn)程號(hào)3309 [oracle@db2 ~]$ cd /proc/3309/fd #以上3309就是進(jìn)程號(hào),然后執(zhí)行l(wèi)s -al查看文件的鏈接
可以看到文件27就是被刪除的文件
[oracle@db2 fd]$ cp 27 /opt/oracle/oradata/member/test01.dbf
查看test表空間狀態(tài)
SQL> select name,status from v$datafile; NAME STATUS ---------------------------------------- ------- /opt/oracle/oradata/member/system01.dbf SYSTEM /opt/oracle/oradata/member/sysaux01.dbf ONLINE /opt/oracle/oradata/member/undotbs01.dbf ONLINE /opt/oracle/oradata/member/users01.dbf ONLINE /opt/oracle/oradata/member/test01.dbf ONLINE
下線test01表文件
SQL> alter database datafile '/opt/oracle/oradata/member/test01.dbf' offline; SQL> recover datafile '/opt/oracle/oradata/member/test01.dbf'; Media recovery complete. SQL> alter database datafile '/opt/oracle/oradata/member/test01.dbf' online; Database altered.
#以上就成功恢復(fù)了,若是出現(xiàn)
SQL> recover datafile '/opt/oracle/oradata/member/test01.dbf'; ORA-00283: recovery session canceled due to errors ORA-01110: data file 5: '/opt/oracle/oradata/member/test01.dbf' ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/opt/oracle/oradata/member/test01.dbf'
則有可能是/opt/oracle/oradata/member/test01.dbf文件的權(quán)限問題引起,在root用戶下
[root@db2 ~]# chown -R oracle.oinstall /opt/oracle/oradata/member/test01.dbf 再recover datafile '/opt/oracle/oradata/member/test01.dbf'
實(shí)驗(yàn)完成后,刪除測試用戶及測試表空間
SQL> drop user test cascade; SQL> drop tablespace test INCLUDING CONTENTS AND DATAFILES;
網(wǎng)頁題目:Oracle數(shù)據(jù)文件物理刪除后的恢復(fù)
標(biāo)題URL:http://muchs.cn/article12/ihgodc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供定制開發(fā)、外貿(mào)網(wǎng)站建設(shè)、移動(dòng)網(wǎng)站建設(shè)、搜索引擎優(yōu)化、營銷型網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)公司
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)