High Water Mark

It is a marker that shows you the point when you last inserted the data in the table. Let us simplify it with an example. Suppose you created a new table initially with no value. The high water mark will be at initial position say at 0. You made an insertion. The HWM will rise by one and will reach at position 1. Similarly as you will make more and more insertions the HWM will also rise; suppose you reached at a position where the value of HWM was 100. Now you started deleting values from the table. The value of HWM will not fall with the deletion of values from the table. So you can easily say that HWM shows the highest level of insertion you have ever done in that particular table.

Every time you give the command of select to fetch values from a table the oracle searches the table to HWM. It does not matter whether your last value is stored on position 10 or 90; if your HWM is 100 then oracle will search up to 100 positions. Normally for deleting values from the table you use the command of delete. This does not affect your HWM even if you have deleted all the values from the table. To bring the HWM to the initial position you can use the command:

SQL>truncate table table_name;

The high water mark is only meaningful in the table segment.

Row Structure

The way Oracle stores a row of a table is known as row structure. A row consists of two parts; one is header and the second is data. In the header part all the control information of that particular row is stored. Control information of a row consists of three parts. First is the links of row migration and row chaining. Secondly is the lock information. Locks are used so that no two transactions should update the row simultaneously. And the last thing is how many columns are there in that row. Every row in a table has same number of columns; so why there is a need for storing this information separately in each row. This is one of the features that only oracle provides. We will see the advantage of this in just a moment.

Next is the data portion. Data portion contains the actual data stored. Each entry of data has two parts. One is known as column length (CL) and the other is column value (CV). Oracle does not store the null values. This is the feature I talked about earlier. Suppose for a particular row there are five columns in which the last three has null values. Oracle does not store the null values altogether. Then how it knows that there is a null value? As I mentioned that oracle stores the number of columns for each row. So the value stored in the header will be 5 for that row but the actual values that are present are two. Oracle will realize here that the last three values are null. Real world databases do have a lot of null values. So oracle wastes a little space for storing the number of columns information in the header of every row but on the other hand it saves a lot of space by not storing the null values.

LOB Introduction

Char and Varchar2 data types are used to store text columns in an Oracle database. While for the normal requirements they are OK, but they are not as suitable when it comes to store large objects. By large objects I mean storing of entire PDF documents for example or storing of an image. Traditionally Oracle provided two data types for these situations namely as RAW and Long RAW. But the use of these data types was inherent to some limitations. The first one was that you cannot have more one column of RAW or Long RAW data types in one table. The storage capacity was limited to 2GB. And last you cannot perform SQL operations on the subset of data stored as RAW or Long RAW.

In Oracle 10g Release 1 and above (not sure of 9i) Oracle introduced Large Objects or LOB for short. The introduction of LOB data types culminates all the restrictions that were present in RAW and Long RAW data types. LOB itself is not a data type. Rather it is a category of four data types which are divided into two distinct categories. The categorization of LOB data types is based on their storage. The two categories are:

1. Internal

2. External

Internally stored LOB objects are those which are stored inside database. They are treated just like any other object inside database. And all SQL operations are valid for them that are valid for other data types. There are three data types in this category:

1. CLOB: It is used to store large documents and stands for Character Large Objects.
2. NCLOB: It is also used to store large documents which are in character format. But it stores these documents using a different format namely as National Character Set.
3. BLOB: This data type is used to store large objects which are in binary format. For example an image.

Externally stored large objects are those objects which are stored outside database but whose reference is maintained inside database. The storage of these objects is inherent to the limitations of Operating System files. Normal SQL operations performed on other internally stored objects will not be applicable to these externally stored objects. The only data type in this category is BFILE. The column of BFILE data type will be used only to store the location of large object file.

Oracle Data Types

Data types in oracle can be divided in two general groups. First are the user defined data types. Oracle database allows you to create your own data types and use them if you find it necessary. Furthermore Oracle is an object relational database management system; that allows you to create objects and store them in the database. Any thing that has data and some functions in it is known as object.

The second group of data types is known as built in data types. These are also known as Oracle data types. These data types can be divided into three more groups. First is scalar data type. Scalar data types are those types that can store only one value in their variables. This group includes many data types that are used to store different types of data. For example char and varchar2 are used to store the characters or string values. However there is a difference and that is: suppose you have declared a variable as char (10) and have stored only one character in it. The char data type will reserve space for 10 characters despite the fact that you have stored only one. Other 9 characters space will be wasted. However if you have declared the same variable with varchar2 (10), the space will not be reserved for the number of characters that you have specified (10) rather only for the characters that you actually stored i.e. 1. The number data type is used to store numbers. It stores whole numbers as well as numbers with decimal points. Date is used for storing dates. Well you can also store dates in varchar2 but that will not allow you to use Oracle’s built in functions for dates. Timestamp is used to store date with time. BLOB stands for Binary Large Objects and it is used to store large objects e.g. images, audio data, video data etc. CLOB stands for Character Large Objects and it is used for storing large objects which are in the form of text e.g. a huge document.

The second group in the built in data types is collection. Collection data types are those data types that can store more then one values in their variables. Suppose if you want to store an array in any field of a table you can use the varray data type. And if you want to store a two dimensional array or table in any field of the table then you can use the table data type. This is used when you want to store a table inside another table.

The third and last group in the built in data types of oracle is relationship. There is only one data type in this group i.e. ref. If you want to store a value inside a field which is not in the oracle database; and resides in some other form of application then you can use the ref data type to give the address of that value. In very simple words this data type works solely as a pointer.

Rollback Segments

The primary purpose of rollback segments is to hold old values. Means that when you update a value the updated value is stored in the table and the old value is stored in the rollback segment. So whenever that old value is needed again it is restored from rollback segment. There are three main purposes of storing old values and thus of rollback segments:

1. Transaction Rollback: Suppose you are working in a table. And you updated the name column value of a particular row from Scott to James. And then suddenly you realized that change you made was not right so you give the command of rollback. The original value i.e. Scott will be restored again. How did database come to know that the old value was Scott? The answer is through rollback segment because it’s the rollback segment that stores the old values.
2. Transaction Recovery: The second purpose is transaction recovery. Suppose you have made a change and have not committed it. But another user working on the same database gave the command of commit. LGWR will write because the commit command is given. And when LGWR writes it writes all the changes from redo buffer cache to on-line redo logs. But remember along other things redo logs also store the SCN of transaction and also whether it is committed by its user or not. Some how your DBWR also fires and writes the uncommitted change to data files. At this point in time your power supply goes off. Next time when instance will startup the SMON process will see that which transactions are stored correctly and also which are committed by their own users and which are not. If any transaction is not committed by its own user just like one above it will restore its old value; and it will take its old value from rollback segment.
3. Read Consistency: Read consistency is that something that every good database must posses. It is a sort of yard stick through which the maturity of a RDBMS is measured. In oracle this feature is implemented by making use of rollback segments. Suppose a user has given the query to fetch the first five rows from a table. The query has only fetched first three rows that another query came and updated the last two rows. Now the first query should get the old values because it was given first then the query who updated the rows. Surely it will get the old values but not from the table but from rollback segment. However any other query given after the update command will get the new updated values. In this example two features of oracle came in front of us. They are consistency and concurrency. By the way consistency must always be given the higher priority.

Rollback segments are maintained in a circular fashion. Usually more then one transaction can write in one rollback segment. Up till 8i it was not possible for multiple transactions to write in one extent. But in later versions of 8i and in above all versions it is possible. But still it is not possible for multiple transactions to write in one data block. Once one extent is full it is switched to the other. This is known as Wrap. When all the extents are full then instead of over writing the first one a new extent is created at run time. It is called Extend.

Rollback segments are automatically freed up when the data in them is no longer required. Suppose your rollback segment was full up to 100M and then automatically freed up to 4M. In this case what will happen of extra space? Is it wasted? No it is not wasted instead oracle shrinks the rollback segment up to a value stored in the optimal parameter. Normally the value of optimal parameter is 4M.

With these considerations we have a legacy problem. Consider again the example given in the Read Consistency. First command wants to fetch the first five rows of a table; it has only fetched the first three that the second command updated the last two rows. At this point in time suppose oracle has shrunk the rollback segment; where the first command will get the old values? The answer is from no where. Till 8i it was a huge problem and it is famous too. It is known as “snapshot too old” and the oracle message number for this error is ora-15500. In 9i and above versions it is automatically controlled by oracle itself.

To get information about the rollback segments you can use v$rollname, v$rollstat data dictionary views. To get information about your current active transactions you can use the view named as v$transaction.

Temporary Segments

As its name suggest that this segment is used to hold temporary data. The prime example of this is sorting. So you can say that temporary segments are used when the database does any kind of sorting. When a database does sorting? There are a few events when a database needs to sort values.

1. Order by: Every time you use the command which includes the order by clause the database does sorting and intimately uses temporary segment. The example of an order by command is:

SQL>select * from emp order by ename;

This command will order the results by Employee Name in ascending order. If you want it to order it descending then you will give the command:

SQL>select * from emp order by ename desc;

2. Group by: When you use the clause of group by the database also performs the sorting. The group by clause is used to take aggregate values on a column. The example of group by clause is:

SQL>select dept no, count(*) from emp group by dept no;

3. Distinct: Distinct keyword is used to avoid the multiple show of a single value in a result. If you use command which includes this keyword the database does the sorting. The example of this is:

SQL>select distinct dept no from emp;

4. Creation of an Index: Every time you create an index the database does the sorting. Indexes are created for easy retrieval of data.
5. Union: Union operator is used when you need to merge the outputs of two commands. The use of union operator also involves the sorting process. The example of this is:

SQL>select * from emp union select * from employee;

These are the five situations when the database does the process of sorting. And when it does it stores some values for some time and it stores those values in the temporary segment.

Temporary segments are created inside the temporary tablespace. If you check the V$tablespace data dictionary view you will see the temporary tablespace named as temp. Usually these segments are created one per transaction. And also they are created at run time. Oracle is very clever it creates the temporary segments in advance for the maximum load. In this manner it avoids the creation of these segments at run time and intimately improves performance. The corresponding data file for the temporary tablespace is temp file and it can be seen on the operating system level. If you want more information about the temporary segments on your database then check the data dictionary view named as v$sort_segment.

Oracle Data Block (Continued)

We are down to the most granular unit of storage in an Oracle system i.e Oracle block. If we continue to break up then it is evident that an extent consists of many Oracle blocks. Further more an Oracle block maps to the one or more physical Operating System blocks. And also it is a good practice to have the size of your Oracle block equal to the OS block size.

There are three main parts of an oracle block. These are header, free space and the data part. The header contains the control information. This control information includes the full address of the oracle block itself, information of the addresses of the rows stored in the data block known as row directory, and information of inittrans and maxtrans. The inittrans parameter defines that how many transactions can initially access the data block. And maxtrans defines that how many transactions are allowed to access the data block. Suppose that your inittrans is 3 and your maxtrans is 5. It means that 3 transactions are currently registered in the header and are accessing the data block and total of 5 are allowed to access the Oracle block. To allow two more transactions to be registered and access the data block the header will take the space from free space and allow them to access the data block.

The free space is reserved for the future growth. The data portion grows upwards while the header portion grows downwards. One point worth mentioning here is that free space is used for update command not for the insert command. It means that when an existing row grows in size then the free space is used; otherwise if you want to insert a new row and there is no free space left then a new data block will be used.

Data portion is used for the actual storage of the data. Suppose you have an extent in which data block 1,2,4,7 are free while all other are full. When new data needs to inserted oracle checks for the free data blocks. One way is look up the entire extent and find out which data blocks are used and which are free. But this is a very cumbersome method. What oracle does is it keeps a list of free data blocks in the form of link list. This list is known as free list.

Two thresholds are used to control the data in the data block named as PCT free and PCT used. When the data in the data block reaches the PCT used limit it means that data block has no more space to hold the data. Oracle looks in the free list and picks another data block. Now suppose a user has started deleting data from the data block. In this process if it gets to the PCT free limit then that block is included in the free list. Normally this limit is 40% of the data block. It means that if a data block is full but not up to 40% then it is kept in the free list.

Let us end it with two more very important concepts. Suppose you have a row with a null value for some column. Now you have updated the row with some value; that value is so big that space in the data block is not enough for it. As you are updating the row space will be available for you from free space. But suppose it is not enough also; in this case a new data block will be created for you and the all of row’s data is stored in it. This is known as Row Migration. In other situation suppose a user is inserting data for a particular row and it is so big that space in the data block is not enough for it. In this case you are inserting data not updating therefore no space will be available for you from free space. Instead a new data block is created for you and remaining data is stored in it. It is called Row Chaining. And the individual pieces of data in different blocks for a single row are known as Row Pieces.

If you want some information about the data blocks and free space then you can use the following views: dba_extents, dba_free_space. And also the following command will give you useful information about data blocks. It will tell you the sum of all data blocks in each tablespace of your database.

SQL>select tablespace_name, count (*), max (blocks), sum (blocks)

from dba_free_space group by tablespace_name;

Extents (Continued)

A segment has more than one extent in it. Extents are the basic allocation units. Suppose you created a tablespace with the following command:

SQL>create tablespace test datafile ‘c: \oracle\oradata\dbase\test01.dbf’ size 10M extent management local uniform size 512K.

Here the second size mentioned 512K is the size of extents created within each segment. By uniform we mean that the size will be same for all extents of all segments. You can override this behavior for a segment by using the storage clause e.g.

SQL>create table t1 (t_no number)

storage

(initial 1M

next 1M

);

Now all the extents created for the table T1 will be of size 1M instead of default 512K. By initial we mean the size of first extent and next are used for the every next extent.

As we know that the extents are the basic allocation units. It means that the size is allocated to tablespace according to the size of extent. When you turn the autoextend on then for example a segment needs more space. In the above example the size of more space given at once to the segment will be 1M i.e. the size of the extent.

Segments (Continued)

We all know that a database will always have users of it. Further more a user also has his or her objects e.g. tables, indexes, views etc. In database a user and his/her objects are collectively known as Schema. For each user in the database there must be a tablespace (although a user can have more than one tablespaces but we will consider only one because tablespace is not the topic here). Within each tablespace there are segments. Each segment corresponds to an object that a user owns. For example there may be a table segment for the tables, an index segment for the index etc. Almost all the user objects are stored inside segments except the one thing and that is programs. Stored procedures, triggers and all other type of programs are stored inside the system tablespace.
Oracle database provides different types of segments for storing different types of objects. They are:

1) Table Segment: It is used to store the tables created by the user.

2) Index Segment: It is used to store the indexes created by the user.

3) Cluster Segment: Sometimes for performance reasons two or more tables are stored together on the disc. It is known as clustering. Cluster segment holds the clustered tables.

4) Table partition Segment: When your table is too large then it is partitioned into one or more components. Table partition segment holds the partitioned table.

5) Index Partition Segment: Similarly when your index is too large it is also partitioned and index partition segment is used to store it. It is only present in 8i and above versions.

6) LOB Partition Segment: LOB stands for large objects. By large objects we mean video data, audio data, images etc. There are three data types for storing large objects. BLOB (Binary Large Objects), CLOB (Character Large Objects), NCLOB (Natural Character Large Objects). When any column in a table has a data type from any of above mentioned data types; then that column is not stored in the segment in which the actual table is being stored. There will be separate segment for that particular column and that segment is called LOB Partition Segment.

7) Index Organized Table Segment: Sometimes the table and the index of it are stored in the same segment. That segment is known as index organized table segment.

8) Undo/Rollback Segment: Suppose you have made a change in a table and you gave the command of rollback. The previous value will be restored. Oracle stores all of its old values in the rollback segment. Before 9i it was known as rollback segment and in and after 9i it is known as undo segment.

9) Temporary Segment: Temporary segment is used to hold the temporary objects that the user needs. For example when you are sorting you need an object to temporarily hold a value.

10) Nested Table Segment: When you store a table inside a table nested table segment is used to store a table inside a table.

Tablespace Management (Continued)

Managing Tablespace means to tell that what its extent management is. We know that inside a Tablespace there are many segments which in turn consist of many extents. There are two ways to manage a Tablespace.

1. Dictionary Managed
2. Locally Managed

Suppose there is a tablespace named as T1, inside T1 there is a segment named S1, and inside S1 there are four extents named as E1, E2, E3, and E4. Here we need to remember is that which extents are full and which are empty. For example for extent E1 entry will be:

T1-S1-E1: empty/full.

And similar for all other extents in that tablespace and in other tablespaces in the database. If you are managing your tablespaces using dictionary managed than all this information will be stored in the data dictionary. And data dictionary is consulted every time a tablespace is used. For storing this information in the data dictionary there are two base tables. One is UET$ and the other is FET$. The first one stands for used extent table and stores the information of all the extents that are used or full. The second one stands for free extent table and stores the information of all extents that are free. When an extent is free it will be in FET$ table and after it is filled its entry is removed from FET$ and is stored in the UET$ table.

The other way is to manage the tablespace locally without using the data dictionary. The extent information is held in the headers of data files in the form of bit maps. The bit map will contain all the information about the extents that whether they are free or full. For example a bit map will show 1 for the extent that is full and 0 for the extent that is free. This is a more preferred approach because it removes the overhead of consulting data dictionary every time you need to use the tablespace.

Both approaches are used. But as mentioned that locally managed is much better option and oracle highly recommends it. Locally managed was introduced in 8i. You can also manage the system tablespace. But for 8i release 2 and early versions you can’t manage it using locally managed. In later versions you can do this. But remember that if have managed the system tablespace using locally managed method than you will not be able to manage other tablespaces using dictionary managed method. You had to use locally managed through out the system. This is not the case with dictionary managed.

Logical Structure of Database (Continue)

Don’t confuse it with the logical structure of oracle server i.e. instance. It is the logical structure of database which is the physical part of oracle server. In other words it is the logical structure of the physical part of oracle server. Oracle database can be divided into two parts. One is physical and the other is logical. Physical part of database consists of Data Files and Operating System Blocks. In logical part a database consists of many tablespaces; which in turn consist of many segments. A segment is consisting of many extents and an extent is consisting of many data blocks or they are also known as oracle blocks. I am going to discuss each of them in detail here and in subsequent posts. Logical part can be mapped to the physical part and mapping is already what we know i.e. a tablespace consists of many data files and an Oracle Data Block consists of many Operating System Blocks.

Managing Tablespaces:

In very simple words a tablespace is the logical container of the data stored in the database. Usually a large database is divided into many tablespaces. Remember that tablespace is a logical construct and on physical level only data files exist. Usually a tablespace consists of many data files. Broadly speaking a tablespace will come in one of the following two types.

1. Permanent Tablespace: A permanent tablespace is a tablespace that stores the permanent data. It has two further types.
• System: It stores the data for the system i.e. oracle server. And as we know that oracle server stores all its data in the data dictionary. So you can say that system tablespace stores data dictionary.
• Non System: It stores all the user data that is of permanent nature.
2. Temporary Tablespace: It stores the data that is of temporary nature. For example when you are sorting you need a temporary storage to hold values for some time.

As mentioned system Tablespace stores the data dictionary. It also stores one more thing and that is programs. By programs I mean the triggers, stored procedures etc. In oracle you can create tablespaces of your own. If you do not create oracle does not generates any errors. But it is highly recommended that you create because it helps in managing the database. How? Suppose you have an ERP system in which there is department of sales, another department of production and yet another department of marketing. If you have stored all the data in one tablespace it will be a headache for you to manage it. But if you hade created a separate tablespace for every department; than it will be easy to mange the data of each department. This is called a modular approach. Let us clear it with an example. Suppose at any instant of time you are required to make the data of sales department unavailable for the users. If you have stored all of your data in one tablespace than you have to shutdown your entire database. But if you are following a modular approach i.e. you have created a separate tablespace for every department. Than to make the data of sales department unavailable all you need is to make that tablespace offline. There will be no effect of this on other tablespaces and on database.

Changing Database Modes

By default Oracle database runs in no archive log mode. This means that Oracle will not make any off line copies of online redo logs. Therefore it is highly recommended that you must change it to archive log mode; so that there may be no loss of log information. Now here is a point that archiving is done in two ways i.e. Manual and Automatic. Manual archiving can be a burden on a DBA and it takes too much time. And in some cases it also brings database to its knees. When you turn the database to archive log mode than ARCH process does the automatic archiving. Now the only question remains to be answered is how to change the database mode. Here are the simple steps.

1. First of all shut down the Instance.
2. Than go into the init.ora file and change the following parameters.

Log_archive_start=true

Log_archive_dest=’c: \oracle\oradata\dbase\archive’

3. Mount the Instance.
4. Than give the command;

SQL>alter database archivelog;

SQL>archive log list;

5. The second command will tell you the information about the database that whether it is in archive or noarchive log mode.
6. Finally open the database with the command.

SQL>alter database open;

After completing all these steps you will see that the mode of your database is changed. If you want to change it back to noarchive log mode then first change the parameter Log_archive_start to false and give the command:

SQL>alter database noarchivelog;

Remember that your database should be in mount state when applying these commands. Also you can check the status again by giving the command:

SQL>archive log list.

Now if you are using Oracle 10g then it is a lot easier. All you got to do is to shutdown the database first and then bring it to mount state and give the command:

SQL>alter database archivelog;

As for as the two parameters are concerned the first one is deprecated and second defaults to the flash recovery area.

Instance Recovery

I mentioned in an earlier post that LSN and SCN are greatly used in the instance recovery. As the matter of fact the whole instance recovery is done through these two. Suppose you were working in the database and before you can update the changes the power supply failed. But you have committed the transactions. When you give the command of commit the LGWR writes in the redo log files. As you know that when a check point occurs the DBWR writes and information of LSN and SCN is stored in the control files and in data file’s header. Next time when instance starts up the SMON process checks the LSN number and SCN number in the redo logs and in the control files. If they are the same then the data is consistent and there is no need of recovery. In case of a system failure the SCN number in the redo logs will be greater then the SCN number in the control files. The SMON process will see the difference in the entries and will restore the data for those entries. Suppose when you last saved the data your LSN was 23 and your SCN was 60. At LSN of 23 and SCN of 100 you committed the data but did not saved the data. The LSN and SCN in the control files will be 23 and 60 respectively because this was when you last saved the data. But the LSN and SCN number in the redo logs will be 23 and 100 respectively because this was when you last committed the data. SMON will see that there is difference of 40 entries; means that data of last 40 entries needs to be saved. So it recovers the data and stores it on data files. The whole process is going on background. Users will be allowed to continue their work. In this way oracle ensures that up till every commit or check point your data will be not lost whether you have saved it or not.

Oracle Forms:Client Server-Web

Oracle forms are proprietary platform for building database applications that make use of Oracle database. The reason for their popularity is their tight integration with Oracle database. Although you can make use of other more strong development environments while still using Oracle like .Net and J2EE etc, Oracle forms are by far the most conventional and easy to use environment for database applications that use Oracle database at the back end. Oracle provides a complete development environment rather then just forms. The suite includes Oracle reports, Oracle Designer, Oracle Schema Builder, and Oracle Query Builder among others. From 9i onwards this suite has taken a huge turn in terms of its runtime environment.

Although development environment is not much changed since the release of 6i but runtime environment is changed from client/server to web. Oracle developer suite 10g now uses Oracle Application Server Forms Services for the deployment of Forms applications. Now instead of client/server it uses three tier architecture. Three tiers are as follows:

1. Client Tier

2. Middle Tier

3. Database Tier

Client tier consists of low end machines with some browser installed so that they can view the forms. Oracle provides a utility named as JInitiator for running forms in browser. JInitiator gets installed on the client machine when you run your first form. Client requests the application through middle tier which is typically Application Server. Application Server uses some Java Servlet for processing requests. This middle tier has dual role to play in the entire Forms Services Architecture. It acts as a server to the client tier and as a client to the database tier. And finally the database tier holds all the company’s data.

This new architecture completely changes the way user log in to use the applications. In the client server environment all the user’s credentials were normally stored in the database; so the authentication process was done through database. This has changed. Although you can still use this technique but Oracle recommends you to use the application server’s security implementations. Oracle AS uses the Oracle Internet Directory (OID) as the central repository for all users’ information. The connection string of every user is maintained through Connect Access Descriptors (CAD) in OID. If you use this technique for authentication then you can make your applications to leverage the Oracle AS’s Single Sign On features.

Redo Mechanism (Continued)

Now what happens if LGWR has written on all the redo log groups one by one? It’s a solid question. The answer is that when LGWR has written on all the redo log groups it comes back to the first one. Now here are two considerations. First is that if your database is running in No Archive Log mode than LGWR will start over writing the groups starting from the first one. But if your database is running in Archived Log mode than LGWR will not over write the log group until ARCH (an optional background process) has made a copy of that redo log group. This offline copy of online redo log is called Archived Redo Log. The LSN number of the redo group of which the copy is created is also stored in the copy. By default oracle database runs in No Archived Log mode but it is highly recommended that you must change it to Archived Log mode.

Here are some commands that you can use in dealing with redo log files. First of all if you want to see the information about redo log groups then you can check V$log view. You will see the number of groups, and members in each group and also some other information. You can’t see this information on operating system level; because this is hidden from operating system. If you want to see the exact location of the members of the redo log groups then open the V$logfile view.

If you want to add a redo log group than use the command:

SQL>alter database add logfile group 4 (‘c: \oracle\oradata\dbase\log41.log’) size 1M;

The second part is the path of the member of the group you created. Name of the member can be anything.

If you want to add a member files in the existing group than use the command:

SQL>alter database add logfile member ‘C: \oracle\oradata\dbase\log42.log’ to group 4;

Remember here you will not mention the size of the file because it is same as of other group members; in this case it will be 1M.

If you want to drop a member files from a group than use the command:

SQL>alter database drop logfile member ‘C: \oracle\oradata\dbase\log42.log’;

If you want to drop a group than use the command:

SQL>alter database drop logfile group 4;

If you want to explicitly switch log at any instant of time than use the following command. Remember that when a log switch occurs than event of check point occurs. And when check point occurs the CKPT process is activated and what it does is already been discussed. This command is useful for synchronization of data and is recommended before and after hot backups.

SQL>alter system switch logfile;

If you want check point to occur than use the following command. The same thing will also happen here except there will be no log switch.

SQL>alter system checkpoint;

Be careful when you are performing all these operations your database should be in mount state not in open.

Redo Mechanism (Continue)

Redo logs are the most important component of oracle database. The way oracle manages its redo logs is known as redo mechanism and is the most salient feature of oracle database. It is this mechanism that differentiates oracle from other database vendors. The basic purpose of redo mechanism is to facilitate recovery. Redo Mechanism involves log buffer, log writer, online redo log, and archived redo log.

Redo log files are logically arranged in the form of groups. Normally each group has two members i.e. two redo log files. The members (files) in a group are of same size. Or you can say that a set of similar copies of redo log files is known as group. This grouping of redo log files is logical. It is hidden even from the operating system. Oracle manages them all by itself. Each group has a number assigned to it which is called Log Sequence Number. Members inside a group have same LSN and it is assigned each time log writer starts writing on redo log files. Within each member of the group there are entries. These are redo log entries; one per transaction. These redo log entries also have a unique number assigned to it which is called System Change Number. These two I mean Log Sequence Number (LSN) and System Change Number (SCN) have a great role to play in the Instance Recovery.

When LGWR writes on these groups it writes on both members of the group simultaneously. If you increase the number of members in the group that extra member will not be an exception to this mechanism. In simple words it uses the concept of multiplexing. When the first group is full it switches to the next and so forth. When LGWR switches from one log group to the other it is known as log switch.

When a log switch occurs the event of check point occurs. Here the CKPT performs both of its functions. First it asks the DBWR to write. Note here the synchronization again that before DBWR, LGWR will write. And second it writes the check point information to control files and to the header of data files. What is the check point information? It includes the LSN and SCN numbers. One point worth mentioning here is that LSN is stored normally of the next redo group (the group to which you are switching); and SCN is stored of the previous group (the group from which you are switching). It is clear now that LSN and SCN stored in the control files and in the data file’s header indicate that until them the data is stored back on disc and is safe.

First the use of multiplexing and then oracle stores the same information in control files and in data file’s header. This shows the concerns that Oracle have about an organization's data.

Oracle V/S ANSI Joins

If you talk about Database Management System now a day it is assumed that you are talking about Relational Database Management System because they are the norm. They have become the standard for information storage and retrieval. It is their power that has made these two business functions more easy and elegant like they were never before. For storing and retrieving data in Relational Database Management System the language used is Structured Query Language. It is shortly termed as SQL. SQL is a standard for successful information storage and retrieval in databases.

The standard of SQL is maintained by American National Standard Institute (ANSI). ANSI keeps track of the requirements and makes the necessary modifications and finally updates the standard. Despite of the standard each database vendor maintains its own version of SQL; so does the Oracle. However the basic functions remain the same. One of the areas in which Oracle SQL is different from ANSI SQL is the way it handles and manipulates the joins. The difference is mainly in the syntax rather in functionality. ANSI SQL puts all the conditions necessary for joins in the “From” clause of “Select” statement and “Where” clause is reserved for any optional conditions. On the other hand Oracle SQL uses “From” clause only to list the tables and aliases and all the conditions (Join Conditions + Optional Conditions) take place in the “Where” clause.

To clarify let us take the all familiar example of Employee-Department relationship. Suppose you want to list department name and all the corresponding employee’s information and for that purpose you intend to use INNER JOIN so that only matching records come in the result set. The syntax in ANSI SQL will be like this;

SQL>select a.dname,b.* from dept a inner join emp b on a.deptno=b.deptno;

The same result can be obtained in Oracle SQL by writing the statement as:

SQL> select a.dname,b.* from dept a,emp b where a.deptno=b.deptno;

Now if you want to see all the departments no matter if they have any employees or not then you will write following ANSI SQL statement;

SQL> select a.dname,b.* from dept a left outer join emp b on a.deptno=b.deptno;

To achieve the same result in Oracle SQL you will need the following statement;

SQL> select a.dname,b.* from dept a,emp b where a.deptno=b.deptno(+);

For RIGHT OUTER JOINS you will use the keyword RIGHT instead of LEFT in the above statement for ANSI SQL and place (+) on the other side for Oracle SQL. ANSI SQL also provides the FULL OUTER JOIN which returns the records from both tables that have not the matching records in the corresponding tables. Oracle does not support the FULL OUTER JOIN.

If you are new to Oracle then don’t panic that you need to learn the syntax of joins again. You can avoid the Oracle syntax joins fully while still working in Oracle. This is possible because Oracle has got full support for ANSI JOINS. You can write all ANSI style queries and they will work for you in Oracle. What if you are stuck to Oracle JOINS and you want to get the results of FULL OUTER JOIN. Here is the simple trick to do this. Just UNION the results of LEFT OUTER JOIN and RIGHT OUTER JOIN and that’s it. As UNION operator eliminates the duplications automatically so the result will be an exact replacement of ANSI SQL’s FULL OUTER JOIN.

Oracle Logminer

Log files are by far the most common way in software applications to keep track of user’s activities. As the world class database server Oracle also has a very strong logging mechanism. The log files in the Oracle database are normally termed as redo logs and the mechanism through which Oracle manages and generates its redo logs is known as redo mechanism. The most important use of these redo logs in the Oracle world is in recovery process. However these redo logs are also used for auditing purposes as well. As being the logs of database they are the perfect place for knowing who logged in and did what. The only question remains to be answered is how? After all Oracle generates redo logs in binary format. Here is the clue. The name of the clue is Logminer. As the name indicates it is used to dig into redo logs and get information out of it. Here is a very brief introduction of it.

Logminer is a tool used to dig into the redo log files. Redo log files store the changes in the database. It is used for both the archived redo log files and for online redo log files. It provides a comprehensive SQL interface to the redo logs; so it can be used as a data audit tool and also as a data analyzer.

Configuration:

There are four basic things that U should be familiar with in order to configure the Logminer.

1. Source Database: it’s the database that has the required redo log files that are need to be analyze.
2. Mining Database: It’s the database that Logminer uses during the phase of analyses.
3. Logminer Dictionary: Logminer dictionary enables the Logminer to return the results in columns names rather then in the form of internal object IDs. Without the dictionary the Logminer will return result in binary form.
4. Redo Logs: They are the desired redo logs that U wants to analyze. They can be online redo logs or archived redo logs.

Tablespace Management

Oracle database is the physical part of Oracle Server. Oracle database mainly consists of different kind of physical files. This physical part has also its logical part. The existence of the logical part of database eases the database administration to a great deal.

A Tablespace in Oracle database is the logical grouping of data files. There can be one or more data files inside a Tablespace but a data file can only be a part of one Tablespace. It is also the top most in units of the logical structure of database. To create a Tablespace login to Oracle with Create Tablespace privilege and issue the following command:

SQL>create tablespace test datafile ‘C:\oracle\product\10.1.0\oradata\orcl\test01.dbf’ size 10M autoextend on extent management local uniform size 1M;

We have used the all too familiar create statement with Tablespace name and the path of datafile. Remember a Tablespace must have at least one datafile. Next we have specified the size of the datafile with the option that this file is allowed to grow automatically when necessary. And finally we specified the type of extent management we want to use for the Tablespace.

Once you have created the Tablespace you can add more files to it with the following command:

SQL>alter tablespace test add datafile ‘C:\oracle\product\10.1.0\oradata\orcl\test02.dbf’ size 5M autoextend on;

If you want to drop a datafile from the Tablespace then you can use the following command:

SQL>alter tablespace test drop datafile ‘C:\oracle\product\10.1.0\oradata\orcl\test02.dbf’;

You can also make the Tablespace off line so that users cannot access the data contained in the Tablespace:

SQL>alter tablespace test offline;

You can change back this to default one i.e. on line with following command:

SQL>alter tablespace test online;

Now if you want to drop a Tablespace then you can use the following command:

SQL>drop tablespace test;

Remember this command will only remove the Tablespace and will not delete any datafiles associated with it. If you want to do that then you must use the following command:

SQL>drop tablespace test including contents and datafiles;

When you create a database using Database Configuration Assistant Oracle creates a number tablespaces. These include System, Sysaux, Temp, Users etc. System Tablespace contains the data dictionary and is therefore the most important one in the database. Database will not start if this Tablespace is missing. Sysaux Tablespace is used for assisting the system Tablespace. Temp is the special Tablespace which contains the temporary data used during the processing. While users Tablespace is the default Tablespace where user’s data resides.

Closing Oracle Instance

Closing Oracle database or Instance for that matter means that the data stored in the database will not be available to the users. It is as simple to shutdown the database as it is to startup. You can use Oracle EM or good old SQL*PLUS to do this. SQL*PLUS is personally my favorite. (It was the first thing I liked about Oracle.) Well, talking about closing the database; the simple command to do so is the Shutdown. There are four different key words that you can use with this command; each has its own effect. These are Normal, Transactional, Immediate and Abort.

When the Shutdown command is used with Normal or no suffix then Oracle will wait for all users to disconnect, save all the unsaved data back to disks (Commit Transactions) and will perform a clean shutdown. The data will be in consistent state on next startup. This type of shutdown is extremely slow because you never know when a user will disconnect from the database. When the Shutdown command is used with Transactional suffix then Oracle will only wait for the current Transactions to complete, save the data to disks and will perform a clean shutdown. Data will be consistent at next startup. This type of shutdown is faster then Normal Shutdown but is still not ideal because you can’t tell for sure when a user will commit or rollback the transaction.

When the Shutdown command is issued with Immediate as suffix then Oracle will immediately close all connections to the database and will shutdown the database. However it will make sure that the data remains in consistent state. Means that it is also a clean shutdown. This is the ideal and most frequently used way to shutdown the database. Shutdown Abort is the brutal force. It will force all users to disconnect from the database loosing all the unsaved changes. You should never use this command to close the database except when it is absolutely necessary. On next startup Instance recovery will be needed to bring back the database in consistent state.

Oracle Backup

Database backup and recovery are among the most important and critical tasks that a DBA must perform. In Oracle you can take the backup of your database in two ways. These two ways are namely as Cold Backup and Hot Backup. Cold Backup is taken when the database is down or closed while Hot Backup is taken when the database is online or open. In order to take the Cold Backup DBA first shutdown the database and make it unavailable to the users and then manually copy the database files, control files, redo log files, network configuration files and parameter files to the database device and finally open the database for normal operations again.

Cold Backup is easy to take but is not ideal for organizations that cannot afford downtime. Most of the organizations today don’t want their database to be down for such a long time especially databases that support online businesses. The prime examples are Google, Yahoo, and Amazon etc. With that in mind Oracle database allows you to take the hot backup. While taking the Hot Backup your database remains open for the user operations. Hot Backup is taken tablespace by tablespace. This means that first you take Backup of one tablespace and then the other. The process continues until all the tablespaces are backed up.

Now suppose you want to take the backup of your “Users” tablespace which is automatically created when Oracle is installed. It is also the default tablespace for all users’ data unless you change it. First you will issue the following command:
SQL>alter tablespace users begin backup;
This command will put the tablespace in backup mode. When a tablespace is in backup mode no changes can be made on the data it contained. However users can still work because their changes are recorded in redo log files and when tablespace gets normal DBWR will apply the changes seamlessly.
The next step is to copy the datafiles belonging to “Users” tablespace. You can issue the following command to do this:
SQL>host copy ‘C:\oracle\product\10.1.0\oradata\orcl\users01.dbf’ ‘F:\backup\users01.dbf’;
This command will copy the datafile to the backup location. “Host” keyword is used to run OS commands from SQL*PLUS. This command will repeat for each datafile in tablespace. Further more you can manually copy and paste the datafile using Explorer instead of issuing this command.
The next command will bring the tablespace backup to normal mode:
SQL>alter tablespace users end backup;
The last command will insure that the database gets into consistent state by forcing a log switch.
SQL>alter system switch logfile;

Normally instead of issuing all these commands manually each time, DBA writes a script consisting of these commands and runs the script. For that purpose open a text editor like Notepad and write all the four commands in it. Be sure to write each command on a separate line and save the file as SQL script file (.sql). To run the script issue the following command:
SQL>@C:\HotBackup.sql

Oracle Instance Startup

Instance and database are normally considered to be the same things and there is no harm in doing so. Although technically both are different but without each other both are meaningless, therefore terms are used interchangeably. Database is just a dump of files that contain the data while instance provides a mean to access that data. Instance consists of some background processes and memory structures which are collectively called SGA or Shared Global Area. The process through which we make the data stored in the database available to users is knows as startup which is actually Instance startup.

There are several stages through which an Instance passes during startup. The reason for the split of Instance startup into stages is to perform certain operations at certain stages. For example most of the administrative tasks are performed in No mount or mount state. When the Instance is in mount or No mount state, it is unavailable to users and only DBA can access it. The initial stage of the Instance is idle. When we give the command startup the Instance first reads the spfile (Server Parameter File) and gets into No mount stage. Server Parameter File is a parameter file that contains the Name/Value pairs of parameters which are necessary for Instance startup. After that Instance reads the control files and gets into the mount state. Finally it reads the data files and gets into open state.

You can also start the Instance step by step. For example if you want to get the instance into mount state then you can issue the command
SQL>startup mount; instead of
SQL>startup
(The command startup will open the database.) After you have mounted your database then you can open it with the following command
SQL>alter database open;
One point worth mentioning here is that you cannot go through the states in reverse order. Means that you cannot mount a database when it is open and hence cannot bring it to No mount state when it is in mount state. If you want to do this then first shutdown the database and then bring it to the desired state.

Oracle Architecture

Oracle Server:

As you can see on top of the hierarchy is Oracle Server. But before going in the details let us start with a very important question. It will seem to you very simple but even many DBAs have no clear answer to this. The question is: what is Oracle. I mean we all know that Oracle is the name of a corporation but what else the word Oracle refers to. Is it the name of a database or it is the name of developer tool or something else? The word Oracle does not refer to a database nor to the developer tool it is something else. And that something else is DBMS. Oracle is the name of the DBMS that controls the Oracle database. And remember that Oracle is not a relational DBMS: it is an Object Relational DataBase Management System (O-RDBMS).

As I told you that Oracle is the name of a DBMS. It is its short term (Oracle). Its full name is Oracle Server. So Oracle Architecture means the Architecture of Oracle Server. Oracle Server has two parts. One is Physical and the second is Logical. Physical Part is the part that resides on the Hard Disc; and Logical Part resides in RAM.

Database (Physical Part):

The physical part of Oracle is database. Database is an informational unit. The two fundamental purposes of database are data storage and retrieval. So database stores the data in such an organized way that it can be efficiently and effectively retrieved. Database resides in the hard disc in the form of different kind of files. There are generally three kinds of files.

Data Files: Those files that contain the actual data. It is particularly user's data. There are different kinds of structures in which data is stored. The most fundamental of which is block.

Redo Log Files: These files contain the log information of the database. They keep track of which data item is stored, retrieved, or modified. They must be kept secure. Oracle does the data recovery through these files. In the case of a system failure if these files are lost than Oracle will not be able to recover the database.

Control Files: Oracle also maintains the information about the database for itself. Oracle stores that information in the control files. They are normally known as the heart of the database. These files keep the information e.g. where the data files and redo log files are actually stored. The exact path of where they are located etc.

Instance (Logical Part):

In very simple words Instance is known as the mean to access the database. Every time you want to get access to the database you must connect through an Instance. Every database has its own Instance which lets its users to communicate with it.

An Oracle Instance consists of two things:

1. Background Processes
   
2. Memory Structures
   

Background Processes: Oracle uses a number of different background processes to perform different kind of activities. The mandatory background processes of Oracle are:

1. Database Writer (DBWR): Suppose you are working on a database. You need to change any part of the table. When you do this you also need to make the change in the actual file for consistency. The process that allows you to write in the database or to change contents of the database is DBWR. Thus it is the only process that can write in database directly.
   
2. Log Writer (LGWR): Its functionality is almost the same of DBWR; the only difference is that it writes or updates the redo log files instead of data files. The updating of redo log files is very important because the whole recovery procedure in case of system failure is dependent on these files.
   
3. Check Point (CKPT): Although the DBWR is the only process that has direct access to the database; but in actual the DBWR is an idle process. It is the CKPT that invokes the DBWR to perform the write operation on the database after a specific time interval known as Check Point.
   
4. System Monitor (SMON): System Monitor as specified by its name it keeps track of the entire system. In case of a system failure it is this process that performs the recovery procedure. It also does the defragmentation of the disc.
   
5. Process Monitor (PMON): In case of abort termination of database the Process Monitor checks which process was using the database. It also performs the resource cleanup; release of resources from the process.
   

Memory Structures: When an Oracle instance is started a set of memory structures are allocated to it. These memory structures are collectively called System Global Area or SGA for short. Every Oracle Instance has its own SGA. The most commonly used memory structures are:

1. Database Buffer Cache: When you retrieve information from a database it is first stored in the database buffer cache. It contains the most recently used data for easy reference if that data is needed again soon. This is a great performance improvement because in the case of a cache hit a lot of time is saved which would have been wasted in retrieving data from disc.

The term buffer is used to represent a minimum storage unit. Normally the size of the buffer is equal to the size of data block on the hard disc. Further more almost all buffer caches mentioned here work on least recently used algorithm. Part of the data that is least used will be written back to disc.

2. Redo Log Buffer Cache: It is said to be the most important component of Oracle Instance. It stores the most current log information of the database. Just like data is modified and stored from database buffer cache; log information is written back from this cache. In case of a system failure data is recovered from this log information. And if you had not written back the log information to disc than data lose through a system crash will be permanent.

3. Shared Pool: Shared Pool stores two important kind of information and therefore has two parts:

1. Data Dictionary Cache: - Data dictionary cache stores the metadata of the database in which you are working. It stores information like the database name, instance name etc.
   
2. Library Cache: - It is used to store information about commands and queries applied by the user. It has two further parts:
   
   • SQL Cache: - As suggested by its name it stores information about most recently used SQL commands.
     
   • PL/SQL Cache: - It stores information about most recently used blocks of code written in PL/SQL.
     

Shared Pool has a great role in the performance of the database. Suppose you have given a select statement to fetch records from a table. What happens here is the Oracle calculates a hash value of every SQL command and stores it in the SQL cache. Next time the command is checked; if its hash value is present in the SQL cache than it will not go under semantics and syntactical analysis again. This saves a lot of CPU time of processing the same command again and again.

4. Large Pool: It is not mandatory part. Rather it is an optional part of Oracle Instance. It is mainly used when the Oracle is running in shared mode. And shared pool is not enough to hold every thing.

5. Java Pool: As suggested by its name it is only used when you are using java with oracle. Thus it is also an optional part.

Autonomous Transactions

Transactions are at the heart of any database management system. Autonomous transaction is a transaction that starts from a parent transaction but proceeds without any regard to the parent transaction. The typical application of autonomous transaction is of logging activity. If you want your logging activity to start on a certain event and then proceed without any regard to the activity for which the logging is taking place then the autonomous transactions are the natural solution. Oracle provides a very simple way to start an autonomous transaction. You can start an autonomous transaction in a PL/SQL block by including the "Pragma Autonomous Transaction" in the declarative section of the block. After the transaction has been started it will operate independently of the parent transaction. It does not rely on the success or failure of PL/SQL block from which it was started.

Talking about transactions; they operate without any user interaction. However Oracle allows you to have some control over their operation. The command "Set Transaction" is used to set five different arguments that will guide the transaction in its operation. The "Read only" argument makes the transaction to work only on a snapshot of data from the database. The "Read Write" argument will bring back the transaction to its default mode. The argument "Read Committed" specifies that if a user is updating a row of table and another user tries to do the same then the second user's transaction will wait for the first one's to complete. This is also the default behavior. The argument "Serializable" is almost the same as "Read Committed" except when set then the transaction of the second user will abort with an error. You can also name the rollback segment that the transaction should use while it proceeds with the argument "Rollback Segment". But remember before you do all this that the Command "Set Transaction" should be the first one in your transaction.

Data Dictionary

Data Dictionary is an important component of any database just like it is of oracle. Data Dictionary holds the meta data. By meta data we mean data about the data. Oracle maintains information about the data it stores in data dictionary. Data dictionary holds information like logical and physical structure of the database, object definitions, Integrity constraints, user privileges, and auditing.

When a database starts two scripts run. First is called sql.bsq and second is known as catalog.sql. When sql.bsq runs it creates the base tables. Base tables are the tables that contain the actual information. All the information stored in data dictionary is in the form of these base tables. When catalog.sql runs it creates views. These views are based on the base tables. Actually a user or even a DBA can’t see the base tables. Whatever information you need to retrieve from the data dictionary is available to you through these views and they are from one of the following categories.

With DBA Prefix: The views that have a prefix of DBA in their names are only visible to a DBA. You can see them when you are login as sysdba. They contain information about the entire database.

With User Prefix: The views with user as their prefix will show the user information about the objects which belong to him.

With All Prefix: The views with all prefix will show the user information about objects that belong to him and also on which he has been granted permission by some other user or DBA.

Let us clarify with an example. There are three views dba_tables, user_tables, and all_tables. The dba_tables view will show you the information about all the tables the database has. The user_tables view will show you the information about the tables which are your self created. The all_tables view will show you the information about the tables which are either your self created or on which you have permissions.

There is another category of data dictionary views Dynamic Performance Views. They contain all the dynamic information about the database which is running at that time. They all have v$ as the prefix with their names. And usually they are known as v$ views. These dynamic views take information from shared global area and from control files. Some examples of these are:

V$session: Will show you the information about current sessions.

V$instance: It will show you the information about current instance.

V$datafile: It will show you the information about currently used data files.