Flashback Technology :#03

Performing Flashback Database

The FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery. In order to flashback the database you must have SYSDBA privilege and the flash recovery area must have been prepared in advance.

The FLASHBACK_ON column of the V$DATABASE view shows the current status of flashback database.
If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode.


With flashback enabled the database can be switched back to a previous point in time or SCN without the need for a manual incomplete recovery. In the following example a table is created, the database is then flashbacked to a time before the table was created.


Flashback database allows you to rollback database to a time in the past.

Flashback database can be issued with 3 different conditions:

1. TO TIMESTAMP
2. TO SCN
3. TO SEQUENCE( LOG ARCHIVE SEQ)
4. RESTORE POINT


How much size we should set the flash recovery area:

After you have enabled the Flashback Database feature and allowed the database to generate some flashback logs, run the following query:

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

ESTIMATED_FLASHBACK_SIZE
------------------------
               221061120
SQL>

How far you can flashback database:

To determine the earliest SCN and earliest Time you can Flashback your database,  give the following query:

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
-------------------- --------------------
             1614954 26-JAN-2018 00:50:08

SQL>


SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
26-JAN-18 09.24.41.474574 AM +05:30

SQL>
SQL> !date
Fri Jan 26 09:24:47 IST 2018

Flashback database using SCN:


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1636591

SQL> create table t1 (id number);

Table created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 26 09:26:08 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> flashback database to scn 1636591;
flashback database to scn 1636591
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.


SQL> select flashback_on from v$database;

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

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 26 09:29:30 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL> flashback database to scn 1636591;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open read only;

Database altered.

SQL> conn ducat/ducat
Connected.
SQL> select *  from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DAY1                           TABLE
EMP                            TABLE
SYS_TEMP_FBT                   TABLE
TEST                           TABLE
TEST1                          TABLE
TEST_FLASH                     TABLE
TEST_RBIN1                     TABLE
TEST_RBIN2                     TABLE
TEST_RBIN3                     TABLE

9 rows selected.

SQL>


If everything looks good



[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 26 09:33:20 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open RESETLOGS;

Database altered.

SQL> select name,open_mode ,log_mode,flashback_on from v$database;

NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON
--------- -------------------- ------------ ------------------
DUCAT     READ WRITE           ARCHIVELOG   YES

SQL>


Flashback database using restore point:

In addition to using SCNs and timestamps, we can create restore points and guaranteed restore points. A restore point is just a text alias representing a SCN. A guaranteed restore point means the database doesn't delete any flashback logs between that point and the current time.


CREATE RESTORE POINT before_upgrade;
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

we can use below command to flashback database: 

Existing all restore points can be displayed using the V$RESTORE_POINT view.

SQL> create restore point before_upgrade;
Restore point created.
SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUARANTEE_ STORAGE_SIZE TIME                 RESTORE_PO PRE NAME
---------- --------------------- ---------- ------------ -------------------- ---------- --- ---------------
   1637197                     6 NO                    0 26-JAN-18 09.42.24.0            NO  BEFORE_UPGRADE
                                                         00000000 AM
SQL>
SQL> create user abc identified by abc;
User created.
SQL> grant create session to abc;
Grant succeeded.
SQL> conn abc/abc
Connected.
SQL> exit


[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 26 09:56:29 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO RESTORE POINT before_upgrade;

Flashback complete.

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DUCAT     READ WRITE

SQL> conn abc/abc
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL>

ABC user not exist after flashback database using restore point before_upgrade.



Flashback table using restore_point:


SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUARANTEE_ STORAGE_SIZE TIME                 RESTORE_PO PRE NAME
---------- --------------------- ---------- ------------ -------------------- ---------- --- ---------------
   1637197                     6 NO                    0 26-JAN-18 09.42.24.0            NO  BEFORE_UPGRADE
                                                         00000000 AM
SQL>

SQL> select * from emp;

     EMPNO NAME
---------- ---------------
       111 ABDD
       222 BCFF
       333 DDGG
       444 EEE
       555 FFFF
       555 FFFF
       666 GGGG
       666 GGGG
       666 GGGG

9 rows selected.

SQL> insert into emp values(999,'LLLL');

1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from emp;

     EMPNO NAME
---------- ---------------
       111 ABDD
       222 BCFF
       333 DDGG
       444 EEE
       555 FFFF
       555 FFFF
       666 GGGG
       666 GGGG
       666 GGGG
       999 LLLL
       999 LLLL
       999 LLLL

12 rows selected.

SQL> FLASHBACK TABLE emp TO RESTORE POINT before_upgrade;  2

Flashback complete.

SQL> select * from emp;

     EMPNO NAME
---------- ---------------
       111 ABDD
       222 BCFF
       333 DDGG
       444 EEE
       555 FFFF
       555 FFFF
       666 GGGG
       666 GGGG
       666 GGGG

9 rows selected.


SQL>