Change sizes of all redo logs using one shell script

I had this rather surprisingly difficult puzzle of changing sizes of all members of both redo log groups this past weekend. And that too using one shell script. There are two challenges here.

First is the fact that there is no resize option available for redo logs and my database had only two redo log groups which is the minimum requirement. Oracle writes on all members of a redo log group simultaneously and once it is full it switches to the next group. In order to change to the size of the members of any redo log group you had to drop that group first and then add again with the desired size. Having only two redo log groups means that you can't drop any of them; Oracle won't let you do that.

The solution to this first problem is to add an interim redo log group, drop the first redo log group and recreate it with desired size, then drop the second redo log group and recreate it and finally drop the 3rd interim redo log group.

Create restore points for specific SCN and one that preserve in 11g

Restore points were introduced in 10g as part of amazing new flashback technology. You can check my earlier post about them here if you want to know more about them first hand.

In 11g there are two minor tweaks in the concept. Normally when you create restore point, it is created on current SCN. Now you can customize this behavior. You can create restore point to some earlier SCN. The only rule is here that SCN should exist in first place.

For example, issue the following command to know the current SCN of database.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    1017119

Suppose we want to create restore point on 1017000 instead of current one. Following command will do that.

SQL> create restore point restore1 as of scn 1017000;

Restore point created.

You can also use time-stamp instead of SCN.

SQL> create restore point restore2 as of timestamp to_date('10-Feb-2013');

Restore point created.

The second concept is of perseverance. Oracle has a retention policy for keeping restore points and once that retention point is reached, the restore points created earlier are deleted in order of their creation. Now you can control that as well. Suppose you want to create a restore point and also want it to be preserved until and unless you drop it yourself, you can use the following command.

SQL> create restore point restore3 preserve;

Restore point created.

You can check which restore points are preserved and which are not by using the following query.

SQL> select name,preserved from v$restore_point;

NAME                           PRE

------------------------------ ---

RESTORE3                       YES

RESTORE1                       NO

RESTORE2                       NO

 

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.

Ora-Dim Utility

Oradim is an Oracle utility that is used to manage Oracle instance. It can be used to create, edit and delete an instance. Its typical use is when you had to manually create the instance for any reason. Among the reasons is when instance gets corrupted or when you are recovering your database after a media failure. For example if you want to create an instance then open the command prompt and issue the following command.

C:\oradim –new –sid crimson –syspwd oracle

The above command will create an instance named as crimson and the password for its sys account will be oracle. Now if you want to delete an instance then issue the following command.

C:\oradim –delete –sid crimson

This command will delete the instance. I was quite used to with these commands but here is something new that I discovered a few days earlier. Oradim can be used not only to start Oracle instance but also to start the database service at operating system well. The problem that I was facing was that I had Oracle 10g installed at home for playing. Normally I turned its services off and whenever required I turn them on. Every now and then I had to go to MMC and do the specific job. That is quite hectic, isn't it? So I searched and then devised a script that will do all the stuff for me with just a single click. It looks like this:

rem Start Listener

lsnrctl start

rem Start Oracle Database

oradim -startup -sid orcl -syspwd oracle -starttype srvc, inst

rem Start EM

set ORACLE_SID=ORCL

emctl start dbconsole

This script is for Windows. It first starts the Listener and then uses Oradim to start the database service and database itself. The parameter –starttype will define what to start. In this case both will get started. (srvc=service, inst=Instance)

Now if want to shutdown all these things then here it is:

rem Stop Oracle Database

oradim -shutdown -sid orcl -syspwd oracle -shuttype srvc, inst -shutmode i

rem Stop Listener

lsnrctl stop

rem Stop EM

set ORACLE_SID=ORCL

emctl stop dbconsole

For further options just type Oradim in command prompt provided that you have Oracle installed.

Problems with Large Database

Data growth is one of the problems that the DBA of today’s age faces. As the world moves toward the information age the amount of data is increasing significantly everyday. With massive amount of data the problems that pop up are space issues, performance issues and management issues. Not only data is increasing but it is also expanding its dimensions because people tend to know more and more now a days. Databases are designed for one solo purpose and that is data storage and manipulation. So no matter what, data is increasing or not the DBA had to tackle it somehow.

I will not talk much about the problems that the DBA faces with large databases because I have no practical experience of that. However I have the experience of working as a developer on large tables. Well the bottom line is experience counts and it counts more then anything. You have read all the stuff related to SQL and PL/SQL and you think that you can merely do anything with it. Most probably you are thinking wrong because knowing and working with tools available are both different things. I am not saying that both SQL and PL/SQL are something really difficult but when you are using them against large databases, problems will occur and you will be wondering that am I doing something wrong?

I am quoting an example of my own here to explain what I am trying to say. A few days back I got an ad-hoc query to answer. The query was to find all those Mobile numbers which are in table “Test1” suppose and are not in “Test2”. I wrote the following query for that:

SQL>select a.mobileno from test1 a where a.mobileno not in (

2 select b.mobileno from test2);

There is nothing wrong with the logic or query. The only thing wrong is that “Not In” operator is too slow to be used in the above query because I have 44 million records in table “Test1” and 10 million records in “Test2”. This query ran well over 10 hours without results. How can I do this? This question made me almost punching my head down. One of my senior colleagues from whom I have learnt a lot of SQL tricks told me the master blaster query which was as under.

SQL>select a.mobileno from test1 a, test2 b

2 where a.mobileno=b.mobileno (+) and b.mobileno is null;

I copied it pasted it and in under 5 minutes or so I got the required results. Now I use this query in so many ways to enhance performance in my applications and I rarely and I mean rarely use the “Not In” operator. The point I want to make is that you will not find this in any documentation or book because they are not meant for that. You can only learn this by experience.

Oracle 11g

Oracle database 11g! For me it is still hype. Not because I have not liked it but because I does not know what is so special about it. I heard about its numerous features but do not know how they can be useful. Oh, then I am talking about Oracle database who already had many, many salient features even before 11g. And I don’t know them all. It has been quite some time that Oracle database 11g has been released and I feel myself somewhat incompetent to know that I have not gone through it yet. But upon further stress I can figure out two problems relating that. First is because of less time I have due to my job and the second is I can’t find a book about it here in Pakistan. It’s hard to find any good book here and especially oracle related.

Well I have installed oracle many and I mean it many times. So as for as installation goes I don’t need anything no matter what version it is. And with the introduction of Basic Installation method since 10g life is even easier. But like most installations the first attempt was unsuccessful. Every thing went well except EM which refused to configure; pointing out some listener specific error. Well quite frankly I was unable to figure out what the problem was because I tried every possible thing that I know to configure my database with EM but to no avail. Lastly I thought that as 10g was not uninstalled properly it may be because of that. So I cleaned my Computer from everything containing the word Oracle; even the poor JIniator got hammered out. I made a fresh install and everything went good enough and installation was successful. I took a deep breathe and cajoled myself that now I will start exploring Oracle 11g because I got it installed and running.