FIND SCN NUMBER IN FLASHBACK (TIMEBASED FIND SCN QUERY)
SELECT timestamp_to_scn(to_timestamp('24-11-2011 02:02:00', 'DD-MM-YYYY HH12:MI:SS')) as scn from dual;
===================
FLASHBACK DROP:-
===================
alter system set log_archive_dest_1='location=c:\test_flash_arcc','mandatory';
shutdown immediate
startup mount
alter database archivelog;
alter database open;
* DB_RECOVERY_FILE_DEST_SIZE
* DB_RECOVERY_FILE_DEST
alter system set db_recovery_file_dest_size=4g;
alter system set db_recovery_file_dest='e:\ora_flashback';
shutdown immediate
startup mount
alter system set db_flashback_retention_target=4320;
alter database flashback on;
alter database open;
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
SQL>SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
################################################################################
How to Enable Flashback Database
To enable flashback database the following operations is needed.
1)Configure the Database in archivelog mode.
To change archiving read, Change Archival Mode
2)Configure Flash Recovery Area.
To configure flash recovery area,
Set up Flash Recovery Area
3)Clean Shutdown and mount the database.
Alter Database Flashback ON;
Before running command you can check whether flashback was actually On or not.
select flashback_on from v$database;
Steps:
------
SQL> alter database flashback ON;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
4)Open the database and optionally you can set DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes. By default it is 1 day(1440 minutes).
To make it 3 days
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 4320
However you can disable Flashback Database for a tablespace.Then you must take its datafiles offline before running FLASHBACK DATABASE.
Like,
SQL> select file_name,file_id from dba_data_files where tablespace_name='TEST';
FILE_NAME FILE_ID
------------------------------ ----------
/oradata2/1.dbf 5
SQL> alter database datafile 5 offline;
Database altered.
SQL> ALTER TABLESPACE test flashback off;
Tablespace altered.
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
To disable flashback feature simply issue,
SQL>ALTER DATABASE FLASHBACK OFF;
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL>ATER DATABASE FLASHBACK ON;
SQL>ALTER DATABASE OPEN;
SQL>SELECT * FROM V$BGPROCESS;
SQL>SELECT NAME FROM V$BGPROCESS WHERE NAME='RVWR';
SQL>CREATE TABLE SCOTT.EMP1 AS SELECT * FROM SCOTT.EMP;
SQL> CREATE TABLE SCOTT.EMP2 AS SELECT * FROM SCOTT.EMP;
SQL> CREATE TABLE SCOTT.EMP3 AS SELECT * FROM SCOTT.EMP;
SQL>DROP TABLE SCOTT.EMP1
SQL>SELECT * FROM V$FLASHBACK_DATABASE_LOG;
SQL>SHOW RECYCLEBIN
SQL>SELECT * FROM DBA_RECYCLEBIN;
SQL>FLSHBACK TABLE EMP1 TO BEFORE DROP;
SQL>SELECT * FROM TAB WHERE TNAME LIKE 'EMP1';
SQL>DROP TABLE SCOTT.EMP2;
SQL>DROP TABLE SCOTT.EMP3;
SQL>SHOW RECYCLEBIN
SQL>SELECT * FROM DBA_RECYCLEBIN;
SQL>FLASHBACK TABLE EMP2 TO BEFORE DROP RENAME TO EMP21;
SQL>FLASHBACK TABLE EMP3 TO BEFORE DROP RENAME TO EMP31;
SQL>SHOW RECYCLEBIN
SQL>SELECT * FROM DBA_RECYCLEBIN;
=================================================================================================
==================================================================================
FLASHBACK QUERY: QUERY ALL DATA AS IT EXISTED AT A SPECIFIC POINT IN TIME STEPS
==================================================================================
SQL> CREATE TABLE SCOTT.ABC (A NUMBER(10),B VARCHAR2(10)) ENABLE ROW MOVEMENT;
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1354136
SQL> INSERT INTO SCOTT.ABC VALUES(1,'AA');
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1354172
SQL> INSERT INTO SCOTT.ABC VALUES(2,'BB');
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1354204
SQL> INSERT INTO SCOTT.ABC VALUES(3,'CC');
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1354382
SQL> INSERT INTO SCOTT.ABC VALUES(4,'DD');
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1354410
SQL> INSERT INTO SCOTT.ABC VALUES(5,'EE');
SQL> COMMIT;
I. NOTE:- See all SCN or Timestamp are same because u'll commit the transaction value only one time after insert the data
SQL> SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC' ORDER BY 5,11;
SQL> INSERT INTO SCOTT.ABC VALUES(6,'FF');
SQL> COMMIT;
SQL> INSERT INTO SCOTT.ABC VALUES(7,'GG');
SQL> COMMIT;
SQL> INSERT INTO SCOTT.ABC VALUES(8,'HH');
SQL> COMMIT;
SQL> INSERT INTO SCOTT.ABC VALUES(9,'II');
SQL> COMMIT;
SQL> INSERT INTO SCOTT.ABC VALUES(10,'JJ');
SQL> COMMIT;
II. NOTE:- See these 5 insert value SCN or Timestamp are different because every insertion after commit the value
SQL>SQL> SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC' ORDER BY 5,11;
SQL> INSERT INTO SCOTT.ABC VALUES(11,'KK');
SQL> INSERT INTO SCOTT.ABC VALUES(12,'LL');
SQL> INSERT INTO SCOTT.ABC VALUES(13,'MM');
SQL> INSERT INTO SCOTT.ABC VALUES(14,'NN');
SQL> INSERT INTO SCOTT.ABC VALUES(15,'OO');
SQL> COMMIT;
III. NOTE:- See NOTE 1 again
SQL>SQL> SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC' ORDER BY 5,11;
##NOTE:- SEE FLASHBACK QUERY DML PERFORM
============================================
SQL> SELECT COUNT(*) FROM SCOTT.ABC;
COUNT(*)
----------
15
SQL> SELECT * FROM SCOTT.ABC AS OF SCN 1354802;
A B
---------- ----------
1 AA
2 BB
3 CC
4 DD
5 EE
6 FF
7 GG
8 HH
9 II
10 JJ
10 rows selected.
SQL> SELECT COUNT(*) FROM SCOTT.ABC;
COUNT(*)
----------
15
####UPDATE
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC' ORDER BY 5,11;
SQL> UPDATE SCOTT.ABC SET B='TEST' WHERE A IN (5,6,7,8,9,10)
SQL> COMMIT;
SQL> SELECT * FROM SCOTT.ABC AS OF SCN 1354802;
A B
---------- ----------
1 AA
2 BB
3 CC
4 DD
5 EE
6 FF
7 GG
8 HH
9 II
10 JJ
SQL> SELECT * FROM SCOTT.ABC;
A B
---------- ----------
1 AA
2 BB
3 CC
4 DD
5 TEST
6 TEST
7 TEST
8 TEST
9 TEST
10 TEST
11 KK
12 LL
13 MM
14 NN
15 OO
SQL> SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC' ORDER BY 5,11;
SQL> UPDATE SCOTT.ABC SET B=(SELECT B
FROM SCOTT.ABC AS OF SCN 1354802
WHERE A IN (5))
WHERE A IN (5,6,7,8,9,10);
SQL> COMMIT;
SQL> SELECT * FROM SCOTT.ABC;
A B
------ ----------
1 AA
2 BB
3 CC
4 DD
5 EE
6 EE
7 EE
8 EE
9 EE
10 EE
11 KK
12 LL
13 MM
14 NN
15 OO
## NOTE:- SAME AS TIMESTAMP BASE
SELECT * FROM SCOTT.ABC AS OF TIMESTAMP TO_TIMESTAMP('29-11-2011 11:05:00','DD-MM-YYYY HH12:MI:SS');
SELECT * FROM SCOTT.ACB AS OF SCN;
===============================================================================================
FLASHBACK TABLE (Flashback Transaction Query)
============================================================================================
CREATE TABLE ABC (A NUMBER(10),B VARCHAR(10));
INSERT INTO ABC VALUES(1,'A');
..after 2 minutes insert
INSERT INTO ABC VALUES(2,'B');
..after 2 minutes insert
INSERT INTO ABC VALUES(3,'C');
..after 2 minutes insert
INSERT INTO ABC VALUES(4,'D');
..after 2 minutes insert
INSERT INTO ABC VALUES(5,'E');
..after 2 minutes insert
COMMIT;
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC'
ALTER TABLE ABC ENABLE ROW MOVEMENT;
SELECT COUNT(*) FROM SCOTT.ABC;
SELECT * FROM SCOTT.ABC;
SELECT CURRENT_SCN FROM V$DATABASE;
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC'
UPDATE TABLE SCOTT.ABC SET B='VINAY' WHERE A=5;
(METHODS:-)
============
(SCN BASED)
1.FLASHBACK TABLE SCOTT.ACB TO SCN 1301875; //see the SCN no. in view (FLASHBACK_TRANSACTION_QUERY) to the limit of back word of data before the update command
(TIMESTAMP BASED)
2.FLASHBACK TABLE LDBO.ABC TO TIMESTAMP TO_TIMESTAMP('25-11-2011 04:01:37','DD-MM-YYYY HH12:MI:SS'); //timestampbase see the time in view (FLASHBACK_TRANSACTION_QUERY)
(TIMESTAMP WITH INTERVAL BASED)
3.FLASHBACK TABLE SCOTT.ABC TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
(RESTORE POINT BASED)
4.FLASHBACK TABLE SCOTT.ACB TO RESTORE POINT THREE;
CREATE TABLE ABC (A NUMBER(10),B VARCHAR(10)) ENABLE ROW MOVEMENT;
CREATE RESTORE POINT ZERO;
INSERT INTO ABC VALUES(1,'A');
CREATE RESTORE POINT ONE;
INSERT INTO ABC VALUES(2,'B');
CREATE RESTORE POINT TWO;
INSERT INTO ABC VALUES(3,'C');
CREATE RESTORE POINT THREE;
INSERT INTO ABC VALUES(4,'D');
CREATE RESTORE POINT FOUR;
INSERT INTO ABC VALUES(5,'E');
CREATE RESTORE POINT FIVE;
COMMIT;
************************===========
PROBLEM:
===========************************
##run create table script & insert value with combine
CREATE TABLE ABC (A NUMBER(10),B VARCHAR(10));
INSERT INTO ABC VALUES(1,'A');
INSERT INTO ABC VALUES(2,'B');
INSERT INTO ABC VALUES(3,'C');
INSERT INTO ABC VALUES(4,'D');
INSERT INTO ABC VALUES(5,'E');
COMMIT;
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC'
Note:- See the all values SCN no. & Timestamp are same in this case i'll flashback show error
#######################################################
SQL> FLASHBACK TABLE LDBO.ABC TO TIMESTAMP TO_TIMESTAMP('25-11-2011 04:01:37','DD-MM-YYYY HH12:MI:SS');
FLASHBACK TABLE LDBO.ABC TO TIMESTAMP TO_TIMESTAMP('25-11-2011 04:01:37','DD-MM-YYYY HH12:MI:SS')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
#######################################################
#######################################################
SQL> FLASHBACK TABLE LDBO.ABC TO SCN 1299644;
FLASHBACK TABLE LDBO.ABC TO 1299644
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
#######################################################
##I'LL TRY TO SOLVE THIS PROBLEM BUT NOT SOLVED
1.After getting SCN run two query for example flashback table and flashback query I ran and it failed.
SQL> select * from TEST_FLASHBACK_FEATURE as of scn 1371097;
select * from TEST_FLASHBACK_FEATURE as of scn 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
SQL> FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371097;
FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
2.Now I want to do flashback to the SCN of 6 seconds later. For that I converted the SCN to timestamp and manually added 6 seconds in to it.
SQL> SELECT SCN_TO_TIMESTAMP(1371097) FROM DUAL;
SCN_TO_TIMESTAMP(1371097)
---------------------------------------------------------------------------
17-MAY-08 07.20.44.000000000 AM
SQL> SELECT TIMESTAMP_TO_SCN('17-MAY-08 07.20.50 AM') FROM DUAL; --add 6 seconds.
TIMESTAMP_TO_SCN('17-MAY-0807.20.50AM')
---------------------------------------
1371098
3.Now perform flashback query with the 6 seconds later SCN.
SQL> SELECT * FROM TEST_FLASHBACK_FEATURE AS OF SCN 1371098;
A
----------
1
SQL> FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371098;
flashback completed.
1.FLASHBACK TABLE ABC TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
2.FLASHBACK TABLE LDBO.ABC TO TIMESTAMP TO_TIMESTAMP('24-11-2011 02:02:00', 'DD-MM-YYYY HH12:MI:SS');
3.FLASHBACK TABLE LDBO.ABC TO SCN 1371098;
============================================================================================
FLASHBACK TABLE (Flashback Version Query / RECORDE COMMIT) PURPOSE AUDIT & MANNUAL DML PERFORM
NOT WORK IN VIEW,FIXED TABLE,TEMPORARY TABLE,EXTERNAL TABLES
============================================================================================
CREATE TABLE SCOTT.ABC (A NUMBER(10),B VARCHAR(10));
INSERT INTO SCOTT.ABC VALUES(1,'A');
INSERT INTO SCOTT.ABC VALUES(2,'B');
INSERT INTO SCOTT.ABC VALUES(3,'C');
INSERT INTO SCOTT.ABC VALUES(4,'D');
INSERT INTO SCOTT.ABC VALUES(5,'E');
COMMIT;
SQL> select * from scott.abc;
A B
---------- ----------
1 AA
2 BB
3 CC
4 DD
5 EE
6 EE
7 EE
8 EE
9 EE
10 EE
11 KK
12 LL
13 MM
14 NN
15 OO
15 rows selected.
SQL> delete from scott.abc where a=10;
SQL> commit;
SQL> exec dbms_lock.sleep(10);
SQL> delete from scott.abc where a=11;
SQL> commit;
SQL> delete from scott.abc where a=12;
SQL> commit;
SQL> delete from scott.abc where a=13;
SQL> commit;
SQL> delete from scott.abc where a in (14,15);
SQL> commit;
SQL> select * from scott.abc;
A B
---------- ----------
1 AA
2 BB
3 CC
4 DD
5 EE
6 EE
7 EE
8 EE
9 EE
SQ> select versions_xid,a,b from scott.abc versions between timestamp minvalue and maxvalue;
VERSIONS_XID A B
---------------- ---------- ---
0900110070010000 15 OO
0900110070010000 14 NN
020012007B010000 13 MM
0500150063010000 12 LL
060014006B010000 11 KK
0A0002006F010000 10 EE
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,A,B
from SCOTT.ABC versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME ;
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = hextoraw('06002900AC040000') ; -- delete
======
NOTE:- SEE UNDO_SQL THEN COPY ANY PASTE SQLPLUS TO INSERT A VALUE AGAIN;
======
OR
create table flashback_test ( c1 number, c2 date ) nologging ;
insert into flashback_test values ( 1, sysdate ) ;
commit ;
update flashback_test set c1 = c1 * 2 ;
commit ;
exec dbms_lock.sleep(15);
update flashback_test set c1 = c1 * 2 ;
commit ;
exec dbms_lock.sleep(15);
delete flashback_test ;
commit ;
exec dbms_lock.sleep(15);
select versions_starttime, versions_endtime, versions_xid, versions_operation, c1
from flashback_test versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME ;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION C1
------------------------------------------------- ------------------------------------------------- ---------------- ------------------ ----------
08/10/2007 08:18:29 08/10/2007 08:18:44 08002200A6040000 U 2
08/10/2007 08:18:44 08/10/2007 08:18:59 03002300C7040000 U 4
08/10/2007 08:18:59 06002900AC040000 D 4
08/10/2007 08:18:29 1
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = hextoraw('06002900AC040000') ; -- delete
UNDO_SQL
--------------------------------------------------------------------------------
insert into "HR"."FLASHBACK_TEST"("C1","C2") values ('4',TO_DATE('08/10/2007', '
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = hextoraw('03002300C7040000') ; -- update 4
UNDO_SQL
--------------------------------------------------------------------------------
update "HR"."FLASHBACK_TEST" set "C1" = '2' where ROWID = 'AAAONoAAEAAAAGWAAA';
The maximum of there versions e can get are dependent on UNDO_RETENTION parameter of the database. TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP flashback functions are used in SQL or PL/SQL as needed. In this example we are looking for the sum of the salaries on the employees in time;
set serveroutput on
DECLARE
l_scn NUMBER;
l_timestamp TIMESTAMP;
BEGIN
l_scn := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/48);
dbms_output.put_line('l_scn '||l_scn);
l_timestamp := SCN_TO_TIMESTAMP(l_scn);
dbms_output.put_line('l_timestamp '||l_timestamp);
END;
/
SELECT sum(salary) FROM employees
AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
SELECT sum(salary) FROM employees -- TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/48)
AS OF TIMESTAMP SCN_TO_TIMESTAMP(1531264);
SQL>
l_scn 1531264
l_timestamp 04/01/2007 08:01:29,000000
PL/SQL procedure successfully completed
SUM(SALARY)
-----------
691400
SUM(SALARY)
-----------
691400
=============================================================
FLASHBACK TABLE (Drop Table Flashback)
=============================================================
## CREATE TABLE SCOTT.ABC (A NUMBER(10),B VARCHAR(10)) ENABLE ROW MOVEMENT;
INSERT INTO SCOTT.ABC VALUES(1,'A');
INSERT INTO SCOTT.ABC VALUES(2,'B');
INSERT INTO SCOTT.ABC VALUES(3,'C');
INSERT INTO SCOTT.ABC VALUES(4,'D');
INSERT INTO SCOTT.ABC VALUES(5,'E');
COMMIT;
DROP TABLE SCOTT.ABC;
SELECT COUNT(*) FROM SCOTT.ABC; //msg:- TABLE & VIEW DOESN'T EXIST
NOTE:- See these command not work error shown TABLE & VIEW Does not exist
FLASHBACK TABLE SCOTT.ABC TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
FLASHBACK TABLE LDBO.ABC TO TIMESTAMP TO_TIMESTAMP('24-11-2011 02:02:00', 'DD-MM-YYYY HH12:MI:SS');
FLASHBACK TABLE LDBO.ABC TO SCN 1152563;
1. FLASHBACK TABLE LDBO.ABC TO BEFORE DROP;
************
2nd Method
************
WILL YOU DELETE A MULLTIPLE TABLES & YOU WILL FLASHBACK ONLY ONE TABLE THEN FOLLOWS STEPS
## CREATE TABLE SCOTT.ABC (A NUMBER(10),B VARCHAR(10)) ENABLE ROW MOVEMENT;
INSERT INTO SCOTT.ABC VALUES(1,'A');
INSERT INTO SCOTT.ABC VALUES(2,'B');
INSERT INTO SCOTT.ABC VALUES(3,'C');
INSERT INTO SCOTT.ABC VALUES(4,'D');
INSERT INTO SCOTT.ABC VALUES(5,'E');
COMMIT;
CREATE TABLE SCOTT.DEF (A NUMBER(10),B VARCHAR(10)) ENABLE ROW MOVEMENT;
INSERT INTO SCOTT.DEF VALUES(1,'A');
INSERT INTO SCOTT.DEF VALUES(2,'B');
INSERT INTO SCOTT.DEF VALUES(3,'C');
INSERT INTO SCOTT.DEF VALUES(4,'D');
INSERT INTO SCOTT.DEF VALUES(5,'E');
COMMIT;
CREATE TABLE SCOTT.GHI (A NUMBER(10),B VARCHAR(10)) ENABLE ROW MOVEMENT;
INSERT INTO SCOTT.GHI VALUES(1,'A');
INSERT INTO SCOTT.GHI VALUES(2,'B');
INSERT INTO SCOTT.GHI VALUES(3,'C');
INSERT INTO SCOTT.GHI VALUES(4,'D');
INSERT INTO SCOTT.GHI VALUES(5,'E');
COMMIT;
DROP TABLE SCOTT.ABC;
DROP TABLE SCOTT.DEF;
DROP TABLE SCOTT.GHI;
1. FLASHBACK TABLE LDBO.DEF TO BEFORE DROP;
2. FLASHBACK TABLE LDBO.DEF TO BEFORE DROP RENAME TO DEF1; /// WITH RENAME
SELECT * FROM DBA_RECYCLEBIN;
CONN SCOTT/TIGER@ORCL1011SRV
SELECT * FROM USER_RECYCLEBIN;
RENAME DEF1 TO DEF;
No comments:
Post a Comment