- 11 Oct, 2023
- 0 Comments
- 3 Mins Read
How To Recover A Dropped User Using Flashback in Oracle.
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.
SQL> select flashback_on,log_mode from v$database; FLASHBACK_ON LOG_MODE ------------------ ------------ YES ARCHIVELOG
- Lets drop a user, to test the scenarios:
SQL> select table_name from dba_tables where owner='TESTUSER'; TABLE_NAME ---------------------------------------------------------------------------- TABLE1 TABLE2 SQL> drop user testuser cascade; User dropped.
- flashback the database to past, when the user was available.
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.
4. Open the database in read only mode:
SQL> ALTER DATABASE OPEN READ ONLY; Database altered. SQL> select table_name from dba_tables where owner='TESTUSER'; TABLE_NAME -------------------------------------------------------------------------------- TABLE1 TABLE2
- Take export backup of the schema TESTUSER:
# 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.
- Now restore the database to current stage:
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.
- Now create an empty user and import the dump.
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