The Cluster said: "I forbid you to migrate SYSTEM tablespace to local"
Introducing Nitin Ramesh
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 24×7 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.
Tags: 24x7 Basis, Associate Consultant, Co Worker, Creative Person, Google, Gsd, Interesting Articles, Intriguing Problems, Local System, ORACLE BLOGS, Piece Of Cake, Pl Sql, Preface, Presentation Purposes, Quot, Ramesh, Rerun, Sincerity, Sql Statement, System Tablespace, Test Environment