Monday, December 5, 2011

enq JI - contention

This enqueue used to prevent two sessions refreshing a materialized view at the same time. It is unavoidable if the situation allows multiple sessions to refresh at the same time.
Even when multiple sessions are trying to refresh the same materialized view, the refresh process come to a conclusion, unless one of the session that is holding the enqueue dies or killed without the immediate option.
Following test case shows two sessions trying to refresh the same materialized view resulting in high JI contention but both session ultimately finishing the refresh task.

1. Create two tables and the materialized view.
create table x (a number primary key, b number);
create table y (c number,d number, foreign key (c) references x(a));

begin
for i in 1 .. 10
loop
insert into x values (i, i + 10);
end loop;
end;
/

begin
for i in 1 .. 5
loop
insert into y values (i, i + 5);
end loop;
end;
/

create materialized view log on x with rowid;
create materialized view log on y with rowid;

CREATE MATERIALIZED VIEW mvtest (p,q,yid,xid) BUILD IMMEDIATE USING INDEX REFRESH FAST ON demand AS select y.d p,x.b q,y.rowid yid,x.rowid xid from y, x where x.a=y.c;
2. Open three sqlplus sessions for the test. Two (called session 2, 3 from now on) will be running the refresh and other session will be used to synchronize the starting of the to refresh sessions (called session 1 from now on).

3. On session 1 run the following pl/sql code block to create and explicitly lock an id
declare
    i pls_integer;
begin
    i := dbms_lock.request(10);
end;
/
4. On session 2 and 3 run the following code. Both session will hang unable to get the lock id 10 in shared mode (which is held in exclusive mode by session 1). However once they obtain the lock both will try to refresh (fast) the materialized view 1000 times
declare
    i pls_integer;
begin
    i := dbms_lock.request(10,DBMS_LOCK.S_MODE);

    for i in 1 .. 1000
    loop
     dbms_snapshot.refresh('mvtest','F'); 
    end loop;
end;
/
5. Release the lock on id 10 by running the following code which will start the refresh process
declare
    j pls_integer;
begin
    j := dbms_lock.release(10);
end;
/
6. Observer the JI contention on em console or APConsole

If the dba_waiters view was queried repeatedly while the refresh process is going on, it could be observed that sid of session 2 and session 3 alternating between holding session and waiting session.
Both session eventually will complete and return to sql prompt.
However as said earlier if the refresh sessions are serialized this will not result in contention and refresh could complete much quicker. (The last bit of CPU spike at the end of the graph above).

Some useful metalink notes.
What is JI enqueue used for? [ID 580001.1]
Materialized View Refresh is Hanging With JI Contention [ID 1358453.1]
Monitoring Locks During Materialized View Refreshes [ID 258258.1]
MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring [ID 258252.1]
Performance Degradation and JI Lock Contention Observed During Batch Load of Tables With On-Commit Materialized Views [ID 371869.1]

Thursday, December 1, 2011

Upgrading from 10.2.0.4 to 11.2.0.3

There's a earlier blog about upgrading from 10g to 11gR2. This blog is to add some missing points in the previous blog and additional information with regard to upgrading from 10.2.0.4 to 11.2.0.3. Another post is available with upgrading RAC environments from 10.2.0.5 to 11.2.0.3

Following metalink notes are useful for this upgrade processes.
Upgrade Advisor: Database from 10.2 to 11.2 [ID 251.1]
Complete Checklist to Upgrade the Database to 11gR2 using DBUA [ID 870814.1]
Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
Oracle Database Upgrade Path Reference List [ID 730365.1]
Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results [ID 1392633.1]

The database to upgrade is a 10.2.0.4 with CPU 2009 July being the latest CPU that has been applied on it. In other words no CPU or PSU released after July 2009 has been applied on this database.

Secondly it's a standard edition database with the following registry components.
COMP_NAME                           STATUS
----------------------------------- -------
OLAP Analytic Workspace             INVALID
OLAP Catalog                        INVALID
Oracle OLAP API                     INVALID
JServer JAVA Virtual Machine        VALID
Oracle Data Mining                  VALID
Oracle Database Catalog Views       VALID
Oracle Database Java Packages       VALID
Oracle Database Packages and Types  VALID
Oracle Enterprise Manager           VALID
Oracle Expression Filter            VALID
Oracle Rules Manager                VALID
Oracle Text                         VALID
Oracle Workspace Manager            VALID
Oracle XDK                          VALID
Oracle XML Database                 VALID
Oracle interMedia                   VALID
Spatial                             VALID
Running utlrp.sql gives the following output as the number of invalid objects
OBJECTS WITH ERRORS
-------------------
                  8
DOC> The following query reports the number of errors caught during
..
ERRORS DURING RECOMPILATION
---------------------------
                          0
4 OLAPSYS packages and 4 public role synonyms are the objects with errors in this case.
OLAP* and several other components are not valid options on a standard edition database. In this case only OLAP* components are invalid but spatial, intermedia, data mining are all not valid for a standard edition database. But the registry will be valid on a fresh installation but after installing CPU or revoking execute on utl* packages for public and etc some of these will become invalid. The earlier blog was of such a fresh installation of 10gR2 that didn't have any components invalid.

Following metalink notes could be used to install/deinstall various components that are not valid in standard edition (if they were install initially).
How To Find Out If OLAP Is Being Used And How To Remove OLAP [ID 739032.1]
Remove Invalid OLAP Objects From SYS And OLAPSYS Schemas [ID 565773.1]
Steps for Manual De-installation of Oracle Spatial [ID 179472.1]
Removed Spatial Option But Spatial Still Appears In V$Option [ID 273573.1]
Where to Find the Information to Install, Upgrade, Downgrade and Deinstall interMedia/Oracle Multimedia? [ID 337415.1](on 10gR2 deinstall $OH/ord/im/admin/imdinst.sql, on 11gR2 $OH/rdbms/admin/catcmprm.sql ORDIM )

Trying to uninstall all non valid components could result in more objects with errors which caused problems during a test upgrade and made the upgrade to fail. If it is decided to remove all the components that are not valid in standard edition it is advisable to make sure that there are no invalid objects prior to upgrade. In this case the upgrade will continue with these three OLAP* components invalid.

The pre-upgrade information tool's output is as follows
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 11-29-2011 16:14:13
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          stdb1
--> version:       10.2.0.4.0
--> compatible:    10.2.0.3.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 725 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 461 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
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]
**********************************************************************
--> background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"
--> user_dump_dest               11.1       DEPRECATED   replaced by  "diagnostic_dest"
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  INVALID
--> OLAP Catalog                 [upgrade]  INVALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Data Mining                  [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  INVALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.4.0 database timezone version
.... to the latest version which comes with the new release.
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 OLAPSYS has 4 INVALID objects.
.... USER PUBLIC has 4 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
        PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
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;

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
Carry out the steps recommended by this tool such as gathering dictionary and schema statistics and etc. More steps available on 837570.1 which are not relevant on this upgrade so they are not listed.

Another key difference is that 11.2.0.3 provides a way to upgrade the timezone at the same time the database is upgrade. Which was not there on the base 11gR2 release. Compare the 5th picture on the previous blog with the picture below which gives the option to upgrade the timezone.

There are several metalink notes related to timezone upgrade.
Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]
Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset [ID 1358166.1]
Before the upgrade the timezone file version was
upgrade 
SQL> select version from v$timezone_file;

   VERSION
----------
         4
Key points relevant to this are (quoting from the metalink note 1358166.1)If this reports a timezone version lower then 14 please see point B.3) in this document.

B.3) If your current timezone version is lower than 14 (typically 4):

B.3b) When upgrading from 10.1.0.x , 10.2.0.x or 11.1.0.x to 11.2.0.3:
For 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 there is no need to apply any patchset before upgrading to 11.2.0.3

Upgrade to 11.2.0.3.No need to apply DST patches on the 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 side first. You can skip any DST related upgrade instructions.
The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5.

After the upgrade to 11.2.0.3 you can:
(recommended) update the 11.2.0.3 database(s) to DSTv14 (standard DST version of 11.2.0.3) by following Note 977512.1 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from step 3a) onwards, when going to DSTv14 there is no need to apply any DST patch to the 11.2.0.3 home

(optional) update to a higher DST than 14 version if this is needed.
Apply the latest 11.2.0.3 DST update patch after the upgrade to 11.2.0.3 and use DDBMS_DST.

The lastest DST patch and related note on how to apply this is found in Note 412160.1 Updated Time Zones in Oracle Time Zone File patches under "C) Notes covering the current DST available updates". Simply follow the note for the latest DST update
. If decided it is possible to upgrade the database's timezone after the database upgrade by following the above metalink notes. In this case timezone was upgraded at the same time database was upgraded.




Save the em control incase later on it is decided to downgrade back to 10g.
Create a TNS name entry in the 10g Oracle Home (network/admin) with the same name as the SID before running the above
/opt/app/oracle/product/11.2.0/dbhome_1/bin/emdwgrd -save -sid stdb1 -path /home/oracle/emdctl
Enter sys password for database stdb1?

Database Unique Name : stdb1
Tue Nov 29 17:01:29 2011 - Validating DB Connection to stdb1 ... pass
Tue Nov 29 17:01:29 2011 - Verify EM DB Control files ... pass
ENV var EM_REMCP not defined, check if rcp or scp is configured.
RCP = /usr/bin/rcp -rp, REMSH = /usr/bin/rsh
shared = 0
Tue Nov 29 17:01:29 2011 - Creating directory ... created
Tue Nov 29 17:01:30 2011 - Stopping DB Control ... stopped
Tue Nov 29 17:01:35 2011 - Saving DB Control files
 ... saved
Tue Nov 29 17:01:35 2011 - Recompiling invalid objects ... recompiled
Tue Nov 29 17:01:36 2011 - Exporting sysman schema for stdb1 ... exported
Tue Nov 29 17:01:59 2011 - DB Control was saved successfully.
Tue Nov 29 17:01:59 2011 - Starting DB Control ... started
Tue Nov 29 17:03:33 2011 - Dump directory was dropped successfully.
Disable all batch and cron jobs.

Take a full backup of the database. (Better if it's a cold backup, not necessary)

Stop the 10g listener and create a listener on 11gR2 home and start it.

Set ORACLE_BASE ,ORACLE_HOME and PATH environment variable pointing to the 11gr2 home and run dbua from the 11gR2 home's bin. Upgrade summary is given below.

Upgrade process will omit some of the components from the upgrade process.

This is also mentioned when selecting the database to upgrade.



During the upgrade it could be seen that some of the components are not upgraded. These components are not valid on standard edition. Also note timezone upgrade.

Upgrade results



Verifying the timezone upgrade
SQL> select version from v$timezone_file;

   VERSION
----------
        14
Components status in the database registry
COMP_NAME                                     VERSION                        STATUS
--------------------------------------------- ------------------------------ -----------
Oracle Multimedia                             11.2.0.3.0                     INVALID
OLAP Analytic Workspace                       10.2.0.4.0                     OPTION OFF
OLAP Catalog                                  10.2.0.4.0                     OPTION OFF
Oracle Data Mining                            10.2.0.4.0                     OPTION OFF
Oracle OLAP API                               10.2.0.4.0                     OPTION OFF
Spatial                                       10.2.0.4.0                     OPTION OFF
JServer JAVA Virtual Machine                  11.2.0.3.0                     VALID
Oracle Database Catalog Views                 11.2.0.3.0                     VALID
Oracle Database Java Packages                 11.2.0.3.0                     VALID
Oracle Database Packages and Types            11.2.0.3.0                     VALID
Oracle Enterprise Manager                     11.2.0.3.0                     VALID
Oracle Expression Filter                      11.2.0.3.0                     VALID
Oracle Rules Manager                          11.2.0.3.0                     VALID
Oracle Text                                   11.2.0.3.0                     VALID
Oracle Workspace Manager                      11.2.0.3.0                     VALID
Oracle XDK                                    11.2.0.3.0                     VALID
Oracle XML Database                           11.2.0.3.0                     VALID

17 rows selected.
Oracle Multimedia(Oracle interMedia in 10g) is invalid after the upgrade. Following two metalink notes help identifying the reason for this
Things To Check When Oracle Multimedia/interMedia Is INVALID In DBA_REGISTRY Or The Version Is Not The Same As The Database Version [ID 1065954.1]
Verifying an Installed Version of Oracle Multimedia [ID 458228.1]

Running the validate_ordim gives the following errors
SQL> set serveroutput on
SQL> execute validate_ordim;
Locator INVALID OBJECTS: PRVT_IDX - 5 - 11
Locator INVALID OBJECTS: SDO_GEOM - 5 - 11
Locator INVALID OBJECTS: SAMCLUST_IMP_T - 5 - 14

PL/SQL procedure successfully completed.
From the output it seems that some of the spatial component related objects are invalid (since spatial option is off after the upgrade) thus oracle media that depends on it also becomes invalid. Remove the Oracle media component with (which also removes spatial component which is depends on media)
SQL> @?/rdbms/admin/catcmprm.sql ORDIM
About to remove Oracle Multimedia.
Checking to see if anyone is using Oracle Multimedia.

Oracle Multimedia is not being used
PL/SQL procedure successfully completed.

Are you sure you want to remove Oracle Multimedia (Y/N): y
PL/SQL procedure successfully completed.

Removing Oracle Multimedia
PL/SQL procedure successfully completed.
After remove dba registry status
COMP_NAME                           VERSION                        STATUS
----------------------------------- ------------------------------ --------------------------------------------
OLAP Analytic Workspace             10.2.0.4.0                     OPTION OFF
OLAP Catalog                        10.2.0.4.0                     OPTION OFF
Oracle Data Mining                  10.2.0.4.0                     OPTION OFF
Oracle OLAP API                     10.2.0.4.0                     OPTION OFF
JServer JAVA Virtual Machine        11.2.0.3.0                     VALID
Oracle Database Catalog Views       11.2.0.3.0                     VALID
Oracle Database Java Packages       11.2.0.3.0                     VALID
Oracle Database Packages and Types  11.2.0.3.0                     VALID
Oracle Enterprise Manager           11.2.0.3.0                     VALID
Oracle Expression Filter            11.2.0.3.0                     VALID
Oracle Rules Manager                11.2.0.3.0                     VALID
Oracle Text                         11.2.0.3.0                     VALID
Oracle Workspace Manager            11.2.0.3.0                     VALID
Oracle XDK                          11.2.0.3.0                     VALID
Oracle XML Database                 11.2.0.3.0                     VALID

15 rows selected.
This will increase the number of invalid objects but these will be OLAP related objects and has no impact on the rest of the database operation.
SQL>select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
OLAPSYS                        PACKAGE BODY                 3
OLAPSYS                        VIEW                        25
PUBLIC                         SYNONYM                     25
It is possible to drop the olapsys user and also drop remaining public synonyms.
DROP USER OLAPSYS CASCADE;
select 'drop '||object_type||' '||substr(object_name,1,40) ||';' from  dba_objects where status='INVALID';
After this expdp/impdp could run into following error
ORA-39127: unexpected error from call to "SYS"."DBMS_CUBE_EXP"."INSTANCE_EXTENDED_INFO_EXP"
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316
ORA-06512: at "SYS.DBMS_METADATA", line 9114
Metalink notes How To Remove or De-activate OLAP After Migrating From 9i To 10g or 11g [ID 467643.1] and ORA-39127 SYS.DBMS_CUBE_EXP.INSTANCE_EXTENDED_INFO_EXP ORA-44002 On Expdp After Upgrade To 11.2.0.2 [ID 1353491.1] explains the solution for this, which is If the OLAP option is not used delete DBMS_CUBE_EXP OLAP package from the export view as follows
SQL> select PACKAGE,SCHEMA,class from exppkgact$ where (schema,package) not in (select owner,object_name from dba_objects where object_type='PACKAGE');

PACKAGE                        SCHEMA                              CLASS
------------------------------ ------------------------------ ----------
DBMS_CUBE_EXP                  SYS                                     4

SQL> delete from sys.exppkgact$ where package = 'DBMS_CUBE_EXP' and schema= 'SYS';
1 row deleted.
SQL> commit;
Commit complete.
After this export will work as expected and there will no invalid objects nor any invalid database components
@?/rdbms/admin/utlrp
OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
..
ERRORS DURING RECOMPILATION
---------------------------
                          0

SQL> select comp_name,version,status from dba_registry order by 3,1,2;

COMP_NAME                           VERSION                        STATUS
----------------------------------- ------------------------------ -----------
OLAP Analytic Workspace             10.2.0.4.0                     OPTION OFF
Oracle Data Mining                  10.2.0.4.0                     OPTION OFF
Oracle OLAP API                     10.2.0.4.0                     OPTION OFF
JServer JAVA Virtual Machine        11.2.0.3.0                     VALID
Oracle Database Catalog Views       11.2.0.3.0                     VALID
Oracle Database Java Packages       11.2.0.3.0                     VALID
Oracle Database Packages and Types  11.2.0.3.0                     VALID
Oracle Enterprise Manager           11.2.0.3.0                     VALID
Oracle Expression Filter            11.2.0.3.0                     VALID
Oracle Rules Manager                11.2.0.3.0                     VALID
Oracle Text                         11.2.0.3.0                     VALID
Oracle Workspace Manager            11.2.0.3.0                     VALID
Oracle XDK                          11.2.0.3.0                     VALID
Oracle XML Database                 11.2.0.3.0                     VALID

14 rows selected.

This concluded the ugprade to 11.2.0.3. After the upgrade the database will exhibit three shared segment behavior that is only found on 11.2.0.3.