Read-only tablespace recovery

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.

sample create_controlfile.sql


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
;

Cheers

Raheel

Advertisements

About Raheel Syed

Oracle DBA
This entry was posted in Backup and Recovery and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s