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 db is up, we can import the dump. It will need sometime( for flashback the database )
PREREQUISITE:
1. Database Must be in Archive log mode
2. Flashback must be enabled for the database.
3. all the flashback log and archives should be available, from the time, the user is dropped
METHODS:
1. Make sure flashback and archive mode is enable.
SQL> select flashback_on,log_mode from v$database; FLASHBACK_ON LOG_MODE ------------------ ------------ YES ARCHIVELOG
2. Lets drop a user, to test the scenarios:
04:47:14 SQL> select table_name from dba_tables where owner='DBACLASS'; TABLE_NAME ---------------------------------------------------------------------------- TABLE1 TABLE2 04:47:32 SQL> drop user dbaclass cascade; User dropped.
3. flashback the database to past, when the user was available.
04:52:15 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
04:52:48 SQL>
04:52:50 SQL>
04:52:50 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.
04:53:08 SQL> flashback database to timestamp to_date('17-JAN-2017 04:47:14','DD-MON-YYYY HH24:MI:SS');
Flashback complete.
4. Open the database in readonly mode:
04:55:13 SQL> ALTER DATABASE OPEN READ ONLY; Database altered. 04:55:31 SQL> select table_name from dba_tables where owner='DBACLASS'; TABLE_NAME -------------------------------------------------------------------------------- TABLE1 TABLE2
We can see the tables are available now.
5. Take export backup of the schema DBACLASS:
# exp owner=DBACLASS file=dbaclass.dmp Export: Release 12.1.0.2.0 - Production on Tue Jan 17 05:17:45 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 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 DBACLASS . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user DBACLASS About to export DBACLASS's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export DBACLASS'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.
6. 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.
7. Now create an empty user and import the dump
SQL> create user dbaclass identified by dbaclass; User created. SQL> grant connect,resource to dbaclass; Grant succeeded. # imp file=dbaclass.dmp fromuser=DBACLASS TOUSER=DBACLASS 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 DBACLASS's objects into DBACLASS . . importing table "TEST1" 86452 rows imported . . importing table "TEST2" 35 rows imported
Though it need some outage in the database, to restore the user, we are able to restore schema, without loosing any other data in database.
