使用Oracle 闪回数据库恢复被删除的用户drop user cascade

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

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

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

 

这篇文章适合任何水平的DBA

前提:

数据库处于归档模式

数据库开启闪回模式

被删除用户的时间应该在db_flash_retention_target 范围内并且归档日志是有效的

被删除的用户下的任何对象不能做任何NOLOGGING操作

这个测试包括以下这些步骤

1:确定有足够的闪回日志能够闪回数据库

2:人为的模仿错误删除用户

3:闪回数据库

4:打开闪回数据库到只读模式

5:检查用户和数据是否有效

6:将被删除的方案下所有数据导出

7:做完全恢复到目前的状态

8:导入用户去解决人为错误删除用户

1. 确认数据库处于闪回模式开启状态

SQL> select flashback_on from v$database;

FLASHBACK_ON

——————

YES

SQL> show parameter flash

NAME                                          VALUE

——————————-      ———–

db_flashback_retention_target          1440

SQL> select oldest_flashback_scn,oldest_flashback_time from $flashback_database_log ;

OLDEST_FLASHBACK_SCN   OLDEST_FLASHBACK_TIM

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

24300236                                       29-MAY-2009 12:56:13

2. 创建一些表到已存在的用户下被删除来模拟情景

SQL> conn flashback_test/flashback

Connected.

SQL> create table flashback_testing ( col1 varchar2(20));

Table created.

SQL> insert into flashback_testing values ( ‘flashback testing’);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

Session altered.

SQL> select sysdate from dual;

SYSDATE

——————–

29-MAY-2009 14:32:50

SQL> conn / as sysdba

Connected.

SQL> drop user falshback_test CASCADE;

User dropped.

3.为了恢复用户,使用闪回数据库恢复到用户被删除前的时间点

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 289406976 bytes

Fixed Size 1290208 bytes

Variable Size 142606368 bytes

Database Buffers 142606336 bytes

Redo Buffers 2904064 bytes

Database mounted.

SQL> flashback database to timestamp to_date(’29-MAY-2009 14:30:00′,’DD-MON-YYYY HH24:MI:SS’);

Flashback complete.

4.打开数据库到只读模式

SQL> alter database open read only;

Database altered.

5. 检查用户和数据是否正确

SQL> conn FALSHBACK_TEST/flashback

Connected.

SQL> select * from flashback_testing;

COL1

——————–

flashback testing

SQL> exit

6. 导出用户

C:\>exp owner=falshback_test file=E:\temp\exp1.dmp

Export: Release 10.2.0.4.0 – Production on Fri May 29 20:23:40 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 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 FALSHBACK_TEST

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user FALSHBACK_TEST

About to export FALSHBACK_TEST’s objects …

. about to export FALSHBACK_TEST’s tables via Conventional Path …

. . exporting table              FLASHBACK_TESTING          1 rows exported

. exporting synonyms

. exporting views

….

. exporting statistics

Export terminated successfully without warnings.

7. 关闭数据库并恢复数据库到当前状态

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 289406976 bytes

Fixed Size 1290208 bytes

Variable Size 142606368 bytes

Database Buffers 142606336 bytes

Redo Buffers 2904064 bytes

Database mounted.

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

现在数据库处于没有被删除的用户的状态,但是我们有完整的被删除用户的导出文件 

8.创建被删除的用户然后导入数据从导出文件中

SQL> create user flashback_test identified by flashback  default tablespace users quota unlimited on users;

User created.

SQL> — Grant the necessary priviledge to that user

C:\>imp full=y file=E:\temp\exp1.dmp

Import: Release 10.2.0.4.0 – Production on Fri May 29 20:26:14 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

. importing SYS’s objects into SYS

. importing FLASHBACK_TEST’s objects into FALSHBACK_TEST

. . importing table “FLASHBACK_TESTING” 1 rows imported

Import terminated successfully without warnings.

现在数据库没有数据丢失

限制:

1. 如果如何闪回日志或者归档日志丢失,将无法接下来的工作

2.如果有NOLOGGING操作,那么这些NOLOGGING事务将软损坏,因此,避免时间点或者SCN号闪回数据库取决于NOLOGGING操作

关注刘相兵的新浪微博

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

Speak Your Mind

沪公网安备 31010802001379号

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