[20170213]刪除資料沒有備份可以恢復嗎.txt
[20170213]刪除資料沒有備份可以恢復嗎.txt
--別人問的問題,實際上只要當時建立資料檔案時歸檔還在是可以恢復的.
--還是透過測試來說明問題:
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> alter system archive log current ;
System altered.
CREATE TABLESPACE TEA DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 100M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
SCOTT@book> create table tt tablespace tea as select * from emp ;
Table created.
SCOTT@book> commit ;
Commit complete.
2.刪除檔案.
$ rm /mnt/ramdisk/book/tea01.dbf
/bin/rm: remove regular file `/mnt/ramdisk/book/tea01.dbf'? y
RMAN> restore datafile 6;
Starting restore at 2017-02-13 11:05:42
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=80 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=90 device type=DISK
creating datafile file number=6 name=/mnt/ramdisk/book/tea01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/13/2017 11:05:43
ORA-01182: cannot create database file 6 - file is in use or recovery
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
--//可以發現無法restore,因為根本沒有備份.出現這種情況,在做上面或者寫檢查點時,資料庫直接crash.
Mon Feb 13 11:05:31 2017
Checker run found 1 new persistent data failures
Mon Feb 13 11:06:25 2017
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_smon_12820.trc:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Mon Feb 13 11:06:28 2017
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ckpt_12818.trc:
ORA-63999: data file suffered media failure
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ckpt_12818.trc:
ORA-63999: data file suffered media failure
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
CKPT (ospid: 12818): terminating the instance due to error 63999
Mon Feb 13 11:06:29 2017
System state dump requested by (instance=1, osid=12818 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_diag_12802_20170213110629.trc
Dumping diagnostic data in directory=[cdmp_20170213110629], requested by (instance=1, osid=12818 (CKPT)), summary=[abnormal instance termination].
Instance terminated by CKPT, pid = 12818
SYS@book> startup
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
SYS@book> select open_mode from v$database ;
OPEN_MODE
--------------------
MOUNTED
SYS@book> alter database create datafile '/mnt/ramdisk/book/tea01.dbf';
Database altered.
$ ls -l tea01.dbf
-rw-r----- 1 oracle oinstall 104865792 2017-02-13 11:08:49 tea01.dbf
RMAN> recover datafile 6;
Starting recover at 2017-02-13 11:09:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=12 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=24 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-02-13 11:09:32
3.開啟到open看看:
SYS@book> alter database open ;
Database altered.
SYS@book> select * from scott.tt where rownum=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
--//補充一點,出現這種情況正常應該先offline,不然很容易在寫檢查點時crash.
SYS@book> alter database datafile 6 offline ;
Database altered.
RMAN> restore datafile 6 ;
Starting restore at 2017-02-13 11:18:37
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=35 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=46 device type=DISK
creating datafile file number=6 name=/mnt/ramdisk/book/tea01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 2017-02-13 11:18:39
RMAN> sql "alter database create datafile ''/mnt/ramdisk/book/tea01.dbf''";
sql statement: alter database create datafile ''/mnt/ramdisk/book/tea01.dbf''
RMAN> recover datafile 6;
Starting recover at 2017-02-13 11:19:27
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
archived log for thread 1 with sequence 458 is already on disk as file /u01/app/oracle/archivelog/book/1_458_896605872.dbf
archived log for thread 1 with sequence 459 is already on disk as file /u01/app/oracle/archivelog/book/1_459_896605872.dbf
archived log for thread 1 with sequence 460 is already on disk as file /u01/app/oracle/archivelog/book/1_460_896605872.dbf
archived log for thread 1 with sequence 461 is already on disk as file /u01/app/oracle/archivelog/book/1_461_896605872.dbf
archived log for thread 1 with sequence 462 is already on disk as file /u01/app/oracle/archivelog/book/1_462_896605872.dbf
archived log for thread 1 with sequence 463 is already on disk as file /u01/app/oracle/archivelog/book/1_463_896605872.dbf
archived log for thread 1 with sequence 464 is already on disk as file /u01/app/oracle/archivelog/book/1_464_896605872.dbf
archived log for thread 1 with sequence 465 is already on disk as file /u01/app/oracle/archivelog/book/1_465_896605872.dbf
archived log for thread 1 with sequence 466 is already on disk as file /u01/app/oracle/archivelog/book/1_466_896605872.dbf
archived log for thread 1 with sequence 467 is already on disk as file /u01/app/oracle/archivelog/book/1_467_896605872.dbf
archived log file name=/u01/app/oracle/archivelog/book/1_458_896605872.dbf thread=1 sequence=458
archived log file name=/u01/app/oracle/archivelog/book/1_459_896605872.dbf thread=1 sequence=459
archived log file name=/u01/app/oracle/archivelog/book/1_460_896605872.dbf thread=1 sequence=460
archived log file name=/u01/app/oracle/archivelog/book/1_461_896605872.dbf thread=1 sequence=461
archived log file name=/u01/app/oracle/archivelog/book/1_462_896605872.dbf thread=1 sequence=462
archived log file name=/u01/app/oracle/archivelog/book/1_463_896605872.dbf thread=1 sequence=463
archived log file name=/u01/app/oracle/archivelog/book/1_464_896605872.dbf thread=1 sequence=464
archived log file name=/u01/app/oracle/archivelog/book/1_465_896605872.dbf thread=1 sequence=465
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-02-13 11:19:28
--//前面的測試沒有刪除前沒有做日誌切換.
RMAN> sql "alter database datafile 6 online";
sql statement: alter database datafile 6 online
SYS@book> select * from scott.tt where rownum=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2133440/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20171225]沒有備份資料檔案的恢復.txt
- 沒有備份的資料檔案恢復（五）
- 歸檔模式，恢復沒有備份的資料檔案模式
- 無備份恢復(歸檔模式)已刪除資料檔案模式
- 【備份恢復】從備份恢復資料庫資料庫
- datafile被刪除後，可以復以前資料嗎？
- 【備份恢復】不使用rman工具就能恢復被rm刪除的資料檔案案例
- Rman Crosscheck刪除失效歸檔-備份恢復ROS
- 利用rman全備恢復刪除的資料庫資料庫
- 【備份恢復】資料恢復指導資料恢復
- 備份與恢復：polardb資料庫備份與恢復資料庫
- 【備份恢復】Oracle 資料備份與恢復微實踐Oracle
- oracle恢復誤刪除資料Oracle
- 【備份恢復】noarchive模式下使用增量備份恢復資料庫Hive模式資料庫
- 【備份恢復】 閃回技術之閃回刪除
- 360粉碎檔案可以恢復嗎，如何恢復360強力刪除的檔案
- roaming資料夾可以刪除嗎
- 【備份恢復】無備份線上恢復非關鍵資料檔案
- Mysql資料備份與恢復MySql
- FLASHBACK DATABASE可以恢復刪除的TABLESPACEDatabase
- Oracle 之利用BBED修改資料塊SCN----沒有備份資料檔案的資料恢復Oracle資料恢復
- Oracle閃回刪除恢復誤刪資料Oracle
- 【北亞資料恢復】誤刪除oracle表和誤刪除oracle表資料的資料恢復方法資料恢復Oracle
- 全備份情況下，刪除控制檔案及恢復
- [20210930]bbed恢復刪除的資料.txt
- 備份與恢復--從備份的歸檔日誌中恢復資料
- Sybase ASE資料庫恢復，Sybase資料恢復，資料誤刪除恢復工具READSYBDEVICE資料庫資料恢復dev
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- 資料庫在沒有備份的情況下的資料檔案損壞的恢復資料庫
- ORACLE 11G沒有備份檔案引數檔案在異機通過rman備份恢復找回被誤刪的資料Oracle
- oracle資料庫備份刪除操作Oracle資料庫
- 資料庫資料的恢復和備份資料庫
- 達夢資料庫備份恢復資料庫
- postgresql備份與恢復資料庫SQL資料庫
- 磁碟資料恢復及備份工具資料恢復
- mongo資料庫備份與恢復Go資料庫
- Redis的資料備份與恢復Redis
- Oracle備份恢復五(資料泵)Oracle