Problem durind Upgrade to oracle 11.2.0.1 with SYS.AUD$ tables

[Former Member]

Hi!

I'm upgrading a Oracle DB from 10.2.0.4 to 11.2.0.1 (with latest Patches BUNDLE - October 2010).

OS is AIX 5.3

During the pre upgrade scripts (PRE_UPGRADE_TASKS.sql) I had:

Purging Recyclebin
This reduces the time needed for upgrading the database.
Truncating SYS.AUD$
This reduces the time needed for upgrading the database.
TRUNCATE TABLE SYS.AUD$
                   *
ERROR at line 1:
ORA-01702: a view is not appropriate here

We decided to go on anyway....

But, during the DBUA we had a lot of

ORA-01403: nodata found with ORA-06512 at line 5

Looking into the log we saw:

DECLARE
2    schema_name        VARCHAR2(10);
3  BEGIN
4    -- find out in which schema AUD$ table exists
5    SELECT u.name INTO schema_name FROM obj$ o, user$ u
6             WHERE o.name = 'AUD$' AND o.type#=2 AND o.owner# = u.user#
7                   AND u.name IN ('SYS', 'SYSTEM');
8
9    -- construct Alter Table statement and execute it
10    EXECUTE IMMEDIATE
11        'ALTER TABLE ' || dbms_assert.enquote_name(schema_name, FALSE)
12                       || '.AUD$ add ('
13                       || ' obj$edition varchar2(30))';
14  END;
15  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

And seem that this errors (a lot!!) are related to the SYS.AUD$.

We have checked via sqlplus that the TABLE SYS.AUD$ do not exist into the DB.

Any idea to go on ??

Regards

Pierfrancesco

Edited by: Pierfrancesco Cocco on Oct 20, 2010 10:47 AM

[Former Member]

Hi,

I believe Oracle 11.2 is not yet supported by SAP. Please check and confirm this before you do an upgrade.

Regards,

Hari Kishan

[former_member204746]

Hari Kishan,

this is completely wrong.

SAP has been supporting Oracle 11 for almost a year now.

[Former Member]

NOW I want to repeat the upgrade.

I'm restoring now the DB 10.2.0.2.

After that I want to start again the UPGRADE.

How can I check if I'm in the case you describe me, and, if yes, how can I turn the DB in a normal and supported situation ?

Of course I can even disable ALL the AUDIT if necessary but I have no idea on how to recreate the SYS.AUD$.

THX in advance

[nikolay_kumanov]

I am not sure that you will go back to a supported situation without calling support. Also I am not sure if my advice will work and if it will not cause any damage!

To check if this is the case, as user SYS

select * from dba_views where view_name='AUD$';

This will show if a view exists and to which table it points. Then you can reverse the procedure from the Oracle note:

Start the database with :

audit_trail=none

As user sys:

rename aud$ to aud$_temp;

create table aud$ as select * from XXX.aud$; -- you get the table name from the view

create index i_aud1 on aud$(sessionid, ses$tid);

grant delete on aud$ to delete_catalog_role;

Once again - I have never done it, I don't know whether it will work

[nikolay_kumanov]

IMHO, it looks as if someone has applied Oracle document 72460.1, moved the AUD$ table to another schema (probably not SYSTEM) and created a view SYS.AUD$ to point to it. Oracle says that this is not supported and can cause problems on upgrades.

If it were my database and if I wouldn't need the audit records, I would check if this is the case, drop the view and recreate an empty SYS.AUD$ table. But I am afraid to suggest it to someone else - better call support.