Problem Description
Whenever you try to startup your database then it fails with ORA-38760.
SQL> startup
ORACLE instance started.
Total System Global Area 1161966444 bytes
Fixed Size 1787966 bytes
Variable Size 1145750568 bytes
Database Buffers 225163524 bytes
Redo Buffers 1262132 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database.
In the Alert log there goes entry like this.
Errors in file /oracle/admin/agprod/udump/agprod_ora_409616.trc:
ORA-38701: Flashback database log 402 seq 402 thread 1: "/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
The analogous of this error is during database running state whenever you delete the current flashbacklog, the database will crash with the following errors:
ORA-38701: Flashback database log 401 seq 401 thread 1:
"/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Sat Apr 11 00:03:12 2006
RVWR: terminating instance due to error 38701
Instance terminated by RVWR, pid = 5721
Cause of The Problem
The above errors occurred due to a well identified bug. If there is any I/O error that is preventing write to the flashback logs, the instance will crash. Losing the current flashback log will cause the instance to crash. Also during database startup if flashback logs are inaccessible then instance will crush.
Solution of The Problem
Solution A)-Upgarde Database Version:
This bug is affected from database version 10.1.0.1 to 10.1.0.4
It is fixed in database version 10gR2. So upgrade your database version.
Solution B)-Disable Flashback and restart the instance.
1)If you don't have the situation to upgrade the database then start the database in mount stage.
SQL> startup mount;
2)Turn off the Flashback
SQL> alter database flashback off;
3)Turn on the Flashback
SQL> alter database flashback on;
4)Startup the database
SQL>alter database open;
Or, SQL>shutdown
SQL>startup
Note:
After disabling flashback if you have guaranteed restore point then you still might get the error ORA-38760: This database instance failed to turn on flashback database while starting up the database. In this case you have to drop the guaranteed restore point.
You can see the available guaranteed restore point by,
select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
Then drop the guaranteed restore point by,
DROP RESTORE POINT NAME_OF_THE_GUARANTEED_RESTORE_POINT;
Now start your database.
Related Documents
Restore Point and Flashback Database.
Can you use flashback database if a period of noarchivelog exists
Performing Flashback Database
Showing posts with label Flashback. Show all posts
Showing posts with label Flashback. Show all posts
Tuesday, July 29, 2008
Wednesday, July 9, 2008
Version Query Pseudocolumns
The version query pseudocolumns are only valid for flashback version query which are discussed on Flashback Version Query.
The available version query pseudocolumns are,
1)VERSIONS_STARTTIME: This pseudocolumn returns the timestamp of the first version of the rows returned by the query.
2)VERSIONS_STARTSCN: This pseudocolumn returns the SCN of the first version of the rows returned by the query.
3)VERSIONS_ENDTIME: This pseudocolumn returns the timestamp of the last version of the rows returned by the query.
4)VERSIONS_ENDSCN: This pseudocolumn returns the SCN of the last version of the rows returned by the query.
5)VERSIONS_XID: This pseudocolumn returns the transaction ID of the transaction that created that row version.
6)VERSIONS_OPERATION: This pseudocolumn returns a single character representing the operation that caused that row version.
It can have I-For Insert Operation.
U-For Update Operation.
D-For Delete Operation.
The available version query pseudocolumns are,
1)VERSIONS_STARTTIME: This pseudocolumn returns the timestamp of the first version of the rows returned by the query.
2)VERSIONS_STARTSCN: This pseudocolumn returns the SCN of the first version of the rows returned by the query.
3)VERSIONS_ENDTIME: This pseudocolumn returns the timestamp of the last version of the rows returned by the query.
4)VERSIONS_ENDSCN: This pseudocolumn returns the SCN of the last version of the rows returned by the query.
5)VERSIONS_XID: This pseudocolumn returns the transaction ID of the transaction that created that row version.
6)VERSIONS_OPERATION: This pseudocolumn returns a single character representing the operation that caused that row version.
It can have I-For Insert Operation.
U-For Update Operation.
D-For Delete Operation.
Saturday, May 17, 2008
ORA-01466: unable to read data - table definition has changed
Problem description:
--------------------------------
Whenever you tried to flashback of a table immediately after creating table and inserting data into it, or you tried to flashback of the table to a time or to a SCN before creating it then flashback table or flashback query fails with error message,
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
Cause of The Problem:
----------------------------
If you give Flashback SCN or time before creating the table then it is expected as because the table was not there. But the question may come why after creating table the error appear. This is because smon_scn_time is updated every 6 seconds and hence that is the minimum time that the flashback query time needs to be behind the timestamp of the first change to the table. So, if after creating table within 5 seconds we run flashback query then error may come.
Workaround Example and Solution:
----------------------------------
In order to create the problem I have made a script named test_flashback.sql. The script will create table TEST_FLASHBACK_FEATURE and after inserting row it will select flashback SCN. Later we will play with that SCN.
1)Create the Script.
SQL> !vi /export/home/oracle/test_flashback.sql
CREATE TABLE TEST_FLASHBACK_FEATURE(A NUMBER)
/
INSERT INTO TEST_FLASHBACK_FEATURE VALUES(1)
/
COMMIT
/
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL
2.Execute The Script.
SQL> @/export/home/oracle/test_flashback.sql
Table created.
1 row created.
Commit complete.
GET_SYSTEM_CHANGE_NUMBER
------------------------
1371097
3.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
4.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
5.Now perform flashback 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 complete.
--------------------------------
Whenever you tried to flashback of a table immediately after creating table and inserting data into it, or you tried to flashback of the table to a time or to a SCN before creating it then flashback table or flashback query fails with error message,
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
Cause of The Problem:
----------------------------
If you give Flashback SCN or time before creating the table then it is expected as because the table was not there. But the question may come why after creating table the error appear. This is because smon_scn_time is updated every 6 seconds and hence that is the minimum time that the flashback query time needs to be behind the timestamp of the first change to the table. So, if after creating table within 5 seconds we run flashback query then error may come.
Workaround Example and Solution:
----------------------------------
In order to create the problem I have made a script named test_flashback.sql. The script will create table TEST_FLASHBACK_FEATURE and after inserting row it will select flashback SCN. Later we will play with that SCN.
1)Create the Script.
SQL> !vi /export/home/oracle/test_flashback.sql
CREATE TABLE TEST_FLASHBACK_FEATURE(A NUMBER)
/
INSERT INTO TEST_FLASHBACK_FEATURE VALUES(1)
/
COMMIT
/
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL
2.Execute The Script.
SQL> @/export/home/oracle/test_flashback.sql
Table created.
1 row created.
Commit complete.
GET_SYSTEM_CHANGE_NUMBER
------------------------
1371097
3.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
4.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
5.Now perform flashback 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 complete.
Thursday, May 15, 2008
ORA-08189: cannot flashback row movement is not enabled
Error Description:
-----------------------
Whenever I tried to Flashback Table feature of oracle the following error occurs.
SQL> FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1;
FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
Cause of The Problem:
---------------------------
Whenever you performed flashback operation(flashback table, flashback version query,flashback transaction query) of a table except flashback drop feature(which don't need to enable row movement as whole objects come from recycle bin) then above error will come.
Solution of The Problem:
------------------------------------
Row movement of the table need to be enabled in order to perform flashback table.
1)To see whether row movement enabled or disabled issue the following,
SQL> SELECT TABLE_NAME,ROW_MOVEMENT FROM USER_TABLES WHERE TABLE_NAME='TEST_NUM';
TABLE_NAME ROW_MOVE
------------------------------ --------
TEST_NUM DISABLED
2)In order to perform flashback table enable row movement by following query,
SQL> ALTER TABLE TEST_NUM ENABLE ROW MOVEMENT;
Table altered.
Check it by,
SQL> SELECT TABLE_NAME,ROW_MOVEMENT FROM USER_TABLES WHERE TABLE_NAME='TEST_NUM';
TABLE_NAME ROW_MOVE
------------------------------ --------
TEST_NUM ENABLED
3)Now perform flashback operation.
SQL> FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1;
Flashback complete.
-----------------------
Whenever I tried to Flashback Table feature of oracle the following error occurs.
SQL> FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1;
FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
Cause of The Problem:
---------------------------
Whenever you performed flashback operation(flashback table, flashback version query,flashback transaction query) of a table except flashback drop feature(which don't need to enable row movement as whole objects come from recycle bin) then above error will come.
Solution of The Problem:
------------------------------------
Row movement of the table need to be enabled in order to perform flashback table.
1)To see whether row movement enabled or disabled issue the following,
SQL> SELECT TABLE_NAME,ROW_MOVEMENT FROM USER_TABLES WHERE TABLE_NAME='TEST_NUM';
TABLE_NAME ROW_MOVE
------------------------------ --------
TEST_NUM DISABLED
2)In order to perform flashback table enable row movement by following query,
SQL> ALTER TABLE TEST_NUM ENABLE ROW MOVEMENT;
Table altered.
Check it by,
SQL> SELECT TABLE_NAME,ROW_MOVEMENT FROM USER_TABLES WHERE TABLE_NAME='TEST_NUM';
TABLE_NAME ROW_MOVE
------------------------------ --------
TEST_NUM ENABLED
3)Now perform flashback operation.
SQL> FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1;
Flashback complete.
Related Documents
Drop Table in Oracle
Flashback Table and Flashback Drop
Drop Table in Oracle
Flashback Table and Flashback Drop
Can you use flashback database if a period of noarchivelog exists
The scenario is I have enabled flashback feature. Now I have ran the database in noarchivelog mode for some time and then I again enable archivelog mode. Now can I get back to a point to previous flashback point. The answer is no. The minimum requirement to enable flashback database is to keep the database in Archive log mode.
Because in order to switch from noarchivelog mode to archivelog mode you have to disable flashback logging and so you lose the logs unless you have guaranteed restore point.
But if you have guaranteed restore point then you can't switch from noarchivelog mode to archivelog mode and hence you can perform flashback.
In the following section the whole scenario is described with example.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> create restore point normal;
Restore point created.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
database mounted.
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled
SQL> alter database flashback off;
Database altered.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> flashback database to restore point normal;
flashback database to restore point normal
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
SQL> select name from v$restore_point;
NAME
--------------------------------------------------------------------------------
NORMAL
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> create restore point g_restore guarantee flashback database;
Restore point created.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
Database mounted.
SQL> alter database flashback off;
Database altered.
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points
So, the minimum requirement to enable flashback database is to keep the database in Archive log mode.
Related Documents
arjudba.blogspot.com/2008/04/performing-flashback-database.html
arjudba.blogspot.com/2008/04/restore-point-and-flashback-database.html
arjudba.blogspot.com/2008/05/ora-08189-cannot-flashback-row-movement.html
arjudba.blogspot.com/2008/05/flashback-on-fails-with-ora-38706-and.html
arjudba.blogspot.com/2008/04/how-to-enable-flashback-database.html
arjudba.blogspot.com/2008/04/flashback-database-to-undo-open.html
Because in order to switch from noarchivelog mode to archivelog mode you have to disable flashback logging and so you lose the logs unless you have guaranteed restore point.
But if you have guaranteed restore point then you can't switch from noarchivelog mode to archivelog mode and hence you can perform flashback.
In the following section the whole scenario is described with example.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> create restore point normal;
Restore point created.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
database mounted.
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled
SQL> alter database flashback off;
Database altered.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> flashback database to restore point normal;
flashback database to restore point normal
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
SQL> select name from v$restore_point;
NAME
--------------------------------------------------------------------------------
NORMAL
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> create restore point g_restore guarantee flashback database;
Restore point created.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
Database mounted.
SQL> alter database flashback off;
Database altered.
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points
So, the minimum requirement to enable flashback database is to keep the database in Archive log mode.
Related Documents
arjudba.blogspot.com/2008/04/performing-flashback-database.html
arjudba.blogspot.com/2008/04/restore-point-and-flashback-database.html
arjudba.blogspot.com/2008/05/ora-08189-cannot-flashback-row-movement.html
arjudba.blogspot.com/2008/05/flashback-on-fails-with-ora-38706-and.html
arjudba.blogspot.com/2008/04/how-to-enable-flashback-database.html
arjudba.blogspot.com/2008/04/flashback-database-to-undo-open.html
Flashback ON fails with ORA-38706 and ORA-38714
Problem Description:
-------------------------
Whenever you tried to enable your database flashback feature it failed with errors.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.
Cause of the problem:
-------------------------
The database is abnormally terminated. In Order to Enable the Flashback option the database need to be cleaned shutdown. Enabling flashback database after crash or shutdown abort will lead to this error.
Solution of The Problem:
------------------------------
1.Cleanly Shutdown of your database.
SHUTDOWN
or SHUTDOWN IMMEDIATE
or, SHUTDOWN TRANSACTIONAL
2.Mount the database.
SQL>ALTER DATABASE MOUNT;
3.Enable flashback.
SQL>Alter database flashback on;
4.Open the database.
SQL>ALTER DATABASE OPEN;
-------------------------
Whenever you tried to enable your database flashback feature it failed with errors.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.
Cause of the problem:
-------------------------
The database is abnormally terminated. In Order to Enable the Flashback option the database need to be cleaned shutdown. Enabling flashback database after crash or shutdown abort will lead to this error.
Solution of The Problem:
------------------------------
1.Cleanly Shutdown of your database.
SHUTDOWN
or SHUTDOWN IMMEDIATE
or, SHUTDOWN TRANSACTIONAL
2.Mount the database.
SQL>ALTER DATABASE MOUNT;
3.Enable flashback.
SQL>Alter database flashback on;
4.Open the database.
SQL>ALTER DATABASE OPEN;
Tuesday, April 29, 2008
How to get back dropped Tablespace using Flashback Database.
It needs to remember that if you drop your tablespace with including datafiles option then it is not possible to get your tablespace back with flashback database. While dropping if you use DROP TABLESPACE INCLUDING CONTENTS option then it is possible to get back tablespace.
Procedures with and Example:
---------------------------------
1)Create a Tablespace and Table inside it.
SQL> create tablespace test_restore datafile '/oradata2/test_restore01.dbf' size 5m;
Tablespace created.
SQL> create table test tablespace test_restore as select level a1 from dual connect by level <99;Table created.
2)Note the SCN and Drop the Tablespace with including contents option.
---------------------------------------------------------------------------
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
938686
SQL> drop tablespace test_restore including contents;
Tablespace dropped.
3)Mount the database.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 104857640 bytes
Database Buffers 54525952 bytes
Redo Buffers 6369280 bytes
Database mounted.
4)Perform FlashBack.
SQL> flashback database to scn 938686;
flashback database to scn 938686
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'
5)The datafile Become Unnamed. So rename it with original data file location.
SQL> alter database rename file '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' to
2 '/oradata2/test_restore01.dbf';
Database altered.
6)Now perforem Flashback and Open the database with read only mode.
SQL> flashback database to scn 938686;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> select count(*) from test;
COUNT(*)
----------
98
7)Now you can follow the step 6 choice b)in Peforming Flashback (export) and then recover database. And later create tablespace and import the contents of tablespace.
Related Document:
-------------------
Perform Flashback Database
Limitations of Flashback Database
Procedures with and Example:
---------------------------------
1)Create a Tablespace and Table inside it.
SQL> create tablespace test_restore datafile '/oradata2/test_restore01.dbf' size 5m;
Tablespace created.
SQL> create table test tablespace test_restore as select level a1 from dual connect by level <99;Table created.
2)Note the SCN and Drop the Tablespace with including contents option.
---------------------------------------------------------------------------
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
938686
SQL> drop tablespace test_restore including contents;
Tablespace dropped.
3)Mount the database.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 104857640 bytes
Database Buffers 54525952 bytes
Redo Buffers 6369280 bytes
Database mounted.
4)Perform FlashBack.
SQL> flashback database to scn 938686;
flashback database to scn 938686
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'
5)The datafile Become Unnamed. So rename it with original data file location.
SQL> alter database rename file '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' to
2 '/oradata2/test_restore01.dbf';
Database altered.
6)Now perforem Flashback and Open the database with read only mode.
SQL> flashback database to scn 938686;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> select count(*) from test;
COUNT(*)
----------
98
7)Now you can follow the step 6 choice b)in Peforming Flashback (export) and then recover database. And later create tablespace and import the contents of tablespace.
Related Document:
-------------------
Perform Flashback Database
Limitations of Flashback Database
Monday, April 28, 2008
How to restore the old data using flashback query
My intention is , I want to get back past data of database after erroneously updated and committed.
We know that committed data can never be flashed back. But with 10g new flashback feature we can get back past data even they are committed.
Before proceed ensure that,
•The UNDO_RETENTION initialization parameter is set to a value so that you can back your data far in the past that you might want to query.
•UNDO_MANAGEMENT is set to AUTO.
•In your UNDO TABLESPACE you have enough space.
With an example I will demonstrate the whole procedure.
1)I have created a table named test_flash_table with column name and salary.
SQL> create table test_flash_table(name varchar2(10), salary number);
Table created.
SQL> insert into test_flash_table values('Arju',10);
1 row created.
SQL> commit;
Commit complete.
The table contains one row.
2)I erroneously updated column salary of Arju and commited data.
SQL> update test_flash_table set salary=20 where name='Arju';
1 row updated.
SQL> commit;
Commit complete.
3)After some moments I found that I have made wrong update. Now be sure to query. Also select that time SCN by TIMESTAMP_TO_SCN.
SQL> select name, salary,systimestamp, TIMESTAMP_TO_SCN(SYSTIMESTAMP-interval '8' minute) SCN from test_flash_table as of timestamp (SYSTIMESTAMP-interval '8' Minute);
NAME SALARY SYSTIMESTAMP SCN
---------- ---------- ---------------------------------------- ----------
Arju 10 29-APR-08 09.34.03.452330 AM -04:00 869222
4)Now update the data based on the SCN.
SQL> update test_flash_table set salary=(select salary from test_flash_table as of scn 869222 where name='Arju') where name='Arju';
1 row updated.
SQL> select * from test_flash_table where name='Arju';
NAME SALARY
---------- ----------
Arju 10
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
We know that committed data can never be flashed back. But with 10g new flashback feature we can get back past data even they are committed.
Before proceed ensure that,
•The UNDO_RETENTION initialization parameter is set to a value so that you can back your data far in the past that you might want to query.
•UNDO_MANAGEMENT is set to AUTO.
•In your UNDO TABLESPACE you have enough space.
With an example I will demonstrate the whole procedure.
1)I have created a table named test_flash_table with column name and salary.
SQL> create table test_flash_table(name varchar2(10), salary number);
Table created.
SQL> insert into test_flash_table values('Arju',10);
1 row created.
SQL> commit;
Commit complete.
The table contains one row.
2)I erroneously updated column salary of Arju and commited data.
SQL> update test_flash_table set salary=20 where name='Arju';
1 row updated.
SQL> commit;
Commit complete.
3)After some moments I found that I have made wrong update. Now be sure to query. Also select that time SCN by TIMESTAMP_TO_SCN.
SQL> select name, salary,systimestamp, TIMESTAMP_TO_SCN(SYSTIMESTAMP-interval '8' minute) SCN from test_flash_table as of timestamp (SYSTIMESTAMP-interval '8' Minute);
NAME SALARY SYSTIMESTAMP SCN
---------- ---------- ---------------------------------------- ----------
Arju 10 29-APR-08 09.34.03.452330 AM -04:00 869222
4)Now update the data based on the SCN.
SQL> update test_flash_table set salary=(select salary from test_flash_table as of scn 869222 where name='Arju') where name='Arju';
1 row updated.
SQL> select * from test_flash_table where name='Arju';
NAME SALARY
---------- ----------
Arju 10
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
Sunday, April 27, 2008
Limitation of Flashback Database.
The Flashback Database allows you to get back the entire database to a specific point-in-time.
It is best used as a replacement for incomplete recovery of a complete database. The main benefit of the Oracle Flashback Database over incomplete database recovery is that the Flashback Database is much quicker and more efficient. The Flashback Database is based on flashback logs.
It has some limitations such as,
•Flashback Database can only undo changes to a datafile made by an Oracle database. If any media failure occurs in database then flashback database can't be used. That means if a datafile is dropped then we can't use flashback feature.
•If you have shrink your any datafile then flashback database can't be used.
•If the database control file is restored from backup or re-created, then you cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
•If you get back to a time by flashback database when nologging operation was running then it is likely happen block corruption on database.
It is best used as a replacement for incomplete recovery of a complete database. The main benefit of the Oracle Flashback Database over incomplete database recovery is that the Flashback Database is much quicker and more efficient. The Flashback Database is based on flashback logs.
It has some limitations such as,
•Flashback Database can only undo changes to a datafile made by an Oracle database. If any media failure occurs in database then flashback database can't be used. That means if a datafile is dropped then we can't use flashback feature.
•If you have shrink your any datafile then flashback database can't be used.
•If the database control file is restored from backup or re-created, then you cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
•If you get back to a time by flashback database when nologging operation was running then it is likely happen block corruption on database.
Flashback Database To The Right of Open Resetlogs
In some cases, you may need to return the database to a point in time in the parent incarnation, later than the SCN of the OPEN RESETLOGS at which the current incarnation path branched from the old incarnation.
The process is described below.
1)Verify that the flashback logs contain enough information to flash back.
SQL> select oldest_flashback_scn from v$flashback_database_log;
2)Determine the target incarnation number for the flashback, that is, the incarnation key for the parent incarnation.
SQL> select prior_incarnation# from v$database_incarnation where status ='CURRENT';
3)Start the database in mount stage.
RMAN>SHUTDOWN IMMEDIATE;
RMAN>START THE MOUNT;
4)Issue List Incarnation to see the SCN.
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DATA1 2547250380 PARENT 1 17-MAR-06
2 2 DATA1 2547250380 PARENT 526290 23-APR-08
3 3 DATA1 2547250380 ORPHAN 789429 27-APR-08
4 4 DATA1 2547250380 PARENT 789429 28-APR-08
5 5 DATA1 2547250380 ORPHAN 792942 28-APR-08
6 6 DATA1 2547250380 PARENT 792942 28-APR-08
7 7 DATA1 2547250380 CURRENT 793335 28-APR-08
5)Re-check the OLDEST_FLASHBACK_SCN again.
SQL> select oldest_flashback_scn from v$flashback_database_log;
OLDEST_FLASHBACK_SCN
--------------------
786801
6)Set the database incarnation to the parent incarnation:
RMAN> RESET DATABASE TO INCARNATION 6;
7)Run the FLASHBACK DATABASE command:
RMAN> flashback database to scn 792942;
8)Open the database read only and see the contents. Either OPEN it with RESETLOGS option or export/import data and RECOVER DATABASE.
The process is described below.
1)Verify that the flashback logs contain enough information to flash back.
SQL> select oldest_flashback_scn from v$flashback_database_log;
2)Determine the target incarnation number for the flashback, that is, the incarnation key for the parent incarnation.
SQL> select prior_incarnation# from v$database_incarnation where status ='CURRENT';
3)Start the database in mount stage.
RMAN>SHUTDOWN IMMEDIATE;
RMAN>START THE MOUNT;
4)Issue List Incarnation to see the SCN.
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DATA1 2547250380 PARENT 1 17-MAR-06
2 2 DATA1 2547250380 PARENT 526290 23-APR-08
3 3 DATA1 2547250380 ORPHAN 789429 27-APR-08
4 4 DATA1 2547250380 PARENT 789429 28-APR-08
5 5 DATA1 2547250380 ORPHAN 792942 28-APR-08
6 6 DATA1 2547250380 PARENT 792942 28-APR-08
7 7 DATA1 2547250380 CURRENT 793335 28-APR-08
5)Re-check the OLDEST_FLASHBACK_SCN again.
SQL> select oldest_flashback_scn from v$flashback_database_log;
OLDEST_FLASHBACK_SCN
--------------------
786801
6)Set the database incarnation to the parent incarnation:
RMAN> RESET DATABASE TO INCARNATION 6;
7)Run the FLASHBACK DATABASE command:
RMAN> flashback database to scn 792942;
8)Open the database read only and see the contents. Either OPEN it with RESETLOGS option or export/import data and RECOVER DATABASE.
Flashback Database to Undo an OPEN RESETLOGS
Suppose you have opened your database with OPEN RESETLOGS option after performing flashback feature. And after that you have made unwanted changes in your database.
So, now you want to get your database prior to RESETLOGS option. Then you have to do following.
1)Verify the flashback window:
Before performing the flashback, verify that the beginning of the flashback window is earlier than the time of the most recent OPEN RESETLOGS.
sql> select resetlogs_change# from v$database;
sql> select oldest_flashback_scn from v$flashback_database_log;
If V$DATABASE.RESETLOGS_CHANGE# is greater than V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN, then you can use Flashback Database to reverse the OPEN RESETLOGS.
2)If step 1 is ok then shutdown the database and mount it. Now check the step 1 again. If the target SCN is prior to the beginning of the flashback database window, an error is returned and the database is not modified.
3)Perform the Operation:
SQL> FLASHBACK DATABASE TO BEFORE RESETLOGS;
4)Open the database with read only and check your desired obejcts.
SQL> alter database open read only;
5)Follow step 6 of Step 6
So, now you want to get your database prior to RESETLOGS option. Then you have to do following.
1)Verify the flashback window:
Before performing the flashback, verify that the beginning of the flashback window is earlier than the time of the most recent OPEN RESETLOGS.
sql> select resetlogs_change# from v$database;
sql> select oldest_flashback_scn from v$flashback_database_log;
If V$DATABASE.RESETLOGS_CHANGE# is greater than V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN, then you can use Flashback Database to reverse the OPEN RESETLOGS.
2)If step 1 is ok then shutdown the database and mount it. Now check the step 1 again. If the target SCN is prior to the beginning of the flashback database window, an error is returned and the database is not modified.
3)Perform the Operation:
SQL> FLASHBACK DATABASE TO BEFORE RESETLOGS;
4)Open the database with read only and check your desired obejcts.
SQL> alter database open read only;
5)Follow step 6 of Step 6
Performing Flashback Database
Before going any production upgrade to database we can make a guaranteed restore point to database and if any wrong then we can get back to the restore point state. Guaranteed restore point always ensure that we can get back data to our restore point creation time.
Suppose before upgradation to database I have made an guaranteed restore point to database like,
SQL> create restore point before_upgrade guarantee flashback database;
Restore point created.
SQL> conn arju/a
Connected.
SQL> select count(*) from user_tables;
COUNT(*)
----------
4
SQL> create table after_restore_point (col1 number);
Table created.
SQL> desc after_restore_point;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
Now I want to perform flashback database to get back database at the time of creating restore point. To do it follow the following steps,
1. Determine the desired SCN, restore point or point in time for the FLASHBACK DATABASE command.
SQL> select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
NAME SCN GUA DATABASE_INCARNATION#
------------------------------ ---------- --- ---------------------
BEFORE_UPGRADE 787027 YES 2
2.Shutdown the database cleanly and mount it.
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
3.Determine the current window for flashback database.
When flashback logging is enabled, the earliest SCN in the flashback database window can be determined by querying V$FLASHBACK_DATABASE_LOG.
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
If you attempt FLASHBACK DATABASE and your target SCN is now outside the flashback window, then FLASHBACK DATABASE will fail with an ORA-38729 error.
So, you can't get back to state to an SCN before OLDEST_FLASHBACK_SCN.
4.Run your RMAN flashback command your restore point or to the desired SCN or to your desired time.
In this case I ran,
SQL> FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
Flashback complete.
You can also use TO SCN Clause and TO TIMESTAMP Clause with FLASHBACK DATABASE.
5. You can verify that you have returned the database to the desired state, by opening the
database read-only and performing some queries to inspect the database contents.Like I perform some operations,
SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
SQL> conn arju/a
Connected.
SQL> select count(*) from tabs;
COUNT(*)
----------
4
SQL> desc after_restore_point;
ERROR:
ORA-04043: object after_restore_point does not exist
6) If you are satisfied with the state of your database about flashback then you have two choices.
Choice a) Make the database available for updates by open resetlogs, Once you perform this OPEN RESETLOGS operation, all changes to the database after the target SCN for FLASHBACK DATABASE are abandoned.
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>alter database open RESETLOGS;
Choice b) Use the export/data pump export utility to export the objects whose state was corrupted. Then, recover the database to the present time:
RMAN> RECOVER DATABASE;
This step undoes the effect of the Flashback Database, by re-applying all changes in the redo logs to the database, returning it to the most recent SCN.
After re-opening the database read-write, you can import the exported objects using the import utility corresponding to the export utility used earlier.
Suppose before upgradation to database I have made an guaranteed restore point to database like,
SQL> create restore point before_upgrade guarantee flashback database;
Restore point created.
SQL> conn arju/a
Connected.
SQL> select count(*) from user_tables;
COUNT(*)
----------
4
SQL> create table after_restore_point (col1 number);
Table created.
SQL> desc after_restore_point;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
Now I want to perform flashback database to get back database at the time of creating restore point. To do it follow the following steps,
1. Determine the desired SCN, restore point or point in time for the FLASHBACK DATABASE command.
SQL> select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
NAME SCN GUA DATABASE_INCARNATION#
------------------------------ ---------- --- ---------------------
BEFORE_UPGRADE 787027 YES 2
2.Shutdown the database cleanly and mount it.
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
3.Determine the current window for flashback database.
When flashback logging is enabled, the earliest SCN in the flashback database window can be determined by querying V$FLASHBACK_DATABASE_LOG.
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
If you attempt FLASHBACK DATABASE and your target SCN is now outside the flashback window, then FLASHBACK DATABASE will fail with an ORA-38729 error.
So, you can't get back to state to an SCN before OLDEST_FLASHBACK_SCN.
4.Run your RMAN flashback command your restore point or to the desired SCN or to your desired time.
In this case I ran,
SQL> FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
Flashback complete.
You can also use TO SCN Clause and TO TIMESTAMP Clause with FLASHBACK DATABASE.
5. You can verify that you have returned the database to the desired state, by opening the
database read-only and performing some queries to inspect the database contents.Like I perform some operations,
SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
SQL> conn arju/a
Connected.
SQL> select count(*) from tabs;
COUNT(*)
----------
4
SQL> desc after_restore_point;
ERROR:
ORA-04043: object after_restore_point does not exist
6) If you are satisfied with the state of your database about flashback then you have two choices.
Choice a) Make the database available for updates by open resetlogs, Once you perform this OPEN RESETLOGS operation, all changes to the database after the target SCN for FLASHBACK DATABASE are abandoned.
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>alter database open RESETLOGS;
Choice b) Use the export/data pump export utility to export the objects whose state was corrupted. Then, recover the database to the present time:
RMAN> RECOVER DATABASE;
This step undoes the effect of the Flashback Database, by re-applying all changes in the redo logs to the database, returning it to the most recent SCN.
After re-opening the database read-write, you can import the exported objects using the import utility corresponding to the export utility used earlier.
Saturday, April 26, 2008
ORA-00439: feature not enabled: Flashback Database
Error Description:
-------------------
While trying to enable flashback feature the error comes.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database
Error Explanation:
--------------------
Some feature is is not enabled in oracle database standard edition or Oracle database Standard Edition One. The most common feature is Real Application Clusters, Online index maintenance, Online table organization, Online table redefinition, Block-level media recovery,Parallel backup and recovery,Point-in-time tablespace recovery ,RMAN Backup Encryption, Unused Block Compression, Oracle Flashback Table, Oracle Flashback Database, Oracle Flashback Transaction Query, Restore Points.
So flashback database is disabled in standard edition or in standard one edition. To be able to flashback on you have to use enterprise edition.
You can check from,
Version:
---------
1)SQL> SELECT * FROM V$VERSION;
To know the options enabled in your database,
2)SQL> SELECT * FROM V$OPTION;
-------------------
While trying to enable flashback feature the error comes.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database
Error Explanation:
--------------------
Some feature is is not enabled in oracle database standard edition or Oracle database Standard Edition One. The most common feature is Real Application Clusters, Online index maintenance, Online table organization, Online table redefinition, Block-level media recovery,Parallel backup and recovery,Point-in-time tablespace recovery ,RMAN Backup Encryption, Unused Block Compression, Oracle Flashback Table, Oracle Flashback Database, Oracle Flashback Transaction Query, Restore Points.
So flashback database is disabled in standard edition or in standard one edition. To be able to flashback on you have to use enterprise edition.
You can check from,
Version:
---------
1)SQL> SELECT * FROM V$VERSION;
To know the options enabled in your database,
2)SQL> SELECT * FROM V$OPTION;
Restore Point and Flashback Database.
Flashback database enables us to get back our database to a prior time.
And with restore point we can make a point before any database upgrade and if there is any problem we can get back to that point.
Restore point can be two types.
1)Normal Restore point:
---------------------------
Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.
In normal restore point Flashback logs are deleted in response to space pressure.
To create a restore point simply use,
CREATE RESTORE POINT before_upgrade;
2)Guaranteed Restore Point:
-------------------------------
In guaranteed restore point Flashback logs are not deleted in response to space pressure, if they are required to satisfy the guarantee.
Guaranteed restore point can be created whether your flashback feature on or off.
If a guaranteed restore point is created when Flashback Database is disabled, then, the first time a datafile block is modified after the time of the guaranteed restore point, an image of the block before the modification is stored in the flashback logs but subsequent modifications to the same block do not cause the block contents to be logged again. In this case you cannot use FLASHBACK DATABASE to reach points in time between the guaranteed restore points and the current time.
If a guaranteed restore point is created when Flashback Database is enabled, then the database perform normal flashback operations and create logs. The flash recovery area always retains the flashback logs required to allow FLASHBACK DATABASE to any time as far back as the earliest
currently defined guaranteed restore point. In this case you can use FLASHBACK DATABASE to reach points in time between the guaranteed restore points and the current time.
To create a guaranteed restore point use,
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
You can see your restore points from V$RESTORE_POINT.
To drop a restore point use
SQL> DROP RESTORE POINT before_app_upgrade;
And with restore point we can make a point before any database upgrade and if there is any problem we can get back to that point.
Restore point can be two types.
1)Normal Restore point:
---------------------------
Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.
In normal restore point Flashback logs are deleted in response to space pressure.
To create a restore point simply use,
CREATE RESTORE POINT before_upgrade;
2)Guaranteed Restore Point:
-------------------------------
In guaranteed restore point Flashback logs are not deleted in response to space pressure, if they are required to satisfy the guarantee.
Guaranteed restore point can be created whether your flashback feature on or off.
If a guaranteed restore point is created when Flashback Database is disabled, then, the first time a datafile block is modified after the time of the guaranteed restore point, an image of the block before the modification is stored in the flashback logs but subsequent modifications to the same block do not cause the block contents to be logged again. In this case you cannot use FLASHBACK DATABASE to reach points in time between the guaranteed restore points and the current time.
If a guaranteed restore point is created when Flashback Database is enabled, then the database perform normal flashback operations and create logs. The flash recovery area always retains the flashback logs required to allow FLASHBACK DATABASE to any time as far back as the earliest
currently defined guaranteed restore point. In this case you can use FLASHBACK DATABASE to reach points in time between the guaranteed restore points and the current time.
To create a guaranteed restore point use,
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
You can see your restore points from V$RESTORE_POINT.
To drop a restore point use
SQL> DROP RESTORE POINT before_app_upgrade;
Thursday, April 24, 2008
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;
Database altered.
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;
Database altered.
Tuesday, April 22, 2008
The Oracle Flashback Technology
Oracle Flashback Technology provides a set of features that provide useful alternatives to view past states of data, and winding data back and forth in time, without requiring you to restore large portions of your database from backup or perform point-in-time recovery. The flashback features of Oracle are more efficient and less disruptive than media recovery in most circumstances.
The available Flashback Features:
---------------------------------
A)Oracle Flashback Query: It lets you to view past state data as they would have appeared at that time.
It is based on undo data. Show UNDO_RETENTION parameter.
B)Oracle Flashback Version Query: It lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval.
It is based on undo data. Show UNDO_RETENTION parameter.
C)Oracle Flashback Transaction Query: It lets you view changes made by a single transaction, or by all the transactions during a period of time.
It is based on undo data. Show UNDO_RETENTION parameter.
D)Oracle Flashback Table: It returns a table to its state at a previous point in time.
It is based on undo data. Show UNDO_RETENTION parameter.
E)Oracle Flashback Drop: To get back a dropped table.
It is based on Recyclebin. Use show recyclebin.
F)Oracle Flashback Database: To get back whole database to a previous point in time. It is based on whether flashback feature is enabled or not. Use select flashback_on from V$database; to see it is on or not. By default it is disabled. However you can enable it by ALTER DATBASE FLASHBACK ON in mount stage.
You can have a look at Flashback Database
Flashback query is discussed on Oracle Flashback query
Flashback version query, flashback transaction query is discussed on Flashback Features
Flashback Table, flashback Drop is discussed on Flashback Table And Flashback Drop
The available Flashback Features:
---------------------------------
A)Oracle Flashback Query: It lets you to view past state data as they would have appeared at that time.
It is based on undo data. Show UNDO_RETENTION parameter.
B)Oracle Flashback Version Query: It lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval.
It is based on undo data. Show UNDO_RETENTION parameter.
C)Oracle Flashback Transaction Query: It lets you view changes made by a single transaction, or by all the transactions during a period of time.
It is based on undo data. Show UNDO_RETENTION parameter.
D)Oracle Flashback Table: It returns a table to its state at a previous point in time.
It is based on undo data. Show UNDO_RETENTION parameter.
E)Oracle Flashback Drop: To get back a dropped table.
It is based on Recyclebin. Use show recyclebin.
F)Oracle Flashback Database: To get back whole database to a previous point in time. It is based on whether flashback feature is enabled or not. Use select flashback_on from V$database; to see it is on or not. By default it is disabled. However you can enable it by ALTER DATBASE FLASHBACK ON in mount stage.
You can have a look at Flashback Database
Flashback query is discussed on Oracle Flashback query
Flashback version query, flashback transaction query is discussed on Flashback Features
Flashback Table, flashback Drop is discussed on Flashback Table And Flashback Drop
Wednesday, April 9, 2008
Flashback Version Query
This is a new feature comes in Oracle 10g. With this you can also get back you present table to a previous state. Like you mistakenly deleted some rows and you have committed and now you want to get back your data.However, it depends on the availability of the undo information in the database, so if the undo information has been aged out, this approach will fail.
Here is an example for your better understanding,
Flashback Table
SQL> flashback table t to timestamp systimestamp-interval '1' minute;
Flashback complete.
Flashback Versions Query
You can also keep track of change data over time suppose when one row is upadted when one row is deleted and when one row is inserted by following query,
select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;
You can find out the changes over time by ,
select a, versions_starttime, versions_endtime
from t versions between timestamp
to_date('7/3/20078 13:33:54','mm/dd/yyyy hh24:mi:ss')
and to_date('7/3/2008 13:37:55','mm/dd/yyyy hh24:mi:ss')
Flashback Transaction Query
You can check undo_sql by,
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = '....';
Flashback Query
SQL> select * from flash;
A
----------
1
2
SQL> insert into flash values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from flash as of timestamp systimestamp-interval '1' minute;
A
----------
1
2
SQL> select * from flash;
A
----------
1
2
3
In order to know more about it have a look at,
Restore Old Data from Flashback Query
Related Documents:
Restore Old Data from Flashback Query
Oracle Flashback Technology
Oracle Flashback Drop
Here is an example for your better understanding,
SQL> select * from t;
A
----------
1
100
10
SQL> insert into t values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
A
----------
1
100
10
2
Flashback Table
SQL> flashback table t to timestamp systimestamp-interval '1' minute;
Flashback complete.
SQL> select * from t;
A
----------
1
100
10
Flashback Versions Query
You can also keep track of change data over time suppose when one row is upadted when one row is deleted and when one row is inserted by following query,
select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;
SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;
Output is,
VERSIONS_STARTT VERSIONS_ENDTIME VERSIONS_XID V A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I 5
.16 PM M
09-APR-08 02.47 0700100035010000 D 5
.26 PM
100
10
1
SQL> insert into t values(200);
1 row created.
SQL> commit;
Commit complete.
SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;
VERSIONS_STARTT VERSIONS_ENDTIME VERSIONS_XID V A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I 5
.16 PM M
09-APR-08 02.47 0700100035010000 D 5
.26 PM
09-APR-08 02.49 0700140035010000 I 200
.35 PM
10
1
100
6 rows selected.
You can find out the changes over time by ,
select a, versions_starttime, versions_endtime
from t versions between timestamp
to_date('7/3/20078 13:33:54','mm/dd/yyyy hh24:mi:ss')
and to_date('7/3/2008 13:37:55','mm/dd/yyyy hh24:mi:ss')
Flashback Transaction Query
You can check undo_sql by,
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = '....';
Flashback Query
SQL> select * from flash;
A
----------
1
2
SQL> insert into flash values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from flash as of timestamp systimestamp-interval '1' minute;
A
----------
1
2
SQL> select * from flash;
A
----------
1
2
3
In order to know more about it have a look at,
Restore Old Data from Flashback Query
Related Documents:
Restore Old Data from Flashback Query
Oracle Flashback Technology
Oracle Flashback Drop
Tuesday, April 8, 2008
Flashback Table and Flashback Drop
The FLASHBACK TABLE statement enables users to get a table to a previous point in time. It provides a fast, online solution to get back a table that has been accidentally modified or deleted by a user or application. It eliminates the DBA to perform more complicated point in time recovery operations.
We should remember the thing is you must use automatic undo management to use the Flashback Table feature. It is based on undo information stored in an undo tablespace.
Now let's go for experiment.
Flashback Drop
1)SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
2)Drop the Table,
SQL> drop table test;
Table dropped.
3)Now see the Status,
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$SnQhAfQtUQzgRAAZ0Xbz6g==$0 TABLE
In fact when we issue drop table command the object resides on recycle bin. We can see the recycle bin objects from dba_recyclebin or simply,
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$SnQhAfQtUQzgRAAZ0Xbz6g==$0 TABLE 2008-04-09:12:35:17
4)Get the Table Back:
Invoke, flahsback command. Like,
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
However if you have another table you can use rename to clause with flashback,
flashback table test to before drop rename to test2;
SQL> drop table test;
Table dropped.
SQL> flashback table test to before drop rename to test2;
Flashback complete.
SQL> desc test2;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
However you can drop table permanently without staying it in recyclebin using,
drop table test2 purge;
To drop table from recyclebin, use,
purge table_name;
Flashback Table
You can also get back you present table to a previous state. Like You deleted some row and you have committed. Now you can back your data. Here is an example for your better understanding,
SQL> select * from t;
A
----------
1
100
10
SQL> insert into t values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
A
----------
1
100
10
2
SQL> flashback table t to timestamp systimestamp-interval '1' minute;
Flashback complete.
SQL> select * from t;
A
----------
1
100
10
Important Things:
1) objects will go to recyclebin or it will not go is based on recyclebin parameter settings.
If I set alter system set recyclebin=off then object will not go in recycle bin.
2)Dropped SYS and SYSTEM schema objects are don't go in recyclebin.
3)The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names.Views and procedures defined on the table are not recompiled and remain in the invalid state. These old names must be retrieved manually and then applied to the flashed-back table.
Like,
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'TEST')
AND ORIGINAL_NAME != 'TEST';
OBJECT_NAME ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpdfanfgMAAAAAANPw==$0 TEST_01 INDEX
BIN$04LhcpdfanfgMAAAAAANPw==$0 TR_TEST TRIGGER
After the table is flashed-back, the indexes and triggers on the table TEST will be named as shown in the OBJECT_NAME column. From the above query, you can use the original name to rename the objects as follows:
ALTER INDEX "RECYCLE BIN NAME" RENAME TO TEST_01;
ALTER TRIGGER "RECYCLE BIN NAME" RENAME TO TR_TEST;
But exception is the bitmap indexes. When they are dropped, they are not placed in the recycle bin and so they are not retrievable. The constraint names are also not retrievable from the view. They have to be renamed from other sources.
4) To do flashback enable row movement must be enabled.
alter table test enable row movement;
Related Documents:
Restore Old Data from Flashback Query
Oracle Flashback Technology
We should remember the thing is you must use automatic undo management to use the Flashback Table feature. It is based on undo information stored in an undo tablespace.
Now let's go for experiment.
Flashback Drop
1)SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
2)Drop the Table,
SQL> drop table test;
Table dropped.
3)Now see the Status,
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$SnQhAfQtUQzgRAAZ0Xbz6g==$0 TABLE
In fact when we issue drop table command the object resides on recycle bin. We can see the recycle bin objects from dba_recyclebin or simply,
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$SnQhAfQtUQzgRAAZ0Xbz6g==$0 TABLE 2008-04-09:12:35:17
4)Get the Table Back:
Invoke, flahsback command. Like,
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
However if you have another table you can use rename to clause with flashback,
flashback table test to before drop rename to test2;
SQL> drop table test;
Table dropped.
SQL> flashback table test to before drop rename to test2;
Flashback complete.
SQL> desc test2;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
However you can drop table permanently without staying it in recyclebin using,
drop table test2 purge;
To drop table from recyclebin, use,
purge table_name;
Flashback Table
You can also get back you present table to a previous state. Like You deleted some row and you have committed. Now you can back your data. Here is an example for your better understanding,
SQL> select * from t;
A
----------
1
100
10
SQL> insert into t values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
A
----------
1
100
10
2
SQL> flashback table t to timestamp systimestamp-interval '1' minute;
Flashback complete.
SQL> select * from t;
A
----------
1
100
10
Important Things:
1) objects will go to recyclebin or it will not go is based on recyclebin parameter settings.
If I set alter system set recyclebin=off then object will not go in recycle bin.
2)Dropped SYS and SYSTEM schema objects are don't go in recyclebin.
3)The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names.Views and procedures defined on the table are not recompiled and remain in the invalid state. These old names must be retrieved manually and then applied to the flashed-back table.
Like,
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'TEST')
AND ORIGINAL_NAME != 'TEST';
OBJECT_NAME ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpdfanfgMAAAAAANPw==$0 TEST_01 INDEX
BIN$04LhcpdfanfgMAAAAAANPw==$0 TR_TEST TRIGGER
After the table is flashed-back, the indexes and triggers on the table TEST will be named as shown in the OBJECT_NAME column. From the above query, you can use the original name to rename the objects as follows:
ALTER INDEX "RECYCLE BIN NAME" RENAME TO TEST_01;
ALTER TRIGGER "RECYCLE BIN NAME" RENAME TO TR_TEST;
But exception is the bitmap indexes. When they are dropped, they are not placed in the recycle bin and so they are not retrievable. The constraint names are also not retrievable from the view. They have to be renamed from other sources.
4) To do flashback enable row movement must be enabled.
alter table test enable row movement;
Related Documents:
Restore Old Data from Flashback Query
Oracle Flashback Technology
Subscribe to:
Posts (Atom)