/* drop table ,not purge */
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> show user
USER is "SCOTT"
SQL> select table_name from user_tables order by BLOCKS;
TABLE_NAME
------------------------------
...
SMALL_TABLE
X041
...
SQL> select * from x041 where rownum < 2;
ID XSCN
---------- --------
2 006d8b27
SQL> select count(*) from x041 ;
COUNT(*)
----------
4002
SQL>
SQL> drop table x041;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TT1 BIN$6+UnlhUkfGzgQKjAWOlGFA==$0 TABLE 2013-11-24:11:50:57
X041 BIN$6+UnlhUsfGzgQKjAWOlGFA==$0 TABLE 2013-11-24:11:57:43
SQL> select count(*) from "BIN$6+UnlhUsfGzgQKjAWOlGFA==$0";
COUNT(*)
----------
4002
SQL> flashback table "BIN$6+UnlhUsfGzgQKjAWOlGFA==$0" to before drop;
Flashback complete.
SQL> select count(*) from x041;
COUNT(*)
----------
4002
/* more info... */
Oracle 10g 开始， 每个表空间都会有一个叫作回收站的逻辑区域，当用户执行drop命令时， 被删除的表和表的关联对象
( 包括索引， 约束，触发器，LOB段，LOB index 段) 不会被物理删除， 这些对象先转移到回收站中，这就给用户提供了一个恢复的可能。
1.查看数据库是否开户recyclebin功能
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
或者只将当前会话开启功能
alter session set recyclebin = on;
2.查看当前用户中的垃圾回收站的对象:
select * from recyclebin;
3.drop table xxx时，不希望将表放入垃圾回收站，而是直接drop
drop table xxx purge;
4.将recyclebin中对象还原或清除
--还原
flashback table "xxxxx" to before drop
"xxxx"乃是recyclebin视图中OBJECT_NAME字段名称
flashback table "xxxxx" to before drop
亦可换成
flashback table "xxxxx" to before drop rename to xxxx重命名
--清除
purge table xxxx
purge recyclebin
还可以指定某个表空间和某个用户下表。
ps:
recycelbin中还保留了index。
如个表空间不够创建新对象时，oracle将自动请除recyclebin中的对象。
以下几种drop不会将相关对象放进RecycleBin：
* drop tablespace：会将RecycleBin中所有属于该tablespace的对象清除
* drop user：会将RecycleBin中所有属于该用户的对象清除
* drop cluster：会将RecycleBin中所有属于该cluster的成员对象清除
* drop type：会将RecycleBin中所有依赖该type的对象清除
Flashback Drop 需要注意的地方：
1). 只能用于非系统表空间和本地管理的表空间
2). 对象的参考约束不会被恢复，指向该对象的外键约束需要重建。
3). 对象能否恢复成功，取决与对象空间是否被覆盖重用。
4). 当删除表时，信赖于该表的物化视图也会同时删除，但是由于物化视图并不会被放入recycle bin，因此当你执行flashback table to before drop 时，也不能恢复依赖其的物化视图，需要dba 手工介入重新创建。
5). 对于Recycle Bin中的对象，只支持查询.
6). Flashback 不支持sys用户. system表空间下的对象，也不能从回收站里拿到。
故使用SYS 或者SYSTEM用户登陆时， show recyclebin 为空。
7). Flashback Drop 是基于Tablespace RecycleBin 来实现恢复的。
它只支持闪回与table 相关连的对象，比如表，索引，约束，触发器等
如果是函数或者存储过程等，就需要使用Flashback Query来实现。
/* 设置闪回恢复区 */
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 1
recovery_parallelism integer 0
col value for a30
col name for a30
select NAME,VALUE,ISSYS_MODIFIABLE from v$parameter where name like 'db_recovery_file_dest%';
NAME VALUE ISSYS_MOD
------------------------------ ------------------------------ ---------
db_recovery_file_dest IMMEDIATE
db_recovery_file_dest_size 1 IMMEDIATE
/* os mkdir ..*/
$ mkdir /u01/flash_recovery_area
SQL> alter system set db_recovery_file_dest_size = 2G scope=both;
System altered.
SQL> alter system set db_recovery_file_dest = '/u01/flash_recovery_area' scope=both;
System altered.
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/flash_recovery_area
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
show parameter db_flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
db_flashback_retention_target 默认是1440分钟，即24 小时
/* 需要mount状态 开启*/
SQL> shutdown immediate
SQL> startup mount
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
/* 所有和恢复相关的文件都可以存放到闪回恢复区 */
SQL> select file_type from v$flash_recovery_area_usage;
FILE_TYPE
--------------------
CONTROL FILE
REDO LOG
ARCHIVED LOG
BACKUP PIECE
IMAGE COPY
FLASHBACK LOG
FOREIGN ARCHIVED LOG
7 rows selected.
/*
在一些 10g 的动态视图里( V$CONTROLFILE, V$LOGFILE, V$ARCHIVED_LOG, V$DATAFILE_COPY 等 )
的新的列 IS_RECOVERY_DEST_FILE ,指明相关的文件是否在恢复区内。
*/
SQL> SELECT recid, blocks, is_recovery_dest_file FROM v$archived_log WHERE recid < 5;
RECID BLOCKS IS_
---------- ---------- ---
1 1 NO
2 1 NO
3 23 NO
4 1 NO
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 11
Next log sequence to archive 16
Current log sequence 16
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=/u01/arch
log_archive_dest_10 string
log_archive_dest_11 string
/*
设置闪回恢复区后，如果没有设置过log_archive_dest_n参数，则归档日志默认是保存到该区域的。
实际上，oracle是通过隐式的设置log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST'来实现的。
所以，如果修改过log_archive_dest_n将归档日志保存到其他位置，也可以修改该参数继续使用闪回恢复区。
我是先设置了log_archive_dest_1, 现在把闪回区也加入多路归档中
*/
SQL> alter system set log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL>
SQL>
SQL>
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=/u01/arch
log_archive_dest_10 string location=USE_DB_RECOVERY_FILE_
DEST
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 17
Current log sequence 17
/* 手动归档看一下 */
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
/* 多路 */
col name for a20
select * from
(select name,sequence#,first_change#,next_change#,blocks,recid,is_recovery_dest_file,first_time,next_time
from v$archived_log a order by SEQUENCE# desc) where rownum < 5 ;
NAME SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# BLOCKS RECID IS_ FIRST_TIME NEXT_TIME
-------------------- ---------- ------------- ------------ ---------- ---------- --- ------------------ ------------------
/u01/arch/1_18_83207 18 7981145 7981241 218 16 NO 24-NOV-13 24-NOV-13
5598.arc
/u01/flash_recovery_ 18 7981145 7981241 218 17 YES 24-NOV-13 24-NOV-13
area/MYORCL11/archiv
elog/2013_11_24/o1_m
f_1_18_9931nt7j_.arc
/u01/arch/1_17_83207 17 7980147 7981145 1272 14 NO 24-NOV-13 24-NOV-13
5598.arc
/u01/flash_recovery_ 17 7980147 7981145 1272 15 YES 24-NOV-13 24-NOV-13
area/MYORCL11/archiv
elog/2013_11_24/o1_m
f_1_17_9931m69j_.arc
/* e.g. 当purge table时, flashback database可以找回 */
SQL> select count(*) from REDOTAB1;
COUNT(*)
----------
500
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
7982417
SQL> select to_char(sysdate,'yy-mm-dd hh24:mi:ss') time from dual;
TIME
-----------------
13-11-24 13:12:46
SQL> drop table REDOTAB1;
Table dropped.
SQL> purge table REDOTAB1;
Table purged.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
SQL> startup mount;
SQL> select dbid,checkpoint_change# from v$database;
DBID CHECKPOINT_CHANGE#
---------- ------------------
2432893466 7983062
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
24-NOV-13 01.14.51.828325 PM +08:00
SQL> flashback database to timestamp to_timestamp('13-11-24 13:12:46','yy-mm-dd hh24:mi:ss');
Flashback complete.
/* df信息已变化 */
SQL> select dbid,checkpoint_change# from v$database;
DBID CHECKPOINT_CHANGE#
---------- ------------------
2432893466 7983062
SQL> select file#,checkpoint_change#,last_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') cpt from v$datafile;
select file#,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') cpt from v$datafile_header;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE# CPT
---------- ------------------ ------------ -------------------
1 7982431 2013-11-24 13:12:47
2 7982431 2013-11-24 13:12:47
3 7982431 2013-11-24 13:12:47
4 7982431 2013-11-24 13:12:47
5 7982431 2013-11-24 13:12:47
6 7982431 2013-11-24 13:12:47
7 7982431 2013-11-24 13:12:47
8 7982431 2013-11-24 13:12:47
9 7982431 2013-11-24 13:12:47
10 7982431 2013-11-24 13:12:47
1024 7982431 2013-11-24 13:12:47
1025 7982431 2013-11-24 13:12:47
1026 7982431 2013-11-24 13:12:47
1027 7982431 2013-11-24 13:12:47
1028 7982431 2013-11-24 13:12:47
15 rows selected.
SQL>
FILE# CHECKPOINT_CHANGE# CPT
---------- ------------------ -------------------
1 7982431 2013-11-24 13:12:47
2 7982431 2013-11-24 13:12:47
3 7982431 2013-11-24 13:12:47
4 7982431 2013-11-24 13:12:47
5 7982431 2013-11-24 13:12:47
6 7982431 2013-11-24 13:12:47
7 7982431 2013-11-24 13:12:47
8 7982431 2013-11-24 13:12:47
9 7982431 2013-11-24 13:12:47
10 7982431 2013-11-24 13:12:47
1024 7982431 2013-11-24 13:12:47
1025 7982431 2013-11-24 13:12:47
1026 7982431 2013-11-24 13:12:47
1027 7982431 2013-11-24 13:12:47
1028 7982431 2013-11-24 13:12:47
15 rows selected.
SQL> alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
/* 执行完flashback database 命令之后，oracle 提供了两种方式让你修复数据库
1). 直接alter database open resetlogs 打开数据库，当然，指定scn 或者timestamp 时间点之后产生的数据统统丢失。
2). 先执行alter database open read only 命令，以read-only 模式打开数据库，然后立刻通过逻辑导出的方式将误操作
涉及表的数据导出，再执行recover database 命令以重新应用数据库产生的redo，将数据库修复到flashback database 操作前的状态，然后再通过逻辑导入的方式，将之前误操作的表重新导入，这样的话对现有数据的影响最小，不会有数据丢失。
*/
SQL> alter database open read only;
Database altered.
SQL> select count(*) from xujh.REDOTAB1; /* flashback worked*/
COUNT(*)
----------
500
/* 只读模式下无法 expdp */
[oracle@localhost ~]$ expdp system/oracle tables=REDOTAB1 job_name=myjob1 dumpfile=/u01/REDOTAB1.dmp;
Export: Release 11.2.0.1.0 - Production on Sun Nov 24 13:46:26 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. 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
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.MYJOB1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-16000: database open for read-only access
/* 改用exp */
[oracle@localhost ~]$ exp system/oracle file=/u01/f1.dmp tables=XUJH.REDOTAB1 rows=y;
Export: Release 11.2.0.1.0 - Production on Sun Nov 24 13:49:13 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. 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
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to XUJH
. . exporting table REDOTAB1 500 rows exported
Export terminated successfully without warnings.
[oracle@localhost ~]$ ls /u01/f1.dmp
/u01/f1.dmp
/* recover database
1 可以直接 recover database;
2 再flashback回去...
实质上是一样的, 都是做介质恢复 可看alert.log
*/
SQL> recover database;
或者:
SQL> flashback database to scn 7983061;
Flashback complete.
SQL> flashback database to scn 7983062;
flashback database to scn 7983062
*
ERROR at line 1:
ORA-38743: Time/SCN is in the future of the database.
SQL> select dbid,checkpoint_change# from v$database;
DBID CHECKPOINT_CHANGE#
---------- ------------------
2432893466 7983062
SQL> select file#,checkpoint_change#,last_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') cpt from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE# CPT
---------- ------------------ ------------ -------------------
1 7983060 7983060 2013-11-24 13:13:41
2 7983060 7983060 2013-11-24 13:13:41
3 7983060 7983060 2013-11-24 13:13:41
....
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
[oracle@localhost ~]$ imp system/oracle file=/u01/f1.dmp full=y;
Import: Release 11.2.0.1.0 - Production on Sun Nov 24 14:17:27 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. 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
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
. importing XUJH's objects into XUJH
. . importing table "REDOTAB1" 500 rows imported
Import terminated successfully without warnings.
SQL> select count(*) from xujh.REDOTAB1 ;
COUNT(*)
----------
500
------------------------------ end -----------------------------------------
/* v$flashback_database_log
Flashback Database 所能回退到的最早时间 */
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------ ---------------- -------------- ------------------------
7978582 24-NOV-13 1440 63053824 1684365312
/* v$flashback_database_stat
以小时为单位记录单位时间内数据库的活动量：*/
SQL> select * from v$flashback_database_stat;
BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------ ------------------ -------------- ---------- ---------- ------------------------
24-NOV-13 24-NOV-13 42811392 101294080 13831680 0
/*Flashback Query
利用oracle 查询多版本一致的特点，实现从回滚段中读取表一定时间内操作过的数据，可用来进行数据比对，
或者修正意外提交造成的错误数据
分Flashback Query,Flashback Version Query， Flashback Transaction Query 三种。
flashback query 对v$tables,x$tables 等动态性能视图无效，不过对于dba_*,all_*,user_*等数据字典是有效的。
该特性也完全支持访问远端数据库，比如select * from tbl@dblink as of scn 3600;的形式。
*/
syntax:
select * from tab_name as of timestamp xxx;
select * from tab_name as of scn dec_xxx;
SQL> delete from xujh.REDOTAB1 ;
500 rows deleted.
SQL> select count(*) from xujh.REDOTAB1 ;
COUNT(*)
----------
0
SQL> select count(*) from xujh.REDOTAB1 as of timestamp sysdate-1/1440;
COUNT(*)
----------
500
SQL> select sysdate from dual;
SYSDATE
------------------
24-NOV-13
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-11-24 14:45:21
SQL> select count(*) from xujh.REDOTAB1 as of timestamp
to_timestamp('2013-11-24 14:42:21','yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
500
SQL> select timestamp_to_scn(to_timestamp('2013-11-24 14:42:21','yyyy-mm-dd hh24:mi:ss')) as fls_scn from dual;
FLS_SCN
----------
7987733
SQL> select count(*) from xujh.REDOTAB1 as of scn 7987733;
COUNT(*)
----------
500
/* Oracle 在内部都是使用scn，即使你指定的是as of timestamp，oracle 也会将其转换成scn，
系统时间标记与scn 之间存在一张表，即SYS 下的SMON_SCN_TIME
每隔5 分钟，系统产生一次系统时间标记与scn 的匹配并存入sys.smon_scn_time 表，该表中记录了最近1440个系统时间标记与scn 的匹配记录，由于该表只维护了最近的1440 条记录，
因此如果使用as of timestamp 的方式则只能flashback 最近5 天内的数据（假设系统是在持续不断运行并无中断或关机重启之类操作的话）。
*/
select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss')from sys.smon_scn_time order by scn desc;
SCN TO_CHAR(TIME_DP,'YY
---------- -------------------
7988955 2013-11-24 06:44:20
7988343 2013-11-24 06:43:52
7987492 2013-11-24 06:37:48
7987253 2013-11-24 06:34:03
7984781 2013-11-24 06:27:47
7984617 2013-11-24 06:24:13
7984127 2013-11-24 06:17:46
7983297 2013-11-24 06:12:45
7982215 2013-11-24 05:10:02
7981852 2013-11-24 05:03:56
/* Flashback Query 函数，存储过程，包，触发器等对象 */
基于timestamp恢复的语句
SQL>SELECT text
FROM dba_source
AS OF TIMESTAMP TO_TIMESTAMP ('XXXXX', 'YYYY-MM-DD HH24:MI:SS')
WHERE owner = 'XXXX' AND name = '你删除的对象名'
ORDER BY line;
more info:
http://blog.csdn.net/tianlesoftware/article/details/4677378
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28859270/viewspace-777437/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/28859270/viewspace-777437/
1145
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
