APPSINFODB – Information Technology Group

Finding Solutions for DBA's to smile…….!!!!!!! DBA its just not Administration, its core of customer’s data integrity

  • Blog Stats

    • 37,356 hits
  • Visitors

    free counters
  • Vistors Access

  • Calendar

    August 2012
    M T W T F S S
    « Jun   Nov »
     12345
    6789101112
    13141516171819
    20212223242526
    2728293031  
  • Archives

  • Authors

Oracle database upgrade from 10g to 11g

Posted by appsinfo on August 20, 2012

Install Oracle 11g database software in different ORACLE_HOME from source Database

SOURCE

$ pwd
/u01/app/oracle/product/11.1/rdbms/admin
$ ls -lt utlu111i.sql
-rw-r–r– 1 oracle oinstall 138636 2009-07-20 09:01 utlu111i.sql
$ pwd
/u01/app/oracle/product/11.1/rdbms/admin
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> spool utlu111_20_July_2009.lst

SQL> @utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool 04-27-2009 09:29:25
.
**********************************************************************
Database:
**********************************************************************
–> name:dev
–> version: 10.2.0.4.0
–> compatible: 10.2.0.3.0
–> blocksize: 8192
–> platform: Linux x86 64-bit
–> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 727 MB
…. AUTOEXTEND additional space required: 247 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 473 MB
…. AUTOEXTEND additional space required: 443 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 425 MB
…. AUTOEXTEND additional space required: 175 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 61 MB
…. AUTOEXTEND additional space required: 41 MB
–> EXAMPLE tablespace is adequate for the upgrade.
…. minimum required size: 69 MB
.**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
WARNING: –> “sga_target” needs to be increased to at least 672 MB
.**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
— No renamed parameters found. No changes are required.
.**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
–> “background_dump_dest” replaced by “diagnostic_dest”
–> “user_dump_dest” replaced by “diagnostic_dest”
–> “core_dump_dest” replaced by “diagnostic_dest”
.**********************************************************************
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
–> Data Mining [upgrade] VALID
–> Expression Filter [upgrade] VALID
–> Rule Manager [upgrade] VALID
–> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS
…. OLAPSYS
…. SYSMAN
…. CTXSYS
…. XDB
…. MDSYS
WARNING: –> Database contains INVALID objects prior to upgrade.
…. USER PUBLIC has 1 INVALID objects.
…. USER SYS has 2 INVALID objects.
WARNING: –> Database contains schemas with objects dependent on network
packages.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
WARNING: –> EM Database Control Repository exists in the database.
…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.

PL/SQL procedure successfully completed.

SQL> select * from v$timezone_file;
Note:
If time zone file version is less than 4 then apply time zone patch 5632264 manually

SQL> select object_name, owner, object_type from all_objects where status like ‘INVALID’;

SQL> select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS
————————————————– —————————— ——————————————–
Oracle Database Catalog Views                              10.2.0.4.0                VALID
Oracle Database Packages and Types                     10.2.0.4.0                VALID
Oracle Workspace Manager                                    10.2.0.4.3                VALID
JServer JAVA Virtual Machine                              10.2.0.4.0                VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages                               10.2.0.4.0                VALID
Oracle Expression Filter                                          10.2.0.4.0                VALID
Oracle Data Mining                                                 10.2.0.4.0                VALID
Oracle Text                                                              10.2.0.4.0                VALID
Oracle XML Database                                            10.2.0.4.0                VALID
Oracle Rules Manager                                             10.2.0.4.0                VALID
Oracle interMedia                                                    10.2.0.4.0                VALID
OLAP Analytic Workspace                                    10.2.0.4.0                VALID
Oracle OLAP API                                                  10.2.0.4.0                VALID
OLAP Catalog                                                        10.2.0.4.0                VALID
Spatial                                                                     10.2.0.4.0                VALID
Oracle Enterprise Manager                                      10.2.0.4.0                VALID

17 rows selected.

If you database using spfile, create pfile

SQL> create pfile from spfile ;
pfile will be created in 10g $ORACLE_HOME/dbs/init[SID].ora

a) Remove *.background_dump_dest, *.core_dump_dest, *.user_dump_dest and add
*.diagnostic_dest=’/11g_base’ (11g Base Directory)
b) Change
*.compatible=’10.2.0.1.0′
to
*.compatible=’11.1.0′
dev.__db_cache_size=377487360
dev.__java_pool_size=4194304
dev.__large_pool_size=4194304
dev.__shared_pool_size=142606336
dev.__streams_pool_size=0
*.control_files=’/u01/app/oracle/datafile/dev/control01.ctl’,’/u01/app/oracle/datafile/dev/control02.ctl’,’/u01/app/oracle/datafile/dev/control03.ctl’
*.db_block_size=8192
*.db_domain=’apt-amd-02′
*.db_file_multiblock_read_count=16
*.db_name=’dev’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=devXDB)’
*.job_queue_processes=10
*.nls_language=’AMERICAN’
*.nls_territory=’AMERICA’
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=536870912
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.compatible=’11.1.0′
*.diagnostic_dest=’/u01/app/oracle/admin/dev/diagnostic’

$ mkdir -p /u01/app/oracle/admin/dev/diagnostic
$ cd /u01/app/oracle/admin/dev/
$ ls -lrt

total 8
drwxr-xr-x 2 oracle oinstall 6 2009-04-27 09:43 diagnostic
drwxr-x— 2 oracle oinstall 4096 2009-04-27 09:29 adump
drwxr-x— 2 oracle oinstall 4096 2009-04-27 09:20 udump
drwxr-x— 2 oracle oinstall 141 2009-04-27 09:20 bdump
drwxr-x— 2 oracle oinstall 35 2009-04-26 13:26 pfile
drwxr-x— 2 oracle oinstall 6 2009-04-26 13:20 cdump
drwxr-x— 2 oracle oinstall 6 2009-04-26 13:20 dpdump

Step 2: Upgrade Database

1. ***Shut down source database (10g) – Your downtime starts here ***

SQL> connect sys/sys as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2. Set your environment variables to Oracle Database 11g Release 1 (11.1):

export ORACLE_HOME=/u01/oracle/11gbase/11.1.0
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.1
export ORACLE_SID=dev
export PATH=$ORACLE_HOME/bin:$PATH:.
export LIBXCB_ALLOW_SLOPPY_LOCK=1
export TZ=GMT

3. Start Upgrade
$pwd
/home/oracle
$sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 – Production on Tue Apr 27 09:50:25 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup upgrade pfile=’/tmp/initdev.ora’;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.

Note: Check shared_pool & java_pool size, to set new values

SQL> spool upgrade_20July2009.log
SQL> @?/rdbms/admin/catupgrd.sql

Note: After upgrade <<catupgrd.sql>> the database will shutdown.

SQL> startup pfile=’/tmp/initdev.ora’;
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.

SQL> spool on utlu111s_20July2009.sql

SQL> @?/rdbms/admin/utlu111s.sql

Oracle Database 11.1 Post-Upgrade Status Tool 04-27-2009 12:11:48
Component Status Version HH:MM:SS
Oracle Server
. VALID 11.1.0.6.0 00:42:49
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:20:34
Oracle Workspace Manager
. VALID 10.2.0.4.3 00:00:01
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:55
OLAP Catalog
. VALID 11.1.0.6.0 00:01:40
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:26
Oracle Enterprise Manager
. ORA-06550: line 5, column 35:
. PL/SQL: ORA-00942: table or view does not exist
. ORA-06550: line 5, column 1:
. PL/SQL: SQL Statement ignored
. ORA-00001: unique constraint (SYSMAN.PARAMETERS_PRIMARY_KEY) violated
. ORA-06512: at “SYSMAN.MGMT_TIME_SYNC”, line 108
. ORA-06512: at “SYSMAN.MGMT_TIME_SYNC”, line 166
. ORA-06512: at line 2
. VALID 11.1.0.6.0 00:20:30
Oracle XDK
. VALID 11.1.0.6.0 00:01:37
Oracle Text
. VALID 11.1.0.6.0 00:01:41
Oracle XML Database
. VALID 11.1.0.6.0 00:13:43
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:42
Oracle Multimedia
. VALID 11.1.0.6.0 00:09:04
Spatial
. VALID 11.1.0.6.0 00:06:41
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:15
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:13
Gathering Statistics
. 00:05:25
Total Upgrade Time: 02:06:28

PL/SQL procedure successfully completed.

SQL>spool off
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON

The above errors does not result in any data loss. Therefore, you can ignore these errors, however you can apply Patch 7449757

$cd /u02/patch
$ unzip p7449757_111070_Generic.zip
$cd 7449757
$pwd

/home/oracle/7449757
$ /u01/app/oracle/product/11.1/OPatch/opatch apply
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> create spfile from pfile=’/tmp/initdev.ora’;

SQL> select count(*) from dba_objects where status like ‘INVALID’;
SQL>select comp_name,version, status from dba_registry;
SQL> select comp_name,version, status from dba_registry

COMP_NAME                                                    VERSION                             STATUS
—————————————-                           —————————— —————
Oracle Enterprise Manager                                11.1.0.6.0                              VALID
OLAP Catalog                                                      11.1.0.6.0                              VALID
Spatial                                                                   11.1.0.6.0                              VALID
Oracle Multimedia                                              11.1.0.6.0                              VALID
Oracle XML Database                                       11.1.0.6.0                              VALID
Oracle Text                                                           11.1.0.6.0                              VALID
Oracle Data Mining                                             11.1.0.6.0                              VALID
Oracle Expression Filter                                      11.1.0.6.0                              VALID
Oracle Rules Manager                                        11.1.0.6.0                              VALID
Oracle Workspace Manager                              10.2.0.4.3                              VALID
Oracle Database Catalog Views                       11.1.0.6.0                              VALID
Oracle Database Packages and Types            11.1.0.6.0                              VALID
JServer JAVA Virtual Machine                         11.1.0.6.0                              VALID
Oracle XDK                                                          11.1.0.6.0                              VALID
Oracle Database Java Packages                      11.1.0.6.0                              VALID
OLAP Analytic Workspace                               11.1.0.6.0                              VALID
Oracle OLAP API                                                11.1.0.6.0                              VALID

17 rows selected.

Configure the tnsnames.ora, listener.ora sqlnet.ora. this finish your upgrade

Advertisements

2 Responses to “Oracle database upgrade from 10g to 11g”

  1. sreedhar said

    I usually follow below steps (at glance):

    1. Install Oracle 11g database software in different ORACLE_HOME from source Database
    2. Run pre upgrade check file from 11g home connecting to 10g database (spool the log file).
    SQL> @utlu111i.sql
    3. Check for warnings in above log and resolve.
    4. If there is any warning related to TIME ZONE, then run select * from v$timezone_file; If time zone file version is less than 4 then apply
    time zone patch manually.
    5. If database using spfile, then create pfile and copy pfile to 11g DBS location.
    6. Remove all deprecated parameters. (Like core_dump_dest, etc..).
    7. Create required diagnostic directory under 11g ORACLE_BASE (optional step).
    8. Shutdown 10g database.
    9. Start DB from 11g home

    export ORACLE_HOME/app/oracle/product/11.2.0/db_1
    export PATH=$ORACLE_HOME/bin:$PATH
    export ORACLE_SID=AREPDB
    export TNS_ADMIN=$ORACLE_HOME/network/admin

    SQL>startup upgrade

    10. After upgrade DB will shutdown
    11. Restart DB using 11g pfile. (Just startup)
    12. Run post-upgrade status files (utlu111s.sql, catuppst.sql & utlrp.sql)
    13. Finally create spfile from pfile;
    14. Check the upgrade status using below queries.

    SQL> select count(*) from dba_objects where status like ‘INVALID’;
    SQL> select comp_name,version, status from dba_registry;
    SQL> select comp_name,version, status from dba_registry

    We can skip step-9 to step-12 if we are running upgrade using DBUA at step-9.

    • appsinfo said

      Hi Sreedhar,

      I agree with you, however these steps are for the manual upgrade and environment specific.

      Thanks for your comments and if you wish to join and help contribute will be much appreciated.

      Thanks

      info.appsdb@gmail.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: