The Cluster said: "I forbid you to migrate SYSTEM tablespace to local"

Introducing Nitin Ramesh

NitinR

In this article, I would like to introduce Nitin Ramesh, an associate consultant with Oracle GSD India, Bangalore. This is the first time that I am having another Oracle Employee and co-worker publish articles on this blog, and I am excited. We solve many problems every for our ~35 customers on a 24x7 basis, but not many of them make it to articles to help others in need. If only we decided to this is on a regular basis, a lot of problems could be solved by a mere "google".

Coming back to Nitin, he is a pretty creative person and likes to solve intriguing problems. I haven't seen many people do that with sincerity. It is hoped that with this publication, he will come forth with more interesting articles and end up having his own blog.

Credit: The following article has been written by him and edited later on for presentation purposes.

Preface

In this article, we will talk about a scenario in which we were required to migrate the SYSTEM tablespace to local. What did you say? Piece of cake? We thought so too!

This process seemed to be simple to be first tried out in the test environment. But it was not all that simple. The first attempt ended with an error.

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL' must be
declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Huh?

The issue was suspected to be the database not being in the proper mount mode to run the command.

The command was rerun with the following options again..

SQL> startup mount restrict exclusive

and

SQL> startup restrict

But, the results were not positive.

The next suspicion was on the package itself. So that angle had to be cleared...

SQL> select object_name, object_type, status from dba_objects where object_name = 'DBMS_SPACE_ADMIN';

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
DBMS_SPACE_ADMIN     PACKAGE             VALID
DBMS_SPACE_ADMIN     PACKAGE BODY        VALID
DBMS_SPACE_ADMIN     SYNONYM             VALID

Hmm, no problem there too.

Maybe the problem was with invoker rights, so we tried giving explicit grant to execute the procedure to sys, but we got the same (lack of) results:

SQL> grant execute any procedure to sys;

What else can we check?

The issue was retested after setting the event '201 trace name errorstack, level 3' at the session level. This was tried with the both startup options – “startup restrict and “startup mount restrict exclusive”, but that did not seem to help.

SQL> show parameter event;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string 10298 trace name context forever, level 32
SQL> EXECUTE 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


How about checking if all the DB options in DBA_REGISTRY were valid?

SQL> select comp_name, version, status from dba_registry;

COMP_NAME                              VERSION                        STATUS
-------------------------------------- ------------------------------ ---------------------------------
Oracle Enterprise Manager              10.2.0.3.0                     VALID
Oracle XML Database                    10.2.0.3.0                     VALID
Oracle Text                            10.2.0.3.0                     VALID
Spatial                                10.2.0.3.0                     VALID
Oracle interMedia                      10.2.0.3.0                     VALID
Oracle Database Catalog Views          10.2.0.3.0                     VALID
Oracle Database Packages and Types     10.2.0.3.0                     VALID
JServer JAVA Virtual Machine           10.2.0.3.0                     VALID
Oracle Database Java Packages          10.2.0.3.0                     VALID
Oracle XDK                             10.2.0.3.0                     VALID
Oracle Real Application Clusters       10.2.0.3.0                     VALID

All right, no problems there. How about if this were a compatibility issue?

SQL> show parameter compatible;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
compatible                           string      10.2.0

How about any freak triggers during startup:

SQL> select * from dba_triggers where TRIGGERING_EVENT='STARTUP';

no rows selected

Enter Oracle Support

Since we had checked all we could, it was time to get an opinion from Product support. The matter was referred to Kevin Cook from the Advanced Resolution Team (AR). He suggested to retry using the STARTUP UPGRADE option, but before doing so, we tried setting the parameter o7_dictionary_accessibility to FALSE. Yet, the result was undesired. Finally, the STARTUP UPGRADE option had to be used to make it work, BUT with a different twist to it.

When nothing works, we should use the most simplistic case. Oh, did we forget to mention that this was a RAC instance? The catch was that the cluster had to be disabled and the UNDO tablespace for the other instance had to be dropped.

How was it done?

All the tablespaces were made ‘read only’ other than SYSTEM, TEMP, SYSAUX and UNDO tablespaces.

SQL> ALTER TABLESPACE <tablespace_name> READ ONLY;

Lets verify it..

SQL> select t.tablespace_name, EXTENT_MANAGEMENT, ENABLED

from dba_tablespaces t, v$datafile d, dba_data_files f

where t.tablespace_name = f.tablespace_name

and f.file_id = d.file#;

Determined the undo tablespace used for the current instance.

SQL> sho parameter UNDO_TABLESPACE;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDO1

As you cannot put UNDO_TS_B or UNDO_TS_C into READ ONLY mode since they are UNDO tablespaces, you must therefore drop these tablespaces, convert the SYSTEM tablespace, then re-create UNDO_TS_B and UNDO_TS_C tablespaces. So, we dropped the UNDO tablespace used by the other instance [UNDO2].

Now, the database was started with the STARTUP UPGRADE option, BUT the cluster_database parameter was set to false in this temp init.ora file:

SQL> startup upgrade pfile='initDUAT1.ora_tmp_oct17';

ORACLE instance started.

Total System Global Area 1.0754E+10 bytes

Fixed Size 2174464 bytes

Variable Size 2889634304 bytes

Database Buffers 7851737088 bytes

Redo Buffers 10649600 bytes

Database mounted.

Database opened.

Take the SYSAUX tablespace offline..

SQL> ALTER TABLESPACE sysaux OFFLINE;

Conversion starts…

Execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure specifying the SYSTEM tablespace:

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

PL/SQL procedure successfully completed.

Verify your change by querying DBA_TABLESPACES:

SQL> SELECT tablespace_name, extent_management FROM dba_tablespaces WHERE tablespace_name = 'SYSTEM';

TABLESPACE_NAME                EXTENT_MAN

------------------------------ -------------------

SYSTEM                         LOCAL

Once all tablespaces are locally managed within a database, the SYS.FET$ table does not contain any rows:

SQL> select * from sys.fet$;

no rows selected

Issue the following command to disable RESTRICTED mode:

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

Issue the following command for each tablespace you placed in READ ONLY mode to return it to READ WRITE:

SQL> ALTER TABLESPACE <tablespace_name> READ WRITE;

Make the SYSAUX tablespace online:

SQL> ALTER TABLESPACE sysaux ONLINE;

Conclusion

A simple exercise turned out to be a learning experience, because the API had a possible dependence or conflicts with some internal RAC views. When RAC was disabled and the UNDO tablespace for the other RAC instances were dropped, then the migration API completed successfully. The moral of the story is, when in doubt, use single instance mode.

 

 

Comments:

Hi, I got a similar task to perform and with the help of your blog have successfully converted the system to locally managed tablespace, we could do this by taking the sysaux offline without going for upgrade option. SQL> ALTER TABLESPACE sysaux OFFLINE; Tablespace altered. SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); PL/SQL procedure successfully completed. The errors i received while executing before where : SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END; * ERROR at line 1: ORA-10644: SYSTEM tablespace cannot be default temporary tablespace ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227 ORA-06512: at line 1 SQL> alter database default temporary tablespace TEMP; Database altered. SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END; * ERROR at line 1: ORA-10647: Tablespace other than SYSTEM, UNDOTS, TEMP not found in read only mode ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227 ORA-06512: at line 1 SQL> select t.tablespace_name, EXTENT_MANAGEMENT, ENABLED from dba_tablespaces t, v$datafile d, dba_data_files f where t.tablespace_name = f.tablespace_name and f.file_id = d.file#; to find the tablespace in read write mode, then select 'alter tablespace '||tablespace_name||' read only;' from dba_tablespaces; made all the tablsepaces execpt SYSTEM, TEMP, SYSAUX and UNDO in read only mode then executed again SQL> execute sys.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); BEGIN sys.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END; * ERROR at line 1: ORA-10648: Tablespace SYSAUX is not offline ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227 ORA-06512: at line 1 SQL> ALTER TABLESPACE sysaux OFFLINE; Tablespace altered. SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); PL/SQL procedure successfully completed. Done

Posted by sandy on November 23, 2009 at 03:23 PM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

bocadmin_ww

Search

Archives
« March 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today