DBA Hub

📋Steps in this guide1/3

How To Recover A Dropped User Using Flashback in Oracle.

Using flashback method, we can restore an accidentally dropped users in oracle.

oracle configurationintermediate
by OracleDba
13 views
1

Overview

Using flashback method, we can restore an accidentally dropped users in oracle. Basically, we will flashback the database to past, when the user was available, Then take an export dump of the schema, and restore the database to same current state. Once database is up, we can import the dump. PREREQUISITE: - Database Must be in Archive log mode - Flashback must be enabled for the database. - all the flashback log and archives should be available, from the time, the user is dropped - Make sure flashback and archive mode is enable.
2

Section 2

- Lets drop a user, to test the scenarios: - flashback the database to past, when the user was available.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
SQL> select flashback_on,log_mode from v$database;

FLASHBACK_ON LOG_MODE
------------------ ------------
YES ARCHIVELOG

SQL> select table_name from dba_tables where owner='TESTUSER';

TABLE_NAME
----------------------------------------------------------------------------
TABLE1
TABLE2


SQL> drop user testuser cascade;

User dropped.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 SQL>
 SQL>
 SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.1107E+10 bytes
Fixed Size 7644464 bytes
Variable Size 9294584528 bytes
Database Buffers 1711276032 bytes
Redo Buffers 93011968 bytes
Database mounted.

 SQL> flashback database to timestamp to_date('20-JAN-2023 04:47:14','DD-MON-YYYY HH24:MI:SS');

Flashback complete.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> select table_name from dba_tables where owner='TESTUSER';

TABLE_NAME
--------------------------------------------------------------------------------
TABLE1
TABLE2
3

Section 3

- Take export backup of the schema TESTUSER: - Now restore the database to current stage: - Now create an empty user and import the dump.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
# exp owner=TESTUSER file=testuser.dmp

Export: Release 12.1.0.2.0 - Production on Tue Jan 20 05:17:45 2023

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TESTUSER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TESTUSER
About to export TESTUSER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TESTUSER's tables via Conventional Path ...
. . exporting table TEST1 86452 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table TEST2 35 rows exported
EXP-00091: Exporting questionable statistics.
. 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 with warnings.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.1107E+10 bytes
Fixed Size 7644464 bytes
Variable Size 9294584528 bytes
Database Buffers 1711276032 bytes
Redo Buffers 93011968 bytes
Database mounted.

SQL> recover database;
Media recovery complete.

SQL> alter database open;

Database altered.

SQL> create user testuser identified by testuser;

User created.

SQL> grant connect,resource to testuser;

Grant succeeded.

# imp file=testuser.dmp fromuser=TESTUSER TOUSER=TESTUSER

Import: Release 12.1.0.2.0 - Production on Tue Jan 17 05:23:59 2017

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

Export file created by EXPORT:V12.01.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing TESTUSER's objects into TESTUSER
. . importing table "TEST1" 86452 rows imported
. . importing table "TEST2" 35 rows imported

Comments (0)

Please to add comments

No comments yet. Be the first to comment!