20-01-2006

20-01-2006

Apart from Oracle many RDBMS are present, Cybase, DB2 from IBM and SQL 7 from Microsoft are other RDBMSs.

Oracle is available in all platforms where as other databases have limitation for their support for other Operating Systems.

Over the years Cybase has come in race. DB2 is considered one of the toughest competitor of Oracle.

Oracle structure is similar to rather structure of other databases is similar to Oracle structure.

Oracle Architecture:

Any system has three main parts:

1. Storage structure.
2. Memory structure.
3. I/O, process structure.

Data is stored in hard-disk. It is transferred to memory via I/O process.

Storage structure can be seen from 2 prospects:

1. Logical
2. Physical

Any database is not permitted to control the hardware in fact it’s the Operating System that covers the Database Storage Management.

1. Logical: It is the way Oracle looks at the data stored.
2. Physical: It is the way OS looks at the data stored.

There is logical and physical execution of command, restoration, storage etc.

Logical	Physical
Table space	Parameter file
Schema	Control files
	Data files
	Redo log files

Parameter file contains the parameters under which Oracle functions.

There are about 250-300 parameters in Oracle. But under normal conditions parameter file contains at the most 50-60 parameters. They are sufficient for normal functioning of Oracle. For different and extreme environments you need to tweak the parameters.

Parameter file is a text file. In UNIX we use VI editor to edit this file and in Microsoft we use notepad to edit this file.

Parameter file comes as a sample file from Oracle Corporation. You can create your own text file. The generic name is init.ora.

An Oracle DBA is supposed to use this file according to the environment he needs. Its syntax is initSID.ora. You can use your own name in place of SID. Say you want to use abc then name of your parameter file will be initabc.ora. Or say if you want to use xyz then name of your parameter file will be initxyz.ora so and so forth. The maximum length of name which you can use in place for SID can be of at the most 8 characters. But Oracle recommends the length to be of 4 characters.

Up to Oracle 8i, initSID.ora file was read when Oracle was booted or started to set the environment conditions as per need. It is the first file which Oracle reads while loading or booting. From Oracle 9i the concept of parameters changed completely.

Suppose you change a parameter of initSID.ora dynamically. This change would remain till server shuts down. Once the server is rebooted the dynamically made changes would no longer exist. This was the scenario before Oracle 9i. But from Oracle 9i onwards, a thought was made to store or preserve the dynamically made changes in a separate file called Spfile. Spfile stands for Sever Parameter file.

*: There is no concept of extentions in Oracle.

Spfile is binary file of initSID.ora file. Along with the contents of initSID.ora file Spfile also contains the dynamically made changes.

Limitation of Spfile:

Suppose you change a parameter of Spfile from x=20 to say x=50. These changes would be stored in Spfile. Now if you want to change the second parameter you can’t do so because you cannot change the Spfile i.e. changes are need to be made in initSID.ora file which are then incorporated in Spfile. Thus you need to first convert already existing Spfile into initSID.ora file and then make desired changes in it. The newly formed Spfile would then contain both the changed parameters.

Important parameters are:

1. Name of Database.

2. Global Name of Database.

3. Names of control files.

4. db block size. Block size is edited only once, but once it is set you cannot change there after because it can lead to serious loss of data.

5. Auditing of database.

6. Modes of database: (MTS/DDS)

7. Archival destination.

8. User dump.

9. Background Dump.

SGA parameters are important parameters.