Flashback technology Recover a Dropped User in Oracle

Flashback technology  Recover a dropped user in oracle

Introduction

Using Flashback Technology  we can restore the dropped user in oracle we will flashback the database to past when the user is available at the time of dropped before,

Then take the export dump of the schema and restore the database to same current state once database is up we can import the dump file  

Prerequisites

1. Database must be Archivelog mode

2.Flash back must be enable for the database

3.All the flashback log and  Archive log should be available from the time the user is dropped 

Let us start the process

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 the user and test the scenarios

04:47:15 SQL> select table_name from Chaitu_table where owner='CHAITANYA';

 

TABLE_NAME

----------------------------------------------------------------------------

ACCTTABLE1

MASTERTABLE2

 

 

04:47:33 SQL> drop user CHAITANYA cascade;

 

User dropped.

3.Flashback the database past when the user was available at that time

04:52:15 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

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('20-AUG-2020 04:47:33','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 chaitu_tables where owner='CHAITANYA';

 

TABLE_NAME

--------------------------------------------------------------

ACCTTABLE1

MASTERTABLE2

we can see the tables are available now

5. Take export backup of the schema CHAITANYA

# exp owner=CHAITANYA file=chaitanya.dmp

 

Export: Release 12.1.0.2.0 - Production on Tue Aug 20 05:17:45 2020

 

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 CHAITANYA

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user CHAITANYA

About to export CHAITANYA's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export CHAITANYA's tables via Conventional Path ...

. . exporting table                           ACCTTABLE1     75341 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table                          MASTERTABLE2        44 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 state

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. create the empty user and import the dumpfile

SQL> create user chaitanya identified by chaitanya;

 

User created.

 

SQL> grant connect,resource to chaitanya;

 

Grant succeeded.

 

# imp file=chaitanya.dmp fromuser=CHAITANYA TOUSER=CHAITANYA

 

Import: Release 12.1.0.2.0 - Production on Tue Aug 20 05:23:59 2020

 

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                        " ACCTTABLE1 "      75341 rows  imported

. . importing table                        "MASTERTABLE2"          44 rows imported

 we can restore the schema user chaitanya by using flashback technology

Note : Info on Flashback technology it may be differ in your environment like production,testing ,development and naming conventions 

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME