ORA-10643: Database should be mounted in restricted mode and Exclusive mode

Hi,
Recently, we ran into the following issue while converting a system tablespace from dictionary managed to locally managed:
SQL> EXEC dbms_space_admin.tablespace_migrate_to_local('SYSTEM');
BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;
*ERROR at line 1:
ORA-10643: Database should be mounted in restricted mode and Exclusive mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

Our database was a RAC database and we were trying to convert the system tablespace to locally managed. The prerequisite for such an activity is that the database should be started in restricted mode and all tablespaces other than system, temp, undo and sysaux be in read only mode. SYSAUX should be offline. We started the first instance and executed the command to initiate the conversion. However, the conversion failed with the above error.

We set the cluster_database value to false and restarted the conversion process. Thanks to my colleague, M Rajesh Kumar for his timely help and suggestion which saved us lot of time. This time around, we did not see the original error, instead a new error cropped up:

ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM, UNDO, TEMP not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

All the tablespaces other than system,temp,undo were in read only mode and sysaux was in offline mode. That is when we realized, we have two UNDO tablespaces, one for each instance. UNDO_TS2, the undo tablespace used by other instance was also online. We cannot make that tablespace read only as that is an UNDO tbs. We tried to complete the conversion by making UNDO_TS2 offline, but it did not succeed - we continued to see the same error.

Fix:

Make note of the details of the UNDO_TS2 like data files/volumes used, auto extend on/off etc and drop UNDO_TS2 from the first instance. Perform the system tablespace conversion from Dictionary managed to LMTS. Recreate the UNDO_TS2 using the details which you have taken before dropping the tbs.

PS: I have not tried to give the exact steps involved in the LMTS conversion. The steps include things like dropping multiple temporary tablespaces, working with default temporary tablespace settings, tablespace groups etc, which we followed.

-Aravind Kamath Posral