xmldb - gone but not forgotten?




During a phase of patching this week to 11.2.0.4 i came across an unusual error as part of the preparation stage. Running the utlu112i script as normal produced the normal output - see below

SYS@DB>@/oracle/11.2.0.4.3.DB/rdbms/admin/utlu112i
Oracle Database 11.2 Pre-Upgrade Information Tool 12-11-2014 19:05:15
Script Version: 11.2.0.4.0 Build: 007
.
**********************************************************************
Database:
**********************************************************************
--> name:          DB
--> version:       11.2.0.2.0
--> compatible:    11.2.0.0.0
--> blocksize:     8192
--> platform:      Linux IA (32-bit)
--> timezone file: V14
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 3626 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 752 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: ON
**********************************************************************
FlashbackInfo:
--> name:          /oracle/DB/recovery_area
--> limit:         4500 MB
--> used:          3505 MB
--> size:          4500 MB
--> reclaim:       3272.2451171875 MB
--> files:         48
WARNING: --> Flashback Recovery Area Set.  Please ensure adequate disk space              in recover
y areas before performing an upgrade.
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 32-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
WARNING: --> "sga_target" needs to be increased to at least 412 MB
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
WARNING: --> "sga_target" needs to be increased to at least 596 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER SYS has 59 INVALID objects.
WARNING: --> Your recycle bin contains 22 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database.  The command:
        PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************

So apart from the normal kind of stuff the main thing that stands out is the invalid objects in the SYS schema - ignoring that for now as i want to show what else was wrong.

The next thing i tried was a purge of the recycle bin - this produced the following error:

SYS@DB>purge dba_recyclebin;
purge dba_recyclebin
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04098: trigger 'SYS.XDB_PI_TRIG' is invalid and failed re-validation

So - that doesn't look good - the trigger is obviously a 'database wide' one and is firing on the purging of the old objects - lets try and compile it

SYS@DB>alter trigger SYS.XDB_PI_TRIG compile;

Warning: Trigger altered with compilation errors.

Kind of expected that - so why does it error?

SYS@DB>show errors
Errors for TRIGGER SYS.XDB_PI_TRIG:
3/5      PL/SQL: Statement ignored
3/13     PLS-00905: object SYS.IS_VPD_ENABLED is invalid

OK - so whats that?

SYS@DB>select * from dba_objects where object_name='IS_VPD_ENABLED';
SYS
IS_VPD_ENABLED
                                   117668                FUNCTION            21-OCT-14
11-DEC-14       2014-12-11:19:05:58 INVALID N N N          1

Does that compile?

SYS@DB>alter function IS_VPD_ENABLED compile;

Warning: Function altered with compilation errors.

So what's wrong?

SYS@DB>show errors
Errors for FUNCTION IS_VPD_ENABLED:
0/0      PL/SQL: Compilation unit analysis terminated
4/43     PLS-00421: circular synonym 'PUBLIC.DBMS_XDBZ'

Hmm OK - lets just see what state the DBA_REGISTRY is in - specifically i want to see if there is a mention of XMLDB

SYS@DB>select * from dba_registry;
CATALOG
Oracle Database Catalog Views
11.2.0.2.0                     VALID                             05-MAR-2012 20:28:55
SERVER                         SYS                            SYS
DBMS_REGISTRY_SYS.VALIDATE_CATALOG



CATPROC
Oracle Database Packages and Types
11.2.0.2.0                     VALID                             05-MAR-2012 20:28:55
SERVER                         SYS                            SYS
DBMS_REGISTRY_SYS.VALIDATE_CATPROC

APPQOSSYS,DBSNMP,DIP,ORACLE_OCM,OUTLN,SYSTEM

Nope - no mention of it - lets double check what that synonym is pointing at

SYS@DB>select * from dba_synonyms where synonym_name='DBMS_XDBZ';
PUBLIC                         DBMS_XDBZ                      XDB
DBMS_XDBZ

Sure enough it's not there it's an orphaned synonym

SYS@DB>desc XDB.DBMS_XDBZ
ERROR:
ORA-04043: object XDB.DBMS_XDBZ does not exist

There is nothing at all owned by XDB, in fact the user is absent.

SYS@DB>select * from dba_objects where owner='XDB';

Lets have a look at the trigger source

SYS@DB>select * from dba_triggers where trigger_name='XDB_PI_TRIG';

SYS                            XDB_PI_TRIG                    BEFORE EVENT
DROP OR TRUNCATE
SYS                            DATABASE

REFERENCING NEW AS NEW OLD AS OLD

ENABLED
sys.xdb_pi_trig
BEFORE DROP OR TRUNCATE on DATABASE
PL/SQL      BEGIN                                                                            NO
              BEGIN
                IF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xd
            b.DBMS_XDBZ.IS_ENABLED_CONTENTS)) THEN
                  xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionar
            y_obj_name);
                ELSIF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name,
             xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN
                  xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dicti
            onary_obj_name);
                END IF;
              EXCEPTION
                WHEN OTHERS THEN
                 null;
              END;
            END;
NO  NO  NO  NO  NO  NO  NO

So lets drop the trigger as it is purely for xmldv and try the purge again - and sure enough it works

SYS@DB>drop trigger sys.xdb_pi_trig;
SYS@DB>purge dba_recyclebin;

And remove the invalid synonym

SYS@DB>drop public synonym dbms_xdbz;

I also removed an additional database wide trigger that had reference to XDB, there were still a lot of SYS objects in an invalid state (50+) but nothing that would cause an issue for the patch so I'll deal with those later.

So basically what had happened was that XDB had been installed at some point (no idea when) and then removed (though not completely cleanly) and this created the issue - a manual tiny up was easy enough - though it's never nice to have to mess around in SYS and the official line would always be to check with oracle support when doing this kind of thing.

I thought that iff XDB was ever installed it always remained in the REGISTRY in some 'removed' type status - maybe thats a newer thing or some intermediate status - but in this case it was completely absent.

Anyway - goes to show what happens when it's partially removed.

I would have said don;t install it unless you have to (XDB that is) - but since 12c you don't get a choice in the matter..... captors runs xdv as it's required for other components now.....



3 comments: