Oracle Server Installation

Installation of Oracle 10gR2(10.2.0.1) on  OEL(Oracle Enterprise Linux)

On Linux

• Create Partitions
• Formating using ext3
• Mount points on a file system /u01   /u02   /u03   /opt   /var

Steps to Install Oracle 

Step 1

• Creation of Group and User

        $groupadd dba
        $useradd -g dba -d /home/oracle

Step 2 

Creation of Directory Structure

 $mkdir -p /u01/Oracle/product/10.2.0/db_1

here option -p indicates --> it creates directories recursively

change ownership and permissions of that directory structure

$chown -R Oracle:dba /u01/Oracle 

Step 3

Setting Kernel Parameters

$ vi /etc/sysctl.conf

if we modify kernel parameters then we have to restart the system or execute this command

/sbin/sysctl

Step 4

Assign read write execute permissions of Oracle 10g software to Oracle user because to install software we need permissions

Step 5

xhost + (This command enables gui display settings)

su - Oracle

cd /Oracle10gsoftware/database

./runInstaller

 Step 6

Post Installation Script - After successful installation we must execute the following script from root level

/u01/Oracle/product/10.2.0/db_1/root.sh

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

 

Oracle Architectural Components

This topic introduces the Oracle server architecture by examining the physical, memory, process, and logical structures involved in establishing a database connection, creating a session, and executing SQL commands.

Here we use Oracle 10g R2 (10.2.0.1)


10.2.0.1 --> means Patchset


10-->Major Version
2  -->Maintainance Release number
0  -->Application Server Release number
1  -->Component specific number

Overview of Primary Components:

What is Oracle Server ? 

There are several files, processes, and memory structures in an Oracle server; however, not all of them are used when processing a SQL statement. Some are used to improve the performance of the database, ensure that the database can be recovered in the event of a software or hardware error, or perform other tasks necessary to maintain the database. The Oracle server consists of an Oracle instance and an Oracle Database

What is Oracle Instance ?

An Oracle instance is the combination of the background processes and memory structures. The instance must be started to access the data in the database. Every time an instance is started, a System Global Area (SGA) is allocated and Oracle background processes are started. Background processes perform functions on behalf of the invoking process. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user. The background processes perform input/output (I/O) and monitor other Oracle processes to provide increased parallelism for better performance and reliability.

What is Oracle Database ?

An Oracle database consists of operating system files, also known as database files, that provide the actual physical storage for database information. The database files are used to ensure that the data is kept consistent and can be recovered in the event of a failure of the instance.

An Oracle database consists of three file types

• Data files containing the actual data in the database
• Redo logs containing a record of changes made to the database to enable recovery of the data in case of failures
• Control files containing information necessary to maintain and verify database integrity

Other Key File Structures

The Oracle server also uses other files that are not part of the database:
• The parameter file defines the characteristics of an Oracle instance. For example, it contains parameters that size some of the memory structures in the SGA.
• The password file authenticates users privileged to start up and shut down an Oracle instance.
• Archived redo log files are offline copies of the redo log files that may be necessary to recover from media failures.

System Global Area (SGA)

The SGA is also called the shared global area. It is used to store database information that is shared by database processes. It contains data and control information for the Oracle server and is allocated in the virtual memory of the computer where Oracle resides. The following statement can be used to view SGA memory allocations: 

SHOW SGA;

Total System Global Area    36437964 bytes
Fixed Size                              6543794 bytes
Variable Size                         19521536 bytes
Database Buffers                  16777216 bytes
Redo Buffers                         73728 bytes



• The SGA consists of several memory structures:

– Shared pool
– Database buffer cache
– Redo log buffer
– Other structures (e.g. lock and latch management, statistical data)

• There are two optional memory structures that can be configured within the SGA:

– Large pool
– Java pool

 Shared pool

The shared pool environment contains both fixed and variable structures. The fixed structures remain relatively the same size, whereas the variable structures grow and shrink based on user and program requirements. The actual sizing for the fixed and variable structures is based on an initialization parameter and the work of an Oracle internal algorithm.


It consists of two key performance-related memory structures:
– Library cache
– Data dictionary cache

Library cache

The library cache stores information about the most recently used SQL and PL/SQL statements. The library cache:
• Enables the sharing of commonly used statements
• Is managed by a least recently used (LRU) algorithm
• Consists of two structures:
– Shared SQL area
– Shared PL/SQL area
• Has its size determined by the shared pool sizing

Data Dictionary Cache

The data dictionary cache is a collection of the most recently used definitions in the database.
• It includes information about database files, tables, indexes, columns, users, privileges, and
other database objects.
• During the parse phase, the server process looks at the data dictionary for information to resolve
object names and validate access.
• Caching the data dictionary information into memory improves response time on queries.
• Size is determined by the shared pool sizing.

Database Buffer Cache

The database buffer cache stores copies of data blocks that have been retrieved from the data files.
• It enables great performance gains when you obtain and update data.
• It is managed through a least recently used (LRU) algorithm.
• DB_BLOCK_SIZE determines the primary block size

Redo Log Buffer Cache

The redo log buffer cache records all changes made to the database data blocks.
• Its primary purpose is recovery.
• Changes recorded within are called redo entries.
• Redo entries contain information to reconstruct or redo changes.
• Size is defined by LOG_BUFFER.

Large Pool

The large pool is an optional area of memory in the SGA configured only in a shared server environment.
• It relieves the burden placed on the shared pool.
• This configured memory area is used for session memory (UGA), I/O slaves, and backup and restore operations.
• Unlike the shared pool, the large pool does not use an LRU list.
• Sized by LARGE_POOL_SIZE.
ALTER SYSTEM SET LARGE_POOL_SIZE = 64M;

Java Pool

The Java pool services the parsing requirements for Java commands.
• Required if installing and using Java.
• It is stored much the same way as PL/SQL in database tables.
• It is sized by the JAVA_POOL_SIZE parameter.

Program Global Area(PGA)

The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. In contrast to the SGA, which is shared by several processes, the PGA is an area that is used by only one process.

Background Processes

The relationship between the physical and memory structures is maintained and enforced by Oracle’s background processes.
• Mandatory background processes
DBWn 

PMON 

CKPT
LGWR 

SMON 

RECO
• Optional background processes
ARCn 

LMON 

Snnn
QMNn 

LMDn
CJQ0 

Pnnn
LCKn 

Dnnn

 

Database Writer (DBWn)

The server process records changes to rollback and data blocks in the buffer cache. Database Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. It ensures that a sufficient number of free buffers—buffers that can be overwritten when server processes need to read in blocks from the data files—are available in the database buffer cache. Database performance is improved because server processes make changes only in the buffer cache.
DBWn defers writing to the data files until one of the following events occurs:
• Incremental or normal checkpoint
• The number of dirty buffers reaches a threshold value
• A process scans a specified number of blocks when scanning for free buffers and cannot fine any.
• Timeout occurs.
• A ping request in Real Application Clusters environment.
• Placing a normal or temporary tablespace offline.
• Placing a tablespace in read only mode.
• Dropping or Truncating a table.
• ALTER TABLESPACE tablespace name BEGIN BACKUP

Log Writer (LGWR)

LGWR performs sequential writes from the redo log buffer cache to the redo log file under the following situations:
• When a transaction commits
• When the redo log buffer cache is one-third full
• When there is more than a megabyte of changes records in the redo log buffer cache
• Before DBWn writes modified blocks in the database buffer cache to the data files
• Every 3 seconds.
Because the redo is needed for recovery, LGWR confirms the commit only after the redo is
written to disk.
LGWR can also call on DBWn to write to the data files.
Note: DBWn does not write to the online redo logs.

System Monitor (SMON)

If the Oracle instance fails, any information in the SGA that has not been written to disk is lost. For example, the failure of the operating system causes an instance failure. After the loss of the instance, the background process SMON automatically performs instance recovery when the database is reopened. Instance recovery consists of the following steps:

1. Rolling forward to recover data that has not been recorded in the data files but that has been recorded in the online redo log. This data has not been written to disk because of the loss of the SGA during instance failure. During this process, SMON reads the redo log files and applies the changes recorded in the redo log to the data blocks. Because all committed transactions have been written to the redo logs, this process completely recovers these transactions.
2. Opening the database so that users can log on. Any data that is not locked by unrecovered transactions is immediately available.
3. Rolling back uncommitted transactions. They are rolled back by SMON or by the individual server processes as they access locked data. SMON also performs some space maintenance functions:
• It combines, or coalesces, adjacent areas of free space in the data files.
• It deallocates temporary segments to return them as free space in data files. Temporary segments are used to store data during SQL statement processing.

Process Monitor (PMON)

The background process PMON cleans up after failed processes by:
• Rolling back the user’s current transaction
• Releasing all currently held table or row locks
• Freeing other resources currently reserved by the user

Archiver (ARCn)

All other background processes are optional, depending on the configuration of the database; however, one of them, ARCn, is crucial to recovering a database after the loss of a disk. As online redo log files fill, the Oracle server begins writing to the next online redo log file. The process of switching from one redo log to another is called a log switch. The ARCn process initiates backing up, or archiving, of the filled log group at every log switch. It automatically archives the online redo log before the log can be reused, so that all of the changes made to the database are preserved. This enables the DBA to recover the database to the point of failure, even if a disk drive is damaged.

Archiving Redo Log Files

One of the important decisions that a DBA has to make is whether to configure the database to operate in ARCHIVELOG or in NOARCHIVELOG mode. 

NOARCHIVELOG Mode: In NOARCHIVELOG mode, the online redo log files are overwritten each time a log switch occurs. LGWR does not overwrite a redo log group until the checkpoint for that group is complete. This ensures that committed data can be recovered if there is an instance crash. During the instance crash, only the SGA is lost. There is no loss of disks, only memory. For example, an operating system crash causes an instance crash.

ARCHIVELOG Mode: If the database is configured to run in ARCHIVELOG mode, inactive groups of filled online redo log files must be archived before they can be used again. Since changes made to the database are recorded in the online redo log files, the database administrator can use the physical backup of the data files and the archived online redo log files to recover the database without losing any committed data because of any single point of failure, including the loss of a disk. Usually, a production database is configured to run in ARCHIVELOG mode.

Logical Structure

A logical structure hierarchy exists as follows:
• An Oracle database is a group of tablespaces.
• A tablespace may consist of one or more segments.
• A segment is made up of extents.
• An extent is made up of logical blocks.
• A block is the smallest unit for read and write operations.

Upgrade Database from Oracle 10.2.0.4 To Oracle 11gR1

Upgradation of Oracle 10g(10.2.0.4.0) to Oracle 11g (11.1.0.6.0):

We cannot upgrade Oracle software from 10g to 11g like upgrading 10.2.0.1.0 to 10.2.0.4.0 , because it is not a patchset. 

So here we need to upgrade database from 10g to 11g. 

below steps are for upgrading database from 10g to11g

Assume that we have already installed Oracle 10g s/w in our system with database PROD.

Oracle 10g home -->/u01/Oracle/product/10.2.0/db_1

(here Oracle 10g os  user is oracle)

then

Step1: 

 we have to download Oracle 11gR1 software from metalink site using username and password.

unzip that Oracle 11g r1 s/w

Unzip linux_11gr1.zip

Then it generates database directory.  Then we have to change the ownership of that s/w or set the appropriate permissions to our user

Chown –R oracle:dba /Oracle11g/database

Before installing software ,we need to check prerequisites for installing Oracle 11g. so we need to verify the installation guide

-->we have to check the hardware requirements

-->we need to create appropriate directories

-->we need to check kernel parameters

xhost +

Then  su – oracle

Cd /database

./runInstaller

We need to select directory where to install s/w and install s/w only without database

-->assume that oracle11g home --> /u01/Oracle/product/11.1.0/db_1

After successful completion of installation

Step 2: Prerequisites for Oracle 10g to 11g Upgradation

In Oracle 11g home --> /uo1/Oracle/product/11.1.0/db_1/rdbms/admin/

There is one script i.e., utlu111i.sql(which is pre upgrade information tool)

Then run that script from Oracle 10g database

Export ORACLE_HOME=/u01/Oracle/product/10.2.0/db_1

Export ORACLE_SID=PROD

Export PATH=/u01/Oracle/product/10.2.0/db_1/bin:$PATH

After exporting env variables

-->sqlplus ‘/as sysdba’

Startup

@u01/Oracle/product/11.1.0/db_1/rdbms/admin/utlu111i.sql

Then it generates the pre upgrade information tool.. inorder to upgrade the database we need to solve those warnings which are generated after running the preupgrade information tool

Here after running utlu111i.sql script I got some warnings 

Warning: Data Timezon version error

Means if the data timezone version is <4 at that time only this timezone warning is generated.

So to solve this issue we need to download utltzpv4.sql from metalink

And run that script

Sql>@utltzpv4.sql

Warning: Obsolete Parameters

Background_dump_dest -à diagnostic_dest

User_dump_dest-àdiagnostic_dest

Core_dump_destàdiagnostic_dest

To solve this problem we need to remove those adump,bdump,cdump and udump from the parameter file and replace with single diagnostic_dest

Warning: Database contains stale optimizer statistics

Component schemas with stale statistics..

SYS

SYSMAN

To solve this problem

Connect as sys user and gather statistics

Conn /as sysdba

Connected

Sql> Exec DBMS_STATS.GATHER_DICTIONARY_STATS;

Sql>Exec DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS);

Sql>Exec DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);

Warning: Invalid objects

To solve this issue run the utlrp.sql (recompile)

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

It recompiles the objects

To verify that there is invalid objects are not

Sql>select count(*) from dba_object where status = ‘INVALID’

These are some warnings. We need to solve those problems to perform upgradation. Because it reduces the time when upgrading.

Step 3: 

After successful completion of prerequisites of upgradation.

We need to copy the parameter file (pfile ) of Oracle 10 database prod to Oracle 11g HOME/dbs location

And edit that pfile

In pfile

Db_compatible = ’11.1.0’

Remove adump,bdump,cdump,udump 

Replace with diagnostic_dest=’/u01/Oracle/product/diagns’

Export ORACLE_HOME=/u01/Oracle/product/11.1.0/db_1

Export ORACLE_SID=PROD

Export PATH=/u01/Oracle/product/11.1.0/db_1/bin:$PATH

After exporting the env variables

Sqlplus ‘/as sysdba’

Sql>startup upgrade;

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

This script upgrades the database it takes lot of time

For me it takes 49 minutes because my database is very small so it takes 49 minutes

After successfully completion of this script

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

This script shows the post upgrade status

 It shows the entire info after upgradations and how much time it takes to upgrade the database

Sql>@?/rdbms/admin/catuppst.sql

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

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

Sql>select  count(*) from dba_objects where status = ‘INVALID’;

Sql>select * from v$version;

---------------------------------------This is the Upgradations Steps --------------------------------------

Upgrade Database from Oracle 10.2.0.1 To 10.2.0.4

Upgrade Database from Oracle 10.2.0.1 To 10.2.0.4 

Requirements: 

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

 To Check database components status is valid or not and version is 10.2.0.1

sql>Column comp_name format a40

sql>Column version format a12

sql>Column status format a6

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

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

SQL> select * from v$version;

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.

$ sqlplus / as sysdba

SQL> shutdown immediate

Database closed

Database dismounted.

ORACLE instance shut down.

$ isqlplusctl stop

iSQL*Plus 10.2.0.1.0

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

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

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

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

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 from www.metalink.oracle.com  and run it.

SQL> @utltzpv4.sql

DROP TABLE sys.sys_tzuv2_temptab CASCADE CONSTRAINTS

ERROR at line 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 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.

PL/SQL procedure successfully completed.

Commit complete.

Once the script finishes successfully execute the following query

sql>column table_owner format a4

sql>column column_name format a18

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

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

$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  608174080 bytes

Fixed Size                  1268896 bytes

Variable Size             171967328 bytes

Database Buffers          427819008 bytes

Redo Buffers                7118848 bytes

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

SQL> shutdown immediate

Database 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.

$ sqlplus / as sysdba

Connected to an idle instance.

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

PL/SQL procedure successfully completed.

TIMESTAMP

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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> spool off

Step 5: Post installation steps

sql>set lines 10000

sql>set pages 1000

sql>column comp_name format a40

sql>column version format a12

sql>column status format a6

sql>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.

SQL> select * from v$version;

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.


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