oracle 根据ts闪回表,Oracle 11g闪回flashback

最新推荐文章于 2021-10-31 15:17:42 发布

坚强努力地活下去

最新推荐文章于 2021-10-31 15:17:42 发布

阅读量118

点赞数

文章标签： oracle 根据ts闪回表

Oracle 11g闪回flashback

oracle9i的闪回：

闪回查询从9i引入，可以按照时间点或者SCN向前查

询，获取修改前的数据。

闪回查询依赖于回滚段中存储的数据前镜像，通过设置

undo_retention参数设置前镜像的保留时间。

查询的语法：

select … as of scn | timestamp

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as hr@JIAGULUN

SQL> select * from student;

ID NAME AGE

----------- -------------------- -----------

1 zhangsan 21

2 lisi 22

3 wangwu 23

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24:

------------------------------

20150124 17:58:58

SQL>

SQL> update student s set s.name='mazi' where s.id = 1;

1 row updated

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24:

------------------------------

20150124 18:02:56

SQL> delete from student s where s.name = 'lisi';

1 row deleted

SQL> select * from student;

ID NAME AGE

----------- -------------------- -----------

1 mazi 21

3 wangwu 23

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24:

------------------------------

20150124 18:04:00

SQL> delete from student;

2 rows deleted

SQL> select * from student;

ID NAME AGE

----------- -------------------- -----------

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24:

------------------------------

20150124 18:04:26

SQL> select * from student as of scn 3099731;

ID NAME AGE

----------- -------------------- -----------

1 zhangsan 21

2 lisi 22

3 wangwu 23

SQL> select * from student;

ID NAME AGE

----------- -------------------- -----------

SQL> commit;

Commit complete

SQL> insert into student values(1,'zhangsan',23);

1 row inserted

SQL> commit;

Commit complete

SQL>

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24:

------------------------------

20150124 19:36:30

SQL>

SQL> delete from student;

1 row deleted

SQL> commit;

Commit complete

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24:

------------------------------

20150124 19:36:51

SQL> select * from student as of timestamp to_date('20150124 19:36:30','yyyymmdd hh24:mi:ss');

ID NAME AGE

----------- -------------------- -----------

1 zhangsan 23

SQL> select * from student as of timestamp to_date('20150124 18:04:26','yyyymmdd hh24:mi:ss');

ID NAME AGE

----------- -------------------- -----------

1 zhangsan 21

2 lisi 22

3 wangwu 23

SQL>

oracle10g的闪回版本查询：

对于9i的闪回查询进行增强，Oracle10g提供了闪回版本

查询，因为9i的闪回查询仅仅能够得到过去某个时间点上的

数据，但是无法反映出一段时间内数据表中数据的变化，

10g的闪回版本查询可以对时间段内数据表的不同版本进行

查询。

闪回版本查询的语法：

select … from … versions between

其中，select后面可以选择伪列，来获得事务的开始、结

束时间、SCN号、ID号等。

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24:

------------------------------

20150124 19:40:11

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------使用上面的系统包必须赋予权限：grant execute on dbms_flashback to hr;

3115836

SQL>

SQL> insert into student values(1,'zhangsan',23);

1 row inserted

SQL> commit;

Commit complete

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24:

------------------------------

20150124 19:51:36

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

3115938

SQL> update student s set s.name='wangwu' where s.id = 1;

1 row updated

SQL> commit;

Commit complete

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

3115954

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24:

------------------------------

20150124 19:52:26

SQL> insert into student values (2,'mazi',24);

1 row inserted

SQL> commit;

Commit complete

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24:

------------------------------

20150124 19:52:58

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

3116062

SQL>

SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,id,name,age from student

2 versions between scn minvalue and maxvalue;

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION ID NAME AGE

-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- ------------------ ----------- -------------------- -----------

24-1月 -15 07.52.54 下午 0B00080005010000 I 2 mazi 24

24-1月 -15 07.52.15 下午 13000100FE000000 U 1 wangwu 23

24-1月 -15 07.51.29 下午 24-1月 -15 07.52.15 下午 12001F00FF000000 I 1 zhangsan 23

SQL>

SQL> select to_char( versions_starttime,'yyyymmdd hh24:mi:ss') ,to_char( versions_endtime,'yyyymmdd hh24:mi:ss'),versions_xid,versions_operation,id,name,age from student

2 versions between scn minvalue and maxvalue;

TO_CHAR(VERSIONS_STARTTIME,'YY TO_CHAR(VERSIONS_ENDTIME,'YYYY VERSIONS_XID VERSIONS_OPERATION ID NAME AGE

------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- -----------

20150124 19:52:54 0B00080005010000 I 2 mazi 24

20150124 19:52:15 13000100FE000000 U 1 wangwu 23

20150124 19:51:29 20150124 19:52:15 12001F00FF000000 I 1 zhangsan 23

SQL>

SQL> select to_char( versions_starttime,'yyyymmdd hh24:mi:ss') ,to_char( versions_endtime,'yyyymmdd hh24:mi:ss'),versions_xid,versions_operation,id,name,age from student

2 versions between timestamp minvalue and maxvalue;

TO_CHAR(VERSIONS_STARTTIME,'YY TO_CHAR(VERSIONS_ENDTIME,'YYYY VERSIONS_XID VERSIONS_OPERATION ID NAME AGE

------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- -----------

20150124 19:52:54 0B00080005010000 I 2 mazi 24

20150124 19:52:15 13000100FE000000 U 1 wangwu 23

20150124 19:51:29 20150124 19:52:15 12001F00FF000000 I 1 zhangsan 23

SQL>

过一段时间你会发现查询的结果变少了：

SQL> select to_char( versions_starttime,'yyyymmdd hh24:mi:ss') ,to_char( versions_endtime,'yyyymmdd hh24:mi:ss'),versions_xid,versions_operation,id,name,age from student

2 versions between timestamp minvalue and maxvalue;

TO_CHAR(VERSIONS_STARTTIME,'YY TO_CHAR(VERSIONS_ENDTIME,'YYYY VERSIONS_XID VERSIONS_OPERATION ID NAME AGE

------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- -----------

2 mazi 24

1 wangwu 23

SQL>

这是因为undo_retention这个值设置的时间到期了。

oracle10g的闪回事务查询：

Oracle10g可以进行基于闪回版本查询的恢复，就是闪回事务查询。

从flashback_transaction_query中查询引起数据变化的

事务，和撤销事务的SQL语句，就是查询 operation和undo_sql 列。

SQL> select xid,start_timestamp,operation,undo_sql from flashback_transaction_query where table_name='STUDENT';

XID START_TIMESTAMP OPERATION UNDO_SQL

---------------- --------------- -------------------------------- --------------------------------------------------------------------------------

0F00020002010000 24-1月-15 20:32: UNKNOWN

0F00040002010000 24-1月-15 20:32: UNKNOWN

13000100FE000000 24-1月-15 19:52: UNKNOWN

1400200004010000 24-1月-15 20:32: UNKNOWN

如上图所示的，但是这里查不出来奇怪了。。。。

通过undo_sql来进行回滚事务(就是再做次反向操作)

oracle10g的闪回表：

Oracle10g的闪回表是把表里的数据回退到以前的某个时

刻或者SCN上。

特点：可以在线操作；自动恢复相关的属性，包括索引、

触发器等。

前提：对表启用行迁移。

语法：

flashback table to timestamp | scn

Connected as hr@JIAGULUN

SQL> select * from student;

ID NAME AGE

----------- -------------------- -----------

3 zhangsan 21

SQL> alter table student enable row movement;

Table altered

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

3143334

SQL> delete from student where id in(1);

0 rows deleted

SQL> delete from student where id in(3);

1 row deleted

SQL> commit;

Commit complete

SQL> flashback table student to scn 3143334;

Done

SQL> select * from student;

ID NAME AGE

----------- -------------------- -----------

3 zhangsan 21

SQL>

注意：sys的表不能闪回。

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as sys@JIAGULUN AS SYSDBA

SQL> select * from student;

ID NAME AGE

----------- -------------------- -----------

1 zhangsan 20

2 lisi 21

3 wangwu 22

SQL> alter table student enable row movement;

Table altered

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

3142994

SQL> delete from student where id in(1,2);

2 rows deleted

SQL> commit;

Commit complete

SQL> select * from student;

ID NAME AGE

----------- -------------------- -----------

3 wangwu 22

SQL> flashback table student to scn 3142994;

flashback table student to scn 3142994

ORA-08185: 用户 SYS 不支持闪回

SQL> show user;

User is "SYS"

SQL>

oracle10g的闪回删除：

Oracle10g的闪回删除：可以恢复一个被drop的对象，因

为进行drop时，Oracle先把它放到回收站中。(回收站和操作系统的回收站很相似)

回收站内的信息：show recyclebin

闪回删除：flashback table to before drop;

彻底删除：drop table purge;

清空回收站: purge recyclebin;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as hr@JIAGULUN

SQL> purge recyclebin;

Done

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

ADDRESS TABLE

SQL> select * from address;

ID NAME

----------- --------------------

1 jiangxi

2 hunan

SQL> show recyclebin;

SQL> select * from user_recyclebin;--回收站并没有任何垃圾

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

SQL> drop table address;--删除表

Table dropped

SQL> show recyclebin;

SQL> select * from user_recyclebin;--会发现回收站中出现了——表，索引垃圾

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

BIN$DXVcPQqfU6HgUKjAA1tAfw==$0 ADDRESS DROP TABLE USERS 2015-01-22:06:10:58 2015-01-24:22:49:08 3145534 YES YES 77236 77236 77236 8

BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 PK_ADDRESS DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:49:08 3145530 NO YES 77236 77236 77239 8

BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 INDEX_ADDRESS_NAME DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:49:08 3145525 NO YES 77236 77236 77240 8

SQL> select * from tab;--刚刚存在address表不见了，但是多了一张下面的表

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

BIN$DXVcPQqfU6HgUKjAA1tAfw==$0 TABLE

SQL> flashback table student to before drop;

flashback table student to before drop

ORA-38305: 对象不在回收站中

SQL> flashback table address to before drop;--对回收站的表进行闪回

Done

SQL> select * from user_recyclebin;--回收站中的表被闪回了

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

SQL> select * from tab;--表被闪回了

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

ADDRESS TABLE

SQL> drop table address;

Table dropped

SQL> create table address(id number(10));

Table created

SQL> insert into address values(1);

1 row inserted

SQL> commit;

Commit complete

SQL> drop table address;

Table dropped

SQL> select * from user_recyclebin;--回收站中有两个address表

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

BIN$DXVcPQqkU6HgUKjAA1tAfw==$0 ADDRESS DROP TABLE USERS 2015-01-24:22:51:52 2015-01-24:22:52:19 3145976 YES YES 77424 77424 77424 8

BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS DROP TABLE USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145840 YES YES 77236 77236 77236 8

BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145836 NO YES 77236 77236 77239 8

BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145831 NO YES 77236 77236 77240 8

SQL> flashback table address to before drop;--闪回表

Done

SQL> select * from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS DROP TABLE USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145840 YES YES 77236 77236 77236 8

BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145836 NO YES 77236 77236 77239 8

BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145831 NO YES 77236 77236 77240 8

SQL> select * from address;--被闪回的表示最晚送到回收站的那张表

-----------

SQL>

SQL> drop table address purge;--删除表并不送入到回收站中

Table dropped

SQL> select * from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS DROP TABLE USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145840 YES YES 77236 77236 77236 8

BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145836 NO YES 77236 77236 77239 8

BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145831 NO YES 77236 77236 77240 8

SQL>

SQL> flashback table address to before drop;--闪回原来存在于回收站的表

Done

SQL> select * from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

SQL> select * from address;

ID NAME

----------- --------------------

1 jiangxi

2 hunan

SQL>

通过参数recyclebin来启用、禁用回收站。

SQL> show parameter recycle;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

buffer_pool_recycle string

db_recycle_cache_size big integer 0

recyclebin string on

SQL>

SQL> select * from address;

ID NAME

----------- --------------------

1 jiangxi

2 hunan

SQL> alter session set recyclebin = off;--关闭回收站

Session altered

SQL> drop table address;

Table dropped

SQL> select * from user_recyclebin;--回收站为空的

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

SQL>

闪回数据库：

如果数据库出现逻辑错误，无法采用闪回表的方式进行恢

复，或者数据库的结构发生了改变，可以通过闪回数据库的

方式把整个数据库回退到出错前的时间点上。步骤：

1，配置数据库为归档模式

2，配置闪回恢复区

3，配置闪回保留时间

4，启用数据库闪回 alter database flashback on;

5，进行闪回数据库

flashback database to timestamp |scn

SQL> show user

USER is "SYS"

SQL> archive log list;--查看是否是归档模式

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archive/archive02/

Oldest online log sequence 362

Next log sequence to archive 365

Current log sequence 365

SQL> alter system set log_archive_start = false scope =spfile;--设置为不是自动归档(在9i后失效了，归档模式就是自动的)

System altered.

SQL> shutdown immediate--关闭数据库

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount--打开数据库到mount状态

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 400846848 bytes

Fixed Size 2213776 bytes

Variable Size 276826224 bytes

Database Buffers 117440512 bytes

Redo Buffers 4366336 bytes

Database mounted.

SQL> alter database noarchivelog--修改为非归档模式

2 ;

Database altered.

SQL> alter database open--打开数据库

2 ;

------------------------------------前面是修改数据库为非归档模式------------------

Database altered.

SQL> archive log list;--查看是否归档

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /u01/app/oracle/archive/archive02/

Oldest online log sequence 362

Current log sequence 365

SQL> clear

SQL> !clear

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /u01/app/oracle/archive/archive02/

Oldest online log sequence 362

Current log sequence 365

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 400846848 bytes

Fixed Size 2213776 bytes

Variable Size 276826224 bytes

Database Buffers 117440512 bytes

Redo Buffers 4366336 bytes

Database mounted.

SQL> alter database archivelog;--修改为归档模式

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archive/archive02/--归档区，默认是在闪回区

Oldest online log sequence 362

Next log sequence to archive 365

Current log sequence 365

SQL> show parameter db_rec

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /u01/app/oracle/flash_recovery

--闪回区配置 _area

db_recovery_file_dest_size big integer 3882M

db_recycle_cache_size big integer 0

SQL> show parameter db_flashback_retent

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target integer 1440--闪回保留时间默认是24小时，也就是1440分钟

SQL> !ls /u01/app/oracle/flash_recovery_area

jiagulun JIAGULUN——里面是空的

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 400846848 bytes

Fixed Size 2213776 bytes

Variable Size 276826224 bytes

Database Buffers 117440512 bytes

Redo Buffers 4366336 bytes

Database mounted.

SQL> alter database flashback on;--启动数据库闪回

Database altered.

SQL> !ls /u01/app/oracle/flash_recovery_area

jiagulun JIAGULUN

[oracle@localhost flashback]$ pwd

/u01/app/oracle/flash_recovery_area/JIAGULUN/flashback

[oracle@localhost JIAGULUN]$ cd flashback/

[oracle@localhost flashback]$ ls

o1_mf_bd97qnhk_.flb o1_mf_bd987hyb_.flb

SQL> alter database open;

Database altered.

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'

-----------------

20150124 23:47:01

SQL> select name from v$tablespace;

NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEMP

EXAMPLE

TESTTS

TEMP2

TEMP3

UNDOTBS2

TEST_TRAN_TS

11 rows selected.

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

Tablespace created.

SQL> select name from v$tablespace;

NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEMP

EXAMPLE

TESTTS

TEMP2

TEMP3

UNDOTBS2

TEST_TRAN_TS

NAME

------------------------------

TEST_FLASHBACK_DATABASE

12 rows selected.

SQL>

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 400846848 bytes

Fixed Size 2213776 bytes

Variable Size 276826224 bytes

Database Buffers 117440512 bytes

Redo Buffers 4366336 bytes

Database mounted.

SQL> flashback database to timestamp to_date('20150124 23:47:01','yyyymmdd hh24:mi:ss');

Flashback complete.

SQL> startup force

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 400846848 bytes

Fixed Size 2213776 bytes

Variable Size 276826224 bytes

Database Buffers 117440512 bytes

Redo Buffers 4366336 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;--为什么会要求open为resetlog？

因为：数据库被闪回了，那么从闪回前在到闪回点的重做日志对于数据库来说已经是无效的了。所以要求重设日志。

SQL> alter database open read only;--有两种模式：read only、read write；之所以为这种状态，因为数据库在闪回后，就不是现在的数据库了

数据库的状态发生了改变，不希望其他用户登录进来防止出现问题

Database altered.

SQL> select name from v$tablespace;

NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEMP

EXAMPLE

TESTTS

TEMP2

TEMP3

UNDOTBS2

TEST_TRAN_TS

TEST_FLASHBACK_DATABASE--已经不存在了，被闪回了

11 rows selected.

SQL>

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 400846848 bytes

Fixed Size 2213776 bytes

Variable Size 276826224 bytes

Database Buffers 117440512 bytes

Redo Buffers 4366336 bytes

Database mounted.

SQL> alter database archivelog

2 ;

Database altered.

SQL>

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archive/archive02/

Oldest online log sequence 1

Next log sequence to archive 1

Current log sequence 1

SQL> alter database noarchivelog;--归档变成非归档

alter database noarchivelog

ERROR at line 1:

ORA-38774: cannot disable media recovery - flashback database is enabled

--表示在flashback状态不能变成非归档

SQL> alter database flashback off;--关闭flashback

Database altered.

SQL> alter database noarchivelog;--再进行归档到非归档的切换

Database altered.

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /u01/app/oracle/archive/archive02/

Oldest online log sequence 1

Current log sequence 1

SQL> alter database open;

Database altered.

SQL>

oracle 根据ts闪回表,Oracle 11g闪回flashback

“相关推荐”对你有帮助么？