Flashback Technology : Flashback to a restore point having standby database also enabled

Yesterday we were in a scenario where we have to do some changes in our production database which if needed can be rollbacked.

We planned to go with a restore point option so that if change not needed by Development team, we can move back to before change time.

This was a big database and it has standby also enabled with it. So if we do a flashback on primary then incarnation of primary will differ from standby and recovery will be stopped.

Below are the steps which we used to handle both primary and standby in case of a Flashback database in Primary :-

1. Backup Primary database

2. Note the Current SCN of primary :-

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   15883324

3. Create a restore point 

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Restore point created.

4. Do your changes in database 

Now you want to do the flashback in your primary Database as the changes which you did, development team does not require those. PFB steps :-

SQL> startup force mount

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2260088 bytes

Variable Size            1124074376 bytes

Database Buffers         3137339392 bytes

Redo Buffers               12107776 bytes

Database mounted.

SQL> flashback database to restore point before_upgrade;

Flashback complete.

SQL> alter database open RESETLOGS;

Database altered.

Now when you check your standby database , MRP is stopped and it's showing that incarnation is different from primary database.

MRP0: Incarnation has changed! Retry recovery...
Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit_pr00_17847.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!

Move your standby database to that SCN which was before doing a flashback.

Standby Database :-

SQL> flashback database to scn 15883324;

Flashback complete.

Now start the MRP and check the alert log, it will show MRP started successfully

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

Attempt to start background Managed Standby Recovery process (dr3fia)

Fri Jul 24 06:10:35 2015

MRP0 started with pid=49, OS id=14805

MRP0: Background Managed Standby Recovery process started (dr3fia)

 started logmerger process

Fri Jul 24 06:10:40 2015

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 12 slaves

Media Recovery start incarnation depth : 1, target inc# : 6, irscn : 15883360

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log +Amit_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

I hope this article helped you.

Thanks

Amit Rath

How to restore a dropped table using Flashback Drop

Flashback Drop is a feature in Oracle Database through which we can restore table which has been accidentally dropped.

To use this feature FLASHBACK has to be enabled in your database . To enable FlASHBACK
visit Enable Flashback in Oracle

Database has to be in Archive log mode. To enable Archive log mode in Database visit Change Archive mode of Database

This feature uses recycle bin to restore dropped table. We can specify either the name of the table in recycle bin or the original table name to restore. We can also rename the table while restoring it from the recycle bin using "RENAME TO" clause.

PFB example to restore a table and its dependent objects through Flashback Drop :-

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                    TABLE                             VALID
AMIT_PP                                              PROCEDURE                 VALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_TT                                             TRIGGER                         VALID
AMIT_VV   VIEW                               VALID

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME         STATUS
-------------                 --------
D_UI                        VALID
ID_NI                       VALID
D_NI                        VALID
ORDER_NI              VALID
USERID                  VALID
ID_TIME                VALID
H_NI                       VALID
DEACT_H_NI        VALID

8 rows selected.

SQL> drop table AMIT cascade constraints;

Table dropped.

########### if we use purge in Drop command then we cannot restore it from recycle bin##########

SQL> drop table AMIT cascade constraints purge;

Table dropped.

Query the user_recyclebin to get the details of dropped table and its dependents. Recyclebin is the synonym for user_recyclebin. The system generated name of the dropped object is unique across database. Dropping a table drop the associated indexes , triggers but not Procedures , Functions and views . 

When we restore a table from Recycle bin , the dependent objects such as triggers , indexes etc do not get their original names back, they have their system generated names with them . We have to manually rename them after the table restore operation. So before restoring the table from Recycle bin, make a note of the system generated recycle bin names of the dependent objects. 

After dropping AMIT table , before restoring it run below query :-

SQL> select  object_name, original_name,type, createtime FROM recyclebin;

OBJECT_NAME                                                             ORIGINAL_NAME                    TYPE                      CREATETIME
-----------------------------------------------------------------          --------------------------------   ------------------------- -------------------
BIN$8RsifQMpTIe2DYcWdrZBsQ==$0                         D_UI                      INDEX   2013-07-01:11:03:38
BIN$zwr7JQ55SOecmnkTOAbGkA==$0                       ID_NI                     INDEX   2013-07-01:11:03:38
BIN$YagDo75NS0enszVEvCtv+g==$0                           D_NI                      INDEX   2013-07-01:11:03:38
BIN$amdEjo63Q5GfqR2aqyLrYg==$0                           ORDER_NI            INDEX   2013-07-01:11:03:38
BIN$kow3P8xOSwC1fzhLep5wgg==$0                          USERID                 INDEX   2013-07-01:11:03:38
BIN$8epaA+Z7RyeIQ+zTiT8r4g==$0                              ID_TIME               INDEX   2013-07-01:11:03:39
BIN$MPqYce8sQLGUoxTor3zMVw==$0                        H_NI                      INDEX   2013-07-01:11:03:39
BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0                       DEACT_H_NI        INDEX   2013-07-01:11:03:39
BIN$kzeXKoZoSwqycBiH6X8FzA==$0                          AMIT_TT              TRIGGER   2013-07-09:12:48:03
BIN$C5EACqXLRZyACHAhLjC/LQ==$0                       AMIT                     TABLE   2013-07-01:11:02:10

Now restore table through Flashback drop :-

SQL> flashback table AMIT to before drop;

Flashback complete.

############ if you want to rename AMIT to TEST then use RENAME ################

SQL> flashback table AMIT to before drop rename to TEST;

Flashback complete

Once the table has been restored , dependent objects also have been restored. Dependent objects like indexes , triggers have their system generated names with them and are in Valid state. Dependent objects like procedures , Functions, View associated with table are in invalid state . We have to compile them manually to make them valid.

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME                                                             STATUS
------------------------------------------------------                  --------
BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0                  VALID
BIN$MPqYce8sQLGUoxTor3zMVw==$0                  VALID
BIN$8epaA+Z7RyeIQ+zTiT8r4g==$0                       VALID
BIN$kow3P8xOSwC1fzhLep5wgg==$0                    VALID
BIN$amdEjo63Q5GfqR2aqyLrYg==$0                     VALID
BIN$YagDo75NS0enszVEvCtv+g==$0                    VALID
BIN$zwr7JQ55SOecmnkTOAbGkA==$0                 VALID
BIN$8RsifQMpTIe2DYcWdrZBsQ==$0                    VALID

8 rows selected.

SQL> select TRIGGER_NAME,status from user_triggers where TABLE_NAME='AMIT';

TRIGGER_NAME                                          STATUS
------------------------------                                  --------
BIN$kzeXKoZoSwqycBiH6X8FzA==$0        ENABLED

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                      TABLE                             VALID
AMIT_PP                                              PROCEDURE                 INVALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_VV    VIEW                               INVALID

We have to manully rename Indexes and triggers according to the note we have made before restoring.

SQL> alter trigger "BIN$kzeXKoZoSwqycBiH6X8FzA==$0" rename to AMIT_TT;

Trigger altered.

SQL> alter index "BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0" rename to ID_NI;

Index altered.

SQL> alter index "BIN$MPqYce8sQLGUoxTor3zMVw==$0" rename to D_UI;

Like this rename all the indexes . Now compile the procedures and Views.

SQL> alter view AMIT_VV compile;

View altered.

SQL> alter procedure AMIT_PP compile;

Procedure altered.

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME         STATUS
-------------                 --------
D_UI                        VALID
ID_NI                       VALID
D_NI                        VALID
ORDER_NI              VALID
USERID                  VALID
ID_TIME                VALID
H_NI                       VALID
DEACT_H_NI        VALID

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                      TABLE                             VALID
AMIT_PP                                              PROCEDURE                 VALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_TT                                             TRIGGER                         VALID
AMIT_VV    VIEW                               VALID

Restoration of table from Recylebin completed.

I hope this article helped you.

Regards,
Amit Rath

How to enable Flashback in oracle database 11g

Flashback in Oracle Database

Flashback technology is a set of features in Oracle database that make your work easier to view past states of data or to move your database objects to a previous state without using point in time media recovery.

View past states of data or move database objects to previous state means you have performed some operations like  DML + COMMIT and now you want to rollback that operation, this can be done easily through FLASHBACK technology without using point in time media recovery.

How to enable FLASHBACK in Oracle Database 11G R1 and below versions

1. Database has to be in ARCHIVELOG mode.
     To change ARCHIVE mode refer to -- Change ARCHIVE mode of database

2. Flash Recovery Area has to be configured. To configure PFB steps :-

SQL> show parameter db_recovery_file_dest

NAME                                  TYPE           VALUE
------------------------------------       ----------- -       -----------------------------
db_recovery_file_dest             string
db_recovery_file_dest_size     big integer     0

Currently flashback is disabled. To enable :-

A. Set db_recovery_file_dest_size initialization parameter.

SQL> alter system set db_recovery_file_dest_size=2g;

System altered.

B. After db_recovery_file_dest_size parameeter has been set, create a location in OS where your FLASHBACK logs will be stored.

bash-3.2$ cd /orcl_db
bash-3.2$ mkdir FLASHBACK
bash-3.2$ pwd
/orcl_db/FLASHBACK

C. Now set db_recovery_file_dest initialization parameter.

SQL> alter system set db_recovery_file_dest='/orcl_db/FLASHBACK';    ##########For Standalone database##########

System altered.

SQL> alter system set db_recovery_file_dest='/orcl_db/FLASHBACK' sid='*';    ##########For RAC database##########

System altered.

SQL> show parameter db_recovery

NAME                                 TYPE           VALUE
------------------------------------      -----------         ------------------------------
db_recovery_file_dest            string           /orcl_db/FLASHBACK
db_recovery_file_dest_size     big integer    2G

3. Create an Undo Tablespace with enough space to keep data for flashback operations. More often users update the database more space is required.

4. By default automatic Undo Management is enabled, if not enable it. In 10g release 2 or later default value of UNDO management is AUTO. If you are using lower release then PFB to enable it:-

SQL> alter system set undo_management=auto scope=spfile;

System altered

5. Shut Down your database

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

6. Startup your database in MOUNT mode

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1025298432 bytes

Fixed Size                  1341000 bytes

Variable Size             322963896 bytes

Database Buffers          696254464 bytes

Redo Buffers                4739072 bytes

Database mounted.

7. Change the Flashback mode of the database

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL>alter database flashback ON;

Database altered.

SQL> select flashback_on from v$database;

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

SQL> alter database open;

Database altered.

FLASHBACK mode of the database has been enabled.

How to disable FLASHBACK in Oracle Database 11G R1 and below versions

1. Shut Down your database

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

2. Startup your database in MOUNT mode

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1025298432 bytes

Fixed Size                  1341000 bytes

Variable Size             322963896 bytes

Database Buffers          696254464 bytes

Redo Buffers                4739072 bytes

Database mounted.

SQL> select flashback_on from v$database;

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

SQL>alter database flashback OFF;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO


SQL> alter database open;

Database altered.

FLASHBACK mode of the database has been disabled.


How to enable/disable FLASHBACK in Oracle Database 11G R2 and above versions.

From 11GR2 we donot have to bounce the database to alter flashback.


1. Database has to be in ARCHIVELOG mode.
     To change ARCHIVE mode refer to -- Change ARCHIVE mode of database

2. Flash Recovery Area has to be configured. To configure PFA steps.

3.  TO enable or disable flashback , we can change this while database is in open mode. PFB


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;

Database altered.

SQL> alter database flashback off;

Database altered.

I hope this article helped you.

Regards,

Amit Rath