Experiments from the Field..Based on True Stories

Preface

Last weekend was pretty exciting. We were upgrading our customer's production 11.5.9 Oracle Applications instance to 10.2.0.3 and part of the effort was applying the ATG Family pack H.RUP6 patch. We were getting some really weird errors while doing the production run, something we had never seen before in the pre-production rounds (we did 6 of them to be exact). The possibility of rolling back seemed quite real.

Essentially, the AD.I.6 patchset failed while running autoconfig implicitly (the patch completed, but autoconfig was failing). Interestingly, Autoconfig was running fine on the admin/concurrent tier, but not working on the web/forms tier. Just to add to the details, the APPL_TOPs for the middle tiers (shared across multiple tiers) and Admin/concurrent tier were different.

This article talks about how we came over this issue. It is hoped it will help someone else in the future too.

A Race against time..

It was a perplexing situation. We were budgeted/negotiated 36 hrs for the entire downtime (Sun JRE Vista Patching, HRMS family pack K3 and 10g upgrade), and time was slipping by. We couldn't afford to lose more time.

Truncated Class File?

The error we were getting on the web/forms tier was as follows:

middle_tier_1:web_prod> ./adautocfg.sh
Enter the APPS user password:
The log file for this session is located at:
/ORACLE/apps/prod/admin/prod_middle_tier_1/log/11151046/adconfig.log

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.
Using APPL_TOP location : /ORACLE/apps/prod
Classpath : /usr/java/j2sdk1.4.2_07/jre/lib/rt.jar:/usr/java/j2sdk1.4.2_07/lib/dt.jar:/usr/java/j2sdk1.4.2_07/lib/tools.jar:/ORACLE/apps/prod/common/java/appsborg2.zip:/ORA
CLE/apps/prod/common/java

Exception in thread "main" java.lang.ClassFormatError: oracle/apps/ad/autoconfig/InstantiateFile
(Truncated class file)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:539)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:123)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:251)
at java.net.URLClassLoader.access$100(URLClassLoader.java:55)
at java.net.URLClassLoader$1.run(URLClassLoader.java:194)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:187)
at java.lang.ClassLoader.loadClass(ClassLoader.java:289)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274)
at java.lang.ClassLoader.loadClass(ClassLoader.java:235)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:302)
at oracle.apps.ad.tools.configuration.Customizer.getProdTopDrivers(Customizer.java:380)
at oracle.apps.ad.tools.configuration.Customizer.getAllDrivers(Customizer.java:358)
at oracle.apps.ad.tools.configuration.VersionConflictListGenerator.getAllConflicts(VersionConflictListGenerator.java:170)
at oracle.apps.ad.tools.configuration.VersionConflictListGenerator.main(VersionConflictListGenerator.java:426)
ERROR: Version Conflicts utility failed.
Terminate.

Ideas, anyone?

All right, so a class file is truncated. The very first thing that we thought about was that maybe regenerating the jar files through adadmin utility would fix it. But, it did not help.

Another thought was that a Version Conflicts utility failed message typically means something is point to incorrect code. The most common reason is usually the version of JDK. Someone pointed that the JDK that we were using was 1.4.2_07, whereas 1.3.1 was certified with 11.5.9. But since our AD level was pretty high, we didn't think this was relevant. The application was working fine with JDK 1.4.2_07 before.

Yet another person thought of removing the entry for Web tier from the TNS topology model itself. But, as can be seen here, even that idea did not work.

$ perl $AD_TOP/bin/adgentns.pl appspass=XXXXXX contextfile=$APPL_TOP/admin/prod_middle_tier_1.xml
##########################################################################
Generate Tns Names

##########################################################################
Logfile: /ORACLE/apps/prod/admin/prod_middle_tier_1/log/11151856/NetServiceHandler.log
Classpath :
/usr/java/j2sdk1.4.2_07/jre/lib/rt.jar:/usr/java/j2sdk1.4.2_07/lib/dt.jar:/usr/java/j2sdk1.4.2_07/lib/tools.jar:/ORACLE/apps/prod/common/java/appsborg2.zip:/ORACLE/apps/prod/common/java

Updating s_tnsmode to 'generateTNS'
UpdateContext exited with status: 0
Exception in thread "main" java.lang.ClassFormatError: oracle/apps/ad/tools/configuration/NetServiceHandler (Truncated class file)
at java.lang.ClassLoader.defineClass(ClassLoader.java:539)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:123)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:251)
at java.net.URLClassLoader.access$100(URLClassLoader.java:55)
at java.net.URLClassLoader$1.run(URLClassLoader.java:194)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:187)
at java.lang.ClassLoader.loadClass(ClassLoader.java:289)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274)
at java.lang.ClassLoader.loadClass(ClassLoader.java:235
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:302)
Error generating tnsnames.ora from the database, temperory tnsnames.ora will be generated using
templates
Instantiating Tools tnsnames.ora
Exception in thread "main" java.lang.ClassFormatError: oracle/apps/ad/autoconfig/InstantiateFile
(Truncated class file)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:539)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:123)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:251)
at java.net.URLClassLoader.access$100(URLClassLoader.java:55)
at java.net.URLClassLoader$1.run(URLClassLoader.java:194)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:187)
at java.lang.ClassLoader.loadClass(ClassLoader.java:289)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274)
at java.lang.ClassLoader.loadClass(ClassLoader.java:235)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:302)
Error in instantiating tools tnsnames.ora:
Exception in thread "main" java.lang.ClassFormatError: oracle/apps/ad/autoconfig/InstantiateFile (Truncated class file)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:539)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:123)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:251)
at java.net.URLClassLoader.access$100(URLClassLoader.java:55)
at java.net.URLClassLoader$1.run(URLClassLoader.java:194)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:187)
at java.lang.ClassLoader.loadClass(ClassLoader.java:289)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274)
at java.lang.ClassLoader.loadClass(ClassLoader.java:235)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:302)
Error in instantiating web tnsnames.ora:

adgentns.pl exiting with status 256
ERRORCODE = 256 ERRORCODE_END

Well, so far, no luck. Then, we did realize that the context file is also uploaded into the Database by OAM functionality (remember the OAM screen through which you can edit the autoconfig tokens?). We had thoroughly checked the context file of the middle tier, but maybe there was a corruption in the database copy ? We came across some metalink notes (e.g. Note:463895.1, Note:352916.1), which talked about purging all uploaded context files (kept in applsys.FND_OAM_CONTEXT_FILES). So potentially, we could take a backup of applsys.FND_OAM_CONTEXT_FILES table, delete the entry corresponding to middle_tier_1 and then autoconfig on middle_tier_1 to reload the data. So, this was a realistic possibility.

Wait a minute .. Can we take the shotgun approach?

OK, so while we were thinking of all these options, the thought came to my mind that we had 5 middle tiers, so why don't we try to run autoconfig on one of the other middle tiers. Most surprisingly, Autoconfig ran fine on another middle tier!!

Hmph, all of them were sharing the same $JAVA_TOP, so what could make the other middle tiers behave differently? Maybe a difference in an autoconfig token or environment variable? But, even after comparing all the entries in XML file and environment variables, there was no perceptible difference. Strange, very strange.

This was a good validation point for us, as we could conclude that there was something wrong with middle_tier_1, but not with the others. So, we could simply continue patching with another middle tier and hope to not get similar issues in the future. middle_tier_1 could be taken out of the load balancer, dealt with later and when fixed, put back in the load balancer. In the interim, it sounded like a workable strategy, so we took it. This is what saved us from spinning our wheels on this issue.

So what was it, really?

We continued patching and successfully migrated the production instance to 10g (Yippee!).

One thing did strike me though much later: the versions of the InstantiateFile.class were different in $JAVA_TOP and $AD_TOP when we were getting this error. For example, this was the situation when we were having this error:

middle_tier_1:web_prod> adident Header $JAVA_TOP/oracle/apps/ad/autoconfig/InstantiateFile.class
/ORACLE/apps/prod/common/java/oracle/apps/ad/autoconfig/InstantiateFile.class:
$Header InstantiateFile.java 115.212 2007/07/10 11:20:16 schagant ship $

middle_tier_1:web_prod> adident Header $AD_TOP/java/oracle/apps/ad/autoconfig/InstantiateFile.class
/ORACLE/apps/prod/ad/11.5.0/java/oracle/apps/ad/autoconfig/InstantiateFile.class:
$Header InstantiateFile.java 115.203 2006/11/01 08:05:36 subhroy ship $

Later on, after the outage, when I checked the versions again, this is what I saw:

middle_tier_1:web_prod> adident Header $JAVA_TOP/oracle/apps/ad/autoconfig/InstantiateFile.class
/ORACLE/apps/prod/common/java/oracle/apps/ad/autoconfig/InstantiateFile.class:
$Header InstantiateFile.java 115.212 2007/07/10 11:20:16 schagant ship $

I am now forced to think that maybe this was a contributor to the issue at hand. If we had known that this .class file had multiple copies across the APPL_TOP and JAVA_TOP, we could have simply run adadmin with the Maintain files->copy files to destination option to sync up the duplicate copies. But then, why didn't the other middle tiers complain? The APPL_TOP and JAVA_TOP was shared amongst all of them. So maybe, it wasn't the real problem.

Later on, when we rebooted middle_tier_1 box (RHAS3 32bit linux), autoconfig ran successfully! This thought was based on Note 556107.1 Java.Lang.Classformaterror: oracle/apps/ibe/store/StoreCurrency (Truncated class file). If it was really this which resolved the issue, it must have been some corrupted shared module in memory, which got cleared off?

Come to think of it, when nothing seems to work on Windows, we do reboot it.

In Retrospect..

So, I don't have all the answers, but a little lateral thinking saved us and enabled us to proceed. Sometimes, the shotgun approach does work. It pays to have multiple middle tiers in the architecture, so that there is no single point of failure. Also, sometimes, rebooting can resolve some really weird errors.

I'm reminded of a phrase in Hindi -- अकल बड़ी या भैंस ? (loose translation: sometimes brute force is superior to elaborate reasoning). You can be the judge.

Post Upgrade Performance Pangs..

After a 10g upgrade of production 11.5.9 Oracle Application instances, one of our clients was seeing some performance problems relating to pricing, particularly with a custom concurrent program called XXONT_PROGRESS_NOTIFIED.

This triggered the onsite Senior DBA, Brian Fane , into action. While watching the current executing sqls of this job, this SQL statement was seen to be executing somewhat frequently:

         SELECT list_header_id
           FROM qp_preq_qual_tmp
          WHERE list_header_id = :b4
            AND line_index = :b3
            AND list_line_id = -1:
            AND pricing_status_code = :b2
            AND pricing_phase_id = :b1:
            AND ROWNUM = 1;

     In fact, based on one AWR report he saw, it was about 19% of the gets and 5% of the total elapsed time between 0800 and 1300 hrs. It ran nearly a million
times. The execution plan looked like this:

         CHILD_NUMBER   QUERY

         0  SELECT STATEMENT   [ALL_ROWS] Cost=1
         0  COUNT STOPKEY
         0    TABLE ACCESS BY INDEX ROWID QP_PREQ_QUAL_TMP_T  Cost=0 Card=1  Bytes=82
         0      INDEX SKIP SCAN XXQP_QP_PREQ_QUAL_TMP_T_N1  Cost=0 Card=1  Bytes=

Comparing with other 9i instances..

      In DEV, the query was not using XXQP_QP_PREQ_QUAL_TMP_T_N1, but instead was using QP_PREQ_QUAL_TMP_N1.

     Taking another approach, in a BCV copy of PROD, when he tried removing the statistics, or updating them, it was either causing a full table scan or reverting back to PROD-like behavior of using XXQP_QP_PREQ_QUAL_TMP_T_N1, respectively.

     So the variant in the situation was the custom index. The question was whether it was needed at all? He decided to look a little further into this, and found that there are
currently 11 queries in the SQL area that were referring to this index. So somehow, the custom index wasn't really helping. This sql was useful for finding out the current sqls using the custom index was:

         SELECT DISTINCT old_hash_value
           FROM v$sql_plan
           JOIN v$sqltext_with_newlines
          using (sql_id)
           JOIN v$sql
          using (sql_id)
          WHERE object_name  = 'XXQP_QP_PREQ_QUAL_TMP_T_N1'
            AND object_owner = 'QP';

A Reliable validation point..

       So was this index being used in 9i at all? Was there another way to confirm this for PROD? Luckily, from a conscious choice, we had taken a backup of 9i's perfstat schema, which had historical sql plans being used by the system too (happens with snap_level >=7). Brian proceeded to imported the perfstat data into the BCV copy and ran this query to determine the sql plans that were using the custom index 'XXQP_QP_PREQ_QUAL_TMP_T_N1:

         SELECT /*+ PARALLEL(ssp, 8) */
                DISTINCT plan_hash_value
           FROM perfstat.stats$sql_plan ssp
          WHERE object_owner = 'QP'
            AND object_name = 'XXQP_QP_PREQ_QUAL_TMP_T_N1';

      This query yielded no queries in at least the last 30 days of PROD's 9i days in which this custom index was used. Simply speaking, this index was not being used in the 9i PROD instance.

      Brian then worked with a developer to get a script for invoking this piece of code, for testing purposes. He thought of the possibility of dropping the index and letting the CBO do either a full table scan or use another index (after gathering statistics).

Testing Results..

      Based on the code Brian got, and by doing two things -- deleting any statistics that exist on the QP_PREQ_QUAL_TMP_T table and dropping the custom
XXQP_QP_PREQ_TMP_T_N1, he did see an improvement of the pricing performance. Here's the summary of the pre-change performance:

call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse      841      0.13       0.12          0          0          0          0
Execute  36094      8.19       8.79        285     371446     118861      36701
Fetch    30680     23.21      24.20      21307    3083457          0      41171
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total    67615     31.53      33.12      21592    3454903     118861      77872

      After making the changes:

call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse     1018      0.18       0.24          0          0          0          0
Execute  36273      8.66       9.38         31     369674     106981      36701
Fetch    30891     12.19      12.47          0    1464382          0      41352
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total    68182     21.03      22.10         31    1834056     106981      78053

The new performance shows gets being about half of the pre-change value, and elapsed time is about two thirds of the previous performance.

Here's the code for the deletion of statistics and dropping the index:

    BEGIN
        dbms_stats.delete_table_stats(ownname => 'QP', tabname =>  'QP_PREQ_QUAL_TMP_T', cascade_indexes => true);
    END;
    /

   DROP INDEX QP.XXQP_QP_PREQ_QUAL_TMP_T_N1;

To back out:.

   CREATE INDEX QP.XXQP_QP_PREQ_QUAL_TMP_T_N1 ON
      QP.QP_PREQ_QUAL_TMP_T
      (UNIQUE_KEY, PRICING_STATUS_CODE, PRICING_PHASE_ID);

    BEGIN
        fnd_stats.gather_table_stats(ownname => 'QP', tabname =>    'QP_PREQ_QUAL_TMP_T', CASCADE => FALSE);
        fnd_stats.gather_index_stats(ownname => 'QP', indname =>    'XXQP_QP_PREQ_QUAL_TMP_T_N1');
    END;
    /

Learning from this anecdote..

One question could be: why did we not create a 10g sql profile (9i's stored outline) for fixing the sql at hand? The only caveat there is, you need a sql profile for every possible sql that would potentially use QP.QP_PREQ_QUAL_TMP_T, which is not an easy proposition.

Even though we may gather statistics on all related objects, the CBO may not choose the best sql plan for really high number of executions. On another note, it pays to have the history of sql plans of working scenarios. For promoting the CBO to choose the optimal plan, It may be required to drop some indexes to force a less costlier plan (over a million executions).

Note: This article is being produced with the explicit permission of Brian Fane, and is aimed at sharing tips in the Oracle world for other people who might be in the same situation.

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 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.