10g TSPIT recovery failed

[karnash]

Hi I am trying TSPIT and I have dropped a table at 11:24 and am trying to recover it. This is what I do.

RMAN> run {
RECOVER TABLESPACE smm_data UNTIL TIME "TO_DATE('03/21/2007 11:20:00','MM/DD/YYYY HH24:MI:SS')"
AUXILIARY DESTINATION '/u02/manual_rman_bkp/MNISTEST';
sql 'alter tablespace smm_data online';
}

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
EXP-00008: ORACLE error 29308 encountered
ORA-29308: view TS_PITR_CHECK failure
ORA-06512: at "SYS.DBMS_PITR", line 887
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
host command complete

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/21/2007 13:49:48
RMAN-03015: error occurred in stored script Memory Script
RMAN-06135: error executing host command: Additional information: 256
Additional information: 4

I cannot understand what could be the error. Can anyone advise. Thanks in advance.

[Peter Godron]

Hi,
not an Oracle expert, but could you have a dependency on the dropped table ?
http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit002.htm#sthref774


Please also read:
http://forums1.itrc.hp.com/service/forums/helptips.do?#33 on how to reward any useful answers given to your questions.

[Eric Antunes]

Hi Karnash,

You can successfully do a TSPITR if the table indexes are ceated within the same tablespace. Is it your situation?

Best Regards,

Eric

[karnash]

Hi all,
I had missed a step and now I realized that the indexes are stored in different TS. so I included that in the restore too. The restore went fine and then export was done and Import terminated successfully with warnings.
then I got
host command complete

sql statement: alter tablespace SMM_DATA online

Removing automatic instance
shutting down automatic instance
target database instance not started
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/22/2007 12:08:56
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on default channel at 03/22/2007 12:08:56
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace SMM_DATA online
ORA-01190: control file or data file 5 is from before the last RESETLOGS
ORA-01110: data file 5: '/u02/oradata/mnistest/smm_data_001.dbf'

Any ideas ???

[Eric Antunes]

Hi,

"ORA-01190 controlfile or data file string is from before the last RESETLOGS
Cause: An attempt was made to use a datafile when the log reset information
in the file does not match the control file. Either the datafile or the control file is
a backup that was made before the most recent ALTER DATABASE OPEN
RESETLOGS.
Action: Restore file from a more recent backup."

After the recover, did you open the database in resetlog mode?

Regards,

Eric