RMAN
Restoring Dropped Tablespace Using Flashback Database
Using flashback database, you can get back the dropped
tablespace and all objects that
were created on it. It gets back the database to the time when the
tablespace existed. Follow the next steps to restore the dropped
tablespace.
- Before starting, have a tablespace which will be dropped and
restored. So create the tablespace and a table with one row:
SQL>
create
tablespace tbs datafile '/tmp/tbs.dbf' size 1m;
Tablespace created.
SQL>
create
user tbs
identified by
tbs;
User created.
SQL>
grant
dba
to
tbs;
Grant succeeded.
SQL>
alter
user tbs default tablespace tbs;
User altered.
SQL>
conn
tbs/tbs
Connected.
SQL>
create
table tbs (id number);
Table created.
SQL>
insert
into tbs values(1);
1 row created.
SQL>
commit;
Commit complete.
- For testing purposes, create an index on the column of the
newly created table and place it in the different tablespace.
Then get the current scn number:
SQL>
create
index idx_tbs
on
tbs(id) tablespace users;
Index created.
SQL>
select
current_scn
from
v$database;
CURRENT_SCN
-----------
460217
- Drop the tablespace and try to perform flashback database to
the previously taken scn value:
SQL>
conn / as
sysdba
Connected.
SQL>
drop
tablespace tbs including contents and datafiles;
Tablespace dropped.
SQL>
shutdown
immediate
SQL>
startup
mount
SQL>
flashback
database to scn 460217;
flashback database to scn 460217
*
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: '/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'
Recreate the datafile using the alter database create datafile
command as follows:
SQL>
alter
database create datafile '/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'
as '/tmp/tbs.dbf';
Database altered.
- Perform the flashback database again:
SQL>
flashback
database to scn 460217;
Flashback complete.
SQL>
As it succeeded, open the database with the resetlogs option
and query the table. Then query the index and the name of the
tablespace:
SQL>
alter
database open resetlogs;
Database altered.
SQL>
select * from
tbs.tbs;
ID
----------
1
SQL>
select
index_name
from
dba_indexes
where
table_name='TBS';
INDEX_NAME
------------------------------
IDX_TBS
SQL>
select
tablespace_name
from
dba_data_files
where
file_name='/tmp/tbs.dbf';
TABLESPACE_NAME
------------------------------
TBS
SQL>
It should be noted that flashback database takes the whole
database back in time and all changes made to the database after
the specified scn were lost.
During this process, detailed information was added to the
alert.log file. Here is the source of the alert.log file:
Flashback Restore Start
Flashback: created tablespace #6: 'TBS' in the controlfile.
Flashback: created OFFLINE file 'UNNAMED00005' for tablespace #6
in the
controlfile.
Filename was:
'/tmp/tbs.dbf' when dropped.
File will have to be restored from a backup and recovered.
Flashback: deleted datafile #5 in tablespace #6 from control
file.
Flashback: dropped tablespace #6: 'TBS' from the control file.
Flashback Restore Complete
Flashback Media Recovery Start
Mon Mar 15 06:37:43 2015
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1 Reading mem
0
Mem# 0 errs 0:
/u01/oracle/product/10.2.0/db_1/oradata/testrc/redo02.log
Flashback recovery: Added file #5 to control file as OFFLINE and
'UNNAMED00005'
because it was dropped during the flashback interval
or it was added during flashback media recovery.
File was originally created as:
'/tmp/tbs.dbf'
File will have to be restored from a backup or
recreated using alter database create datafile command,
and the file has to be onlined and recovered.
Mon Mar 15 06:37:44 2015
Incomplete Recovery applied until change 460213
Flashback Media Recovery Complete
ORA-38795 signalled during: flashback database to scn 460211...
Oracle created an offline file unnamed00005 and used the redo
log file to recover it. Then as the alter database create datafile
command was issued and the flashback performed for the second
time, the following information was added to the alert.log file:
alter database create datafile
'/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' as '/tmp/tbs.dbf'
Mon Mar 15 06:45:08 2015
Completed: alter database create datafile
'/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' as '/tmp/tbs.dbf'Mon
Mar 15 06:45:31 2015
flashback database to scn 490829
Mon Mar 15 06:45:32 2015
ORA-38743 signalled during: flashback database to scn 490829...
Mon Mar 15 06:45:55 2015
flashback database to scn 460217
Mon Mar 15 06:45:55 2015
Flashback Restore Start
Deleted file /tmp/tbs.dbf
Flashback: deleted datafile #5 in tablespace #6 from control
file.
Flashback: dropped tablespace #6: 'TBS' from the control file.
Flashback Restore Complete
Flashback Media Recovery Start
Mon Mar 15 06:45:56 2015
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1 Reading mem
0
Mem# 0 errs 0:
/u01/oracle/product/10.2.0/db_1/oradata/testrc/redo02.log
Recovery created file /tmp/tbs.dbf
Successfully added datafile 5 to media recovery
Datafile #5: '/tmp/tbs.dbf'
Mon Mar 15 06:45:56 2015
Incomplete Recovery applied until change 460235
Flashback Media Recovery Complete
Completed: flashback database to scn 460217
Disabling Flashback Database
SQL>
alter database flashback off;
Database altered.
SQL>
It is possible to disable flashback when the database is open
and running. By disabling flashback database, Oracle stops the
RVWR process, deletes all flashback logs and information is
written to the alert.log file as follows:
Wed Feb 17 17:20:30 2015
alter database flashback off
Wed Feb 17 17:20:30 2015
Stopping background process RVWR
Deleted Oracle managed file
/u01/oracle/product/10.2.0/db_1/flash_recovery_area/BK/flashback/
o1_mf_5qrtsofs_.flb
Deleted Oracle managed file
/u01/oracle/product/10.2.0/db_1/flash_recovery_area/BK/flashback/
o1_mf_5qrtvsov_.flb
Flashback Database Disabled
Completed: alter database flashback off
In case the flashback log files are removed manually, the
subsequent startup of the database will fail. Therefore, all you
need to do is shut down the database, start up mount, flashback
off, flashback on, and finally open the database. It is better not
to remove the flashback log files manually; rather, let Oracle
delete them automatically by turning the flashback off.
As Oracle performs flashback database using flashback logs, if
it cannot find any log, it throws the following error:
SQL>
flashback
database to scn 479111;
flashback database to scn 479111
*
ERROR at line 1:
ORA-38701: Flashback database log 2 seq 2 thread 1:
"/u01/oracle/product/10.2.0/db_1/flash_recovery_area/BK/flashback/
o1_mf_5qrv5513_.flb"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL>
To determine how far back it went, it is possible to revert the
database using flashback logs by running the following query:
SQL>
select
oldest_flashback_scn,
to_char
(oldest_flashback_time,'dd-mm-yyyy
hh24:mi:ss')oldest_flashback_time
from
v$flashback_database_log;
OLDEST_FLASHBACK_SCN ODLEST_FLASHBACK_TIME
-------------------- ---------------------
544000 20-02-2010 21:46:37
SQL>
