A fantastically well attended SIG, the large room is packed and it’s at my favourite venue used by UKOUG, just a hop from the houses of parliament. In fact there were 125 registered attendees, which is roughly double the number of the last UNIX SIG event, held in the midlands. However, I would say [...]

I spent a little time last night perusing Stuart Frost’s blog (CEO, DATAllegro) and learned something new. Microsoft, it appears, has ported Windows and SQL Server to platforms beyond x86, x86_64 and IA64. I quote: Database vendors such as Oracle and Microsoft have to build their software to run on any hardware. Hence there are a [...]

Aman made me aware about not being able to post any comments on my blog. I checked and found that everything was fine with Firefox but in IE 6 it was not possible to post a comment. I was using Did You Pass Math plugin to stop comment spam and it had some problems in IE. I disabled it for the time being and installed WP-SpamFree. Everything seems to be fine except that it doesn’t allow very small comments and i haven’t been able to figure out where that setting is ? (yea…i am really poor with this web stuff ).

The display of single post in IE is still broken. Some issues with the theme i am using, i guess. Will try to fix that.

If you have any difficulty in reading or commenting on the post, please let me know at amardeepsidhu at gmail dot com.

Thanks

I’ve just been reading Log Buffer 103 from the Pythian Website, and noticed that the All India Oracle User Group is holding their first ever technical event on Friday 18th July in Hyderabad. Since I won’t be able to attend, I thought I’d offer my support by advertising it here. [...]

I noticed that when I wanted to do some tests with the 11g Native Database Web Service (again), NDWS for short, that I had to gather the information from all over the place on my site. Which was not so very handy. So here a short intro how to set it up, configure and deploy [...]

I have been too busy since getting back from vacation, thus no posts for a while. But I hope the waiting was worthwhile as I present you LatchProf, a tool for digging in to latch contention problems - using plain SQL and sqlplus! As, I’m still busy, I make it short. LatchProf is a script similar to [...]

I’ve recently been discussing how empty index blocks or those blocks that contain nothing but deleted index entries are placed on the index freelist and can potentially be recycled during subsequent index block split operations. A point that’s not so well known about such empty index blocks is how Oracle considers them when calculating index related statistics and the [...]

I read Jake’s post this morning and it hit a raw nerve because I’m a bit slack on the Calendar front. I use the calendar on my phone, but don’t sync it to anything, so when I “misplaced” my phone yesterday morning I had lost everything… Fortunately I found my phone last night, but it made me realize I’m one incident away from being screwed.

As a result I decided to centralize my calendar on Google Calendar and use GooSync to synchronize my online and phone calendars.

Now as long as I don’t lose my phone on the same day Google go out of business, I’m probably safe…

Cheers

Tim…

I've mentioned already that, for the second time in ten years, I'm starting a business. It's a lot easier nowadays than it was back in 1999. I know; it's supposed to be easier the second time you do something, but what I mean is different from that. It's just a lot easier to start a business now than it used to be.

Take email for example. I remember the trauma of having to buy and build a server, install Linux on it, find a location for it, install Sendmail, figure out how to manage that, eventually hire someone to manage it, buy email client software for everyone (in our case, Microsoft Outlook), eventually decide that we wanted to use Microsoft Exchange instead of Sendmail, and then keep on top of hardware and software maintenance for everything we had bought, all in an environment where prices and technology and requirements were continuously variable. It took nearly a whole full-time person just to figure out which options we should be thinking about.

Jeff Holt did most of this work for us in my first start-up almost ten years ago. Now, when you think of how many people in the world there are who can set up email, and compare that to how few people in the world there are who can do what Jeff can do with an Oracle database, you realize that the opportunity cost of having Jeff fiddle with email is ludicrously high. But in 1999, the only other option I knew about was to spend a bunch of cash to hire a separate person to do it instead of Jeff.

Today, you pay $50 to Google for a whole year's worth of Gmail service for each employee you have, and that's it. Ok, there's a half hour or so of configuration work you have to do to get your own domain name in your email addresses. But for way less than one month's rent, you've got email for your company for a whole year that works every time, all the time, from anywhere. All you need is a browser to access it, and even that is free these days.

I can tell you the same kind of story for web hosting, bug tracking, backup and recovery, HR and payroll, accounting, even for sales. The common thread here is that there are a lot of things you have to do as a business that have nothing whatsoever to do with what your business really does, which is that content that your people are really passionate about providing to the market. Today, it's economically efficient to let specialty firms do things for you that ten years ago, you wouldn't have considered letting someone else do.

...Which brings me to what we do. My company, Method R Corporation, does performance for a living. Specifically, Oracle software performance. We know how to make just about any Oracle-based software go faster, and we can do it quicker than you probably think. And we can teach people how to do it just like we do. We even sell the tools we use, which make it a lot easier to do what we do. It works. Read the testimonials at our Profiler page for some evidence of what I mean.

So here's a really important question for our company: Why would a telco or a manufacturer or a transportation company or a financial services company—or even a computer software manufacturer—want to learn as much about Oracle performance as the people in Method R have invested into learning? The answer is that a lot of companies just don't.

I love the field of software performance. I love it; it's my life's work. But most people don't. There are a lot of business owners and even software developers out there who just don't love thinking about software performance. I get that. Hey, I happen not to love thinking about software security. I know it's necessary, and I want it; I just don't want to have to think about it. I think most people regard software performance the same way: want it, need it even, don't want to think about it.

What if software performance were something, like Gmail, that just worked, and the only time you had to think about it was when you wrote a little check to make sure you could continue not having to think about it? I think there's a real business model there.

So here's what we're doing.

The people here at Method R have created a software package that we call our SLA Manager. "SLA" stands for "Service Level Agreement." It is software that tracks the response times (the durations that your end-users care about) of the business tasks that you mark as the most important things you want to watch. For example, if your application's "Book Order" function is something that's important to you, we can measure all 10,436 of your "Book Order" button clicks that happened yesterday. Our SLA Manager could tell you how long every single one of those clicks took. We can report information like, "Only 92.7% of those clicks were fulfilled in 3 seconds or less (not 99% like you wanted)." Of course, we can see trends in the data (that is, we can see your performance problems before your users can), and so on.

So, our value proposition is this: We'll install some data collection software at your site. We'll instrument some of the business tasks that you want to make sure never have performance problems. We'll show you exactly what we're doing so there's no need to fear whether we're messing anything up for you. For example, we'll show you how to turn all our stuff off with the flick of a switch in case you ever get into a debate with one of your software vendors over the impact our measurements might have upon your system.

We'll periodically transfer data from your site to ours, where we'll look at your performance data. We'll charge a small fee for that. The people looking at your data will be Cary Millsap, Jeff Holt, Karen Morton, ...people like that.

Remember: we're not looking at your actual transactions; all we're going to see is how many you do and how long they take.

We'll report regularly to you on what we see, and we'll make recommendations when we see opportunities for improvement. How much or how little help you want will be your decision. If you ever do want us to help you fix a performance problem with one of the tasks that we've helped you instrument, we'll be able to provide quick answers because we have the tools that work with the instrumentation we installed.

Another part of our service will be regularly scheduled knowledge transfer sessions, where the same people I've mentioned already will be available to you. Whether the events are public or private, remote or on-site, ...that will depend on the level of service you want to purchase. We'll tailor these sessions to your needs. We'll be in tune with those needs because of the data we'll be collecting.

If this business model sounds attractive to you, then I hope you'll drop us a note at info at method-r dot com. If it doesn't sound attractive, then we're eager to know how we could make the idea more appealing.

We have tried to consolidate list of Frequently Asked Questions (FAQ) on ASM. Please feel free to ask any other question or contribute to this FAQ by using comment section

Frequently Asked Question on ASM

1) What is ASM?

Automatic storage Management (ASM) is a new type of filesystem which was introduced with Oracle 10g. ASM is recommended filesystem for RAC and Single instance ASM for storing database files. This provides direct I/O to the file and performance is comparable with that provided by RAW Devices. Oracle creates a separate instance for this purpose.

2) How do we identify if we are connected to Normal Instance or ASM instance?

Issue following command to identify this

SQL> show parameter instance_type

NAME TYPE VALUE
———————————— ———– ——————————
instance_type string asm

In case you are connected to ASM instance, it will display value as asm, otherwise it will display value as RDBMS.

3) What are Diskgroup’s and Failuregroups?

Diskgroup is a terminology used for logical structure which holds the database files. Each Diskgroup consists of Disks/Raw devices where the files are actually stored. Any ASM file is completely contained within a single disk group. However, a disk group might contain files belonging to several databases and a single database can use files from multiple disk groups.

Failuregroups are used when using Normal/High Redundancy. They contain the mirrored ASM extents and must be containing different disks and preferably on separate disk controller.

4)Can ASM be used as replacement for RAID?

ASM is supposed to stripe the data and also mirror the data (if Using Normal, High Redundancy). So this can be used as a alternative for RAID 0+1 solutions

5) How does ASM provides Redundancy?

When you create a disk group, you specify an ASM disk group type based on one of the following three redundancy levels:

• Normal for 2-way mirroring - When ASM allocates an extent for a normal redundancy file, ASM allocates a primary copy and a secondary copy. ASM chooses the disk on which to store the secondary copy in a different failure group other than the primary copy.
• High for 3-way mirroring. In this case the extent is mirrored across 3 disks.
• External to not use ASM mirroring. This is used if you are using Third party Redundancy mechanism like RAID, Storage arrays.

6) Can we change the Redundancy for Diskgroup after its creation.

No, we cannot modify the redundancy for Diskgroup once it has been created. To alter it we will be required to create a new Diskgroup and move the files to it. This can also be done by restoring full backup on the new Diskgroup. Following metalink note describes the steps

Note.438580.1 - How To Move The Database To Different Diskgroup (Change Diskgroup Redundancy)

7) I am unable to open the ASM instance. What is the reason?

ASM instance does not have open stage. It has got only two options

• Nomount- This starts the ASM instance
• Mount- At this stage, Diskgroup defined in ASM_DISKGROUPS parameter are mounted

When you try to open the ASM instance , you get following error

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-15000: command disallowed by current instance type

Can ASM instance and database (rdbms) be on different servers?

ASM instance and Database (rdbms) have to be present on same server. Otherwise it will not work.

9)Can we see the files stored in the ASM instance using standard unix commands.

No, you cannot see the files using standard unix commands like ls. You need to use utility called asmcmd to do this. This is present in 10.2 and above.e.g
/home/oracle>asmcmd
Asmcmd>

You can use help command to see the options.

Note: - You can use asmcmd for 10.1 database also. For this you can copy the asmcmdcore and asmcmd file from 10.2 ORACLE_HOME to 10.1 ORACLE_HOME. Else you can download the files from Metalink Note:332853.1

10) What kind of files can be stored on ASM Diskgroup.

You can store the following file types in ASM disk groups:

• Control files
• Datafiles, temporary datafiles, and datafile copies
• SPFILEs
• Online redo logs, archive logs, and Flashback logs
• RMAN backups
• Disaster recovery configurations
• Change tracking bitmaps
• Data Pump dumpsets

Note: Oracle executables and ASCII files, such as alert logs and trace files, cannot be stored in ASM disk groups.

11)Can we use ASM for storing Voting Disk/OCR in a RAC instance?

No. You cannot use ASM for storing the voting disk and OCR. It is due to the fact that Clusterware starts before ASM instance and it should be able to access these files which is not possible if you are storing it on ASM. You will have to use raw devices or OCFS or any other shared storage.

12)Does ASM instance automatically rebalances and takes care of hot spots?

No. This is a myth and ASM does not do it. It will initiate automatic rebalance only when a new disk is added to Diskgroup or we drop a disk from existing Diskgroup.

13) What is ASMLIB?

ASMLIB is the support library for the ASM. ASMLIB allows an Oracle database using ASM more efficient and capable access to diskgroups. The purpose of ASMLIB, is to provide an alternative interface to identify and access block devices. Additionally, the ASMLIB API enables storage and operating system vendors to supply extended storage-related features. You can read more about ASMLIB in

Note: 359266.1 - FAQ ASMLIB CONFIGURE, VERIFY, TROUBLESHOOT

ASMLIB for linux can be downloaded from following link

http://www.oracle.com/technology/tech/linux/asmlib/index.html

14) What is SYSASM role?
Starting from Oracle 11g, SYSASM role can be used to administer the ASM instances. You can continue using SYSDBA role to connect to ASM but it will generate following warning messages at time of startup/shutdown, create Diskgroup/add disk ,etc

Alert entry
WARNING: Deprecated privilege SYSDBA for command ‘STARTUP’

15) How can we copy the files from/to ASM?

You can use RMAN or DBMS_FILE_TRANSFER.COPY_FILE procedure to copy the files to/from ASM from/to Filesystem. Starting from Oracle 11g, you can use cp command in asmcmd to perform the same between ASM Diskgroups and also to OS Filesystem.

16) Can we use BCV to clone the ASM Diskgroup on same host?

Diskgroup Cloning is not supported on the same host using BCV. You have no other option to use except RMAN DUPLICATE. You can find more detail on BCV and ASM in below whitepaper

http://www.oracle.com/technology/products/database/asm/pdf/asm-on-emc-5_3.pdf

17) Can we edit the ASM Disk header to change the Diskgroup Name?

No. This cannot be done. Please find details in

http://askdba.org/weblog/?p=128

18) Whats is Kfed?

kfed is a utility which can be used to view the ASM Disk information. Syntax for using it is

kfed read devicename

Read more about kfed here.

19) Can we use block devices for ASM Disks?

Yes. Starting from Oracle Database 10.2 block devices can be used directly for ASM Disks in Linux. This is not true for other Unix based systems where block devices are not supported yet.

Along with this it is recommended to use a Device mapping functionality so that disk mapping is preserved after disk failure. This is important when you have devices as /dev/sda,/dev/sdb,/dev/sdc and due to some reason the devices are not detected at next reboot (say /dev/sdb), the system will map the incorrect device (i.e /dev/sdc will be marked as /dev/sdb). You can use following methods for preserving disk names

-udev – the role of udev is to provide device persistency and naming consistency.This is especially important for the Oracle Cluster Registry (OCR) and Voting disks required by Oracle Clusterware.

-ASMLIB – ASMLIB will provide device management specifically for ASM disk devices.

Refer to following Whitepaper for more details

http://www.oracle.com/technology/products/database/asm/pdf/device-mapper-udev-crs-asm%20rh4.pdf

20)Is it mandatory to use disks of same size and characteristics for Diskgroups?

No, it is not mandatory to use the disks of same size and characteristics for Diskgroups though it is a Recommended Practice.

Same size disk for Failuregroups in Normal/High redundancy will prevent issues like ORA-15041 as the file extents needs to be mirrored across the disks. Also as Oracle distributes data based on capacity, so larger disk will have more data stored in it and which will result in higher I/O to disk and eventually can lead to sub-optimal performance.

Moreover having disks of different characteristic like varying disk speed can impact the performance.

When managing disks with different size and performance capabilities, best practice is to group them into disk groups according to their characteristics. So you can use higher speed disks for your database files while other disks can be part of Diskgroup used for Flash Recovery Area.

21)Do we need to install ASM and Oracle Database Software in different ORACLE_HOME?

No. Again installing ASM and Oracle Database Software in different ORACLE_HOME is not mandatory but a best practice. This is useful in cases when we need to have multiple databases using same ASM instance and you need to patch only one of them. E.g You need to apply a CBO patch to one of 10.2 database while your other 10.1 database using different installation does not require it. In this case having a ASM_HOME separate from 10.2 ORACLE_HOME will allow your 10.1 database to keep running. Thus this approach is useful for High Availability.

22)I have created Oracle database using DBCA and having a different home for ASM and Oracle Database. I see that listener is running from ASM_HOME. Is it correct?

This is fine. When using different home for ASM, you need to run the listener from ASM_HOME instead of ORACLE_HOME.

Additional Links for Further Reading

http://www.oracle.com/technology/products/database/asm/index.html http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/toc.htm

http://www.oracle.com/technology/products/database/asm/pdf/device-mapper-udev-crs-asm%20rh4.pdf
Note:359266.1 - FAQ ASMLIB CONFIGURE,VERIFY, TROUBLESHOOT
Note 266028.1 - ASM Using Files Instead of Real Devices on Linux
Note.438580.1 - How To Move The Database To Different Diskgroup (Change Diskgroup Redundancy)

Few days back, while trying to create a synonym over dblink I experienced following error:

SQL> create synonym synonymn_name for dual@DBLINK9i;
SP2-0042: unknown command "DBLINK9i" - rest of line ignored

Strange error !!!

Also while trying to perform a simple select statement over another dblink:

SQL > select * from dual@dblink10g;
SP2-0042: unknown command "DBLINK10g" - rest of line ignored.

It also failed with a similar error, though the select over this dblink was working fine earlier.The tnsnames.ora entry was correct and double checked.So what happened to this dblink now?

From other database I was able to perform a simple select over same dblink.

While I was researching this, I came to know that sql queries also started failing :

SQL> UPDATE HRRECORD_TEST SET EMAIL_ADDRESS = 'abc@123.com' WHERE NAME = 'XYZ';
SP2-0734: unknown command beginning "123.com'..." - rest of line ignored.
SQL>

One Notable thing was that all the queries which failed contained “@” symbol and I also got this error  while create a synonym over dblink (having “@” symbol).

These two issues were related, Then I tried to create a synonym locally and it went fine. This led to conclusion  that sql prompt was not recognising “@” symbol.

So it was clear that the problem was with terminal settings at OS level for oracle user.

Searcing google for terminal setting at OS, I found a command which is used to Sets options for your terminal i.e “stty”. By using stty we can change the terminal settings.

Now checking the current terminal settings using:

# stty -a

speed 9600 baud; line = 0;
rows = 36; columns = 80
min = 4; time = 0;
intr = DEL; quit = ^\; erase = DEL; kill = @
eof = ^D; eol = ^@; eol2 <undef>; swtch <undef>
stop = ^S; start = ^Q; susp <undef>; dsusp <undef>
werase <undef>; lnext <undef>
parenb -parodd cs7 -cstopb hupcl -cread -clocal -loblk -crts

From this output I found that there were two settings causing problem:

1) kill = @

2) eol = ^@

Then, to overcome this I added the following two lines in the oracle user .profile

stty kill ^U

stty eol ^E

Above change will force oracle user to use ^U to kill any session and ^E to end a line instead of using @ for these purpose.

After making these changes in oracle user’s .profile everything went fine. In the end it turned out OS issue whose settings was modified by sysadmin.

I’ve noticed a few forum questions regarding ASM or indeed the OUI not being able to see devices that are managed via ASMLib. This prompted me to “upgrade” my knowledge of ASMLib and this blog is just a few extra tools for checking on your ASMLib devices. By the way, anyone out there thinking ASMLib [...]

I have been using Vista for about 3 months and finally found a way to start my databases with a single double-click

The method I used until yesterday was a .BAT file that starts the services, I had then to right click on the shortcut, run as administrator, confirm the UAC warning. 3 Clicks. Way to much…

I have googled quite a lot and found Vista setuid - How to elevate without prompting

Ok, here we go :

1) run mmc
2) file - add snapin
3) Task Scheduler Library - Create Task
4) Name: startoracle
Run with highest privileges
Actions - New - Start a program - Program:net - Arguments: start OracleOraDb11g_home1TNSListener
Actions - New - Start a program - Program:net - Arguments: start OracleServiceLSC01
Actions - New - Start a program - Program:net - Arguments: start OracleServiceLSC02
…
5) create a batch file that runs : schtasks /run /tn startoracle

Same for stoporacle

It seems to work fine. Do not play too much with mmc if you do not know it. It is a powerful tool

I am learning that I have been trusting Oracle too much. I have figured that Oracle has top rate developers, so when they offer a facility like ASMM or GATHER_STATS_JOB. I assume that it will be done well.

Now I am learning that ASMM has been allocating our shared pool too large, and has been adjusting sizes not once an hour, but once a minute.

I am not happy with the statistics that the 'wonderful' automatic GATHER_STATS_JOB has been collecting. Also the job has been causing contention with some of our production jobs.

The one that seems really strange to me is Log_buffer. I allocated 28M to log_buffer. According to Oracle, LGWR should be writing, at a maximum, when log_buffer is 1M full. Instead, I see that it is writing 2M at a time. I am going to have to figure that one out.

I expect that when Oracle offers to automate these processes, that this is their own product, so the calculations will be well done. I expected, but I am wrong.

Over in the forums, one of the most frequent complaints by people who try to answer questions is around the lack of information about the Oracle product version and the operating system version and edition. This information is usually missing by people who want to learn Oracle by installing it on their home/school/laptop computers.

A general hint for all of you who want to learn by installing Oracle product on your computer ...

It may be a surprise that Microsoft has different versions and different editions of Windows. Each version has different internals. Each edition has different capabilities.

When Oracle programs are written and released, they expect certain internals (DLLs, registry layout, etc.) and certain capabilities (supporting applications, such as security administration)

If you try to install Oracle product on a Windows version that is too old or too new, Oracle may not be able to call the operating system properly. So installing Oracle 8i database 8.1.7 on Windows XP Professional may not work, and installing any Oracle database version lower than 10.2.0.3 on Vista will not work.

(All right, technically Express Edition is at version 10.2.0.1 - but it was modified to work with Vista and a lot of the incompatible features are not included in XE.)

If you try to install Oracle product on a Windows Edition that is too limited, you may encounter problems because of Windows capabilities being missing, turned off, or just being limited. So installing Oracle 9i or 10g Database on Windows XP Home may or may not work (or some aspects may not work) partially because some features require security settings that are not exposed in XP Home. This seems to be especially true of the Vista Home vs Vista Business editions.


Please mention the version & edition of Windows! (And the product, version and edition of Oracle!)