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>

Flashback Technology :#02

Performing a Flashback Drop Operation

In Oracle Ver. 10g  Oracle introduced the concept of Recycle Bin i.e. whatever tables you drop the database does not immediately remove the space used by table. Instead, the table is renamed and placed in Recycle Bin. The FLASHBACK TABLE…BEFORE DROP command will restore the table.
Oracle renames dropped and their associated objects, giving them system-generated recyclebin names that begin with BIN$.

BIN$unique_id$version
where:

• unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases 
• version is a version number assigned by the database

This feature is not dependent on UNDO TABLESPACE so UNDO_RETENTION parameter has no impact on this feature.

To Recover Drop tables the RECYCLEBIN parameter must be turned ON, otherwise you will not be able to recover drop tables

SQL> SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

VALUE
---------------
on

SQL>

if the Value Is “on” then recyclebin feature is enabled for the Database. 
If the Value is “off” the recyclebin feature is disabled.

SQL > ALTER SYSTEM SET recyclebin = ON;
or
SQL > ALTER SESSION SET recyclebin = ON;

SQL > ALTER SYSTEM SET recyclebin = OFF;
or
SQL > ALTER SESSION SET recyclebin = OFF;

Show the Contents in RECYCLEBIN

SQL > SHOW RECYCLEBIN;
Or
SQL > SELECT * FROM USER_RECYCLEBIN;
or
SQL> SELECT * FROM DBA_RECYCLEBIN;


Restore the Objects:
User can restore the Dropped tables by issuing the following commands,The following Commands can be used to restore the dropped Objects.

SQL > FLASHBACK TABLE <<Table_Name >> TO BEFORE DROP;

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 D
         2 Z

SQL>
SQL> drop table test;

Table dropped.

SQL> SHOW RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Y6TCpiPVE1jgVQAAAAAAAQ==$0 TABLE        2018-01-26:06:54:40
SQL>

SQL> FLASHBACK TABLE TEST TO BEFORE DROP;

Flashback complete.

SQL> select * from user_recyclebin;

no rows selected

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 D
         2 Z

SQL> 


 It is possible to restore the table in to different name by issuing the following SQL Command.

 SQL > FLASHBACK TABLE << Dropped Table Name >> TO BEFORE DROP RENAME TO <<New Table Name >>;

 FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;

 SQL>  select * from test;

        ID NAME
---------- --------------------------------------------------
         1 D
         2 Z
         3 A

SQL> drop table test;

Table dropped.

SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Y6TCpiPWE1jgVQAAAAAAAQ==$0 TABLE        2018-01-26:06:59:46

SQL> FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;

Flashback complete.

SQL> select * from test1;

        ID NAME
---------- --------------------------------------------------
         1 D
         2 Z
         3 A


 
Using the above statement, its possible to restore the various version of the table data if the table
is created and Dropped more than once.

While restoring system restores the table in Descending order.

SQL> show recyclebin
SQL>
SQL> CREATE TABLE TEST_RBIN (COL1 NUMBER);

Table created.

SQL> INSERT INTO TEST_RBIN VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST_RBIN;

Table dropped.

SQL> CREATE TABLE TEST_RBIN (COL1 NUMBER);

Table created.

SQL> INSERT INTO TEST_RBIN VALUES (2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST_RBIN;

Table dropped.

SQL> CREATE TABLE TEST_RBIN (COL1 NUMBER);

Table created.

SQL>  INSERT INTO TEST_RBIN VALUES (3);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST_RBIN;

Table dropped.

SQL> SHOW RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST_RBIN        BIN$Y6TCpiPZE1jgVQAAAAAAAQ==$0 TABLE        2018-01-26:07:05:02
TEST_RBIN        BIN$Y6TCpiPYE1jgVQAAAAAAAQ==$0 TABLE        2018-01-26:07:04:22
TEST_RBIN        BIN$Y6TCpiPXE1jgVQAAAAAAAQ==$0 TABLE        2018-01-26:07:03:45
SQL>  FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN1;

Flashback complete.

SQL> FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN2;

Flashback complete.

SQL> FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN3;

Flashback complete.

SQL> SELECT * FROM TEST_RBIN1;

      COL1
----------
         3

SQL> SELECT * FROM TEST_RBIN2;

      COL1
----------
         2

SQL> SELECT * FROM TEST_RBIN3;

      COL1
----------
         1

SQL>



Purging Objects from Recycle Bin

f you want to recover the space used by a dropped table give the following command

SQL> purge table emp;

If you want  to purge objects of  logon user  give the following command

SQL> purge recycle bin;

If you want to recover space for dropped objects of a particular tablespace give the command

SQL> purge tablespace hr;

You can also purge only objects from a tablespace belonging to a specific user, using the following form of the command:

SQL> PURGE TABLESPACE hr USER scott;

If you have the SYSDBA privilege, then you can purge all objects from the recycle bin, regardless of which user owns the objects, using this command:

SQL> PURGE DBA_RECYCLEBIN;

To view the contents of Recycle Bin give the following command

SQL> show recycle bin;

Permanently Dropping Tables
If you want to permanently drop tables without putting it into Recycle Bin, drop tables with purge command like this

SQL> drop table emp purge;

This will drop the table permanently and it cannot be restored.




if table have indexes



SQL> drop table test;

Table dropped.

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    TYPE
------------------------------ -------------------------------- -------------------------
BIN$Y6TCpiPcE1jgVQAAAAAAAQ==$1 BIN$Y6TCpiPaE1jgVQAAAAAAAQ==$0   INDEX
BIN$Y6TCpiPdE1jgVQAAAAAAAQ==$0 TEST                             TABLE

SQL>
SQL> flashback table test to before drop;

Flashback complete.

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from user_recyclebin;

no rows selected

SQL> select owner,index_name,table_name,status from dba_indexes where owner='DUCAT';

OWNER                          INDEX_NAME                     TABLE_NAME STATUS
------------------------------ ------------------------------ ---------- --------
DUCAT                          BIN$Y6TCpiPcE1jgVQAAAAAAAQ==$1 TEST       VALID

SQL> alter index "BIN$Y6TCpiPcE1jgVQAAAAAAAQ==$1" rename to test_id_idx;

Index altered.

SQL> select owner,index_name,table_name,status from dba_indexes where owner='DUCAT';

OWNER                          INDEX_NAME                     TABLE_NAME STATUS
------------------------------ ------------------------------ ---------- --------
DUCAT                          TEST_ID_IDX                    TEST       VALID

SQL>

Related Views

• RECYCLEBIN$ (base table)
• DBA_RECYCLEBIN
• USER_RECYCLEBIN
• RECYCLEBIN (synonym for USER_RECYCLEBIN)

Flashback Technology :#01

Flashback Table


Using Flashback Table to return Table to Past States.

With Flashback Table, you can recover a table or tables to a specific point in time without restoring a backup. When you use this feature, the data in tables and their associated objects (indexes,constraints, triggers, and so on) is restored. The data used to satisfy a Flashback Table request is retrieved from the undo tablespace. Therefore, UNDO_RETENTION parameter is significant in Flashing Back Tables to a past state. You can only flash back tables up to the retention time you specified.You can use Flashback Versions Query and Flashback Transaction Query to determine the appropriate flashback time.

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations.

You must grant the FLASHBACK TABLE or FLASHBACK ANY TABLE system privilege to any user that uses the Flashback Table feature. In addition, you must grant the SELECT, INSERT, DELETE, and ALTER object privileges to the user.


Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:

ALTER TABLE emp ENABLE ROW MOVEMENT;



SQL> FLASHBACK TABLE EMP TO TIMESTAMP
      TO_TIMESTAMP('26/01/2018 04:12:13','DD/MM/YYYY HH24:MI:SS')
      ENABLE TRIGGERS;  2    3
FLASHBACK TABLE EMP TO TIMESTAMP
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

SQL>ALTER TABLE emp ENABLE ROW MOVEMENT;
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
       999 KUMAR

10 rows selected.

SQL> FLASHBACK TABLE EMP TO TIMESTAMP
      TO_TIMESTAMP('26/01/2018 04:12:13','DD/MM/YYYY HH24:MI:SS')
      ENABLE TRIGGERS;  2    3

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>



FLASHBACK TABLE EMP TO TIMESTAMP
      TO_TIMESTAMP('26/01/2018 04:11:13','DD/MM/YYYY HH24:MI:SS')
      ENABLE TRIGGERS;

You have to give ENABLE TRIGGERS option otherwise, by default all database triggers on the table will be disabled.

Flashback Table cannot be performed on system tables, remote tables, and fixed tables.

Flashback table using SCN

SQL>
SQL> create table test_flash(id number);

Table created.

SQL> alter table test_flash enable row MOVEMENT;

Table altered.

SQL>
SQL>
SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    1628982

SQL> insert into test_flash values(1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    1629015

SQL> FLASHBACK TABLE test_flash TO SCN 1628982;

Flashback complete.

SQL> select * from test_flash;

no rows selected

SQL> FLASHBACK TABLE test_flash TO SCN 1629015;

Flashback complete.

SQL> select * from test_flash;

        ID
----------
         1
         1
         1

SQL>

Converting SCN to a Timestamp and Vice-versa

The system change number (SCN) is Oracle's clock, every time we commit the clock increments. The SCN just marks a consistent point in time in the database.Database SCN number some time is very important.

If you know the SCN (system change number) you can get its timestamp value (within about +/–3 seconds) by the function scn_to_timestamp.

*In versions of Oracle prior to 10g, the time mapping of SCN with time was +/- 5 minutes but from 10g onwards, this is changed to +/- 3 seconds.

• Filling gaps of archive logs between primary server and Physical standby server
• particularly when you are doing  recovery

GET_SYSTEM_CHANGE_NUMBER: for getting the current system change number of the database. in 9i

SQL>  select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1620762

SQL>

From 10g

SQL> select current_scn from v$database;  

CURRENT_SCN
-----------
    1621647


SQL>

SCN_TO_TIMESTAMP: for converting given scn to timestamp value.

SQL> select scn_to_timestamp(1620762) as timestamp from dual;

TIMESTAMP
---------------------------------------------------------------------------
26-JAN-18 02.42.53.000000000 AM


TIMESTAMP_TO_SCN : For getting SCN equivalent of the given timestamps value. You must use to_timestamp conversion for the
character value.


SQL> select timestamp_to_scn(to_timestamp('26/01/2018 02:42:53','DD/MM/YYYY HH24:MI:SS')) as scn from dual;

       SCN
----------
   1620762

SQL>


SQL> select timestamp_to_scn(systimestamp) current_scn from dual;

CURRENT_SCN
-----------
    1621155

SQL>



If the value passed in too old, you will get the following message "ORA-08180:no snapshot found based on specified time."


SQL> select timestamp_to_scn(to_timestamp('26/01/2012 02:42:53','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
select timestamp_to_scn(to_timestamp('26/01/2012 02:42:53','DD/MM/YYYY HH24:MI:SS')) as scn from dual
       *
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1