RMAN: Recoving a tablespace from a backup

[BeefStu]

I am having a problem restoring a tablespace from a full database backup. Can somebody please point me in the right direction.

Below are the steps I have taken.


#
# Create catalog
#

rman catalog rman/rman
RMAN> create catalog;
recovery catalog created
exit

#
# Register database
#

$ rman catalog rman/rman target backdba/backdba

connected to target database: PBH10G2 (DBID=2517645563)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

#
# Configure parameters
#

RMAN> configure retention policy to recovery window of 7 days;
RMAN> configure default device type to disk;
RMAN> configure controlfile autobackup on;
RMAN> configure channel device type disk format '/rdbms/oracle_data/10g2/pbh10g2/backup/Backup%d_DB_%u_%s_%p';

#
# Backup the database
#

RMAN> run { backup database plus archivelog;
2> delete noprompt obsolete;
3> }


Starting backup at 21-AUG-06
current log archived
...
...
Finished backup at 21-AUG-06

Starting backup at 21-AUG-06
...
...
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 21-AUG-06

Starting backup at 21-AUG-06
Finished backup at 21-AUG-06
...
...
Finished Control File and SPFILE Autobackup at 21-AUG-06
no obsolete backups found

Now that I have a succesful backup, I go into another window via SQLPLUS and drop a table (ledger_entries) that is in my ctctables tablespace. Note, this tablespace is made up of only one datafile '/rdbms/oracle_data/10g2/pbh10g2/ctctables.dbf'

SQL> drop table develqc.ledger_entries;

When I try to recover my tablespace I get the following error:

RMAN> run {sql 'ALTER TABLESPACE ctctables OFFLINE IMMEDIATE';
2> restore tablespace ctctables;
3> recover tablespace ctctables;
4> sql 'ALTER TABLESPACE ctctables ONLINE';
5> }

sql statement: ALTER TABLESPACE ctctables OFFLINE IMMEDIATE

Starting restore at 21-AUG-06
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/21/2006 14:35:22
RMAN-06004: ORACLE error from recovery catalog database: ORA-00376: file 4 canno
t be read at this time
ORA-01110: data file 4: '/rdbms/oracle_data/10g2/pbh10g2/ctctables.dbf'
RMAN-06019: could not translate tablespace name "CTCTABLES"

[LKBrwn_DBA]

It seems you did not actually read the RMAN manual on how to recover a tablespace.

[BV1963]

Did you put the catalog in the same database?
rman catalog rman/rman target backdba/backdba ?????

If so what is default tablespace from user rman?

[LKBrwn_DBA]

There are two ways to recover a tablespace:

1) Recover whole database to point in time before the DROP TABLE command.
2) Recover tablespace to auxiliary db to that point in time and move data or transport tablespace to main db.