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

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

 

 

这里我将展示如何使用闪回数据库恢复删除的用户不会有数据丢失

环境:

归档模式

Flashback 开启

删除用户的时间应该在参数db_flashback_retention_target 范围内且归档日志是有效的

用户下的任何对象都不能有任何NOLOGGING操作

第一步:检查闪回模式和时间限制

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.

第四步:启动到mount模式,基于时间闪回数据库

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.

第七步:关机,启动到mountrecover数据库

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>

Ok,现在我成功的恢复被删除的用户没有丢失任何数据

关注刘相兵的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

Speak Your Mind

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569