Upgrading Oracle from 11.2.0.4.0 to 12.1.0.2.0 on Windows server

Database upgrade from 11.2.0.4.0  to  12.1.0.2.0 on Windows server


Summary


Serve Namer=

Database Name=

1)  Download preupgrade_12.1.0.2.0_15_crlf-The Preupgrade Assistant tool is a diagnostics utility which assesses the system for possible in-place upgrade limitations and provides a report with the

analysis results.

2) Download  12 database software(12.1.0.2.0 ) winx64_12102_database_1of2 and winx64_12102_database_2of2

3) Extract first zip file( winx64_12102_database_1of2) and extract second zip file in same folder ,give same folder name which extracted earlier and it prompt for merger ,Please continue for merger)

4) Check invalid objs for sys and system after running dictionary stats

5) Check tablespace free space as below and added space in system and sysaux tablespace if require

6) Check_stale_stats on production database and registry of database

7) Take backup of pfile,controlfile, inventory and listener ora ,tnsname.ora  file.

8) Run the preupgrade tool and act as per recommendation

9)Take full backup

10) Bring listener down

11) Disable archivelog mode

12) Disable Backup jobs (   Taskschd.msc )

13 )Purge recyclebin

14) Installation of 12c software in new Home

15) Shutdown db and delete all oracle services from old oracle home (oracle database services and listener) and create new database service and start the listener from new Oracle Home

15) upgrade the database after setting new home

16) run post script and do verification


Details


Pre Check


Login on database as below


Microsoft Windows [Version 6.1.7601]

Copyright (c) 2009 Microsoft Corporation.  All rights reserved.


C:\Users\user1>set ORACLE_SID=<Database Name>


C:\Users\user1>set ORACLE_HOME=e:\app\oracle\product\11.2.0.4\db1


C:\Users\user1>sqlplus


SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 11 08:24:09 2017


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


Enter user-name: / as sysdba


Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options



Check tablespace free space as below and added space in system and sysaux table if require


set pages 999 lines 100

col    "Tablespace"  for a50

col    "Size MB"     for 999999999

col    "%Used"       for 999

col    "Add (80%)"   for 999999

select tsu.tablespace_name "Tablespace"

,      ceil(tsu.used_mb) "Size MB"

,      100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"

,      ceil((tsu.used_mb - tsf.free_mb) / .75) - tsu.used_mb "Add (75%)"

from   (select tablespace_name, sum(bytes)/1024/1024 used_mb

       from    dba_data_files group by tablespace_name) tsu

,             (select ts.tablespace_name

       ,       nvl(sum(bytes)/1024/1024, 0) free_mb

       from    dba_tablespaces ts, dba_free_space fs

       where   ts.tablespace_name = fs.tablespace_name (+)

       group by ts.tablespace_name) tsf

where  tsu.tablespace_name = tsf.tablespace_name (+)

and    100 - floor(tsf.free_mb/tsu.used_mb*100) >= 75

order  by 3,4

/


Tablespace                                            Size MB %Used  Add (75%)

-------------------------------------------------- ---------- ----- ----------

SPOTFIRE_DATA                                            1100    90        206

SYSAUX                                                  12230    96       3299


SQL>



TABLESPACE_NAME                TOTAL_IN_MB USED_IN_MB FREE_IN_MB PCT_USED

------------------------------ ----------- ---------- ---------- ---------

SYSAUX                            12230.00   11645.63     583.38     95.22

UNDOTBS1                            250.00       9.81     239.19      3.93


USERS                                 5.00       0.31       3.69      6.25

SYSTEM                             2600.00     742.06    1856.94     28.54


if require ,Add space in tablespace


 set num 5

 COL file_name FORMAT A60

select file_name,bytes/1024/1024 mb    ,AUTOEXTENSIBLE  from dba_data_files where tablespace_name='&TSPAC' ;


SQL> alter datafile 'E:\APP\ORACLE\ORADATA\SYSAUX01.DBF' resize  14000m

;

SQL> alter database datafile 'E:\APP\ORACLE\ORADATA\SYSAUX01.DBF' resiz

e  14000m;


Database altered.


SQL> alter database datafile 'E:\APP\ORACLE\ORADATA\DBCENTQL\SPOTFIRE_DATA.DBF'

resize 3000m;

Database altered.


SQL> alter database datafile 'E:\APP\ORACLE\ORADATA\SYSTEM01.DBF' resiz

e 4000m;

Database altered.


SQL>


To check invalid,Statistics and registry status


select owner, object_name, object_type,status from dba_objects where owner in ('SYS','SYSTEM') and status like 'INVALID';


col TABLE_NAME for a30

col PARTITION_NAME for a20

col SUBPARTITION_NAME for a20

select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';


set lines 200 pages 200

col action_time format a40;

col action format a30;

col namespace format a10;

col comments format a30;

col version format a13;

set lin 200 head on feed on pagesize 100;

select i.INST_ID,i.instance_name,r.* from gv$instance i, registry$history r;


col value format a70

select * from gv$diag_info where name='Diag Trace';


set lines 200 pages 200

column comp_name format a50

col SCHEMA format a20

SELECT comp_id,comp_name, status,schema version FROM dba_registry;


Run the utlrp to compile all objects


@?/rdbms/admin/utlrp.sql



Take backup of control file and spfile and listener.ora and tnsnames.ora file


  create pfile='F:\backup_12c/DBCENTQL.ora' from spfile;

   alter database backup controlfile to trace as 'F:\backup_12c/control1.ctl';



SQL>   create pfile='F:\backup_12c/DBCENTQL.ora' from spfile;


File created.


SQL>  alter database backup controlfile to trace as 'F:\backup_12c/control1.ctl'

;
Database altered.


Check archive log mode and disable it

SQL>

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            E:\app\oracle\ARCH\ARCHDATA\DBCENTQL

Oldest online log sequence     8663

Next log sequence to archive   8665

Current log sequence           8665


shutdown database


SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.


Startup  mount


SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.


Total System Global Area 1.1525E+10 bytes

Fixed Size                  2291648 bytes

Variable Size            1979713600 bytes

Database Buffers         9529458688 bytes

Redo Buffers               13123584 bytes

Database mounted.

SQL>  alter database noarchivelog;


Database altered.


SQL> alter database open;


Database altered.


SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            E:\app\oracle\ARCH\ARCHDATA\DBCENTQL

Oldest online log sequence     8663

Current log sequence           8665

SQL>

SQL>

SQL> SELECT version FROM v$timezone_file;


VERSION

-------

     14


1 row selected.


SQL>



SQL>


SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value  FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%'  ORDER BY PROPERTY_NAME;


PROPERTY_NAME                  VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE


3 rows selected.


SQL>


Take backup of database(RMAN or Cold)


SQL> spool 'F:\backup_12c\logss.log'

SQL> select name from v$datafile;


NAME

--------------------------------------------------------------------------------

E:\APP\ORACLE\ORADATA\SYSTEM01.DBF

E:\APP\ORACLE\ORADATA\SYSAUX01.DBF

E:\APP\ORACLE\ORADATA\UNDOTBS01.DBF

E:\APP\ORACLE\ORADATADBCENTQL\USERS01.DBF


8 rows selected.


SQL> select name from v$controlfile;


NAME

--------------------------------------------------------------------------------

E:\APP\ORACLE\ORADATA\CONTROL01.CTL

E:\APP\ORACLE\FAST_RECOVERY_AREA\CONTROL02.CTL


2 rows selected.


SQL> select member from v$logfile;


MEMBER

--------------------------------------------------------------------------------

E:\APP\ORACLE\ORADATA\REDO03.LOG

E:\APP\ORACLE\ORADATA\REDO02.LOG

E:\APP\ORACLE\ORADATA\REDO01.LOG


3 rows selected.


check the stats



SQL> @E:\oracle\CHG0100819\check_stale_stats.sql


-- There are no stale statistics in APEX_030200 schema.

-- There are no stale statistics in CTXSYS schema.

-- There are no stale statistics in EXFSYS schema.

-- There are no stale statistics in MDSYS schema.

--------------------------------------------------------------------------------

-----------------------

-- OLAPSYS schema contains stale statistics use the following to gather the stat

istics --

--------------------------------------------------------------------------------

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('OLAPSYS',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);


-- There are no stale statistics in ORDSYS schema.


-- There are no stale statistics in OWBSYS schema.

--------------------------------------------------------------------------------


-- SYS schema contains stale statistics use the following to gather the statisti

cs --

--------------------------------------------------------------------------------

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);


--------------------------------------------------------------------------------

-- SYSMAN schema contains stale statistics use the following to gather the stati

stics --

--------------------------------------------------------------------------------

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYSMAN',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);


-- There are no stale statistics in WMSYS schema.

-- There are no stale statistics in XDB schema.



12 Software Installation


Create new home

No need to create folder it will take automatically

F:\app\oracle\product\12.1.0\db1


Install the 12c Binary

 run the Pre upgrade Tool from Old home


spool 'E:\oracle\CHG0100819/preupgrade.log'


@E:\oracle\CHG0100819\dbupgdiag.sql

@E:\oracle\CHG0100819\preupgrd.sql


SQL>

SQL> @E:\oracle\CHG0100819\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 DBCENTQL...

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

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


                 ====>> ERRORS FOUND for DBCENTQL <<====


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


ACTIONS REQUIRED:


1. Review results of the pre-upgrade checks:

 E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade.log


2. Execute in the SOURCE environment BEFORE upgrade:

 E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade_fixups.sql


3. Execute in the NEW environment AFTER upgrade:

 E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\postupgrade_fixups.sql


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

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

Pre-Upgrade Checks in DBCENTQL Completed.

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

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

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

SQL>


Upload db_upg_diag_DBCENTQL_11_Apr_2017_0905.log from "0" directory


 @E:\CHG0100819\oracle_12c_software\preupgrade_12.1.0.2.0_15_crlf\preupgrd.sql



Oracle Database Pre-Upgrade Information Tool 04-11-2017 09:15:54

Script Version: 12.1.0.2.0 Build: 015

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

    Database Name:  DBCENTQL

   Container Name:  Not Applicable in Pre-12.1 database

     Container ID:  Not Applicable in Pre-12.1 database

          Version:  11.2.0.4.0

       Compatible:  11.2.0.4.0

        Blocksize:  8192

         Platform:  Microsoft Windows x86 64-bit

    Timezone file:  V14

Database log mode:  ARCHIVELOG

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

                           [Update parameters]

         [Update Oracle Database 11.2.0.4.0 init.ora or spfile]

[The minimum memory/pool size recommendations are for the upgrade process

 only.  After the upgrade, these values can be reverted or adjusted as

 applicable for the target release and production work.]


WARNING: --> "processes" needs to be increased to at least 300

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

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

                          [Renamed Parameters]

                     [No Renamed Parameters in use]

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

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

                    [Obsolete/Deprecated Parameters]

--> remote_os_authent            11.1       DESUPPORTED


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


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

                            [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: 1255 MB

--> SYSAUX tablespace is adequate for the upgrade.

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


                      [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=400 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.


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.


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.4.0 database time zone version

     to the latest version which comes with the new release.

     Please refer to My Oracle Support note number 1509653.1 for details.


INFORMATION: --> There are existing Oracle components that will NOT be

     upgraded by the database upgrade script.  Typically, such components

     have their own upgrade scripts, are deprecated, or obsolete.

     Those components are:  OLAP Catalog,OWB


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

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

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

 2 WARNINGS that Oracle suggests are addressed to improve database performance.

 4 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 1509653.1

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



E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade

E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade_fixups.sql

E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\postupgrade_fixups.sql


run pre upgrade fixup script and act as per above recommendation

@E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade_fixups.sql



for removing dbconsole


     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 or stop from service.msc and run below command


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


   SET ECHO ON;

   SET SERVEROUTPUT ON;

   @F:\app\oracle\product\12.1.0.2\rdbms/admin\emremove.sql

  


Shutdown db and delete all oracle services from old oracle home (oracle database services and listener) and create new database service and start the listener from new Oracle Home


Note: open command prompt as administrator



a) delete database service and listener service from old oracle home as below

C:\Users\user1>set ORACLE_HOME=e:\app\oracle\product\11.2.0.4\db1

oradim -DELETE -SID DBCENTQL


b) set old oracle home and run below command to delete old listener


set ORACLE_HOME=e:\app\oracle\product\11.2.0.4\db1

sc delete OracleOraDb11g_home1TNSListenerlsitener

sc delete OracleOraDb11g_home1TNSListenerLISTENER_11g1104

 sc delete  OracleOraDb11g_home1TNSListener

sc delete OracleOraDb11g_home1ClrAgent


c) copy spfile to new home and create new service from new home


set ORACLE_HOME=D:\app\oracle\product\12.1.0.2

ORADIM -NEW -SID DBCENTQ8 -INTPWD oracle@123 -STARTMODE MANUAL

or

F:\app\oracle\product\12.1.0.2\database

set ORACLE_HOME=F:\app\oracle\product\12.1.0.2

set PATH=%ORACLE_HOME%/bin

echo %ORACLE_HOME

ORADIM -NEW -SID DBCENTQL -INTPWD oracle@123 -STARTMODE MANUAL

d) start new listener from new home as below


set ORACLE_HOME=F:\app\oracle\product\12.1.0.2


C:\Users\user1> lsnrctl start


you can see newly created listener in service panel(services.msc)


disable the scheduled backup


Implementation for database Upgrade


1) start database into upgrade mode with New Oracle Home and run catupgrd.sql

C:\Windows\system32>set ORACLE_SID=<Database_name>

C:\Windows\system32>set ORACLE_HOME=F:\app\oracle\product\12.1.0.2

C:\Windows\system32>sqlplus


SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 11 11:50:01 2017


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


Enter user-name: / as sysdba

Connected to an idle instance.


SQL> startup upgrade

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 1.1576E+10 bytes

Fixed Size                  6990320 bytes

Variable Size            2415920656 bytes

Database Buffers         9126805504 bytes

Redo Buffers               26562560 bytes

Database mounted.

Database opened.

SQL>

Example: Where parallelism is 6 ( n=6)


F:\backup_12c is backup location for log

cd %ORACLE_HOME%\rdbms\admin

%ORACLE_HOME%\perl\bin\perl catctl.pl -n 4 -l F:\backup_12c catupgrd.sql

Note:


I have seen that as many as 73 phases listed – some can act in parallel, other require serial execution. This will take some time depending on your system. If you wonder about the RESTART phases: those happen if timing dependencies make it necessary to rerun a certain action. The logfiles will be written by default into the directory from which you started catctl.pl, $ORACLE_HOME/rdbms/admin

Once the upgrade is finished it will shutdown the database and in the next phase restart it in normal mode.


F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN>%ORACLE_HOME%\perl\bin\perl catctl.pl

 -n 4 -l F:\backup_12c catupgrd.sql


Argument list for [catctl.pl]

SQL Process Count     n = 4

SQL PDB Process Count N = 0

Input Directory       d = 0

Phase Logging Table   t = 0

Log Dir               l = F:\backup_12c

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           = F:\app\oracle\product\12.1.0.2


Analyzing file catupgrd.sql

Log files in F:\backup_12c

catcon: ALL catcon-related output will be written to F:\backup_12c/catupgrd_catc

on_33408.lst

catcon: See F:\backup_12c/catupgrd*.log files for output generated by scripts

catcon: See F:\backup_12c/catupgrd_*.lst files for spool files, if any

Number of Cpus        = 24

SQL Process Count     = 4


------------------------------------------------------

Phases [0-73]

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

Serial   Phase #: 1 Files: 5     Time: 109s

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

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

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

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

Serial   Phase #: 6 Files: 1     Time: 28s

Serial   Phase #: 7 Files: 4     Time: 20s

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

Parallel Phase #: 9 Files: 62    Time: 62s

Restart  Phase #:10 Files: 1     Time: 1s

Serial   Phase #:11 Files: 1     Time: 33s

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

Parallel Phase #:13 Files: 91    Time: 32s

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

Parallel Phase #:15 Files: 111   Time: 45s

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

Serial   Phase #:17 Files: 3     Time: 4s

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

Parallel Phase #:19 Files: 32    Time: 33s

Restart  Phase #:20 Files: 1     Time: 1s

Serial   Phase #:21 Files: 3     Time: 15s

Restart  Phase #:22 Files: 1     Time: 1s

Parallel Phase #:23 Files: 23    Time: 111s

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

Parallel Phase #:25 Files: 11    Time: 46s

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

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: 46s

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

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

Serial   Phase #:34 Files: 1     Time: 11s

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

Restart  Phase #:36 Files: 1     Time: 1s

Serial   Phase #:37 Files: 4     Time: 83s

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

Parallel Phase #:39 Files: 13    Time: 63s

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

Parallel Phase #:41 Files: 10    Time: 13s

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

Serial   Phase #:43 Files: 1     Time: 12s

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

Serial   Phase #:45 Files: 1     Time: 12s

Serial   Phase #:46 Files: 1     Time: 2s

Restart  Phase #:47 Files: 1     Time: 1s

Serial   Phase #:48 Files: 1     Time: 349s

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

Serial   Phase #:50 Files: 1     Time: 58s

Restart  Phase #:51 Files: 1     Time: 1s

Serial   Phase #:52 Files: 1     Time: 31s

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

Serial   Phase #:54 Files: 1     Time: 656s

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

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

Restart  Phase #:57 Files: 1     Time: 1s

Serial   Phase #:58 Files: 1     Time: 196s

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

Serial   Phase #:60 Files: 1     Time: 962s

Restart  Phase #:61 Files: 1     Time: 1s

Serial   Phase #:62 Files: 1     Time: 2537s

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

Serial   Phase #:64 Files: 1     Time: 5s

Serial   Phase #:65 Files: 1 Calling sqlpatch with F:\app\oracle\product\12.1.0.

2\perl\bin\perl.exe -I F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN -I F:\app\orac

le\product\12.1.0.2\sqlpatch F:\app\oracle\product\12.1.0.2\sqlpatch\sqlpatch.pl

 -verbose -upgrade_mode_only > F:\backup_12c/catupgrd_datapatch_upgrade.log 2> F

:\backup_12c/catupgrd_datapatch_upgrade.err

returned from sqlpatch

A subdirectory or file F:\app\oracle\product\12.1.0.2\cfgtoollogs\DBCENTQL\upgra

de already exists.

    Time: 113s

Serial   Phase #:66 Files: 1     Time: 80s

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

Serial   Phase #:69 Files: 1 Calling sqlpatch with F:\app\oracle\product\12.1.0.

2\perl\bin\perl.exe -I F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN -I F:\app\orac

le\product\12.1.0.2\sqlpatch F:\app\oracle\product\12.1.0.2\sqlpatch\sqlpatch.pl

 -verbose > F:\backup_12c/catupgrd_datapatch_normal.log 2> F:\backup_12c/catupgr

d_datapatch_normal.err

returned from sqlpatch

    Time: 106s

Serial   Phase #:70 Files: 1


Serial   Phase #:71 Files: 1     Time: 1s

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

Serial   Phase #:73 Files: 1     Time: 25s


Grand Total Time: 7181s


LOG FILES: (catupgrd*.log)


Upgrade Summary Report Located in:

F:\app\oracle\product\12.1.0.2\cfgtoollogs\DBCENTQL\upgrade\upg_summary.log


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


F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN>


Post Upgradation


1) start the database in normal mode and Run the Post-Upgrade Status Tool and post fixupscript


post upgradfile pscript


REM Post Upgrade Script Generated on: 2017-04-11 09:15:52

REM Generated by Version: 12.1.0.2 Build: 015

SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 750;


DECLARE

con_name varchar2(40);


BEGIN

execute immediate

  'select dbms_preup.get_con_name from sys.dual' into con_name;


IF con_name = 'DBCENTQL' THEN


BEGIN

dbms_output.put_line ('Post Upgrade Fixup Script Generated on 2017-04-11 09:15:52  Version: 12.1.0.2 Build: 015');

dbms_output.put_line ('Beginning Post-Upgrade Fixups...');

END;


BEGIN

dbms_preup.clear_run_flag(FALSE);

END;


BEGIN

-- *****************  Fixup Details ***********************************

-- Name:        OLD_TIME_ZONES_EXIST

-- Description: Check for use of older timezone data file

-- Severity:    Informational

-- Action:      ^^^ MANUAL ACTION REQUIRED ^^^

-- Fix Summary:

--     Update the timezone using the DBMS_DST package after upgrade is complete.


dbms_preup.run_fixup_and_report('OLD_TIME_ZONES_EXIST');

END;


BEGIN

-- *****************  Fixup Details ***********************************

-- Name:        NOT_UPG_BY_STD_UPGRD

-- Description: Identify existing components that will NOT be upgraded

-- Severity:    Informational

-- Action:      ^^^ MANUAL ACTION REQUIRED ^^^

-- Fix Summary:

--     This fixup does not perform any action.


dbms_preup.run_fixup_and_report('NOT_UPG_BY_STD_UPGRD');

END;


BEGIN

dbms_output.put_line ('');

dbms_output.put_line ('**********************************************************************');

dbms_output.put_line ('                     [Post-Upgrade Recommendations]');

dbms_output.put_line ('**********************************************************************');

dbms_output.put_line ('');

END;


BEGIN

dbms_output.put_line ('                        *****************************************');

dbms_output.put_line ('                        ******** Fixed Object Statistics ********');

dbms_output.put_line ('                        *****************************************');

dbms_output.put_line ('');

dbms_output.put_line ('Please create stats on fixed objects two weeks');

dbms_output.put_line ('after the upgrade using the command:');

dbms_output.put_line ('   EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;');

dbms_output.put_line ('');

dbms_output.put_line ('^^^ MANUAL ACTION SUGGESTED ^^^');

dbms_output.put_line ('');

END;


BEGIN dbms_preup.fixup_summary(FALSE); END;


BEGIN

dbms_output.put_line ('*************** Post Upgrade Fixup Script Complete ********************');

END;


END IF;


END;

/

-- Post Upgrade Script Closed At: 2017-04-11 09:16:14

REM __________________________________________________________________________


 $ORACLE_HOME/rdbms/admin/utlu121s.sql which provides a summary of the upgrade at the end of the spool log.

It displays the status of the database components in the upgraded database and the time required to complete each component upgrade.

Any errors that occur during the upgrade are listed with each component and must be addressed.

E:\app\oracle\cfgtoollogs\DBCENTQJ\preupgrade\postupgrade_fixups.sql


 @%ORACLE_HOME%\/rdbms/admin/utlu121s.sql

SQL>  @%ORACLE_HOME%\/rdbms/admin/utlu121s.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.


CATCTL REPORT = F:\app\oracle\product\12.1.0.2\cfgtoollogs\DBCENTQL\upgrade\upg_

summary.log


PL/SQL procedure successfully completed.



Oracle Database 12.1 Post-Upgrade Status Tool           04-11-2017 14:03:02


Component                               Current         Version  Elapsed Time

Name                                    Status          Number   HH:MM:SS


Oracle Server                          UPGRADED      12.1.0.2.0  00:27:33

JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:05:46

Oracle Workspace Manager                  VALID      12.1.0.2.0  00:01:21

OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:28

OLAP Catalog                         OPTION OFF      11.2.0.4.0  00:00:00

Oracle OLAP API                           VALID      12.1.0.2.0  00:00:33

Oracle XDK                                VALID      12.1.0.2.0  00:00:55

Oracle Text                               VALID      12.1.0.2.0  00:01:25

Oracle XML Database                       VALID      12.1.0.2.0  00:09:27

Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:19

Oracle Multimedia                         VALID      12.1.0.2.0  00:03:13

Spatial                                UPGRADED      12.1.0.2.0  00:15:59

Oracle Application Express                VALID     4.2.5.00.08  00:40:41

Final Actions                                                    00:02:45

Post Upgrade                                                     00:03:59


Total Upgrade Time: 01:55:57


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.


SQL>


Important: The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process. Check the log file for "BEGIN catuppst.sql" to verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step. Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.

Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.

SQL> @catuppst.sql


@%ORACLE_HOME%\/rdbms/admin/catuppst.sql

This script can be run concurrently with utlrp.sql.

Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.

SQL> @utlrp.sql

@%ORACLE_HOME%\/rdbms/admin/utlrp.sql

Check for the integrity of the upgraded database by running dbupgdiag.sql script from the below article:

Check for the integrity of the upgraded database by running dbupgdiag.sql script from the below article:

Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.


After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.


1)  Execute in the NEW environment AFTER upgrade:

 E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\postupgrade_fixups.sql

3) complete stats on database

4) enable scheduled backup

5) enable archive mode

6) check invalid objects

SELECT DISTINCT object_name, object_type, owner FROM dba_objects  WHERE  status='INVALID' ;

SQL> select count(*) from dba_objects where status like '%INVALID%';

7) Please check upgrade logs and make sure catuppst.sql run without any error.

Run utluiobj.sql to validate that all expected packages and classes are valid :


SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql

@%ORACLE_HOME%\/rdbms/admin/utluiobj.sql

 
Collect statistics on fixed object with following command :


SQL> execute dbms_stats.gather_fixed_object_stats


13) Check the database component status to confirm upgrade completion

Connect as SYS user to the database

col comp_id format a10

col comp_name format a30

col version format a10

col status format a8

select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status from dba_registry


copy listener.ora and tnsname.ora from old home to new home and check   remote connectivity.

PGA Overview And Tuning PGA Memory in Oracle Database

What is PGA 

A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process.

When you use a dedicated server type of Oracle database, the Program Global Area (PGA) is a private memory region containing data and control information for dedicated server process,

For example, runtime area of cursors. Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region.

For complex queries (for example, some SR/OFFERING-related queries are always complex and have many JOINs among base tables), a big portion of the runtime area is required because these queries always contain several kinds of memory-intensive operators, such as the following:

• Sort-based operators, such as ORDER BY, GROUP BY, ROLLUP, and window functions
• Hash-join
• Bitmap related operations
• Write buffers used by bulk load operations

 

Configuration

Automatic PGA Memory Management

With automatic PGA memory management, you set a target size only for the total instance PGA PGA_AGGREGATE_TARGET). The database then tunes the size of the instance PGA to your target, and dynamically tunes the sizes of individual PGAs. 

Manual PGA Memory Management

Previous releases of Oracle Database required the DBA to manually specify the maximum work area size for each type of SQL operator (such as sort or hash-join). This proved to be very difficult and inefficient. Although the current release of Oracle Database supports this manual PGA memory management method, we seldom use this method (for example, we use this method to force the database to use a small workarea to simulate one client's database behavior).

When running under the automatic PGA memory management mode, sizing of work areas for all sessions is controlled by Oracle automatically. The *_AREA_SIZE parameters are ignored. At any given time, the total amount of PGA memory available to active work areas in the instance is automatically derived from the PGA_AGGREGATE_TARGET initialization parameter. This amount is set to the value of PGA_AGGREGATE_TARGET minus the amount of PGA memory allocated by other components of the system (for example, PGA memory allocated by sessions). The resulting PGA memory is then assigned to individual active work areas, based on their specific memory requirements.

NOTE: Manual management of PGA memory is not recommended.

 

Setting PGA_AGGREGATE_TARGET Initially

 

The value of the PGA_AGGREGATE_TARGET initialization parameter  should be set based on the total amount of memory available for the Oracle database instance. This value can then be tuned and dynamically modified at the instance level.

illustrates a typical situation.

Let total physical memory available on the system = 4 GB

Memory  left for the OS and other non-Oracle applications running on the same

system = 20% i.e. .8 GB

Memory available for Oracle instance = 80% i.e. 3.2 GB

You must then divide the resulting memory between the SGA and the PGA.

For OLTP systems, the PGA memory = 20% i.e. 3.2 * .2 = 0.64 GB leaving 80% for the SGA.

For DSS systems running large, memory-intensive queries, PGA memory can
typically use up to 70% of that total i.e. 3.2 * 0.7 = 2.2 GB ).

Good initial values for the parameter PGA_AGGREGATE_TARGET might be:

For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 70%

where total_mem is the total amount of physical memory available on the system.

In this example, with a value of total_mem equal to 4 GB, you can
initially set PGA_AGGREGATE_TARGET to 1600 MB for a DSS system and to
655 MB for an OLTP system.

 

Monitoring and tuning

 

We can use several dynamic performance views to monitor the PGA status. By leveraging the information collected by Oracle underway, we can assess the performance of the Program Global Area:

• V$PGA_TARGET_ADVICE - This view provides information about how the statistics cache-hit percentage and over-allocation count are affected if we change the value of PGA_AGGREGATE_TARGET.

• V$PGA_TARGET_ADVICE_HISTOGRAM - This view provides estimated information of a group of the performance statistics if we change the value of PGA_AGGREGATE_TARGET.

• V$PGASTAT - This view provides information about instance-level statistics on the PGA memory usage and the automatic PGA memory manager.

• V$PROCESS - This view provides information for each process connected to the instance.

• V$PROCESS_MEMORY - This view provides dynamic PGA memory usage by named component categories for each process.

• V$SQL_WORKAREA_HISTOGRAM - This view shows the number of work areas executed with optimal memory size, one-pass memory size, and multi-pass memory size.

• V$SQL_WORKAREA_ACTIVE - This view can be used to display the work areas that are active (or executing) and to determine whether these active work areas spill to a temporary segment.

• V$SQL_WORKAREA - This view can be used to cumulative work area statistics for each loaded cursor whose execution plan uses one or more work areas.

AWR report also provides straightforward information in the PGA Memory Advisory section.

You can also find a list of non-optimal operations information in the histogram section.

To Check size of PGA

 show parameter pga
 show parameter memory

To know total memory used by database

select (sga+pga)/1024/1024 as "sga_pga"
from
(select sum(value) sga from v$sga),
(select sum(pga_used_mem) pga from v$process);

or

select (sga+pga)/1024/1024 as "sga_pga"
from
(select sum(value) sga from v$sga),
(select sum(PGA_ALLOC_MEM) pga from v$process);

SELECT * FROM v$process_memory_detail ORDER BY pid, bytes DESC;

show parameter PGA%TARGET

 

/* PGA */

select sum(PGA_ALLOC_MEM)/1024/1024 "Total PGA Allocated (Mb)" from v$process p, v$session s where p.addr = s.paddr;

 

select PGA_TARGET_FOR_ESTIMATE/1024/1024,BYTES_PROCESSED/1024/1024,ESTD_EXTRA_BYTES_RW/1024/1024 from  V$PGA_TARGET_ADVICE;

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,

       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

       ESTD_OVERALLOC_COUNT

FROM   v$pga_target_advice;

 

column name format a40

column value format 999,999,999

select name, value from v$pgastat;

 

Following query can be used to check the processes which are allocating more PGA memory

set lines 500
set pages 1000
col program format a20
col module format a20
col machine format a40

select a.spid OS_PROCESS_ID,b.sid,b.serial#,b.username,b.schemaname,b.osuser,b.machine,b.status,b.server,
b.program,b.module,round(a.PGA_ALLOC_MEM/1024/1024,2) "Currecn PGA Allocated",round(a.PGA_USED_MEM/1024/1024,2)"Currect PGA memory in use" from v$process a,v$session b where  a.addr=b.PADDR
order by a.PGA_ALLOC_MEM;

PGA_USED_MEM in v$process means the memory which has been used by oracle, there is one more column in v$process i.e. PGA_ALLOC_MEM which means the actual memory which has been allocated to use, So try using PGA_ALLOC_MEM instead of PGA_USED_MEM in your query because you are caculating on the basis of physical memory and PGA_ALLOC_MEM is the memory which has not been released to OS.

Note
====
The idea with the PGA Memory Advisory is the following:

Determine if the PGA is already at the PGA_SIZE with the lowest Estd Extra W/A MB Read/ Written to Disk and/or Estd Time. If not, then to get the best performance from the PGA, the PGA_TARGET should be changed to the optimal PGA_SIZE.

Using the results from the AWR report, we see that the smallest Estd Extra W/A MB Read/ Written to Disk is 0, and the smallest Estd Time is 94,412,484. However, the biggest improvement appears to be at PGA_SIZE_FACTOR of 0.5, with any further increase in PGA_SIZE_FACTOR show no diminishing returns. So, using the results above, the PGA_TARGET at 800mb would give the best performance without allocating more memory than may be necessary.



A forecast of the memory consumption is quite difficult as it depends on multiple factors, such as how big the database is, what will be the load and how it will be distributed, if you will use Java objects, and so on.

As a general recommendation, we suggest you to:
1. Use ASMM (i.e. setting SGA_TARGET parameter) to a certain value and preferably set SGA_MAX_SIZE to a larger value, to allow you to increase the memory without database restart. In this way, granules of memory will be moved automatically between the memory components (shared_pool_size, db_cache_size) automatically when the database considers one component needs most of the memory.

2. After a while of database load(a few days), please query the view V$SGA_TARGET_ADVICE, which provides tuning advice for the SGA_TARGET initialization parameter, as described in https://docs.oracle.com/cd/E11882_01/server.112/e25494/memory.htm#ADMIN11202
Please follow the recommendation from the v$sga_target_advice and increase the memory, if recommended.

3. After you have setup a recommended value for the SGA_TARGET, please wait a few days until the database will experience the usual load, and set the SHARED_POOL_SIZE to the maxim TARGET_SIZE value found for the shared pool in either V$SGA_RESIZE_OPS or DBA_HIST_MEMORY_RESIZE_OPS (whichever of the 2 is the largest).
Also, it is recommended to set minimum values for:
- the DB_CACHE_SIZE (usually larger than the SHARED_POOL_SIZE)
- for LARGE_POOL_SIZE, if you use RMAN for backup the database or if you use parallel executions
- for STREAMS_POOL_SIZE, if you use AQ or if you export/import with Datapump regularly (usually 150MB or more)
- for JAVA_POOL_SIZE, if you use JAVA objects in the database

These values you setup will be minimum values for these components, but they can grow to accommodate with database load, when needed. However, their sum will always be equal to SGA_TARGET.

Check LGWR Traces

Even if the average wait for 'log file parallel write' may be in the normal range, there may be peaks where the write time is longer and will therefore influence waits on 'log file sync'. From 10.2.0.4, messages are written in the LGWR trace when a write to the log file takes more than 500 ms. This is quite a high threshold so a lack of messages does not necessarily mean there is no problem. The messages look similar to the following:

*** 2011-10-26 10:14:41.718
Warning: log write elapsed time 21130ms, size 1KB
(set event 10468 level 4 to disable this warning)


*** 2011-10-26 10:14:42.929
Warning: log write elapsed time 4916ms, size 1KB
(set event 10468 level 4 to disable this warning)