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.....
very good
ReplyDeletevery good thanks admin
ReplyDeletenakliyat firması olarak görev yapan firma
ReplyDeleteAwesome, Thank you a lot. Firstly, I thought that this blog was obscure and lengthy, But it worked. Thanks.
ReplyDelete