controlfile recovery for a db having few readonly tablespaces

here is the situation- The platform services took a cold backup of a UAT database before 3 weeks. and now the application group want that backup to be restored to the UAT . so I asked the platform services to go ahead and overwrite the existing UAT db related filesystems.

after they restored the cold backup I try to open the database and it complains . so I had to list out all the datafiles(including the readonly datafiles( though I dont know which datafiles are readonly from these 250+ datafiles )) . and recreate the controlfile . then i did a recover database using backup controlfile . and somehow it asks for the log file so I had to do a recovery using online redo logs . and then i opened the database with resetlogs . after that still confused about the read only tablespaces I did a status check in dba_tablespaces and I found that the data dictionary had all the readonly ts information and even though the controlfile was recreated but still the database was able to indentify properly .

-not that of a big deal but it was a good learning for me .
lesson- never keep the controlfile outside the datafile filesystem. becuase it is a lot of headache when you took a cold backup and then keep scratching your head that why you are not able to use the backup to restore it . always make sure that a cold backup backs up all the files .

The readonly tablespaces hit me back . when the user reported this error.

ORA-00376: file 71 cannot be read at this time

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

ORA-01110: data file 71: '/u1/app/oracle/product/9.2.0/dbs/MISSING00071'

so I had to to just do trial and error

select file_id,file_name from dba_data_files
where file_name like '%MISS%'

select * from v$datafile where status='OFFLINE'

SQL> alter tablespace PDB_IX_DS1 online;
alter tablespace PDB_IX_DS1 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 246 - see DBWR trace file
ORA-01111: name for data file 246 is unknown - rename to correct file
ORA-01110: data file 246: '/u1/app/oracle/product/9.2.0/dbs/MISSING00246'

doing a alter datbase backup controlfile to trace reported these errors

ALTER DATABASE RENAME FILE 'MISSING00227'
# WARNING! This command can not be completed because the file
# needs to be renamed to a real file name.

anyway I guess I was just lucky . somehow able to patch up the missing links. but I will make sure to take a alter database backup controfile to trace for any database specifically with readonly tablespace before I do a cold backup.

-

it seems oradebug dump helps to identify the tablespace/datafile relationship . thanks to Rijaj in oracle list . have to try.