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.