VIPCA - NO INTERFACE AVAILABLE(NATIVE:listNetInterfaces:[11])

While doing the RAC install, after running the root.sh file on both the nodes and while running the vipca manually we may encounter the following error.

ERROR:

Error: No interface available(Native: listNetInterfaces:[11])

SOLUTION:

Solution: On both the hosts you should able to do "nslookup" on vipname. For example if your vip name is configured in /etc/hosts file as below. # Public VIPs for RAC 1.2.3.4   node1-vip.sss.com    node1-vip 1.2.4.3   node2-vip.sss.com    node2-vip You should able to do "nslookup node1-vip.sss.com". If you get "nslookup" not found, you have to contact your network adminstrator to configure "nslookup" on these hosts.

CRS ERROR - PRKC-1073 - Error while copying directory /opt/mis/oracle/product/crs10 with exclude file list 'null'

We may encounter the following error while installing the CRS. The error is as follows.

ERROR:

Error:Error while copying directory "/opt/mis/oracle/product/crs10/" with exclude file list "null" to nodes node2. [PRKC-1073:Failed to transfer directory "/opt/mis/oracle/product/crs10/" to any if the given nodes "node2". Error on node node2:mkdir:/opt/mis/oracle/product/crs10": Exists but it is not a directory:failed]

CAUSE:

Cause: 1) The possible cause for this error is, the directory "crs10" in the path "/opt/mis/oracle/product/crs10/" is not a directory. If we create crs10 as a symbolic link to some other location, the CRS installation will fail. What I have observed was it will install all the files in "node1", but it will not copy the install files on node2. 2) The other reason will be this directory should have the correct ownership for the files in this directory.

SOLUTION:

Solution: 1)The folder where we are installing the CRS software should not be a symbolic link. 2)Change the ownership of the directory using the following command. chown -R oracle:dba /opt/mis/oracle/product/crs10/

Physical standby using HOT BACKUP

Here are the steps to create physical standby database using HOTBACKUP. The primary database name is "SRIPROD" and the standby databse is "SRITEST".

Step 1: On the primary database side, enable the FORCELOGGING.

a) Connect to the primary database.           sqlplus "/as sysdba"      b) SQL> alter database force logging;

Step 2: Create the password file on secondary database if it doesn’t exist. The password file will exist in $ORACLE_HOME/dbs

a) SQL> orapwd file=orapwSRIPROD password=xxx force=y

Step 3: Create the standby Redo logs.

a) The size of the standby redo log files should match the size of current primary database online redo log files. First we have find the size of the primary database online redo log files.           SQL> select bytes from v$log;                BYTES               ----------                104857600                104857600                104857600 Here the size of my online redo log files is 100M. b) Now use the following query to find the details of the log files in the primary database. (The following command has to execute on primary database.) SQL> select group#,type,member from v$logfile; GROUP# TYPE MEMBER ---------- ------- ------------------------------------------------------------ 1 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log1a.dbf 1 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log1b.dbf 2 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log2a.dbf 2 ONLINE /u01/app/oracle/data/db2/data/SRIPROD/SRIPROD_log2b.dbf 3 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log3a.dbf 3 ONLINE /u01/app/oracle/data/db3/data/SRIPROD/SRIPROD_log3b.dbf c) Now create the standby redo log files in the primary database. 1) ALTER DATABASE ADD SRITESTDBY LOGFILE GROUP 4 ('/u01/app/oracle/data/db2/data/SRIPROD/SRIPROD_log4a.dbf','/u01/app/oracle/data/db2/data/ SRIPROD/SRIPROD_log4b.dbf') SIZE 100M; 2) ALTER DATABASE ADD SRITESTDBY LOGFILE GROUP 5 ('/u01/app/oracle/data/db3/data/SRIPROD/SRIPROD_log5a.dbf','/u01/app/oracle/data/db3/data/ SRIPROD/SRIPROD_log5b.dbf') SIZE 100M; 3) ALTER DATABASE ADD SRITESTDBY LOGFILE GROUP 6 ('/u01/app/oracle/data/db4/data/SRIPROD/SRIPROD_log6a.dbf','/u01/app/oracle/data/db4/data/ SRIPROD/SRIPROD_log6b.dbf') SIZE 100M; d) To verify the results of the standby redo log groups creation, run the following query on the primary database: SQL> select group#,type,member from v$logfile; GROUP# TYPE MEMBER ---------- ------- ------------------------------------------------------------ 1 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log1a.dbf 1 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log1b.dbf 2 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log2a.dbf 2 ONLINE /u01/app/oracle/data/db2/data/SRIPROD/SRIPROD_log2b.dbf 3 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log3a.dbf 3 ONLINE /u01/app/oracle/data/db3/data/SRIPROD/SRIPROD_log3b.dbf 4 STANDBY /u01/app/oracle/data/db2/data/SRIPROD/SRIPROD_log4a.dbf 4 STANDBY /u01/app/oracle/data/db2/data/SRIPROD/SRIPROD_log4b.dbf 5 STANDBY /u01/app/oracle/data/db3/data/SRIPROD/SRIPROD_log5a.dbf 5 STANDBY /u01/app/oracle/data/db3/data/SRIPROD/SRIPROD_log5b.dbf 6 STANDBY /u01/app/oracle/data/db4/data/SRIPROD/SRIPROD_log6a.dbf 6 STANDBY /u01/app/oracle/data/db4/data/SRIPROD/SRIPROD_log6b.dbf

Step 4: Enable Archiving on primary database.

a) First check the archiving is enabled or not?           SQL> archive log list;      b) If arching is not enabled then do the following steps to enable the archive log mode.            SQL>shutdown immediate;            SQL>startup mount;            SQL>alter database archivelog;            SQL>alter database open;            SQL>archive log list;

Step 5: Now we have to set the initialization parameters on primary database.

a)First create the pfile from spfile if the database is mounted using spfile.           cd $ORACLE_HOME/dbs           sqlplus “/ as sysdba”           create pfile from spfile; It will create initSRIPROD.ora file in $ORACLE_HOME/dbs folder. b) Now edit the initSRIPROD.ora file to add the new primary and standby role parameters. *.db_name=SRIPROD *.db_unique_name=SRIPROD *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(SRIPROD,SRITEST)' *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/admin/SRIPROD/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SRIPROD' *.LOG_ARCHIVE_DEST_2='SERVICE=SRITEST LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SRITEST' *.LOG_ARCHIVE_DEST_STATE_1='ENABLE' *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' *.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' *.FAL_CLIENT='SRIPROD' *.FAL_SERVER='SRITEST' *.STANDBY_FILE_MANAGEMENT='AUTO' *.DB_FILE_NAME_CONVERT='SRITEST','SRIPROD' *.LOG_FILE_NAME_CONVERT='SRITEST','SRIPROD'

Step 6: Now shutdown the primary database and mount the database with pfile.

On Primary database.           SQL> sqlplus “/ as sysdba”           SQL> shutdown immediate;           SQL> exit           UNIX PROMPT> cd $ORACLE_HOME/dbs           UNIX PROMPT> rm –rf spfileSRIPROD.ora (remove/move the spfile since we should mount the database using pfile.)           UNIX PROMPT> sqlplus “/as sysdba”           SQL> startup

Step 7: Now create the spfile from pfile(new pfile after adding the stand by parameters)

SQL> create spfile from pfile;      SQL> shutdown immediate;      SQL> startup; Now it will mount using the spfile.      SQL> Check whether the database is mounted using spfile or not? SQL> select nvl(value,'NO') from v$parameter where name='spfile'; NVL(VALUE,'NO') ---------------------------------------------------------------------------- /u01/app/oracle/product/10.2.0.1/dbs/spfileSRIPROD.ora

Step 8: Now we need to take a backup of primary database using cold backup or hot backup or using RMAN.
Here I am taking hot backup since there is no need for me to shut down the primary database.

a) Test whether primary database is in Archivelog mode or not? If it is not enable the archive log mode as described in Step 4. b) Use this sql command to check to find whether all the table spaces are in online mode or not on primary database?        $ select tablespace_name, online_status from dba_data_files; c) Use the following command to startup the actual database backup using hotbackup by placing all tablespaces in begin backup mode.      SQL> select 'alter tablespace ' || tablespace_name || ' begin backup;' from dba_tablespaces where tablespace_name not like 'TEMP%'; 'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;' ------------------------------------------------------------- alter tablespace SYSTEM begin backup; alter tablespace UNDOTBS1 begin backup; alter tablespace SYSAUX begin backup; alter tablespace USERS begin backup; alter tablespace TEST begin backup; d) execute the above result to place all the table spaces in begin backup mode. e) Now copy the data files from primary database to stand by database locations. In primary use the following query to find the datafiles. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_system1a.dbf /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_undotbs1a.dbf /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_sysaux1a.dbf /u01/app/oracle/data/db4/data/SRIPROD/SRIPROD_log1d.dbf /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_test1a.dbf f) Copy all these datafiles to stand by database locations. My stand by database file structure is as follows. /u01/app/oracle/data/db1/data/SRITEST/ So use the following copy commands to copy the datafiles from primary to stand by. Cp /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_system1a.dbf /u01/app/oracle/data/db1/data/SRITEST/SRITEST_system1a.dbf Cp /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_undotbs1a.dbf /u01/app/oracle/data/db1/data/SRITEST/SRITEST_undotbs1a.dbf Cp /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_sysaux1a.dbf /u01/app/oracle/data/db1/data/SRITEST/SRITEST_sysaux1a.dbf Cp u01/app/oracle/data/db4/data/SRIPROD/SRIPROD_log1d.dbf u01/app/oracle/data/db4/data/SRITEST/SRITEST_log1d.dbf Cp /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_test1a.dbf /u01/app/oracle/data/db1/data/SRITEST/SRITEST_test1a.dbf NOTE: It is very important to change the name of the data file in stand by database to the corresponding sid name as we are giving the *.DB_FILE_NAME_CONVERT='SRIPROD',' SRITEST '. That means it will replace all the SRIPROD name with SRITEST. g) After copying all data files it is needed to put all the table spaces in end backup mode. For that use the following… SQL> select 'alter tablespace ' || tablespace_name || ' end backup;' from dba_tablespaces where tablespace_name not like 'TEMP%'; 'ALTERTABLESPACE'||TABLESPACE_NAME||'ENDBACKUP;' ----------------------------------------------------------- alter tablespace SYSTEM end backup; alter tablespace UNDOTBS1 end backup; alter tablespace SYSAUX end backup; alter tablespace USERS end backup; alter tablespace TEST end backup; h) Now execute the above sql “alter tablespace” statements to place the table spaces in end backup mode. SQL> alter tablespace SYSTEM end backup; SQL> alter tablespace UNDOTBS1 end backup; SQL> alter tablespace SYSAUX end backup; SQL> alter tablespace USERS end backup; SQL> alter tablespace TEST end backup; i) With this hot backup is completed.

Step 9: Create the control file to the stand by database.
On the primary database create the controlfile for the standby to use as follows.

For us the primary database is in open mode. So we need to shut down the primary database and have to open in mount state.           On Primary:           SQL> shutdown immediate;           SQL> startup mount;           SQL> alter database create standby controlfile as ‘/u01/app/oracle/data/db1/data/SRITEST/SRITEST_control01.ctl’;           SQL> ALTER DATABASE OPEN;

Step 10: Copy the primary database pfile to stand by server $ORACLE_HOME/dbs/ and rename the pfile with appropriate oracle SID name.

setinst SRIPROD; si;      On primary database host:      UNIX PROMPT> cd $ORACLE_HOME/dbs      UNIX PROMPT> cp initSRIPROD.ora initSRITEST.ora (If both primary and stand by databases are on same host)           OR      UNIX PROMOT> scp –r –p initSRIPROD.ora oracle@hostname:$ORACLE_HOME/dbs/initSRITEST.ora

Step 11: Edit the init parameter file on standby database host.

On stand by database host change the following parameters on stand by database init parameter file.      Cd $ORACLE_HOME/dbs      View initSRITEST.ora *.audit_file_dest='/u01/app/oracle/admin/SRITEST/audit' *.background_dump_dest='/u01/app/oracle/admin/SRITEST/bdump' *.core_dump_dest='/u01/app/oracle/admin/SRITEST/cdump' *.user_dump_dest='/u01/app/oracle/admin/SRITEST/udump' *.compatible='10.2.0.3.0' *.control_files='/u01/app/oracle/data/db1/data/SRITEST/SRITEST_control01.ctl' *.db_name='SRIPROD' *.DB_UNIQUE_NAME='SRITEST' *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(SRIPR)D,SRITEST)' *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/admin/SRITEST/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SRITEST' *.LOG_ARCHIVE_DEST_2='SERVICE=SRIPROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SRIPROD' *.LOG_ARCHIVE_DEST_STATE_1='ENABLE' *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' *.FAL_CLIENT='SRITEST' *.FAL_SERVER='SRIPROD' *.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' *.DB_FILE_NAME_CONVERT='SRIPROD','SRITEST' *.LOG_FILE_NAME_CONVERT='SRIPROD','SRITEST'

Step 12: On Standby server, create all required directories for dump and archived log destination. Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.

Note: We should copy the controlfile which is created in step 9 in appropriate folder as *.control_files parameter in init parameter file of standby database look for that file.

Step 13: Copy the standby control file “SRITEST_control01.ctl” which is created in Step 9 from primary to standby destinations.

Step 14: Copy the primary database password file to stand by database $ORACLE_HOME/dbs location and rename that file to “orapwSRITEST”

Step 15: Configure the listeners on primary database and on standby database.

a) View listener.ora LISTENER_SRIPROD= (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = SRIPROD)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost.srikanth.com) (PORT = 1521)) ) ) ) SID_LIST_LISTENER_SRIPROD= (SID_LIST = (SID_DESC = (SID_NAME = SRIPROD) (ORACLE_HOME=/u01/app/oracle/product/10.2.0.1) ) ) b) Now add tns entry for SRIPROD in tnsnames.ora file of primary database. On primary database: Cd $ORACLE_HOME/network/admin/ View tnsnames.ora SRIPROD = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.srikanth.com)(PORT=1521))(CONNECT_DATA=(SID=SRIPROD))) c) On Secondary database host: cd $ORACLE_HOME/network/admin Add the following entry. LISTENER_SRITEST= (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = SRITEST)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost.srikanth.com) (PORT = 1527)) ) ) ) SID_LIST_LISTENER_SRITEST= (SID_LIST = (SID_DESC = (SID_NAME = SRITEST) (ORACLE_HOME=/u01/app/oracle/product/10.2.0.1) ) ) d) Now add tns entry for SRITEST in tnsnames.ora file of secondary database host. On secondary database’s host: Cd $ORACLE_HOME/network/admin/ View tnsnames.ora SRITEST = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.srikanth.com)(PORT=1521))(CONNECT_DATA=(SID=SRITEST)))

Step 16: Now start the listeners on both databases.

a) On primary database host:           lsnrctl start LISTENER_SRIPROD      b) On secondary database host:           lsnrctl start LISTENER_SRITEST      c) On primary database host:           Check tnsping to both services:             $tnsping SRIPROD             $tnsping STITEST      d) On secondary database host:           Check tnsping to both services:             $tnsping SRIPROD             $tnsping STITEST

Step 17: On Standby database host start the instance using newly created init parameter file and generate spfile.

On Secondary database      $ cd $ORACLE_HOME/dbs      $ sqlplus “/as sysdba”      SQL> startup nomount -> The database will start using init file. It should start in nomount state only.      SQL> create spfile from pfile;      SQL> shutdown immediate;

Step 18: Do the recovery for the stand by database as we did hot backup.

On standby database: a)      SQL> startup nomount -> Now it will start using spfile.      SQL> alter database mount standby database ;      SQL> recover standby database ;      Specify log: {=suggested | filename | AUTO | CANCEL} NOTE: Apply all the archive log files as you find them in primary database archive directory. After applying all the archive log files give “CANCEL”. b) Start Redo apply      On the standby database, to start redo apply:      SQL>alter database recover managed standby database disconnect from session; c) If you ever need to stop log apply services:      SQL> alter database recover managed standby database cancel;

Step 19: Verify the standby database is performing properly.

a) On Standby perform a query:           SQL>select sequence#, first_time, next_time from v$archived_log;      b) On Primary, force a logfile switch:           SQL>alter system switch logfile;      c) On Standby, verify the archived redo log files were applied:           SQL>select sequence#, applied from v$archived_log order by sequence#;      d) If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.      e) To start real-time apply.           SQL> alter database recover managed standby database using current logfile disconnect;

Oracle DB links

Here are the steps to create Db links from one database to another database.
I am creating a database link in SRITSTDB(source db) for schema/user "TEST" to SRISTGDB(target db).

Step 1: Adding a TNS entry for target db in source db host machine.

a) Open tnsnames.ora file in SRITSTDB database.           It will be located in $ORACLE_HOME/network/admin      b) add the tns entry like below.           "SRISTGDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hello)(PORT=1521))           (CONNECT_DATA=(SID=SRISTGDB)))"

Step 2: Login as user in which you want to create this db link.

Here I mentioned for TEST user. So i am logging as "TEST" user as below.      UNIX PROMPT>sqlplus test/test)))"

Step 3: Creating a database link.
Use the below sql to create the syntax.

Syntax: Create database link linkname connect to user identified by pwd using 'CONEECT_STRING'; Usage 1:Create database link SRISTGDBLINK CONNECT to test identified by test                using 'SRISTGDB'; Note: If there is any problem in TNS setup on that host use the follwing method to create the db link. I am using the complete connect string descriptor instead of just connect string name. Some times If you are trying to use this db link from sql navigator or from sql*plus it will give "ORA-12541: TNS: no listener". For that use the follwing syntax to create the dblink. Usage 2:Create database link SRISTGDBLINK CONNECT to test identified by test using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hello)(PORT=1521))(CONNECT_DATA=(SID=SRISTGDB)))';

Step 4: Checking.

a) Connect as TEST user to the SRITSTDB.      b) select name from v$database@SRISTGDBLINK;

Applying Oracle Patches on 10.2.0.2 home

The follwoing patch numbers are required for a new Oracle 10.2.0.2 home. I have applied these patches on Solaris 10 operating system. Check your operating system before applying.

The main thing in this article is, applying a group of patches at a time using some scripts.

Step 1: We have to apply following 6 Patches to 10.2.0.2 home after upgrading it from 10.2.0.1. The following are the required Patches.

1) 5117016     2) 5458753     3) 5075470     4) 4689959     5) 4712729     6) 4966417

Step 2: Create a directory /tmp/Patches/

Step 3: Create a file "apply_all.sh" file with the following code.
cd /tmp/Patches
vi apply_all.sh

#!/bin/sh set PATH=${PATH}:${ORACLE_HOME}/OPatch; export PATH #This is the Oracle home where the patches will apply. set OBJECT_MODE=32_64; export OBJECT_MOD for patchid in `cat patches`; do current_dir=`pwd` cd $patchid $ORACLE_HOME/OPatch/opatch apply #This is OPatch software directory which we are using to apply patches. cd $current_dir done exit

Step 4: Create a file "apply_all.sh" file with the following code.
The above script uses the "patches" file which contains all the patch numbers which we have to apply. So just create a file "patches" with the following content.

vi patches     5458753     5075470     4689959     4712729     4966417 Note: This patchs file may contain any number of patches. It may be one or any number. Here we are applying 6 patches, so this file contains all the 6 Patch numbers which we are applying to this home.

Step 5: Make sure the all the databases and listeners are down, if any of the databases are using this oracle 10.2.0.2 home.
NOTE:If it is a new host/new home then there is no need to stop any databases because it may not used by any databases.

Step 6:Execution of "apply_all" script.

a) set the environment to current oracle home i.e. 10.2.0.2 - setenv ORACLESID b) Double check the $ORACLE_HOME; env | grep ORA ; -> It should set to 10.2.0.2 home. b) cd /tmp/Patches; c) chmod 775 apply_all.sh d) ./apply_all

Oracle Database manual upgrade from 9.2.0.7 to 10.2.0.3

This upgrade is also similar to upgrade from 10.2.0.2 to 10.2.0.3 except in 9i there is no SYSAUX tablespace. We have to manually create the SYSAUX tablespace before we do upgrade.

Just follow all the steps described in my previous post "Oracle Database manual upgrade from 10.2.0.2 to 10.2.0.3" In Step 7 modify the steps as below.

Step 7: Start the upgrade
UNIX PROMPT> mkdir /tmp/upgrade/ -> create this directory to store the log files.
UNIX PROMPT> sqlplus "/as sysdba"
SQL> startup upgrade

After giving “statup upgrade” create the sysaux tablespace as follows.
SQL> CREATE TABLESPACE sysaux DATAFILE '/opt/mis/oracle/data/data/sysaux1a.dbf'
SIZE 1024M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
Now the upgrade process will starts. It will take atleast 45 mins to 1:15 mins.
At last it will show the components list and their status.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.3.0 00:28:27
JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:04:42
Oracle XDK VALID 10.2.0.3.0 00:01:02
Oracle Database Java Packages VALID 10.2.0.3.0 00:00:36
Oracle Expression Filter INVALID 10.2.0.3.0 00:00:22
Oracle Enterprise Manager VALID 10.2.0.3.0 00:03:51

SQL>spool off;

Oracle Database manual upgrade from 10.2.0.2 to 10.2.0.3

I am providing step by step Oracle database manual upgrade process from 10.2.0.2 to 10.2.0.3

NOTE: I hope you have already created oracle 10.2.0.3 home in your machine.
On my machine I have oracle 10.2.0.2 binaries in "/opt/mis/oracle/product/10.2.0.2" path and oracle 10.2.0.3 binaries in "/opt/mis/oracle/product/10.2.0.3" path.

This is for solaris/linux servers.

Step 1: Pre checks
a) Check SYSTEM table space has atleast 150 MB free.
b) Check shared_pool_size parameter is set to 400MB.
Show parameter shared_pool_size;
If it is not set do "ALTER SYSTEM SET SHARED_POOL_SIZE='400M' SCOPE=both;"
c) Check java_pool_size parameter is set to 150MB.
Show parameter java_pool_size
If it is not set, do ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;"

Step 2: Disable all cron jobs related to the ORACLE_SID
This step is to ensure that no cron(unix) jobs are running for this database. It is not needed if you do not have any cron jobs running for this ORACLE_SID.
To check cron jobs for a particular ORACLE_SID do " crontab -l grep ORACLE_SID "

Step 3: Create spfile if it doesn't exist in 10.2.0.2_HOME/dbs directory.
Check whether your current database is mounted using spfile or not. For this use the follwing query.
SQL> SELECT NVL(value,'NO')FROM v$parameter WHERE NAME='spfile';
If it is not mounted using spfile, create an spfile using...
UNIX PROMPT> cd $ORACLE_HOME/dbs
UNIX PROMPT> sqlplus "/as sysdba"
SQL> create spfile from pfile;
SQL> shutdown immediate;
SQL> startup;

Step 4: Change the $ORACLE_HOME enviroment variable
Now we have to set the $ORACLE_HOME environment variable to 10.2.0.3
UNIX PROMPT> env | grep ORACLE_HOME
ORACLE_HOME=/opt/mis/oracle/product/10.2.0.2 (This is before change.)

You have to change this in the enviroment file. In my solaris system these environment variables are stored in .cshrc file in the home directory.
Any way the normal environment variable file entries will look as follows.
Change the ORACLE_HOME variable. See below.
unsetenv LD_LIBRARY_PATH_64
unsetenv LD_LIBRARY_PATH
setenv ORACLE_SID BMC4INT
setenv ORACLE_BASE /opt/mis/oracle
setenv ORACLE_HOME $ORACLE_BASE/product/10.2.0.3
setenv LD_LIBRARY_PATH_64 $ORACLE_HOME/lib:$ORACLE_HOME/javavm/lib
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/javavm/lib
unsetenv TWO_TASK
unsetenv ORACLE_SERVER
setenv DBA $ORACLE_BASE/admin # OFA
setenv NLS_LANG AMERICAN_AMERICA.UTF8
setenv ORA_NLS10 $ORACLE_HOME/nls/data # new in 10.2

Step 5: Copy the spfile.ora from 10.2.0.2_oracleHome/dbs to 10.2.0.3_oraclehome/dbs directory.
UNIX PROMPT> cd /opt/mis/oracle/product/10.2.0.2/dbs/
UNIX PROMPT> cp spfileORALCE_SID.ora /opt/mis/oracle/product/10.2.0.3/dbs/
NOTE:Do not change the COMPATIBLE parameter. It should set
to 10.2.0.2 only.

Step 6: Double check env variables for 10.2.0.3
UNIX PROMPT> env | grep ORACLE_HOME
Now it should show 10.2.0.3

Step 7: Start the upgrade
UNIX PROMPT> mkdir /tmp/upgrade/ -> create this directory to store the log files.
UNIX PROMPT> sqlplus "/as sysdba"
SQL> startup upgrade
SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
Now the upgrade process will starts. It will take atleast 45 mins to 1:15 mins.
At last it will show the components list and their status.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.3.0 00:28:27
JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:04:42
Oracle XDK VALID 10.2.0.3.0 00:01:02
Oracle Database Java Packages VALID 10.2.0.3.0 00:00:36
Oracle Expression Filter INVALID 10.2.0.3.0 00:00:22
Oracle Enterprise Manager VALID 10.2.0.3.0 00:03:51

SQL>spool off;

Step 8: Set the COMPATIBLE parameter to 10.2.0.3.
SQL> alter system set compatible='10.2.0.3' scope=both;
SQL> shutdown immediate;
SQL> startup;
SQL> show parameter compatible; -> It should show 10.2.0.3

Step 9: Check the status of all the components of DBA_REGISTRY:
SQL> select comp_name,comp_id,version,status from dba_registry;
SQL> exit;

Step 10: Create a script called "recomp.sql" in "/tmp/upgrade" directory.
This script is used to execute utlrp.sql file along with some other manual compile statemetns.
UNIX PROMPT> cd /tmp/upgrade/
UNIX PROMPT> vi recomp.sql
Add the follwing entries to the file.

col object_type for a20
col object_name for a32
col owner for a12
spool recomp.log
select owner,object_type,count(1) from dba_objects where status<>'VALID'
group by owner,object_type;
@?/rdbms/admin/utlrp
set echo on feed on
select owner,object_type,object_name from dba_objects where status<>'VALID' and
owner in ('SYS','SYSTEM');
alter session set nls_length_semantics=byte;
alter view KU$_XMLSCHEMA_VIEW compile;
select owner,object_type,object_name from dba_objects where status<>'VALID' and
owner in ('SYS','SYSTEM');
spool off;
exit;

UNIX PROMPT> nohup sqlplus "/ as sysdba" @recomp.sql &
It will execute the file in nohup mode.
Step 11: Checking
Select comp_name,comp_id,version,status from dba_registry;
Now all the objects should show "VALID" state.

Step 12: Change the Listener
Change the listener.ora file in $ORACLE_HOME/network/admin/ folder and make home
point to new 10.2.0.3 home.

Step 13: Restore the cron jobs for this ORACLE SID