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.