Upgrade database Release 11gR2 to Release 12c


This Article show how to manual upgrade 11.2.0.3 Oracle database ti non-plugged Database 12c

Starting from database 11203

SQL> select instance_name,version,statup_time,status from v$instance;

 

INSTANCE_NAME    VERSION       STARTUP_T    STATUS

————- —————————-  ———-

      DB11          11.2.0.3.0   28-JAN-14   OPEN   

 

Below Minimum database version that can be  directly upgraded to Oracle 12c Release 1 (12.1)

Source Database     Target Database

10.2.0.5     12.1.x

11.1.0.7     12.1.x

11.2.0.2 or higher 12.1.x

 

The following database versions will require an indirect upgrade path:

Source Database     Intermediate Upgrade Path for Target Database Target Database

11.2.0.1            —-> 11.2.0.2 or higher                             —-> 12.1.x

11.1.0.6            —-> 11.1.0.7 or 11.2.0.2 or higher                 —-> 12.1.x

10.2.0.4(or earlier)—-> 10.2.0.5 or later direct upgrade version        —-> 12.1.x

10.1.0.5(or earlier)—-> 10.2.0.5 or later direct upgrade version       —-> 12.1.x

9.2.0.8 (or earlier)—-> 9.2.0.8 –> 11.2.0.2 or higher                 —-> 12.1.x

·         Run Preupgrade script in order to collect required information about database status.

 

SQL> @preupgrd.sql

Loading Pre-Upgrade Package…

WARNING: Failed to open preupgrade.log for write access

    script will generate terminal output only

WARNING: Failed to open preupgrade_fixups.sql for write access

     script will not generate fixup scripts.

Executing Pre-Upgrade Checks…

Oracle Database Pre-Upgrade Information Tool 01-28-2014 15:56:49

Script Version: 12.1.0.1.0 Build: 006

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

   Database Name:  DB11

        Version:  11.2.0.3.0

      Compatible:  11.2.0

       Blocksize:  8192

       Platform:  Linux x86 64-bit

   Timezone file:  V14

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

                      [Renamed Parameters]

“audit_trail” old value was “DB_EXTENDED”;

        –> new name is “audit_trail”, new value is “DB,EXTENDED”

 

       [Changes required in Oracle Database init.ora or spfile]

 

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

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

                 [Obsolete/Deprecated Parameters]

            [No Obsolete or Desupported Parameters in use]

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

                        [Component List]

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

–> Oracle Catalog Views             [upgrade]  VALID

–> Oracle Packages and Types              [upgrade]  VALID

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

                          [Tablespaces]

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

ERROR: –> SYSTEM tablespace is not large enough for the upgrade.

     currently allocated size: 512 MB

     minimum required size: 587 MB

     increase current size by: 75 MB

     tablespace is NOT AUTOEXTEND ENABLED.

–> SYSAUX tablespace is adequate for the upgrade.

     minimum required size: 500 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

 

             [make adjustments in the current environment]

 

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

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

                      [Pre-Upgrade Checks]

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

WARNING: –> Process Count may be too low

 

     Database has a maximum process count of 50 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.

 

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

 

 1 ERROR exist that must be addressed prior to performing your upgrade.

 1 WARNING that Oracle suggests are addressed to improve database performance.

 1 INFORMATIONAL message 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

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

Pre-Upgrade Checks Complete.

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

 

Results of the checks are located at:

 *** Scripts/Logs are not being Generated ***preupgrade.log

 

 

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

                ====>> 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

 

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

 

 

As output script we need to do some preupgrading step before proceed.

 

·         Gather dictionary stats

 

SQL>EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

 

PL/SQL procedure successfully completed.

 

 

·         Increase System Tablespace

 

SQL> alter database datafile ‘/app/oradata/DB11/system/system01.dbf’ resize 1024M;

 

Database altered.

 

·         Increase processes number in init File (in this case no spfile is used)

 

In this case no Access control list was configured into database, so no particular action are required in order to upgrade this part.

 

Database Time zone will be updated at the end of upgrade processes.

 

·         By now we can check that no recovery / backup are running and other check before proceeding to next steps

 

 

SQL> SELECT * FROM v$recover_file;

 

no rows selected

 

SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;

no rows selected

 

SQL> SELECT * FROM dba_2pc_pending;

 

no rows selected

 

SQL> PURGE DBA_RECYCLEBIN;

 

DBA Recyclebin purged.

 

SQL> SELECT username, default_tablespace

     FROM dba_users

     WHERE username in (‘SYS’,’SYSTEM’); 

USERNAME                   DEFAULT_TABLESPACE

—————————— ——————————

SYS                        SYSTEM

SYSTEM                     SYSTEM

 

SQL>  SELECT owner,tablespace_name

     FROM dba_tables

     WHERE table_name=’AUD$’;

 

OWNER                      TABLESPACE_NAME

—————————— ——————————

SYS                        SYSTEM

 

 

SQL> SELECT name FROM v$controlfile;

 

NAME

——————————————————————————–

/app/oracle/admin/DB11/data/ctl/control01.ctl

/app/oracle/admin/DB11/data/ctl/control02.ctl

 

SQL>  SELECT file_name FROM dba_data_files;

 

FILE_NAME

——————————————————————————–

/app/oradata/DB11/system/system01.dbf

/app/oradata/DB11/system/sysaux01.dbf

/app/oradata/DB11/undo/undo01.dbf

 

SQL>  SELECT group#, member FROM v$logfile;

 

    GROUP#   MEMBER

———-  —————————

        1    /redo/redo_a/redo01a.log

        1    /redo/redo_b/redo01b.log

        2    /redo/redo_a/redo02a.log

        2    /redo/redo_b/redo02b.log

 

SQL> select comp_id,version,status from dba_registry

 

COMP_ID      VERSION        STATUS

————————– ———-

CATALOG      11.2.0.3.0       VALID

CATPROC      11.2.0.3.0      VALID

 

·         No Enterprise manager component was installed into database , so no action are required before proceed

Enterprise Manager Database Control is superseded in 12c by Oracle Enterprise Manager Express .

 

     Therefore no repository is needed anymore .

 Remove  Enterprise Manager  Database Control  repository MANUALLY using the following command

     Note : You will get the emremove.sql script in the Oracle 12c home .

 

    Copy the emremove.sql script from the 12c $ORACLE_HOME/rdbms/admin to the source $ORALCE_HOME/rdbms/admin and then execute on the source database prior to upgrade.

 

$emctl stop dbcontrol

SQL> @ ?/rdbms/admin/emremove.sql

  If the EM repository is not removed up front ,it will be automatically removed during the catuppst.sql post-upgrade phase.

 

·         Copy pfile/spfile and password file to the new Oracle Home (12c)

 

·         Shutdown the database.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

 

·         Load new Oracle Variable to point ORACLE_HOME 12c installation

 

[oracle@ciukinobox ~]$ . .bash_profile12

·         Modify /etc/oratab entry

 

 [oracle@ciukinobox ~]$ more /etc/oratab

 

db11:/app/oracle/product/12.1.0:N

 

·         Upgrade database

 

[oracle@ciukinobox]$ cd $ORACLE_HOME/rdbms/admin/

 [oracle@ciukinobox admin]$ sqlplus

 

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jan 28 17:14:51 2014

 

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

 

Enter user-name: /as sysdba

 

Connected to an idle instance.

 

SQL> startup upgrade

ORACLE instance started.

 

Total System Global Area  534462464 bytes

Fixed Size              2290416 bytes

Variable Size         423628048 bytes

Database Buffers      104857600 bytes

Redo Buffers            3686400 bytes

Database mounted.

Database opened.

 

·         The new script catctl replace catupgrd script used until 11gR2, in parallel mode (6 is the number of parallel processes used during upgrade phase)

 

[oracle@ciukinobox admin]$  $ORACLE_HOME/perl/bin/perl catctl.pl -n  6 -l $ORACLE_HOME/diagnostics catupgrd.sql

 

Analyzing file catupgrd.sql

Log files in /app/oracle/product/12.1.0/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

 

…..

……………..

…………………………….

[Phase 56] type is 1 with 1 Files

catshutdown.sql 

 

Using 6 processes.

Serial   Phase #: 0 Files: 1  Time: 91s

Serial   Phase #: 1 Files: 3  Time: 56s

Restart  Phase #: 2 Files: 1      Time: 0s

Parallel Phase #: 3 Files: 18     Time: 18s

Restart  Phase #: 4 Files: 1      Time: 0s

Serial   Phase #: 5 Files: 5 Time: 24s

……….

………………………..

Grand Total Time: 1349s

 

·         Startup upgraded database and run post upgrade step

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  534462464 bytes

Fixed Size              2290416 bytes

Variable Size         423628048 bytes

Database Buffers      104857600 bytes

Redo Buffers            3686400 bytes

Database mounted.

Database opened.

 

 

SQL> @utlu121s.sql

.

Oracle Database 12.1 Post-Upgrade Status Tool         01-28-2014 17:46:46

.

Component                         Current      Version  Elapsed Time

Name                              Status       Number HH:MM:SS

.

Oracle Server

.                                 UPGRADED      12.1.0.1.0  00:14:05

Oracle XML Database

.                                   VALID      12.1.0.1.0  00:02:36

Final Actions

.                                                     00:01:26

Total Upgrade Time: 00:18:10

 

PL/SQL procedure successfully completed.

 

SQL>  @catuppst.sql

 

Session altered.

 

Session altered.

 

Session altered.

TIMESTAMP

——————————————————————————–

COMP_TIMESTAMP POSTUP_BGN 2014-01-28 17:47:27

 

TIMESTAMP

——————————————————————————–

COMP_TIMESTAMP CATREQ_BGN 2014-01-28 17:47:27

 

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.

……….

……………….

……………………………

 

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:

/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DB11_APPLY_2014Jan28_17_47_29.log

 

Session altered.

 

 

Session altered.

 

·         Recompile full database

 

SQL> @?/rdbms/admin/utlrp

 

TIMESTAMP

——————————————————————————–

COMP_TIMESTAMP UTLRP_BGN  2014-01-28 17:50:01

 

PL/SQL procedure successfully completed.

 

TIMESTAMP

——————————————————————————–

COMP_TIMESTAMP UTLRP_END  2014-01-28 17:50:03

 

OBJECTS WITH ERRORS

——————-

               0

ERRORS DURING RECOMPILATION

—————————

                      0

SQL> select owner,object_name,object_type,status from dba_objects where status<>’VALID’;

 

no rows selected

·         Check post upgrade components with dbupgdiag script [Below an extract of logfile]

SQL> alter session set nls_language=’American’;

 

Session altered.

 

SQL> @dbupgdiag.sql

 

Enter location for Spooled output:

 

Enter value for 1: /home/oracle

 

28_Jan_2014_1753      .log

 

DB11_

 

                      *** Start of LogFile ***

 

  Oracle Database Upgrade Diagnostic Utility      01-28-2014 17:53:11

 

===============

Hostname

===============

 

ciukinobox

 

===============

Database Name

===============

 

DB11

===============

Database Uptime

===============

 

17:45 28-JAN-14

 

=================

Database Wordsize

=================

This is a 64-bit database

 

================

Software Version

================

 

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production             0

PL/SQL Release 12.1.0.1.0 – Production                                             0

CORE   12.1.0.1.0   Production                                                    0

TNS for Linux: Version 12.1.0.1.0 – Production                                     0

NLSRTL Version 12.1.0.1.0 – Production                                             0

 

=============

Compatibility

=============

 

Compatibility is set as 11.2.0

 

================

Archive Log Mode

================

 

Database log mode          No Archive Mode

Automatic archival         Disabled

Archive destination       /app/oracle/product/12.1.0/dbs/arch

Oldest online log sequence     23

Current log sequence              24

 

DOC>################################################################

DOC>

DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before

DOC> upgrading the database

DOC>

DOC>################################################################

DOC>#

 

===========================================

Tablespace and the owner of the aud$ table

===========================================

 

OWNER       TABLESPACE_NAME

———— ——————————

SYS         SYSTEM

 

============================================================================

count of records in the sys.aud$ table where dbid is null- Standard Auditing

============================================================================

 

        0

================

Time Zone version

================

 

       14

 

================

Local Listener

================

 

================

Component Status

================

 

Comp ID Component                    Status    Version           Org_Version    Prv_Version

——- ———————————- ——— ————– ————– ————–

CATALOG Oracle Database Catalog Views      VALID     12.1.0.1.0     11.2.0.3.0     11.2.0.3.0

CATPROC Oracle Database Packages and Types VALID     12.1.0.1.0     11.2.0.3.0      11.2.0.3.0

XDB    Oracle XML Database           VALID     12.1.0.1.0

 

======================================================

List of Invalid Database Objects Owned by SYS / SYSTEM

======================================================

 

Number of Invalid Objects

——————————————————————

There are no Invalid Objects

 

DOC>################################################################

DOC>

DOC> If there are no Invalid objects below will result in zero rows.

DOC>

DOC>################################################################

DOC>#

 

 

no rows selected

 

================================

List of Invalid Database Objects

================================

 

Number of Invalid Objects

——————————————————————

There are no Invalid Objects

 

DOC>################################################################

DOC>

DOC> If there are no Invalid objects below will result in zero rows.

DOC>

DOC>################################################################

DOC>#

 

no rows selected

======================================================

Count of Invalids by Schema

======================================================

 

==============================================================

Identifying whether a database was created as 32-bit or 64-bit

==============================================================

 

DOC>###########################################################################

DOC>

DOC> Result referencing the string ‘B023’ ==> Database was created as 32-bit

DOC> Result referencing the string ‘B047’ ==> Database was created as 64-bit

DOC> When String results in ‘B023’ and when upgrading database to 10.2.0.3.0

DOC> (64-bit) , For known issue refer below articles

DOC>

DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While

DOC>            Upgrading Or Patching Databases To 10.2.0.3

DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and

DOC>           OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6

DOC>

DOC>###########################################################################

DOC>#

 

Metadata Initial DB Creation Info

——– ———————————–

B047   Database was created as 64-bit

 

===================================================

Number of Duplicate Objects Owned by SYS and SYSTEM

===================================================

 

Counting duplicate objects ….

 

 

  COUNT(1)

———-

        4

 

=========================================

Duplicate Objects Owned by SYS and SYSTEM

=========================================

 

Querying duplicate objects ….

 

Upload db_upg_diag_DB11_28_Jan_2014_0750.log from “/home/oracle” directory

 

·         Upgrade database Timezone

 

SQL> SELECT version FROM v$timezone_file;

 

   VERSION

———-

       14

SQL> @upg_tzv_check.sql

INFO: Starting with RDBMS DST update preparation.

INFO: NO actual RDBMS DST update will be done by this script.

INFO: If an ERROR occurs the script will EXIT sqlplus.

INFO: Database version is 12.1.0.1 .

INFO: Database RDBMS DST version is DSTv14 .

INFO: Doing checks for known issues …

INFO: No known issues detected.

INFO: Now detecting new RDBMS DST version.

A prepare window has been successfully started.

INFO: Newest RDBMS DST version detected is DSTv18 .

INFO: Next step is checking all TSTZ data.

INFO: It might take a while before any further output is seen …

A prepare window has been successfully ended.

INFO: A newer RDBMS DST version than the one currently used is found.

INFO: Note that NO DST update was yet done.

INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.

INFO: Note that the upg_tzv_apply.sql script will

INFO: restart the database 2 times WITHOUT any confirmation or prompt.

SQL> @upg_tzv_apply.sql

INFO: If an ERROR occurs the script will EXIT sqlplus.

INFO: The database RDBMS DST version will be updated to DSTv18 .

WARNING: This script will restart the database 2 times

WARNING: WITHOUT asking ANY confirmation.

WARNING: Hit control-c NOW if this is not intended.

INFO: Restarting the database in UPGRADE mode to start the DST upgrade.

Database closed.

Database dismounted.

ORACLE instance shut down.

 

ORACLE instance started.

 

Total System Global Area  534462464 bytes

Fixed Size              2290416 bytes

Variable Size         423628048 bytes

Database Buffers      104857600 bytes

Redo Buffers            3686400 bytes

Database mounted.

Database opened.

INFO: Starting the RDBMS DST upgrade.

INFO: Upgrading all SYS owned TSTZ data.

INFO: It might take time before any further output is seen …

An upgrade window has been successfully started.

INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.

Database closed.

Database dismounted.

ORACLE instance shut down.

RACLE instance started.

 

Total System Global Area  534462464 bytes

Fixed Size              2290416 bytes

Variable Size         423628048 bytes

Database Buffers      104857600 bytes

Redo Buffers            3686400 bytes

Database mounted.

Database opened.

INFO: Upgrading all non-SYS TSTZ data.

INFO: It might take time before any further output is seen …

INFO: Do NOT start any application yet that uses TSTZ data!

INFO: Next is a list of all upgraded tables:

Table list: “GSMADMIN_INTERNAL”.”AQ$_CHANGE_LOG_QUEUE_TABLE_S”

Number of failures: 0

Table list: “GSMADMIN_INTERNAL”.”AQ$_CHANGE_LOG_QUEUE_TABLE_L”

Number of failures: 0

INFO: Total failures during update of TSTZ data: 0 .

An upgrade window has been successfully ended.

INFO: Your new Server RDBMS DST version is DSTv18 .

INFO: The RDBMS DST update is successfully finished.

INFO: Make sure to exit this sqlplus session.

INFO: Do not use it for timezone related selects.

 

 

SQL> SELECT version FROM v$timezone_file;

 

   VERSION

———-

       18

·         At this point we have database upgraded to the new Release

 

SQL> select instance_name,version,status  from v$instance;

 

INSTANCE_NAME      VERSION       STATUS

————— ————– —————–

         DB11       12.1.0.1.0    OPEN

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *