ORACLE之闪回—flashback
1. 闪回数据库
1．1开启数据库闪回功能
SQL> select name ,flashback_on from v$database;
NAME FLASHBACK_ON
------------------------------------------------------
ZLZCS NO
--开启归档
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /arch/local_arch
Oldest online log sequence 3
Current log sequence 5
--修改归档位置
SQL> alter system set log_archive_dest_1='location=/arch/local_arch' scope=both;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
--开启归档
SQL> alter database archivelog;
Database altered.
--开启闪回
SQL> alter database flashback on;
Database altered.
--打开数据库
SQL> alter database open;
Database altered.
--查看归档状态
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch/local_arch
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
--查看闪回，归档状态
SQL> select flashback_on,log_mode from v$database;
FLASHBACK_ON LOG_MODE
------------------------------------------------------------
YES ARCHIVELOG
1．2关闭数据库闪回功能
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
--关闭闪回功能
SQL> alter database flashback off;
Database altered.
--关闭归档功能
SQL> alter database noarchivelog;
Database altered.
开启数据库
SQL> alter database open;
Database altered.
--查看归档关态
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /arch/local_arch
Oldest online log sequence 3
Current log sequence 5
--查看闪回，归档状态
SQL> select flashback_on,log_mode from v$database;
FLASHBACK_ON LOG_MODE
------------------------------------ ------------------------
NO NOARCHIVELOG
1．3设置db_flashback_retention_target
SQL> alter system set db_flashback_retention_target=1440 scope=both;
System altered.
1．4闪回数据库测试
--备份数据库
RMAN> backup database;
Starting backup at 13-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s)in backupset
input datafile fno=00001name=/opt/oracle/oradata/zlzcs/system01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/zlzcs/sysaux01.dbf
input datafile fno=00005name=/opt/oracle/oradata/zlzcs/xb01.dbf
input datafile fno=00002name=/opt/oracle/oradata/zlzcs/undotbs01.dbf
input datafile fno=00004name=/opt/oracle/oradata/zlzcs/users01.dbf
channel ORA_DISK_1: starting piece 1 at13-MAY-13
channel ORA_DISK_1: finished piece 1 at13-MAY-13
piecehandle=/opt/oracle/flash_recovery_area/ZLZCS/backupset/2013_05_13/o1_mf_nnndf_TAG20130513T021804_8s11f2mx_.bkptag=TAG20130513T021804 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:02:25
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s)in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at13-MAY-13
channel ORA_DISK_1: finished piece 1 at13-MAY-13
piecehandle=/opt/oracle/flash_recovery_area/ZLZCS/backupset/2013_05_13/o1_mf_ncsnf_TAG20130513T021804_8s11kk0n_.bkptag=TAG20130513T021804 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:05
Finished backup at 13-MAY-13
--清空数据
SQL> select count(*) from tb_test;
COUNT(*)
----------
30000
SQL> truncate table tb_test;
Table truncated.
SQL> select count(*) from tb_test;
COUNT(*)
----------
0
--当前时间
SQL> select to_char(sysdate,'yyyy-mm-ddhh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2013-05-13 02:25:44
--确认能恢复的时间点
SQL> select * from v$flashback_database_logfile;
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE#FIRST_TIME
-------------------------------------------------------------------------------- ---------- ---------- ---------- ------------- ------------
/opt/oracle/flash_recovery_area/ZLZCS/flashback/o1_mf_8s10m7h1_.flb 1 1 1 8192000 456505 13-MAY-13
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHRETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ---------------------------- -------------- ------------------------
456465 13-MAY-13 1440 8192000 212017152
--开始恢复
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2013-05-13 02:25:35','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> select count(*) from tb_test;
COUNT(*)
----------
0
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2013-05-13 02:20:45','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> select count(*) from tb_test;
COUNT(*)
----------
30000
--reset logs
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from tb_test;
COUNT(*)
----------
30000
--再次备份数据库
RMAN> backup database;
Starting backup at 13-MAY-13
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s)in backupset
input datafile fno=00001name=/opt/oracle/oradata/zlzcs/system01.dbf
input datafile fno=00003name=/opt/oracle/oradata/zlzcs/sysaux01.dbf
input datafile fno=00005name=/opt/oracle/oradata/zlzcs/xb01.dbf
input datafile fno=00002name=/opt/oracle/oradata/zlzcs/undotbs01.dbf
input datafile fno=00004name=/opt/oracle/oradata/zlzcs/users01.dbf
channel ORA_DISK_1: starting piece 1 at13-MAY-13
channel ORA_DISK_1: finished piece 1 at13-MAY-13
piecehandle=/opt/oracle/flash_recovery_area/ZLZCS/backupset/2013_05_13/o1_mf_nnndf_TAG20130513T024745_8s134l8v_.bkptag=TAG20130513T024745 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:01:16
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s)in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at13-MAY-13
channel ORA_DISK_1: finished piece 1 at13-MAY-13
piecehandle=/opt/oracle/flash_recovery_area/ZLZCS/backupset/2013_05_13/o1_mf_ncsnf_TAG20130513T024745_8s1370bg_.bkptag=TAG20130513T024745 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:03
Finished backupat 13-MAY-13
涉及到的视图
V$database;
V$flashback_database_log;
V$flashback_database_logfile;
V$flashback_database_stat;
2．Flashback drop table
--查看是否开启recyclebin功能
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------------------------------------------------
recyclebin string on
--查看recyclebin对像
SQL> drop table tb_test;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------------------------------------- ------------ -------------------
TB_TEST BIN$3JUwXxJzTePgQAB/AQBgVQ==$0TABLE 2013-05-13:03:08:51
--通过视图查看recyclebin对像
SQL> select object_name,original_name from recyclebin;
OBJECT_NAME ORIGINAL_NAME
----------------------------------------------------------------------------------------------------------------------------
BIN$3JUwXxJzTePgQAB/AQBgVQ==$0 TB_TEST
--可以像查看普通表一样来查看recyclebin对象
SQL> select count(*) from "BIN$3JUwXxJzTePgQAB/AQBgVQ==$0";
COUNT(*)
----------
30000
--闪回表
SQL> flashback table tb_test to before drop;
Flashback complete.
--闪回指定的recyclebin
SQL> drop table tb_test;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------------------ -------------------
TB_TEST BIN$3JUwXxJ0TePgQAB/AQBgVQ==$0TABLE 2013-05-13:03:28:42
SQL> flashback table"BIN$3JUwXxJ0TePgQAB/AQBgVQ==$0" to before drop;
Flashback complete.
-闪回后重命名
SQL> drop table tb_test;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------------------------------------- ------------ -------------------
TB_TEST BIN$3JUwXxJ1TePgQAB/AQBgVQ==$0TABLE 2013-05-13:03:30:45
SQL> flashback table tb_test to before drop rename to tb_test1;
Flashback complete.
SQL> select count(*) from tb_test1;
COUNT(*)
----------
30000
SQL> select count(*) from tb_test;
select count(*) from tb_test
*
ERROR at line 1:
ORA-00942: table or view does not exist
3．Flashback query
--flasback query
SQL> select count(*) from tb_test as of timestamp to_timestamp('2013-05-13:03:28:42','yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
30000
4．Flashback version query
--flasback version query
SQL> update tb_test setname='zhaolizheng' where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select versions_xid,versions_startscn,versions_endscn,versions_operation,id,name
2 from tb_test
3 versions between scn minvalue and maxvalue
4 where id=1;
VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN VE ID NAME
---------------- -------------------------------- -- --------------------------------------------------------------------------------
04001F00C6000000 461584 U 1 zhaolizheng
461584 1 striver1
5．分析ora_rowscn
--创建表
--不指定rowdependencies
SQL> create table emp1 (id integer,name varchar2(50))tablespace tbs_xb;
Table created.
--指定rowdependencies
SQL> create table emp2(id integer,namevarchar2(50)) tablespace tbs_xb rowdependencies;
Table created.
--插入数据
SQL> insert into emp1 values(1,'striver1');
1 row created.
SQL> insert into emp2 values(2,'striver2');
1 row created.
SQL> insert into emp1 values(2,'striver2');
1 row created.
SQL> insert into emp2 values(1,'striver1');
1 row created.
SQL> commit;
Commit complete.
--查看两个表的数据位置
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from emp1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------------------------------------------
5 120
5 120
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from emp2;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------------------------------------------
5 128
5 128
---dump出数据库内容
SQL> alter system dump datafile 5 block 120;
System altered.
SQL> alter system dump datafile 5 block 128;
System altered.
SQL> select to_number('7161f','xxxxxxxxxxxxxx') from dual;
TO_NUMBER('7161F','XXXXXXXXXXXXXX')
-----------------------------------
464415
SQL> select scn_to_timestamp(464415) from dual;
SCN_TO_TIMESTAMP(464415)
---------------------------------------------------------------------------
13-MAY-13 05.24.15.000000000 AM
6．Flashback transaction query
SQL> select xid,start_timestamp,undo_sql from flashback_transaction_query where rownum<2;
XID START_TIMEST
---------------- ------------
UNDO_SQL
--------------------------------------------------------------------------------
01001100C6000000 13-MAY-13
update "SYS"."JOB$" set"LAST_DATE" = TO_DATE('13-MAY-13', 'DD-MON-RR'), "THIS_D
ATE" = TO_DATE('13-MAY-13','DD-MON-RR'), "NEXT_DATE" = TO_DATE('13-MAY-13', 'DD
-MON-RR'), "TOTAL" ='.00053240740740740740740740740740740740741', "FAILURES" =
'0', "FLAG" = '0' where ROWID ='AAAADtAABAAAAZ6AAA';
1562
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
