Wednesday, January 21, 2015

ORA-00942 for WMSYS.OWM_MIG_PKG When Upgrading from 11.2.0.3 to 11.2.0.4

"ORA-06512: at "WMSYS.OWM_MIG_PKG", line 1579" was observed while upgrading 11.2.0.3 enterprise edition to 11.2.0.4. Upgrade was continued selecting the ignore option. Show below is the output from the upgrade result summary.

If after the upgrade the database registry shows the status valid for oracle workspace manager then this error could be safely ignored.
COMP_ID                        COMP_NAME                                STATUS     VERSION
------------------------------ ---------------------------------------- ---------- -----------
EM                             Oracle Enterprise Manager                VALID      11.2.0.4.0
OWB                            OWB                                      VALID      11.2.0.3.0
APEX                           Oracle Application Express               VALID      3.2.1.00.12
AMD                            OLAP Catalog                             VALID      11.2.0.4.0
SDO                            Spatial                                  VALID      11.2.0.4.0
ORDIM                          Oracle Multimedia                        VALID      11.2.0.4.0
XDB                            Oracle XML Database                      VALID      11.2.0.4.0
CONTEXT                        Oracle Text                              VALID      11.2.0.4.0
EXF                            Oracle Expression Filter                 VALID      11.2.0.4.0
RUL                            Oracle Rules Manager                     VALID      11.2.0.4.0
OWM                            Oracle Workspace Manager                 VALID      11.2.0.4.0
CATALOG                        Oracle Database Catalog Views            VALID      11.2.0.4.0
CATPROC                        Oracle Database Packages and Types       VALID      11.2.0.4.0
JAVAVM                         JServer JAVA Virtual Machine             VALID      11.2.0.4.0
XML                            Oracle XDK                               VALID      11.2.0.4.0
CATJAVA                        Oracle Database Java Packages            VALID      11.2.0.4.0
APS                            OLAP Analytic Workspace                  VALID      11.2.0.4.0
XOQ                            Oracle OLAP API                          VALID      11.2.0.4.0
RAC                            Oracle Real Application Clusters         VALID      11.2.0.4.0




Useful metalink notes
ORA-00942 during database upgrade of Oracle Workspace Manager [ID 1399508.1]
Post-Upgrade Status Script utlu112s.sql Fails with ORA-942 [ID 1051991.1]
ORA-20194 And ORA-01720 During Upgrade of Oracle Workspace Manager When Upgrading from 11.2.0.3 to 11.2.0.4 [ID 1666657.1]

Related Posts
Upgrading RAC from 11.2.0.3 to 11.2.0.4 - Grid Infrastructure
Upgrading RAC from 11.2.0.3 to 11.2.0.4 - Database

Thursday, January 1, 2015

Upgrading 12c CDB and PDB from 12.1.0.1 to 12.1.0.2

An earlier post listed the steps for upgrading non-CDB in a data guard configuration from 12.1.0.1 to 12.1.0.2. This post shows the steps for upgrading a CDB with a PDB from 12.1.0.1 to 12.1.0.2. This is not a complete how to guide, refer (1933011.1) and oracle documentation for complete checklist for upgrade. The software upgrade from 12.1.0.1 to 12.1.0.2 is similar to 11gR2 upgrades where new database software is installed in a new oracle home location (out of place upgrade). It is assumed that software upgrade is done and only remaining task is the database upgrade.
Open all PDBs before running the preupgrade script. The CDB used here is called cdb12c and the PDB is called pdbone.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBONE                         MOUNTED

SQL>  alter pluggable database PDBONE open;

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBONE                         READ WRITE NO
Run preupgrade script from the 12.1.0.2 home or download from (884522.1). Preuprade script consists of two *.sql files. Copy them to a temporary location.
mkdir -p /home/oracle/tmp
cp preupgrd.sql utluppkg.sql /home/oracle/tmp
Execute the catcon.pl perl script (refer 1932340.1) which will run the upgrade script in all of the containers (CDB and PDB).
/opt/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl catcon.pl -d /home/oracle/tmp -l /home/oracle/tmp -b pdblogs preupgrd.sql
-d specify where the preupgrade scripts have been copied to and -b specify the base for generated log files.
The generated preupgrade log file will have sections for each container as shown below (not the entire log output).
more  /opt/app/oracle/cfgtoollogs/cdb12c/preupgrade/preupgrade.log

Script Version: 12.1.0.2.0 Build: 009
**********************************************************************
   Database Name:  CDB12C
  Container Name:  CDB$ROOT
    Container ID:  1
         Version:  12.1.0.1.0
      Compatible:  12.1.0.0.0
       Blocksize:  8192
        Platform:  Linux x86 64-bit
   Timezone file:  V18
**********************************************************************


**********************************************************************
   Database Name:  CDB12C
  Container Name:  PDB$SEED
    Container ID:  2
         Version:  12.1.0.1.0
      Compatible:  12.1.0.0.0
       Blocksize:  8192
        Platform:  Linux x86 64-bit
   Timezone file:  V18
**********************************************************************


**********************************************************************
   Database Name:  CDB12C
  Container Name:  PDBONE
    Container ID:  3
         Version:  12.1.0.1.0
      Compatible:  12.1.0.0.0
       Blocksize:  8192
        Platform:  Linux x86 64-bit
   Timezone file:  V18
**********************************************************************
**********************************************************************
--> Oracle Catalog Views                   [upgrade]  VALID
--> Oracle Packages and Types              [upgrade]  VALID
--> JServer JAVA Virtual Machine           [upgrade]  VALID
--> Oracle XDK for Java                    [upgrade]  VALID
--> Real Application Clusters              [upgrade]  OPTION OFF
--> Oracle Workspace Manager               [upgrade]  VALID
--> OLAP Analytic Workspace                [upgrade]  VALID
--> Oracle Label Security                  [upgrade]  VALID
--> Oracle Database Vault                  [upgrade]  VALID
--> Oracle Text                            [upgrade]  VALID
--> Oracle XML Database                    [upgrade]  VALID
--> Oracle Java Packages                   [upgrade]  VALID
--> Oracle Multimedia                      [upgrade]  VALID
--> Oracle Spatial                         [upgrade]  VALID
--> Oracle OLAP API                        [upgrade]  VALID
**********************************************************************
Rectify any outstanding errors and warning before upgrade.
@/opt/app/oracle/cfgtoollogs/cdb12c/preupgrade/preupgrade_fixups.sql
Run dbua from the new 12.1.0.2 home. Select upgrade database option.
Select the CDB to upgrade. There's only one CDB in this server.
The PDBs will be also upgraded along with the CDB. Any PDB created in the CDB will be auto detected by the CDB.
Select upgrade related options as desired.




Upgrade summary
During the upgrade it is possible to view the progress of each the containers (CDB and PDBs)
Once the upgrade has completed click the upgrade result button to view the upgrade results. The DBUA is exited through this process of viewing upgrade results and then close button. The finish button is not active during this time.

Once the upgrade is complete run the post upgrade script
@/opt/app/oracle/cfgtoollogs/cdb12c/preupgrade/postupgrade_fixups.sql
The preupgrade.log also states the following
After your database is upgraded and open in normal mode you must run
 rdbms/admin/catuppst.sql which executes several required tasks and completes
 the upgrade process.

 You should follow that with the execution of rdbms/admin/utlrp.sql, and a
 comparison of invalid objects before and after the upgrade using
 rdbms/admin/utluiobj.sql
Run these scripts to complete the upgrade. The remaining step is to update the compatibility parameter (compatible=12.1.0.2.0). As this is irreversible make sure the database is performing as expected before updating the compatible parameter. If ASM is used then compatible.asm and compatible.rdbms parameters may need updating as well.
Because of a bug in 12.1.0.2 the upgrade step not registered in the database registry history.
SQL>  select * from dba_registry_history;

ACTION_TIME                   ACTION          NAMESPACE   VERSION            ID COMMENTS
----------------------------- --------------- ----------- ---------- ---------- --------------------
20-JUN-14 01.26.28.005739 PM  APPLY           SERVER      12.1.0.1            0 Patchset 12.1.0.0.0
25-NOV-14 04.19.50.469828 PM  VIEW INVALIDATE                           8289601 view invalidation
To resolve this apply patch 19518079. However this patch itself has an issue as it fails with
Error: prereq checks failed!
  patch 19518079: apply script /opt/app/oracle/product/12.1.0/dbhome_2/sqlpatch/19518079/18024100/19518079_apply.sql does not exist
Prereq check failed, exiting without installing any patches.
Reason is the directory path 19518079/18024100 is missing. Create the directory path inside sqlpatch and move the 19518079_apply.sql into it and run datapatch. After which the upgrade step is listed in the dba_registry_history
SQL>  select * from dba_registry_history;

ACTION_TIME                   ACTION          NAMESPACE   VERSION            ID COMMENTS
----------------------------- --------------- ----------- ---------- ---------- --------------------
20-JUN-14 01.26.28.005739 PM  APPLY           SERVER      12.1.0.1            0 Patchset 12.1.0.0.0
25-NOV-14 04.19.50.469828 PM  VIEW INVALIDATE                           8289601 view invalidation
25-NOV-14 05.40.16.396667 PM  UPGRADE         SERVER      12.1.0.2.0            Upgraded from 12.1.0.1.0

Related Post
Upgrading RAC from 11.2.0.4 to 12.1.0.2 - Database
Upgrade Oracle Database 12c1 from 12.1.0.1 to 12.1.0.2
Upgrade from 11.1.0.7 to 11.2.0.4 (Clusterware, ASM & RAC)
Upgrading from 10.2.0.4 to 10.2.0.5 (Clusterware, RAC, ASM)
Upgrade from 10.2.0.5 to 11.2.0.3 (Clusterware, RAC, ASM)
Upgrade from 11.1.0.7 to 11.2.0.3 (Clusterware, ASM & RAC)
Upgrading from 11.1.0.7 to 11.2.0.3 with Transient Logical Standby
Upgrading from 11.2.0.1 to 11.2.0.3 with in-place upgrade for RAC
In-place upgrade from 11.2.0.2 to 11.2.0.3
Upgrading from 11.2.0.2 to 11.2.0.3 with Physical Standby - 1
Upgrading from 11.2.0.2 to 11.2.0.3 with Physical Standby - 2
Upgrading from 11gR2 (11.2.0.3) to 12c (12.1.0.1) Grid Infrastructure

Useful metalink notes
Master Note For Oracle Database 12c Release 1 (12.1) Database/Client Installation/Upgrade/Migration Standalone Environment (Non-RAC) [ID 1520299.1]
Complete Checklist for DBUA Upgrade from 12.1.0.1 to 12.1.0.N [ID 1933011.1]
How to Download and Run Oracle's Database Pre-Upgrade Utility [ID 884522.1]
How to execute sql scripts in Multitenant environment (catcon.pl) [ID 1932340.1]
Complete checklist for 12c R1 PDB upgrade (Upgrading single/multiple PDB) [ID 1933391.1]
Complete checklist for manual upgrade from 12.1.0.1 to 12.1.0.N (Full CDB Upgrade) [ID 1932762.1]
Oracle Database 12c Release 1 (12.1) DBUA : Understanding New Changes with All New 12.1 DBUA [ID 1493645.1]
Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA [ID 1516557.1]
Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST [ID 1509653.1]
DBA_REGISTRY_HISTORY Does Not Get Updated on Database Upgraded to 12.1.0.2 [ID 1941383.1]
Bug 19518079 - DBA_REGISTRY_HISTORY is not updated during upgrade to 12.1.0.2 [ID 19518079.8]
dba_registry_history Table On Newly Created 11.2.0.2 Database Shows PSU Entry[ID 1367065.1]
"VIEW INVALIDATE" In Action Column Dba_Registry_History During Upgarde To 11.2.0.2 [ID 1390402.1]