[20170213]刪除資料沒有備份可以恢復嗎.txt

lfree發表於2017-02-13

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章