DBF刪除和改名的恢復操作
結論：關於普通表空間和system表空間被rm的情況，處理的方法基本是一樣的，都是需要restore datafile然後在recover datafile。有區別的就是如果是system dbf被刪除，那資料庫會崩。
小插曲：
關於dbf突然被誤刪除，如果是業務資料檔案的話，那還好,可以restore+recover的方式恢復，而不影響其他使用者訪問其他dbf裡的資料。但如果是system表空間的檔案被刪除的話，那基本db就崩了，但會等待一小會才會崩，而不是馬上。這是因為linux的刪除機制，(Linux 是通過 link 的數量來控制檔案刪除，只有當一個檔案不存在任何 link 的時候，這個檔案才會被刪除。每個檔案都有 2 個 link 計數器—— i_count 和 i_nlink。i_count 的意義是當前使用者的數量，i_nlink 的意義是介質連線的數量；或者可以理解為 i_count 是記憶體引用計數器，i_nlink 是硬碟引用計數器。再換句話說，當檔案被某個程式引用時，i_count 就會增加；當建立檔案的硬連線的時候，i_nlink 就會增加。對於 rm 而言，就是減少 i_nlink。這裡就出現一個問題，如果一個檔案正在被某個程式呼叫，而使用者卻執行 rm 操作把檔案刪除了，會出現什麼結果呢？當使用者執行 rm 操作後，ls 或者其他檔案管理命令不再能夠找到這個檔案，但是程式卻依然在繼續正常執行，依然能夠從檔案中正確的讀取內容。這是因為，`rm` 操作只是將 i_nlink 置為 0 了；由於檔案被程式引用的緣故，i_count 不為 0，所以系統沒有真正刪除這個檔案。i_nlink 是檔案刪除的充分條件，而 i_count 才是檔案刪除的必要條件。)
所以我們可以得出一個結論，就是普通dbf檔案被刪除並不會影響其他dbf的讀取以及業務使用，但如果是system的話，就肯定會影響，而且會導致資料庫崩潰。
業務dbf被改名
SQL> create user mao identified by mao account unlock default tablespace users;
User created.
SQL> grant connect,resource to mao;
Grant succeeded.
SQL> conn mao/mao
Connected.
SQL> create table t_mao (id int);
Table created.
SQL> insert into t_mao values(111111111111);
SQL> /
/
。。。。。
131072 rows created.
SQL>
262144 rows created.
SQL> commit;
Commit complete.
SQL> select table_name,tablespace_name from user_tables where table_name='T_MAO';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_MAO USERS
SQL> conn / as sysdba
Connected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dong/system01.dbf
/u01/app/oracle/oradata/dong/sysaux01.dbf
/u01/app/oracle/oradata/dong/undotbs01.dbf
/u01/app/oracle/oradata/dong/users01.dbf
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
將dbf改名，那一會就不需要restore了，直接recover即可。
SQL> !mv /u01/app/oracle/oradata/dong/users01.dbf /u01/app/oracle/oradata/dong/users01.dbf.bak
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dong/system01.dbf
/u01/app/oracle/oradata/dong/sysaux01.dbf
/u01/app/oracle/oradata/dong/undotbs01.dbf
/u01/app/oracle/oradata/dong/users01.dbf
SQL> !ls -l /u01/app/oracle/oradata/dong/users01.dbf
可以發現此檔案已經沒有了。
SQL> select table_name,tablespace_name from dba_tables where table_name='T_MAO';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_MAO USERS
SQL> conn mao/mao
Connected.
SQL> select count(*) from t_mao;
COUNT(*)
----------
524288
SQL> conn / as sysdba
Connected.
SQL> grant dba to mao;
Grant succeeded.
SQL> conn mao/mao
Connected.
SQL> set autotrace traceonly
SQL> select * from t_mao where rownum=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 4144912178
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T_MAO | 487K| 6184K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets---都是在記憶體中讀取，所以dbf刪了，這裡也可以讀到資料。
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> update t_mao set id=22222222222222222222222244;
update t_mao set id=22222222222222222222222244
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
--但對於修改就肯定不行了。
SQL> SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 10 52428800 512 1 YES INACTIVE 1050012 16-NOV-13 1059303 16-NOV-13
2 1 11 52428800 512 1 YES INACTIVE 1059303 16-NOV-13 1081764 18-NOV-13
3 1 12 52428800 512 1 NO CURRENT 1081764 18-NOV-13 2.8147E+14
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 13 52428800 512 1 NO CURRENT 1083148 18-NOV-13 2.8147E+14
2 1 11 52428800 512 1 YES INACTIVE 1059303 16-NOV-13 1081764 18-NOV-13
3 1 12 52428800 512 1 YES ACTIVE 1081764 18-NOV-13 1083148 18-NOV-13
SQL> alter table t_tteqteq move to users;
alter table t_tteqteq move to users
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
SQL> create table ttt(id int) tablespace users;
create table ttt(id int) tablespace users
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
告警日誌：
Thread 1 advanced to log sequence 13 (LGWR switch)
Current log# 1 seq# 13 mem# 0: /u01/app/oracle/oradata/dong/redo01.log
Mon Nov 18 14:56:12 2013
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_10_25/o1_mf_1_4_96mxrw7k_.arc
Archived Log entry 187 added for thread 1 sequence 12 ID 0x7bd66ee8 dest 1:
Mon Nov 18 14:58:16 2013
Errors in file /u01/app/oracle/diag/rdbms/dong/dong/trace/dong_m000_3812.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
此時檢視dbf的狀態為recover：
SQL> select name,status,bytes,blocks,block1_offset from v$datafile;
NAME STATUS BYTES BLOCKS BLOCK1_OFFSET
-------------------------------------------------- ------- ---------- ---------- -------------
/u01/app/oracle/oradata/dong/system01.dbf SYSTEM 817889280 99840 8192
/u01/app/oracle/oradata/dong/sysaux01.dbf ONLINE 482344960 58880 8192
/u01/app/oracle/oradata/dong/undotbs01.dbf ONLINE 214958080 26240 8192
/u01/app/oracle/oradata/dong/users01.dbf RECOVER 0 0 4294967295
將dbf給移動回來。
SQL> !mv /u01/app/oracle/oradata/dong/users01.dbf.bak /u01/app/oracle/oradata/dong/users01.dbf
SQL> select name,status,bytes,blocks,block1_offset from v$datafile;
NAME STATUS BYTES BLOCKS BLOCK1_OFFSET
-------------------------------------------------- ------- ---------- ---------- -------------
/u01/app/oracle/oradata/dong/system01.dbf SYSTEM 817889280 99840 8192
/u01/app/oracle/oradata/dong/sysaux01.dbf ONLINE 482344960 58880 8192
/u01/app/oracle/oradata/dong/undotbs01.dbf ONLINE 214958080 26240 8192
/u01/app/oracle/oradata/dong/users01.dbf RECOVER 10485760 1280 8192
SQL> update mao.t_mao set id=22222222222222222222222244;
update mao.t_mao set id=22222222222222222222222244
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
SQL> select file#,name,status,bytes,blocks,block1_offset from v$datafile;
FILE# NAME STATUS BYTES BLOCKS BLOCK1_OFFSET
---------- -------------------------------------------------- ------- ---------- ---------- -------------
1 /u01/app/oracle/oradata/dong/system01.dbf SYSTEM 817889280 99840 8192
2 /u01/app/oracle/oradata/dong/sysaux01.dbf ONLINE 482344960 58880 8192
3 /u01/app/oracle/oradata/dong/undotbs01.dbf ONLINE 214958080 26240 8192
4 /u01/app/oracle/oradata/dong/users01.dbf RECOVER 10485760 1280 8192
直接恢復dbf檔案：
SQL> recover datafile 4;
Media recovery complete.
SQL> select file#,name,status,bytes,blocks,block1_offset from v$datafile;
FILE# NAME STATUS BYTES BLOCKS BLOCK1_OFFSET
---------- -------------------------------------------------- ------- ---------- ---------- -------------
1 /u01/app/oracle/oradata/dong/system01.dbf SYSTEM 817889280 99840 8192
2 /u01/app/oracle/oradata/dong/sysaux01.dbf ONLINE 482344960 58880 8192
3 /u01/app/oracle/oradata/dong/undotbs01.dbf ONLINE 214958080 26240 8192
4 /u01/app/oracle/oradata/dong/users01.dbf OFFLINE 10485760 1280 8192
SQL> update mao.t_mao set id=22222222222222222222222244;
update mao.t_mao set id=22222222222222222222222244
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
SQL> alter database datafile 4 online;
Database altered.
SQL> select file#,name,status,bytes,blocks,block1_offset from v$datafile;
FILE# NAME STATUS BYTES BLOCKS BLOCK1_OFFSET
---------- -------------------------------------------------- ------- ---------- ---------- -------------
1 /u01/app/oracle/oradata/dong/system01.dbf SYSTEM 817889280 99840 8192
2 /u01/app/oracle/oradata/dong/sysaux01.dbf ONLINE 482344960 58880 8192
3 /u01/app/oracle/oradata/dong/undotbs01.dbf ONLINE 214958080 26240 8192
4 /u01/app/oracle/oradata/dong/users01.dbf ONLINE 10485760 1280 8192
SQL> update mao.t_mao set id=22222222222222222222222244;
524288 rows updated.
SQL> commit;
Commit complete.
完畢。以上為普通業務dbf刪除恢復的情況。
業務dbf被刪除
[oracle@baobao ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 17:05:32 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dong/system01.dbf
/u01/app/oracle/oradata/dong/sysaux01.dbf
/u01/app/oracle/oradata/dong/undotbs01.dbf
/u01/app/oracle/oradata/dong/users01.dbf
SQL> !mv /u01/app/oracle/oradata/dong/system01.dbf.bak /u01/app/oracle/oradata/dong/system01.dbf
SQL> !ls -l /u01/app/oracle/oradata/dong/system01.dbf
-rw-r-----. 1 oracle oinstall 828383232 11月 19 16:57 /u01/app/oracle/oradata/dong/system01.dbf
SQL> recover datafile 1;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dong/system01.dbf
/u01/app/oracle/oradata/dong/sysaux01.dbf
/u01/app/oracle/oradata/dong/undotbs01.dbf
/u01/app/oracle/oradata/dong/users01.dbf
SQL> !rm -rf /u01/app/oracle/oradata/dong/users01.dbf
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@baobao ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 19 17:18:48 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DONG (DBID=2075447482)
RMAN> restore datafile 4;
Starting restore at 19-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dong/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/fulldb1119
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/19/2013 17:18:59
ORA-19870: error while restoring backup piece /home/oracle/fulldb1119
ORA-19573: cannot obtain exclusive enqueue for datafile 4
RMAN> exit
Recovery Manager complete.
[oracle@baobao ~]$ ps -ef |grep smon
oracle 5071 1 0 16:59 ? 00:00:00 ora_smon_dong
oracle 5343 2611 0 17:19 pts/0 00:00:00 grep smon
[oracle@baobao ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 17:19:22 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> !ps -ef |grep smonb
oracle 5348 5345 0 17:19 pts/1 00:00:00 /bin/bash -c ps -ef |grep smonb
oracle 5350 5348 0 17:19 pts/1 00:00:00 grep smonb
SQL> !ps -ef |grep smon
oracle 5071 1 0 16:59 ? 00:00:00 ora_smon_dong
oracle 5351 5345 0 17:19 pts/1 00:00:00 /bin/bash -c ps -ef |grep smon
oracle 5353 5351 0 17:19 pts/1 00:00:00 grep smon
SQL> shutdown immediate
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2092498944 bytes
Fixed Size 1337604 bytes
Variable Size 251660028 bytes
Database Buffers 1828716544 bytes
Redo Buffers 10784768 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@baobao ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 19 17:20:09 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DONG (DBID=2075447482, not open)
RMAN> restore datafile 4;
Starting restore at 19-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dong/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/fulldb1119
channel ORA_DISK_1: piece handle=/home/oracle/fulldb1119 tag=TAG20131119T164123
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 19-NOV-13
RMAN> recover datafile 4;
Starting recover at 19-NOV-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_1_98p96htn_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_2_98p9lgy6_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_3_98p9pzhq_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_4_98pbhy62_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_1_98p96htn_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_2_98p9lgy6_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-NOV-13
RMAN> alter database open;
database opened
以下為system dbf刪除的情況：
其實和普通dbf的恢復區別就是system dbf刪除之後，資料庫會崩，所以我們需要啟動到mount狀態，然後restore datafile1 然後recover datafile1，然後alter database open即可。
[oracle@baobao ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:42:44 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t19 as select * from dba_objects;
Table created.
SQL> alter system swith logfile;
alter system swith logfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
SQL> alter system switch logfile;
System altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dong/system01.dbf
/u01/app/oracle/oradata/dong/sysaux01.dbf
/u01/app/oracle/oradata/dong/undotbs01.dbf
/u01/app/oracle/oradata/dong/users01.dbf
SQL> !rm -rf /u01/app/oracle/oradata/dong/system01.dbf
SQL> !ls -l /u01/app/oracle/oradata/dong/system01.dbf
ls: 無法訪問/u01/app/oracle/oradata/dong/system01.dbf: 沒有那個檔案或目錄
SQL> create table t20 as select * from dba_objects;
create table t20 as select * from dba_objects
*
ERROR at line 1:
ORA-01565: error in identifying file
'/u01/app/oracle/oradata/dong/system01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> restore datafile 1;
SP2-0734: unknown command beginning "restore da..." - rest of line ignored.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@baobao ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 19 16:46:45 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DONG (DBID=2075447482)
RMAN> restore datafile 1;
Starting restore at 19-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dong/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/fulldb1119
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/19/2013 16:46:54
ORA-19870: error while restoring backup piece /home/oracle/fulldb1119
ORA-19573: cannot obtain exclusive enqueue for datafile 1
RMAN> shutdown immediate
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 11/19/2013 16:47:11
ORA-03113: end-of-file on communication channel
RMAN> exit
Recovery Manager complete.
[oracle@baobao ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:47:37 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
SQL> exit
Disconnected
[oracle@baobao ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:47:44 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2092498944 bytes
Fixed Size 1337604 bytes
Variable Size 251660028 bytes
Database Buffers 1828716544 bytes
Redo Buffers 10784768 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@baobao ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 19 16:48:45 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DONG (DBID=2075447482, not open)
RMAN> restore datafile 1
2> ;
Starting restore at 19-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dong/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/fulldb1119
channel ORA_DISK_1: piece handle=/home/oracle/fulldb1119 tag=TAG20131119T164123
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 19-NOV-13
RMAN> exit
Recovery Manager complete.
[oracle@baobao ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:50:07 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover datafile 1;
Media recovery complete.
SQL> alter database open;
Database altered.
以上是將system dbf刪除的情況。
system dbf被改名：
[oracle@baobao ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:50:07 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover datafile 1;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>
SQL>
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dong/system01.dbf
/u01/app/oracle/oradata/dong/sysaux01.dbf
/u01/app/oracle/oradata/dong/undotbs01.dbf
/u01/app/oracle/oradata/dong/users01.dbf
SQL> !mv /u01/app/oracle/oradata/dong/system01.dbf /u01/app/oracle/oradata/dong/system01.dbf.bak
SQL> create table t20 as select * from dba_objects;
create table t20 as select * from dba_objects
*
ERROR at line 1:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/dong/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> !ps -ef |grep smon
oracle 4769 1 0 16:48 ? 00:00:00 ora_smon_dong
oracle 4966 4913 3 16:52 pts/1 00:00:00 /bin/bash -c ps -ef |grep smon
oracle 4968 4966 1 16:52 pts/1 00:00:00 grep smon
SQL> !ps -ef |grep smon
oracle 4769 1 0 16:48 ? 00:00:00 ora_smon_dong
oracle 4969 4913 0 16:52 pts/1 00:00:00 /bin/bash -c ps -ef |grep smon
oracle 4971 4969 0 16:52 pts/1 00:00:00 grep smon
SQL> !ps -ef |grep smon
oracle 4769 1 0 16:48 ? 00:00:00 ora_smon_dong
oracle 4972 4913 1 16:52 pts/1 00:00:00 /bin/bash -c ps -ef |grep smon
oracle 4974 4972 1 16:52 pts/1 00:00:00 grep smon
SQL> alter system switch logfile;
System altered.
SQL> !ps -ef |grep smon
oracle 4769 1 0 16:48 ? 00:00:00 ora_smon_dong
oracle 4975 4913 2 16:52 pts/1 00:00:00 /bin/bash -c ps -ef |grep smon
oracle 4977 4975 1 16:52 pts/1 00:00:00 grep smon
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@baobao ~]$ ps -ef |grep smn
oracle 4980 2611 0 16:53 pts/0 00:00:00 grep smn
[oracle@baobao ~]$ ps -ef |grep smon
oracle 4769 1 0 16:48 ? 00:00:00 ora_smon_dong
oracle 4982 2611 0 16:53 pts/0 00:00:00 grep smon
[oracle@baobao ~]$ ls /u01/app/oracle/oradata/dong/system01.dbf
ls: 無法訪問/u01/app/oracle/oradata/dong/system01.dbf: 沒有那個檔案或目錄
[oracle@baobao ~]$ ps -ef |grep smon
oracle 4769 1 0 16:48 ? 00:00:00 ora_smon_dong
oracle 4986 2611 0 16:53 pts/0 00:00:00 grep smon
[oracle@baobao ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:53:50 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01075: you are currently logged on
[oracle@baobao ~]$ ps -ef |grep smon
oracle 5018 2611 0 16:57 pts/0 00:00:00 grep smon
[oracle@baobao ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:59:37 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2092498944 bytes
Fixed Size 1337604 bytes
Variable Size 251660028 bytes
Database Buffers 1828716544 bytes
Redo Buffers 10784768 bytes
Database mounted.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dong/system01.dbf
/u01/app/oracle/oradata/dong/sysaux01.dbf
/u01/app/oracle/oradata/dong/undotbs01.dbf
/u01/app/oracle/oradata/dong/users01.dbf
SQL> !ls -l /u01/app/oracle/oradata/dong/system01.dbf*
-rw-r-----. 1 oracle oinstall 828383232 11月 19 16:57 /u01/app/oracle/oradata/dong/system01.dbf.bak
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: '/u01/app/oracle/oradata/dong/system01.dbf'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/dong/system01.dbf'
SQL> !ps -ef |grep smon
oracle 5071 1 0 16:59 ? 00:00:00 ora_smon_dong
oracle 5223 5023 0 17:05 pts/1 00:00:00 /bin/bash -c ps -ef |grep smon
oracle 5225 5223 0 17:05 pts/1 00:00:00 grep smon
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@baobao ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 19 17:05:17 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DONG (DBID=2075447482, not open)
RMAN> recover datafile 1;
Starting recover at 19-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/19/2013 17:05:24
RMAN-06094: datafile 1 must be restored
RMAN> exit
Recovery Manager complete.
[oracle@baobao ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 17:05:32 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dong/system01.dbf
/u01/app/oracle/oradata/dong/sysaux01.dbf
/u01/app/oracle/oradata/dong/undotbs01.dbf
/u01/app/oracle/oradata/dong/users01.dbf
SQL> !mv /u01/app/oracle/oradata/dong/system01.dbf.bak /u01/app/oracle/oradata/dong/system01.dbf
SQL> !ls -l /u01/app/oracle/oradata/dong/system01.dbf
-rw-r-----. 1 oracle oinstall 828383232 11月 19 16:57 /u01/app/oracle/oradata/dong/system01.dbf
SQL> recover datafile 1;
Media recovery complete.
SQL> alter database open;
Database altered.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24500180/viewspace-777019/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- RM 刪除資料檔案恢復操作
- 恢復刪除的檔案
- 刪除檔案的恢復
- Git恢復刪除的檔案Git
- 恢復 Git 被刪除的分支Git
- Git恢復被刪除的分支Git
- 【儲存資料恢復案例】Netapp誤操作刪除lun的資料恢復資料恢復APP
- 行動硬碟刪除的檔案能恢復嗎，怎樣恢復刪除的檔案硬碟
- 【北亞資料恢復】誤刪除oracle表和誤刪除oracle表資料的資料恢復方法資料恢復Oracle
- 行動硬碟刪除的檔案能恢復嗎，怎麼恢復硬碟刪除的檔案硬碟
- 伺服器資料恢復—NTFS誤操作刪除/格式化的資料恢復案例伺服器資料恢復
- linux中誤刪除oracle資料檔案的恢復操作LinuxOracle
- 怎樣恢復回收站已刪除檔案，檔案刪除恢復教程
- 恢復被刪除的Word選單
- FLASHBACK DATABASE可以恢復刪除的TABLESPACEDatabase
- Oracle恢復誤操作刪除掉的表Oracle
- oracle使用小記、刪除恢復Oracle
- oracle恢復誤刪除資料Oracle
- mysql資料庫誤刪除後的資料恢復操作說明MySql資料庫資料恢復
- MySQL 資料庫誤刪除後的資料恢復操作說明MySql資料庫資料恢復
- linux中誤刪除oracle資料檔案的恢復操作（轉）LinuxOracle
- 2 Day DBA-管理方案物件-執行備份和恢復-使用Oracle閃回刪除功能恢復被刪除的表物件Oracle
- MySQL 如何重建/恢復刪除的 sys SchemaMySql
- sd卡刪除的檔案如何恢復SD卡
- Shift + Delete刪除的檔案如何恢復？delete
- PostgreSQL中刪除的資料能否恢復SQL
- DB2 恢復誤刪除的表DB2
- 使用lsof恢復誤刪除的檔案
- 儲存過程誤刪除的恢復儲存過程
- 一個恢復刪除檔案的工具
- eclipse中恢復刪除的檔案Eclipse
- oracle redo 日誌刪除後的恢復Oracle Redo
- 恢復被刪除的Word選單(轉)
- 【伺服器資料恢復】XenServer虛擬機器被誤操作刪除的資料恢復案例伺服器資料恢復Server虛擬機
- Oracle閃回刪除恢復誤刪資料Oracle
- EMC UNITY 400儲存卷刪除資料恢復操作過程Unity資料恢復
- 檔案描述符恢復rm掉的dbf
- Linux下誤刪除後的恢復操作（ext3/ext4）Linux