1. 数据库开启flashback

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

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

NO

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1319473152 bytes

Fixed Size 2213096 bytes

Variable Size 486542104 bytes

Database Buffers 822083584 bytes

Redo Buffers 8634368 bytes

Database mounted.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

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

YES

2. 创建表t1，并记录系统时间

创建表t2，truncate表t1

SQL> create user woods identified by woods;

User created.

SQL> grant connect,resource to woods;

Grant succeeded.

SQL> create table woods.t1 as select * from v$datafile;

Table created.

SQL> select count(*) from woods.t1;

COUNT(*)

----------

4

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE

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

2016-08-18 00:35:34

SQL> create table woods.t2 as select * from v$logfile;

Table created.

SQL> select count(*) from woods.t2;

COUNT(*)

----------

3

SQL> truncate table woods.t1;

Table truncated.

SQL> select count(*) from woods.t1;

COUNT(*)

----------

0

3. 备份数据库

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

backup as compressed backupset filesperset 5 format '/u01/oracle/backup/full_%s_%p_%t' database;

sql 'alter system archive log current';

backup as compressed backupset filesperset 5 format '/u01/oracle/backup/arc_%s_%p_%t' archivelog all not backed up 1 times;

backup current controlfile format '/u01/oracle/backup/ctl_%s_%p_%t';

backup spfile format '/u01/oracle/backup/spf_%s_%p_%t';

}

4. 再创建一张测试表t3

SQL> create table woods.t3 as select * from dba_users;

Table created.

SQL> select count(*) from woods.t3;

COUNT(*)

----------

31

5. 闪回数据库到truncate t1 之前

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1319473152 bytes

Fixed Size 2213096 bytes

Variable Size 486542104 bytes

Database Buffers 822083584 bytes

Redo Buffers 8634368 bytes

Database mounted.

SQL> flashback database to timestamp to_timestamp('2016-08-18 00:35:34','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from woods.t1;

COUNT(*)

----------

4

SQL> select count(*) from woods.t2; select count(*) from woods.t1;

select count(*) from woods.t2

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select count(*) from woods.t3;

select count(*) from woods.t3

*

ERROR at line 1:

ORA-00942: table or view does not exist

6. 把表t1导出

[oracle@localhost ~]$ expdp \' / as sysdba \' dumpfile=t1.dmp logfile=t1.log directory=backup tables=woods.t1

7. 把数据库还原回去

RMAN> restore controlfile from '/u01/oracle/backup/ctl_24_1_920162495';

RMAN> alter database mount;

RMAN> run

2> {

3> allocate channel c1 type disk;

4> allocate channel c2 type disk;

5> allocate channel c3 type disk;

6> restore database;

7> recover database;

8> }

RMAN> alter database open resetlogs;

SQL> select count(*) from woods.t1;

COUNT(*)

----------

0

SQL> select count(*) from woods.t2;

COUNT(*)

----------

3

SQL> select count(*) from woods.t3;

COUNT(*)

----------

31

8. 把表t1导入

SQL> drop table woods.t1;

Table dropped.

[oracle@localhost ~]$ impdp \' / as sysdba \' dumpfile=t1.dmp logfile=t1_imp.log directory=backup tables=woods.t1

SQL> select count(*) from woods.t1;

COUNT(*)

----------

4

SQL> select count(*) from woods.t2;

COUNT(*)

----------

3

SQL> select count(*) from woods.t3;

COUNT(*)

----------

31

ITPub博客

Flashback Database简单一列——truncate数据恢复

最新文章