10g to 11g

Upgrade Oracle 10g Release 2 from 10201 to 10204

Posted by Zahid on July 3, 2010.

This post demonstrate a step by step guide to apply oracle patchset 10.2.0.4 (patch number 6810189) on 10.2.0.1 database. My current environment is Oracle 10gR2 (10.2.0.1) installed on Redhat Enterprise Linux 5 update 4 32-bit.

Preparing for the upgrade to 10.2.0.4

Stop all oracle components running like LISTENER, EM , ISQLPLUS and DB itself etc.

/* Stop the isqlplus if running */$ isqlplusctl stopiSQL*Plus 10.2.0.1.0Copyright (c) 2003, 2005, Oracle. All rights reserved.Stopping iSQL*Plus ...iSQL*Plus stopped.

/* Stop the EM dbconsole */

$ emctl stop dbconsoleTZ set to US/EasternOracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.http://ora10.home.com:1158/em/console/aboutApplicationStopping Oracle Enterprise Manager 10g Database Control ... ... Stopped.

/* Stop the listener */

$ lsnrctl stop

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))The command completed successfully

/* Shutdown the database itself */

$ sqlplus / as sysdba

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL>

Take a backup of Oracle Home and Database

Once the oracle database is shutdown, take a cold backup of your database and a backup of your ORACLE_HOME.

$cd /u01/apps/oracle/oradata/

http://www.oracleflash.com/about.html

/* All my data files , control files and log files are in a directory ora10g at the location /u01/apps/oracle/oradata/. I am going to make a tar archive of ora10g directory. If these files are at separate locations then add all those locations into the tar archive. And since this is just a test database and is very small in size so tar archive works much better then every thing else. But if it would be a production db and is big in size then I would consider other faster ways to take a cold backup of my data files.*/

$ tar czf /home/oracle/ora10g.tar.gz ora10g

$ echo $ORACLE_HOME/u01/apps/oracle/product/10.2.0/db_1

/* This is my ORACLE_HOME so I would make a tar archive of "db_1" directory.*/

$ cd /u01/apps/oracle/product/10.2.0/$ tar czf /home/oracle/oraHomeBackup.tar.gz db_1

Manage your data with TimeZone before upgrade

(Only perform this step if you have data or Scheduler jobs with TZ info)

From 9i onwards Oracle has 2 datatypes that may have data stored affected by a update of the RDBMS DST (Daylight Saving Time) definitions, those are TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) and TIMESTAMP WITH TIME ZONE (TSTZ). If you have TZ data stored in your database you need to go through the following steps to ensure the integrity of your data while the database upgrade.

Check which TIMEZONE version file you are currently using.

SQL> select version from v$timezone_file;

VERSION---------- 2

If this gives 4 then you may simply proceed with the upgrade even if you have TZ data.If this gives higher then 4, look at the meta link note: Note 553812.1 If this gives lower then 4, perform the following steps:

Download utltzpv4.sql and run it.

SQL> @utltzpv4.sql DROP TABLE sys.sys_tzuv2_temptab CASCADE CONSTRAINTS *ERROR at line 1:

https://support.oracle.com/CSP/main/article?cmd=show&type=ATT&id=1086400.1:UTLTZPV4_13

http://home.c2i.net/toreingolf/oracle/note.htm?id=553812.1

ORA-00942: table or view does not exist

Table created.

DROP TABLE sys.sys_tzuv2_affected_regions CASCADE CONSTRAINTS *ERROR at line 1:ORA-00942: table or view does not exist

Table created.

Your current timezone version is 2!.Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONEdata is affected by version 4 transition rules..Any table with YES in the nested_tab column (last column) needsa manual check as these are nested tables.

PL/SQL procedure successfully completed.

Commit complete.

SQL>

/* Once the script finishes successfully execute the following query */

column table_owner format a4column column_name format a18select * from sys_tzuv2_temptab;

TABL TABLE_NAME COLUMN_NAME ROWCOUNT NES---- ------------------------------ ------------------ ---------- ---SYS SCHEDULER$_JOB LAST_ENABLED_TIME 3SYS SCHEDULER$_JOB LAST_END_DATE 1SYS SCHEDULER$_JOB LAST_START_DATE 1SYS SCHEDULER$_JOB NEXT_RUN_DATE 1SYS SCHEDULER$_JOB START_DATE 1SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE 1SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE 1SYS SCHEDULER$_WINDOW LAST_START_DATE 2SYS SCHEDULER$_WINDOW NEXT_START_DATE 2

9 rows selected.

If it returns no rows, there is nothing that needs to be done. Just proceed with the upgrade.If it retunrs the detail of columns that contain TZ data which may be affected by the upgrade, see metalink note: Note 553812.1

The Note 553812.1 states that if you see SYS owned SCHEDULER objects then it is safe to ignore them and proceed with the upgrade. But if you see user data or user created jobs here then you need to take a

http://home.c2i.net/toreingolf/oracle/note.htm?id=553812.1

backup of data before upgrade and restore it back after the upgrade. Remove any user created jobs and re-create them after the upgrade.

Download Oracle Patchset 10.2.0.4 (6810189)

Now is the time to prepare the oracle 10.2.0.4 patchset installer.

You can download the patchset from

For Linux x86 (32-bit):

10.2.0.4 for Linux x86

For Linux x86-64 (64-bit):

10.2.0.4 for Linux x86-64

Once downloaded login as root in another console and execute following:

# xhost +SI:localuser:oracle

Install the patchset 10.2.0.4

Now come back to the oracle user console and move to the directory where you downloaded the patch and unzip the file.

$ cd /home/oracle$ unzip p6810189_10204_Linux-x86.zip$ cd Disk1/$ ./runInstaller

The first screen is welcome screen.

https://updates.oracle.com/Orion/PatchDetails/handle_plat_lang_change?plat_lang=46P&patch_file=&file_id=&password_required=&password_required_readme=&merged_trans=&aru=9961232&patch_num=6810189&patch_num_id=939957&default_release=80102040&default_plat_lang=226P&default_compatible_with=&patch_password=&orderby=&direction=&no_header=0

https://updates.oracle.com/Orion/PatchDetails/handle_plat_lang_change?plat_lang=912P&patch_file=&file_id=&password_required=&password_required_readme=&merged_trans=&aru=10032632&patch_num=6810189&patch_num_id=939957&default_release=80102040&default_plat_lang=46P&default_compatible_with=&patch_password=&orderby=&direction=&no_header=0

Provide the Oracle home details here (The oracle 10.2.0.1 home).

The installer will perform prerequisite checks on this screen. Make sure you see the message "The overall result of this check is passed" in the output.

Oracle configuration Manager allows you to associate your configuration with your metalink support account. You may skip this.

Installation Summary.

Installation progress.

Once progress shows 100%, you will be asked to perform some root specific actions.

Login as root

# which dbhome/usr/local/bin/dbhome/* this shows the location of dbhome, oraenv and coraenv files rename them for 10.2.0.1 as the root.sh create new ones for 10.2.0.4*/# cd /usr/local/bin/# mv dbhome dbhome_10201# mv oraenv oraenv_10201# mv coraenv coraenv_10201

/* Now execute the script suggested by the installer. */

# /u01/apps/oracle/product/10.2.0/db_1/root.shRunning Oracle10 root.sh script...

The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /u01/apps/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]: Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed byDatabase Configuration Assistant when a database is created

Finished running generic part of root.sh script.Now product-specific root actions will be performed.

When root.sh finishes successfully come back to installer and press ok. Then you should see the End of Installation page as below.

Press exit and your ORACLE_HOME is patched with 10.2.0.4 patchset. All your db's working under this ORACLE_HOME will become unusable unless you upgrade your database to 10.2.04 as well.


Connected to an idle instance.

SQL> startupORACLE instance started.

Total System Global Area 608174080 bytesFixed Size 1268896 bytesVariable Size 171967328 bytesDatabase Buffers 427819008 bytesRedo Buffers 7118848 bytes

Database mounted.ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> exit

As you can see the database is unable to open and throwing the error ORA-01092. Lets look at the alert log file to know what actually happened.

$ tail -f /u01/apps/oracle/admin/ora10g/bdump/alert_ora10g.log SMON: enabling cache recoveryFri Jul 2 15:30:15 2010Errors in file /u01/apps/oracle/admin/ora10g/udump/ora10g_ora_12856.trc:ORA-00704: bootstrap process failureORA-39700: database must be opened with UPGRADE optionFri Jul 2 15:30:15 2010Error 704 happened during db open, shutting down databaseUSER: terminating instance due to error 704Instance terminated by USER, pid = 12856ORA-1092 signalled during: ALTER DATABASE OPEN...$

The alert log states that the database has to be upgraded first using UPGRADE option to be able to OPEN normally.

Upgrade the database from 10.2.0.1 to 10.2.0.4

Now startup the database with upgrade option and run the pre-upgrade information tool to see if the database is okay for the upgrade and if there is some thing to be changed before starting the upgrade.

$ cd $ORACLE_HOME/rdbms/admin$ sqlplus / as sysdba

SQL> startup upgrade

SQL> spool pre_upgrade.log

SQL> @utlu102i.sqlOracle Database 10.2 Upgrade Information Utility 07-02-2010 17:39:25.**********************************************************************Database:**********************************************************************--> name: ORA10G--> version: 10.2.0.1.0--> compatible: 10.2.0.1.0--> blocksize: 8192.**********************************************************************Tablespaces: [make adjustments in the current environment]**********************************************************************--> SYSTEM tablespace is adequate for the upgrade..... minimum required size: 488 MB.... AUTOEXTEND additional space required: 8 MB--> UNDOTBS1 tablespace is adequate for the upgrade..... minimum required size: 400 MB

.... AUTOEXTEND additional space required: 370 MB--> SYSAUX tablespace is adequate for the upgrade..... minimum required size: 245 MB.... AUTOEXTEND additional space required: 15 MB--> TEMP tablespace is adequate for the upgrade..... minimum required size: 58 MB.... AUTOEXTEND additional space required: 38 MB--> EXAMPLE tablespace is adequate for the upgrade..... minimum required size: 69 MB.**********************************************************************Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]**********************************************************************-- No update parameter changes are required..**********************************************************************Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]**********************************************************************-- No renamed parameters found. No changes are required..**********************************************************************Obsolete/Deprecated Parameters: [Update Oracle Database 10.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 Java Packages [upgrade] VALID--> Oracle Text [upgrade] VALID--> Oracle XML Database [upgrade] VALID--> Oracle Workspace Manager [upgrade] VALID--> Oracle Data Mining [upgrade] VALID--> OLAP Analytic Workspace [upgrade] VALID--> OLAP Catalog [upgrade] VALID--> Oracle OLAP API [upgrade] VALID--> Oracle interMedia [upgrade] VALID--> Spatial [upgrade] VALID--> Expression Filter [upgrade] VALID--> EM Repository [upgrade] VALID--> Rule Manager [upgrade] VALID.


SQL> spool off

The output from utlu102i.sql shows that every thing is fine, no changes are required and the database is ready for upgrade.

Lets start the upgrade process.

SQL> spool upgrade.logSQL> @catupgrd.sql...[output trimmed]...Oracle Database 10.2 Upgrade Status Utility 07-02-2010 18:13:40.Component Status Version HH:MM:SSOracle Database Server VALID 10.2.0.4.0 00:09:32JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:03:34Oracle XDK VALID 10.2.0.4.0 00:00:29Oracle Database Java Packages VALID 10.2.0.4.0 00:00:39Oracle Text VALID 10.2.0.4.0 00:00:20Oracle XML Database VALID 10.2.0.4.0 00:01:29Oracle Workspace Manager VALID 10.2.0.4.3 00:00:39Oracle Data Mining VALID 10.2.0.4.0 00:00:18OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:19OLAP Catalog VALID 10.2.0.4.0 00:00:50Oracle OLAP API VALID 10.2.0.4.0 00:00:45Oracle interMedia VALID 10.2.0.4.0 00:03:48Spatial VALID 10.2.0.4.0 00:01:29Oracle Expression Filter VALID 10.2.0.4.0 00:00:09Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:16Oracle Rule Manager VALID 10.2.0.4.0 00:00:07.Total Upgrade Time: 00:25:52DOC>#######################################################################DOC>#######################################################################DOC>DOC> The above PL/SQL lists the SERVER components in the upgradedDOC> database, along with their current version and status.DOC>DOC> Please review the status and version columns and look forDOC> any errors in the spool log file. If there are errors in the spoolDOC> file, or any components are not VALID or not the current version,DOC> consult the Oracle Database Upgrade Guide for troubleshootingDOC> recommendations.DOC>DOC> Next shutdown immediate, restart for normal operation, and thenDOC> run utlrp.sql to recompile any invalid application objects.DOC>DOC>#######################################################################DOC>#######################################################################DOC>#SQL> spool offSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL>

When the upgrade script catupgrd.sql finishes shutdown the database and open the spool of the upgrade process and try to find out if any thing failed. If you see something failed try to fix it and re-run the upgrade process.

The upgrade process may leave many objects invalid in the database. Perform a normal startup and run the utlrp.sql script to recompile any invalid objects.



SQL> startup

SQL> spool recompile.log

SQL> @utlrp.sql

TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_BGN 2010-07-02 18:23:16

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalidDOC> objects in the database. Recompilation time is proportional to theDOC> number of invalid objects in the database, so this command may takeDOC> a long time to execute on a database with a large number of invalidDOC> objects.DOC>DOC> Use the following queries to track recompilation progress:DOC>DOC> 1. Query returning the number of invalid objects remaining. ThisDOC> 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 numberDOC> should increase with time.DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;DOC>DOC> This script automatically chooses serial or parallel recompilationDOC> based on the number of CPUs available (parameter cpu_count) multipliedDOC> 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 parallelDOC> recompilation. Jobs are created without instance affinity so that theyDOC> can migrate across RAC nodes. Use the following queries to verifyDOC> whether UTL_RECOMP jobs are being created and run correctly:DOC>DOC> 1. Query showing jobs created by UTL_RECOMPDOC> SELECT job_name FROM dba_scheduler_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>DOC> 2. Query showing UTL_RECOMP jobs that are runningDOC> SELECT job_name FROM dba_scheduler_running_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>#


TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_END 2010-07-02 18:23:50


DOC> The following query reports the number of objects that have compiledDOC> with errors (objects that compile with errors have status set to 3 inDOC> obj$). If the number is higher than expected, please examine the errorDOC> messages reported with each object (using SHOW ERRORS) to see if theyDOC> point to system misconfiguration or resource constraints that must beDOC> fixed before attempting to recompile these objects.DOC>#

OBJECTS WITH ERRORS------------------- 0

DOC> The following query reports the number of errors caught duringDOC> recompilation. If this number is non-zero, please query the errorDOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errorsDOC> are due to misconfiguration or resource constraints that must beDOC> fixed before objects can compile successfully.DOC>#

ERRORS DURING RECOMPILATION--------------------------- 0


SQL> spool off

/* When the script utlrp.sql completes go ahead and verify if all the components are upgraded to 10.2.0.4*/set lines 10000set pages 1000column comp_name format a40column version format a12column status format a6select comp_name, version, status from sys.dba_registry;

COMP_NAME VERSION STATUS---------------------------------------- ------------ ------Oracle Database Catalog Views 10.2.0.4.0 VALIDOracle Database Packages and Types 10.2.0.4.0 VALIDOracle Workspace Manager 10.2.0.4.3 VALID

JServer JAVA Virtual Machine 10.2.0.4.0 VALIDOracle XDK 10.2.0.4.0 VALIDOracle Database Java Packages 10.2.0.4.0 VALIDOracle Expression Filter 10.2.0.4.0 VALIDOracle Data Mining 10.2.0.4.0 VALIDOracle Text 10.2.0.4.0 VALIDOracle XML Database 10.2.0.4.0 VALIDOracle Rule Manager 10.2.0.4.0 VALIDOracle interMedia 10.2.0.4.0 VALIDOLAP Analytic Workspace 10.2.0.4.0 VALIDOracle OLAP API 10.2.0.4.0 VALIDOLAP Catalog 10.2.0.4.0 VALIDSpatial 10.2.0.4.0 VALIDOracle Enterprise Manager 10.2.0.4.0 VALID

17 rows selected.

The above query shows that the database components are at 10.2.0.4 version now.

Restore the database back to 10.2.0.1 if any thing failed

Let's suppse the upgrade process fails, then you can always go back to 10.2.0.1 level be restoring the backup we took at the beginning of the process and start over the upgrade.

Just shutdown the database and restore the backups as follows:

$ echo $ORACLE_HOME/u01/apps/oracle/product/10.2.0/db_1

$ cd /u01/apps/oracle/product/10.2.0/$ rm -fr db_1$ tar xzf /home/oracle/oraHomeBackup.tar.gz *

/* This will restore the old ORACLE_HOME */

$ cd /u01/apps/oracle/oradata/$ rm -fr ora10g$ tar xzf /home/oracle/ora10g.tar.gz *

/* This will restore a consistent copy of datafiles, controlfiles and redo log files */

Now start the database and see which version is it?



SQL> startup

set lines 10000set pages 1000column comp_name format a40column version format a12column status format a6

select comp_name, version, status from sys.dba_registry;

COMP_NAME VERSION STATUS---------------------------------------- ------------ ------Oracle Database Catalog Views 10.2.0.1.0 VALIDOracle Database Packages and Types 10.2.0.1.0 VALIDOracle Workspace Manager 10.2.0.1.0 VALIDJServer JAVA Virtual Machine 10.2.0.1.0 VALIDOracle XDK 10.2.0.1.0 VALIDOracle Database Java Packages 10.2.0.1.0 VALIDOracle Expression Filter 10.2.0.1.0 VALIDOracle Data Mining 10.2.0.1.0 VALIDOracle Text 10.2.0.1.0 VALIDOracle XML Database 10.2.0.1.0 VALIDOracle Rules Manager 10.2.0.1.0 VALIDOracle interMedia 10.2.0.1.0 VALIDOLAP Analytic Workspace 10.2.0.1.0 VALIDOracle OLAP API 10.2.0.1.0 VALIDOLAP Catalog 10.2.0.1.0 VALIDSpatial 10.2.0.1.0 VALIDOracle Enterprise Manager 10.2.0.1.0 VALID

17 rows selected.

We are back again from where we started.

Upgrade Oracle 10g from 10.2.0.1 to 10.2.0.4 (Single Instance)

Before database upgrade it is recommanded to backup the PRODUCTION database.

1. Stop all services of oracle

[oracle@ittestdb ~]$ echo $ORACLE_BASE

/u01/app/oracle

[oracle@ittestdb ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/10.2.0/db_1

[oracle@ittestdb ~]$ echo $ORACLE_SID

orcl

[oracle@ittestdb ~]$

[oracle@ittestdb ~]$ emctl stop dbconsole

TZ set to Asia/Baghdad

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

http://ittestdb.amardhaka.com:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Database Control …

… Stopped.

[oracle@ittestdb ~]$ isqlplusctl stop

iSQL*Plus 10.2.0.1.0


Stopping iSQL*Plus …

iSQL*Plus stopped.


[oracle@ittestdb ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 08-FEB-2010 13:17:18


http://ittestdb.asiacell.com:1158/em/console/aboutApplication

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

The command completed successfully

[oracle@ittestdb ~]$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Feb 8 13:17:29 2010


Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 –

Production

With the Partitioning, OLAP and Data Mining options

[oracle@ittestdb ~]$ ps -ef|grep oracle

root 13754 32094 0 13:13 pts/1 00:00:00 su – oracle

oracle 13755 13754 0 13:13 pts/1 00:00:00 -bash

oracle 14525 13755 0 13:18 pts/1 00:00:00 ps -ef

oracle 14526 13755 0 13:18 pts/1 00:00:00 grep oracle


Step 2: Install the Database Patch Set

[oracle@ittestdb ~]$ export DISPLAY=10.13.5.95:0.0

[oracle@ittestdb ~]$ /u01/stage/patch/Disk1/runInstaller

Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-

4, redhat-5, UnitedLinux-1.0, asianux-1, asianux-2 or asianux-3

Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-02-

08_01-22-19PM. Please wait …[oracle@ittestdb ~]$ Oracle Universal Installer,

Version 10.2.0.4.0 Production

Copyright (C) 1999, 2008, Oracle. All rights reserved.


Step 3 : Upgrade Database

[oracle@ittestdb ~]$ ps -ef|grep oracle

root 13754 32094 0 13:13 pts/1 00:00:00 su – oracle

oracle 13755 13754 0 13:13 pts/1 00:00:00 -bash

oracle 18304 13755 0 13:28 pts/1 00:00:00 ps -ef

oracle 18305 13755 0 13:28 pts/1 00:00:00 grep oracle

[oracle@ittestdb ~]$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Feb 8 13:28:53 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


SQL> STARTUP UPGRADE

ORACLE instance started.

Total System Global Area 1224736768 bytes

Fixed Size 1267188 bytes

Variable Size 318769676 bytes

Database Buffers 889192448 bytes

Redo Buffers 15507456 bytes

Database mounted.

Database opened.

SQL>








Database mounted.

Database opened.

SQL> SPOOL /u01/stage/patch/Disk1/upgrade_info.log

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

Oracle Database 10.2 Upgrade Information Utility 02-08-2010 13:30:50

.

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

Database:

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

–> name: ORCL

–> version: 10.2.0.1.0

–> compatible: 10.2.0.1.0

–> blocksize: 8192

.

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

Tablespaces: [make adjustments in the current environment]

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

–> SYSTEM tablespace is adequate for the upgrade.

…. minimum required size: 488 MB

…. AUTOEXTEND additional space required: 8 MB

–> UNDOTBS1 tablespace is adequate for the upgrade.



–> SYSAUX tablespace is adequate for the upgrade.



–> TEMP tablespace is adequate for the upgrade.



–> EXAMPLE tablespace is adequate for the upgrade.


.

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

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

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

– No update parameter changes are required.

.

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

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

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

– No renamed parameters found. No changes are required.

.

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

Obsolete/Deprecated Parameters: [Update Oracle Database 10.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 Java Packages [upgrade] VALID

–> Oracle Text [upgrade] VALID

–> Oracle XML Database [upgrade] VALID

–> Oracle Workspace Manager [upgrade] VALID

–> Oracle Data Mining [upgrade] VALID

–> OLAP Analytic Workspace [upgrade] VALID

–> OLAP Catalog [upgrade] VALID

–> Oracle OLAP API [upgrade] VALID

–> Oracle interMedia [upgrade] VALID

–> Spatial [upgrade] VALID

–> Expression Filter [upgrade] VALID

–> EM Repository [upgrade] VALID

–> Rule Manager [upgrade] VALID

.


SQL> SPOOL OFF;

SQL> SPOOL /u01/stage/patch/Disk1/patch.log

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

……………………………………………

…………………………..

177 PROCEDURE selectTablespace( tsname IN varchar2 );

178

179 — This procedure informs this package that the caller intends to do

180 — point-in-time recovery on the specified tablespace. This procedure

must

181 — be called once for each tablespace in the recovery set.

182 — It alter selected tablespace read only, also checks datafiles in the

183 — selected tablespace.

184 –

185 — Input parameters:

186 – tsname

187 – The tablespace name.

188 –

189 — Exceptions:

190 – WRONG_ORDER (ORA-29301)

191 – wrong dbms_pitr package functions/procedure order.

192 – WRONG_TSNAME (ORA-29304)

193 – select tablespace does not exist

194 – NOT_READ_ONLY (ORA-29305)

195 – cannot alter the tablespace read only

196 – FILE_OFFLINE (ORA-29306)

197 – datafile is not online

SQL>

SQL>

SQL>

SQL> SPOOL OFF

SQL> SHUTDOWN IMMEDIATE

Database closed.



SQL> STARTUP







Database mounted.

Database opened.

SQL>

To compile invalid objects

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

……………………………..

…………………………………

TIMESTAMP

——————————————————————————–

COMP_TIMESTAMP UTLRP_END 2010-02-08 15:02:23

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

SQL>

SQL> select comp_name, version, status from sys.dba_registry;

COMP_NAME VERSION ST

ATUS

——————————————— —————————— ———–

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 Rule


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 VA

LID

Oracle Enterprise


SQL>exit

Now Start other services (listener, EM, iSQLPlus…)

Upgrade Oracle from 10.2.0.1 To 10.2.0.4 1. Patch Set Overview

Patch set release 10.2.0.4. Before installing this patch set you must be need 10.2.0.1 version.

2. Requirements

Oracle DB : Oracle 10.2.0.1 (later) Operating System: Windows

3. Pre - Installation Tasks

1. Identify prior installation

Before installing this patch you must install oracle 10.2.0.1 (or later version)

2. Download Patch set

Download 6810189 patch set installation archive to a directory that is not the Oracle home

directory or under the Oracle home directory.

3. Shutdown oracle database.

C:\>set oracle_sid= GOLDLINK

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys@GOLDLINK as sysdba Enter password: Connected.

SQL> SHUTDOWN Database closed. Database dismounted.

https://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=6810189&release=80102040&plat_lang=912P&patch_num_id=939957&[email protected]&userid=ml-roysam&

http://babudba.blogspot.in/2008/09/upgrade-oracle-from-10201-to-10204.html


4. Stop all services

C:\>set oracle_sid=GoldLink

C:\>net stop OracleDBConsoleGOLDLINK The OracleDBConsoleGOLDLINK service is stopping................ The OracleDBConsoleGOLDLINK service was stopped successfully.

C:\>net stop OracleServiceGOLDLINK The OracleServiceGOLDLINK service is stopping. The OracleServiceGOLDLINK service was stopped successfully.

C:\>net stop OracleOraDB10g_Home1iSQL*Plus.. The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

C:\>net stop OracleOraDB10g_Home1TNSListener The OracleOraDb10g_home1TNSListener service is stopping. The OracleOraDb10g_home1TNSListener service was stopped successfully.

Note: while patch installation time If you face Distributed Transaction Coordinator still running. You should be stop this service

C:\>net stop msdtc The Distributed Transaction Coordinator service is stopping. The Distributed Transaction Coordinator service was stopped successfully.

4. Backup your database.

Oracle recommends that you create a backup of the Oracle 10g installation before you install the patch set.

5. Check Tablespace Sizes and Set Parameter Values

Review the following sections before upgrading a database.

8. Upgrade the Database

After you install the patch set, you must perform the following steps on every database

associated with the upgraded Oracle home:

1. Start all services

2. Connect sys user

C:\> sqlplus /NOLOG

SQL> CONNECT SYS/SYS_password AS SYSDBA

3. Enter the following SQL*Plus commands:


SQL> SPOOL patch.log

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql

SQL> SPOOL OFF

Review the patch.log file for errors and inspect the list of components that is displayed at

the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database.

If necessary, rerun the catupgrd.sql script after correcting any problems.

4. Restart the database:

SQL> SHUTDOWN SQL> STARTUP

5. Compile Invalid Objects

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the

packages are accessed for the first time. This step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> select * from v$version;

BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.4.0 - Production PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production

TNS for 32-bit Windows: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production

step by step oracle database 10g upgrade from 10.2.0.1 to 10.2.0.4 April 18th, 2011 in Oracle Database 10G • posts • Redhat Enterprise Linux by adminRequirements:

Oracle database 10g patch set 10.2.0.4 download from

www.metalink.oracle.com

Step 1: Prerequisites check to before applying the patch set (6810189).

Check your dba_registry view oracle database components status is

valid or not, version is 10.2.0.1. Have a look at the below output

Column comp_name format a40

Column version format a12

Column status format a6

Select comp_name, version, status from sys.dba_registry;

COMP_NAME VERSION STATUS

———————————————– ———— ——

Oracle Database Catalog Views 10.2.0.1.0 VALID

Oracle Database Packages and Types 10.2.0.1.0 VALID

Oracle Workspace Manager 10.2.0.1.0 VALID

JServer JAVA Virtual Machine 10.2.0.1.0 VALID

Oracle XDK 10.2.0.1.0 VALID

http://www.dataqapps.com/author/admin/

http://www.dataqapps.com/category/redhat-enterprise-linux/

http://www.dataqapps.com/category/post/

http://www.dataqapps.com/category/oracle-database-10g/

http://www.dataqapps.com/step-by-step-oracle-database-10g-upgrade-from-10-2-0-1-to-10-2-0-4/

http://www.dataqapps.com/step-by-step-oracle-database-10g-upgrade-from-10-2-0-1-to-10-2-0-4/

Oracle Database Java Packages 10.2.0.1.0 VALID

Oracle Expression Filter 10.2.0.1.0 VALID

Oracle Data Mining 10.2.0.1.0 VALID

Oracle Text 10.2.0.1.0 VALID

Oracle XML Database 10.2.0.1.0 VALID

Oracle Rules Manager 10.2.0.1.0 VALID

Oracle interMedia 10.2.0.1.0 VALID

OLAP Analytic Workspace 10.2.0.1.0 VALID

Oracle OLAP API 10.2.0.1.0 VALID

OLAP Catalog 10.2.0.1.0 VALID

Spatial 10.2.0.1.0 VALID

Oracle Enterprise Manager 10.2.0.1.0 VALID

17 rows selected.

Check your v$version view


BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod

PL/SQL Release 10.2.0.1.0 – Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 – Production

NLSRTL Version 10.2.0.1.0 – Production

Check your database objects are valid or not

SQL> select object_name,status from dba_objects where status=’INVALID’;

No rows selected

In my environment there are no invalid objects. If you have any invalid objects then you have to really

Validate your database objects through the below scripts

SQL> exec utl_recomp.recomp_serial (); -> this is for all user’s objects across the database

Then stop all the process like sql, emctl, tnslistener and shutdown your database.



Database closed



$ isqlplusctl stop

iSQL*Plus 10.2.0.1.0


Stopping iSQL*Plus …

iSQL*Plus stopped.

$ emctl stop dbconsole

TZ set to US/Eastern

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved

http://ora10.home.com:1158/em/console/aboutApplication

Stopping Oracle Enterprise manager 10g database Control

….Stoped

$ lsnrctl stop


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

The command completed successfully

Step 2:

Cold Backup of your oracle database 10g and $ORACLE_HOME using Operating system

Command

Step 3:

Manage your data with Time Zone before upgrade

In my environment there are no data and scheduler jobs with Time Zone

information that’s why I skip the step. If you have huge volume of data and

scheduler jobs means please follow the below steps


Version

———-

2

If this gives 4 then you may simply proceed with the upgrade even if you have TZ data.

If this gives higher then 4 look at the Meta link note: Note 553812.1

If this gives lower then 4 perform the following steps:

Download utltzpv4.sql from www.metalink.oracle.com and run it.

SQL> @utltzpv4.sql

DROP TABLE sys.sys_tzuv2_temptab CASCADE CONSTRAINTS

ERROR at line 1:


Table created.

DROP TABLE sys.sys_tzuv2_affected_regions CASCADE CONSTRAINTS

ERROR at line 1:


Table created.

Your current timezone version is 2!

Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONE

data is affected by version 4 transition rules.

Any table with YES in the nested_tab column (last column) needs

a manual check as these are nested tables.


Commit complete.

SQL>

Once the script finishes successfully execute the following query

column table_owner format a4

column column_name format a18

select * from sys_tzuv2_temptab;

TABL TABLE_NAME COLUMN_NAME ROWCOUNT NES

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

SYS SCHEDULER$_JOB LAST_ENABLED_TIME 3

SYS SCHEDULER$_JOB LAST_END_DATE 1

SYS SCHEDULER$_JOB LAST_START_DATE 1

SYS SCHEDULER$_JOB NEXT_RUN_DATE 1

SYS SCHEDULER$_JOB START_DATE 1

SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE 1

SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE 1

SYS SCHEDULER$_WINDOW LAST_START_DATE 2

SYS SCHEDULER$_WINDOW NEXT_START_DATE 2

9 rows selected.

Step 3: Applying Oracle database 10g patch set (Patch number 6810189)

After downloaded the patch set, you have login as a root user and execute the following

# xhost +SI:localuser:oracle

Install the patch set 10.2.0.4

$ cd /home/oracle

$ unzip p6810189_10204_Linux-x86.zip

$ cd Disk1/

$ ./runInstaller

Click Next to proceed the next step

Here you should provide your $ORACLE_HOME(10.2.0.1) detail

Click Next

http://dataqapps.com/wp-content/uploads/2011/04/1.jpg



Click Next

Click Install




Click OK and run the above script as a root user

Then startup database with upgrade option otherwise the

below error will

come and you can see the error in the alert log file



SQL> startup










Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> exit

$ tail -f /opt/oracle/admin/test/bdump/alert_ora10g.log

SMON: enabling cache recovery

Fri Jul 2 15:30:15 2010

Errors in file /u01/apps/oracle/admin/ora10g/udump/ora10g_ora_12856.trc:

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Fri Jul 2 15:30:15 2010

Error 704 happened during db open, shutting down database

USER: terminating instance due to error 704

Instance terminated by USER, pid = 12856

ORA-1092 signalled during: ALTER DATABASE OPEN…

Now startup the database with upgrade option and run the pre-upgrade information tool to see if there are any

recommendation. If there is any recommendation then you have to really follow the recommendations.

Let’s start the upgrade process.

$ cd $ORACLE_HOME/rdbms/admin

SQL> spool upgrade.log

SQL> @catupgrd.sql

[output trimmed]

Oracle Database 10.2 Upgrade Status Utility 07-02-2010 18:13:40

Component Status Version HH:MM:SS

Oracle Database Server VALID 10.2.0.4.0 00:09:32

JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:03:34

Oracle XDK VALID 10.2.0.4.0 00:00:29

Oracle Database Java Packages VALID 10.2.0.4.0 00:00:39

Oracle Text VALID 10.2.0.4.0 00:00:20

Oracle XML Database VALID 10.2.0.4.0 00:01:29

Oracle Workspace Manager VALID 10.2.0.4.3 00:00:39

Oracle Data Mining VALID 10.2.0.4.0 00:00:18

OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:19

OLAP Catalog VALID 10.2.0.4.0 00:00:50

Oracle OLAP API VALID 10.2.0.4.0 00:00:45

Oracle interMedia VALID 10.2.0.4.0 00:03:48

Spatial VALID 10.2.0.4.0 00:01:29

Oracle Expression Filter VALID 10.2.0.4.0 00:00:09

Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:16

Oracle Rule Manager VALID 10.2.0.4.0 00:00:07

Total Upgrade Time: 00:25:52

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

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

DOC>

DOC> The above PL/SQL lists the SERVER components in the upgraded

DOC> database, along with their current version and status.

DOC>

DOC> Please review the status and version columns and look for

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

DOC> file, or any components are not VALID or not the current version,

DOC> consult the Oracle Database Upgrade Guide for troubleshooting

DOC> recommendations.

DOC>

DOC> Next shutdown immediate, restart for normal operation, and then

DOC> run utlrp.sql to recompile any invalid application objects.

DOC>

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

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

DOC>#

SQL> spool off


Database closed.



SQL>

When the upgrade script catupgrd.sql finishes shutdown the database and open the spool of the upgrade process

and try to find out if any thing failed. If you see something failed try to fix it and re-run the upgrade process.

The upgrade process may leave many objects invalid in the database. Perform a normal startup and run the utlrp.sql

script to recompile any invalid objects.



SQL> startup

SQL> spool recompile.log

SQL> @utlrp.sql

TIMESTAMP

==============

COMP_TIMESTAMP UTLRP_BGN 2010-07-02 18:23:116

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


TIMESTAMP

=============

COMP_TIMESTAMP UTLRP_END 2010-07-02 18:23:50


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 ERROR

===============

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


SQL> spool off

Step 5:

Post installation steps

set lines 10000

set pages 1000

column comp_name format a40

column version format a12

column status format a6

select comp_name, version, status from sys.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 Rule 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.


BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod

PL/SQL Release 10.2.0.4.0 – Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 – Production

NLSRTL Version 10.2.0.4.0 – Production

Check your database objects are valid or not

SQL> select object_name,status from dba_objects where status=’INVALID’;

No rows selected.

More 10.2.0.4 to 11.2.0.1 upgradesFiled under: 10g, 11g, Oracle — kkempf @ 9:38 am

Rate This

http://kkempf.wordpress.com/category/oracle/

http://kkempf.wordpress.com/category/11g/

http://kkempf.wordpress.com/category/10g/

With the end of Premier support for RDBMS 10.2 coming in July of next year

(Officially from Oracle here), I started doing some more 10g to 11g migrations. 6 upgrades later, I am

happy to say that it’s been successful across the board. In the interest of brevity, I’m going to skip all

the detailed checks outlined in 837570.1 and cut to the chase: Make sure you have a backup before

you start the upgrade. The rest of this assumes a vanilla database, meaning you don’t have any of the

issues outlined in 836570.1. Like you don’t have duplicate items in SYS and SYSTEM schemas, you are

smart enough to already have a SYSAUX tablespace, you understand what the new connect role is, you

don’t have TIMESTAMP WITH TIMEZONE Datatypes in the database, etc, etc, etc.

1. Install 11g in a new home

2. Run the 11g_home/rdbms/admin/utlu112i.sql script against your 10gR2 database and see what it says. In my case:

a. Archivelog formats now require %r in them

b. user/background/core dump destination parameters are depricated (I simply removed them and went with the new defaults)

c. minimum sga_target of 700M & minimum java_pool_size of 128M (I allowed this for the upgrade, then cut it back)

d. Empty the recycle bin: purge dba_recyclebin

e. exec dbms_stats.gather_schema_stats vs. SYS and SYSMAN schemas

3. Run 10g_home/rdbms/admin/utlrp to recompile invalids

4. Shutdown the 10g instance

5. Source the new Oracle Home so $ORACLE_HOME points to the 11.2 home

6. Copy the new/modified initSID.ora file to the 11g_home/dbs directory; copy the listener.ora and tnsnames.ora file from the 10g home to the 11g_home/network/admin directory

7. startup upgrade pfile=initSID.ora (11g version)

8. Run 11g_home/rdbms/admin/catupgrd.sql (catalog upgrade, this script takes quite a while to finish, about 1.5 hours on a wimpy 2 CPU VM)

9. Run 11g_home/rdbms/admin/utlu112s.sql (the doc incorrectly identifies this as utlu111s.sql)

10. Run 11g_home/rdbms/admin/utlrp.sql (recompile)

11. Check your compatible parameter (in my case I simply changed it from 10.2.0.2.0 to 11.2.0.1.0) and restart the database if required

12. Start your listener

Oracle Database 11.2 Post-Upgrade Status Tool 12-15-2009 09:34:18.Component Status Version HH:MM:SS.Oracle Server. VALID 11.2.0.1.0 00:31:18JServer JAVA Virtual Machine. VALID 11.2.0.1.0 00:11:33Oracle Workspace Manager. VALID 11.2.0.1.0 00:01:00OLAP Analytic Workspace. OPTION OFF 10.2.0.2.0 00:00:00OLAP Catalog. OPTION OFF 10.2.0.2.0 00:00:00

http://www.oracle.com/support/library/brochure/lifetime-support-technology.pdf

http://kkempf.files.wordpress.com/2009/12/11g.jpeg

Oracle OLAP API. OPTION OFF 10.2.0.2.0 00:00:00Oracle Enterprise Manager. VALID 11.2.0.1.0 00:12:34Oracle XDK. VALID 11.2.0.1.0 00:01:29Oracle Text. VALID 11.2.0.1.0 00:01:17Oracle XML Database. VALID 11.2.0.1.0 00:04:45Oracle Database Java Packages. VALID 11.2.0.1.0 00:00:33Oracle Multimedia. VALID 11.2.0.1.0 00:04:37Spatial. OPTION OFF 10.2.0.2.0 00:00:00Oracle Expression Filter. VALID 11.2.0.1.0 00:00:21Oracle Rule Manager. VALID 11.2.0.1.0 00:00:11Gathering Statistics. 00:15:12Total Upgrade Time: 01:24:57

Step By Step Manual Database Upgrade to 11.2.0.2

Compatibility Matrix

Minimum Version of the database that can be directly upgraded to Oracle 11g Release 2

Source Database Target Database 9.2.0.8 or higher 11.2.x10.1.0.5 or higher 11.2.x10.2.0.2 or higher 11.2.x11.1.0.6 or higher 11.2.x

The following database version will require an indirect upgrade path.

Source Database ---> Upgrade Path for Target Database--->Target Database7.3.3 (or lower)-----> 7.3.4 ---> 9.2.0.8 ---->11.2.x8.0.5 (or lower)----> 8.0.6 ---> 9.2.0.8 ---->11.2.x8.1.7 (or lower)----> 8.1.7.4---> 10.2.0.4---->11.2.x9.0.1.3 (or lower)----> 9.0.1.4-- ->10.2.0.4---->11.2.x9.2.0.7(or lower)---->9.2.0.8---->11.2.x

Here I am upgrading my Oracle 10.2.0.4 database to Oracle 11.2.0.2

1. Copy Pre upgrade Information gathering Script:

http://www.dbas-oracle.com/2011/06/step-by-step-manual-database-upgrade-to.html


Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.

Copy the Pre-Upgrade Information Tool (utlu112i.sql) and utltzuv2.sql from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the temporary directory on your system.

$ORACLE_HOME/rdbms/admin/utlu112i.sql

2. Execute Pre Upgrade Script:

Should be change to the directory where utlu112i.sql had been copied in the previous step.

Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the utlu112i.sql file. Please note that the database should be started using the Source Oracle Home.$ sqlplus '/ as sysdba'SQL> spool upgrade_info.logSQL> @utlu112i.sqlSQL> spool offSQL>

Check the spool file and examine the output of the upgrade information tool.

3. Check for the integrity of the source database:

Check for the integrity of the source database prior to starting the upgrade by downloading and running dbupgdiag.sql script from below My Oracle Support article

Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) (Avoid this step if don’t have support access)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.

$ cd $ORACLE_HOME/rdbms/admin$ sqlplus "/ as sysdba"SQL> @utlrp.sql

After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

4. Check that National Characterset:

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=556610.1





Check that National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.

select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

If this is UTF8 or AL16UTF16 then no action is needed.If is not UTF8 or AL16UTF16 then refer the following article

Note 276914.1 The National Character Set in Oracle 9i and 10g.

5. Optimizer Statistics:

When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection 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

$ sqlplus "/as sysdba"

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

6. Ensure that no files need media recovery:

Sql> SELECT * FROM v$recover_file;

7. Stop the listener for the database:

Make pfile from spfile;

$ lsnrctl stop

8. Suhtdown the Database:

Shutdown the database.



$ sqlplus "/as sysdba"SQL> shutdown immediate;

9. Back Up the Database:

1- Perform Cold Backup(or)2- Take a backup using RMAN

Connect to RMAN:

rman "target / nocatalog"

RUN{ALLOCATE CHANNEL chan_name TYPE DISK;BACKUP DATABASE FORMAT '%U' TAG before_upgrade;BACKUP CURRENT CONTROLFILE TO '';}

10 Backup and change pfile:

Make a backup of the init.ora file.Comment out obsolete parameters

* The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.

Note: Once the Parameter file is modified as per your requirement, copy the file to $ORACLE_HOME/dbs (11g Oracle Home )

11 Set Environment Variables:

If your operating system is UNIX then complete this step, else skip to next Step.

1. Make sure the following environment variables point to the Oracle 11g Release directories:


- ORACLE_BASE- ORACLE_HOME- PATH

$ export ORACLE_HOME=<location of oracle 11.2>$ export PATH=$ORACLE_HOME/bin:$PATH$ export ORACLE_BASE=<oracle_base set during installation></oracle_base set during installation></location of oracle 11.2>

Note : If ORACLE_BASE is not known, after setting Path towards 11g Oracle Home, execute 'orabase', which will point the location of base.

$ orabase/uo1/app/oracle

2. Update the oratab entry, to set the new ORACLE_HOME pointing towards ORCL and disable automatic startup

Sample /etc/oratab

#orcl:/opt/oracle/product/10.2/db_1:Norcl:/opt/oracle/product/11.2/db_1:N

Note : After /etc/oratab is updated to have sid and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the sid which is entered in /etc/oratab against 11g home.

for Instance,

[oracle@localhost ~]$ . oraenvORACLE_SID = [orcl] ? orclThe Oracle base for ORACLE_HOME=/opt/oracle/product/11.2/db_1 is /u01/app/oracle[oracle@localhost ~]$

12 Upgrade Database:

At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.

$ cd $ORACLE_HOME/rdbms/admin$ sqlplus "/ as sysdba"SQL> startup UPGRADE

Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.

SQL> set echo onSQL> SPOOL upgrade.logSQL> @catupgrd.sqlSQL> spool off

These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software. Also, if you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters.

Post Upgrade Steps

13 Post Upgrade:

Start the database and run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed

$ sqlplus "/as sysdba"SQL> STARTUPSQL> @utlu112s.sql

14 Recompile Invalid Objects:

This script can be run concurrently with utlrp.sql. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.

SQL> @utlrp.sql

15 Check for the integrity of the source database:

Check for the integrity of the upgraded database by running dbupgdiag.sql script from below Metalink article

Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.

After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.

16 Configure & Start Listener.ora :

Modify the listener.ora file:

For the upgraded instance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME. Start the listener :

lsnrctl start

17 Crosscheck Environment Variables:

Set Environment Variables

1. Make sure the following environment variables point to the Oracle 11g Release directories:

- ORACLE_BASE- ORACLE_HOME- PATH

Also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the Oracle Database 11g Release 2 (11.2) home.

Note : If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.

2. Modify /etc/oratab entry to use automatic startup

SID:ORACLE_HOME:Y


For Instance,orcl:/opt/oracle/product/11.2/db_1:Y

18 Spfile from Pfile:

Edit init.ora:

- If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE- Migrate your initialization parameter file to a server parameter file.

Create a server parameter file with a initialization parameter file

SQL> create spfile from pfile;

This will create a spfile as a copy of the init.ora file located in $ORACLE_HOME/dbs (UNIX) & %ORACLE_HOME%\database (Windows).

19 Start the Database with spfile:

Shutdown the database:

Sql> shutdown immediate

Now start the database it will user spfile

Sql> startup

Check the Alert log file for any Error.

Database is ready to use now.

Manual Upgrade Database from Oracle 10.2.0.4 to Oracle 11.2.0.2

More Sharing Services Share |Share on facebook Share on twitter Share on email Share on

print

Introduction:

This document applies for Oracle 10.2.0.4 Ent. Edition on Red Hat Linux 5. This

document will guide you how to upgrade Oracle 10.2.0.4 Ent Edition single Instance to

Oracle 11.2.0.2 Ent. Edition.

The following topics guide you through the process of performing a manual upgrade.

They assume that you have previously run the Pre-Upgrade Information Tool

• Backing up the Database

• Preparing the New Oracle Home

http://blog.sapadmin.co.in/?p=495





http://blog.sapadmin.co.in/addthis.com/bookmark.php?v=300&username=xa-4d2b47597ad291fb

• Manually Upgrading the Database

• Troubleshooting the Upgrade

• Cancelling the Upgrade

Backing Up the Database

After running the Pre-Upgrade Information Tool and cleanly shutting down the instance,

Oracle recommends that you back up the database as described in this section. If you

encounter problems with the upgrade and wish to abandon the upgrade completely,

then you must restore the database from this backup. Therefore, Oracle recommends

that you back up your database now as a precaution.

There are two ways to take backup:

1. Cold Backup

2. Rman Backup

Cold Backup:

Copy all the datafiles, control files and Redo log files to a different location.

Rman Backup:

Sign on to RMAN:

1. rman “target / nocatalog”

2. Issue the following RMAN commands:

RUN

{

ALLOCATE CHANNEL chan_name TYPE DISK;

BACKUP DATABASE FORMAT ‘some_backup_directory%U’ TAG before_upgrade;

BACKUP CURRENT CONTROLFILE FORMAT ‘controlfile location and name’;

}

Preparing the New Oracle Home

After backing up the database to be upgraded, prepare the new Oracle home in a new

location. Do this for any release of Oracle Database for which you are upgrading,

whether the database is release 11.2 or earlier.

To prepare the new Oracle home

1. Copy configuration files from the Oracle home of the database being upgraded to

the new Oracle Database 11gRelease 2 (11.2) Oracle home:

2. If your parameter file resides within the old environment’s Oracle home, then copy

it to the new Oracle home. By default, Oracle looks for the parameter file in the

ORACLE_HOME/dbs directory on Linux or UNIX platforms. The parameter file can reside

anywhere you wish, but it should not reside in the old environment’s Oracle home after

you upgrade to Oracle Database 11g Release 2 (11.2).

http://www.dbas-oracle.com/2011/06/manual-upgrade-database-from-oracle.html

Note: It might be necessary to create a text initialization parameter file (PFILE) from

the server parameter file (SPFILE) so that you can edit the initialization parameters.

Sql> CREATE pfile FROM spfile;

3. If you have a password file that resides within the old environment’s Oracle home,

then move or copy the password file to the new Oracle Database 11g Release 2 (11.2)

Oracle home.The name and location of the password file are operating system-specific.

On Linux or UNIX platforms, the default password file is orapwsid, located in the

ORACLE_HOME/dbs directory. In both cases, sid is your Oracle instance ID.

4. The Pre-Upgrade Information Tool displays any deprecated parameters and obsolete

parameters it finds in the Deprecated Parameters and Obsolete Parameters sections.

Make sure the COMPATIBLE initialization parameter is properly set for Oracle Database

11g Release 2 (11.2). The Pre-Upgrade Information Tool displays a warning in the

Database section if COMPATIBLE is not properly set.

5. Adjust the values of the initialization parameters to at least the minimum values

indicated by the Pre-Upgrade Information Tool.

6. Make sure all path names in the parameter file are fully specified. You should not

have relative path names in the parameter file.

7. Make sure you save all of the files you modified after making these adjustments.

Manually Upgrading the Database

After preparing the new Oracle home, you are ready to proceed with the manual

upgrade.

To manually upgrade the database

• Shut down the instance:

Sql> SHUTDOWN IMMEDIATE

• On Linux or UNIX operating system perform the following checks:

Your ORACLE_SID is set correctly. The oratab file points to your Oracle Database 11g

Release 2 (11.2) Oracle home

The following environment variables should point to the Oracle Database 11g

Release 2 (11.2) directories:

ORACLE_HOME, PATH

Any scripts that clients use to set the ORACLE_HOME value must point to the new

Oracle home.

• Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2)

Oracle home directory.

• At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

[oracle@ ~]$ cd $ ORACLE_HOME/rdbms/admin

• Start SQL*Plus.

[oracle@ ~]$ export ORACLE_SID=[instance_name]

[oracle@ ~]$ sqlplus sys as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 29 10:29:11 2011


Enter password:

• Start the instance by issuing the following command:

Sql> STARTUP UPGRADE







Database mounted.

Database opened.

Note: The UPGRADE keyword enables you to open a database based on an earlier

Oracle Database release. It also restricts logons to AS SYSDBA sessions, disables system

triggers, and performs additional operations that prepare the environment for the

upgrade. You might be required to use the PFILE option to specify the location of your

initialization parameter file.

Once the database is started in upgrade mode, only queries on fixed views execute

without errors until after the catupgrd.sql script is run. Before running catupgrd.sql,

queries on any other view or the use of PL/SQL returns an error.

Common Error & Solution for Database Upgrade:

The following are common errors that might occur when attempting to start the new

Oracle Database 11g Release 2 (11.2) database. Some of these errors are written to the

alert log and not to your session. If you receive any of these errors, then issue the

SHUTDOWN ABORT command to shut down the database and correct the problem.

ORA-00401: the value for parameter compatible is not supported by this release

Solution: The COMPATIBLE initialization parameter is set to a value less than 10.0.0.

ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE

Solution: The CLUSTER_DATABASE initialization parameter is set to TRUE instead of

FALSE.


ORA-39700: database must be opened with UPGRADE option

Solution: The STARTUP command was issued without the UPGRADE keyword.

ORA-00336: log file size xxxx blocks is less than minimum 8192 blocks

Solution: A redo log file size is less than 4 MB:

If errors appear listing obsolete initialization parameters, then make a note of the

obsolete initialization parameters and continue with the upgrade. Remove the obsolete

initialization parameters the next time you shut down the database.

• Sql> SPOOL upgrade.log

• Run the Pre-Upgrade Information Tool by executing the utlu112i.sql script:

Sql> @utlu112i.sql

• Run the catupgrd.sql script:

Sql> @catupgrd.sql

Note: If you did not run the Pre-Upgrade Information Tool, the catupgrd.sql script

terminates with one of the following errors:

RA-00942: table or view does not exist

ORA-00904: “TZ_VERSION”: invalid identifier

ORA-01722: invalid number

If you receive any of these errors, issue the SHUTDOWN ABORT statement, revert to the

original Oracle home directory, and run the Pre-Upgrade Information Tool (utlu112i.sql)

The catupgrd.sql script determines which upgrade scripts must be run, runs them, and

then shuts down the database. You must run the script in the Oracle Database 11g

Release 2 (11.2) environment.

• Restart the instance to reinitialize the system parameters for normal operation.

Sql> STARTUP

This restart, following the database shutdown performed as part of the catupgrd.sql

script, flushes all caches, clears buffers, and performs other housekeeping activities.

These measures are an important final step to ensure the integrity and consistency of

the newly upgraded Oracle Database software.

Note: If you encountered a message listing obsolete initialization parameters when

you started the database, then remove the obsolete initialization parameters from the

parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can

edit the file to delete parameters

• Run utlu112s.sql, the Post-Upgrade Status Tool, which provides a summary of the

upgrade at the end of the spool log. You must run utlu112s.sql only immediately after

running catupgrd.sql. Run utlu112s.sql to display the results of the upgrade as follows:

Sql> @utlu112s.sql

If the Post-Upgrade Status Tool returns errors or shows components that are not VALID

or not the most recent release, then see “Troubleshooting the Upgrade” for more

information.

• Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

Sql> @utlrp.sql

Verify that all expected packages and classes are valid:

Sql> SELECT count(*) FROM dba_invalid_objects;

Sql> SELECT distinct object_name FROM dba_invalid_objects;

Note: If the pre-upgrade information tool detected INVALID objects and populated the

registry$sys_inv_objs and registry$nonsys_inv_objs tables, then execute

ORACLE_HOME/rdbms/admin/utluiobj.sql to display only those objects which are newly

invalid because of the upgrade process. The utluiobj.sql script only displays objects that

are now INVALID but which were VALID before the upgrade.

• Exit SQL*Plus.

Your database is now upgraded to the new Oracle Database 11g release. You are ready

to complete the procedures described in Chapter 4, “After Upgrading to the New

Release”.

WARNING:

If you retain the old Oracle software, then never start the upgraded database with the

old software. Only start the database with the executables in the new Oracle Database

installation. Also, before you remove the old Oracle environment, make sure you

relocate any data files in that environment to the new Oracle Database environment.

See the Oracle Database Administrator’s Guide for information about relocating data

files.

About the Post-Upgrade Status Tool

The Post-Upgrade Status Tool, which is the utlu112s.sql script, displays the status of the

database components in the upgraded database and the time required to complete

each component upgrade. Any errors that occur during the upgrade are listed with each




component and must be addressed. The utlu112s.sql script must only be run

immediately after catupgrd.sql

The Post-Upgrade Status Tool displays a report similar to the following output:

Oracle Database 11.2 Post-Upgrade Status Tool 10-18-2010 22:48:55

Component Status Version HH:MM:SS

Oracle Server. VALID 11.2.0.2.0 00:17:31

JServer JAVA

Virtual Machine. VALID 11.2.0.2.0 00:02:32

————————–

————————–

Oracle Application Express. VALID 3.2.1.00.12 00:23:25

Gathering Statistics. 00:05:12

Total Upgrade Time: 01:29:03

Note: Any time after utlrp.sql is run instead of using utl112s.sql to determine the

STATUS of a component, run this query:

SELECT COMP_NAME,VERSION,STATUS FROM DBA_REGISTRY;

This will return the most up-to-date information.

Troubleshooting the Upgrade:

This section explains what to do if something goes wrong with your upgrade. This

section contains the following topics:

• Resource Limits

• Edition Session Startup Error

• Manual Workaround for ORA-01408

• Running the DBMS_DST Package After Upgrade Can Result in ORA-01822

• DBUA May Mark Invalid Components with an X Before Entire Upgrade is Done

• Component Status

• Rerunning the Upgrade

• Cancelling the Upgrade

Resource Limits

If you run out of resources during the upgrade, then increase the resource allocation.

After increasing the resource allocation, you should perform a SHUTDOWN ABORT and

restart the instance (in UPGRADE mode) before rerunning the catupgrd.sql script or

restarting DBUA.

The resources that generally require increases for a new Oracle Database release are as

follows:

• SYSTEM and SYSAUX tablespaces

Typically you receive one of the following messages during the upgrade if your SYSTEM

tablespace size is insufficient:

ORA-01650: unable to extend rollback segment string by string in tablespace string

ORA-01651: unable to extend save undo segment by string for tablespace string



ORA-01652: unable to extend temp segment by string in tablespace string

ORA-01653: unable to extend table string.string by string in tablespace string

ORA-01654: unable to extend index string.string by string in tablespace string

ORA-01655: unable to extend cluster string.string by string in tablespace string

To avoid these errors, set AUTOEXTEND ON MAXSIZE UNLIMITED for the SYSTEM and

SYSAUX tablespaces.

ORA-04031: unable to allocate string bytes of shared memory

(“string”,”string”,”string”,”string”)

You might require larger shared memory pool sizes in some cases. The error message

indicates which shared memory initialization parameter must be increased.

If you are using an undo tablespace, then be sure it is at least 400 MB.

Rerunning the Upgrade

You can rerun the upgrade with the catupgrd.sql script as described in the following

steps.

To rerun the upgrade

1. Shut down the database as follows:

2. Sql> SHUTDOWN IMMEDIATE

3. Restart the database in UPGRADE mode:

4. Sql> STARTUP UPGRADE

5. Set the system to spool results to a log file for later verification of success:

6. Sql> SPOOL upgrade.log

7. Rerun catupgrd.sql:

Sql> @catupgrd.sql

Note: You can rerun the catupgrd.sql script as many times as necessary. The first time

you run the script, there should be no error messages returned. If you rerun the script,

then the ORA-00001 message is displayed. You can safely ignore this message.

Cancelling the Upgrade

If you completed the steps in “Backing Up the Database” to back up your database,

then the easiest way to cancel the upgrade is to restore that backup as described in the

following procedure.

To cancel the upgrade by restoring the previous backup

Restore Usingin RMAN:

1. Log in to the system as the owner of the Oracle home directory of the previous

release.

2. Sign on to RMAN:

3. rman “target / nocatalog”

4. Issue the following RMAN commands:

5. STARTUP NOMOUNT

RUN

{

RESTORE CONTROLFILE FROM ‘save_controlfile_location’;

ALTER DATABASE MOUNT;

RESTORE DATABASE FROM TAG before_upgrade

ALTER DATABASE OPEN RESETLOGS;

}

Restore Using Cold Backup:

Copy database backup files to original location and database is restored.

How to Upgrade a 10.2 Database to an 11.2 DatabaseOracle Database » How To Guides » How to Upgrade a 10.2 Database to an 11.2 Database

Fold

Table of Contents

Background & Overview

Assumptions & Pre-Requisites

Useful Information

Step-By-Step Guide

Example Install

Example Upgrade

File Listings

Oracle .profile

oratab

listener.ora

initSID.ora

File Systems

Known Issues

Error ORA-06550 During Upgrade.

Description

http://www.oracle-wiki.net/startdocshowtoupgradedb102todb112#toc14















http://www.oracle-wiki.net/startdocs

http://www.oracle-wiki.net/start

Fix

Background & Overview

The following documentation provides instructions for upgrading an Oracle 10.2.0.4 database to an Oracle 11.2 database residing on Sun Solaris 10.

Assumptions & Pre-Requisites

This document expects and assumes the following:

The instructions are carried out by a qualified DBA. Access to the internet is available.

All necessary client software, e.g. Telnet and X-Server is available.

The resultant database will be standalone, i.e. not part of a cluster or failover configuration.

All references to SID should be replaced with correct database name as derived using a suitable database naming standard.

There is only ONE database installed on the server.

Useful Information

This section outlines useful information and changes to previous 10g documentation found on this site. Some have been made for cosmetic reasons, others for more practical considerations.

A new directory structure exists. See below.

A new init.ora parameter called diag_dest has been set and maps to the diag directory in the new directory structure mentioned above.

A listener.ora file has a new parameter called ADR_BASE_LISTENER. This maps to the base of the diag directory.

There are a number of new maintenance jobs in 11g. These can be turned off as part of this step-by-step guide.

Read the Known Issues section below.

Step-By-Step Guide

1. Ensure the new directory structure exists. See below.

2. Download the binaries for the Oracle 11g Database into/u01/app/oracle/SOFTWARE/DATABASE_11G

http://www.oracle-wiki.net/startdocshowtoupgradedb102todb112#directory_structure

http://www.oracle-wiki.net/startdocshowtoupgradedb102todb112#known_issues

http://www.oracle-wiki.net/startdocshowtoupgradedb102todb112#directory_structure


Enterprise Edition for Oracle Solaris Operating System (SPARC) (64-bit)solaris.sparc64_11gR2_database_1of2.zip := 1,373,903,591 bytes; cksum - 2779246655solaris.sparc64_11gR2_database_2of2.zip := 1,048,619,225 bytes; cksum - 3179998886

3. Unzip the downloaded files using the following commands

cd /u01/app/oracle/SOFTWARE/DATABASE_11G unzip solaris.sparc64_11gR2_database_1of2.zip

unzip solaris.sparc64_11gR2_database_2of2.zip

4. Unset the ORACLE_HOME and TNS_ADMIN

unset ORACLE_HOME unset TNS_ADMIN

5. Start x-server.

6. Start the installer.

cd /u01/app/oracle/SOFTWARE/DATABASE_11G/database export DISPLAY= Enter Your IP Address

./runInstaller

7. Follow the instructions as demonstrated in the Example Install section below.

Before continuing apply any oneoff patches, patch bundles or CPU patch bundles recommended by Oracle or the application vendor. At the time of writing this guide, the following PSU is available: 9654983

8. If PSU 11.2.0.1.2 has been applied then patch 9315778 will need to be applied to avoid ORA-06550druing the upgrade.

9. Ensure all Pre-Upgrade Requirements are Met.

. oraenv ORACLE_SID = [oracle] ? SID

sqlplus '/ as sysdba'

SQL> @?/rdbms/admin/utlrp

SQL> purge dba_recyclebin

SQL> alter database datafile '/u03/oradata/SID/system/d1/system_01.dbf' resize 1000m;

10. Download the Latest Upgrade Check Script from Metalink article 884522.1

https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(viewingMode=1143&from=BOOKMARK&bmDocTitle=How%20to%20Download%20and%20Run%20Oracle's%20Database%20Pre-Upgrade%20Utility&bmDocDsrc=KB&bmDocType=HOWTO&bmDocID=884522.1))

http://www.oracle-wiki.net/startdocshowtoupgradedb102todb112#example_install

11. Perform an upgrade check on the exiting 10g database.



SQL> startup

SQL> spool /tmp/upgrade_check.log

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

12. Review the output and resolve any issues.

13. Start the listener


lsnrctl start

14. Start DBUA.

cd /u01/app/oracle/product/11.2.0/dbhome_1/bin export DISPLAY= Enter Your IP Address

dbua

15. Follow the instructions as demonstrated in the Example Upgrade section below.

16. Update the Oracle .profile

Update the .profile using the listing below as a template. Log off and log back onto Oracle to pick up the new .profile

17. Create the admin directories

cd /u01/app/oracle/admin/SID mkdir pfile

mkdir audit

18. Reconfigure the new listener


lsnrctl stop

lsnrctl start

http://www.oracle-wiki.net/startdocshowtoupgradedb102todb112#oracle_profile

http://www.oracle-wiki.net/startdocshowtoupgradedb102todb112#example_upgrade

19. Create new spfile



create pfile='/u01/app/oracle/admin/SID/pfile/initSID.ora' from spfile

shutdown immediate

quit

cd /u01/app/oracle/admin/SID/pfile

mv initSID.ora initSID.POST_UPGRADE

Create a pfile using the listing below.

cd $ORACLE_HOME/dbs

rm spfileSID.ora

rm initSID.ora

ln -s /u01/app/oracle/admin/SID/pfile/initSID.ora


startup

create spfile='/u01/app/oracle/admin/SID/pfile/spfileSID.ora' from pfile;

shutdown immediate

quit

cd $ORACLE_HOME/dbs

rm initSID.ord

ln -s /u01/app/oracle/admin/SID/pfile/spfileSID.ora

cd /u01/app/oracle/admin/SID/pfile

rm initSID.ora


startup

quit

20. Remove the old binaries

rm -rf OLD_ORACLE_HOME

http://www.oracle-wiki.net/startdocshowtoupgradedb102todb112#initsid_ora

21. Check the database is registered with the listener

lsnrctl status

22. Ensure DST update is not in progress



SQL> select property_name, substr(property_value, 1, 30) value from database_properties where property_name like 'DST_%' order by property_name;

o NB DST_SECONDARY_TT_VERSION should be set to 0

o NB DST_UPGRADE_STATE should be set to NONE

23. Prepare the DST Timezone Update



SQL> exec DBMS_DST.BEGIN_PREPARE(&version);

o NB For 11.2.0.1, the latest version is 11. For 11.2.0.2 the latest version is 14.

24. Ensure the prepare has completed successfully.




o NB DST_SECONDARY_TT_VERSION should be set to the version being upgraded to. E.g. 11

o NB DST_UPGRADE_STATE should be set to prepare

25. Clear out the DST upgrade logging tables prior to checkinng for affected data



SQL> truncate table sys.dst$trigger_table;

SQL> truncate table sys.dst$affected_tables;

SQL> truncate table sys.dst$error_table;

26. Check for data that may be affected by the DST update



SQL> BEGIN DBMS_DST.FIND_AFFECTED_TABLES (affected_tables => 'sys.dst$affected_tables', log_errors => TRUE, log_errors_table => 'sys.dst$error_table'); END; /

SQL> select * from sys.dst$affected_tables;

o NB This will show a list of tables affected by the DST update. Hopefully none.

SQL> SELECT * FROM sys.dst$error_table;

o NB This will show type errors that occured in the rows reported in dst$affected_tables.

27. End the prepare state in preparation for the upgrade.



SQL> EXEC DBMS_DST.END_PREPARE;


o NB DST_SECONDARY_TT_VERSION should be set to 0

o NB DST_UPGRADE_STATE should be set to NONE

28. Upgrade the DST timezone.





SQL> purge dba_recyclebin

SQL> truncate table sys.dst$trigger_table;

SQL> truncate table sys.dst$affected_tables;

SQL> truncate table sys.dst$error_table;

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);


SQL> shutdown immediate;

SQL> startup

SQL> set serveroutput on

SQL> VAR numfail number

SQL> BEGIN DBMS_DST.UPGRADE_DATABASE(:numfail,parallel => TRUE,log_errors => TRUE,log_errors_table => 'SYS.DST$ERROR_TABLE',log_triggers_table => 'SYS.DST$TRIGGER_TABLE',error_on_overlap_time => FALSE,error_on_nonexisting_time => FALSE); DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail); END; /

SQL> VAR fail number

SQL> BEGIN DBMS_DST.END_UPGRADE(:fail); DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail); END; /

SQL> select * from v$timezone_file;

o NB This should report the new timezone file version. E.g. 11

29. Check for Depricated, non-default parameters.

ORACLE_SID = [oracle] ? SID sqlplus '/ as sysdba'

SQL> SELECT name FROM v$parameter WHERE isdeprecated = 'TRUE' and isdefault='FALSE'

o NB This should return 0 rows. If not, migrate to a more suitable init.ora.

30. Disable the default 11g maintenance jobs if required.

sqlplus /nolog SQL> connect sys as sysdba

SQL> BEGIN

SQL> dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => NULL,window_name => NULL);

SQL> dbms_auto_task_admin.disable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);

SQL> dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);

SQL> END;

SQL> /

31. Run Gather Stats against 'SYS.

sqlplus /nolog SQL> connect sys as sysdba

SQL> exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Example Install

Un-tick the I wish to receive security updates via My Oracle Support box.

Click Next.

Click Yes to remain uninformed.

Select Install database software only. Click Next.

Ensure Single instance database installation is selected. Click Next.

Ensure languages Englsih and English (United Kingdom) are selected. Click Next.

Ensure Enterprise Edition is selected. Click Next.

Set Oracle Base to /u01/app/oracle Allow Software Location to default

Click Next.

Ensure Database Administrator (OSDBA) Group is set to dba Ensure Database Operator (OSOPER) Group is set to dba

Click Next

This page may not be displayed. Review and fix any issues.

Click Next

Note: The installer may report a failed check on OS Patch 124861-15. Its possible that this failed check can be ignored. Review Metalink document 969497.1

https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocDsrc=KB&bmDocType=PROBLEM&bmDocID=969497.1&viewingMode=1143&from=BOOKMARK&bmDocTitle=During%20An%2011gR2%20(11.2)%20Installation%20On%20Solaris,%20OUI%20Performs%20A%20Prerequisite%252

Review the summary. If required save the response file by selecting Save Response File

Click Finish

This screen moves on automatically when complete.

Ask Unix Administrator to run root.sh.

Click OK.

Click Close.

Example Upgrade

Click Next.

Select the database you wish to upgrade. Click Next.

Review the warnings and fix if applicable. Click Yes.

Ensure Recompile invalid objects at the end of upgrade is ticked. Turn off archiving if required. Mostly this will not be necessary.

Ensure Backup database is NOT ticked.

o NB You should backup the database prior to this step using the normal backup method.

Click Next.

If you left archiving on, Click Yes to continue.

Ensure Do Not Move Database Files as Part of Upgrade is selected. Click Next.

Ensure Specify Flash Recovery Area is Un-Ticked. Ensure the Diagnostics Destination is set to /u01/app/oracle

Click Next.

Review the Database Upgrade Summary. Click Finish.

Monitor the upgrade progress. NOTE This screen will move on automatically.

Click Ignore. NOTE - Review the Known Issues section below regarding this error message.

http://www.oracle-wiki.net/startdocshowtoupgradedb102todb112#known_issues

Review the Upgrade Results Click Close.

File Listings

Oracle .profile #---------------------------------------------------------------------- # Configure Terminal Settings. #----------------------------------------------------------------------

stty susp ^Z stty quit ^C stty erase ^? export TERM=vt100-w export ORACLE_TERM=vt100 #---------------------------------------------------------------------- # Configure Shell Settings. #---------------------------------------------------------------------- set -o vi export PATH=/bin:/usr/sbin:/usr/bin:/usr/local/bin:$PATH export EDITOR=vi export HOSTNAME=`hostname` export PS1='$LOGNAME@$HOSTNAME:$ORACLE_SID> ' export TMPDIR=/tmp export TEMP=/tmp umask 022 #---------------------------------------------------------------------- # Configure Aliases. #---------------------------------------------------------------------- alias ll="ls -la" alias bdf="df -k" #---------------------------------------------------------------------- # Configure Oracle Settings. #---------------------------------------------------------------------- export ORACLE_BASE=/u01/app/oracle export SQLPATH=$ORACLE_BASE/scripts/dba/sql export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export TNS_ADMIN=$ORACLE_HOME/network/admin export ORACLE_HOME_LISTNER=$ORACLE_BASE/product/11.2.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH export ORATAB=/var/opt/oracle/oratab

oratab SID:/u01/app/oracle/product/11.2.0/dbhome_1:Y

listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = <SID>) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = <SID>) ) )

LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521)) )

ADR_BASE_LISTENER = /u01/app/oracle

initSID.ora ########################################### # Cache and I/O ########################################### db_block_size=8192 memory_target=1606418432 db_block_checking=MEDIUM db_block_checksum='FULL'

########################################### # Cursors and Library Cache ########################################### open_cursors=500

########################################### # Database Identification ########################################### compatible='11.2.0.0.0' db_name='SID'

########################################### # Diagnostics and Statistics ########################################### diagnostic_dest='/u01/app/oracle'

########################################### # File Configuration ########################################### control_files='+DATA/SID/control01.ctl','+DATA/SID/control02.ctl', '+DATA/SID/control03.ctl'

########################################### # Processes ########################################### processes=1000

########################################### # System Managed Undo and Rollback Segments ########################################### undo_tablespace='UNDO'

########################################### # Security and Auditing ########################################### audit_trail='db' remote_login_passwordfile='EXCLUSIVE'

########################################### # Enabling Archivelog mode ########################################### log_archive_dest='+FRA/SID/arch/d1' log_archive_format=arch_%t_%s_%r.log

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

# Defer Segment Creation ########################################### deferred_segment_creation=FALSE

File Systems /--- /var --- /opt --- /oracle | --- /u01 --- /app --- /oracle --- /software | | | --- /dba --- /scripts | | | --- /sql | | | --- /diag | | | --- /product --- /11.2.0 ---/dbhome_1 | | | --- /admin --- /SID --- /adump | | | --- /create | | | --- /exp | | (link to /u09) | | | --- /parfile | | | --- /pfile | | | --- /scripts ---/base ---/rfc | | --- /u02 --- /oradata --- /SID --- /redo --- /d1 | | | --- /redo --- /d2 | --- /u03 --- /oradata --- /SID --- /indx --- /d1 | | | --- /sysaux --- /d1 | | | --- /system --- /d1 | | | --- /tools --- /d1 | | | --- /users --- /d1 | --- /u04 --- /oradata --- /SID --- /undo --- /d1 | --- /u05 --- /oradata --- /SID --- /temp --- /d1 | --- /u06 --- /oradata --- /SID --- /arch --- /d1 | --- /u07 --- /oradata --- /SID --- /data --- /d1 |

--- /u08 --- /oradata --- /SID --- /index --- /d1 | --- /u09 --- /oradata --- /SID --- /exp --- /d1 | --- /u10 --- /oradata --- /SID --- /flash --- /d1

Known Issues

Error ORA-06550 During Upgrade.

Description

Whilst running the upgrade you may encouner an ORA-06550 as detailled in Metalink article 1066828.1.

Fix

This error can be ignored. However, you can avoid the error by applying patch 9315778 to the 11.2 binaries prior to the upgrade starting.

RDBMS Upgrade : 10.2.0.4 to 11.2.0.2

nitiserver[oracle]_test> mkdir -p /nitiserver/dba01/oracle/test/upgrade11gR2

nitiserver[oracle]_test> cp /nitiserver/testdb/oracle/product/11.2.0.2/rdbms/admin/utlu112i.sql $ORACLE_HOME/test/upgrade11gR2

nitiserver[oracle]_test> cd $ORACLE_HOME/test/upgrade11gR2

nitiserver[oracle]_test> pwd/nitiserver/dba01/oracle/test/upgrade11gR2

nitiserver[oracle]_test> ls -ltrtotal 204-rw-r--r-- 1 oracle dba 204205 Apr 15 03:43 utlu112i.sql

nitiserver[oracle]_test> sqlplus "/ as sysdba"SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 15 03:43:48 2012Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing optionsSQL> spool upgrade_info.logSQL> @utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 04-15-2012 03:44:22Script Version: 11.2.0.2.0 Build: 001

https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(viewingMode=1143&bmDocTitle=11GR2%20DBUA%20ORA-06550%20PLS-00201%20IDENTIFIER%20SYS.DBMS_JAVA%20MUST%20BE%20DECLARED&bmDocDsrc=KB&bmDocType=PROBLEM&bmDocID=1066828.1&from=BOOKMARK))

.**********************************************************************Database:**********************************************************************--> name: test--> version: 10.2.0.4.0--> compatible: 10.2.0.2.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: 389 MB--> UNDOTBS1 tablespace is adequate for the upgrade..... minimum required size: 211 MB--> TEMP tablespace is adequate for the upgrade..... minimum required size: 61 MB--> SYSAUX tablespace is adequate for the upgrade..... minimum required size: 318 MB.**********************************************************************Flashback: OFF********************************************************************************************************************************************Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]Note: Pre-upgrade tool was run on a lower version 32-bit database.**********************************************************************--> If Target Oracle is 32-Bit, refer here for Update Parameters:WARNING: --> "shared_pool_size" needs to be increased to at least 236 MB.--> If Target Oracle is 64-Bit, refer here for Update Parameters:WARNING: --> "shared_pool_size" needs to be increased to at least 472 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]**********************************************************************--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"--> user_dump_dest 11.1 DEPRECATED 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--> OLAP Analytic Workspace [upgrade] VALID--> Oracle OLAP API [upgrade] VALID.**********************************************************************Miscellaneous Warnings**********************************************************************WARNING: --> Database is using a timezone file older than version 14..... After the release migration, it is recommended that DBMS_DST package.... be used to upgrade the 10.2.0.4.0 database timezone version.... to the latest version which comes with the new release.WARNING: --> Database contains INVALID objects prior to upgrade..... The list of invalid SYS/SYSTEM objects was written to.... registry$sys_inv_objs..... The list of non-SYS/SYSTEM objects was written to.... registry$nonsys_inv_objs..... Use utluiobj.sql after the upgrade to identify any new invalid.... objects due to the upgrade..... USER testBA has 2 INVALID objects..... USER SYSTEM has 11 INVALID objects..... USER testBAPRD has 27 INVALID objects..... USER SYS has 2 INVALID objects..**********************************************************************Recommendations**********************************************************************Oracle recommends gathering dictionary statistics prior toupgrading the database.To gather dictionary statistics execute the following commandwhile 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 commandswhile 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> @$ORACLE_HOME/rdbms/admin/utlrp.sql


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

cd /nitiserver/oracle/bincp test_11202 test

nitiserver[oracle]_test> vi /etc/oratab# This file is used by ORACLE utilities. It is created by root.sh# and updated by the Database Configuration Assistant when creating# a database.# A colon, ':', is used as the field terminator. A new line terminates# the entry. Lines beginning with a pound sign, '#', are comments.## Entries are of the form:# $ORACLE_SID:$ORACLE_HOME:<N|Y>:## The first and second fields are the system identifier and home# directory of the database respectively. The third filed indicates# to the dbstart utility that the database should , "Y", or should not,# "N", be brought up at system boot time.## Multiple entries with the same $ORACLE_SID are not allowed.##test:/nitiserver/testdb/oracle/product/test_10.2.0.4:Ntest:/nitiserver/testdb/oracle/product/11.2.0.2:N

######################################################################################nitiserver[oracle]_test> cat /etc/oraInst.loc

#inventory_loc=/nitiserver/testdb/oracle/product/test_10.2.0.4/oraInventoryinventory_loc=/nitiserver/testdb/oracle/product/11.2.0.2/oraInventoryinst_group=dba############################################################################################Change the init paramete diagnostic_dest=/nitiserver/dump01/oracle/test/bdumpdiagnostic_dest=/nitiserver/dump01/oracle/test/udumpcore_dump_dest=/nitiserver/dump01/oracle/test/cdump

compatible=11.2.0.2.0shared_pool_size=263402291

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

nitiserver[oracle]_test> ln -s $SID_HOME/pfile/init$ORACLE_SID.ora $ORACLE_HOME/dbs/init$ORACLE_SID.ora

nitiserver[oracle]_test> ls -ltr $ORACLE_HOME/dbs/total 4-rw-r--r-- 1 oracle dba 2851 May 15 2009 init.oralrwxrwxrwx 1 oracle dba 50 Apr 15 04:37 inittest.ora -> /nitiserver/dba01/oracle/test/pfile/inittest.ora

nitiserver[oracle]_test> cd $SID_HOME/upgrade11gR2nitiserver[oracle]_test> pwd/nitiserver/dba01/oracle/test/upgrade11gR2

SQL> STARTUP UPGRADE;ORA-00371: not enough shared pool memory, should be atleast 263402291 bytes#################################################################################################[oracle@nitiserver ~]$ sqlplusSQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 15 06:36:42 2012Copyright (c) 1982, 2010, Oracle. All rights reserved.Enter user-name: / as sysdbaConnected to:Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> !echo $ORACLE_SIDtestSQL> STARTUP UPGRADE;

ORACLE instance started.Total System Global Area 481275904 bytesFixed Size 1344952 bytesVariable Size 339741256 bytesDatabase Buffers 134217728 bytesRedo Buffers 5971968 bytesDatabase mounted.Database opened.SQL> SET SERVEROUTPUT ONSQL> 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> EXEC DBMS_DST.BEGIN_UPGRADE(11);An upgrade window has been successfully started.PL/SQL procedure successfully completed.SQL> column PROPERTY_NAME format a30column VALUE format a30SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME LIKE 'DST_%'ORDER BY PROPERTY_NAME;SQL> SQL> 2 3 4PROPERTY_NAME VALUE------------------------------ ------------------------------DST_PRIMARY_TT_VERSION 11DST_SECONDARY_TT_VERSION 4DST_UPGRADE_STATE UPGRADESQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);BEGIN DBMS_DST.BEGIN_UPGRADE(14); END;*ERROR at line 1:ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loadingof a secondary time zone data file is in an active stateORA-06512: at "SYS.DBMS_SYS_ERROR", line 79ORA-06512: at "SYS.DBMS_DST", line 1158ORA-06512: at line 1

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES WHERE UPGRADE_IN_PROGRESS='YES'; 2 3no rows selectedSQL> SHUTDOWN IMMEDIATEDatabase closed.Database dismounted.ORACLE instance shut down.SQL> STARTUPORACLE instance started.Total System Global Area 481275904 bytesFixed Size 1344952 bytesVariable Size 339741256 bytesDatabase Buffers 134217728 bytesRedo Buffers 5971968 bytesDatabase mounted.Database opened.SQL> set serveroutput onVAR numfail numberBEGINSQL> SQL> 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 /Failures:0PL/SQL procedure successfully completed.SQL> VAR fail numberBEGINDBMS_DST.END_UPGRADE(:fail);DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);END;SQL> 2 3 4 5 6 ; 7 8 /;*ERROR at line 6:ORA-06550: line 6, column 1:PLS-00103: Encountered the symbol ";"

SQL> VAR fail numberBEGINDBMS_DST.END_UPGRADE(:fail);DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);END;SQL> 2 3 4 5 /An upgrade window has been successfully ended.Failures:0PL/SQL procedure successfully completed.SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME LIKE 'DST_%'ORDER BY PROPERTY_NAME;SELECT * FROM v$timezone_file; 2 3 4PROPERTY_NAME VALUE------------------------------ ------------------------------DST_PRIMARY_TT_VERSION 11DST_SECONDARY_TT_VERSION 0DST_UPGRADE_STATE NONESQL> SQL>FILENAME VERSION-------------------- ----------timezlrg_11.dat 11SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

BEGIN DBMS_DST.BEGIN_UPGRADE(14); END;*ERROR at line 1:ORA-56926: database must be in UPGRADE mode in order to start an upgrade windowORA-06512: at "SYS.DBMS_SYS_ERROR", line 79ORA-06512: at "SYS.DBMS_DST", line 1195ORA-06512: at line 1

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> SQL> startupORACLE instance started.Total System Global Area 481275904 bytesFixed Size 1344952 bytesVariable Size 339741256 bytesDatabase Buffers 134217728 bytesRedo Buffers 5971968 bytesDatabase mounted.Database opened.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> SQL> startupORACLE instance started.Total System Global Area 481275904 bytesFixed Size 1344952 bytesVariable Size 339741256 bytesDatabase Buffers 134217728 bytesRedo Buffers 5971968 bytesDatabase mounted.Database opened.SQL> SELECT comp_name, version, status FROM dba_registry;COMP_NAME--------------------------------------------------------------------------------VERSION STATUS------------------------------ -----------Oracle Database Catalog Views11.2.0.2.0 VALIDOracle Database Packages and Types11.2.0.2.0 VALIDOLAP Analytic Workspace11.2.0.2.0 VALID

COMP_NAME--------------------------------------------------------------------------------VERSION STATUS

------------------------------ -----------Oracle OLAP API11.2.0.2.0 VALID

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> STARTUP UPGRADE;ORACLE instance started.Total System Global Area 481275904 bytesFixed Size 1344952 bytesVariable Size 339741256 bytesDatabase Buffers 134217728 bytesRedo Buffers 5971968 bytesDatabase mounted.Database opened.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> EXEC DBMS_DST.BEGIN_UPGRADE(14);PL/SQL procedure successfully completed.SQL> column PROPERTY_NAME format a30column VALUE format a30SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME LIKE 'DST_%'ORDER BY PROPERTY_NAME;SQL> SQL> 2 3 4PROPERTY_NAME VALUE------------------------------ ------------------------------DST_PRIMARY_TT_VERSION 14DST_SECONDARY_TT_VERSION 11DST_UPGRADE_STATE UPGRADESQL> SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES WHERE UPGRADE_IN_PROGRESS='YES'; 2 3no rows selectedSQL> SHUTDOWN IMMEDIATEstartupDatabase closed.Database dismounted.

ORACLE instance shut down.SQL> ORACLE instance started.Total System Global Area 481275904 bytesFixed Size 1344952 bytesVariable Size 339741256 bytesDatabase Buffers 134217728 bytesRedo Buffers 5971968 bytesDatabase mounted.Database opened.SQL> set serveroutput onVAR numfail numberBEGINSQL> SQL> 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 /Failures:0PL/SQL procedure successfully completed.SQL> VAR fail numberBEGINDBMS_DST.END_UPGRADE(:fail);DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);END;/SQL> 2 3 4 5 An upgrade window has been successfully ended.Failures:0PL/SQL procedure successfully completed.SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME LIKE 'DST_%'ORDER BY PROPERTY_NAME; 2 3 4PROPERTY_NAME VALUE------------------------------ ------------------------------DST_PRIMARY_TT_VERSION 14DST_SECONDARY_TT_VERSION 0DST_UPGRADE_STATE NONESQL> SQL> SELECT * FROM v$timezone_file;FILENAME VERSION-------------------- ----------timezlrg_14.dat 14

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

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

COMP_NAME VERSION STATUS--------------------------------------------- ------------------------------ -----------Oracle Database Catalog Views 11.2.0.2.0 VALIDOracle Database Packages and Types 11.2.0.2.0 VALIDOLAP Analytic Workspace 11.2.0.2.0 VALIDOracle OLAP API 11.2.0.2.0 VALID

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

LSNRCTL> start LSTNtestStarting /nitiserver/testdb/oracle/product/11.2.0.2/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.2.0 - ProductionSystem parameter file is /nitiserver/testdb/oracle/product/11.2.0.2/network/admin/test_nitiserver/listener.oraLog messages written to /nitiserver/testdb/oracle/product/11.2.0.2/log/diag/tnslsnr/nitiserver/lstntest/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nitiserver)(PORT=1599)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=nitiserver)(PORT=1599)))STATUS of the LISTENER------------------------Alias LSTNtestVersion TNSLSNR for Linux: Version 11.2.0.2.0 - ProductionStart Date 15-APR-2012 05:37:37Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP ONListener Parameter File /nitiserver/testdb/oracle/product/11.2.0.2/network/admin/test_nitiserver/listener.oraListener Log File /nitiserver/testdb/oracle/product/11.2.0.2/log/diag/tnslsnr/nitiserver/lstntest/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nitiserver)(PORT=1599)))Services Summary...Service "test." has 1 instance(s). Instance "test", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully########################################################################################################

[oracle@nitiserver test_nitiserver]$ tnsping testTNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 15-APR-2012 05:38:30Copyright (c) 1997, 2010, Oracle. All rights reserved.Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = ( ADDRESS = (PROTOCOL = TCP) ( HOST = nitiserver)(PORT = 1599)) ( CONNECT_DATA = ( SID = test)))OK (0 msec)

10.2.0.4 to 11.2.0.2 Standalone DB upgrade using dbuaFollowed Complete checklist to upgrade the database to 11g R2 using DBUA [ID

870814.1]

1. Check dba_registry for correctness of status and version at the source 10.2.0.4

database. Take necessary steps to validate them.

2. Copy from 11g Env: $ORACLE_HOME/rdbms/admin/utlu112i.sql to some other

location e.g. /tmp

3. From 10g Env:

cd /tmp

$ sqlplus '/ as sysdba'

SQL> spool upgrade_prereq.log

SQL> @utlu112i.sql

SQL> spool off

4. Review the upgrade_prereq.log and fix them.

I will put some lines from my log file where it suggested some changes:

..

..


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

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

WARNING: --> "shared_pool_size" needs to be increased to at least 472 MB

WARNING: --> "db_cache_size" needs to be increased to at least 50331648 bytes

==> dbua would take care of this.

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

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

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

--> background_dump_dest 11.1 DEPRECATED replaced by

"diagnostic_dest"

--> user_dump_dest 11.1 DEPRECATED replaced by

"diagnostic_dest"

==> dbua would take care of this

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

Miscellaneous Warnings

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

WARNING: --> Database is using a timezone file older than version 14.

.... After the release migration, it is recommended that DBMS_DST

package

.... be used to upgrade the 10.2.0.4.0 database timezone version

.... to the latest version which comes with the new release.

==> Checked "Actions FOR DST Updates When Upgrading To Or Applying The

11.2.0.2 Patchset (Doc ID 1201253.1)" . Check step B.3b). It says we can skip the

DST related upgrade instructions. dbua gives an option of upgrading the timezone

version and TIMESTAMP WITH TIME ZONE data.

WARNING: --> Database contains INVALID objects prior to upgrade.

.... The list of invalid SYS/SYSTEM objects was written to

.... registry$sys_inv_objs.

.... The list of non-SYS/SYSTEM objects was written to

.... registry$nonsys_inv_objs.

.... Use utluiobj.sql after the upgrade to identify any new invalid

.... objects due to the upgrade.

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: --> Your recycle bin contains 93 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.

==> This actually needs be done post upgrade.

http://download.oracle.com/docs/cd/E11882_01/server.112/e17222/upgrade.htm#B

ABJHIHH

5. Gather dictionary stats prior to upgrade:

On 10g ENV:

sqlplus "/as sysdba"

EXECUTE dbms_stats.gather_dictionary_stats;

6. purge dba_recyclebin

7. Run dbua

==> I saw few errors during oracle server upgrade but I could ignore those. May be

because my 10g db had many invalid objects. As most of them were ORA-04063.

==>Also EM configuration failed. Will write another post for this one.

8. Change compatible.

I havent done this yet, as I would leave it to 10.2.0.4 for few days so that in case I

want to downgrade it.

As described in “Oracle Database Upgrade Guide 11g Release 1

(11.1)“, there are three upgrade methods offered while you want to

upgrade database from 10g to 11g.

1. Database Upgrade Assistant (DBUA)

2. Manual Upgrade (Oracle provided scripts)

3. Export/Import (exp/imp, or expdp/impdp)

Besides benefits mentioned in above upgrade document, I’m thinking we

are beneficial from using export/import approach. The reasons we decided

using this method are:

we want to set up identical testing databases on development server first

we want to keep original production databases unchanged anyway for safety

reason

With this approach, we first installed Oracle 11g software on development

server and created empty database by using provided standard template

New_Database.dbt, which could be found at

http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/preup.htm#i694345

http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/preup.htm#i694345

$ORACLE_HOME/assistants/dbca/templates. After that, we followed the

following steps to move data from 10g database to newly created 11g

database.

Step 1: Pre-create tablespaces on target 11g database

using the Generate_Create_Tablespaces.sql to generate tablespace script

editing generated script to remove entries for SYSTEM, USERS tablespaces

Step 2: Export full database of source 10g database

expdp system@database directory=DATA_PUMP_DIR full=y

dumpfile=export.dmp logfile=export.log

Step 3: Copy dumpfile over network to development server

Step 4: Grant IMPORT_FULL_DATABASE system privilege to user

SYSTEM of target 11g database

Step 5: Import full database to target 11g database

Note: If you have co-existing oracle home/version on development server,

please make sure switching to Oracle 11g environment, which will assure to

use data pump at 11g version to import data.

Impdp system@database directory=DATA_PUMP_DIR full=y

dumpfile=export.dmp logfile=import.logStep by Step Upgrading Oracle 10g to Oracle 11gPosted by Debashis Paul * A Passionate Blogger * under Oracle in Windows, Oracle11g | Tags: 10g R1 to 11g upgrade, 11g EMCA configuration, 11g enterprise manager emca, creating oracle 11g TNS, Database Upgrade Assistant, oracle 11.1.0.6.0 installation upgrade, oracle 11g install, Oracle 11g Net Asistant, oracle 11g tns create, oracle enterprise manager console 11g, SOA suite version oracle, upgrade oracle 10g to 11g | 1 Comment

6 Votes

http://debaatobiee.wordpress.com/2009/10/17/step-by-step-upgrading-oracle-10g-to-oracle-11g/#comments

http://debaatobiee.wordpress.com/tag/upgrade-oracle-10g-to-11g/

http://debaatobiee.wordpress.com/tag/soa-suite-version-oracle/

http://debaatobiee.wordpress.com/tag/oracle-enterprise-manager-console-11g/

http://debaatobiee.wordpress.com/tag/oracle-enterprise-manager-console-11g/

http://debaatobiee.wordpress.com/tag/oracle-11g-tns-create/

http://debaatobiee.wordpress.com/tag/oracle-11g-net-asistant/

http://debaatobiee.wordpress.com/tag/oracle-11g-install/

http://debaatobiee.wordpress.com/tag/oracle-11-1-0-6-0-installation-upgrade/

http://debaatobiee.wordpress.com/tag/oracle-11-1-0-6-0-installation-upgrade/

http://debaatobiee.wordpress.com/tag/database-upgrade-assistant/

http://debaatobiee.wordpress.com/tag/creating-oracle-11g-tns/

http://debaatobiee.wordpress.com/tag/11g-enterprise-manager-emca/

http://debaatobiee.wordpress.com/tag/11g-enterprise-manager-emca/

http://debaatobiee.wordpress.com/tag/11g-emca-configuration/

http://debaatobiee.wordpress.com/tag/10g-r1-to-11g-upgrade/

http://debaatobiee.wordpress.com/category/oracle11g/

http://debaatobiee.wordpress.com/category/oracle-in-windows/

mailto:system@database

mailto:system@database

http://www.oraclepoint.com/oralife/wp-content/uploads/2010/07/Generate_Create_Tablespaces.sql

Hell ! Yes , my Oracle 10g in Windows XP SP2 is the older one i.e 10.1.0.2 which doesn’t Support the

SOA Suite installation . Oracle suggest to upgrade this to 10R2 (10.2.0.4 or higher ) . But bit lethargic

about this upgrade as I do have to again download the Patch from Metalink i.e of size ~972 MB .

Since I have had Oracle 11g installer so best possible option is to throw my Oracle 10g DB and install

11g afresh . But again the pain is to export all Db objects and again import into new DB .What if the

new DB failed to import in 11g ? What if there is Unicode/Non-Unicode character set incompatibility

occurs ? I know patchset available but I can’t rely on my knowledge about applying those pathset

successfully !

After lots of brainstorming the final thought come is that there might be some way to upgrade the DB

from already installed 10g to 11g using the available installer rather using the upgrade patchset for

11g. So finally kickoff 11g installation after executing the setup file and follow the onscreen

instruction step by step .

Here you goes …

—————————————————————————————–

http://debaatobiee.files.wordpress.com/2009/10/oracle-11g-install-1.png


—————————————————————————————-

So I have installed 11g softwares without having any Database . My aim is to upgrade the existing

10g instance as 11g .

1) Copy file : utlu111i.sql to d:\temp (any temporary location) from “D:\Oradb11g\RDBMS\ADMIN” .








2) Connected into Oracle 10g instance while DB is up and running and execute the .sql file from SQL

prompt and get the below stat .

3) To resolve the below warning :

WARNING: –> Database is using an old timezone file version.

…. Patch the 10.2.0.1.0 database to timezone file version 4

…. BEFORE upgrading the database. Re-run utlu111i.sql after

…. patching the database to record the new timezone file version.

Execute :

SQL> select * from v$timezone_file;

SQL> SELECT CASE COUNT(DISTINCT(tzname))

WHEN 183 then 1

WHEN 355 then 1



WHEN 347 then 1

WHEN 377 then 2

WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end

WHEN 185 then 3

WHEN 386 then 3

WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end





ELSE 0 end VERSION

FROM v$timezone_names;

If the output of this is < 4 then prior to upgrade we need to apply the Timezone patch .So I need to

apply it prior to go ahead . For my Windows 32 bit the patch(5731187) could be found at Metalink.

Download the file : p5731187_10105_WINNT.zip and un-archive to location as : D:\temp\5731187 .

4) Copy the files from location : D:\temp\5731187\files\oracore\zoneinfo and paste it to existing

Oracle 10g location “D:\oracle\oracore\zoneinfo” . Make sure to keep backup of existing folder so that

you could revert if any problem occurs .

5) Stop the DB instance and Start . After that execute Step 2&3 again and the warning should be

removed and timzone file version should be upgraded. Otherwise assist Oracle Support .

6) To remove below warning execute the below command from sysdba users .

WARNING: –> Database contains stale optimizer statistics.

…. Refer to the 11g Upgrade Guide for instructions to update

…. statistics prior to upgrading the database.

http://updates.oracle.com/download/5731187.html


…. Component Schemas with stale statistics:

…. SYS

…. SYSMAN

Gather Dictionary stats:

Connect as sys user and gather statistics


SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(’SYS’);


SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(’SYSMAN’);


7) I skip the other warnings .To avoid showing other warnings use Oracle Documentation about Pre-

Upgrade Information

Final) Now lets perform the Database upgrade steps using : Database Upgrade Assistant(DBUA) a

GUI to upgrade the DB and it can be found under Start -) All Programs -) Oracle 11g Home -)

Configuration and Migration Tool and follow through the onscreen steps ….

http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/upgrade.htm#i1011372

http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/upgrade.htm#i1011372

http://debaatobiee.files.wordpress.com/2009/10/upgrade-10g-to-11g-using-dbua-0.png




N.B : You if are interested about the Manual upgradation using the scripts rather using the DBUA you

can take the reference of onlineappsdba or Advait Blog .

After running for quite a long time (more than ~1 hr) the upgrade finishes . But alas ! life is no longer

easy and another problem crops up .Enterprise Manager console is not getting configured as there

are some problem with 11g TNS service .Apart from this, everything looks fine after post upgradation

messages !

http://advait.wordpress.com/2007/09/07/upgrading-to-oracle-database-11g/

http://onlineappsdba.com/index.php/2009/01/22/upgrade-oracle-database-10g-to-11g-r1-111x/






So lets wash hands and think more . The problem was I supposed to delete the Oracle11g TNS

Service for 11g from Windows Registry after Software installation only and before creating the DB

using DBUA .That is the reason why the TNS fails and this inturn cause the configuration failure for

EMCA . The nuisance created by me and need to solve by me only . This problem might not crops

up in your case ,I suppose .Otherwise follow the below steps to create the TNS . Run the “Net

Configuration Assistant” from Program files OracleHome entry of start menu .Otherwise run : D:\

Oradb11g\bin\launch.exe .

Create the TNS service is pretty easy there .Add new service with a name and the entry will be added

automatically in Windows service list . Check by running : services.msc from Windows Run option .

N.B : Dont try to tweak registry and copy 10g TNS listener or modify it to point to 11g . Its pretty

hard believe me until you dont know the impact in Windows Heart … i.e at Registry ! Also don’t try to

create service manually by using sc.exe windows utility . Rely on Oracle Native Net Config Assistant

all time

Hence … My Oracle 11g TNS is ready …DB is up and running fine…See below :

Now lets resolve the problem of EMCA.Follow the below command line utilities to drop the

Enterprise Manager Repository and reconfigure it .Its pretty simple …again if you know the right way

of doing it and finally reconfigure it .


And finally all is done and lets Cheers !

Follow Me on:

Upgrade Steps From 10g to 11gR2

1. BACKUP BINARIES, DB,TNSNAMES,LISTENER,INIT,SPFILE,PWFILE THAT ARE BEING UPGRADED

2. COMPATIBLE PARAMETER MUST BE SET TO MINIMUM 10.0.0.0. THE RECOMMENDED IS 11.2.0 FOR 11gR2 PARAMETER FILE.

Part A.

Install the New Oracle Database Software & Apply any patches necessary.

Part B.




Run the Pre-Upgrade Information Tool (You Must Run This tool)

1. Copy the Pre-Upgrade Information Tool (utlul12i.sql) from the Oracle Database 11gR2 directory: $ORACLE_HOME/rdbms/admin to a temporary directory /tmp.

2. Set your environment to the one that is being upgraded. Assuming 10g.3. Change directory to /tmp that you copied utlu112i.sql to in Step 1.4. Start SQL*Plus and login as ‘/ as sysdba’5. Spool the results to a log file:

o SQL> SPOOL upgrade_info.log6. Run the Pre-Upgrade Information Tool:

o SQL> @utlul12i.sqlo SQL> SPOOL OFF

Check the output of the Pre-Upgrade Information Tool in upgrade_info.log.

The following is an example of the output generated by the Pre-Upgrade Information Tool:

Oracle Database 11.2 Pre-Upgrade Information Tool 10-14-2008 23:25:25

.

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

Database:

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

--> name: ORCL

--> version: 10.2.0.2.0

--> compatible: 10.2.0.2

--> blocksize: 8192

Run the Pre-Upgrade Information Tool

Upgrading to the New Release 3-9

--> platform: Linux IA (32-bit)

--> timezone file: V2

.

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

Tablespaces: [make adjustments in the current environment]

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

WARNING: --> SYSTEM tablespace is not large enough for the upgrade.

.... currently allocated size: 560 MB

.... minimum required size: 910 MB

.... increase current size by: 350 MB

.... tablespace is NOT AUTOEXTEND ENABLED.

--> UNDOTBS1 tablespace is adequate for the upgrade.


.... AUTOEXTEND additional space required: 352 MB

--> SYSAUX tablespace is adequate for the upgrade.



--> TEMP tablespace is adequate for the upgrade.



--> EXAMPLE tablespace is adequate for the upgrade.


.

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


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

WARNING: --> "sga_target" needs to be increased to at least 388 MB

.

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

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

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

WARNING: --> "plsql_compiler_flags" old value was "INTERPRETED";

new name is "plsql_code_type" new value is "INTERPRETED"

.

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

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

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

--> "max_enabled_roles"

--> "remote_os_authent"

--> "background_dump_dest" replaced by "diagnostic_dest"

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

--> Messaging Gateway [upgrade] VALID

--> OLAP Analytic Workspace [upgrade] VALID

--> OLAP Catalog [upgrade] VALID

--> Oracle Label Security [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


3-10 Oracle Database Upgrade Guide

--> Data Mining [upgrade] VALID

--> Expression Filter [upgrade] VALID

--> Rule Manager [upgrade] VALID

--> Oracle Application Express [upgrade]

--> 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 10.2.0.2.0 database timezone version

.... to the latest version which comes with the new release.

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

.... WMSYS

.... CTXSYS

WARNING: --> Database contains INVALID objects prior to upgrade.

.... The list of invalid SYS/SYSTEM objects was written to

.... registry$sys_inv_objs.

.... The list of non-SYS/SYSTEM objects was written to

.... registry$nonsys_inv_objs.

.... Use utluiobj.sql after the upgrade to identify any new invalid

.... objects due to the upgrade.

.... USER PUBLIC has 7 INVALID objects.

.... USER FLOWS_010600 has 1 INVALID objects.

.... USER SYS has 1 INVALID objects.

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.

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.

.

WARNING: --> There are materialized view refreshes in progress.

.... Ensure all materialized view refreshes are complete prior to upgrade.

WARNING: --> There are files which need media recovery.

.... Ensure no files need media recovery prior to upgrade.

WARNING: --> There are files in backup mode.

.... Ensure no files are in backup mode prior to upgrade.

WARNING:--> There are outstanding unresolved distributed transactions.

.... Resolve outstanding distributed transactions prior to upgrade.

WARNING:--> A standby database exists.

.... Sync standby database prior to upgrade.

WARNING: --> log_archive_format must be updated.

.... As of 10.1, log_archive_format requires a %r format qualifier

.... be present in its format string. Your current setting is:

.... log_archive_format='%t_%s.dbf'.

.... Archive Logging is currently OFF, but failure to add the %r to the

.... format string will still prevent the upgraded database from starting up.

WARNING:--> recycle bin in use.

.... Your recycle bin is turned on and it contains

.... 3 object(s). It is REQUIRED

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

.... your database.


Upgrading to the New Release 3-11

.... The command: PURGE DBA_RECYCLEBIN

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

Check the warning messages and rectify them as needed. The following points must be addressed correctly otherwise there maybe problems.

Example: CONNECT role now only has create session privilege.

If you have any db links with passwords, you may want to back up the sys.link$ table to save the information.

Oracle recommends gathering stats before the upgrade: EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

■ Deprecated CONNECT Role

■ Access Control to Network Utility Packages

■ Database Links with passwords

■ TIMESTAMP WITH TIME ZONE Data Type

■ Optimizer Statistics

■ Invalid Objects

■ Save Oracle Enterprise Manager Database Control Data

■ Complete Materialized View Refreshes

■ Ensure No Files Need Media Recovery

■ Ensure No Files Are in Backup Mode

■ Resolve Outstanding Distributed Transactions

■ Sync Standby Database with the Primary Database

■ Purging the Database Recycle Bin

Part C.

Prepare the new 11gR2 ORACLE_HOME

1. Copy configuration files (init file, tnsnames, listener, pwfile) from old Oracle Home to new.

2. Update init files with new COMPATIBLE parameter, fix any deprecated ones, and adjust the values to at least the minimum values indicated by the Pre-Upgrade Tool.

3. Update any relative path names in parameter file to fully path names.

Part D.

Upgrade the Database

1. Shutdown the database:o SQL> SHUTDOWN IMMEDIATE;

2. Make sure the following checks:o The oratab file points to Oracle Database 11g Release 2 Oracle Homeo The following environment variables point to the Oracle 11g Release 2

directories: ORACLE_HOME PATH

3. Change to the $ORACLE_HOME/rdbms/admin directory and start SQL*Pluso sqlplus ‘/ as sysdba’

4. Start the instance by issuing following command (you may get messages that parameters are obsolete, fix those and start up the db again):

o SQL> STARTUP UPGRADE;o SQL> SPOOL upgrade.log

5. Run the catupgrd.sql script:o SQL> @catupgrd.sql

6. Once completed, shutdown the database and restart it.o SQL> SHUTDOWN IMMEDIATE;o SQL> STARTUP;

7. Run the Post-Upgrade Status Tool to provide a summary of the upgrade. (If there are any INVALID components, then check upgrade manual for fixes.)

o SQL> @utlul12s.sql8. Run catuppst.sql to perform upgrade actions that do not require db to be in

upgrade mode:o SQL> $ORACLE_HOME/rdbms/admin/catuppst.sql

9. Run utlrp to recompile any remaining stored PL/SQL and other objects.o SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql

10. Verify that all objects are valid:o SQL> SELECT count(*) FROM dba_invalid_objects;o SQL> SELECT distinct object_name FROM dba_invalid_objects;

Upgrade Oracle Database 10g to 11g Manual UpgradeWe are going to use Manual Upgrade

Install 11g database software in different ORACLE_HOME from source Database

oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> pwd/opt/app/oracle/product/11.1/rdbms/adminoracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> ls -lt utlu111i.sql-rw-r--r-- 1 oracle oinstall 138636 2007-07-11 09:01 utlu111i.sqloracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> pwd/opt/app/oracle/product/11.1/rdbms/adminoracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 27 09:28:04 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> spool anujupgrade.lst

SQL> @utlu111i.sql Oracle Database 11.1 Pre-Upgrade Information Tool 04-27-2010 09:29:25. ********************************************************************** Database: ********************************************************************** --> name: VIHAAN --> 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.... MDSYSWARNING: --> 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 networkpackages..... 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..


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

select object_name, owner, object_type from all_objects where status like 'INVALID';

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

OBJECT_NAME OWNER------------------------------ ------------------------------OBJECT_TYPE

-------------------DBMS_REGISTRY SYSPACKAGE BODY

DBA_REGISTRY_DATABASE SYSVIEW

DBA_REGISTRY_DATABASE PUBLICSYNONYM

col COMP_NAME format a50set linesize 200set pagesize 200SQL> r1* select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS-------------------------------------------------- ------------------------------ --------------------------------------------Oracle Database Catalog Views 10.2.0.4.0 VALIDOracle Database Packages and Types 10.2.0.4.0 VALIDOracle Workspace Manager 10.2.0.4.3 VALIDJServer JAVA Virtual Machine 10.2.0.4.0 VALIDOracle XDK 10.2.0.4.0 VALIDOracle Database Java Packages 10.2.0.4.0 VALIDOracle Expression Filter 10.2.0.4.0 VALIDOracle Data Mining 10.2.0.4.0 VALIDOracle Text 10.2.0.4.0 VALIDOracle XML Database 10.2.0.4.0 VALIDOracle Rules Manager 10.2.0.4.0 VALIDOracle interMedia 10.2.0.4.0 VALIDOLAP Analytic Workspace 10.2.0.4.0 VALIDOracle OLAP API 10.2.0.4.0 VALIDOLAP Catalog 10.2.0.4.0 VALIDSpatial 10.2.0.4.0 VALIDOracle Enterprise Manager 10.2.0.4.0 VALID

17 rows selected.

1.7 If you are using spfile, create pfileSQL> create pfile from spfile ;

This will create pfile 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′

vihaan.__db_cache_size=377487360vihaan.__java_pool_size=4194304vihaan.__large_pool_size=4194304vihaan.__shared_pool_size=142606336vihaan.__streams_pool_size=0*.control_files='/opt/app/oracle/datafile/vihaan/control01.ctl','/opt/app/oracle/datafile/vihaan/control02.ctl','/opt/app/oracle/datafile/vihaan/control03.ctl'*.db_block_size=8192*.db_domain='apt-amd-02'*.db_file_multiblock_read_count=16*.db_name='vihaan'*.dispatchers='(PROTOCOL=TCP) (SERVICE=vihaanXDB)'*.job_queue_processes=10*.nls_language='ENGLISH'*.nls_territory='UNITED KINGDOM'*.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='/opt/app/oracle/admin/vihaan/diagnostic'

oracle@apt-amd-02:/opt/app/oracle/product/10.2/dbs> mkdir -p /opt/app/oracle/admin/vihaan/diagnostic

oracle@apt-amd-02:/opt/app/oracle/product/10.2/dbs> cd /opt/app/oracle/admin/vihaan/oracle@apt-amd-02:/opt/app/oracle/admin/vihaan> ls -lttotal 8drwxr-xr-x 2 oracle oinstall 6 2010-04-27 09:43 diagnosticdrwxr-x--- 2 oracle oinstall 4096 2010-04-27 09:29 adumpdrwxr-x--- 2 oracle oinstall 4096 2010-04-27 09:20 udumpdrwxr-x--- 2 oracle oinstall 141 2010-04-27 09:20 bdumpdrwxr-x--- 2 oracle oinstall 35 2010-04-26 13:26 pfiledrwxr-x--- 2 oracle oinstall 6 2010-04-26 13:20 cdumpdrwxr-x--- 2 oracle oinstall 6 2010-04-26 13:20 dpdump

2. Upgrade Database

Shut down source database (10g) - Your downtime starts here

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

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

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

export ORACLE_BASE=/opt/app/oracleexport ORACLE_HOME=/opt/app/oracle/product/10.2#export ORACLE_HOME=/opt/app/oracle/product/11.1export ORACLE_SID=vihaanexport PATH=$ORACLE_HOME/bin:$PATH:.export LIBXCB_ALLOW_SLOPPY_LOCK=1export TZ=GMT

comment the oracle 10g home and uncomment 11g

2.3 Start Upgradeoracle@apt-amd-02:~> pwd/home/oracleoracle@apt-amd-02:~> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 27 09:50:25 2010



SQL> startup upgrade pfile='/tmp/initvihaan.ora' ;ORACLE instance started.

Total System Global Area 534462464 bytesFixed Size 2146112 bytesVariable Size 150995136 bytesDatabase Buffers 377487360 bytesRedo Buffers 3833856 bytesDatabase mounted.Database opened.

Check shared_pool & java_pool size, to set new values

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

after catupgrd.sql database will be shutdown .

SQL> /*****************************************************************************/SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!!SQL> */SQL> /*****************************************************************************/SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL>SQL>

SQL>SQL> DOCDOC>#######################################################################DOC>#######################################################################DOC>DOC> The above sql script is the final step of the upgrade. PleaseDOC> review any errors in the spool log file. If there are any errors inDOC> the spool file, consult the Oracle Database Upgrade Guide forDOC> troubleshooting recommendations.DOC>DOC> Next restart for normal operation, and then run utlrp.sql toDOC> recompile any invalid application objects.DOC>DOC>#######################################################################DOC>#######################################################################DOC>#SQL>SQL>SQL>SQL>SQL>SQL> Rem Set errorlogging offSQL> SET ERRORLOGGING OFF;SQL>SQL> Rem *********************************************************************SQL> Rem END catupgrd.sqlSQL> Rem *********************************************************************SQL>

=====

SQL> startup pfile='/tmp/initvihaan.ora' ;ORACLE instance started.

Total System Global Area 534462464 bytesFixed Size 2146112 bytesVariable Size 150995136 bytesDatabase Buffers 377487360 bytesRedo Buffers 3833856 bytes

Database mounted.Database opened.

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

Oracle Database 11.1 Post-Upgrade Status Tool 04-27-2010 12:11:48.Component Status Version HH:MM:SS.Oracle Server. VALID 11.1.0.6.0 00:42:49JServer JAVA Virtual Machine. VALID 11.1.0.6.0 00:20:34Oracle Workspace Manager. VALID 10.2.0.4.3 00:00:01OLAP Analytic Workspace. VALID 11.1.0.6.0 00:00:55OLAP Catalog. VALID 11.1.0.6.0 00:01:40Oracle OLAP API. VALID 11.1.0.6.0 00:00:26Oracle 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:30Oracle XDK. VALID 11.1.0.6.0 00:01:37Oracle Text. VALID 11.1.0.6.0 00:01:41Oracle XML Database. VALID 11.1.0.6.0 00:13:43Oracle Database Java Packages. VALID 11.1.0.6.0 00:00:42Oracle Multimedia. VALID 11.1.0.6.0 00:09:04

Spatial. VALID 11.1.0.6.0 00:06:41Oracle Expression Filter. VALID 11.1.0.6.0 00:00:15Oracle Rules Manager. VALID 11.1.0.6.0 00:00:13Gathering Statistics. 00:05:25Total Upgrade Time: 02:06:28


SQL>SQL> SET SERVEROUTPUT OFFSQL> SET VERIFY ON

Workaround:

These errors do not result in any data loss. Therefore, you can ignore these errors.

===============================or apply Patch 7449757

oracle@apt-amd-02:~> unzip p7449757_111070_Generic.zipArchive: p7449757_111070_Generic.zipcreating: 7449757/creating: 7449757/etc/creating: 7449757/etc/xml/inflating: 7449757/etc/xml/ShiphomeDirectoryStructure.xmlinflating: 7449757/etc/xml/GenericActions.xmlcreating: 7449757/etc/config/inflating: 7449757/etc/config/actions.xmlinflating: 7449757/etc/config/inventory.xmlinflating: 7449757/README.txtcreating: 7449757/files/creating: 7449757/files/rdbms/creating: 7449757/files/rdbms/admin/inflating: 7449757/files/rdbms/admin/i1002000.sql

oracle@apt-amd-02:~> cd 7449757/ oracle@apt-amd-02:~/7449757> pwd

/home/oracle/7449757 oracle@apt-amd-02:~/7449757> /opt/app/oracle/product/11.1/OPatch/opatch applyInvoking OPatch 11.1.0.6.0

====

SQL> @?/rdbms/admin/catuppst.sqlSQL> @?/rdbms/admin/utlrp.sql

SQL> create spfile from pfile='/tmp/initvihaan.ora' ;

File created.

Check invalid objectsSQL> select count(*) from dba_objects where status like 'INVALID';

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

Post Upgrade steps

Check status of database componentsSQL>select comp_name,version, status from dba_registry;

SQL> col COMP_NAME format a40SQL> set pagesize 200SQL> set linesize 200SQL> col STATUS format a15SQL> select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS---------------------------------------- ------------------------------ ---------------Oracle Enterprise Manager 11.1.0.6.0 VALIDOLAP Catalog 11.1.0.6.0 VALIDSpatial 11.1.0.6.0 VALIDOracle Multimedia 11.1.0.6.0 VALIDOracle XML Database 11.1.0.6.0 VALIDOracle Text 11.1.0.6.0 VALID

Oracle Data Mining 11.1.0.6.0 VALIDOracle Expression Filter 11.1.0.6.0 VALIDOracle Rules Manager 11.1.0.6.0 VALIDOracle Workspace Manager 10.2.0.4.3 VALIDOracle Database Catalog Views 11.1.0.6.0 VALIDOracle Database Packages and Types 11.1.0.6.0 VALIDJServer JAVA Virtual Machine 11.1.0.6.0 VALIDOracle XDK 11.1.0.6.0 VALIDOracle Database Java Packages 11.1.0.6.0 VALIDOLAP Analytic Workspace 11.1.0.6.0 VALIDOracle OLAP API 11.1.0.6.0 VALID

17 rows selected.

Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source (10g) oracle_home to target (11g) oracle_home

===useful link

# 429825.1 Complete Checklist for Manual Upgrades to 11gR1# 744693.1 ORA-00001 For SYS.DIANA_VERSION$ During RDBMS Upgrade From 11.1.0.6 To 11.1.0.7# 413671.1 Applying version 4 Time Zone Files on an Oracle Database# 396387.1 Workarounds when Database time zone patches are not available for your patchset# 396671.1 Usage of utltzuv2.sql before updating time zone files in Oracle 10# 730057.1 Upgrading to 11g Fails with ORA-01722: invalid number

Check Metalink note: Complete Checklist for Manual Upgrades to 11gR1 - 429825.1

Posted by Anuj Singh at Tuesday, April 27, 2010

2 comments:

Anuj Singh said...

http://www.blogger.com/profile/10590674268018229093

http://anuj-singh.blogspot.in/2010/04/upgrade-oracle-database-10g-to-11g.html



SQL> exec dbms_stats.gather_dictionary_stats;


SQL> exec dbms_stats.gather_fixed_objects_stats;


2 September 2011 16:45

Anuj Singh said...

for schema stats

exec dbms_stats.gather_schema_stats (ownname=>'PROD1', estimate_percent=>100,method_opt=>'for all columns size skewonly', degree => DBMS_STATS.AUTO_DEGREE ,cascade=>true) ;

Manually upgrade Database (10.2.0.4) to Oracle database 11g2 on RHEL 5

1. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

2. Check dba_registry

column comp_name format a40set pagesize 50set line 2000select trim(comp_name) comp_name , version , status from dba_registry;

3. Verify that all expected packages and classes are valid:

http://deepakguptadba.blogspot.com/2010/08/manually-upgrade-database-10g2-10204-to.html


http://anuj-singh.blogspot.com/2010/04/upgrade-oracle-database-10g-to-11g.html?showComment=1314978321855#c8119499519028073474


SQL> select count(*) from dba_objects where status <>’VALID’;SQL>select owner,object_type,count(*) from dba_objects where status <>’VALID’ group by owner,object_type order by 1,2;

4. Backup the database.

5. Analyze the existing instance using the

SQL>@$ORACLE_11G_HOME/rdbms/admin/utlu112i.sql script

6. Shut down the instance:


7. If your operating system is Linux or UNIX, then make the following checks:

a. The oratab file points to your Oracle Database 11g Rel. 2 Oracle home.b. Your ORACLE_SID is set correctlyc. The following environment variables point to the Oracle Database 11g2 directories:– ORACLE_HOME– PATHd. Any scripts that clients use to set the ORACLE_HOME value must point to the New Oracle home.Note: If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.

8. Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2)

Oracle home directory. At a system prompt, change to the $ORACLE_HOME/rdbms/admin directory.

9. Copy init or spfile file to 11g ORACLE_HOME.

10. Connect to the database instance as a user with SYSDBA privileges.

Start the instance by issuing the following command:


11. Set the system to spool results to a log file for later verification of success:

SQL> SPOOL upgrade.log

12. Run the catupgrd.sql script:

SQL> @catupgrd.sql This scripts runs the desired upgrade scripts and Shuts Down the database.

13. Restart the instance to reinitialize the system parameters for normal operation.

SQL> STARTUP

14. Run utlu112s.sql to display the results of the upgrade:

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

15. (Optional ) Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, toperform upgrade actions that do not require the database to be in UPGRADE mode:

SQL> @rdbms/admin/catuppst.sql

16. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

17. Verify that all expected packages and classes are valid:

SQL> SELECT count(*) FROM dba_invalid_objects;SQL> SELECT distinct object_name FROM dba_invalid_objects;

18. Exit SQL* Plus

Upgrade Oracle from 10.2.0.1 To 10.2.0.4

1. Patch Set Overview

Patch set release 10.2.0.4. Before installing this patch set you must be need 10.2.0.1 version.

2. Requirements

Oracle DB : Oracle 10.2.0.1 (later) Operating System: Windows

3. Pre - Installation Tasks

1. Identify prior installation

Before installing this patch you must install oracle 10.2.0.1 (or later version)

2. Download Patch set

Download 6810189 patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.

3. Shutdown oracle database.

C:\>set oracle_sid= GOLDLINK

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys@GOLDLINK as sysdba Enter password: Connected.

SQL> SHUTDOWN Database closed. Database dismounted. ORACLE instance shut down.

4. Stop all services

C:\>set oracle_sid=GoldLink

C:\>net stop OracleDBConsoleGOLDLINK

https://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=6810189&release=80102040&plat_lang=912P&patch_num_id=939957&[email protected]&userid=ml-roysam&

The OracleDBConsoleGOLDLINK service is stopping................ The OracleDBConsoleGOLDLINK service was stopped successfully.

C:\>net stop OracleServiceGOLDLINK The OracleServiceGOLDLINK service is stopping. The OracleServiceGOLDLINK service was stopped successfully.

C:\>net stop OracleOraDB10g_Home1iSQL*Plus.. The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

C:\>net stop OracleOraDB10g_Home1TNSListener The OracleOraDb10g_home1TNSListener service is stopping. The OracleOraDb10g_home1TNSListener service was stopped successfully.

Note: while patch installation time If you face Distributed Transaction Coordinator still running. You should be stop this service

C:\>net stop msdtc The Distributed Transaction Coordinator service is stopping. The Distributed Transaction Coordinator service was stopped successfully.

4. Backup your database & Old oracle binaries.

Oracle recommends that you create a backup of the Oracle 10g installation before you install the patch set.

--- Check Tablespace Sizes and Set Parameter Values

Review the following sections before upgrading a database.

5. Install the new patch Binaries on same Oracle Home Install the new patch binaries on same oracle home.

6. Upgrade the Database

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

1. Start all services

2. Connect sys user

C:\> sqlplus /NOLOG

SQL> CONNECT SYS/SYS_password AS SYSDBA

3. Enter the following SQL*Plus commands:


SQL> SPOOL patch.log

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql

SQL> SPOOL OFF

Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database. If necessary, rerun the catupgrd.sql script after correcting any problems.

4. Restart the database:

SQL> SHUTDOWN SQL> STARTUP

5. Compile Invalid Objects

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql


BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.4.0 - Production PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for 32-bit Windows: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production

PSU Patch_Implementation_steps 10.2.0.5_ UNIX

A common DBA task is to patch Oracle homes and databases. These patches can be one of the many bug fixes or the quarterly CPU. Regardless of the reason, the following must be done for every database the DBA is responsible for:

1. The DBA must move the patch software to the server and unzip the file.

2. Make sure the environment is setup properly to run Opatch.

3. Shutdown the listener and database.

4. Patch the Oracle home.

5. Restart the database and listener

6. Run Catcpu.sql.

7. Run Utlrp.sql to compile PL/SQL.

http://www.dba-oracle.com/t_patching_cloning_oracle_home.htm

=========================================================================================================================

step 1 --- copy the patch patch software to the server and unzip the file.

unzip p10249537_10205_LINUX.zip

step 2 --- Shutdown DB ( Shut down all database of ORACLE_HOME) & Listner

sqlplus /nolog

conn / as sysdba

SQL> create pfile from spfile;

SQL> alter database backup controlfile to trace;


$lsnrctl

LSNRCTL> status

LSNRCTL> stop

step 3 --- Take the backup of ORACLE Binaries & Database ( RMAN or COLD)

inventory backup ----------------cat /etc/oraInst.loc

tar -cvzf /u01/oradata/backup/myOracleInventoryClonePSU.tar.gz /u01/app/oracle/oraInventory

ORACLE_HOME Binaries backup---------------------------


tar -cvzf /u01/oradata/backup/myOracleHomeClonePSU.tar.gz /u01/app/oracle/product/10.2.0/db_1

Database Cold Backup---------------------tar -cvzf /u01/oradata/backup/myOracleDATABASEClonePSU.tar.gz /u01/oradata/stumarket

step 4 --- Patch Installation Instructions

#Ensure that the $PATH has the following executables: make, ar, ld, and nm.

#The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH as follows:

#export PATH=$PATH:/usr/ccs/bin

$ export PATH=$PATH:/usr/bin

#Check applied patches

$ORACLE_HOME/OPatch/opatch lsinventory

*********************************************************[oracle@aud1ora01 ~]$ $ORACLE_HOME/OPatch/opatch lsinventoryInvoking OPatch 10.2.0.4.9

Oracle Interim Patch Installer version 10.2.0.4.9Copyright (c) 2009, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/10.2.0/db_1Central Inventory : /u01/app/oracle/oraInventory from : /etc/oraInst.locOPatch version : 10.2.0.4.9OUI version : 10.2.0.5.0OUI location : /u01/app/oracle/product/10.2.0/db_1/ouiLog file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-02-14_17-36-20PM.log

Patch history file: /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2011-02-14_17-36-20PM.txt

--------------------------------------------------------------------------------Installed Top-level Products (2):

Oracle Database 10g 10.2.0.1.0Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0aThere are 2 products installed in this Oracle Home.

Interim patches (2) :

Patch 9963497 : applied on Thu Feb 10 17:58:14 GMT+05:30 2011Unique Patch ID: 13098966 Created on 2 Aug 2010, 03:38:02 hrs PST8PDT Bugs fixed: 9963497

Patch 10249537 : applied on Thu Feb 10 17:58:10 GMT+05:30 2011Unique Patch ID: 13098966 Created on 14 Dec 2010, 08:51:21 hrs PST8PDT Bugs fixed: 10249537, 9952270

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

OPatch succeeded.

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

#One-off Patch Conflict Detection and Resolution

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/10248542

*************************************************************[oracle@aud1ora01 10248542]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/10248542Invoking OPatch 10.2.0.4.9


PREREQ session



Invoking prereq "checkconflictagainstohwithdetail"

ZOP-40: The patch(es) has conflicts/supersets with other patches installed in the Oracle Home (or) among themselves.

Prereq "checkConflictAgainstOHWithDetail" failed.

Summary of Conflict Analysis:

Patches that can be applied now without any conflicts are :10248542

Following patches are not required, as they are subset of the patches in Oracle Home or subset of the patches in the given list :9963497, 10249537

Following patches will be rolled back from Oracle Home on application of the patches in the given list :9963497, 10249537

Conflicts/Supersets for each patch are:

Patch : 10248542

Bug Superset of 9963497 Super set bugs are: 9963497

Bug Superset of 10249537 Super set bugs are: 10249537, 9952270

OPatch succeeded.

*************************************************************# Update the opatch

cp p6880880_102000_LINUX.zip $ORACLE_HOME

cd $ORACLE_HOME


$ORACLE_HOME/OPatch/opatch versionInvoking OPatch 10.2.0.5.1

OPatch Version: 10.2.0.5.1

===================================

#Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:

$ cd 10248542$ $ORACLE_HOME/OPatch/opatch apply

********************************************[oracle@aud1ora01 10249537]$ $ORACLE_HOME/OPatch/opatch applyInvoking OPatch 10.2.0.4.9


UTIL session



Invoking utility "napply"Checking conflict among patches...Checking if Oracle Home has components required by patches...Skip patch 10327179 from list of patches to apply: This patch is not needed.Checking skip_duplicateChecking skip_subset

Checking conflicts against Oracle Home...OPatch continues with these patches: 10249537 9963497

Do you want to proceed? [y|n]yUser Responded with: Y

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files affected by the patch 'NApply' for restore. This might take a while...Execution of 'sh /home/oracle/10249537/10249537/custom/scripts/pre -apply 10249537 ':

Return Code = 0

Applying patch 10249537...

ApplySession applying interim patch '10249537' to OH '/u01/app/oracle/product/10.2.0/db_1'Backing up files affected by the patch '10249537' for rollback. This might take a while...

Patching component oracle.rdbms, 10.2.0.5.0...Copying file to "/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/bundledata_CPU.xml"Copying file to "/u01/app/oracle/product/10.2.0/db_1/cpu/CPUJan2011/catcpu.sql"Copying file to "/u01/app/oracle/product/10.2.0/db_1/cpu/CPUJan2011/catcpu_rollback.sql"

Patching component oracle.rdbms.rsf, 10.2.0.5.0...Copying file to "/u01/app/oracle/product/10.2.0/db_1/cpu/CPUJan2011/rollback_all.lst"Copying file to "/u01/app/oracle/product/10.2.0/db_1/cpu/CPUJan2011/rollback_new.lst"ApplySession adding interim patch '10249537' to inventory

Verifying the update...Inventory check OK: Patch ID 10249537 is registered in Oracle Home inventory with proper meta-data.Files check OK: Files from Patch ID 10249537 are present in Oracle Home.



Patching component oracle.rdbms, 10.2.0.5.0...Copying file to "/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/prvtjob.plb"ApplySession adding interim patch '9963497' to inventory


The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.[oracle@aud1ora01 10249537]$

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

Step 5 -- Post Installation Instructions

Loading Modified SQL Files into the Database--------------------------------------------

#For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:

cd $ORACLE_HOME/rdbms/adminsqlplus /nologSQL> CONNECT / AS SYSDBASQL> STARTUPSQL> @catbundle.sql psu apply

SQL> @utlrp.sql

##################################################################Check the following log files in $ORACLE_HOME/cfgtoollogs/catbundle for any errors:

catbundle_CPU_<database SID>_APPLY_<TIMESTAMP>.logcatbundle_CPU_<database SID>_GENERATE_<TIMESTAMP>.log

where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS.

Note --Check the following log file for errors:/u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/catbundle/catbundle_CPU_UMARKET_APPLY_2011Feb10_18_04_02.log

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

Step 6 -- Cross check patch implementation.


******************************************************************[oracle@aud1ora01 admin]$ $ORACLE_HOME/OPatch/opatch lsinventoryInvoking OPatch 10.2.0.4.9








Patch 9963497 : applied on Thu Feb 10 17:58:14 GMT+05:30 2011

Unique Patch ID: 13098966 Created on 2 Aug 2010, 03:38:02 hrs PST8PDT Bugs fixed: 9963497


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

OPatch succeeded.**********************************************************************************************

col action_time for a28col action for a6col namespace for a9col version for a10col id for 99999999col comments for a10

select * from registry;

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

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES---------------------------- ------ --------- ---------- --------- ---------- ------------------------------10-FEB-11 06.04.02.693640 PM APPLY SERVER 10.2.0.5 2 CPUJan2011 CPU

CPU Patch_Implementation_steps 10.2.0.5_ UNIX

A common DBA task is to patch Oracle homes and databases. These patches can be one of the many bug fixes or the quarterly CPU. Regardless of the reason, the following must be done for every database the DBA is responsible for:

1. The DBA must move the patch software to the server and unzip the file.

2. Make sure the environment is setup properly to run Opatch.

3. Shutdown the listener and database.

4. Patch the Oracle home.

5. Restart the database and listener

6. Run Catcpu.sql.

7. Run Utlrp.sql to compile PL/SQL.


=========================================================================================================================

step 1 --- copy the patch patch software to the server and unzip the file.


step 2 --- Shutdown DB ( Shut down all database of ORACLE_HOME) & Listner

sqlplus /nolog

conn / as sysdba

SQL> create pfile from spfile;

SQL> alter database backup controlfile to trace;


$lsnrctl

LSNRCTL> status

LSNRCTL> stop

step 3 --- Take the backup of ORACLE Binaries & Database ( RMAN or COLD)

inventory backup ----------------cat /etc/oraInst.loc

tar -cvzf /u01/oradata/backup/myOracleInventoryClone.tar.gz /u01/app/oracle/oraInventory

ORACLE_HOME Binaries backup---------------------------

tar -cvzf /u01/oradata/backup/myOracleHomeClone.tar.gz /u01/app/oracle/product/10.2.0/db_1

Database Cold Backup---------------------tar -cvzf /u01/oradata/backup/myOracleDATABASEClone.tar.gz /u01/oradata/stumarket

step 4 --- Patch Installation Instructions

#Ensure that the $PATH has the following executables: make, ar, ld, and nm.

#The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH as follows:

#export PATH=$PATH:/usr/ccs/bin


$ export PATH=$PATH:/usr/bin

#Check applied patches


*********************************************************[oracle@aud1ora01 10249537]$ $ORACLE_HOME/OPatch/opatch lsinventoryInvoking OPatch 10.2.0.4.9







There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.

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

#Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:

$ cd 10249537$ ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate

********************************************[oracle@aud1ora01 10249537]$ $ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicateInvoking OPatch 10.2.0.4.9


UTIL session



Invoking utility "napply"Checking conflict among patches...Checking if Oracle Home has components required by patches...Skip patch 10327179 from list of patches to apply: This patch is not needed.Checking skip_duplicateChecking skip_subsetChecking conflicts against Oracle Home...OPatch continues with these patches: 10249537 9963497

Do you want to proceed? [y|n]yUser Responded with: Y

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files affected by the patch 'NApply' for restore. This might take a while...Execution of 'sh /home/oracle/10249537/10249537/custom/scripts/pre -apply 10249537 ':

Return Code = 0



Patching component oracle.rdbms, 10.2.0.5.0...Copying file to "/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/bundledata_CPU.xml"Copying file to "/u01/app/oracle/product/10.2.0/db_1/cpu/CPUJan2011/catcpu.sql"Copying file to "/u01/app/oracle/product/10.2.0/db_1/cpu/CPUJan2011/catcpu_rollback.sql"

Patching component oracle.rdbms.rsf, 10.2.0.5.0...Copying file to "/u01/app/oracle/product/10.2.0/db_1/cpu/CPUJan2011/rollback_all.lst"Copying file to "/u01/app/oracle/product/10.2.0/db_1/cpu/CPUJan2011/rollback_new.lst"ApplySession adding interim patch '10249537' to inventory




Patching component oracle.rdbms, 10.2.0.5.0...Copying file to "/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/prvtjob.plb"ApplySession adding interim patch '9963497' to inventory

Verifying the update...Inventory check OK: Patch ID 9963497 is registered in Oracle Home inventory with proper meta-data.

Files check OK: Files from Patch ID 9963497 are present in Oracle Home.

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.[oracle@aud1ora01 10249537]$

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

Step 5 -- Post Installation Instructions

Loading Modified SQL Files into the Database--------------------------------------------

#For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:

cd $ORACLE_HOME/rdbms/adminsqlplus /nologSQL> CONNECT / AS SYSDBASQL> STARTUPSQL> @catbundle.sql cpu apply

SQL> @utlrp.sql

##################################################################Check the following log files in $ORACLE_HOME/cfgtoollogs/catbundle for any errors:

catbundle_CPU_<database SID>_APPLY_<TIMESTAMP>.logcatbundle_CPU_<database SID>_GENERATE_<TIMESTAMP>.log

where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS.

Note --Check the following log file for errors:/u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/catbundle/catbundle_CPU_UMARKET_APPLY_2011Feb10_18_04_02.log

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

Step 6 -- Cross check patch implementation.


******************************************************************[oracle@aud1ora01 admin]$ $ORACLE_HOME/OPatch/opatch lsinventoryInvoking OPatch 10.2.0.4.9


Oracle Home : /u01/app/oracle/product/10.2.0/db_1Central Inventory : /u01/app/oracle/oraInventory from : /etc/oraInst.locOPatch version : 10.2.0.4.9OUI version : 10.2.0.5.0OUI location : /u01/app/oracle/product/10.2.0/db_1/oui

Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-02-10_18-07-49PM.log






Patch 9963497 : applied on Thu Feb 10 17:58:14 GMT+05:30 2011Unique Patch ID: 13098966 Created on 2 Aug 2010, 03:38:02 hrs PST8PDT Bugs fixed: 9963497


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

OPatch succeeded.**********************************************************************************************

col action_time for a28col action for a6col namespace for a9col version for a10col id for 99999999col comments for a10

select * from registry;

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

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES---------------------------- ------ --------- ---------- --------- ---------- ------------------------------10-FEB-11 06.04.02.693640 PM APPLY SERVER 10.2.0.5 2 CPUJan2011 CPU

UPGRADATION 10gR2 to 11gR1Pre-Requisite:

You should have the Oracle database 10g, which you want to migrate.OS level should be RHEL 4.0Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6)

Step 1) Installing Oracle 11g Home

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle

home as a separate ORACLE_HOME in parallel to 10g Oracle Home.

Step 2) Pre-Upgrade Utility

In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.

for more about utlu111i.sql : http://oracleflash.com/38/Oracle-11g-Release-1-Pre-Upgrade-tool-utlu111i-sql.html

Step 3) Executing the recommended steps

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle HomeWARNING: –> Database is using an old timezone file version.…. Patch the 10.2.0.1.0 database to timezone file version 4…. BEFORE upgrading the database. Re-run utlu111i.sql after…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files:


version

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

2

here oracle 10.2.0.1 has timezone file version of 2

Before you upgrade your timezone file version, you must run utltzuv2.sql to see if there is data

that will be affected by the timezone file version upgrade. The script utltzuv2.sql creates a table

named sys.sys_tzuv2_temptab, and fills it with data that may get affected by the timezone file

version.

SQL > select * from sys.sys_tzuv2_temptab ;

no rows selected

http://oracleflash.com/38/Oracle-11g-Release-1-Pre-Upgrade-tool-utlu111i-sql.html

here we see no rows but if we see any rows then we need to take that data backup and restore after timezone file version upgrade

Step 4) Run Pre-Upgrade Utility again

Run utlrp.sql to validate the objects if there is any invalidate objects.

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

To View the INVALID Objects use:

SQL> select count(*) from dba_objects

Create pfile from spfile --It creates pfile for database in ORACLE_HOME/dbs location(init<dbname>.ora)

Shutdown -shut down the database in oracle 10g.

Copy the pfile of database from ORACLE_HOME/dbs(10g) to ORACLE_HOME/dbs(11g) using cp command.

In that make changes shown below,

1. Remove BDUMP, CDUMP, UDUMP and their locations

2. Add Diagnostic_dest=/’$ORACLE_BASE/diag’-location for diagnostic dest

3. Change audit_file_dest location

4. Change compatibility to 11g and save it.

After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings. If everything looks fine, shut down the database from 10g Oracle Home.

Step 5) Starting Upgrade

Source the following variables for 11g Oracle Home[oracle@ocvmrh2178 db_1]$ export ORACLE_HOME=/home/oracle/oracle/product/product/11.1.0/db_1[oracle@ocvmrh2178 db_1]$ export PATH=$ORACLE_HOME/bin:$PATH[oracle@ocvmrh2178 db_1]$ export ORACLE_SID=orcl[oracle@ocvmrh2178 db_1]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba

sqlplus “/ as sysdba” –> will be connected to idle instance


SQL> SPOOL upgrade.logSQL> @catupgrd.sql

Once the upgrades finishes. It will shut down the database automatically.Login again as sysdba and startup in normal mode.

Check the dba_registry for the components and its status

SQL> select COMP_NAME, STATUS, VERSION from DBA_REGISTRY;

Step 6) Post-Upgrade Steps

Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.SQL> STARTUP

Run utlu111s.sql to display/verify the results of the upgrade:

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

Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:

SQL> @?/rdbms/admin/catuppst.sqlRun utlrp.sql to recompile invalid objects

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

10g to 11g

Documents

oraclebaseoraclehomerdbmsadminutlrp

oraclebaseoraclehomerdbmsadmincatupgrd

disconnection

sqlplus nolog

sqlgt

oracleaud1ora01

remaining

olap analytic