当前位置：首页 > article >正文

经典的ORACLE 11/12/19闪回操作

article 2024/11/13 10:26:52

1、闪回表

SQL> show parameter recycle

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD4/PROD4/system01.dbf
/u01/app/oracle/oradata/PROD4/PROD4/sysaux01.dbf
/u01/app/oracle/oradata/PROD4/PROD4/undotbs01.dbf
/u01/app/oracle/oradata/PROD4/PROD4/users01.dbf
/u01/app/oracle/oradata/PROD4/PROD4/example01.dbf

10 rows selected.

SQL> create tablespace test datafile '/u01/app/oracle/oradata/PROD4/PROD4/test.dbf' size 10m;

Tablespace created.

SQL> create user sf identified by oracle default tablespace test;

User created.

SQL> grant dba to sf;

Grant succeeded.

SQL> conn sf/oracle
Connected.
SQL> create table TEST1 as select * from dba_objects;

create table TEST10 as select * from dba_objects;

Table created.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='TEST1';

SUM(BYTES)/1024/1024
--------------------
9

SQL> show recyclebin
SQL> desc dba_free_space;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER

SQL> select TABLESPACE_NAME,bytes/1024/1024 from dba_free_space where tablespace_name='TEST'; --查看TEST表空间剩余量
select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_free_space group by TABLESPACE_NAME; --查看所有表空间的剩余量
no rows selected

SQL> DROP TABLE TEST1;

Table dropped.

select TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM dba_free_space WHERE TABLESPACE_NAME='TEST' GROUP BY TABLESPACE_NAME；
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
TEST 9

SQL> alter database datafile 9 autoextend on;

SQL> select TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM dba_free_space WHERE TABLESPACE_NAME='TEST' GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
TEST 9

SQL> create table test2 as select * from dba_objects;

Table created.

SQL> show paramter recyclebin

SQL> select TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM dba_free_space WHERE TABLESPACE_NAME='TEST' GROUP BY TABLESPACE_NAME;

no rows selected

SQL> drop table test2;

Table dropped.

SQL> show parameter recyclebin
show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST2 BIN$JAkhk08TMaDgUL4KHGhpXg==$0 TABLE 2015-11-08:14:23:08
SQL> alter database datafile 11 autoextend off;

Database altered.

SQL> alter database datafile 9 resize 20m;
alter database datafile 9 resize 10m;

Database altered.

SQL> select TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM dba_free_space WHERE TABLESPACE_NAME='TEST' GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
TEST 19

SQL> create table test3 as select * from dba_objects;

Table created.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST2 BIN$JAkhk08TMaDgUL4KHGhpXg==$0 TABLE 2015-11-08:14:23:08

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$JAkhk08TMaDgUL4KHGhpXg==$0 TABLE
TEST3 TABLE

SQL> flashback table "BIN$JAkhk08TMaDgUL4KHGhpXg==$0" to before drop;
flashback table "BIN$i6USmIX6EDvgU0IQqMCSVw==$0" to before drop;

Flashback complete.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST2 TABLE
TEST3 TABLE

SQL>
purge recyclebin;

create table testa(id number,name varchar2(10));
drop table testa;
create table testa(id number,depart_name varchar2(10));
drop table testa;

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$HwOgJaEC91vgUL4KHGgVGw==$0 TABLE 2015-09-05:17:49:33
TEST BIN$HwOgJaEB91vgUL4KHGgVGw==$0 TABLE 2015-09-05:17:49:32

SQL> select owner,object_name,ts_name from DBA_RECYCLEBIN;

OWNER OBJECT_NAME TS_NAME
------------------------------ ------------------------------ ------------------------------
sf BIN$HwOgJaEB91vgUL4KHGgVGw==$0 USERS

SQL> desc "BIN$HwOgJaEC91vgUL4KHGgVGw==$0";
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
DEPART_NAME VARCHAR2(10)

SQL> flashback table "BIN$HwOgJaEC91vgUL4KHGgVGw==$0" to before drop rename to testnew;

Flashback complete.

SQL> desc testnew;

DROP TABLE <table_name> [PURGE] ;

#############################################################################
2、查询表已删除的数据（闪回查询）

SQL> create table ORIGINAL_SALARY as select EMPLOYEE_ID,COMMISSION_PCT from hr.employees;
create table ORIGINAL_SALARY1 as select EMPLOYEE_ID,COMMISSION_PCT from hr.employees;
create table ORIGINAL_SALARY3 as select EMPLOYEE_ID from hr.employees;
create table ORIGINAL_SALARY5 as select EMPLOYEE_ID from hr.employees;

select * from ORIGINAL_SALARY1 where EMPLOYEE_ID=102;
查看下操作系统时间date

2019-06-19 11:36:54

delete ORIGINAL_SALARY where employee_id in (select employee_id from hr.employees where HIRE_DATE<to_date('2002-01-01','yyyy-mm-dd'));
delete ORIGINAL_SALARY1 where employee_id in (select employee_id from hr.employees where HIRE_DATE<to_date('2002-01-01','yyyy-mm-dd'));

insert

24 05:36:55 UTC 2016
02:19:22

select * from ORIGINAL_SALARY AS OF timestamp to_date('2019-06-19 11:26:27','yyyy-mm-dd hh24:mi:ss') where employee_id in (select employee_id from hr.employees where HIRE_DATE<to_date('2002-01-01','yyyy-mm-dd'));
select * from ORIGINAL_SALARY1 AS OF timestamp to_date('2019-06-19 11:26:27','yyyy-mm-dd hh24:mi:ss') where employee_id in (select employee_id from hr.employees where HIRE_DATE<to_date('2002-01-01','yyyy-mm-dd'));
select count(*) from ORIGINAL_SALARY3 AS OF timestamp to_date('2019-06-19 11:36:54','yyyy-mm-dd hh24:mi:ss');
select count(*) from ORIGINAL_SALARY3 AS OF timestamp to_date('2019-06-19 11:39:00','yyyy-mm-dd hh24:mi:ss');
select count(*) from ORIGINAL_SALARY5 AS OF timestamp to_date('2019-06-19 11:45:45','yyyy-mm-dd hh24:mi:ss');
select count(*) from ORIGINAL_SALARY5 AS OF timestamp to_date('2019-06-19 11:46:08','yyyy-mm-dd hh24:mi:ss');

create view orig_view as select * from ORIGINAL_SALARY AS OF timestamp to_date('2016-05-27 02:19:22','yyyy-mm-dd hh24:mi:ss') ;

02:15:40 UTC 2016
--delete 整个表后可以使用flashback table 找回

SQL> alter table test enable row movement;--先打开行移动的功能

flashback table ORIGINAL_SALARY to timestamp to_date('2016-07-12 02:17:33','yyyy-mm-dd hh24:mi:ss');
flashback table DEPARTMENTS to timestamp to_date('2016-05-27 03:09:22','yyyy-mm-dd hh24:mi:ss');
————————————————————————————————————

对于truncate的表，除了flashback database之外，其它的flashback功能可能帮不了你）
truncate table test2;

flashback database to timestamp to_timestamp('2016-02-24 05:36:55','yyyy-mm-dd hh24:mi:ss');

#############################################################################
3、闪回数据库（需要人工打开闪回数据库功能）

确保归档是打开的

SQL>show parameter recover
SQL>show parameter db_flash

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
在oracle11g中打开闪回数据库可以在open状态打开

归档是必须要开启

下面这是oracle10g时的操作：
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2G;
SQL> ALTER SYSTEM SET db_recovery_file_dest ='/u01/app/oracle/flash';
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH; --默认是1440 一天
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

##############################################################3
估算闪回数据库日志所需要的磁盘空间：
select estimated_flashback_size,retention_target,flashback_size from v$flashback_database_log;
确定当前闪回窗口,确定可闪回到的最小scn号
SELECT oldest_flashback_scn,
oldest_flashback_time
FROM V$FLASHBACK_DATABASE_LOG;

监视闪回数据库日志中的事件记录:
SELECT begin_time, end_time, flashback_data, db_data,
redo_data, estimated_flashback_size AS EST_FB_SZE
FROM V$FLASHBACK_DATABASE_STAT;

找出当前scn：
select current_scn from v$database;
获得当前SCN
select current_scn from v$database;
*** select dbms_flashback.get_system_change_number from dual;

3010059

SQL> create table t1 as select * from dba_objects;

create table t2 as select * from dba_objects;

Table created.

SQL> insert into t1 select * from t1;
insert into t2 select * from t2;

SQL> startup mount force;
ORACLE instance started.

Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 226493416 bytes
Database Buffers 402653184 bytes
Redo Buffers 5615616 bytes
Database mounted.
SQL> flashback database to scn 1182885;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

SQL> alter database open resetlogs;

创建普通还原点：
SQL> CREATE RESTORE POINT test_POINT;

SQL> FLASHBACK DATABASE TO RESTORE point test_POINT;

可靠还原点：
CREATE RESTORE POINT test_guarantee GUARANTEE FLASHBACK DATABASE;

FLASHBACK DATABASE TO RESTORE point test_guarantee;

基本时间点的闪回：
FLASHBACK DATABASE TO TIMESTAMP(TO_DATE('2016-01-18 06:19:00', 'YYYY-MM-DD HH24:MI:SS'));

#############################################################################
4、创建一个默认的闪回数据归档，取名为“FRA”，操作者必须拥有SYSDBA权限：

SQL> create tablespace fratbs datafile '/u01/app/oracle/oradata/PROD4/PROD4/fratbs.dbf' size 50m;

SQL> create user fr_admin identified by oracle default tablespace fratbs;
SQL> grant connect,dba to fr_admin;
SQL> grant flashback archive administer to fr_admin;

Grant succeeded.

SQL> create flashback archive default fra tablespace fratbs quota 10m retention 1 year;
SQL> alter table SH.PROMOTIONS flashback archive;

SQL> select OWNER_NAME,FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS,STATUS from DBA_FLASHBACK_ARCHIVE;
SQL> select TABLE_NAME,OWNER_NAME,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS from DBA_FLASHBACK_ARCHIVE_TABLES;

#############################################################################5、flash version query
flashback version query提供了一个审计行改变的功能，它能找到所有已经提交了的行的记录，比如说，时间1，我插入一条记录，时间2我删除了这条记录，对于时间3，当然查询不到这条记录，但是flashback version query能够把时间1、时间2的操作给记录下来，并详细的查询出来。
flashback version query一样依赖于AUM（自动undo管理），所以，这里就不再多说AUM的配置。flashback version query采用VERSIONS BETWEEN语句来进行查询
，常用的有：
·VERSIONS_SCN – 系统改变号
·VERSIONS_TIMESTAMP – 时间

下面将展示这个 flashback version query

SQL> connect test/test
Connected.
SQL> create table t1(ID int,NAME varchar2(10));
Table created.
我们执行一系列如下的操作:
insert into T1 values(1,'A');

commit;

update T1 set ID=2;

commit;

update T1 set ID=3;

commit;

update T1 set ID=4;

现在，我们看到该表最后的记录是
16:34:49 SQL> select * from T1;
A B
---------- ----------
5 16:34:34
这个时候，如果我们利用flash table或者是flash query，我们也只能是看到过去的莫一个快照而已，利用flashback version query,我们将找到其中的任何变化，如
SQL>select versions_starttime, versions_endtime, versions_xid,
versions_operation,ID,NAME
from t1 versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME;

SELECT * FROM (select versions_starttime, versions_endtime, versions_xid,
versions_operation,ID,NAME
from t1 versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME) WHERE NAME='B' ;

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ID NAME
------------------------- ------------------------------ ---------------- - ---------- ----
12-OCT-15 02.01.30 PM 01000D00C3010000 U 5 D
12-OCT-15 02.01.30 PM 12-OCT-15 02.01.30 PM 0600110096020000 I 4 C
12-OCT-15 02.01.30 PM 12-OCT-15 02.01.30 PM 0A001F00FB010000 I 1 A
12-OCT-15 02.01.30 PM 12-OCT-15 02.01.30 PM 04001C002B020000 U 3 B
12-OCT-15 02.01.30 PM 12-OCT-15 02.01.30 PM 080009006D030000 U 2 B
12-OCT-15 02.01.30 PM 090002008E020000 D 3 B

select versions_starttime, versions_endtime, versions_xid,
versions_operation,salary
from employees versions between timestamp minvalue and maxvalue where employee_id=107
order by VERSIONS_STARTTIME;
注意：
此处显示了对该表所作的所有更改，甚至包括该行被删除和重新插入的情况。VERSION_OPERATION 列显示对该行执行了什么操作 (Insert/Update/Delete)。
所做的这些工作不需要历史表或额外的列。
flashback version query记载的是行的改变，如果一个操作有多行的改变，如delete from tablename，那么，在flashback version query对应的也是多行。

在上述查询中，列 versions_starttime、versions_endtime、versions_xid、versions_operation 是伪列，与 ROWNUM、LEVEL 等其他熟悉的伪列相类似。其他伪列 — 如 VERSIONS_STARTSCN 和 VERSIONS_ENDSCN — 显示了该时刻的系统更改号。列 versions_xid 显示了更改该行的事务标识符。有关该事务的更多详细信息可在视图 FLASHBACK_TRANSACTION_QUERY 中找到（这个将在flashback transaction query中描述）。

当然，除了分析以上所有的变更之外，我们可以指定时间段，如
select ID,NAME
from test versions between timestamp
to_date('2004-04-07 04:34:04','yyyy-mm-dd hh24:mi:ss')
and to_date('2004-04-07 04:34:50','yyyy-mm-dd hh24:mi:ss');

还可以使用 SCN 来找出过去的版本值。可以从伪列 VERSIONS_STARTSCN 和 VERSIONS_ENDSCN 中获得 SCN 号。以下是一个示例：
select versions_starttime, versions_endtime
from test versions
between scn 1000 and 1001
#############################################################################6、flashback transaction query闪回事务：
Oracle 11g的闪回事务查询就是对过去某段时间内所完成的事务的查询和撤销。10g数据来源于undo，11g来自undo/redo/archivelog，在考试中我们使用em来操作，举例如下：
a、打开数据库补充日志：
sql>alter database add supplemental log data;
b、打开主键补充日志：
sql>alter database add supplemental log data (primary key) columns;
然后用em查询，和查询闪回版本差不多：打开em----Availability----->Perform Recovery----如下选择

/*== transaction 1 ==*/

INSERT INTO hr.departments VALUES (280,'ERP', NULL, 1700)
/
INSERT INTO hr.departments VALUES (290,'Support Operations', NULL, 1700)
/
INSERT INTO hr.departments VALUES (300,'HR Training', NULL, 1700)
/
commit
/

/*== transaction 2 ==*/

update hr.departments set department_id=310 where department_id=280
/
commit
/

/*== transaction 3 ==*/

update hr.departments set department_name='ABC' where department_id=310
/
commit

alter system switch logfile; 切三遍以上
然后用em查询，和查询闪回版本差不多：打开em----Availability----->Perform Recovery----如下选择：将上面的事务操作闪回到第一次update之前。

查看全文

http://www.kler.cn/a/387456.html