Manual database upgrade 11.2.0.3 to 11.2.0.4

Database Upgrade to 11gR2

11.2.0.3 to 11.2.0.4 Manual

This document is describe you how to manual upgrade 11.2.0.3 to 11.2.0.4 in out of box setup.

For that you need to PSU 11.2.0.3.5+. I am using PSU 11.2.0.3.14. For details of PSU installation please click here (PSU Apply).

ACTION_TIME ACTION VERSION COMMENTS BUNDLE_SERIES

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

17-SEP-11 10.21.11.595816 AM APPLY 11.2.0.3 Patchset 11.2.0.2.0 PSU

25-JUN-15 09.02.05.268136 AM APPLY 11.2.0.3 Patchset 11.2.0.2.0 PSU

13-AUG-15 11.25.32.694491 PM APPLY 11.2.0.3 PSU 11.2.0.3.14 PSU

· After applying latest PSU we are moving to database upgrade to 11.2.0.4.

· Download Oracle 11.2.0.4 software from Oracle Download Index.

· Install the software in /u01/app/oracle/product/11.2.0.4/dbhome_1 (separate home).

· Create pfile from spfile and copy to New Home under dbs folder.

· Before upgrade run the preupgrade tool to check the pre-requisites before proceeding upgrade which is available in new home. Fix as per output result.

SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 08-14-2015 00:49:24

Script Version: 11.2.0.4.0 Build: 001

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

Database:

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

--> name: EMREP

--> version: 11.2.0.3.0

--> compatible: 11.2.0.0.0

--> blocksize: 8192

--> platform: Linux x86 64-bit

--> timezone file: V14

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

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 1065 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 728 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 400 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 60 MB

--> EXAMPLE tablespace is adequate for the upgrade.

.... minimum required size: 309 MB

--> MGMT_TABLESPACE tablespace is adequate for the upgrade.

.... minimum required size: 1969 MB

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

Flashback: OFF

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

Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]

Note: Pre-upgrade tool was run on a lower version 64-bit database.

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

--> If Target Oracle is 32-Bit, refer here for Update Parameters:

-- No update parameter changes are required.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:

-- No update parameter changes are required.

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

Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]

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

-- No renamed parameters found. No changes are required.

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

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

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

-- No obsolete parameters found. No changes are required

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

Components: [The following database components will be upgraded or installed]

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

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

--> OLAP Catalog [upgrade] VALID

--> Oracle Text [upgrade] VALID

--> Oracle XML Database [upgrade] VALID

--> Oracle Java Packages [upgrade] VALID

--> Oracle interMedia [upgrade] VALID

--> Spatial [upgrade] VALID

--> Expression Filter [upgrade] VALID

--> Rule Manager [upgrade] VALID

--> Oracle Application Express [upgrade] VALID

... APEX will only be upgraded if the version of APEX in

... the target Oracle home is higher than the current one.

--> Oracle OLAP API [upgrade] VALID

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

Miscellaneous Warnings

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

WARNING: --> Your recycle bin contains 58 object(s).

.... It is REQUIRED that the recycle bin is empty prior to upgrading

.... your database. The command:

PURGE DBA_RECYCLEBIN

.... must be executed immediately prior to executing your upgrade.

WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.

.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.

.... USER APEX_030200 has dependent objects.

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

Recommendations

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

Oracle recommends gathering dictionary statistics prior to

upgrading the database.

To gather dictionary statistics execute the following command

while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

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

Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands

while connected AS SYSDBA:

Events:

SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2

WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'

Trace Events:

SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2

WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

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

SQL> PURGE DBA_RECYCLEBIN ;

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

SQL> set feedback on

SQL> SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE UPPER(name) ='EVENT' AND isdefault='FALSE';

no rows selected

SQL> SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE' ;

no rows selected

· Restore point created.

To avoid any kind of disaster we created a Guaranteed Restore Point.

SQL> create restore point before_upgrade guarantee flashback database;

SQL> select * from v$restore_point;

SCN PRE NAME

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

3068252 YES BEFORE_UPGRADE

· Check Oracle RDBMS time zone definitions

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

· Shutdown database and setting new environment variable (11.2.0.4) home.

· Start the database using new oracle home.

[oracle@vm211 SOFT]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 14 01:17:39 2015

Connected to an idle instance.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size 2254952 bytes

Variable Size 805308312 bytes

Database Buffers 1325400064 bytes

Redo Buffers 4923392 bytes

Database mounted.

Database opened.

SQL> @?/rdbms/admin/catupgrd

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

DOC>

DOC> The first time this script is run, there should be no error messages

DOC> generated; all normal upgrade error messages are suppressed.

…..

Oracle Database 11.2 Post-Upgrade Status Tool 08-14-2015 01:34:42

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server

. VALID 11.2.0.4.0 00:05:28

JServer JAVA Virtual Machine

. VALID 11.2.0.4.0 00:03:55

Oracle Workspace Manager

. VALID 11.2.0.4.0 00:00:18

OLAP Analytic Workspace

. VALID 11.2.0.4.0 00:00:20

OLAP Catalog

. VALID 11.2.0.4.0 00:00:17

Oracle OLAP API

. VALID 11.2.0.4.0 00:00:11

Oracle XDK

. VALID 11.2.0.4.0 00:00:28

Oracle Text

. VALID 11.2.0.4.0 00:00:15

Oracle XML Database

. VALID 11.2.0.4.0 00:01:14

Oracle Database Java Packages

. VALID 11.2.0.4.0 00:00:10

Oracle Multimedia

. VALID 11.2.0.4.0 00:01:32

Spatial

. VALID 11.2.0.4.0 00:01:14

Oracle Expression Filter

. VALID 11.2.0.4.0 00:00:05

Oracle Rules Manager

. VALID 11.2.0.4.0 00:00:03

Oracle Application Express

. VALID 3.2.1.00.12

Final Actions

. 00:00:00

Total Upgrade Time: 00:15:38

PL/SQL procedure successfully completed.

SQL>

SQL> SET SERVEROUTPUT OFF

SQL> SET VERIFY ON

SQL> commit;

Commit complete.

SQL>

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> DOC

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

DOC>

DOC> The above sql script is the final step of the upgrade. Please

DOC> review any errors in the spool log file. If there are any errors in

DOC> the spool file, consult the Oracle Database Upgrade Guide for

DOC> troubleshooting recommendations.

DOC>

DOC> Next restart for normal operation, and then run utlrp.sql to

DOC> recompile any invalid application objects.

DOC>

DOC> If the source database had an older time zone version prior to

DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade

DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped

DOC> with Oracle.

DOC>

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

DOC>#

SQL>

SQL> Rem Set errorlogging off

SQL> SET ERRORLOGGING OFF;

SQL>

SQL> REM END OF CATUPGRD.SQL

SQL>

SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.

SQL> REM This forces user to start a new sqlplus session in order

SQL> REM to connect to the upgraded db.

SQL> exit

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

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

· Startup the upgraded database and follow post upgrade task.

[oracle@vm211 SOFT]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 14 01:36:31 2015

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size 2254952 bytes

Variable Size 805308312 bytes

Database Buffers 1325400064 bytes

Redo Buffers 4923392 bytes

Database mounted.

Database opened.

SQL> @?/rdbms/admin/utlu112s

Oracle Database 11.2 Post-Upgrade Status Tool 08-14-2015 01:39:29

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server

. VALID 11.2.0.4.0 00:05:28

JServer JAVA Virtual Machine

. VALID 11.2.0.4.0 00:03:55

Oracle Workspace Manager

. VALID 11.2.0.4.0 00:00:18

OLAP Analytic Workspace

. VALID 11.2.0.4.0 00:00:20

OLAP Catalog

. VALID 11.2.0.4.0 00:00:17

Oracle OLAP API

. VALID 11.2.0.4.0 00:00:11

Oracle XDK

. VALID 11.2.0.4.0 00:00:28

Oracle Text

. VALID 11.2.0.4.0 00:00:15

Oracle XML Database

. VALID 11.2.0.4.0 00:01:14

Oracle Database Java Packages

. VALID 11.2.0.4.0 00:00:10

Oracle Multimedia

. VALID 11.2.0.4.0 00:01:32

Spatial

. VALID 11.2.0.4.0 00:01:14

Oracle Expression Filter

. VALID 11.2.0.4.0 00:00:05

Oracle Rules Manager

. VALID 11.2.0.4.0 00:00:03

Oracle Application Express

. VALID 3.2.1.00.12

Final Actions

. 00:00:00

Total Upgrade Time: 00:15:38

PL/SQL procedure successfully completed.

· For migrating the baseline from pre 11g database, run the below one if required.

SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catuppst.sql

TIMESTAMP

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

COMP_TIMESTAMP POSTUP_BGN 2015-08-14 01:40:35

PL/SQL procedure successfully completed.

This script will migrate the Baseline data on a pre-11g database

to the 11g database.

... ...

... Completed Moving the Baseline Data ...

... ...

... If there are no Move BL Data messages ...

... above, then there are no renamed ...

... baseline tables in the system. ...

... ...

... Completed the Dropping of the ...

... Renamed Baseline Tables ...

... ...

... If there are no Drop Table messages ...

... above, then there are no renamed ...

... baseline tables in the system. ...

... ...

PL/SQL procedure successfully completed.

0 rows created.

Commit complete.

Table created.

2 rows created.

1 row updated.

2 rows updated.

0 rows updated.

Table dropped.

Commit complete.

0 rows updated.

Commit complete.

0 rows updated.

Commit complete.

0 rows updated.

Commit complete.

0 rows created.

Commit complete.

0 rows created.

Commit complete.

PL/SQL procedure successfully completed.

TIMESTAMP

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

COMP_TIMESTAMP POSTUP_END 2015-08-14 01:40:36

PL/SQL procedure successfully completed.

Generating apply and rollback scripts...

Check the following file for errors:

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_EMREP_GENERATE_2015Aug14_01_40_38.log

Apply script: /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catbundle_PSU_EMREP_APPLY.sql

Rollback script: /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catbundle_PSU_EMREP_ROLLBACK.sql

PL/SQL procedure successfully completed.

Executing script file...

SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT

SQL> SELECT '/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;

SQL> SPOOL &spool_file

SQL> exec dbms_registry.set_session_namespace('SERVER')

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET current_schema = SYS;

Session altered.

SQL> PROMPT Updating registry...

Updating registry...

SQL> INSERT INTO registry$history

2 (action_time, action,

3 namespace, version, id,

4 bundle_series, comments)

5 VALUES

6 (SYSTIMESTAMP, 'APPLY',

7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),

8 '11.2.0.4',

9 0,

10 'PSU',

11 'Patchset 11.2.0.2.0');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off

SQL> SET echo off

Check the following log file for errors:

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_EMREP_APPLY_2015Aug14_01_40_38.log

· Recompile objects

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

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2015-08-14 01:41:13

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC> objects in the database. Recompilation time is proportional to the

DOC> number of invalid objects in the database, so this command may take

DOC> a long time to execute on a database with a large number of invalid

DOC> objects.

DOC>

DOC> Use the following queries to track recompilation progress:

DOC>

DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.

DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC> This script automatically chooses serial or parallel recompilation

DOC> based on the number of CPUs available (parameter cpu_count) multiplied

DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC> On RAC, this number is added across all RAC nodes.

DOC>

DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC> recompilation. Jobs are created without instance affinity so that they

DOC> can migrate across RAC nodes. Use the following queries to verify

DOC> whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP

COMP_TIMESTAMP UTLRP_END 2015-08-14 01:42:30

DOC> The following query reports the number of objects that have compiled

DOC> with errors.

DOC>

DOC> If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

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

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

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

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

· For object invalidation in sys schema before upgrade the run below one.

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

Oracle Database 11.2 Post-Upgrade Invalid Objects Tool 08-14-2015 01:43:03

This tool lists post-upgrade invalid objects that were not invalid

prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).

Owner Object Name Object Type

PL/SQL procedure successfully completed.

· Verify the details

select * from registry$history

SQL> /

ACTION_TIME ACTION VERSION COMMENTS BUNDLE_SERIES

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

17-SEP-11 10.21.11.595816 AM APPLY 11.2.0.3 Patchset 11.2.0.2.0 PSU

25-JUN-15 09.02.05.268136 AM APPLY 11.2.0.3 Patchset 11.2.0.2.0 PSU

13-AUG-15 11.25.32.694491 PM APPLY 11.2.0.3 PSU 11.2.0.3.14 PSU

14-AUG-15 01.34.42.240480 AM VIEW INVALIDATE view invalidation

14-AUG-15 01.34.42.293555 AM UPGRADE 11.2.0.4.0 Upgraded from 11.2.0.3.0

14-AUG-15 01.40.38.813582 AM APPLY 11.2.0.4 Patchset 11.2.0.2.0 PSU

6 rows selected.

SQL> select * from v$version ;

BANNER

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

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

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

For Deinstallation Old Home click below link

Deinstall old ORACLE_HOME

http://dbaocm.blogspot.in/2015/08/deinstall-old-oracle-home-in-11g-r2.html

Oracle Certified Master's - Blogs

Home

Friday, August 14, 2015

Manual database upgrade 11.2.0.3 to 11.2.0.4

1 comment: