The Purposes and Working of Undo Segments in Oracle database

A couple of days back a friend of mine (happens to be a DBA) called me and asked a question which I was unable to answer at that time but figured out later that I actually know the answer and how come I forgot. Here is the question:

"When a transaction starts does an undo segment is assigned to it?"

To make myself sure I started to look into my blog. I found a post named "Rollback Segments" which I wrote back in 2007. Here is the rewrite of that post because apparently rollback segments don't exist anymore. Oracle has renamed rollback tablespace and segments with undo tablespace and segments. At that point both were used but now there is no such thing as rollback. There are numerous advantages of undo tablespace over rollback tablespace, one of them is automation. As for as the question goes the answer is no. I found the answer in my old blog and have underlined the lines below which gave me the answer.

Purposes:

The primary purpose of undo 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 undo segment. So whenever that old value is needed again it is restored from undo segment. There are three main purposes of storing old values and thus of undo 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 undo segment because it’s the undo 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 the old value from undo segment.
3. Read Consistency: Read consistency is 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 undo 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 issued 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. Both queries will get a consistent view of data with respect to time they arrived at database engine; this is Read Consistency.

Working:

Undo 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.

Intersession Communication and how Oracle deals with it

Communication is something that we all need no matter what we do and where we are. It can solve problems which in other ways would have been very difficult or costly. We do need to communicate where ever we need to work with each other, same goes for database users. A typical form of communication while working on database and the one we all do one way or the other is sharing of data. 

Suppose you have a large table that consist of hundreds of thousands of rows in one schema. There may be a time when that same piece of information is required by another database schema. Without the availability of proper communication channel between these two schema we will had to export data from first schema and import into the second one. This is time consuming, requires more effort and resources and also negates one of the primary advantage of using database i.e. no data duplication. But of course we don't do things this way in real life. Two simple solutions are to grant second schema access to the specific table in first schema or if that is not feasible then create a synonym over the table and grant access to that synonym to the second user. This is Intersession communication and even more...

Intersession communication means two user sessions communicating with each other. The user sessions can belong to the single user or they can be of two different users. Oracle Database 10g provides two methods for doing this. First one is that in which there is a requirement of permanent or semi-permanent database structures for example tables, synonyms etc (mentioned above). This type of communication is feasible if you want your data to be shared with other users but is not a good practice for message communication. Obviously nobody will like to create database structures just for the sake of communication with the other user. Secondly this type of communication is limited by Transaction Control. Transaction Control means that a user must complete and commit the changes to the database for other users to see.

The second method is through two built in PL/SQL packages namely DBMS_PIPE and DBMS_ALERT. DBMS_PIPE uses memory structures called pipes to send and receive messages from one user session to the other. The pipes are pretty much similar to UNIX pipes. The advantage is there is no requirement of permanent or semi-permanent database structures. The communication takes place in memory. And also this type of communication is not limited by Transaction Control limitations. DBSM_ALERT works pretty much similar and implements the publish and subscribe paradigm. An event is published and users that subscribed for the event gets notified about the event. The structure used by this package is not formally referred to as pipe but works like public pipes. 

Also posted on Tech Blessing.

Difference between Transactions, Save Ponits and Restore Points in Oracle

Copy blogging is one hell of a great way to get you started and motivated towards your blogging experience. Sometimes its the only way. I regularly read perhaps the best blog about blogging to kept myself going. But to use Copy Blogging well is also a technique that requires time to master and there are some rules that you need to follow even in copying and guys at Copy Blogger are very good at teaching you those rules. Today was yet another day when I used one of those techniques to blog. Its called re-blogging your own blog. Re-writing your old posts gives you new insights to an old idea and also enables you to do it better. Scanning through my blog I realized that there are a lot of topics that are required to be changed because in many cases technology has changed a lot since that original writing. So here is first one... 

 

Transaction mechanism is one of the most important aspects of any database and likewise every database implements it. Oracle introduced transaction management way back in 1984 in their Release 3 of Oracle RDBMS. Transaction is an atomic block of SQL statements that can be executed against a database. Committing a transaction means that the changes made by the transaction should be made permanent. And likewise rollback means that the changes made by transaction must not be applied to the database. Transaction mechanism works logically in the memory and not on the physical hard disc. It means that once you have committed a transaction or have rolled back then you can’t do the other. Transactions introduced the concept of atomicity while executing multiple statements at the same time and also gave much more control of what can be done.

Almost same is the concept of Save Points. Save Point allow you to specify specific points within a transaction so that you can perform specific operations on specific points within a transaction. So save points introduced further granular control inside a transaction.

While transaction and save points are mainly concerned with developer, the concept of Restore Points normally helps a DBA. Restore points allow you to bring your database back to a previous state. That tells us one thing and that is the Restore Points apply to database (data stored on hard disk) and not to memory (or in other words instance). Consider the following situation: 

Suppose you are working on your database and did some updates/deletes in one of your tables and also committed it or you accidentally deleted a table or any other database object that was critical for system or business . Now you want to undo all that crap. Of course you got a very good list of tools to help you recover from this situation. If you go for recovery options than you required to have a backup and if you use Flashback Query that would require quite a lot of time if number of affected rows were in hundreds or thousands. On the other hand if you would have created Restore Point before that dangerous update/delete, life would have been much easier. You can simply restore database back to before update/delete operation.

Creating restore point is simple and you can issue the following command to create one before every critical update/delete:

SQL>Create restore point before_update;

Name can be anything of your choice but should be a descriptive one. Now if something goes wrong with database even if database stops responding than simply shutdown it, bring it to mount state and issue the following command:

SQL>flashback database to restore point before_update;

P.S. Restore point is a feature available in Oracle Database 10g R2 or later and also requires that you enable flashback.

Also posted on Tech Blessing. 

Migrating from Oracle iAS to Weblogic Server, Webinar turned into disappointment

A few days back I got the invitation from Oracle to attend an online webinar of how to migrate from Oracle Application Server to Oracle Weblogic Server. I saw this as a pretty decent opportunity to learn something about Weblogic. My expectation was to have detailed understanding of how these two products differ from each other and in the process get my hands on Weblogic which I haven't been able to do so. I have a good architectural and practical knowledge of Oracle iAs 10g and its easy to understand a product if you know how its counterpart product works so I was more excited towards this than usual. But...

The webinar started with the usual marketing crap and a couple of slides were wasted to that and after that I was told that yes you are here to know about migration considerations from Oracle iAS 10g to Oracle Weblogic. The next three to four slides were as I expected. The overview of basic difference between the two products and how it was such a genious move on Oracle's part to acquire BEA's weblogic. You get easily confuse of what they were trying to say. They were praising their acquisition at the same time they were also saying how good a product Oracle iAS 10g was. Why there was a need to buy Weblogic when your existing product was beating everybody else to the punch. None the less I tried to stay focus and wait for the actual stuff. Like I said I was expecting all this. What came after this was shocking.

The migration process suddenly halted and they began to explain how good is Weblogic. The next 40 minutes were dedicated to Weblogic Server's praise and how it is beating everyone else in the market especially IBM's websphere. Next they moved onto Weblogic 12C. I had no idea before this webinar that it is a must have for everyone and how it is tightly integrated to Oracle EM 12C (Oh, if you don't have EM 12C - for your information it is a must have also). In short they were the 60 minutes of my life that I can't get back.

Weblogic is one hell of a product and we all know since the days when Oracle's own iAS was a competitor of it. I used to wish to use Weblogic with Oracle Database as easily as Oracle iAS. Because quite frankly Oracle iAs was one crappy product. Its hard for me to say it like that because I had so many sleepless nights understanding this huge product. Weblogic is small and simple but still does it all what iAS used to do (except to run Froms and Reports Server which are slowly getting obsolete) and this is where its actual beauty lye's. But I don't know this gem as well as I should have; may be I had to have some more sleepless nights for Weblogic as well. But I am sure in those nights I won't be listening to another Webinar.

Oracle Restart: A cool new feature in Oracle 11g R2

One of my few complaints about Oracle on Linux was used to be that database does not gets started automatically with the operating system. Although Oracle did provided scripts to automate this process and I also got successful a couple of times in making them work properly but it still remained a constant source of pain for me. I never felt comfortable with the task and most of the time one thing or the other malfunctioned. Every time it used to took too much of time and the end result was simply not worth it. So I adopted a more liked approach of most DBAs and that is manually starting database once system gets up and running.

Its a long debate that whether 11g R2 is a major release or a minor one but I do tend to agree that yes it has a very long list of new features or changes to existing features. One of the new features is Oracle Restart. Before I go into the details let me say this that Oracle Restart and Oracle ASM are not part of Oracle Database software (ASM was part of database software until 11g R1 and Restart is new to 11g R2). Both of these are part of Oracle Grid software. Yes, you are right if you are wondering that you had to install Grid software if you want to use ASM. Right or wrong its a topic for some other day.

So what is Oracle Restart? Simply put its a utility that lets you start/stop database, ASM instances as well as EM, grid control and listener. All you have to do is to make every component of your Oracle infrastructure known to this utility and it will manage the start and stop operations without any hassle (of course you had to edit oratab file as usual but that's the easy part). If you have installed grid software first for ASM and everything went OK. By OK I mean everything was configured by Oracle's standard tools (like netca for Listener and dbca for database etc) then all components will get configured with Oracle Restart as well and you don't have to do anything. But in case you configured something manually or installed and created database before grid software than Oracle Restart will not configure existing database automatically. You will have to add database to Oracle Restart and it will manage the rest. Adding a database is simple and the following command can be used to configure Oracle Database named rite with Oracle Restart.

$ srvctl add database -d rite

If you are an old DBA and know Oracle from its version 8i than the srvctl is not knew for you. You can also use this utility to manually start and stop database/ASM instances as well if there is a need much like you do using SQL PLUS. However there is advantage of using srvctl over SQL PLUS. If you use srvctl utility to start the database than it will also start all dependent components before starting database e.g. Listener and ASM. On the other hand SQL PLUS will just give an error message and will not start components on which database is dependent to run. For full list of srvctl commnads and options type the following on Shell prompt.

srvctl help  

Cross posted on Tech Blessing.

PL/SQL - More useful than its perception

Although my general perception about the development tools of Oracle has never been quite good but I have always admired and loved PL/SQL; especially the PL part of it. Oracle has done quite a few changes in normal syntax of SQL as well and I think they have only enhanced the normal standard ANSI SQL which is the standard and is independent of any DBMS. Many believe that Analytical Functions introduced by Oracle are the best things that has happened to SQL after SELECT statement. Coming back to PL/SQL...

The greatest thing about PL/SQL has been over the years is that it is tightly integrated to a RDBMS that holds more than 40% of market share of databases. You can query something and immediately start using the programming constructs with absolutely no extra effort. All you have to do is to enclose your query within a Begin and an End. You don't need to do import any classes or packages; there are no setup costs or efforts. Although the part of it has to do with Oracle including its compiler into the RDBMS but you simply can't resist the simplicity and usefulness of this language. Every Oracle application developer will happily tell you how good it is and how difficult his/her life would have been without it.

When Oracle started to push Java more and more - due to whatever reasons - many thought that end is near for PL/SQL. With the recent acquisition of Sun Micro Systems by Oracle and thus Java as well, this rumor started to heat up again. But somehow it has successfully passed the test of time ever since it came into being back in 1988 with Oracle Version 6. Although its name still says that it is a procedural language but it has everything that an object oriented language should have. Even every SOA application has some kind of dependency on it because it manipulates and programs the single most important entity of an organization i.e. Data with ease that is unmatched.

When Oracle introduced HTML DB now known as APEX I was more than sure that Oracle has no plans of deprecating PL/SQL. APEX is another very handy tool that uses just PL/SQL and nothing else. If you want to quickly build Oracle database backed web applications APEX is your dream tool and much like PL/SQL it is free as well. You can literally build entire websites just using PL/SQL and this is just another huge plus point for a language that is mostly considered an underdog.

Recovering lost home directory in Linux

Not to use root user to log into Linux system is one of the first things that is told when you step foot in Linux administration world. Although in real world it is still used but it is discouraged and for good reasons as well. Many Linux distributions have alternate ways which can be used to simulate root user privileges for example one of them is sudo command. I still use root especially on VM machines which I have kept for practice reasons and felt the curse of it a few days back when I accidentally deleted the home directory of Oracle user. If you ever had an experience of running Oracle on Linux you know that this user is mandatory to successfully install and maintain the Oracle Software.

More importantly I was logged into Oracle user desktop at that time and switched to root at command prompt. Although the home directory was deleted for the current logged in user but Linux being Linux did not even bothered to notify me. But when I tried to log into Linux again the day after with Oracle user I was unable to see the desktop and I was told that home directory is missing and you won't be able to use GUI or even normal command prompt. I was presented with a drilled down version of Shell terminal which was less than useful for me. So I went to Google and found after some Googling that I need to perform the below mentioned steps to restore my home directory:

1. Log in using root (Yeah, again)  and create the home directory manually in /home folder (You can change the location if you want to).

2. Copy the contents of /etc/skel directory to the newly created home directory.

3. Change the ownership from root to the user for which home directory is being created (In my case oracle).

4. Set the appropriate permissions on directory.

Now you can log off that freak root and can log in using the normal user again. Technically below mentioned commands are required to be executed to recreate the lost home directory of e.g. oracle user after you get logged in using root.

mkdir -p /home/oracle

cp /etc/skel/.* /etc/skel/* /home/oracle

chown -R oracle:oinstall /home/oracle

chmod -R 775 /home/oracle

P.S. If you had your personal data stored in your home directory when you lost it, the data will be lost and you will have to restore it using your backup. The /etc/skel only contains files required to get any new user be able to log in unless it is changes manually by System Admins.

Cross Posted on Tech Blessing. 

Blog, blog and blog!!!

I started this blog with a lot of passion. I was a student at that time and mindset was completely different than I have today. In course of three years I realized that even passion has its own limits and corporate world is much different than what we expect. Here is a bit techy example to explain my journey. :) I learned and believed that Oracle RDBMS is an almost perfect software that is ever written. Even the masterpieces like Linux Kernel suffer from dead locks but Oracle RDBMS does not. To this point I still believe in this theory but what I learned after when I came in real world business was even more surprising. Oracle may be the perfect software but that does not mean it won't have any problems. After all there are situations in real world where Oracle RDBMS is dependent on Softwares like Windows operating system.

When I started blogging my world was hardly anything more than Oracle and Blogger. So the hurdles were minimum. As time went along and money became a more prominent factor in dictating the road that I need to follow, things changed. When I learned the hard truth that the Golden era of web when every blog and website can make money was over; this passion of mine suffered more because I required to look after other things more than blogging because from there my livelihood was coming.

Although I never quite blogging permanently but the thing that suffered the most was this blog of my own where I started all. My blogging range expanded from Oracle to include Cricket, general tech, Smart Phones, tablets, Social Media and of course I also blogged a lot about my new found love i.e. iPhone; but that was on some of my close friend's websites. 

This is my third coming back to myself and I hope that I can keep up with it. But the plan is more realistic this time and the goal is simple for now i.e. blog two to three times per week and mostly on Weekends.