I was asked about read-only tablespace recovery scenario in an interview.
Interviewer: All current controlfiles are lost and no backup of controlfiles, one of the tablespace is in read-only mode. What do you do after re-creating the controlfile ?
In real world, DBA at least backup the controlfile, but that’s not the answer to the question 🙂 right, we will take it as one of case.
So as a DBA while re-creating the controlfile I will not list the read-only files in create controlfile script. Here are the steps
1) create controlfile script(skip read-only tablespace)
2) run the create controlfile script and mount and open the database.
3) Database performs data dictionary check on files listed in create controlfile script and for the files NOT listed in controlfile script but present in data dictionary are named as missingnnnnnn in controlfile
4) after database is open, rename the missing files with correct file names as
SQL> alter database rename file 9 to <>;
Basically you don’t need recovery on read-only tablespace unless these files are restored from the time when these files were read/write.
CREATE CONTROLFILE REUSE DATABASE ORCL NORESETLOGS ARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 32 MAXINSTANCES 16 MAXLOGHISTORY 1600 LOGFILE GROUP 1 ( '/app/oracle/orcl/db/group1a.dbf', '/app/oracle/orcl/db/group1b.dbf' ) SIZE 100K GROUP 2 ( '/app/oracle/orcl/db/group2a.dbf', '/app/oracle/orcl/db/group2b.dbf' ) SIZE 100K DATAFILE '/app/oracle/orcl/db/system.dbf', '/app/oracle/orcl/db/sysaux.dbf', '/app/oracle/orcl/db/undotbs1.dbf', '/app/oracle/orcl/db/users1.dbf' CHARACTER SET WE8MSWIN1252 ;