Should AUD$ Table Exist on System Tablespace During DB Upgrade?
I see this following message on all My Oracle Support notes, which talks about database Oracle manual upgrade steps.
4.16 Verify the existence of the AUD$ tables
Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace.
If the AUD$ table is not in SYSTEM tablespace and not owned by the SYS user then before doing the upgrade put it back to the SYSTEM tablespace and it should be owned by SYS . When I come across the same kind of message even for the latest database version 12cR1 on MOS note 1503653.1, I thought of checking the true functionality of this warning. This doubt seems very valid especially when we have new feature named "DBMS_AUDIT_MGMT", which can be used to relocate and purge data for aud$ table from oracle rdbms version 10gR1. I created a database named "test" using rdbms version 11.2.0.3.0 and enabled database auditing. After few sessions testing, I see records on aud$ table. I moved the table and associated LOB segments after disabling db audit and enable it again after the activity completion. SQL> SELECT comp_id, status, SUBSTR(version,1,10) version, comp_name FROM dba_registry ORDER BY 1; COMP_ID STATUS VERSION COMP_NAME ------- -------- ---------------------------------------- ----------------------------------- CATALOG VALID 11.2.0.3.0 Oracle Database Catalog Views CATPROC VALID 11.2.0.3.0 Oracle Database Packages and Types SQL> show parameter audit_trail NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string NONE SQL> alter table sys.aud$ move tablespace users 2 LOB (sqltext) STORE AS lobsegment1 (TABLESPACE users ) 3 LOB (sqlbind) STORE AS lobsegment2 (TABLESPACE users ); Table altered. SQL> select count(1) from aud$; COUNT(1) ---------- 5 SQL> select distinct tablespace_name from dba_segments where segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$');
TABLESPACE_NAME
------------------------------
USERS SQL> ALTER SYSTEM SET audit_trail=db, extended scope=spfile; System altered. SQL> alter system set audit_sys_operations=TRUE scope=spfile; System altered. I configured Oracle home of version 12.1.0.1.0 and executed the pre-upgrade script "preupgrd.sql" on the 11gR2 oracle home. I didn't notice any error messages related to this table availability on different tablespace. Reviewed the steps required as per MOS note 1503653.1, omitted many points due to the nature of this database including step 4.16. I never faced any issues with the upgrade and even noticed the aud$ table exists on USERS tablespace only after the upgrade. SQL> SELECT comp_id, status, SUBSTR(version,1,10) version, comp_name FROM dba_registry ORDER BY 1; COMP_ID STATUS VERSION COMP_NAME ------- -------- ---------------------------------------- ----------------------------------- CATALOG VALID 12.1.0.1.0 Oracle Database Catalog Views CATPROC VALID 12.1.0.1.0 Oracle Database Packages and Types XDB VALID 12.1.0.1.0 Oracle XML Database SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner; no rows selected SQL> select count(1) from sys.aud$; COUNT(1) ---------- 5 SQL> select tablespace_name from dba_segments where segment_name='AUD$';
TABLESPACE_NAME
------------------------------
USERS SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner; no rows selected SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 12.0.0 SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u02/app/oracle/product/12.1.0/dbhome_1/rdbms/audit audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB, EXTENDED Checked few other MOS notes, it seems they really look for aud$ table existence of SYS schema only. FAQ : Database Upgrade And Migration (Doc ID 1352987.1) Which schema should the AUD$ table exist in while doing the upgrade ?
AUD$ table should exist in SYS schema while doing the upgrade. So this blog question remains valid. For those who keep bigger aud$ table on dedicated tablespace for better database performance, this relaxation means saving 1-3 hours of production database downtime. Is this the time to ask Oracle Support to review the point 4.16 to check for owner only for aud$ table? Note: This testing was carried out only from rdbms version 11gR2 to 12cR1. Please test this behavior on your test environment before you prepare action plan for the production upgrade.
