Tuesday, June 28, 2016

Recovering Dropped schema using Flashback Database,without any data loss-

You can recover a dropped schema if flashback is enabled for your database.The main advantage here is that you won't lose any data,but you have to go for a downtime until you finish the entire operation.
Make sure that the database is in flashback mode
SQL> select name from v$database;

NAME
---------
DIREC

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log ;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM
-------------------- --------------------
           133450557 27-JUN-2016 16:29:59

To simulate the issue lets create few tables in a existing user and then drop it.

SQL> conn mahi
Enter password:
Connected.
SQL> create table flashback_testing ( col1 varchar2(20));

Table created.

SQL>  insert into flashback_testing values ( 'flashback testing');

1 row created.

SQL> commit;

Commit complete.

SQL>

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
27-JUN-2016 19:49:12

SQL> conn / as sysdba
Connected.

SQL> drop user  mahi cascade ;

User dropped.

SQL>
SQL>

Let’s create some new user at this point- this is to ensure that our data are still intact at the end of this practice.

SQL> create user mahi_second identified by mahi;

User created.

SQL> grant dba to mahi_second ;

Grant succeeded.

SQL> conn mahi_second/mahi
Connected.
SQL>
SQL>
SQL> create table t1 (id number );

Table created.

SQL> insert into t1 values (222);

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from t1;

        ID
----------
       222

In order to recover lost schema, use the flashback database feature to a time approximately before the user dropped.
 
SQL> conn / as sysdba
Connected.
SQL>
SQL> shut immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  5291928 bytes
Variable Size            2717909096 bytes
Database Buffers         1560281088 bytes
Redo Buffers               11485184 bytes
Database mounted.
SQL> flashback database to timestamp to_date('27-JUN-2016 19:50:00','DD-MON-YYYY HH24:MI:SS');

Flashback complete.

Following output  from the alert log 


Open the database in Read Only mode

SQL> alter database open read only;

Database altered.

SQL> conn mahi_second/mahi
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

Could see that we lost new user(mahi_second) that  we created after droping the mahi user . 

Check for the mahi user and its corresponing data
 
SQL> conn mahi
Enter password:
Connected.
SQL> select * from flashback_testing;

COL1
--------------------
flashback testing

SQL> conn / as sysdba
Connected.

Take an export of the user using traditional exp utilitynote that expdp wont work when database in read only mode.

[oracle@exbdb01]:[DIREC1] $ exp owner=mahi  file=/t3_nfs/REC/mahi_schema_bkp.dmp

Export: Release 12.1.0.2.0 - Production on Mon Jun 27 20:03:28 2016

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Applicat
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 MAHI
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MAHI
About to export MAHI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MAHI's tables via Conventional Path ...
. . exporting table              FLASHBACK_TESTING          1 rows exported
. . exporting table                        QRC_TAB          5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                              T          3 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
. . exporting table                             T1        245 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           TAB1      10005 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                           TAB2      10100 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                          TEST2        230 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                          TOTAL
EXP-00107: Feature (VIRTUAL COLUMN) of column TOTAL in table MAHI.TOTAL is not supported. The table will not be exported.
. . exporting table                             TT     721536 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                            USR        245 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.

Get the ddl of mahi user :-

SQL> set long 99999999
SQL> set pages 0
SQL>  select dbms_metadata.get_ddl( 'USER','MAHI' ) from dual;

   CREATE USER "MAHI" IDENTIFIED BY VALUES 'S:60D9117322086C4480665FBAFD12E27702
A0CCADE4701293D060EAF9CC2F;H:71882F2C5A0988B33789D6D72A80FAD9;T:BE0347FD254339F7
AC47C3CA6E04898360237911C4C523135729EF65E89D6CFEFF2094166DA0E717772108EB83434A0E
B4DE230F133673C493026416042BA9F49362B05A068CB01388B324E86B4E4C34;4CC4C0C9A546EF70'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','MAHI') from dual;

  GRANT UNLIMITED TABLESPACE TO "MAHI"


SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT','MAHI') from dual;
ERROR:
ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 6069
ORA-06512: at "SYS.DBMS_METADATA", line 8817
ORA-06512: at line 1

no rows selected

SQL>  select dbms_metadata.get_granted_ddl('ROLE_GRANT','MAHI')   from dual;

   GRANT "DBA" TO "MAHI"

SQL>
[oracle@exbdb01]:[DIREC1] $ ls -lrt /t3_nfs/REC/mahi_schema_bkp.dmp

Shutdown the database and do a recovery of the database to bring it to the CURRENT state.
 
[oracle@exbdb01]:[DIREC1] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 20:04:24 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

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

Total System Global Area 4294967296 bytes
Fixed Size                  5291928 bytes
Variable Size            2717909096 bytes
Database Buffers         1560281088 bytes
Redo Buffers               11485184 bytes
Database mounted.
SQL> recover database;

Media recovery complete
SQL
SQL> alter database open;

Database altered.

Now this database is in curent state without the dropped user but we have full export dump of that user before its dropped.

Create mahi user  and give necessary privileges  :-

SQL> @create_mahi_user.sql

User created.


Grant succeeded.


Grant succeeded.

SQL>



[oracle@exbdb01]:[DIREC1] $ imp full=y  file=/t3_nfs/REC/mahi_schema_bkp.dmp

Import: Release 12.1.0.2.0 - Production on Mon Jun 27 20:09:44 2016

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Applicat

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 SYS's objects into SYS
. importing MAHI's objects into MAHI
. . importing table            "FLASHBACK_TESTING"          1 rows imported
. . importing table                      "QRC_TAB"          5 rows imported
. . importing table                            "T"          3 rows imported
. . importing table                           "T1"        245 rows imported
. . importing table                         "TAB1"      10005 rows imported
. . importing table                         "TAB2"      10100 rows imported
. . importing table                        "TEST2"        230 rows imported
. . importing table                           "TT"     721536 rows imported
. . importing table                          "USR"        245 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@exbdb01]:[DIREC1] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 20:10:46 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> conn mahi/mahi
Connected.
SQL> select * from "FLASHBACK_TESTING";

COL1
--------------------
flashback testing

SQL> conn mahi_second/mahi
Connected.
SQL> select * from t1;

        ID
----------
       222

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@exbdb01]:[DIREC1] $

Now this database is upto date with the dropped table. NO DATA LOSS :-)
LIMITATION:
1. If any of the flashback log or archivelog are lost then the above option won't work.
2. If there is  NOLOGGING operation after recovery those NOLOGGING transacrion blocks will be soft corrupt.Hence avoid using FLASHBACK DATABASE with a target time or SCN that coincides with a NOLOGGING operation