Recover The Table space From Flashback Database

Recover The Table space From Flashback Database

Backup was taken of both controlfile and datafile at 10 am. At 11 am Tablespace was

created and at 1pm tablespace was dropped. How to Recover the tablespace using cold

backup.

Solution -

If you actually DROPPED the tablespace, the controlfile you're trying to use will have no

record of the tablespace. You can recover with Flash database or You will need to do a

point in time recovery using a backup controlfile from a time when the tablespace

existed, then recover up to the point before the drop.

Using Flashback database :-

1. Configure the Oracle Database for Flashback

SQL> shutdown immediate;

SQL> startup mount

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

SQL> create tablespace test_restore datafile

'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\MYTEST08.DBF' size 5m;

Tablespace created.

SQL> create table test tablespace test_restore as select level a1 from dual connect by

level < 99;

Table created.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

581924

SQL> drop tablespace test_restore including contents;

Tablespace dropped.

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

Variable Size 75498876 bytes

Database Buffers 83886080 bytes

Redo Buffers 7139328 bytes

Database mounted.

SQL> flashback database to scn 581924

2 /

flashback database to scn 581924

*

ERROR at line 1:

ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get

error

below

ORA-01245: offline file 8 will be lost if RESETLOGS is done

ORA-01111: name for data file 8 is unknown - rename to correct file

ORA-01110: data file 8:

'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008'

SQL> alter database rename file

'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' to

'C:\ORACLE\PRODUCT\10.2.0\ORADA

YTEST08.DBF';

Database altered.

SQL> flashback database to scn 581924;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

MYTEST ONLINE

MYTEST5 ONLINE

TEST_RESTORE ONLINE

9 rows selected.

SQL>

SQL> select count(*) from test;

COUNT(*)

----------

98

At this point, you could export everything you wanted and then shutdown the database

and startup mount then issue RECOVER DATABASE and be right back to where you

were after the tablespace drop. Then, if needed, you could recreate the tablespace and

import the data.

C:\>exp userid='sys/oracle@test5 as sysdba' tables=mytest file=mytest

About to export specified tables via Conventional Path ...

. . exporting table TEST 98 rows exported

Export terminated successfully without warnings.

C:\>sqlplus sys/oracle@test5 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 21 15:42:32 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

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

Variable Size 75498876 bytes

Database Buffers 83886080 bytes

Redo Buffers 7139328 bytes

Database mounted.

SQL> recover database;

Media recovery complete.

SQL> alter database open ;

Database altered.

SQL> select count(*) from test;

select count(*) from test

*

ERROR at line 1:

ORA-00942: table or view does not exist

At this you can create the the tablespace and import the data

http://www.orafaq.com/forum/t/101479/0/