Pak DBA: Installing Patch 11.2.0.3 (Upgrade from 11.2.0.2 to 11.2.0.3)

Document to follow
http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#UPGRD52731

To run the Pre-Upgrade Information Tool

Log in to the system as the owner of the environment of the database being upgraded.

Important:
The Pre-Upgrade Information Tool must be copied to and must be run from the environment of the database being upgraded.
Start SQL*Plus.
Connect to the database instance as a user with SYSDBA privileges.
Set the system to spool results to a log file for later analysis:
```
SQL> SPOOL upgrade_info.log
```

Run the Pre-Upgrade Information Tool:

SQL> @$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql

Turn off the spooling of script results to the log file:
```
SQL> SPOOL OFF
```
Check the output of the Pre-Upgrade Information Tool in upgrade_info.log.

##################################################

OUTPUT OF upgrade_info.log file

#################################################

Oracle Database 11.2 Pre-Upgrade Information Tool 12-24-2013 13:47:49

Script Version: 11.2.0.3.0 Build: 001

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

Database:

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

--> name: TEST

--> version: 11.2.0.2.0

--> compatible: 11.2.0.0.0

--> blocksize: 8192

--> platform: Microsoft Windows x86 64-bit

--> timezone file: V14

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

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 705 MB

--> SYSAUX tablespace is adequate for the upgrade.

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

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

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

--> EM Repository [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 is turned on and currently contains no objects.

.... Because it is REQUIRED that the recycle bin be empty prior to upgrading

.... and your recycle bin is turned on, you may need to execute the command:

PURGE DBA_RECYCLEBIN

.... prior to executing your upgrade to confirm the recycle bin is empty.

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.

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

To check the status of access and add ACLs for network utility packages

Run the pre-upgrade information tool as described in "Using the Pre-Upgrade Information Tool".

Check the output from the pre-upgrade information tool (upgrade_info.log) for messages such as the following:

WARNING: --> Database contains schemas with objects dependent on network packages.

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

.... USER WKSYS has dependent objects.

.... USER SYSMAN has dependent objects.

.... USER FLOWS_010600 has dependent objects.

.

Query the DBA_DEPENDENCIES view to obtain more information about the dependencies. For example:

SELECT * FROM DBA_DEPENDENCIES

WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP')

AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

Prepare post-upgrade scripts now to make the scripts available for use in the test environment. This ensures the new access controls are part of your upgrade testing.

To configure network access control lists (ACLs) in the database so that these packages can work as they did in prior releases, see the example script provided in "Configuring Fine-Grained Access to External Network Services After Upgrading Oracle Database". This script shows how to use the DBMS_NETWORK_ACL_ADMIN package to create, assign, and add privileges to the access control list.

After the upgrade, you must grant the specific required privileges. Access is based on the usage in the original database.

#############################################################

Decreasing Downtime for Gathering Optimizer Statistics (Optional)

When upgrading to the new Oracle Database 11g release, optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection process can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.

To decrease the amount of downtime incurred when collecting statistics

Collect statistics before performing the actual database upgrade. Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATSprocedure to gather these statistics. For example, you can enter the following SQL statement:
```
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
```

################################################################################

Ensuring That No Files Are in Backup Mode

Files must not be in backup mode when performing the upgrade; therefore, you must wait until backups are completed. You can query the system to see a list of any files in backup mode and then take appropriate action by either waiting for the backup to complete, or by aborting any backups that are not needed.

To get a list of files in backup mode

Issue the following statement:

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

Resolving Outstanding Distributed Transactions

You must resolve outstanding distributed transactions before performing the upgrade. You can do this by first querying to see any pending transactions, and then committing the transactions. You must wait until all pending distributed transactions have been committed.

To resolve outstanding distributed transactions

Issue the following statement:
```
SQL> SELECT * FROM dba_2pc_pending;
```

If the query in the previous step returns any rows, then issue the following statements:

SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

Synchronizing a Standby Database with the Primary Database

If a standby database exists, then you must synchronize it with the primary database.

To check if a standby database exists and to synchronize it

Issue the following query:

SQL> SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

If the query in the previous step returns a row, then synchronize the standby database with the primary database.
- Make sure all the logs are transported to the standby server after a final log switch in the primary.
- Start the recovery of the standby database with the NODELAY option.

Purging the Database Recycle Bin

The database recycle bin must be empty before you begin the upgrade process. You use the PURGE statement to remove items and their associated objects from the recycle bin and release their storage space.

To empty the database recycle bin

Issue the following command:
```
SQL> PURGE dba_recyclebin
```

####################################################################

From newly installed home 11.2.0.3 invoke DBUA (Database Upgrade Assistant)

dbua
On Windows operating systems, select
Start >
Programs >
Oracle - HOME_NAME >
Configuration and Migration Tools >

Database Upgrade Assistant.