Tuesday, September 30, 2008

Uninstalling Oracle 10g Manually from Windows XP/Windows 2003 Server

Uninstalling Oracle 10g Manually from Windows XP/Windows 2003 Server

I have uninstalled Oracle 10g manually for my Windows XP and Windows 2003 server. Here are the steps bellow:

Step 1:
Use Oracle Universal installer to deinstall Oracle.
Follow the steps and select correct oracle home to remove it.

Step 2:

After running the supplied Oracle uninstallation utility (which may or may not do some or all of the following):
• Stop any Oracle services that have been left running.
Start->Settings->Control Panel->Services
Look for any services with names starting with 'Oracle' and stop them.

Step 3:
Take the backup of your registry in case you accidentally delete other thing (wrongly delete any entry in registry might let to reinstall whole server.)

In DOS command prompt type regedit
Move the icon in my computer then file-export

Step 4:
• Run regedit and delete the following keys (some may have slightly different names in your registry):
HKEY_CURRENT_USER\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\Oracle.oracle
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleDBConsole
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle10g_home
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OraclService

Step 5:

Reboot the system.

• Delete the Oracle home directory
C:\Oracle
• Delete the Oracle Program Files directory:
C:\Program Files\Oracle
• Delete the Oracle Start Menu shortcuts directory:
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*
Where * indicates the name of your install. Look for and remove all Oracle directories from that location.

Step 6:
• Remove Oracle refereces from the path. To edit your path go to:

Start->Settings->Control Panel->System->Advanced->Environment Variables

Edit both of the environment variables user PATH and system PATH. Remove any Oracle references in them.

• Remove Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\


Now you can install Oracle 10g.

Monday, September 29, 2008

How to release dead lock in Oracle

1. Find out who is locking in Deadlock scenario

SQL> select (select username from v$session where sid=a.sid) blocker,
a.sid, ' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1 and b.request > 0
and a.id1 = b.id1 and a.id2 = b.id2

/BLOCKER SID 'ISBLOCKING' BLOCKEE SID------------------------------ ----------ORAUSER 94 is blocking OPIS 1032.

2. Find out SID and Serial#

SQL> SELECT s.sid, s.serial#, s.osuser,FROM v$session swhere s.sid=94SQL> SID SERIAL# OSUSER PROGRAM---------- ---------- -------------------------------94 39897 mross3.

Kill the session:

SQL> alter system kill session '94,39897';

System altered.

Friday, September 19, 2008

Upgrading Oracle Database Server 10g Release 10.2.0.1 to Oracle Database 10g Release 10.2.0.3 on Windows 2003

Upgrading Oracle Database Server 10g Release 10.2.0.1 to Oracle Database 10g Release 10.2.0.3 on Windows 2003

We had recently upgraded one of our test databases from 10.2.0.1 to 10.2.0.3 on Windows 2003.Details:

Operating Version Details Windows 2003 Standard EditionOracle Version Details (Current) : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionOracle
Instance Name: tom
Patchset (to be applied on) : p5337014_10203_WINNT.zip (Downloaded from http://www.metalink.oracle.com/)

Oracle Universal Installer Version RequirementsThe Oracle Universal Installer release must be 10.1.0.3 (This includes in the Patchset).

System Requirements* Oracle Database 10g (10.2.0.1) or laterPreinstallation TasksTo download and extract the patch set installation software:* Download the p5337014_10203_WINNT.zip patch set.

Set the ORACLE_HOME and ORACLE_SID Environment Variables
C:\> set ORACLE_HOME= D:\oracle\product\10.2.0\db_1
c:\> set ORACLE_SID=TOMThe oracle_home is the Oracle home directory where the

Oracle10g installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.


Stopping All Processes for a Single Instance Installation

**To stop Listener ServiceC:\lsnrctlLSNRCTL> stop

** To stop Enterprise Manager ServiceC:\ emctl stop dbconsole

** To stop iSQLPlus ServiceC:\ isqlplusctl stop**

To Shut Down Oracle DatabasesC:\ sqlplus /nolog
C:\ connect /as sysdbaSQL> SHUTDOWN IMMEDIATE;

Back Up the SystemOracle recommends that you create a backup of the Oracle10g installation before you install the patch set.We took the backup of Oracle home and all the database files.

Installation TasksInstalling the Oracle Database 10g Patch Set Interactively

** Log in as AdministratorGo to unzipped location of p5337014_10203_WINNT.zip file** To start Oracle Universal Installer, where patchset_directory is the directory where you unzipped the patch set softwareD:\ patchset_directory\patch\Disk1

Dubble click setup.exe

** On the Welcome screen, click Next.
** In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.** On the Summary screen, click Install.This screen lists all of the patches available for installation.** On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.Postinstallation TasksUpgrading of Oracle 10g Software from Release 10.2.0.1 to 10.2.0.3 is now successfully done. Now, earlier Oracle Database needs to be upgraded from 10.2.0.1 to 10.2.0.3. Oracle recommends two ways to upgrade the database, one is using DBUA (Database Upgrade Assistance), and another is Manual database upgrade. Here we have followed manual method for upgrade.


Upgrading a Release 10.2 Database using DBUA (Oracle Database Upgrade Assistant)
After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

Note:
If you do not run the Oracle Database Upgrade Assistant script as described in this section, then the following errors are displayed:

ORA-01092: ORACLE instance terminated.
ORA-39700: database must be opened with UPGRADE option.


Run Oracle Database Upgrade Assistant either in the interactive or noninteractive mode:
Interactive mode:


1. Start the Oracle Database Upgrade Assistant:
From the Start menu, select Programs, then Oracle - HOME_NAME,

then Configuration and Migration Tools and then Database Upgrade Assistant to start the Oracle Database Upgrade Assistant.
Complete the following steps displayed in the Oracle Database Upgrade Assistant screen:

2. On the Welcome screen, click Next.
3. On the Databases screen, select the name of the Oracle Database that you want to update, then click Next.

4. On the Recompile invalid objects screen, select the Recompile the invalid objects at the end of upgrade option, then click Next.

5. If you have not taken the back up of the database earlier, on the Backup screen, select the I would like to take this tool to backup the database option, mention the Path, then click Next.

6. On the Summary screen, check the summary, then click Finish.

7. On the End of upgradation screen, click Exit, then click Yes to exit from Oracle Database Upgrade Assistant.

## Start All Oracle Related Services
## To Check Current Oracle Version

SQL> select * from version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


Manually Upgrading a Release 10.2 Database


* Required Preupgrade Checks**

Check the SYSTEM Tablespace Size

SQL> SELECT sum(bytes/1024/1024) "Size in MB"2 FROM dba_free_space3 WHERE tablespace_name='SYSTEM';
Size in MB----------97.125

We have enough free space in SYSTEM tablespace.I

f not enough free space in SYSTEM tablespace, then add/resize the datafile as below.

## TO add a datafileALTER TABLESPACE SYSTEMADD ' D:\oracle\product\10.2.0\oradata\TOM\ SYSTEM01.DBF 'SIZE 100M;

## To resize a existing datafile.ALTER DATABASE DATAFILE ' D:\oracle\product\10.2.0\oradata\TOM\ SYSTEM01.DBF' RESIZE 500M;

* Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters

##Start the database with the NOMOUNT option:

SQL> STARTUP NOMOUNT
## To check initialization parameter file (initsid.ora or spfilesid.ora)

SQL> SHOW PARAMETER PFILE;

## To determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE

SQL> SHOW PARAMETER JAVA_POOL_SIZE

##If the system is using a server parameter file:If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;

If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:

SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;

##If the system uses an initialization parameter fileIf necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).

Shut down the database:

SQL> SHUTDOWN

Upgrading a Release 10.2 Database
##Login as Windows Administrator

##Start the Listener
C:\lsnrctlLSNRCTL> START

##Connect to sqlplus as sysdba and start the database in upgrade mode
C:\sqlplus /nologSQL> connect /as sysdba
SQL> STARTUP UPGRADE;

##Run the catupgrd.sql scrips
SQL> SPOOL patch.log
SQL> @D:\oracle\product\10.2.0\db_1\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.

## Restart the database:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.

SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql
Start All Oracle Related Services

To Check Current Oracle Version
SQL> select * from version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


Common Errors:
** If you attempt to install this patch set in an Oracle home directory that does not contain an Oracle Database 10g release 10.2.0.1 or 10.2.0.2 installation, Oracle Universal Installer displays a warning dialog with the following error:
OUI-10091: There are no patches that need to be applied from the patch set Oracle Database 10g Release 2 Patch Set 210.2.0.3
The Oracle Universal Installer does not allow the installation to proceed. Click OK, then click Cancel to end the installation.

** If you do not run the Oracle Database Upgrade Assistant as described in this section, then the following errors are displayed:ORA-01092: ORACLE instance terminated.ORA-39700: database must be opened with UPGRADE option.
C:\sqlplus /nologSQL> connect /as sysdbaSQL> STARTUP UPGRADE;