September 17, 2009

Recovering a Dropped Tablespace with TSPITR in 11gR2

This is heavy dutty stuff. The scenario here is when you drop a tablespace and want it back! We're testing out the 11gR2 new feature that allows us to recover a dropped tablespace from a previous backup.

In order not to damage any of the other existing tablespaces, we'll create a guinea pig tablespace called LMC:
SQL> create tablespace lmc datafile '/u02/oradata/orcl/lmc01.dbf' size 50m;

Tablespace created.

We can't use the SYS schema to arbor an example table because objects belonging to SYS are not considered self-contained, and in the end RMAN would throw these errors:
RMAN-06960:    EXPDP> ORA-39123: Data Pump transportable tablespace job aborted
ORA-39187: The transportable set is not self-contained, violation list is
ORA-39917: SYS owned object TABELA_GRANDE in tablespace LMC not allowed in pluggable set

So, let's not make the common mistake of using the SYS schema for testing.
Let us create this trial table on a new fresh schema:
SQL> grant connect, resource to fresh identified by cool;

Grant succeeded.

Now we create a 30MB empty table inside that tablespace, belonging to the "fresh" schema ...
create table fresh.tabela_grande (coluna_grande varchar2(4000), n number)
tablespace lmc
storage (initial 30m);

Table created.

... and we load random data inside:
for i in 1..4000 loop
insert into fresh.tabela_grande values (dbms_random.string('a',4000),i);
end loop;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

In order to test TSPITR we need to have a full database backup, along with our LMC tablespace. I'm assuming that the full database backup had been done somewhere in a near past moment (we did it before creating the LMC tablespace).
But before that, controlfile autobackup needs to be enabled:
$ rman target /

Recovery Manager: Release - Production on Thu Sep 17 16:03:48 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1225517806)


new RMAN configuration parameters:
new RMAN configuration parameters are successfully stored

RMAN> backup tablespace lmc;

Starting backup at 17-SEP-09
Finished backup at 17-SEP-09

Now we drop the tablespace, but before that we register the time so we can do a real "point-in-time" recovery of the dropped tablespace:
SQL> !date
Thu Sep 17 18:52:24 WEST 2009

SQL> drop tablespace lmc including contents;

Tablespace dropped.

And now let's hope that 11gR2's TSPITR can bring us our LMC tablespace from the "dead"!!
UNTIL TIME "to_date('September 17 2009, 18:52:24','Month dd yyyy, HH24:MI:SS')"

This last command will last a bit long and throw sooooo many output at you that you shouldn't worry about.

If your process ends with these errors:
RMAN-06961:    IMPDP> ORA-39123: Data Pump transportable tablespace job aborted
ORA-01565: error in identifying file '+DATA/orcl/datafile/lmc.256.697830651'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/datafile/lmc.256.697830651
ORA-15012: ASM file '+DATA/orcl/datafile/lmc.256.697830651' does not exist

It's because you used the clause "AND DATAFILES" in the DROP TABLESPACE statement. If you did you should had restored the datafile first, or not had used the clause at all, like we did.

In the end if all is fine let's see it from SQL point of view:
SQL> select count(*) from fresh.tabela_grande;
select count(*) from fresh.tabela_grande
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u02/oradata/orcl/lmc01.dbf'

SQL> alter tablespace lmc online;

Tablespace altered.

SQL> select count(*) from fresh.tabela_grande;


Bingo! The table is back!

Bottom line: In 11gR2 if you drop a tablespace and want it back you should have the datafiles somehow, either by a operating system backup or by restoring them via an image copy. To avoid this hassle, don't use the "AND DATAFILES" clause of DROP TABLESPACE statement, backed them up before dropping (unless they're empty), and store the datafiles for a reasonable period. If in ASM, make an image copy via RMAN to a file system.

No comments:

Post a Comment