使用闪回数据库flashback database 恢复删除的用户

SQL> select flashback_on from v$database;

FLASHBACK_ON

——————

YES

SQL> show parameter flash;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_flash_cache_file                  string

db_flash_cache_size                  big integer 0

db_flashback_retention_target        integer     1440

SQL> create user flashtest identified by flashtest default tablespace testdb;

User created.

SQL> grant connect,resource,dba to flashtest;

Grant succeeded.

SQL> conn flashtest/flashtest;

Connected.

SQL> create table emp(ename varchar2(20),city varchar2(20));

Table created.

SQL> insert into emp values(‘azar’,’riyadh’);

1 row created.

SQL> insert into emp values(‘kareem’,’dubai’);

1 row created.

SQL> insert into emp values(‘azmi’,’chennai’);

1 row created.

SQL> insert into emp values(‘idress’,’riyadh’);

1 row created.

SQL> insert into emp values(‘ajmal’,’chennai’);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP

—————————————————————————

18-DEC-10 12.14.21.137000 PM +03:00

SQL> conn / as sysdba

Connected.

SQL> drop user flashtest cascade;

User dropped.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1375792 bytes

Variable Size             335544784 bytes

Database Buffers          192937984 bytes

Redo Buffers                5804032 bytes

Database mounted.

SQL>  flashback database to timestamp to_date(’18-DEC-10 12.14.22′,’DD-MM-YY HH24:MI:SS’);

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> conn flashtest/flashtest;

Connected.

SQL> select * from emp;

ENAME                CITY

——————– ——————–

azar                 riyadh

kareem               dubai

azmi                 chennai

idress               riyadh

ajmal                chennai

C:\Users\mazar>exp flashtest/flashtest file=d:\backup\emp.dmp log=d:\backup\emp.log direct=y consistent=y statistics=’none’

Export: Release 11.2.0.1.0 – Production on Sat Dec 18 12:17:52 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set

About to export specified users …

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user FLASHTEST

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user FLASHTEST

About to export FLASHTEST’s objects …

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export FLASHTEST’s tables via Direct Path …

. . exporting table                            EMP          5 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1375792 bytes

Variable Size             335544784 bytes

Database Buffers          192937984 bytes

Redo Buffers                5804032 bytes

Database mounted.

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> conn flashtest/flashtest;

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn / as sysdba

Connected.

SQL> create user flashtest identified by flashtest default tablespace testdb;

User created.

SQL> grant connect,resource,dba to flashtest;

Grant succeeded.

C:\Users\mazar>imp flashtest/flashtest file=d:\backup\emp.dmp log=d:\backup\emp.log full=y

Import: Release 11.2.0.1.0 – Production on Sat Dec 18 12:20:06 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via direct path

import done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set

. importing FLASHTEST’s objects into FLASHTEST

. . importing table                          “EMP”          5 rows imported

Import terminated successfully without warnings.

C:\Users\mazar>

SQL> conn flashtest/flashtest;

Connected.

SQL> select * from emp;

ENAME                CITY

——————– ——————–

azar                 riyadh

kareem               dubai

azmi                 chennai

idress               riyadh

ajmal                chennai

SQL>