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

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操作