Oracle官方并不推荐在数据库物理结构发生改变的情况下进行Flashback,但有些特殊情况。例如项目上线时出现异常需要快速的回滚到表空间被删除之前的操作。那么flashback tablespace想比于进行全库的restore和recover能更快速的满足要求,减少宕机时间。
具体演示
SQL> create tablespace tt datafile '/home/oracle/tt.dbf' size 10m;
Tablespace created.
SQL> conn hr/hr;
Connected.
SQL> create table t1 tablespace tt as select * from hr.employees;
Table created.
SQL> conn / as sysdba
Connected.
SQL> create restore point aa guarantee flashback database;
Restore point created.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
854968
SQL> alter tablespace tt offline;
Tablespace altered.
SQL> drop tablespace tt including contents and datafiles;
Tablespace dropped.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1874841600 bytes
Fixed Size 1337268 bytes
Variable Size 1073743948 bytes
Database Buffers 788529152 bytes
Redo Buffers 11231232 bytes
Database mounted.
SQL> flashback database to restore point aa;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> col error for a20
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ---------
6 OFFLINE OFFLINE FILE NOT FOUND 0
6 rows selected.
SQL> col name for a60
SQL> select name, file#, status from v$datafile;
NAME FILE# STATUS
------------------------------------------------------------ ---------- -------
/u01/app/oracle/oradata/PROD/system01.dbf 1 SYSTEM
/u01/app/oracle/oradata/PROD/sysaux01.dbf 2 ONLINE
/u01/app/oracle/oradata/PROD/undotbs01.dbf 3 ONLINE
/u01/app/oracle/oradata/PROD/users01.dbf 4 ONLINE
/u01/app/oracle/oradata/PROD/example01.dbf 5 ONLINE
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006 6 OFFLINE
6 rows selected.
SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006' as '/home/oracle/tt.dbf';
Database altered.
SQL> select name, file#, status from v$datafile;
NAME FILE# STATUS
------------------------------------------------------------ ---------- -------
/u01/app/oracle/oradata/PROD/system01.dbf 1 SYSTEM
/u01/app/oracle/oradata/PROD/sysaux01.dbf 2 ONLINE
/u01/app/oracle/oradata/PROD/undotbs01.dbf 3 ONLINE
/u01/app/oracle/oradata/PROD/users01.dbf 4 ONLINE
/u01/app/oracle/oradata/PROD/example01.dbf 5 ONLINE
/home/oracle/tt.dbf 6 OFFLINE
6 rows selected.
SQL> recover datafile 6;
ORA-00279: change 854654 generated at 11/15/2015 21:49:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/PROD/archivelog/2015_11_15/o1_mf_1_7_c4k3f1g
g_.arc
ORA-00280: change 854654 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
SQL> desc hr.t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL> select count(*) from hr.t1;
COUNT(*)
----------
107
相关日志
Completed: ALTER DATABASE MOUNT
Sun Nov 15 21:50:17 2015
flashback database to restore point aa #flashback database
Flashback Restore Start
Flashback: created tablespace #7: 'TT' in the controlfile.
Flashback: created OFFLINE file 'UNNAMED00006' for tablespace #7 in the controlfile.
Filename was:
'/home/oracle/tt.dbf' when dropped.
File will have to be restored from a backup and recovered.
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/PROD/redo01.log
Incomplete Recovery applied until change 854954 time 11/15/2015 21:49:24
Flashback Media Recovery Complete
Completed: flashback database to restore point aa
alter database open resetlogs
Archived Log entry 1 added for thread 1 sequence 7 ID 0x10d32bc0 dest 1:
Archived Log entry 2 added for thread 1 sequence 5 ID 0x10d32bc0 dest 1:
Sun Nov 15 21:50:28 2015
Archived Log entry 3 added for thread 1 sequence 6 ID 0x10d32bc0 dest 1:
RESETLOGS after incomplete recovery UNTIL CHANGE 854954
Resetting resetlogs activation ID 282274752 (0x10d32bc0)
Sun Nov 15 21:50:35 2015
Setting recovery target incarnation to 3
......................................................
Dictionary check beginning
File #6 is offline, but is part of an online tablespace.
data file 6: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006' #发现datafile 6 offline
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
......................................................
alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006' as '/home/oracle/tt.dbf' #rename datafile 6
Completed: alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006' as '/home/oracle/tt.db
.................................
ALTER DATABASE RECOVER datafile 6 #recover datafile 6
Media Recovery Start
Serial Media Recovery started
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 854954
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 6 ...
Sun Nov 15 21:52:57 2015
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u01/app/oracle/flash_recovery_area/PROD/archivelog/2015_11_15/o1_mf_1_7_c4k3f1gg_.arc
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/PROD/redo01.log
Media Recovery Complete (PROD)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
alter database datafile 6 online
Completed: alter database datafile 6 online
本文原创,转载请注明出处,作者
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
ORACLE flashback database测试,主要进行ORACLE flashbackup 闪回整个数据库的实现。
全面总结和解释了oracle flashback技术的使用,优势以及限制
Oracle Flashback 技术总结
主要详细讲解了oracle10g以来对于闪回机制的类型及相关的使用方法和场景的介绍,很有用,尤其对于无意中对表数据删除了的恢复较有价值
ORACLE FLASHBACK综述.pdf
Oracle Flashback技术总结.比较先进一些,这些内容很丰富的~
NULL 博文链接:https://shihuan830619.iteye.com/blog/1485908
Oracle_Flashback_技术_总结.pdf
Oracle 10g High Availability-Flashback技術介紹
Oracle Flashback在医院数据恢复中的应用.pdf
FLASHBACK DATABASE flashback data1base闪回到过去的某一时刻 闪回点之后的所有工作都将丢失 必须使用resetlogs创建新的场景并打开数据库(一旦resetlogs之后,将不能再闪回至resetlogs之前的时间点) .......
必须设定undo保留时间足够大以能够重构需要闪回的数据 ALTER SYSTEM SET UNDO_RETENTION=; seconds值是undo数据保持的秒数。 Flashback view是由undo retention interval来限制的。
oracle数据库FLASHBACK系列功能介绍
oracle flashback特性(闪回语句,闪回表,闪回数据库).doc
NULL 博文链接:https://bestxiaok.iteye.com/blog/1129883
oracle 表恢复 闪回 flashback
如何正确删除Oracle归档日志,oralce归档日志清理.
oracle 10g flashback(数据恢复)/oracle 10g flashback(数据恢复)/oracle 10g flashback(数据恢复)/oracle 10g flashback(数据恢复)
Oracle Database 10g: High Availablity with RAC Flashback & Data Guard This text concentrates on explaining 10g technologies and practices to database administrators, covering general availability, ...
Oracle中FlashBack技术的应用 1: 闪回数据库; 2: 闪回drop掉的表; 3: 闪回对表数据的修改; 4: 闪回版本查询