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.

Modes Of Oracle Server

Oracle runs in two modes. One is Dedicated mode and the other is Shared mode. Before differentiating the two let us take a look at what happens when a user or client connects to Oracle Database.

When a user connects to Oracle using a client tool like SQL*PLUS two things happen: first user is connected to listener and second a user process is created for the user. The Listener connects the user to the Instance part of the Oracle Server. And as you know the Instance is mean to access the database. So Instance connects the user to the database. After the connection is established a Server process and Process Global Area (PGA) is created for the user. Now there is no need to be connected to the listener. All the requests of the user will now be carried out through the user process. The user process will put them in the PGA. The Server process will take the requests from PGA and will carry them out for the user. After the server process had got the data from the database which was requested by the user; it will put the data into the PGA. The user process will now take the data from the PGA and will hand it over to the user or client. Again remember that not even the server process is directly connected to the database. It is using the Instance (Background Processes + Shared Global Area) as a mean to access the database.

The default mode of Oracle server- is dedicated mode. In dedicated mode there is one user process, one server process and one PGA for each user. And the working described above takes place for each individual user. But when there are hundreds and even thousands of users connected to the database the dedicated mode is not appropriate. You must turn the server into shared mode. When Oracle is running in shared mode like in parallel server there is only one user process that will take requests from all the users connected to the database. There will be very few server processes (if not one than normally far less than the number of users) communicating with the database on behalf of the users. In shared mode there is only one PGA. The PGA here does not contain any requests or data; it consists of small UGAs (User Global Area) one for each user. The actual data and requests are kept inside individual UGAs.

Cursor Defined

When a SQL statement is issued against Oracle RDBMS then Oracle checks whether it is a valid SQL statement and also whether the user who has issued it has the appropriate permissions. If these two conditions are satisfied then a private SQL area is assigned to the statement. If this is the first time this statement is issued then it is parsed and its parsed copy is saved in the Shared SQL area for further use. Next time when this statement is issued then it is not parsed again but instead already parsed version residing in the Shared SQL area is used.

Both the Private and Shared SQL areas are part of Library Cache which in turn is part of Shared Pool. Private SQL area further consists of two more areas. One is called Persistent area which has static size and stores all the constants values used in the statement. The other is called runtime area and it is used to store all dynamic contents of the statement; for example Binding information. The cursor is nothing but a symbolic name or handle of the Private SQL area of a statement.

Oracle SQL Developer

More then a year ago Sir Fahd gave me an Oracle product as a New Year gift. The product was named as Oracle Raptor. The product was for developing SQL in an easy way then it is possible using SQL*PLUS. I did not use it for two reasons. First it is always hard for me to use something in place of SQL*PLUS and second was the presence of Oracle EM which can aid you to a great deal if you want your development to be fast. One more reason was the time it takes in starting.

The latest version of this product is released in this year’s February. I downloaded it just for fun but for my surprise it takes much less time to s art then its earlier version. Also it has some more and exciting new features. One of them of my great interest is its support for other databases then Oracle. For the time being it supports Microsoft SQL Server, Microsoft Access, MySQL and of course Oracle itself. In my office there is no Oracle type thing so I am trying to connect it to SQL Server. So far I am not been able to do it but still trying. Hoping some favor from it. All in all some appreciation has started to arise in my mind for it. Let us see to how far?

PL/SQL Engine

From its birth SQL lacks the procedural processing like other languages have. And the need was felt that if some way these capabilities can be added to SQL then it will be more useful then it is without them. PL/SQL is nothing but Oracle’s answer for that. If you belong to Oracle world then the learning of this language can benefit you more then you can think. Because it is used in all the development tools of Oracle and also you can use it against the Oracle Server as well. Well benefits aside I am going to talk about the PL/SQL engine and how it works.

PL/SQL is present there where PL/SQL is. Suppose you are working on Forms Developer product of Oracle then let us see how a PL/SQL procedure is compiled. The PL/SQL engine breaks the procedure in three different types of blocks of statements. All the statements that consist of procedural constructs like IF, For Loop etc are processed by Procedural Statement Executor in the Forms Developer itself. All the SQL statements are sent to the Database’s SQL Statement Executor. And all the PL/SQL statements stored in the database are sent to process in the Oracle Server’s Procedural Statement Executor. The net result is combined and is sent back to the user or caller function.

Microsoft’s .Net

Since the beginning of this year I am closely attached to .Net platform. I feel that I will be selfish if I do not talk about this revolutionary application development IDE. Well I am not a very good .Net developer and neither I can be in this short amount of time. I am just going to discuss what I feel about it in my little experience. Well in very simple words I would like to say that I like it. And I really appreciate it and love developing in it. All the promising things that you find in Microsoft documentation like ease of development, CLR features and language independence are there.
I have been mostly tightened to VB.Net. The language is great in itself especially with the introduction of Object Oriented Programming. Whether it is pure object oriented or not is an issue that is very daunting in itself but it really has some very sound OOPs implementations which I think are a lot better then its counter part Java or at least they are really simple to use as compared to Java. Another significant enhancement from previous version of VB is the introduction of proper Exception Handling methods. The old “On error resume next” was simply pathetic but that at least made Windows 98 to run despite everything that happen with it internally.
If you are a .Net developer then I would like you to share your thoughts and correct me.