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. 

Lib Couldn't be found error while installing FOD Schema

One of my future goals include to learn JDeveloper and ADF. So I started with Oracle by Example series (I think the best thing to learn a tool on OTN documentation). In article that I started out there was a requirement to install FOD schema. I checked and to my disappointment was unable to find this in default installation 11g. So I had to install it. I followed the link provided in the article and to my surprise it was not a simple SQL script that I required to run rather was an application that I had to run by first opening it in JDeveloper.

So I opened it up and did the necessary steps mentioned and than ran the project. The output was not good at all. It gave me the error that path to lib folder is missing. It was pointing to the root folder where I unzipped the application despite the fact that I gave the correct JDeveloper home path where necessary. I cannot believe it took three hours to fix this problem. And to my anger the error was the same that I had experienced a couple of days ago. Use of \ instead of / while giving folder paths to apache. So please use / instead of \ while giving paths in Oracle configuration (No, I know you are using Windows). 

Oracle Database Firewall

Oracle has recently released a new product named Oracle Database Firewall. Essentially the concept is same and that is to monitor and possibly block the unwanted connections to database (rather than OS here). However it does not block connections because it is not on network level but what it does is block invalid SQL statements. Now what is the definition of invalid SQL considering that any syntactical errors are detected by DBMS already.


Here invalid SQL does not mean the SQL that produces wrong results but here it means an SQL that is suspected to be one created from SQL injection techniques. SQL injections are pretty common and in most cases if not all they are unavoidable. There are techniques to prevent code from inadvertently executing injected statements but someway or the other they all fail to protect the data fully. But now you have database firewall. And the best thing about it is that it only works with Oracle Database but also on SQL Server, Sybase, IBM's DB2 etc. 


For more information you can see this podcast from Director Security at Oracle Corp.

Apache path error on Windows

I can almost remember this correctly. I have made this mistake three times. The mistake is to use \ instead of / while setting paths for apache on Windows. Whether it was setting report path parameter in configuration of Oracle AS services, or setting images path while installing APEX I have made this mistake again and again, only to waste a couple of hours of my life. The exception can be in case of report configuration because it was then when I made this mistake for the first time. The reason it happens to me only on Windows is because I copy paste the addresses from path bar of Windows explorer and we all know that Windows uses \ instead of / in paths but apache does not.


So be very very very careful (can't stress anymore) every time you configure something in apache which is installed on Windows. Even if it is necessary to copy paste address from address bar make sure to remove \ and replace it with /. The error it caused usually states that required directory or file cannot be found. So every time you see an error like this don't stress too much and do this change first. :)

Firefox out Chrome in

I used and I loved Firefox for almost six years. Much of the time I did not even considered anything else but then Chrome came in. The first thing that got my attention was that it is from Google but then I was unable to leave my Firefox. I continued to use Firefox despite the fact that the Chrome was just too much appealing.

It ended last week and finally I have switched to Chrome. It's lighter, more attractive and even now it looks like that people behind Firefox has admitted the fact that Yes Chrome is better. If you are wondering what I am trying to say then just download the beta version of Firefox 4. You will be surprised if you are a fan of Firefox that actually they have copied the interface of Chrome. Same goes for IE 9 beta. I think I have found a new love.

This pretty much makes this prediction of mine true!!!