Upgrade db 11gr1 to 11gr2 on windows

Dear All,

I am upgrading database from 11gr1 to 11gr2 on windows 2003r2.

my database configured with DATA GUARD and i am upgrade PRIMARY DATABASE.

during the upgrade through DBUA getting error message 'ORA-16024 : LOG_ARCHIVE_DEST_1='LOCATION= XXXXX' CAN'T PARSED. AND upgrade process abort.

Actually this was happen due to bug.

Kindly find the below details.

Upgrading a 11.1.0.6 database which is part of Data Guard environment to
11.1.0.7 using dbua.

The log_archive_dest parameters are written on more than one line in the
11.1.0.6 database parameter file, for example.,

log_archive_dest_1       = "LOCATION=USE_DB_RECOVERY_FILE_DEST
                      VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
                      DB_UNIQUE_NAME=testp"
log_archive_dest_2       = "SERVICE=p9secs ASYNC COMPRESSION=ENABLE
                      VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)
                      DB_UNIQUE_NAME=tests"

While upgrading to 11.1.0.7 using DBUA, these parameters are truncated to a
single line as below

log_archive_dest_1       = "LOCATION=USE_DB_RECOVERY_FILE_DEST"
log_archive_dest_2       = "SERVICE=p9secs ASYNC COMPRESSION=ENABLE"

Since the init parameters are truncated by dbua, this has caused problems
with the rolling upgrade in the data guard environment for the customer

Bug: Bug 8225933: DBUA TRUNCATES INITIALIZATION PARAMETER(S)

Real Time Exprience about DB Upgrade

Dear friends,

I just upgraded our production box (windows & linux) from 10gr1 (10.1.0.2.0) to 10gr2 (10.2.0.4.0).

I want to share upgrade procedure with you all.

Keep in mind there is two types of upgrade procedure

1. From lower oracle verion to higher oracle version

For eg:

Oracle 8i,9i,10g to oracle 11g

or

Oracle 8i,9i to oracle 10g

or

Oracle 8i to oracle 9i

or

so on.

2. patch set or cpu(critical patch update) upgrade.

it is always apply on same oracle version means if we are using oracle 10gr1 then we can apply only latest patchset for 10gr1

For eg:

If we are using oracle 8i (8.1.0) then we can apply only latest patch set for 8i is (8.1.7.4) patchset

If we are using oracle 9ir1(9.0.1) then we can apply only latest patch set for 9ir1 is(9.0.1.4) (not sure about latest patchset )

If we are using oracle 9ir2(9.2.0) then we can apply only latest patch set for 9ir2 is (9.2.0.7) same like10gr1 ---10.1.0.5.0, 10gr2 ---10.2.0.4.0 , 11gr1 ---N/A

according to me apply patchset is easy and safest task compare to upgrade from lower oracle version to higher oracle version.

Oracle enhanced with every new release so some feature in lower version are obsolete & desupported in higher version. Same like in higher version new features are introduced which are not exist in lower version. And upgrade/migrate is one of the important task for DBA. so we have to do it

I always recommended never ever upgrade directly PRODUCTION box without prior testing on TEST/DEV server.

ORA-01422 + ORA-06512 + "SYSMAN.MGMT_TIME_SYNC"

Platform: Windows2003

During Patchset applying process from 10gr2(10.2.0.1.0) to 10gr2(10.2.0.4.0) "catupgrd.sql" ended with below error.

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 193

ORA-06512: at line 2

All oracle component was successfully upgraded except OEM becuase of above error.

Clause: After searching on metalink and google didn't find proper reason why it is happened.

Solution: Re-Run catupgrd.sql script

ksdpec: called for event 13740 prior to event group initialization

After upgrade db from 10gr1 to 10gr2 I got the following message in alertlog file during db startup time only.

ksdpec: called for event 13740 prior to event group initialization

starting up ORACLE RDBMS Version: 10.2.0.1.0

After searching on metalink and google I found below metalink note.

Metalink Note: 342505.1

http://oracleappstechnology.blogspot.com/2008/03/ksdpec-called-for-event-13740-prior-to.html

'Lb_library_path Is Not Set' On Startup

After successfully upgrade db from 10gr1(10.1.0.3.0) to 10gr2 (10.2.0.1.0) I got below warning message during database startup

Sat Aug 9 17:52:09 2008

Starting ORACLE instance(normal)

Cannot determine all dependent dynamic libraries for /proc/self/exe

Unable to find dynamic library libocr10.so in search paths

after searching on metalink I found below metalink note.

Metalink Note: 371408.1

According to metalink note it is a BUG:4918834.

Soluction: Set LD_LIBRARY_PATH enviourment variable.

$export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

Memory Notification: Library Cache Object Loaded Into SGA

After upgrade db from 10gr1 to 10gr2(10.2.0.1.0) on linux server I got below warning message in alert_sid.log file.

Tue Aug 12 11:34:37 2008Memory Notification: Library Cache Object loaded into SGAHeap size 2205K exceeds notification threshold (2048K)

After searching on metalink and google I found below metalink note and link.

Metalink Note: 330239.1

http://sabdarsyed.blogspot.com/2007/03/warning-memory-notification-library.html

Upgrade DB from 10gr1 to 10gr2

Yesterday I upgrade our production box from 10gr1 to 10gr2 on linux platform.

Old Version: 10.1.0.3.0

New Version: 10.2.0.1.0

Platform: Linux 4

I am going to share with you short notes for upgrade process which i follow.

Steps:

1. Take backup of Oracle DB / Ora Inventory / Ora_home of old version

2. Install New oracle version with Seperate ORACLE_HOME directory

3. Run Pre-Upgrade Utility on database

4. Run catupgrd.sql script on database Under New Oracle Home.

5. Run utlrp.sql script for compile all invalid objects

6. Bounce the database (shutdown normal + startup)

7. Create spfile or change location of redolog or datafile if these files exists in "old oracle home".

8. Remove the old oracle home folder.

9. shutdown the database startup with spfile in mount mode then change location of datafile/redolog file then open the database

10. Take the database full backup.

11. Create the new temporary tablespace if old temporary tablespace is exist on old oracle home and make default temporary tablespace to database , rename the temp tablespace name

check also below metalink note: http://dbataj.blogspot.com/2008/08/ora-00959-tablespace-deleted00-does-not.html

Above steps is only short details for upgrade.

Kindly visit Oracle official upgrade guide http://tahiti.oracle.com/

Database & Patch set Upgrade

Database & patch upgrade is one of the most important work for Database Administrator.

Before going further first understand what are “upgrade” & “migration” & “patch set” & “CPU patch” word.

Upgrade:
Usually use for oracle database upgrade.
Eg: upgrade from oracle 9i to oracle 10g.

Migration:
Usually use for migrate non oracle database to oracle database.
Eg: From SQL server database to oracle database

Patch set:
There are two types of patch upgrade
1. Patch set update
From one oracle version to another oracle version
Eg: oracle 9i to oracle 10g

NOTE: Patch set based on oracle base release
Eg: oracle 10gr1 (10.1.0.2.0) and for 10gr2 (10.2.0.1.0)

2. CPU patch (Critical Patch update)
Patches is apply for fix database bug suppose after apply latest patch set for current release if there is any bug occur then oracle release cpu patches in regular interval to fix those bug.
Eg: oracle 10gr1 base rel: 10.1.0.2.0, Latest Patch set: 10.1.0.5.0, Latest CPU patch: Jan-08 CPU patch for 10.1.0.5.0

NOTE: CPU patch based on latest patch set.
For more info: http://www.oracle.com/technology/deploy/security/alerts.htm
How to upgrade database to oracle 10gr2?

Determine the Upgrade Path to the New Oracle Database 10g Release
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i1007814

Before upgrading process we need to define upgrade method
There are four methods to upgrade our database
1. DBUA (Database upgrade assistant)
2. Manually upgrade
3. Export/Import
4. Data copying
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i694345
Patch set upgrade example:
1. From oracle 9ir2 (9.2.0.2 to 9.2.0.8) on Windows http://babudba.blogspot.com/2007/11/patch-installation.html
2. From oracle 9ir2 (9.2.0.1 to 9.2.0.7) on Solaris
http://sabdarsyed.blogspot.com/2007/02/upgrade-oracle-database-9201-to-9207-on.html
3. From oracle 10gr1 (10.1.0.2 to 10.1.0.5) on windows
http://dbataj.blogspot.com/2007/06/upgrading-database-from-101020-to.html

4. From oracle 10gr2 (10.2.0.2 to 10.2.0.3) on windows
http://babudba.blogspot.com/2007/11/upgrade-oracle-10202-to-10203.html

5. From oracle 10gr2 (10.2.0.1 to 10.2.0.3) on Linux
http://sabdarsyed.blogspot.com/2007/03/upgrading-oracle-database-server-10g.html

Critical Patch update example:
On Oracle 10gr1 (10.1.0.5.0) patch set
http://dbataj.blogspot.com/2007/06/critical-patch-update.html

Upgrading Database From 10.1.0.2.0 to 10.1.0.5.0

Example : Below procedure based on our test enviourment if you follow this instruction please go through README.TXT file comes with PATCH SET.





Upgrading Oracle Database Server 10g Release 10.1.0.2 to Oracle Database 10g Release 10.1.0.5 on Windows XP Sp2



Patch Set Overview

You can apply the Oracle Database 10g release 10.1.0.5 patch set to the following Oracle Database 10g release 1 (10.1.0.2, 10.1.0.3, or 10.1.0.4) installations.

Oracle Universal Installer Version Requirements

This patch set includes Oracle Universal Installer release 10.1.0.5, which is also installed when you install this patch set.

System Requirements

Hardware requirement : Intel Pentium
OS requirement : Windows XP Prof
Oracle DB : 10gr1(10.1.0.2) or later

Preinstallation Tasks

1. Identify the Oracle Database Installation


This is not a complete software distribution. You must install it in an existing Oracle Database 10g Oracle home.


2. Download and Extract the Installation Software


Download and extract the p4505133_10105_WINNT.zip patch set.


3. Shut Down Oracle Databases


C:\>sqlplus sys@ORCL/pwd as sysdba
SQL>shutdown immediate;

C:\>sqlplus sys@CATDB/pwd as sysdba
SQL>shutdown immediate;


Stop All Services


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


C:\>net stop OracleServiceORCL
The following services are dependent on the OracleServiceORCL service.
Stopping the OracleServiceORCL service will also stop these services.

OracleDBConsoleorcl

Do you want to continue this operation? (Y/N) [N]: Y
The OracleDBConsoleorcl service is stopping....
The OracleDBConsoleorcl service was stopped successfully.

The OracleServiceORCL service is stopping.
The OracleServiceORCL service was stopped successfully.

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


C:\>LSNRCTL stop

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 13-JUN-2007 16:30
:58

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

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

C:\>net stop OracleCSService

The OracleCSService service was stopped successfully.


Back up the SYSTEM



Installation Tasks


1. Run SETUP.EXE from where you download/extract your patch.





In the Name field in the Destination section, select the 10.1.0.x Oracle home that you want to update from the drop down list, then click Next.





On the Summary screen, click Install.



Upgrading Release 10.1 to Oracle Database 10g Release 1 (10.1.0.5)


1.Check SYSTEM TBS should have 10mb Free Space.
2.SHARED_POOL_SIZE or JAVA_POOL_SIZE at least 150 MB.

SQL> alter tablespace SYSTEM
2 add datafile 'c:\oracle\product\10.1.0\oradata\orcl\system02.dbf' size 20m;


Tablespace altered.

SQL> alter system set SHARED_POOL_SIZE = 150M SCOPE=spfile;

System altered.

SQL> alter system set JAVA_POOL_SIZE = 150M SCOPE=spfile;

System altered.


SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP UPGRADE
ORACLE instance started.

Total System Global Area 356515840 bytes
Fixed Size 789400 bytes
Variable Size 330036328 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> SPOOL patch.log

SQL> @C:\oracle\product\10.1.0\db_1\rdbms\admin\catpatch.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following PL/SQL block will cause an ORA-20000 error and
DOC> terminate the current SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

PL/SQL procedure successfully completed.
....
[OUTPUT CUT]

SQL> SPOOL OFF


Restart the database

SQL>SHUTDOWN
SQL>STARTUP


Run the utlrp.sql script to recompile all invalid PL/SQL packages


SQL> @C:\oracle\product\10.1.0\db_1\rdbms\admin\utlrp.sql


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for 32-bit Windows: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production



Known Issues


select dbms_java.full_ncomp_enabled from dual
*
ERROR at line 1:
ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for
instructions.
ORA-06512: at "SYS.DBMS_JAVA", line 236


IGNORE
---> If you do not use Java in the database, then ignore this error message.

Don't Ignore
--->If you use Java in the database, then install the NCOMP libraries by selecting the Oracle Database 10g Products installation type from the Oracle Database Companion CD.


If RMAN CATALOG is also configure then upgrade RMAN CATALOG


C:\>rman CATALOG=rman/cat@CATDB

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to recovery catalog database

RMAN> UPGRADE CATALOG;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> UPGRADE CATALOG;

recovery catalog upgraded to version 10.01.00
DBMS_RCVMAN package upgraded to version 10.01.00
DBMS_RCVCAT package upgraded to version 10.01.00