Tuesday, March 24, 2015

Manual upgrading Oracle database 11g to 12c

If you want to know how we upgrade an 11g database to 12c using DBUA, click here
For upgrading 12.1.0.1 to 12.1.0.2 using DBUA, click here.

This article explains how to upgrade 11.2.0.2 and above, and 12.1.0.1 to 12.1.0.2 manually.
For details of each step, take a look at official 12c
upgrade guide which I also followed to write this article

Manually upgrading to 12c
There are many DBAs who don’t want to follow the recommended way of upgrade using DBUA (Database upgrade Assistant). Following is the method of manually upgrading 11g database to 12c. I will be using here 11.2.0.3 to upgrade to 12.1.0.2, on Windows platform. Windows platform needs a couple of extra steps to be executed, like creation of OS level service using oradim.exe from 12c home before the upgrade, rest of the steps are almost same. Direct upgrade to 12c is possible only starting from 11.2.0.2 version. For other lower versions, you may wish to choose a different method of upgrade discussed in the following white paper.

1)
Copy preupgrd.sql and utluppkg.sql scripts from ORACLE_HOME\rdbms\admin to any temporarily created directory. ORACLE_HOME mentioned here is 12c home.
C:\>copy C:\app\salmqure\product\12.1.0\dbhome_1\rdbms\admin\preupgrd.sql c:\temp
        1 file(s) copied.

C:\>copy C:\app\salmqure\product\12.1.0\dbhome_1\rdbms\admin\utluppkg.sql c:\temp
        1 file(s) copied.

2)
Log in as SYSDBA and execute the preupgrd.sql script.
SQL> @c:\temp\preupgrd.sql

The syntax of the command is incorrect.


The syntax of the command is incorrect.

Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in TESTDB...
***************************************************************************


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

                 ====>> ERRORS FOUND for TESTDB <<====

 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

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

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

             ====>> PRE-UPGRADE RESULTS for TESTDB <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:
 C:\app\salmqure\cfgtoollogs\testdb\preupgrade\preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:
 C:\app\salmqure\cfgtoollogs\testdb\preupgrade\preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:
 C:\app\salmqure\cfgtoollogs\testdb\preupgrade\postupgrade_fixups.sql

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

***************************************************************************
Pre-Upgrade Checks in TESTDB Completed.
***************************************************************************

***************************************************************************
***************************************************************************
SQL>

If there is any error displayed on the screen, you would need to correct it before proceeding. Check preupgrade.log file also.
Error "The syntax of the command is incorrect" as highlighted above can safely be ignored, as this is because of a bug in the preupgrd.sql script and does not harm anything.

3)
Above step also creates preupgrade_fixups.sql and postupgrade_fixups.sql script. Execute preupgrade_fixups.sql
SQL> @C:\app\salmqure\cfgtoollogs\testdb\preupgrade\preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2015-03-20 11:27:20  Version: 12.1.0.2 Build: 006
Beginning Pre-Upgrade Fixups...
Executing in container TESTDB

**********************************************************************
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:     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 Catalog component is desupported.
     If you are not using the OLAP Catalog component and want
     to remove it, then execute the
     ORACLE_HOME/olap/admin/catnoamd.sql script before or
     after the upgrade.
**********************************************************************


**********************************************************************
Check Tag:     APEX_UPGRADE_MSG
Check Summary: Check that APEX will need to be upgraded.
Fix Summary:   Oracle Application Express can be manually upgraded prior to database upgrade.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Oracle Application Express (APEX) can be
     manually upgraded prior to database upgrade

     APEX is currently at version 3.2.1.00.12 and will need to be
     upgraded to APEX version 4.2.5 in the new release.
     Note 1: To reduce database upgrade time, APEX can be manually
             upgraded outside of and prior to database upgrade.
     Note 2: See MOS Note 1088970.1 for information on APEX
             installation upgrades.
**********************************************************************


**********************************************************************
                      [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 ************

 3 fixup routines generated INFORMATIONAL messages that should be reviewed.

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

As we can see that it has given 4 recommendations (may be more or less for your case), OEM de-configuraiton, removal of OLAP and APEX upgrade prior to the database upgrade and gather dictionary stats. I can ignore first 1st and 3rd recommendation as these would be automatically done during upgrade of database (you may wish to execute them now to reduce the total upgrade process time).

4)
I would remove OLAP because I am not using OLAP and also gather dictionary statistics though, as recommended above.

SQL>@C:\app\salmqure\product\11.2.0\dbhome_1\olap\admin\catnoamd.sql
SQL> EXECUTE dbms_stats.gather_dictionary_stats;

5)
Shutdown the database and take full database backup. Alternatively you can turn on the flashback database and create a Guaranteed Restore Point which can be used to rollback if upgrade process fails.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

6)
Only for Windows platform,
stop the OS level service, delete the service and then recreate the service by executing oradim.exe from Oracle 12c home.
Also copy SPFILE/ PFILE from 11G ORACLE_HOME\database directory to 12G ORACLE_HOME\database directory.
Stop Service
C:\>net stop oracleserviceTESTDB
The OracleServiceTESTDB service is stopping.
The OracleServiceTESTDB service was stopped successfully.

Delete Service using correct oradim
C:\>where oradim
C:\app\salmqure\product\11.2.0\dbhome_1\BIN\oradim.exe
C:\app\salmqure\product\12.1.0\dbhome_1\BIN\oradim.exe

C:\>C:\app\salmqure\product\11.2.0\dbhome_1\BIN\oradim.exe -delete -sid TESTDB
Instance deleted.

C:\>C:\app\salmqure\product\12.1.0\dbhome_1\BIN\oradim.exe -new -sid TESTDB -syspwd syspassword -startmode auto
Instance created.



7)
Only for Unix based platforms
, copy password file and SPFILE/PFILE from 11g $ORACLE_HOME/dbs directory to 12c $ORACLE_HOME/dbs directory.

8)
Log into the system (OS) using oracle 12c software owner and set environment variables. Modify the following paths according to your 12c oracle home
For Windows
C:\>set ORACLE_HOME=C:\app\salmqure\product\12.1.0\dbhome_1
C:\>set PATH=%ORACLE_HOME%\bin;%PATH%
C:\>set ORACLE_SID=TESTDB

For Unix based
$export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
$export PATH=$ORACLE_HOME\bin:$PATH
$export ORACLE_SID=TESTDB

9)
Log into the database using sqlplus, as SYSDBA, and the startup the database with upgrade option. Exit the SQLPLUS
C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 19 15:56:41 2015

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

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  3047720 bytes
Variable Size             838864600 bytes
Database Buffers         1291845632 bytes
Redo Buffers               13725696 bytes
Database mounted.
Database opened.

If this is 12.1.0.1 CDB (container database) with one or more pluggable databases, open all pluggable database in upgrade mode
SQL> alter pluggable database all open upgrade;

SQL>EXIT
C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>

Starting 12c, we have a new parallel upgrade utility called catctl.pl which is a perl script used to upgrade the database using parallel processes, thus reducing the upgrade time. Following link explains more about parallel upgrade utility for 12c.

10)
Change the directory to %ORACLE_HOME%\rdbms\admin and start upgrade using catctl.pl utility. Use “-n” parameter for catctl.pl to specify the degree of parallelism. If you don’t specify, the default value for non-CDB is 4 and maximum value that could be used it 8. –l parameter is used for the location of output log files.
Before we start the upgrade, it is very much important to have sufficient memory allocated to the SGA, otherwise upgrade may fail with memory allocation errors. For this case, I used 2G value for SGA_TARGET parameter.

C:\>cd %ORACLE_HOME%\rdbms\admin
C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>%ORACLE_HOME%\perl\bin\perl catctl.pl -n 2  -l c:\temp catupgrd.sql

Running catupgrd.sql in conventional way
Alternatively, we can use traditional way of running catupgrd.sql script from SQLPLUS, but
it should be run with PARALLEL=NO option as follows
SQL> spool c:\temp\upgrade.log
SQL> @catupgrd.sql PARALLEL=NO

In case of a container 12.1.0.1 source database with one or more pluggable databases, it should be run for CDB$ROOT, PDB$SEED and also each of the pluggable database.
SQL>alter session set container = "CDB$ROOT"
SQL> spool c:\temp\upgrade.log
SQL> @catupgrd.sql PARALLEL=NO

SQL>alter session set container = "PDB$SEED"
SQL> @catupgrd.sql PARALLEL=NO



11)
If used catctl.pl, the output of this upgrade process would be similar to the following. For this example, log files would be in c:\temp folder as specified in upgrade command. For conventional running of catupgrd.sql from SQLPLUS, please see the spool file for output or any errors
C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>%ORACLE_HOME%\perl\bin\perl catctl.pl -n 2  -l c:\temp catupgrd.sql

Argument list for [catctl.pl]
SQL Process Count     n = 2
SQL PDB Process Count N = 0
Input Directory       d = 0
Phase Logging Table   t = 0
Log Dir               l = c:\temp
Script                s = 0
Serial Run            S = 0
Upgrade Mode active   M = 0
Start Phase           p = 0
End Phase             P = 0
Log Id                i = 0
Run in                c = 0
Do not run in         C = 0
Echo OFF              e = 1
No Post Upgrade       x = 0
Reverse Order         r = 0
Open Mode Normal      o = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0
Display Phases        y = 0
Child Process         I = 0

catctl.pl version: 12.1.0.2.0
Oracle Home           = C:\app\salmqure\product\12.1.0\dbhome_1

Analyzing file catupgrd.sql
Log files in c:\temp
catcon: ALL catcon-related output will be written to c:\temp/catupgrd_catcon_9264.lst
catcon: See c:\temp/catupgrd*.log files for output generated by scripts
catcon: See c:\temp/catupgrd_*.lst files for spool files, if any
Number of Cpus        = 4
SQL Process Count     = 2

------------------------------------------------------
Phases [0-73]
Serial   Phase #: 0 Files: 1     Time: 116s
Serial   Phase #: 1 Files: 5     Time: 36s
Restart  Phase #: 2 Files: 1     Time: 0s
Parallel Phase #: 3 Files: 18    Time: 21s
Restart  Phase #: 4 Files: 1     Time: 0s
Serial   Phase #: 5 Files: 5     Time: 18s
Serial   Phase #: 6 Files: 1     Time: 13s
Serial   Phase #: 7 Files: 4     Time: 10s
Restart  Phase #: 8 Files: 1     Time: 1s
Parallel Phase #: 9 Files: 62    Time: 69s
Restart  Phase #:10 Files: 1     Time: 0s
Serial   Phase #:11 Files: 1     Time: 15s
Restart  Phase #:12 Files: 1     Time: 0s
Parallel Phase #:13 Files: 91    Time: 54s
Restart  Phase #:14 Files: 1     Time: 0s
Parallel Phase #:15 Files: 111   Time: 71s
Restart  Phase #:16 Files: 1     Time: 0s
Serial   Phase #:17 Files: 3     Time: 3s
Restart  Phase #:18 Files: 1     Time: 0s
Parallel Phase #:19 Files: 32    Time: 41s
Restart  Phase #:20 Files: 1     Time: 0s
Serial   Phase #:21 Files: 3     Time: 8s
Restart  Phase #:22 Files: 1     Time: 0s
Parallel Phase #:23 Files: 23    Time: 88s
Restart  Phase #:24 Files: 1     Time: 0s
Parallel Phase #:25 Files: 11    Time: 51s
Restart  Phase #:26 Files: 1     Time: 1s
Serial   Phase #:27 Files: 1     Time: 2s
Restart  Phase #:28 Files: 1     Time: 0s
Serial   Phase #:30 Files: 1     Time: 0s
Serial   Phase #:31 Files: 257   Time: 25s
Serial   Phase #:32 Files: 1     Time: 0s
Restart  Phase #:33 Files: 1     Time: 0s
Serial   Phase #:34 Files: 1     Time: 6s
Restart  Phase #:35 Files: 1     Time: 0s
Restart  Phase #:36 Files: 1     Time: 0s
Serial   Phase #:37 Files: 4     Time: 50s
Restart  Phase #:38 Files: 1     Time: 1s
Parallel Phase #:39 Files: 13    Time: 55s
Restart  Phase #:40 Files: 1     Time: 1s
Parallel Phase #:41 Files: 10    Time: 11s
Restart  Phase #:42 Files: 1     Time: 1s
Serial   Phase #:43 Files: 1     Time: 6s
Restart  Phase #:44 Files: 1     Time: 0s
Serial   Phase #:45 Files: 1     Time: 7s
Serial   Phase #:46 Files: 1     Time: 2s
Restart  Phase #:47 Files: 1     Time: 0s
Serial   Phase #:48 Files: 1     Time: 332s
Restart  Phase #:49 Files: 1     Time: 0s
Serial   Phase #:50 Files: 1     Time: 47s
Restart  Phase #:51 Files: 1     Time: 1s
Serial   Phase #:52 Files: 1     Time: 21s
Restart  Phase #:53 Files: 1     Time: 1s
Serial   Phase #:54 Files: 1     Time: 394s
Restart  Phase #:55 Files: 1     Time: 1s
Serial   Phase #:56 Files: 1     Time: 66s
Restart  Phase #:57 Files: 1     Time: 0s
Serial   Phase #:58 Files: 1     Time: 129s
Restart  Phase #:59 Files: 1     Time: 0s
Serial   Phase #:60 Files: 1     Time: 675s
Restart  Phase #:61 Files: 1     Time: 0s
Serial   Phase #:62 Files: 1     Time: 1595s
Restart  Phase #:63 Files: 1     Time: 0s
Serial   Phase #:64 Files: 1     Time: 3s
Serial   Phase #:65 Files: 1 Calling sqlpatch with C:\app\salmqure\product\12.1.0\dbhome_1\perl\bin\perl.exe -I C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN -I C:\app\salmqure\product\12.1.0\dbhome_1\sqlpatch C:\app\salmqure\product\12.1.0\dbhome_1\sqlpatch\sqlpatch.pl -verbose -upgrade_mode_only > c:\temp/catupgrd_datapatch_upgrade.log 2> c:\temp/catupgrd_datapatch_upgrade.err
returned from sqlpatch
A subdirectory or file C:\app\salmqure\product\12.1.0\dbhome_1\cfgtoollogs\TESTDB\upgrade already exists.
    Time: 67s
Serial   Phase #:66 Files: 1     Time: 50s
Serial   Phase #:68 Files: 1     Time: 0s
Serial   Phase #:69 Files: 1 Calling sqlpatch with C:\app\salmqure\product\12.1.0\dbhome_1\perl\bin\perl.exe -I C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN -I C:\app\salmqure\product\12.1.0\dbhome_1\sqlpatch C:\app\salmqure\product\12.1.0\dbhome_1\sqlpatch\sqlpatch.pl -verbose > c:\temp/catupgrd_datapatch_normal.log 2> c:\temp/catupgrd_datapatch_normal.err
returned from sqlpatch
    Time: 84s
Serial   Phase #:70 Files: 1     Time: 241s
Serial   Phase #:71 Files: 1     Time: 1s
Serial   Phase #:72 Files: 1     Time: 0s
Serial   Phase #:73 Files: 1     Time: 56s

Grand Total Time: 4559s

LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:
C:\app\salmqure\product\12.1.0\dbhome_1\cfgtoollogs\TESTDB\upgrade\upg_summary.log

Grand Total Upgrade Time:    [0d:1h:15m:59s]

12)
Database would be shutdown after the upgrade, startup the database and execute postupgrade_fixups.sql script.
C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 25 11:33:34 2015

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

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  3047720 bytes
Variable Size            1023413976 bytes
Database Buffers         1107296256 bytes
Redo Buffers               13725696 bytes
Database mounted.
Database opened.

SQL> @C:\app\salmqure\cfgtoollogs\TESTDB\preupgrade\postupgrade_fixups.sql

Post Upgrade Fixup Script Generated on 2015-03-25 12:01:40  Version: 12.1.0.2 Build: 006
Beginning Post-Upgrade Fixups...

**********************************************************************
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.2.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.
**********************************************************************


**********************************************************************
Check Tag:     NOT_UPG_BY_STD_UPGRD
Check Summary: Identify existing components that will NOT be upgraded
Fix Summary:   This fixup does not perform any action.
**********************************************************************
Fixup Returned Information:
This fixup does not perform any action.
If you want to upgrade those other components, you must do so manually.
**********************************************************************


**********************************************************************
                     [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 ^^^


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

 2 fixup routines generated INFORMATIONAL messages that should be reviewed.

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

PL/SQL procedure successfully completed.
Recommendations displayed by the postupgrade_fixups.sql should be executed here. For this example, upgrade the time zone file and gather fixed objects stats.

13)
Execute utlu121s.sql

SQL>  @utlu121s.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

CATCTL REPORT = C:\app\salmqure\product\12.1.0\dbhome_1\cfgtoollogs\testdb\upgrade\upg_summary.log

PL/SQL procedure successfully completed.


Oracle Database 12.1 Post-Upgrade Status Tool           03-25-2015 11:36:42

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.1.0.2.0  00:13:01
JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:05:29
Oracle Workspace Manager                  VALID      12.1.0.2.0  00:00:50
OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:19
Oracle OLAP API                           VALID      12.1.0.2.0  00:00:16
Oracle XDK                                VALID      12.1.0.2.0  00:00:44
Oracle Text                               VALID      12.1.0.2.0  00:00:50
Oracle XML Database                       VALID      12.1.0.2.0  00:05:42
Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:12
Oracle Multimedia                         VALID      12.1.0.2.0  00:02:07
Spatial                                UPGRADED      12.1.0.2.0  00:11:12
Oracle Application Express                VALID     4.2.5.00.08  00:25:48
Final Actions                                                    00:01:41
Post Upgrade                                                     00:03:44

Total Upgrade Time: 01:12:52

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Update Summary Table with con_name and endtime.
SQL> --
SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,
  2                                  con_name = SYS_CONTEXT('USERENV','CON_NAME'),
  3                                  endtime  = SYSDATE
  4         WHERE con_id = -1;

1 row updated.

SQL> commit;

Commit complete.

Everything is fine and all components have been upgraded. If there is any error message faced during upgrade or message displayed in the output of utlu121s.sql, we would need to run catuppst.sql script manually. If there is no error during upgrade, this catuppst.sql is executed as part of the upgrade process. C:\temp\catupgrd0.log file should show any error message generated during the upgrade and check whether catuppst.sql was run or not. As mentioned in Oracle upgrade guide, if we find text “Rem BEGIN catuppst.sql” in the log file, it means this script was executed as part of upgrade.
If we have not deleted OLAP before the upgrade, we will see following information for OLAP in the output of postupgrade_fixups.sql
OLAP Catalog                         OPTION OFF      11.2.0.3.0  00:00:00

If you are upgrading 11g to 12.1.0.1, there could be error messages during upgrade if OLAP was not deleted before the upgrade, and because of these errors, catuppst.sql would not be run and would require to be run manually. There could be other errors as well which would require catuppst.sql to be executed manually. If catupgrd.sql was executed from SQLPLUS, then catuppst.sql was also not run - and in this case, you would also required to run catuppst.sql, manually as follows.
C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>%ORACLE_HOME%\ perl/bin/perl catcon.pl -n 1 -b catuppst  catuppst.sql


14)
Lastly, execute utlrp.sql script to compile if there are any invalid objects. Again we can do this in parallel (which also compiles packages in all containers for a CDB) or using a conventional way
Parallel, using catcoon.pl
C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>%oracle_home%\perl/bin/perl catcon.pl -n 2 -b utlrp  utlrp.sql

Conventional way
SQL> @utlrp.sql

Set COMPATIBLE parameter value to 12.

10 comments:

  1. Excellent Post, it helped me run manual steps , Thanks !

    ReplyDelete
  2. Hi,

    I have a question. Have u dim the oracle db to new 12c db ? Did I miss some steps as I don't have files at new Oracle home/bin. Where can I find the catctl.sql ?

    Lance

    ReplyDelete
    Replies
    1. Hi,
      There is not catctl.sql, but catctl.pl, and it should be under %ORACLE_HOME%\perl\bin\perl directory; this is already mentioned in this article.

      Delete
  3. Very well explained- I am thinking of using it for our upcoming upgrade.

    ReplyDelete
  4. One question:

    Here:

    "
    Log into the database using sqlplus, as SYSDBA, and the startup the database with upgrade option. Exit the SQLPLUS
    C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>sqlplus

    SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 19 15:56:41 2015

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

    Enter user-name: sys as sysdba
    Enter password:
    Connected to an idle instance.

    SQL> startup upgrade
    ORACLE instance started.

    Total System Global Area 2147483648 bytes
    Fixed Size 3047720 bytes
    Variable Size 838864600 bytes
    Database Buffers 1291845632 bytes
    Redo Buffers 13725696 bytes
    Database mounted.
    Database opened.

    "


    What puzzles me is we have to run startup upgrade after logging in to Oracle 12C Database, but should not we be doing this from 11g.
    I might be missing something here, but perhaps you can enlighten me here.

    ReplyDelete
    Replies
    1. Hi,
      This is very late reply and I am sorry for that. We need to "startup upgrade" the database from the home to which we are upgrading our database. 12c home in this case.

      Delete
  5. what is inplace upgrade. do you have any steps?

    ReplyDelete
    Replies
    1. In an in-place upgrade, same oracle home is used to install patchset binarirs. Alternatively, new software (same version as existing) is installed in a new home and then patched, and then database is upgraded to new patchset from the new home.

      When it comes to version upgrade, there is no in-place upgrade. New version must be installed in a new home.

      Delete

Popular Posts - All Times