Sunday, 28 May 2017

Flashback table, Flashback database and Flashback version query in Oracle

 FLASHBACK

  FLASHBACK:- Flashback is a technology in Oracle by this we can recover the dropped table as well as we can flashback database upto a point of time or upto scn number.
   
Q:-How can check that flashback is on or off?
Ans:- By below query we can check that flashback is on or off

SQL> select flashback_on from v$database;

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

Note:- "YES" means flashback is on and "NO" means flashback is off;


Q:-How can we on/off flashback ?
Ans:- We can on/off flashback only in mount stage of database.
 Also database should in archivelog mode.

Note:- If database is not in archivelog mode then we can not on flashback.  

1) Below are the steps to off flashback from on

 


2) Below are the steps to on flashback

 





























Q:-How can we recover dropped table by flashback ?
Ans: Below is the demonstration for same
 


































Q:-How can we recover purged table by flashback ?
Ans:- Below is the demonstration for same


























Flashback database :-Flashback database is used to recover database up-to timestamp or upto scn number or upto restore point.

Note:- Purged table not recover by "flashback table <table_name> to before drop;" see the below 
 
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3819219

SQL> drop table temp purge;

Table dropped.

SQL> flashback table temp to before drop;
flashback table temp to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

Note:- Purged table we can recover by "flashback database" command which can be run at mount stage

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4225675264 bytes
Fixed Size                  2182464 bytes
Variable Size            2566914752 bytes
Database Buffers         1644167168 bytes
Redo Buffers               12410880 bytes
Database mounted.

SQL> flashback database to scn 3819373;

Flashback complete.

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

Note:- Flashback recovery is not a complete recovery so you need to open database with resetlogs option.
 
SQL> alter database open resetlogs;

Database altered.

SQL> conn  scott/scott
Connected.
SQL> select tname from tab where tname ='TEMP';

TNAME
------------------------------
TEMP

Q:-How we can recover database to restore point by flashback
Ans:- Below is the demonstration for same

SQL> create restore point abc2;

Restore point created.

SQL> drop table temp;

Table dropped.

SQL> select tname from tab where tname ='TEMP';

no rows selected

SQL> conn sys/sys as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4225675264 bytes
Fixed Size                  2182464 bytes
Variable Size            2566914752 bytes
Database Buffers         1644167168 bytes
Redo Buffers               12410880 bytes
Database mounted.
SQL> flashback database to restore point abc2;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> conn scott/scott
Connected.
SQL> select tname from tab where tname ='TEMP';

TNAME
------------------------------
TEMP

Q:-How we can recover database to timestamp by flashback ?
Ans:- Below is the demonstration for same



Q:-What is the Flashback version query ? 
Ans:- Below is the demonstration for same

SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
30-APR-2017 10:43:26

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80       1080                    20
      7566 JONES      MANAGER         7839 02-APR-81     8972.5                    20
      7782 CLARK      MANAGER         7839 09-JUN-81       2650                    95
      7788 SCOTT      ANALYST         7566 19-APR-87       3500                    20
      7839 KING       PRESIDENT            17-NOV-81       5200                    95
      7876 ADAMS      CLERK           7788 23-MAY-87       1410                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3500                    20
      7934 MILLER     CLERK           7782 23-JAN-82       2500                    95

8 rows selected.

SQL> update emp set sal=sal+200;

13 rows updated.

SQL> commit;

Commit complete.


SQL> delete from emp where deptno=30;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3820030

SQL> set page 100
SQL> set pages 100
SQL> set lin 800
SQL> /

SQL> select versions_xid,versions_startscn,versions_endscn,
  2  versions_operation,empno,ename,sal,deptno
  3  from emp
  4  versions between scn minvalue and maxvalue
  5  as of scn 3820030;


VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN V      EMPNO ENAME             SAL     DEPTNO
---------------- ----------------- --------------- - ---------- ---------- ---------- ----------
0200200027090000           3820026                 D       7844 TURNER           1700         30
0200200027090000           3820026                 D       7698 BLAKE            3050         30
0200200027090000           3820026                 D       7654 MARTIN           1450         30
0200200027090000           3820026                 D       7521 WARD             1450         30
0200200027090000           3820026                 D       7499 ALLEN            1800         30
0700090014080000           3820008                 U       7369 SMITH            1080         20
0700090014080000           3820008         3820026 U       7499 ALLEN            1800         30
0700090014080000           3820008         3820026 U       7521 WARD             1450         30
0700090014080000           3820008                 U       7566 JONES          8972.5         20
0700090014080000           3820008         3820026 U       7654 MARTIN           1450         30
0700090014080000           3820008         3820026 U       7698 BLAKE            3050         30
0700090014080000           3820008                 U       7782 CLARK            2650         95
0700090014080000           3820008                 U       7788 SCOTT            3500         20
0700090014080000           3820008                 U       7839 KING             5200         95
0700090014080000           3820008         3820026 U       7844 TURNER           1700         30
0700090014080000           3820008                 U       7876 ADAMS            1410         20
0700090014080000           3820008                 U       7902 FORD             3500         20
0700090014080000           3820008                 U       7934 MILLER           2500         95
                                           3820008         7369 SMITH             880         20
                                           3820008         7499 ALLEN            1600         30
                                           3820008         7521 WARD             1250         30
                                           3820008         7566 JONES          8772.5         20
                                           3820008         7654 MARTIN           1250         30
                                           3820008         7698 BLAKE            2850         30
                                           3820008         7782 CLARK            2450         95
                                           3820008         7788 SCOTT            3300         20
                                           3820008         7839 KING             5000         95
                                           3820008         7844 TURNER           1500         30
                                           3820008         7876 ADAMS            1210         20
                                           3820008         7902 FORD             3300         20
                                           3820008         7934 MILLER           2300         95

31 rows selected.

SQL>

No comments:

Post a Comment

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...