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. It will need some time( 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 from v$database; FLASHBACK_ON ------------------ NO SQL> alter database flashback ON; alter database flashback ON * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled. SQL> alter database archivelog; Database altered. SQL> alter database flashback on; Database altered. SQL> select flashback_on,log_mode from v$database; FLASHBACK_ON LOG_MODE ------------------ ------------ YES ARCHIVELOG
2. Let’s drop a user, to test the scenarios:
SQL> select table_name from dba_tables where owner='RAM'; TABLE_NAME ------------- TABLE1 TABLE2 TABLE3 SQL> drop user ram cascade; User dropped.
SQL> select table_name from dba_tables where owner='RAM'; no rows selected SQL> conn ram/ram ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.
3. flashback the database to past, when the user was available.
SQL> set time on;
21:08:33 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
21:09:00 SQL> startup mount;
ORACLE instance started.
Total System Global Area 1879048192 bytes
Fixed Size 8622000 bytes
Variable Size 553648208 bytes
Database Buffers 1308622848 bytes
Redo Buffers 8155136 bytes
Database mounted.
21:09:18 SQL> flashback database to timestamp to_date('11-JAN-2019 21:05:11','DD-MON-YYYY HH24:MI:SS');
Flashback complete.
Elapsed: 00:00:01.31
4. Open the database in read only mode:
21:10:42 SQL> 21:11:08 SQL> alter database open read only; Database altered. Elapsed: 00:00:00.65 21:11:29 SQL> select table_name from dba_tables where owner='RAM'; TABLE_NAME ------------- TABLE1 TABLE2 TABLE3 Elapsed: 00:00:00.65
We can see the tables are available now.
5. Take export backup of the schema RAM:
6. Now restore the database to current stage:
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
