Emre Baransel - Oracle Blog

Upgrade was done from 10.2.0.3 to 10.2.0.4. Dataguard has physical standby configuration. Steps and commands may vary depending on your environment.

...... primary database commands

...... standby database commands

1. On both the primary and standby host uncompress the downloaded patchset file into a new directory.

2. On the active primary database instance, identify and record the current log thread and sequence number. Then, archive the current log:

SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT';

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

3. Shut down the existing Oracle Database instance on the primary host with normal or immediate priority. Stop all listeners, agents and other processes running against the ORACLE_HOME

%lsnrctl stop

% emctl stop dbconsole

SQL> SHUTDOWN IMMEDIATE;

4. On the active standby instance that is running Redo Apply, query the V$LOG_HISTORY view to verify that each log file archived in Step 2 has been received and applied to the standby database. For example:

SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

5. Once the last log has been applied stop Redo Apply cancel managed recovery on the standby database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

6. Shutdown the standby instance on the standby host. Stop all listeners, agents and other processes running against the ORACLE_HOME.

% lsnrctl stop

% emctl stop dbconsole

SQL> SHUTDOWN IMMEDIATE;

7. Use 'runInstaller' to install the patchset. Do this on both the primary and standby ORACLE_HOME.

%./runInstaller

%./runInstaller

8. Once the patchset has been installed on on all hosts / nodes, startup the standby listener on the standby host first.
% lsnrctl start

9. Startup mount the standby database.
% sqlplus / as sysdba
SQL> startup mount

10. Place the standby database in managed recovery mode.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE parallel 8 DISCONNECT FROM SESSION;

11. Startup the primary instance on the primary host.
% sqlplus / as sysdba
SQL> STARTUP UPGRADE

12. Ensure that remote archiving to the standby database is functioning correctly by switching logfiles on the primary and verifying that v$archive_dest.status is valid.

SQL> select dest_id, status from v$archive_dest;

SQL> alter system archive log current;

13. Check if the archived log is applied on the standby database

SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

14. Start the Database Upgrade Assistant and upgrade the primary database.

% cd /oracle/app/oracle/product/10.2.0/bin

% ./dbua

There should be a line in the /etc/oratab file for the database that will be upgraded. Otherwise you cannot see the database when you open Database Upgrade Assistant.

DBUA may give a warning about the invalid objects onthe database and you should note these invalid object before the upgrade operation. The script below can be used to see a list of invalid objects in the database.

break on c1 skip 2

set pages 999

col c1 heading 'owner' format a15

col c2 heading 'name' format a40

col c3 heading 'type' format a10

ttitle 'Invalid|Objects'

select

owner c1,

object_type c3,

object_name c2

from

dba_objects

where

status != 'VALID'

order by

owner,

object_type

;

15. Once DBUA completes make note of the current log sequence and archive the current log:

SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT';
SQL> alter system archive log current;

16. Restart the primary database:
SQL> SHUTDOWN
SQL> STARTUP

17. Once all actions have been completed verify the standby database has been recovered to the last archive log produced by the primary. (Step 15)
On the primary:
SQL> select max(sequence#) from v$archived_log;
On the standby:
SQL> select max(sequence#) from v$log_history;

18. Run the script above again to get the list of invalid objects after database upgrade.

19. Check the success of upgrade with the following query

SQL>select comp_name, status, version from dba_registry;

20. Upgrade RMAN catalog

$ rman catalog username/password@alias

RMAN> UPGRADE CATALOG;

Note: After executing the query in step 19, i saw that Oracle Database Packages and Types component has the status INVALID, here is a work around to solve this issue:

SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql

http://forums.oracle.com/forums/thread.jspa?threadID=669838