Deprecated view V$XS_SESSION_ROLE still exists after migration

SYMPTOMS

View V$XS_SESSION_ROLE still exists after migration from 11g to 12c and it is INVALID.

CAUSE

This view is not required in 12c and should have been dropped during the upgrade from 11g to 12c.

SOLUTION

A. To avoid this issue, please apply patch 22393016 as below:

1. Take a COLD database backup.

2. Apply patch 22393016 as below:

– install 12c software
– apply patch 22393016, which will provide the correct c1201000.sql
– upgrade database, this will use the corrected post-install scripts.

B. If the migration to 12c has been done and the view is present in the database, you have below options:

Option 1. Drop the view by running below workaround:

1. Take a COLD database backup.

2. Apply patch 22393016 as below:

– apply patch 22393016, which will provide the correct c1201000.sql
– shutdown database
– startup migrate
– @c1102000.sql
– shutdown database
– startup

Note: Do not run c1102000.sql on 12c database without applying patch 22393016 first. This patch provides the correct c1201000.sql.

If the patch is not applied before running @c1102000.sql, you will have invalid objects in the database.  If this accidentally happens, you can run catalog, catproc and utlrp.sql as below, for validating invalid objects:

NOTE: the database must be started in upgrade mode

Option 2. You can drop the view by following below steps:

1. Take a COLD database backup.

2. Delete the object from the data dictionary:

Please do not skip step 1, as you are dropping from the database dictionary.

Option 3. You can ignore the view, as it is not used in 12c.

Option 4. You can manually change the status in the Data Dictionary