Upgrade Oracle Database 10.2.0.1 to 10.2.0.2

If you are installing Content DB, the db version has to be 10.2.0.2 in Windows Platform.
The upgrade was fairly simple process. But it was loooongggg...The patchset to download is 4547817. Login http://metalink.oracle.com and search for this patch.
Download the 570 + MB patch, unzip it to temp folder and run the installer.
It helps to go through the patch note, but it was fairly straight forward if you are not using RAC.
Stop all services in the 10.2.0.1 home. Run the setup. Select the home of the 10.2.0.1 database.
Once you install the new 10.2.0.2, follow the patch note to perform the post installation steps. In short they are to
-Start the listener
-Start the database in the upgrade mode (startup upgrade, once you have logged in as sys)
-Run the catupgrd.sql script (in rdbms\admin folder) (plan for a couple of hours !!)
-Shutdown and Start the database-Run the utlrp to compile all invalid objects (optional step)-
- Some more steps if you are using the recovery manager catalog.

difference between the sysdba and sysoper

sysdba user have all privilages of sysoper in addition to that
sysdba has following privilages
1) perform incompleate recovery
2) create database
3) see all user objects

upgrading database rhel3 to rhel4

upgrading database from rhel3 to rhel4

The steps to be followed if you are upgrading already existing OS without formatting OS partition.

1. Upgrade the OS from RHEL 3 to 4.

2. Install the required RPMs. Please refer to Metalink Note 169706.1 for the list of required RPMs to be installed. This is because the list of packages required for RHEL 3 and 4 are different.

3. Relink Oracle binaries. For more information about how to relink Oracle binaries, please refer to Metalink Note 131321.1

4. Here there is no need to configure the kernel parameters and Oracle user's environment because this will be preserved while upgrading the OS without formatting OS partition.

The steps to be followed if you are upgrading to RHEL 4 by formatting OS partition and re-installing RHEL 4.

1. Take backup of the files /etc/oraInst.loc and /etc/oratab files. Note down the Oracle database user(oracle) and groups (oinstall, dba) ids.

2. Install RHEL 4. While installing make sure that you have selected default RPM installation. This is mandatory requirement to install Oracle database.

3. Install all other required packages from Metalink Note 169706.1.

4. Create Oracle database user(oracle) and groups (oinstall, dba). Make sure that you have these Oracle users and groups with the same ids and names that you had in RHEL 3.

5. Configure the kernel parameters and Oracle user's environment as per installation guide.

6. Restore the files /etc/oraInst.loc and /etc/oratab files that you have taken backup in step 1.

7. Relink Oracle binaries. For more information about how to relink Oracle binaries, please refer to Metalink Note 131321.1.

8. Run $ORACLE_HOME/root.sh script that creates dbhome, oraenv and coraenv scripts in the directory /usr/local/bin.

Recovery in Archive log Mode

Recovery in Archive log Mode
1.Closed Database Recovery
2.Open Database Recovery with database initially open
3.Open Database Recovery with database initially closed
4.Datafile Recovery with no datafile backup


1.Closed Database Recovery
Situations where this mode is applicable
1.When the system datafile is missing
2.When the undo datafile is missing
3.Large number of datafiles are missing

SQL> shut abort
Now physically copy the required datafiles from hot backup
SQL> startup mount
Check which datafile needs recovery by v$recover_file
SQL> recover database
SQL>alter database open

2.Open Database Recovery with database initially open
Situations where this mode is applicable
1.other than system undo data files are missing

Database is open stage
Offline the particular table space
Physically copy the missed files from hot backup

Recover data file name or number
Make table space online

SQL> alter tablespace tablespacename offline immediate
Copy the particular datafile which is missing from hotbackup

SQL> recover datafile ‘datafile path or number’
SQL>alter tablespace online

3.Open Database Recovery with database initially closed
Situations where this mode is applicable
1.other than system undo data files are missing
2.when the database is in the closed in abnormal state due to hardware issue

SQL>startup mount
SQL>alter database datafile ‘ path’ offline
SQL>alter database open
Copy the particular datafile which is missing from hotbackup
SQL>recover datafile ‘path or number’
SQL>alter datafile ‘path’ online

4.Datafile Recovery with no datafile backup

Situations where this mode is applicable
Normal Datafile is missing if no back up is there
SQL> startup mount
SQL> alter database create datafile ‘path’
SQL> recover datafile ‘path’
SQL>alter database open;

Or
If database is in open stage
SQL>alter datafile ‘file’ offline
or
SQL>alter tablespace t.name offline
SQL>alter database create datafile ‘filepath’
SQL>recover datafile ‘file path’
SQL>alter datafile ‘file path’ online

password file creation

What is password file
Oracle provides a password utility, orapwd, to create a password file. When you connect
using SYSDBA privilege, you are connecting as SYS schema and not the schema associated
with your username. For SYSOPER, you are connected to the PUBLIC schema.
What is the use of password file
By using password file you can create a user with sysdba privilages
and you can connect remotly with sysdba privilages
Creating a password file
Go to dba directory and create a password file by usein orapwd utility
[satish@host dbs]$ pwd
/oracle/satish/9i
[satish@host dbs]$ orapwd file=orapwsatish password=welcome
Then password file will be created
after set the parameter
remote_login_passwordfile=exclusive in pfile or spfile
then password file will be ready
after you can create a user with sysdba privilages and you can connect with sysdba prives
creating a user with sysdba privilages
SQL>show user
sys
SQL>create user satish identified by welcome
user created
SQL>grant sysdba to satish
Grant succeeded.

then normal user is ready with sysdba privilages
connect user with sysdba privilages
SQL> conn satish/welcome as sysdba

Note:-
Set the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE or SHARED
where:
EXCLUSIVE indicates that only one instance can use the password file and that
the password file contains names other than SYS. Using an
EXCLUSIVE password file you can grant SYSDBA or
SYSOPER privileges to individual users.
SHARED indicates that more than one instance can use the password file.
The only user recognized by the password file is SYS. You
cannot add users to the SHARED password file.

TABLE SPACE MANAGEMENT

A small database might need only the SYSTEM tablespace; however, Oracle recommends that
you create additional tablespaces to store user data, user indexes, undo segments, and
temporary segments separate from data dictionary.

The DBA can create new tablespaces, resize data files, add data files to tablespaces, set and
alter default segment storage settings for segments created in a tablespace, make a tablespace
read-only or read-write, make a tablespace temporary or permanent, and drop

tablespaces.

• The data in an Oracle database are stored in tablespaces.
• An Oracle database can be logically grouped into smaller logical areas of space known
  as tablespaces.
• A tablespace can belong to only one database at a time.
• Each tablespace consists of one or more operating system files, which are called data files.

Table spaces are two types

1)system tablespace 2)nonsystem tablespace

SYSTEM tablespace

*Created with the database
*Contains the data dictionary
*Contains the SYSTEM undo segment

Non-SYSTEM tablespaces

*Separate segments
*Ease space administration
*Control amount of space allocated to a user

Space Management in Tablespaces

Table spaces are managed by two types

1)locally managed 2)Dictionary managed

locally managed:-The extents are managed with in the tablespace via bitmaps

*Free extents recorded in bitmap
*Each bit corresponds to a block or group of blocks
*Bit value indicates free or used

Dictionary managed:-the extents are managed by the data dictionary segments in dictionary managed tablespace can have customized storage.

*Free extents recorded in data dictionary tables

syntax for creation of tablespaces

SQL>create tablespace svs datafile '$ORACLE_HOME/oradata/svs.dbf ' size 10m extent management local;

SQL>create tablespace svs1 datafile '$ORACLE_HOME/oradata/svs1.dbf' size 20m extent management local autoallocated;

SQL>create tablespace svs2 datafile '$ORACLE_HOME/oradata/svs2.dbf ' size 30m extent management local uniform size 10k;

SQL>create tablespace svs3 datafile '$ORACLE_HOME/oradata/svs3.dbf ' size 10m extent management dictionary;

SQL>create tablespace svs4 datafile '$ORACLE_HOME/oradata/svs4.dbf ' size 10m extent management dictionary default storage (initial 1m next 1m pct increase0);

we can give storage parameters in three levels

1)server level

2)tablespace level

3)segment level

Configure database noarchive to archive log mode

Configure 9i database noarchive to archive log mode

create a directory for archives
[satish@host satish]$cd 9i/oradata
[satish@host oradata ]$pwd
/oracle/satish/9i/oradata
[satish@host oradata]$mkdir archs
then open the parameter file and add this parameters
[satish@host oradata]$cd $ORACLE_HOME/dbs
[satish@host dbs]$vi initsatish.ora
#add this lines
log_archive_start=true
log_archive_dest='/oracle/satish/9i/oradata/archs'
log_archive_format=%s%t
#save and exit
connect to sql promt with sysdba
[satish@host satish]$sqlplus /'as sysdba'
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
then database will be open with archive log mod
if you want to check your database is archive mod or noarchive mod then use this command

SQL>archive log list

--satish

MAINTAINING REDO LOG FILES

REDO LOGFILES

Redo log files provide the means to redo transaction in the event of a database failure

Online redo log file groups

• A set of identical copies of online redo log file is called an online redo log file group
• The LGWR background process concurrently writes the same information to all online redo log files in a group
• The oracle server needs a minimus of two online redo log file groups for the normal operation of a database

Online redo log file member

• each online redo log file in a group is called a member
• each member in a group has identical log sequence number and of the same size
• the corrent log sequence number is stored in the control file and in the header of all datafiles

Log switch

When the current online redo logfile group filled LGWR begins writing to the next group

this is called a log switch

this command is showing the redolog groups

SQL>select * from v$log;

this command is showing the redolog files

SQL>select * from v$logfile;

adding online redo log file group

SQL>alter database add logfile group3('/oracle/satish/9i/oradata/redo3a.log','/oracle/satish/9i/oradata/redo3b.log') size 5m;

adding online redo logfile member

SQL>alter database add logfile member '/oracle/satish/9i/oradata/redo3c.log' to group 3;

Relocation or renaming online redo log files

tow wayes is there

1)alter database

SQL>alter database rename file 'path1' to 'path2'

2)add new member and drop old member

Drop online redo log file groups

SQL>alter database drop logfile group 3;

• An instance require atleast two groups of online redo log files
• An active or current froup can not be dropped

Droping online redo log file members

SQL>alter database drop logfile member '/oracle/satish/9i/oradata/redo3a.log';

• we can not drop the last member
• we can not drop the current member
• if the databse is running in archive mode and the log file group to which the member belong is not archived then the member can not be dropped

Resize the redo log file

we can not resize the redo log file

just we can create another file and delete oldfile.

Forcing log switch

SQL>alter system switch logfile;

--satish

multiflexing of control files

Control File Contents

The information in the control file includes:
• Database name is taken from either the name specified by the initialization parameter
DB_NAME or the name used in the CREATE DATABASE statement.
• Database identifier is recorded when the database is created.
• Time stamp of database creation is also recorded at database creation.
• Names and locations of associated data files and online redo log files are updated when
a data file or redo log is added to, renamed in, or dropped from the database.
• Tablespace information is updated as tablespaces are added or dropped.
• Redo log history is recorded during log switches.
• Location and status of archived logs are recorded when archiving occurs.
• Location and status of backups are recorded by the Recovery Manager utility.
• Current log sequence number is recorded when log switches occur.
• Checkpoint information is recorded as checkpoints are made.


Multiplexing the Control File using pfile

1)shutdown the database
2)By using OS command copy the oldfile to newfile
3)In initsid.ora file change this control_file=(oldfilepath,newfilepath')
4)startup the database

SQL>shut immediate
SQL>!
[satish@host oradata]$pwd
/oracle/satish/9i/oradata
[satish@host oradata]$ls
control.ctl redo1.log redo2.log system.dbf undo.dbf
[satish@host oradata]$cp control.ctl control1.ctl
[satish@host oradata]$cd /oracle/satish/9i/dbs
[satish@host dbs]$vi initsatish.ora
#change this parameter
control_files=(/oracle/satish/9i/oradata/control.ctl,oracle/satish/9i/oradata/control1.ctl)
#save and exit
[satish@host dbs]$ cd
[satish@host satish]$sqlplus /'as sysdba'
SQL>start up
then database will be start with two control files
if you want check youer control files use this command
SQL>show parameter control_files


Multiplexing the Control File using spfile

SQL>alter sysytem set control_files='$ORACLE_HOME/oradata/control.ctl','$ORACLE_HOME/oradata/control1.ctl','$ORACLE_HOME/oradata/control2.ctl' scop=spfile;
SQL>shut immediate
SQL>exit
[satish@host satish]$
[satish@host satish]$cd $ORACLE_HOME/oradata/
[satish@host oradata]$pwd
/oracle/satish/9i/oradata
[satish@host oradata]$ cp control.ctl control2.ctl
[satish@host oradata]$cd
[satish@host satish]$sqlplus /'as sysdba'
SQL>startup
then database will be started with 3 control files
if you want see the control file then
SQL>show parameter control_files
or
SQL>select * from v$control_file

--satish

SPFILE CREATION

SPFILE CREATION

If you know your database is using pfile or spfile like this
SQL>show parameter spfile
then if it will be displayed strig value is null then your database is running by using pfile
other wise it is running by using spfile
create spfile
SQL>create spfile from pfile;

then spfile will be created default location is $ORACLE_HOME/dbs
spfile file name is spfilesatish.ora means spfile.ora

after shout down and startup the database
defult the database is startup by using spfile
if spfile is there but you want to start the database by using pfile then use this command
SQL>startup pfile=$ORACLE_HOME/dbs/initsatish.ora

what is the use of spfile
By using spfile you can change parameter values dynamically. That means if you want to change parameter value
no need to shutdown the database
offcourse you can chnge some parameters dynamically by using pfile but thsoe parameter values are not permanent

By using spfile you can change parameter value this type
SQL>alter system set parameter=(parametername) scope=memory or spfile or both;

database creation on 9i

DATABASE CREATION ON 9I

Go to oracle s/w directory
I installed s/w in /oracle/satish/9i
So go to that directory and create 2directoryes like
[satish@host 9i]$ pwd
/oracle/satish/9i
[satish@host 9i]$ mkdir oradata dump
go to dump directory and create bdump udump cdump directoryes like
[satish@host dump]$ pwd
/oracle/satish/9i/dump
[satish@host dump]$ mkdir bdump cdump udump
wile instalation time in ORACLE_HOME/dbs init.ora file will be come
then copy that file to initsid.ora like
[satish@host dbs]$ pwd
/oracle/satish/9i/dbs
[satish@host dbs]$ cp init.ora initsatish.ora
after open initsatish.ora by using vi editor and make changes like
db_name=satish
control_file=(/oracle/satish/9i/oradata/control.ctl)
#and add this lines
background_dump_dest='$ORACLE_HOME/dump/bdump'
user_dump_dest='$ORACLE_HOME/dump/udump'
core_dump_dest='$ORACLE_HOME/dump/cdump'
compatible=9.2.0
undo_management=auto

save and exit
after connect to sql promp with sysdba like

[satish@host satish]$ sqlplus /'as sysdba'
after sql prompt will be come then nomount the database like

sql> startup nomount
then data base will be go to nomount stage after go to os and create a file by using vi editor and after run that file in sql promt like this

sql> !
[satish@host satish]$ vi createdb.sql

create database satish
logfile
group 1('/oracle/satish/9i/oradata/redo1.log') size 5m;
group 2('/oracle/satish/9i/oradata/redo2.log') size 5m
datafile
'$ORACLE_HOME/oradata/system.dbf' size 500m
undo tablespace undotabs
datafile '/oracle/satish/9i/oradata/undo.dbf' size 300m
charecte set us7ascii
;

#save and exit
after connect to sql prompt with sysdba
and take database with no mount stage
and run that script like
SQL>sqlplus 'as sysdba'
SQL>startup nomount
SQL> @/oracle/satish/createdb.sql
then database will be created and default database in open stage
then run this fallowing scripts


SQL>@/oracle/satish/9i/rdbms/admin/catalog.sql
SQL>@/oracle/satish/9i/rdbms/admin/catproc.sql
after connect system/manager run the pupbld.sql script like
SQL>conn system/manager
SQL>@oracle/satish/9i/sqlplus/admin/pupbld.sql

then database will be ready


best of luck
--satish

instalation oracle 9i on RHEL3.0

required Redhat packages compat-db,copat-gcc,compat-gcc-c++,compat-libstdc++,openmotif,setarch
those packges are available in redhat os cds
install that packages
[ root@host packlocation] # rpm -ivh compat-db* --aid --force
means rpm -ivh --aid --force
install all packages by using above command

check package is installed or not
[ root@host packlocation] # rpm -qa comepat-db*
means rpm -qa (packname>


after run this commands

[root@host root]#mv -i /usr/bin/gcc /usr/bin/gcc323
[root@host root]#mv -i /usr/bin/g++ /usr/bin/gcc
[root@host root]#ln -s /usr/bin/g++296 /usr/bin/g++

required patch 3006854
down load that pach from metalink and unzip that and applay that patch
note-: after apply the patch restart the system

add group

[root@host root]#groupadd dba
add the user with home directory /oracle and group dba

[root@host root]#useradd -d /oracle/satish -g dba satish
hear /oracle is local mount point you can give any mount point name

how to check your mount points

[root@host root]#df -h
set password for user

[root@host root]#passwd satish
give new password and retype new password
then password will be set
set ora inst location

[root@host root]#vi /etc/oraInst.loc
inventory_loc=/oracle/satish/oraInventory

save and exit

check graphicaly mod is exist or not
[root@host root]#xclock

go to created user

[root@host root]#su - satish

set the bashrc in satish user

[satish@host satish]$vi .bashrc
#come to last line and writ it
export ORACLE_SID=satish
export ORACLE_BASE=/oracle/satish
export ORACLE_HOME=/oracle/satish/9i
export LD_ASSUME_KERNEL=2.4.19
export PATH=$PATH:/$ORACLE_HOME/bin:.
#save and exit

run the bashrc

[satish@host satish]$ . .bashrc

run the instalation

go to the 9i software path and run

[satish@host Disk1]./runInstaller


after select what ever packeges you need and install

you can download the software from this site

http://www.oracle.com/technology/software/products/oracle9i/index.html

best of luck
--satish