Wednesday, September 4, 2013

Upgrading Oracle Database 11203 to 12101.

Here I dint show the installation of Oracle 12c RDBMS. Please follow my previous post for the same.
http://dbarohit.blogspot.in/2013/08/installation-of-oracle-12c-cloud.html

You can upgrade the database to 12101 only when the existing database version is higher or equal than 11202.

Step 1.

Download the script dbupgdiag.sql from Oracle support Note. 556610.1
run that script to verify that all the components in dba_registry are valid.
And no invalid data dictionary objects exist in dba_objects.

SQL> @/u01/dbupgdiag.sql

Enter location for Spooled output:

Enter value for 1: /tmp

04_Sep_2013_0105 .log

db11g_



                          *** Start of LogFile ***

  Oracle Database Upgrade Diagnostic Utility       09-04-2013 01:05:36

===============
Hostname
===============

box1.oracle.com

===============
Database Name
===============

DB11G

===============
Database Uptime
===============

01:04 04-SEP-13

=================
Database Wordsize
=================

This is a 64-bit database

================
Software Version
================

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

=============
Compatibility
=============

Compatibility is set as 11.2.0.0.0

================
Archive Log Mode
================

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     61
Current log sequence           63

================
Auditing Check
================
........
.......
.......


DOC>
DOC>#################################################################
DOC>
DOC> If the JAVAVM component is not installed in the database (for
DOC> example, after creating the database with custom scripts), the
DOC> next query will report the following error:
DOC>
DOC>   select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC>   *
DOC>   ERROR at line 1:
DOC>   ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC>
DOC> If the JAVAVM component is installed, the query should succeed
DOC> with 'foo' as result.
DOC>
DOC>#################################################################
DOC>#


JAVAVM TESTING
---------------
foo


                            *** End of LogFile ***



Upload db_upg_diag_db11g_04_Sep_2013_0105.log from "/tmp" directory

SQL>

Step 2.


Run the '$ORACLE_HOME/rdbms/admin/utlrp.sql' to validate the invalid objects in the database till the time all the invalid objects become valid.

[oracle@box1 u01]$ cd /u01/app/oracle/product/12.1.0/db_1/rdbms/admin/

SQL> @utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-09-04 01:07:34

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-09-04 01:07:36

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL>

Step 3.

Copy the 'preupgrd.sql' and 'utluppkg.sql' from the 12c Oracle_home/rdbms/admin to the temp directory and run those scripts from the sqlplus.
[oracle@box1 12c_upg]$ ls
preupgrd.sql  utluppkg.sql

SQL> @preupgrd.sql
Loading Pre-Upgrade Package...
Executing Pre-Upgrade Checks...
Pre-Upgrade Checks Complete.
      ************************************************************

Results of the checks are located at:
 /u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade.log

Pre-Upgrade Fixup Script (run in source database environment):
 /u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade_fixups.sql

Post-Upgrade Fixup Script (run shortly after upgrade):
 /u01/app/oracle/cfgtoollogs/db11g/preupgrade/postupgrade_fixups.sql

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

         Fixup scripts must be reviewed prior to being executed.

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

      ************************************************************
                   ====>> USER ACTION REQUIRED  <<====
      ************************************************************

 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    prior to attempting your upgrade.
            Failure to do so will result in a failed upgrade.

           You MUST resolve the above errors prior to upgrade

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

SQL> !more /u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade.log
Oracle Database Pre-Upgrade Information Tool 09-04-2013 00:28:56
Script Version: 12.1.0.1.0 Build: 006
**********************************************************************
   Database Name:  DB11G
         Version:  11.2.0.3.0
      Compatible:  11.2.0.0.0
       Blocksize:  8192
        Platform:  Linux x86 64-bit
   Timezone file:  V14
**********************************************************************
                          [Renamed Parameters]
                     [No Renamed Parameters in use]
**********************************************************************
**********************************************************************
                    [Obsolete/Deprecated Parameters]
             [No Obsolete or Desupported Parameters in use]
**********************************************************************
                            [Component List]
**********************************************************************
--> 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]  VALID
--> Oracle Enterprise Manager Repository   [upgrade]  VALID
--> Oracle Text                            [upgrade]  VALID
--> Oracle XML Database                    [upgrade]  VALID
--> Oracle Java Packages                   [upgrade]  VALID
--> Oracle Multimedia                      [upgrade]  VALID
--> Oracle Spatial                         [upgrade]  VALID
--> Expression Filter                      [upgrade]  VALID
--> Rule Manager                           [upgrade]  VALID
--> Oracle Application Express             [upgrade]  VALID
--> Oracle OLAP API                        [upgrade]  VALID
**********************************************************************
                              [Tablespaces]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
     minimum required size: 1240 MB
--> SYSAUX tablespace is adequate for the upgrade.
     minimum required size: 1472 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
--> EXAMPLE tablespace is adequate for the upgrade.
     minimum required size: 78 MB

                      [No adjustments recommended]

**********************************************************************
**********************************************************************
                          [Pre-Upgrade Checks]
**********************************************************************
WARNING: --> Process Count may be too low

     Database has a maximum process count of 150 which is lower than the
     default value of 300 for this release.
     You should update your processes value prior to the upgrade
     to a value of at least 300.
     For example:
        ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
     or update your init.ora file.

WARNING: --> Enterprise Manager Database Control repository found in the database

     In Oracle Database 12c, Database Control is removed during
     the upgrade. To save time during the Upgrade, this action
     can be done prior to upgrading using the following steps after
     copying rdbms/admin/emremove.sql from the new Oracle home
   - Stop EM Database Control:
    $> emctl stop dbconsole

   - Connect to the Database using the SYS account AS SYSDBA:

   SET ECHO ON;
   SET SERVEROUTPUT ON;
   @emremove.sql
     Without the set echo and serveroutput commands you will not
     be able to follow the progress of the script.

WARNING: --> Existing DBMS_LDAP dependent objects

     Database contains schemas with objects dependent on DBMS_LDAP package.
     Refer to the Upgrade Guide for instructions to configure Network ACLs.
     USER APEX_030200 has dependent objects.

INFORMATION: --> OLAP Catalog(AMD) exists in database

     Starting with Oracle Database 12c, OLAP is desupported.
     If you are not using the OLAP Catalog component and want
     to remove it, then execute the
     ORACLE_HOME/oraolap/admin/catnoamd.sql script before or
     after the upgrade.

INFORMATION: --> Older Timezone in use

     Database is using a time zone file older than version 18.
     After the upgrade, it is recommended that DBMS_DST package
     be used to upgrade the 11.2.0.3.0 database time zone version
     to the latest version which comes with the new release.
     Please refer to My Oracle Support note number 977512.1 for details.


**********************************************************************
                      [Pre-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ********* Dictionary Statistics *********
                        *****************************************

Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^

**********************************************************************
                     [Post-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ******** Fixed Object Statistics ********
                        *****************************************

Please create stats on fixed objects two weeks
after the upgrade using the command:
   EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^

**********************************************************************
                   ************  Summary  ************

 0 ERRORS exist in your database.
 3 WARNINGS that Oracle suggests are addressed to improve database performance.
 2 INFORMATIONAL messages that should be reviewed prior to your upgrade.

 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

 If needed you may want to upgrade your timezone data using the process
 described in My Oracle Support note 977512.1
                   ***********************************


The script 'preupgrd.sql' creates one log file and two scripts.

Read  the ' /u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade.log' file for any error or recommendation.

Step 4.

run the 'preupgrade_fixups.sql' script.
SQL> @/u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade_fixups.sql

Pre-Upgrade Fixup Script Generated on 2013-09-04 00:28:53  Version: 12.1.0.1 Build: 006
Beginning Pre-Upgrade Fixups...

**********************************************************************
Check Tag:     DEFAULT_PROCESS_COUNT
Check Summary: Verify min process count is not too low
Fix Summary:   Review and increase if needed, your PROCESSES value.
**********************************************************************
Fixup Returned Information:
WARNING: --> Process Count may be too low

     Database has a maximum process count of 150 which is lower than the
     default value of 300 for this release.
     You should update your processes value prior to the upgrade
     to a value of at least 300.
     For example:
        ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
     or update your init.ora file.
**********************************************************************


**********************************************************************
Check Tag:     EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary:   Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
WARNING: --> Enterprise Manager Database Control repository found in the database

     In Oracle Database 12c, Database Control is removed during
     the upgrade. To save time during the Upgrade, this action
     can be done prior to upgrading using the following steps after
     copying rdbms/admin/emremove.sql from the new Oracle home
   - Stop EM Database Control:
    $> emctl stop dbconsole

   - Connect to the Database using the SYS account AS SYSDBA:

   SET ECHO ON;
   SET SERVEROUTPUT ON;
   @emremove.sql
     Without the set echo and serveroutput commands you will not
     be able to follow the progress of the script.
**********************************************************************


**********************************************************************
Check Tag:     DBMS_LDAP_DEPENDENCIES_EXIST
Check Summary: Check for dependency on DBMS_LDAP package
Fix Summary:   Network Objects must be reviewed manually.
**********************************************************************
Fixup Returned Information:
WARNING: --> Existing DBMS_LDAP dependent objects

     Database contains schemas with objects dependent on DBMS_LDAP package.
     Refer to the Upgrade Guide for instructions to configure Network ACLs.
     USER APEX_030200 has dependent objects.
**********************************************************************


**********************************************************************
Check Tag:     AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary:   Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> OLAP Catalog(AMD) exists in database

     Starting with Oracle Database 12c, OLAP is desupported.
     If you are not using the OLAP Catalog component and want
     to remove it, then execute the
     ORACLE_HOME/oraolap/admin/catnoamd.sql script before or
     after the upgrade.
**********************************************************************


**********************************************************************
                      [Pre-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ********* Dictionary Statistics *********
                        *****************************************

Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^


           **************************************************
                ************* Fixup Summary ************

 4 fixup routines generated INFORMATIONAL messages that should be reviewed.

**************** Pre-Upgrade Fixup Script Complete *********************






SQL> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @/u01/app/oracle/product/12.1.0/db_1/rdbms/admin/emremove.sql
SQL> @?/olap/admin/catnoamd.sql
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 5.

Copy the parameter file and password file from old oracle home to new oracle home.

[oracle@box1 dbs]$ cp spfiledb11g.ora /u01/app/oracle/product/12.1.0/db_1/dbs/
[oracle@box1 dbs]$ cp orapwdb11g /u01/app/oracle/product/12.1.0/db_1/dbs/

make new oracle home entry in '/etc/oratab' file.
[oracle@box1 dbs]$ vim /etc/oratab

Update the oracle user .bash_profile for the new 12c Oracle home. 

Copy the listener and tnsnames file from old oracle network directory to new one and make an entry into listener file for new oracle home.
Start the listener once done with above.

[oracle@box1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 04-SEP-2013 01:17:54

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/box1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=box1.oracle.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                04-SEP-2013 01:17:54
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/box1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=box1.oracle.com)(PORT=1521)))
The listener supports no services
The command completed successfully


Step 6.

Perform the database upgrade.

[oracle@box1 admin]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1
[oracle@box1 admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.1.0 Production on Wed Sep 4 01:19:13 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2037673984 bytes
Fixed Size                  2290120 bytes
Variable Size            1325403704 bytes
Database Buffers          704643072 bytes
Redo Buffers                5337088 bytes
Database mounted.
Database opened.

Step 7.


Run the 'catctl.pl' script. 

In 12c the new upgrade utility 'catctl.pl' replaces 'catupgrd.sql' 

[oracle@box1 admin]$ /u01/app/oracle/product/12.1.0/db_1/perl/bin/perl catctl.pl -n 8 -l /u01/app/oracle/product/12.1.0/db_1/diagnostics/ catupgrd.sql

Analyzing file catupgrd.sql
Log files in /u01/app/oracle/product/12.1.0/db_1/diagnostics/
14 scripts found in file catupgrd.sql
Next path: catalog.sql
32 scripts found in file catalog.sql
Next path: catproc.sql
37 scripts found in file catproc.sql
Next path: catptabs.sql
61 scripts found in file catptabs.sql
Next path: catpdbms.sql
205 scripts found in file catpdbms.sql
Next path: catpdeps.sql
77 scripts found in file catpdeps.sql
Next path: catpprvt.sql
260 scripts found in file catpprvt.sql
Next path: catpexec.sql
26 scripts found in file catpexec.sql
Next path: cmpupgrd.sql
16 scripts found in file cmpupgrd.sql

[Phase 0] type is 1 with 1 Files
catupstr.sql

[Phase 1] type is 1 with 3 Files
cdstrt.sql       cdfixed.sql      cdcore.sql

[Phase 2] type is 1 with 1 Files
ora_restart.sql

[Phase 3] type is 2 with 18 Files
cdplsql.sql      cdsqlddl.sql     cdmanage.sql     cdtxnspc.sql
cdenv.sql        cdrac.sql        cdsec.sql        cdobj.sql
cdjava.sql       cdpart.sql       cdrep.sql        cdaw.sql
cdsummgt.sql     cdtools.sql      cdexttab.sql     cddm.sql
catldr.sql       cdclst.sql

[Phase 4] type is 1 with 1 Files
ora_restart.sql

[Phase 5] type is 1 with 5 Files
cdoptim.sql      catsum.sql       catexp.sql       cddst.sql
cdend.sql

[Phase 6] type is 1 with 1 Files
catpstrt.sql

[Phase 7] type is 1 with 3 Files
catptyps.sql     catpgrants.sql   catgwm.sql

[Phase 8] type is 1 with 1 Files
ora_restart.sql

[Phase 9] type is 2 with 60 Files
catnodpt.sql     catbac.sql       prvtrctv.plb     catactx.sql
prvtuttv.plb     catsvrmg.sql     prvtlsis.plb     prvtlsss.plb
cattrans.sql     catrule.sql      catsnap.sql      catpitr.sql
catdip.sql       catrls.sql       catar.sql        catfga.sql
catamgt.sql      catidxu.sql      cattsm.sql       catchnf.sql
catodm.sql       catkppls.sql     catsscr.sql      catqueue.sql
cathae.sql       catadvtb.sql     catrm.sql        catsch.sql
catol.sql        catdpb.sql       catcrc.sql       dbmscr.sql
dbmsutil.sql     catdbfus.sql     catalrt.sql      catatsk.sql
catmntr.sql      catsqlt.sql      catawrtv.sql     catsmbvw.sql
catwrrtb.sql     catsumat.sql     catrep.sql       catlmnr.sql
catdef.sql       catadrvw.sql     catrepv.sql      catpexe.sql
cattlog.sql      catcapi.sql      catpspi.sql      catts.sql
catnacl.sql      catredact.sql    catproftab.sql   catpstdy.sql
catrupg.sql      catratmask.sql   catqitab.sql     catappcont.sql


[Phase 10] type is 1 with 1 Files
ora_restart.sql

[Phase 11] type is 1 with 1 Files
catpspec.sql

[Phase 12] type is 1 with 1 Files
ora_restart.sql

[Phase 13] type is 2 with 199 Files
utlinad.sql      utlsmtp.sql      utlurl.sql       utlenc.sql
utlgdk.sql       utlcstk.sql      utlcomp.sql      utli18n.sql
utllms.sql       dbmsplsw.sql     utlnla.sql       dbmspdb.sql
dbmstrns.sql     dbmsrwid.sql     dbmspclx.sql     dbmserlg.sql
dbmsspu.sql      dbmsapin.sql     dbmssyer.sql     dbmspipe.sql
dbmsalrt.sql     dbmsdesc.sql     dbmspexp.sql     dbmsjob.sql
..
....

Restart  Phase #:52 Files: 1      Time: 1s
Serial   Phase #:53 Files: 1      Time: 2s
Serial   Phase #:54 Files: 1

*** WARNING: ERRORS FOUND DURING UPGRADE ***

Due to errors found during the upgrade process, the post
upgrade actions in catuppst.sql have not been automatically run.

 *** THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE ***

 1. Evaluate the errors found in the upgrade logs (*.log) and determine the proper action.
 2. Execute the post upgrade script as described in Chapter 3 of the Database Upgrade Guide.

     Time: 220s
Grand Total Time: 5021s

SQL> startup
ORACLE instance started.

Total System Global Area 2037673984 bytes
Fixed Size                  2290120 bytes
Variable Size            1342180920 bytes
Database Buffers          687865856 bytes
Redo Buffers                5337088 bytes
Database mounted.
Database opened.

Step 8.


Run the 'utlu121s.sql'  which display the status of the databse components in the upgraded database and the time required to complete each component upgrade.

SQL> @utlu121s.sql
.
Oracle Database 12.1 Post-Upgrade Status Tool           09-04-2013 20:45:43
.
Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
.
Oracle Server
.                                      UPGRADED      12.1.0.1.0  00:21:16
JServer JAVA Virtual Machine
.                                         VALID      12.1.0.1.0  00:03:04
Oracle Workspace Manager
.                                         VALID      12.1.0.1.0  00:01:31
OLAP Analytic Workspace
.                                         VALID      12.1.0.1.0  00:00:26
Oracle OLAP API
.   SP2-0310: unable to open file "xoqsys.sql"
.   ORA-01917: user or role "OLAPSYS" does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.                                         VALID      12.1.0.1.0  00:00:25
Oracle XDK
.                                         VALID      12.1.0.1.0  00:01:12
Oracle Text
.                                         VALID      12.1.0.1.0  00:00:55
Oracle XML Database
.                                         VALID      12.1.0.1.0  00:06:06
Oracle Database Java Packages
.                                         VALID      12.1.0.1.0  00:00:22
Oracle Multimedia
.                                         VALID      12.1.0.1.0  00:03:36
Spatial
.                                         VALID      12.1.0.1.0  00:07:07
Oracle Application Express
.                                         VALID     4.2.0.00.27  00:33:31
Final Actions
.                                                                00:02:37
Total Upgrade Time: 01:22:36

PL/SQL procedure successfully completed.

Step 9

Run the 'catuppst.sql'.

SQL> @catuppst.sql

Session altered.


Session altered.


Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2013-09-04 20:47:16


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2013-09-04 20:47:16


PL/SQL procedure successfully completed.

catrequtlmg: b_StatEvt     = TRUE
catrequtlmg: b_SelProps    = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig    = FALSE

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2013-09-04 20:47:16

catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

PL/SQL procedure successfully completed.
........
.......
SQL> ALTER SESSION SET current_schema = SYS;

Session altered.

SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
  2    (action_time, action,
  3     namespace, version, id,
  4     bundle_series, comments)
  5  VALUES
  6    (SYSTIMESTAMP, 'APPLY',
  7     SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
  8     '12.1.0.1',
  9     0,
 10     'PSU',
 11     'Patchset 12.1.0.0.0');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DB11G_APPLY_2013Sep04_20_50_07.log

Session altered.
Session altered.


Step 10.

Run the script '/u01/app/oracle/cfgtoollogs/db11g/preupgrade/postupgrade_fixups.sql' which was generated by 'preupgrd.sql'.


SQL> @/u01/app/oracle/cfgtoollogs/db11g/preupgrade/postupgrade_fixups.sql
Post Upgrade Fixup Script Generated on 2013-09-04 00:28:53  Version: 12.1.0.1 Build: 006
Beginning Post-Upgrade Fixups...

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


**********************************************************************
Check Tag:     OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary:   Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Older Timezone in use

     Database is using a time zone file older than version 18.
     After the upgrade, it is recommended that DBMS_DST package
     be used to upgrade the 12.1.0.1.0 database time zone version
     to the latest version which comes with the new release.
     Please refer to My Oracle Support note number 977512.1 for details.
**********************************************************************


PL/SQL procedure successfully completed.

**********************************************************************
                     [Post-Upgrade Recommendations]
**********************************************************************


PL/SQL procedure successfully completed.

                        *****************************************
                        ******** Fixed Object Statistics ********
                        *****************************************

Please create stats on fixed objects two weeks
after the upgrade using the command:
   EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^


PL/SQL procedure successfully completed.


           **************************************************
                ************* Fixup Summary ************

 1 fixup routine generated an INFORMATIONAL message that should be reviewed.


PL/SQL procedure successfully completed.

*************** Post Upgrade Fixup Script Complete ********************

PL/SQL procedure successfully completed.


follow the instruction given in the o/p.

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.

Step 11.

Recompile invalid object.
SQL> @utlrp.sql

TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-09-04 20:59:04

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-09-04 21:16:40

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.

...Database user "SYS", database schema "APEX_040200", user# "117" 21:17:13
...Compiled 0 out of 2998 objects considered, 0 failed compilation 21:17:14
...263 packages
...255 package bodies
...453 tables
...11 functions
...16 procedures
...3 sequences
...458 triggers
...1322 indexes
...207 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 21:17:14
...Completed key object existence check 21:17:15
...Setting DBMS Registry 21:17:15
...Setting DBMS Registry Complete 21:17:15
...Exiting validate 21:17:15

PL/SQL procedure successfully completed.

Step 12.

Check for newly invalid objects.

SQL> @utluiobj.sql
.
Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 09-04-2013 21:21:37
.
This tool lists post-upgrade invalid objects that were not invalid
prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).
.
                           Owner                     Object Name                     Object Type
.

PL/SQL procedure successfully completed.

Step 13.

Run to check the final outcome of the upgrade.

SQL> @utlu121s.sql
.
Oracle Database 12.1 Post-Upgrade Status Tool           09-04-2013 21:21:45
.
Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
.
Oracle Server
.                                         VALID      12.1.0.1.0  00:21:16
JServer JAVA Virtual Machine
.                                         VALID      12.1.0.1.0  00:03:04
Oracle Workspace Manager
.                                         VALID      12.1.0.1.0  00:01:31
OLAP Analytic Workspace
.                                         VALID      12.1.0.1.0  00:00:26
Oracle OLAP API
.   SP2-0310: unable to open file "xoqsys.sql"
.   ORA-01917: user or role "OLAPSYS" does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.                                         VALID      12.1.0.1.0  00:00:25
Oracle XDK
.                                         VALID      12.1.0.1.0  00:01:12
Oracle Text
.                                         VALID      12.1.0.1.0  00:00:55
Oracle XML Database
.                                         VALID      12.1.0.1.0  00:06:06
Oracle Database Java Packages
.                                         VALID      12.1.0.1.0  00:00:22
Oracle Multimedia
.                                         VALID      12.1.0.1.0  00:03:36
Spatial
.                                         VALID      12.1.0.1.0  00:07:07
Oracle Application Express
.                                         VALID     4.2.0.00.27  00:33:31
Final Actions
.                                                                00:02:37
Total Upgrade Time: 01:22:36

PL/SQL procedure successfully completed.


Check the database version.

SQL> SELECT name, open_mode FROM v$database;

NAME      OPEN_MODE
--------- --------------------
DB11G     READ WRITE

SQL> select version from v$instance;

VERSION
-----------------
12.1.0.1.0


Reference.

-->OTN Doc. 1503653.1: Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1)


-->http://www.oracle-base.com/articles/12c/upgrading-to-12c.php#manual-upgrades 

No comments:

Post a Comment