全面测试oracle 10g的闪回功能

1296阅读　0评论2009-03-26　舒风星
分类：Oracle

1.开启10g的数据库闪回特征
a.确定当前的模式：
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

SQL> show parameter db_recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oracle/product/10.2.0/db_1/fl
                                                 ash_recovery_area
db_recovery_file_dest_size           big integer 2G

SQL> show parameter db_flashback_retention_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

b.启用flash database选项：
SQL> shutdown immediate;      
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount 
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size             104860528 bytes
Database Buffers           58720256 bytes
Redo Buffers                2912256 bytes
Database mounted.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
 
--可以看到，flashback还必须要归档的保证

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     17
Current log sequence           19
 
SQL> alter database archivelog;
Database altered.  
 
--9i还要设置Automatic archival，10g简化了。
 
SQL> alter database flashback on;
Database altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence           19
 
SQL> alter database open;
Database altered.

2.使用Flashback database功能
a.背景知识
启动Flashback Database之后，oracle定期将已发生变化的块写入闪回日志的日志文件中。这些日志不是由传统的Log Writer (LGWR) 过程写入，而是由一种称作Recovery Writer (RVWR)的新过程写入。这是Oracle10g的新增进程，如下：
# ps -ef|grep rvwr|grep -v grep
  oracle  6028     1   0 16:42:48 ?           0:00 ora_rvwr_sjh10g
 
与常规的重做日志（redo logs）不同，回闪日志既不需要由DBA创建，也不需要由他们维护；它们由Oracle Managed Files（OMF）自动在闪回恢复区域所指定的目录中创建。这些文件不会归档，所以，如果在该目录发生介质故障后就不可能再进行恢复。
# pwd
/oracle/product/10.2.0/db_1/flash_recovery_area/SJH10G/flashback

# ls
o1_mf_4wmvn7f0_.flb   --闪回区日志。
 
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
 
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
 
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
             1383302 2009-03-25 16:42:48
 
--通过以上SQL我们可以知道，可以前滚恢复到的最早的SCN与时间点是多少，如果没有确保flashback database，该视图将没有查询结果。
 
b.创建测试数据
SQL> select sysdate from dual;                   
SYSDATE
-------------------
2009-03-25 17:01:55
 
SQL> create table sjh0 as select * from dba_users;
Table created.
 
SQL> select count(*) from dba_users;
  COUNT(*)
----------
        22
 
SQL> select sysdate from dual;
SYSDATE
-------------------
2009-03-25 17:03:15
 
SQL> create table sjh1 as select * from dba_users;
Table created.
 
SQL> select sysdate from dual;   
SYSDATE
-------------------
2009-03-25 17:03:55
 
c.进行闪回操作
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size             104860528 bytes
Database Buffers           58720256 bytes
Redo Buffers                2912256 bytes
Database mounted.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
 
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
             1383302 2009-03-25 16:42:48
 
SQL> flashback database to timestamp to_timestamp('2009-03-25 17:01:55','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
 
SQL> alter database open read only;
Database altered.
 
SQL> desc sjh0;
ERROR:
ORA-04043: object sjh0 does not exist
 
--显然在2009-03-25 17:01:55这个时间点sjh0还没有创建。所以对象不存在。
 
SQL> alter database close;
Database altered.
 
SQL> flashback database to timestamp to_timestamp('2009-03-25 17:03:18','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
 
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed

SQL> alter database dismount;
Database altered.
 
SQL> startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size             104860528 bytes
Database Buffers           58720256 bytes
Redo Buffers                2912256 bytes

Database mounted.

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.
 
SQL> desc sjh0
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)
 
SQL> desc sjh1
ERROR:
ORA-04043: object sjh1 does not exist
 
--恢复的时间点选在sjh1创建之前。一旦resetlogs之后，将不能再flashback的resetlogs之前的时间点。
 
3.使用flashback drop 功能
a.背景知识：
Oracle10g提供了flashback drop的功能。而在以前的版本中，通常只能进行不完全恢复。
Oracle 10g的flashback drop功能，允许你从当前数据库中恢复一个被drop了的对象，在执行drop操作时，现在Oracle不是真正删除它，而是将该对象自动将放入回收站。对于一个对象的删除，其实仅仅就是简单的重命名操作。所谓的回收站，是一个虚拟的容器，用于存放所有被删除的对象。在回收站中，被删除的对象将占用创建时的同样的空间，你甚至还可以对已经删除的表查询，也可以利用flashback功能来恢复它， 这个就是flashback drop功能。回收站内的相关信息可以从recyclebin，user_recyclebin，dba_recyclebin等视图中获取，或者通过SQL*Plus的show recyclebin 命令查看。
 
b.测试
SQL> create table sjh0 as select * from dba_users;
Table created.
 
SQL> select count(*) from sjh0;
  COUNT(*)
----------
        22
 
SQL> drop table sjh0;
Table dropped.
 
SQL> show recyclebin;
--sysdba的drop操作不会被记录。
 
SQL> conn sjh/sjh
Connected.

SQL> create table sjh0 as select * from dba_users;
Table created.
 
SQL> select count(*) from sjh0;
  COUNT(*)
----------
        22
 
SQL> drop table sjh0;
Table dropped.
 
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
SJH0             BIN$ZfALsiRTGCLgRAAEdupMww==$0 TABLE        2009-03-25:18:34:34
 
SQL> flashback table sjh0 to before drop;
Flashback complete.
 
SQL> desc sjh0
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)
 
SQL> select count(*) from sjh0;
  COUNT(*)
----------
        22
 
SQL> show recyclebin;
 
--对象没有了，被恢复了。
 
SQL> drop table sjh0;
Table dropped.
 
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
SJH0             BIN$ZfALsiRbGCLgRAAEdupMww==$0 TABLE        2009-03-25:18:38:03
 
SQL> purge table sjh0;    --从回收站清除。
Table purged.
 
SQL> show recyclebin;
 
--没有对象了。
 
SQL> create table sjh0 as select * from dba_users;
Table created.
 
SQL> select count(*) from sjh0;
  COUNT(*)
----------
        22
 
SQL> drop table sjh0 purge;   --彻底删除一个表，不让进入回收站。
Table dropped.
 
SQL> show recyclebin;
 
--没有对象。
 
4.对DML操作的闪回查询
Oracle10g对于闪回查询进行了增强，支持更简单的SQL操作，允许对误删除、误更新等DML操作进行闪回。关于9i的闪回查询请参考我的另一篇blog文章：
http://blog.chinaunix.net/u/10516/showart.php?id=482946
 
a.测试
SQL> create table sjh0 as select * from dba_users;
Table created.
 
SQL> select count(*) from dba_users;
  COUNT(*)
----------
        22
 
SQL> delete from sjh0;
22 rows deleted.
 
SQL> select count(*) from sjh0;
  COUNT(*)
----------
         0
 
SQL> select dbms_flashback.get_system_change_number from dual;
select dbms_flashback.get_system_change_number from dual
       *
ERROR at line 1:
ORA-00904: : invalid identifier

SQL> conn sys/sys as sysdba
Connected.

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1388195
 
SQL> select count(*) from sjh0 as of scn 1388190;
select count(*) from sjh0 as of scn 1388190
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
 

SQL> conn sjh/sjh
Connected.
 
SQL> select count(*) from sjh0 as of scn 1388193;
  COUNT(*)
----------
         0
 
SQL> select count(*) from sjh0 as of scn 1388190;
  COUNT(*)
----------
        22
 
--在scn 1388190的时刻数据都还在。
 
SQL> insert into sjh0 select * from sjh0 as of scn 1388190;
22 rows created.
 
SQL> select count(*) from sjh0;
  COUNT(*)
----------
        22
 
5.使用Flashback version Query
闪回版本查询的测试：
SQL> create table sjh0 as select username,user_id from dba_users;
Table created.
 
SQL> desc sjh0;         
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 
SQL> select * from sjh0;
USERNAME                          USER_ID
------------------------------ ----------
MGMT_VIEW                              53
SYS                                     0
SYSTEM                                  5
DBSNMP                                 24
SYSMAN                                 51
SJH                                    55
OUTLN                                  11
MDSYS                                  46
ORDSYS                                 43
CTXSYS                                 36
ANONYMOUS                              39
USERNAME                          USER_ID
------------------------------ ----------
EXFSYS                                 34
DMSYS                                  35
WMSYS                                  25
XDB                                    38
ORDPLUGINS                             44
SI_INFORMTN_SCHEMA                     45
OLAPSYS                                47
MDDATA                                 50
DIP                                    19
SCOTT                                  54
TSMSYS                                 21
22 rows selected.
 
--以下执行一些DML语句：
 
SQL> delete from sjh0 where username='SYS';
1 row deleted.
 
SQL> COMMIT;
Commit complete.
 
SQL> DELETE FROM sjh0 where username='SYSTEM';
1 row deleted.
 
SQL> COMMIT;
Commit complete.
 
SQL> UPDATE sjh0 set user_id=0 where username='SJH';
1 row updated.
 
SQL> commit;
Commit complete.
 
SQL> update sjh0 set user_id=1 where username='SCOTT';
1 row updated.
 
SQL> commit;
Commit complete.
 
SQL> select * from sjh0;                                        
USERNAME                          USER_ID
------------------------------ ----------
MGMT_VIEW                              53
DBSNMP                                 24
SYSMAN                                 51
SJH                                     0
OUTLN                                  11
MDSYS                                  46
ORDSYS                                 43
CTXSYS                                 36
ANONYMOUS                              39
EXFSYS                                 34
DMSYS                                  35
USERNAME                          USER_ID
------------------------------ ----------
WMSYS                                  25
XDB                                    38
ORDPLUGINS                             44
SI_INFORMTN_SCHEMA                     45
OLAPSYS                                47
MDDATA                                 50
DIP                                    19
SCOTT                                   1
TSMSYS                                 21
20 rows selected.
 
--执行Flashback version Query
 
SQL> select versions_starttime, versions_endtime, versions_xid,
  2  versions_operation, username,user_id from sjh0 versions between timestamp minvalue and maxvalue;
VERSIONS_STARTTIME                                VERSIONS_ENDTIME                                  VERSIONS_XID     VERSIONS_OPERATION USERNAME                          USER_ID
------------------------------------------------- ------------------------------------------------- ---------------- ------------------ ------------------------------ ----------
26-MAR-09 01.58.03 PM                                                                               02001B0010030000 U                  SCOTT                                   1
26-MAR-09 01.57.21 PM                                                                               01002600CF020000 U                  SJH                                     0
26-MAR-09 01.56.12 PM                                                                               07000F00C5020000 D                  SYSTEM                                  5
26-MAR-09 01.55.48 PM                                                                               0A001700C0020000 D                  SYS                                     0
                                                                                                                                        MGMT_VIEW                              53
                                                  26-MAR-09 01.55.48 PM                                                                 SYS                                     0
                                                  26-MAR-09 01.56.12 PM   
 
--为了不显得格式凌乱，后面的记录省略。。。
--下面我们利用VERSIONS_XID来查询undo语句，需要用到sys/system用户。最后利用这些undo来撤销不同版本的事物。
 
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='0A001700C0020000';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "SJH"."SJH0"("USERNAME","USER_ID") values ('SYS','0');

SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='07000F00C5020000';         
UNDO_SQL
--------------------------------------------------------------------------------
insert into "SJH"."SJH0"("USERNAME","USER_ID") values ('SYSTEM','5');

SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='01002600CF020000';
UNDO_SQL
--------------------------------------------------------------------------------
update "SJH"."SJH0" set "USER_ID" = '55' where ROWID = 'AAAM3JAAEAAAABMAAF';

SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='02001B0010030000';
UNDO_SQL
--------------------------------------------------------------------------------
update "SJH"."SJH0" set "USER_ID" = '54' where ROWID = 'AAAM3JAAEAAAABMAAU';

SQL> insert into "SJH"."SJH0"("USERNAME","USER_ID") values ('SYS','0');
1 row created.
SQL> insert into "SJH"."SJH0"("USERNAME","USER_ID") values ('SYSTEM','5');
1 row created.
SQL> update "SJH"."SJH0" set "USER_ID" = '55' where ROWID = 'AAAM3JAAEAAAABMAAF';
1 row updated.
SQL> update "SJH"."SJH0" set "USER_ID" = '54' where ROWID = 'AAAM3JAAEAAAABMAAU';
1 row updated.
 
SQL> commit;
Commit complete.
 
SQL> select * from sjh0;
USERNAME                          USER_ID
------------------------------ ----------
MGMT_VIEW                              53
DBSNMP                                 24
SYSMAN                                 51
SJH                                    55
OUTLN                                  11
MDSYS                                  46
ORDSYS                                 43
CTXSYS                                 36
ANONYMOUS                              39
EXFSYS                                 34
DMSYS                                  35
USERNAME                          USER_ID
------------------------------ ----------
WMSYS                                  25
XDB                                    38
ORDPLUGINS                             44
SI_INFORMTN_SCHEMA                     45
OLAPSYS                                47
MDDATA                                 50
DIP                                    19
SCOTT                                  54
TSMSYS                                 21
SYS                                     0
SYSTEM                                  5
22 rows selected.
 
--完成事务的撤销。
--Flashback transaction query需要用到FLASHBACK_TRANSACTION_QUERY视图。这个视图来源于x$ktuqqry表且xid字段上不存在索引，所以走的是全表扫描。x$ktuqqry的查询非常耗时，所以请注意评估你的恢复成本。
 
 
 
 
 
 
上一篇：解析PVID及案例分析
下一篇：如何将数据导入不同的表空间