Flashback table to scn/timestamp
Flashback table drop
Flashback table drop with purge
Flashback tablespace recover
Flashback table to scn/timestamp
By using flashback table to scn/timestamp we will go back to the wrong updataion tn the table or retrieve the table from the delete table command.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
----------- 1626571
SQL> SELECT OPERATION FROM FLASHBACK_TRANSACTION_QUERY
2 WHERE START_SCN=1626271;
no rows selected
SQL> CONNECT FLASH/FLASH
Connected.
SQL> insert into flash values(123);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from flash;
ID
----------
46
23
46
23
46
23
46
23
123
9 rows selected.
SQL> alter table flash enable row moment;
alter table flash enable row moment
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> alter table flash enable row movement;
Table altered.
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1626794
SQL> select count(*) from flash;
select count(*) from flash
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect flash/flash
Connected.
SQL> select count(*) from flash;
COUNT(*)
----------
9
SQL> delete flash;
9 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from flash;
COUNT(*)
----------
0
SQL> select count(*) from flash as of scn 1626794;
COUNT(*)
----------
9
SQL> flashback table flash to scn 1626794;
Flashback complete.
SQL> select count(*) from flash;
COUNT(*)
----------
9
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1626997
SQL> insert into flash values(343);
1 row created.
SQL> insert into flash values(346);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> //
1 row created.
SQL> commit;
Commit complete.
SQL> select * from flash;
ID
----------
46
23
46
23
46
23
46
23
123
343
346
ID
346
346
346
346
346
346
346
346
19 rows selected.
SQL> flashback table flash to scn 1626997;
Flashback complete.
SQL> select * from flash;
ID
----------
46
23
46
23
46
23
46
23
123
9 rows selected.
SQL> select count(*) from flash;
COUNT(*)
9
Flashback table drop
By using this flashback table drop we retrieve the table from flashback drop command,the table is stored in the recyclebin.
SQL> connect /as sydba
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where ::= [/][@] | /
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1627359
SQL> connect flash/flash
Connected.
SQL> drop table flash;
Table dropped.
SQL> select * from dba_recyclebin;
select * from dba_recyclebin
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect /as sysdba
Connected.
SQL> select * from dba_recyclebin;
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
FLASH BIN$IXgZGdTYRh24/5LbJ2qRhw==$0
FLASH DROP TABLE
FLASH 2012-04-25:16:05:26 2012-04-25:17:35:01
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
1627376 YES YES 53046 53046
53046 8
SQL> flashback table flash to before drop;
flashback table flash to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
SQL> connect flash/flash
Connected.
SQL> flashback table flash to before drop;
Flashback complete.
SQL> select count(*) from flash;
COUNT(*)
----------
9
Flashback table drop with purge
By using flashback table drop with purge condition we cant retrieve table from recyclebin.table will be deleted permanently.so we will take the database to back in time using scn/timestamp.(flashback database to scn)
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1627479
SQL> drop table flash purge;
drop table flash purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect flash/flash
Connected.
SQL> drop table flash purge;
Table dropped.
SQL> select * from flash;
select * from flash
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from dba_recyclebin;
select * from dba_recyclebin
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect /as sysdba
Connected.
SQL> select * from dba_recyclebin;
no rows selected
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> flashback database to scn 1627479;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> connect flash/flash;
Connected.
SQL> select count(*) from flash;
COUNT(*)
----------
9
SQL> shu immediate;
ORA-01031: insufficient privileges
SQL> connect /as sysdba
Connected.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> connect flash/flash
Connected.
SQL> select * from flash;
ID
----------
46
23
46
23
46
23
46
23
123
9 rows selected.
SQL> connect /as sysdba
Connected.
Flashback tablespace recover:
If tablespace is dropped by using the flashback database to scn or timestamp to take the database to back in time.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\FLASH.DBF
10 rows selected.
SQL> create tablespace agile datafile 'e:\oracle\product\10.2.0\oradata\agile2\agile.dbf' size 5m;
Tablespace created.
SQL> create user flash1 identified by flash1 default tablespace flash1;
User created.
SQL> grant connect,resource to flash1;
Grant succeeded.
SQL> alter user flash1 default tablespace agile;
User altered.
SQL> connect flash1
Enter password: ******
Connected.
SQL> select * from tab;
no rows selected
SQL> create table flash (id number(33));
Table created.
SQL> insert into flash values (34);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from flash;
ID
----------
34
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$Database;
CURRENT_SCN
-----------
1629045
SQL> connect flash1/flash1
Connected.
SQL> drop tablespace agile including contents;
drop tablespace agile including contents
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$Database;
CURRENT_SCN
-----------
1629083
SQL> drop tablespace agile including contents;
Tablespace dropped.
SQL> select * from flash1.flash;
select * from flash1.flash
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> flashback database to scn 1629083;
flashback database to scn 1629083
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 11 will be lost if RESETLOGS is done
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00011'
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\FLASH.DBF
E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00011
11 rows selected.
SQL> alter database rename file 'E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00011' to 'e:\oracle\
product\10.2.0\oradata\agile2\agile.dbf';
Database altered.
SQL> flashback database to scn 1629083;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> connect flash1
Enter password: ******
Connected.
SQL> select * from flash;
ID
----------
34
SQL> connect /as sysdba
Connected.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from flash1.flash;
ID
----------
34
Flashback table drop
Flashback table drop with purge
Flashback tablespace recover
Flashback table to scn/timestamp
By using flashback table to scn/timestamp we will go back to the wrong updataion tn the table or retrieve the table from the delete table command.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
----------- 1626571
SQL> SELECT OPERATION FROM FLASHBACK_TRANSACTION_QUERY
2 WHERE START_SCN=1626271;
no rows selected
SQL> CONNECT FLASH/FLASH
Connected.
SQL> insert into flash values(123);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from flash;
ID
----------
46
23
46
23
46
23
46
23
123
9 rows selected.
SQL> alter table flash enable row moment;
alter table flash enable row moment
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> alter table flash enable row movement;
Table altered.
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1626794
SQL> select count(*) from flash;
select count(*) from flash
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect flash/flash
Connected.
SQL> select count(*) from flash;
COUNT(*)
----------
9
SQL> delete flash;
9 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from flash;
COUNT(*)
----------
0
SQL> select count(*) from flash as of scn 1626794;
COUNT(*)
----------
9
SQL> flashback table flash to scn 1626794;
Flashback complete.
SQL> select count(*) from flash;
COUNT(*)
----------
9
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1626997
SQL> insert into flash values(343);
1 row created.
SQL> insert into flash values(346);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> //
1 row created.
SQL> commit;
Commit complete.
SQL> select * from flash;
ID
----------
46
23
46
23
46
23
46
23
123
343
346
ID
346
346
346
346
346
346
346
346
19 rows selected.
SQL> flashback table flash to scn 1626997;
Flashback complete.
SQL> select * from flash;
ID
----------
46
23
46
23
46
23
46
23
123
9 rows selected.
SQL> select count(*) from flash;
COUNT(*)
9
Flashback table drop
By using this flashback table drop we retrieve the table from flashback drop command,the table is stored in the recyclebin.
SQL> connect /as sydba
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1627359
SQL> connect flash/flash
Connected.
SQL> drop table flash;
Table dropped.
SQL> select * from dba_recyclebin;
select * from dba_recyclebin
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect /as sysdba
Connected.
SQL> select * from dba_recyclebin;
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
FLASH BIN$IXgZGdTYRh24/5LbJ2qRhw==$0
FLASH DROP TABLE
FLASH 2012-04-25:16:05:26 2012-04-25:17:35:01
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
1627376 YES YES 53046 53046
53046 8
SQL> flashback table flash to before drop;
flashback table flash to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
SQL> connect flash/flash
Connected.
SQL> flashback table flash to before drop;
Flashback complete.
SQL> select count(*) from flash;
COUNT(*)
----------
9
Flashback table drop with purge
By using flashback table drop with purge condition we cant retrieve table from recyclebin.table will be deleted permanently.so we will take the database to back in time using scn/timestamp.(flashback database to scn)
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1627479
SQL> drop table flash purge;
drop table flash purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect flash/flash
Connected.
SQL> drop table flash purge;
Table dropped.
SQL> select * from flash;
select * from flash
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from dba_recyclebin;
select * from dba_recyclebin
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect /as sysdba
Connected.
SQL> select * from dba_recyclebin;
no rows selected
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> flashback database to scn 1627479;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> connect flash/flash;
Connected.
SQL> select count(*) from flash;
COUNT(*)
----------
9
SQL> shu immediate;
ORA-01031: insufficient privileges
SQL> connect /as sysdba
Connected.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> connect flash/flash
Connected.
SQL> select * from flash;
ID
----------
46
23
46
23
46
23
46
23
123
9 rows selected.
SQL> connect /as sysdba
Connected.
Flashback tablespace recover:
If tablespace is dropped by using the flashback database to scn or timestamp to take the database to back in time.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\FLASH.DBF
10 rows selected.
SQL> create tablespace agile datafile 'e:\oracle\product\10.2.0\oradata\agile2\agile.dbf' size 5m;
Tablespace created.
SQL> create user flash1 identified by flash1 default tablespace flash1;
User created.
SQL> grant connect,resource to flash1;
Grant succeeded.
SQL> alter user flash1 default tablespace agile;
User altered.
SQL> connect flash1
Enter password: ******
Connected.
SQL> select * from tab;
no rows selected
SQL> create table flash (id number(33));
Table created.
SQL> insert into flash values (34);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from flash;
ID
----------
34
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$Database;
CURRENT_SCN
-----------
1629045
SQL> connect flash1/flash1
Connected.
SQL> drop tablespace agile including contents;
drop tablespace agile including contents
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> connect /as sysdba
Connected.
SQL> select current_scn from v$Database;
CURRENT_SCN
-----------
1629083
SQL> drop tablespace agile including contents;
Tablespace dropped.
SQL> select * from flash1.flash;
select * from flash1.flash
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> flashback database to scn 1629083;
flashback database to scn 1629083
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 11 will be lost if RESETLOGS is done
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00011'
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\FLASH.DBF
E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00011
11 rows selected.
SQL> alter database rename file 'E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00011' to 'e:\oracle\
product\10.2.0\oradata\agile2\agile.dbf';
Database altered.
SQL> flashback database to scn 1629083;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> connect flash1
Enter password: ******
Connected.
SQL> select * from flash;
ID
----------
34
SQL> connect /as sysdba
Connected.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from flash1.flash;
ID
----------
34
No comments:
Post a Comment