About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Saturday, October 6, 2012

Oracle Database 11gR1 to 11gR2 Upgrade

Upgrading 11GR1 to 11GR2, PFB steps :-
  • Install 11gR2 software in a new Oracle home.
  • Run the pre-upgrade steps in current Oracle (11gR1) version and make the necessary modification  as suggested by pre upgrade utility.
  • Upgrade Oracle version to 11gR2 using manual upgrade method.
  • After upgrade process has completed, run the post-upgrade checks. 
  • Recreate the listener using the new Oracle version. 
  • Update the time zone to the appropriate version.
Install 11GR2 software only and install it in a new Oracle Home.

Preupgrade steps :- Run the utlu112i.sql in 11GR1 oracle home.

PFB Output of  utlu112i.sql script
[cognos@rac1 ~]$ sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 6 04:54:58 2012

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL> @/u04/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool    10-06-2012 04:55:38
.
**********************************************************************
Database:
**********************************************************************
--> name:          ORCL
--> version:       11.1.0.6.0
--> compatible:    11.1.0.0.0
--> blocksize:     8192
--> platform:      Linux IA (32-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1049 MB
.... AUTOEXTEND additional space required: 349 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 909 MB
.... AUTOEXTEND additional space required: 308 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 462 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 78 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
.
**********************************************************************
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
--> Oracle Ultra Search          [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle Application Express   [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 11.1.0.6.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
....   SYSMAN
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 33 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.


. PL/SQL procedure successfully completed.

  
Purge recyclebin of database.
 SQL> PURGE DBA_RECYCLEBIN;

           DBA Recyclebin purged.

Gather Schema and Database statistics
 SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

           PL/SQL procedure successfully completed.
 SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

           PL/SQL procedure successfully completed.

 SQL> exit

Remove Oracle EM database control.

[cognos@rac1 ~]$ export ORACLE_SID=orcl
[cognos@rac1 ~]$ emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Mar 7, 2012 12:20:43 PM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 7, 2012 12:20:53 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at E:\app\svijay7\cfgtoollogs\emca\test\emca_2012_03_07_12_20_43.log.
Mar 7, 2012 12:20:55 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 7, 2012 12:21:03 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 7, 2012 12:24:23 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 7, 2012 12:24:36 PM


Oracle EM successfully removed.

Shut Down your Oracle 11GR1 Database.
Copy your init.ora file of 11GR1 database to 11GR2 Oracle home dbs folder
Edit the Pfile by increasing java pool size to 64 MB, audit_file_dest, diagnostic_dest and db_recovery_file_dest parameters according to the new 11GR2 oracle home.

Upgrading Database to 11GR2 

Invoke sqlplus from 11GR2 Oracle Home

[cognos@rac1 ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 12:34:14 2012

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


Enter user-name: /as sysdba
Connected to an idle instance.


SQL> startup upgrade pfile='/u04/app/cognos/dbs/initpfile.ora';
ORACLE instance started.
Total System Global Area 602619904 bytes
Fixed Size 1334828 bytes
Variable Size 163578324 bytes
Database Buffers 432013312 bytes
Redo Buffers 5693440 bytes
Database mounted.
Database opened.


(If you are upgrading this in windows server then you will get below mentioned error)

SQL> spool F:\11gr2_upgrade.log     (remember this log file will be very large, can be in GB's)
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/catupgrd.sql

DOC>#######################################################################
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.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
DOC>
DOC> ORA-00001: unique constraint (<constraint_name>) violated
DOC> possibly in conjunction with
DOC> ORA-06512: at "<procedure/function name>", line NN
DOC>
DOC> These errors will automatically be suppressed by the Database Upgrade
DOC> Assistant (DBUA) when it re-runs an upgrade.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect

DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database server version is not correct for this script.
DOC> Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
SELECT TO_NUMBER('MUST_BE_11_2') FROM v$instance
*
ERROR at line 1:
ORA-01722: invalid number
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Steps to recover from this error in windows server
a. Stop all Oracle Services

C:\Documents and Settings\amit>NET STOP OracleServiceorcl
The OracleServiceorcl service is stopping.
The OracleServiceorcl service was stopped successfully.


b. Delete Oracle service using ORADIM binary from which the database is upgraded

C:\Documents and Settings\amit>oradim -delete -SID orcl
Instance deleted.
C:\Documents and Settings\amit>ORADIM -NEW -SID orcl -INTPWD sys -STARTMODE manual
Instance created.

After this again run the below upgrade script :-
@\u02\app\cognos\product\11.2.0\dbhome_2\rdbms\admin\catupgrd.sql

If you are upgrading this in Unix server then you will get below mentioned Output

SQL> spool F:\11gr2_upgrade.log     (remember this log file will be very large, can be in GB's)
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/catupgrd.sql


It will take more than an hour to finish the upgrade process and database will be in a shutdown stage. 

Post Upgrade steps:-

Start the database and run the post upgrade utility

[cognos@rac1 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 12:34:14 2012

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

Enter user-name: /as sysdba
Connected to an idle instance.


SQL> startup pfile='/u04/initorcl.ora'
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.
Database opened.


SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 06-10-2012 10:40:44
.
Component Status Version HH:MM:SS
.
Oracle Server                                                      . VALID 11.2.0.1.0 00:17:39  

JServer JAVA Virtual Machine                           . VALID 11.2.0.1.0 00:06:28
Oracle Workspace Manager                               . VALID 11.2.0.1.0 00:00:45
OLAP Analytic Workspace                                 . VALID 11.2.0.1.0 00:01:53
OLAP Catalog                                                    . VALID 11.2.0.1.0 00:01:58
Oracle OLAP API                                              . VALID 11.2.0.1.0 00:00:29
Oracle XDK                                                       . VALID 11.2.0.1.0 00:01:11
Oracle Text                                                        . VALID 11.1.0.6.0 00:00:00
Oracle XML Database                                      . VALID 11.2.0.1.0 00:03:52
Oracle Database Java Packages                      . VALID 11.2.0.1.0 00:00:47
Oracle Multimedia                                            . VALID 11.2.0.1.0 00:09:02
Spatial                                                               . VALID 11.2.0.1.0 00:05:45
Oracle Expression Filter                                   . VALID 11.2.0.1.0 00:00:13
Oracle Rule Manager                                        . VALID 11.2.0.1.0 00:00:10
Gathering Statistics. 00:04:29
Total Upgrade Time: 01:34:54
PL/SQL procedure successfully completed.


SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


Run catuppst.sql to perform post-upgrade actions.

SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/catuppst.sql

TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP   POSTUP_BGN 2012-10-06 12:40:50


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 2012-10-06 12:41:03


Run the utlrp.sql script to recompile any invalid objects.

SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_BGN  2012-10-06 12:43:14

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  2012-10-06 12:46:49

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). 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
-------------------
                  0

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


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


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


COUNT(*)
----------
          0


Recreate the Listener service 

Recreate the listener service from new oracle home.

Check the Current version of  the Oracle time Zone definitions in Upgraded database

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
         4


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             4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE                      NONE



(if this is LOWER than 11 (for 11.2.0.1 ) or 14 (for 11.2.0.2 and 11.2.0.3) then it is *recommended* to upgrade the timezone version)

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.


SQL> exec DBMS_DST.BEGIN_PREPARE(11);


PL/SQL procedure successfully completed.

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             4
DST_SECONDARY_TT_VERSION      11
DST_UPGRADE_STATE                      PREPARE


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[cognos@rac1 ~]$
[cognos@rac1 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 13:03:59 2012

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> shu  immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


Update the Database DST version

SQL> startup upgrade pfile='/u04/initorcl.ora'
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> create spfile='+AMIT_R1' from pfile='/u04/initorcl.ora';

File created.

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            4
DST_SECONDARY_TT_VERSION     11
DST_UPGRADE_STATE                    PREPARE

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
BEGIN DBMS_DST.BEGIN_UPGRADE(11); END;

*
ERROR at line 1:
ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1054
ORA-06512: at line 1


SQL> EXEC DBMS_DST.END_PREPARE;

PL/SQL procedure successfully completed.

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         4
DST_SECONDARY_TT_VERSION   0
DST_UPGRADE_STATE                   NONE

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);

PL/SQL procedure successfully completed.


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         11
DST_SECONDARY_TT_VERSION   4
DST_UPGRADE_STATE                  UPGRADE


-- Optionally you can check what user tables still need to be updated using DBMS_DST.UPGRADE_DATABASE 
-- BEGIN_UPGRADE upgrades system tables that contain TSTZ data and marks user tables (containing TSTZ data) with the UPGRADE_IN_PROGRESS property. 
-- even if this select gives no rows you still need to do to the rest of the steps
-- it simply gives an indication of how many user objects need to processed in the later steps

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

OWNER                              TABLE_NAME                                                      UPG
------------------------------    ------------------------------                                          ---
SYSMAN                             MGMT_PROV_NET_CONFIG                            YES
SYSMAN                             MGMT_PROV_IP_RANGE                                 YES
SYSMAN                             MGMT_PROV_SUITE_INST_MEMBERS          YES
SYSMAN                              MGMT_PROV_BOOTSERVER                           YES
SYSMAN                              AQ$_MGMT_NOTIFY_QTABLE_L                    YES
SYSMAN                              AQ$_MGMT_LOADER_QTABLE_S                   YES
SYSMAN                              AQ$_MGMT_LOADER_QTABLE_L                   YES
SYSMAN                              AQ$_MGMT_NOTIFY_QTABLE_S                     YES
SYSMAN                              MGMT_CONFIG_ACTIVITIES                           YES
SYSMAN                              MGMT_PROV_STAGING_DIRS                         YES
SYSMAN                              MGMT_PROV_OPERATION                              YES

OWNER                                TABLE_NAME                                                     UPG
------------------------------      ------------------------------                                         ---
SYSMAN                              MGMT_PROV_ASSIGNMENT                           YES
SYSMAN                              MGMT_PROV_CLUSTER_NODES                   YES
SYSMAN                              MGMT_PROV_RPM_REP                                  YES
SYSMAN                             MGMT_PROV_DEFAULT_IMAGE                    YES
IX                                       AQ$_STREAMS_QUEUE_TABLE_S                         YES
IX                                      AQ$_STREAMS_QUEUE_TABLE_L                         YES
IX                                      AQ$_ORDERS_QUEUETABLE_S                             YES
IX                                     AQ$_ORDERS_QUEUETABLE_L                             YES

19 rows selected.


These 19 tables need to be upgraded

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


[cognos@rac1 dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 13:24:53 2012

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

Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.
Database opened.
 

Previeous 19 tables will be upgraded now 

SQL>
SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.


SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
  2  DBMS_DST.UPGRADE_DATABASE(:numfail,
  3  parallel => TRUE,
  4  log_errors => TRUE,
  5  log_errors_table => 'SYS.DST$ERROR_TABLE',
  6  log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
  7  error_on_overlap_time => FALSE,
  8  error_on_nonexisting_time => FALSE);
  9  DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
 10  END;
 11  /
Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_SUITE_INST_MEMBERS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_STAGING_DIRS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_RPM_REP
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_OPERATION
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_NET_CONFIG
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_IP_RANGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_DEFAULT_IMAGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_CLUSTER_NODES
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_BOOTSERVER
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_L
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_L
Number of failures: 0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_S
Number of failures: 0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_L
Number of failures: 0
Table list: IX.AQ$_ORDERS_QUEUETABLE_S
Number of failures: 0
Table list: IX.AQ$_ORDERS_QUEUETABLE_L
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.


-- if there where no failures then end the upgrade.


SQL> VAR fail number
SQL> BEGIN
  2  DBMS_DST.END_UPGRADE(:fail);
  3  DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
  4  END;
  5  /
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.


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         11
DST_SECONDARY_TT_VERSION   0
DST_UPGRADE_STATE                  NONE


SQL> SELECT * FROM v$timezone_file;

FILENAME                 VERSION
--------------------          ----------
timezlrg_11.dat           11

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production



######################### Upgrade to 11gR2 is complete ##########################

I hope this article helped you.

Regards,
Amit Rath


2 comments:

  1. Greate pieces. Keep writing such kind of info on your blog.
    Im really impressed by your site.
    Hey there, You have performed an excellent job. I'll definitely digg it and for my part suggest to my friends.
    I am sure they will be benefited from this web site.

    My weblog ... Reed

    ReplyDelete
  2. very good article

    ReplyDelete