[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