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 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>
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
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
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