Skip to content

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

connect / as sysdba
startup upgrade
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
shutdown immediate
startup

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:

connect / as sysdbadelete from sys.objauth$
where obj# IN (SELECT obj# FROM obj$ WHERE subname = ‘$DEPRECATED$’);

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

— check with CDB and PDB

connect / as sysdba

update obj$ set status = 1 where name = ‘V$XS_SESSION_ROLE’ and subname = ‘$DEPRECATED$’;
commit;

select * from dba_invalid_objects;

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: