Flowers To India

Flowers To India
Send cakes,flowers to India

Tuesday, January 22, 2008

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

Monday, January 21, 2008

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

Saturday, January 19, 2008

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