Upgrade database from 9.2.0.5 to 9.2.0.7

Steps followed to upgrade database from 9.2.0.5 to 9.2.0.7

This was just one of the specific requirement from the application team so i am posting it just in case it benefits anyone.

Steps:
Download software and extract the oracle installation software

Set the oracle environment

Shut down the database

Stop all processes

Take a backup of Oracle installation

Installation

Post Installation Task(Database Upgrade)

STEP 1: Download and extract the latest patch

You can use the oracle website to download the latest oracle patch set. Here we have installed p4163445_92070_SOLARIS64.zip

Here we have placed the patch set dump file p4163445_92070_SOLARIS64.zip at /var/opt/oracle/product

Once the patch set dump has been placed on the server extract the patch set p4163445_92070_SOLARIS64.zip

unzip p4163445_92070_SOLARIS64.zip


STEP2: Set up the oracle environments variables:
This is one of the very important step that need to be followed religiously because if this is not followed properly then this can ruin all the effort.

Set up mainly the correct ORACLE_HOME and ORACLE_SID
Export ORACLE_SID=blfxen1
Export ORACLE_HOME=/var/opt/oracle/product/9.2.0.5.0



STEP 3: SHUT DOWN DATABASE:

Ensure that before the installation begin shut down the databases which the using binaries for which you are doing the upgrading activity.

SQl>Shutdown immediate;


STEP 4: Stop all processes

Ensure that before you begin this upgradation activity all the oracle processes should be stopped.
The main oracle processes would be the database listeners as well as any agent(if running.

Lsnrctl stop
Or
lsnrop.sh stop

Ensure that you run the above command for all the listeners for the databases(if more than one) running on the same server.

STEP 5: BACKUP

This is recommended that before installation activity starts we should take a backup of the binaries on which the database is running. This is to ensure to roll back in case of any corruption after the installation activity completes.

STEP 6: INSTALLING BINARIES

a. First of all we need to direct the X applications to display on the local system. So for that export the DISPLAY parameter a:

DISPLAY=export DISPLAY=:0.0
For e.g.

DISPLAY=export DISPLAY=10.5.12.133:0.0

Where LOCAL_HOST is the address (ip or hostname) of the machine where you want to display runInstaller.

b. Check on your local machine where you are displaying the runInstaller for softwares like EXCEED/XSESSION (XSecurePro64) which supports display settings required for running the runInstaller.

c. Once the patch set dump is extracted

unzip p4163445_92070_SOLARIS64.zip


It will create a directory Disk1
Inside the directory below are the files that will be present.

(f7q21-08) [SID: blfxenv1]/var/opt/oracle/product>cd Disk1
(f7q21-08) [SID: blfxenv1]/var/opt/oracle/product/Disk1>ls -ltr
total 4280
-rwxr-xr-x 1 oracle dba 1477 Sep 1 2005 runInstaller
drwxr-xr-x 2 oracle dba 96 Sep 1 2005 response
drwxr-xr-x 4 oracle dba 1024 Sep 1 2005 install
-rwxr-xr-x 1 oracle dba 2087863 Sep 2 2005 9207_buglist.htm
-rwxr-xr-x 1 oracle dba 99327 Sep 2 2005 patch_note.htm
drwxr-xr-x 8 oracle dba 1024 Sep 2 2005 stage

d.Now,go to /var/opt/oracle/product/Disk1

Before running runInstaller ensure that file /var/opt/oracle/oraInst.loc contains the right oraInventory path

If any issue you face like the installer product are not being shown at the
GUI runInstaller window, you must give the correct path of the ORAINVENTORY in the orainst.loc file.

5.RUNINSTALLER

This will open a GUI window:

(f7q21-08) [SID: blfxdev2]/var/opt/oracle/product/Disk1>./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.6, 5.7, 5.8, 5.9 or 5.10. Actual 5.9
Passed

Checking Temp space: must be greater than 250 MB. Actual 27549 MB Passed
Checking swap space: must be greater than 500 MB. Actual 29116MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 256 Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-08-20_11-15-44AM. Please wait ...(f7q21-08) [SID: blfxdev2]/var/opt/oracle/product/Disk1>Oracle Universal Installer, Version 10.1.0.4.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.

Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct



Just ensure that installed product show the binaries installed


Here ensure that when you click on the Installed product (as shown above in the screen shot ) you find the Oracle products installed. If no check path of inventory_loc your file’s /var/opt/oracle/oraInst.loc and ensure that it is not corrupted. Other wise change this to path of $ORACLE_HOME/oraInventory

f11u30-06:sdas> more oraInst.loc
#Oracle Installer Location File Location
#Mon Jun 08 23:20:57 GMT 2009
inst_group=dba
inventory_loc=/var/opt/oracle/oraInventory


Also ensure that the Destination PATH taken while installation is your ORACLE_HOME and source PATH is file patchset_directory/Disk1/stage/products.xml(here my pathset_directory is /var/opt/oracle/product).


Here as shown in the above diagram, path will be the path of the product that needs to be installed. Here in this case it is /d2100/var/opt/oracle/product/stage/product.xml

At the destination side path will be the path of your old binaries. Here in this case this is ORACLE_HOME.

$ORACLE_HOME=/var/opt/oracle/product/9.2.0.5

Here once you click NEXT installer check the components present at the ORACLE_HOME location.




As shown above, the installer checks for the oracle components that are installed in the database. If the upgraded version is already installed then, an error will be popped up telling that the patchset is already applied.
Otherwise the product component will start loading on the server. Once this completes to 100% then we will get an overall statistics.

Next we will start the upgradation.

d. At last when the upgradation of binaries completes before which, you will be asked to run root.sh by the root user.
Run this file by root user otherwise contact unix SA’s
/d2100/var/opt/oracle/product/9.2.0.5.0/root.sh

Output of the root.sh
----------------------------

Running Oracle9 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /var/opt/oracle/product/9.2.0.5.0

Enter the full pathname of the local bin directory: [/usr/local/bin]:
bash-2.05# /d2100/var/opt/oracle/product/9.2.0.5.0/root.sh
Running Oracle9 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /var/opt/oracle/product/9.2.0.5.0

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin ...

Adding entry to /var/opt/oracle/oratab file...
Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/dbs/initbillprd1.ora
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/dbs/orapwbillprd1
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/rdbms/filemap
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.



STEP 7: DATABASE UPGRADE
a.Check SYSTEM Tablespace Size
Here as a pre-requisite we need to ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace. If not we need to add some space to system tablespace.
Next, check for SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters

b.Check java_pool_size:
show parameter java_pool_size

Here if required increase the size of java_pool_size:

alter system set java_pool_size=’150M’ scope=spfile;

c.check shared_pool_size
show parameter shared_pool_size

Here if required increase the size of shared_pool_size


alter system set shared_pool_size =’150M’ scope=spfile;

d.If the system uses an initialization parameter file then change the value of these parameters in the pfile (initsid.ora)

Shut down the database: shut down immediate
Startup pfile=
Create spfile form pfile;
Shut immediate
Startup

Now ensure that the changes done in the pfile are getting reflected in the spfile
show parameter java_pool_size
show parameter shared_pool_size



e. Now we are ready for the upgradation as the pre check has completed.

First startup the data



Startup the database in migrate mode:
Startup migrate;

Once the database is started in migrate mode, we need to run catpatch.sql script.

Before running take a spool file
SQL> SPOOL patch_blfxenv1.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF


I am attaching below log file that comes up on running the catpatch.sql script.



Once the catpatch finishes the last few lines will show you as given below:


Output file will show as given below

COMP_NAME STATUS VERSION
----------------------------------- ----------- ------------------------------
Oracle9i Catalog Views VALID 9.2.0.7.0
Oracle9i Packages and Types VALID 9.2.0.7.0
JServer JAVA Virtual Machine VALID 9.2.0.7.0
Oracle XDK for Java VALID 9.2.0.9.0
Oracle9i Java Packages VALID 9.2.0.7.0


f.INVALID OBJECTS

Check all the invalid objects in the database. We should always run the utlrp utility once the upgrade completes.

To check the invalid objects in the database:
SQL>select * from dba_objects where status='INVALID'

Compile all the invalid objects in the database.
SQL>@?/rdbms/admin/utlrp


This completes the upgradation of the database from 9.2.0.5.0 to 9.2.0.7.0



g.CONFIRMATION

TO confirm the version of the several components installed in the database, query dba_registry

SQL> select COMP_NAME,VERSION from dba_registry;


COMP_NAME VERSION

Oracle9i Catalog Views 9.2.0.7.0
Oracle9i Packages and Types 9.2.0.7.0
JServer JAVA Virtual Machine 9.2.0.7.0
Oracle XDK for Java 9.2.0.9.0
Oracle9i Java Packages 9.2.0.7.0

ORA-16014: log n sequence# not archived, no available destinations

Recently i created a standby database but soon at the primary side started seeing the below error:


Thu May 3 15:09:42 2012
Errors in file /app/oracle/admin/orcl/trace/bdump/orcl_arc1_246675.trc:

ORA-16014: log 9 sequence# 269 not archived, no available destinations
ORA-00312: online log 9 thread 1: '/ora03/oradata/orcl/redo_stby/orcl_redo_g9m1.rdo'
ORA-00312: online log 9 thread 1: '/ora04/oradata/orcl/redo_stby/orcl_redo_g9m2.rdo'

Similarly

Thu May 3 15:19:52 2012

ORACLE Instance orcl - Archival Error
Thu May 3 15:19:52 2012
ORA-16014: log 10 sequence# 270 not archived, no available destinations
ORA-00312: online log 10 thread 1: '/ora01/oradata/orcl/redo_stby/orcl_redo_g10m1.rdo'
ORA-00312: online log 10 thread 1: '/ora02/oradata/orcl/redo_stby/orcl_redo_g10m2.rdo'

and


Thu May 3 15:22:29 2012
ORACLE Instance orcl - Archival Error
Thu May 3 15:22:29 2012
ORA-16014: log 6 sequence# 272 not archived, no available destinations
ORA-00312: online log 6 thread 1: '/ora01/oradata/orcl/redo_stby/orcl_redo_g6m1.rdo'
ORA-00312: online log 6 thread 1: '/ora02/oradata/orcl/redo_stby/orcl_redo_g6m2.rdo'

To resolve this i found that the redo log groups  6,9,10 were that of standby so only option i had to get rid of these errors was to clear these unarchived redo groups.

So i did the below:
alter database clear unarchived logfile group 6;
alter database clear unarchived logfile group 9;

alter database clear unarchived logfile group 10;
Once i cleared these unarchived standby redo logs , i did not see them again :)

Listener Issues after patching oracle binaries in oracle database 10.2.0.4

I had patched my oracle database binaries of 10.2.0.4 with patch 6460304 but i had to do this by creating a new oracle home for my database as this was a prod database and could not bring these binaries down by bringing down all the dependent databases as they were very critical. So i created a new oracle binaries 10.2.0.4 and then applied this patch on this.

So first brought down the listeners running for the database and database as well. Then i changed the oratab entries and tried bringing up the databases and the listeners.


Did the below steps as per oracle documentation:
# Downloaded the patch from oracle and then hosted in my unix solaris machine.
# % cd 6460304
#
#
# Ensure that the directory containing the opatch script appears in
# your $PATH; then enter the following command:
#
# % opatch apply
#
#
# 4. PostInstall Steps:
#
# cd rdbms/admin
# sqlplus "/ as sysdba"
@@catnomtt.sql
@@dbmsmeta.sql
@@dbmsmeti.sql
@@dbmsmetu.sql
@@dbmsmetb.sql
@@dbmsmetd.sql
@@catmeta.sql
@@prvtmeta.plb
@@prvtmeti.plb
@@prvtmetu.plb
@@prvtmetb.plb
@@prvtmetd.plb
@@catmet2.sql

ORCL:/app/oracle/product/10.2.0.4_64_v2:Y ---> My new Oracle Home location

LISTENER_ORCL:/app/oracle/product/10.2.0.4_64_v2:N ---> My new listener location
#ORCL:/app/oracle/product/10.2.0.4_64:Y
#LISTENER_ORCL:/app/oracle/product/10.2.0.4_64:N

C:\Documents and Settings\User1>sqlplus user@orcl

SQL*Plus: Release 9.2.0.6.0 - Production on Sat Jan 7 13:12:33 2012

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

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory


SOlution: Make sure all the changes of the new oracle home is done in oratab and listener.ora. In my case i missed to make this change in listener.ora.

LISTENER_ORCL=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXX.domain.com)(PORT = 1200))
)
)
)
SID_LIST_LISTENER_ORCL=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL.domain.com)
(ORACLE_HOME = /app/oracle/product/10.2.0.4_64)
### (ORACLE_HOME = /app/oracle/product/10.2.0.4_64_v2) ###New Oracle Home after patch 6460304 07th Jan 2012####
(SID_NAME = ORCL)
)
)

This is how my issue got resolved.

Error: Oracle executable binary mismatch detected

Error:
I recently faced this issue. After patching binaries of 10.2.0.4 saw the below error continuosly in alert log of the database.

WARNING: Oracle executable binary mismatch detected.
Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages

Please ignore if this is after oracle patch. Solution is just to bounce the database.

Gathering stats in the database

Version 9.2 onwards You can gather stats of the database by the below ways:

GATHER_TABLE_STATS
EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname =>'SCHEMA_NAME'',tabname=>'TABLE_NAME',method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',granularity =>'ALL',cascade => TRUE,degree => DBMS_STATS.DEFAULT_DEGREE);

GATHER_INDEX_STATS
Execute DBMS_STATS.GATHER_INDEX_STATS ('TEST','TEST_PK',estimate_percent => 30, degree => 6);

GATHER_SCHEMA_STATS
EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname =>'SCHEMA_NAME'',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree => 12)

Renaming table in Oracle

In Oracle you can rename a table in the same schema as below:

alter table OWNER. rename to NEW_TABLE NAME;

Here the new table will be created in the OWNER schema. Here the target table's schema name need not be required to mention.

Oracle database Broken job 10g

TO check status of broken job:

SQL> select job, broken,what from dba_jobs
JOB BROKEN WHAT
----- ------ ----
3 N PROC1;
2 Y PROC2;

Here the BROKEN column of job 2 is Y which means that this is a broken one.

To mark it as not broken:

EXEC DBMS_JOB.BROKEN(2,FALSE);


TO verify if the broken job is fixed.

SQL> select job, broken,what from dba_jobs

JOB BROKEN WHAT
--- ---- ----
3 N PROC1;
2 N PROC2;