Oracle Rant

I just wanted to share an experience that I had with Oracle ASM that made my life a breeze. ASM gives the DBA more control and flexibility in dealing with storage. It even has some excellent performance benefits.

Well, I would also like to add to the list that it also proactively cures headaches and stress! This is why. We have an IBM Shark that is going off of lease. We needed to move the database to Hitachi storage. The problem was we needed to do it fast and we couldn’t get the application owners to give us a change window in the alloted time frame. The most they would give us was 2 hours and this was a multi-terabyte database. It wasn’t happening!

So how did Oracle ASM save me? Simple! I just added the new disks to my existing diskgroup and let ASM automatically rebalance the disks. I let this job run overnight with virtually no impact to application performance. I could have done it with a higher rebalancing factor but that had a greater chance to impact the application. Instead, I just did it with a power of 1 which was the default.

Once the job finished rebalancing, I then moved to drop the old disks. Please make sure you add more than enough space in new disks to allow you to remove the old ones. With the disks being dropped, Oracle moved all extents off those disks and neatly rebalanced it on the ones that were left. The job took a day to complete and after ASM was done with them, the Unix SA and SAN admin went ahead and removed them, all the while the application maintained 100% uptime and solid performance.

ASM has it’s limitations such as having to use RMAN to do backups and many of your shell scripts won’t work against it without being rewritten to use sqlplus instead or RMAN, but even RMAN is an improvement over traditional backups. I wouldn’t even call this a limitation but a strength. Oh well, I’ll just stick with ASM on this one. Definitely take the time to get up to speed on ASM! It’s a lifesaver.
 

I came across Tyler Muth’s blog via Tom Kyte’s blog earlier this week. While he doesn’t have that many posts, his blog has powerful content. I also love the layout of his blog. In particular, I was fascinated with the way he was formatting his code snippets and wanted to add this functionality to my blog.

I came across a Wordpress plugin by the name of "syntaxhighlighter". I also found out how to add this functionality to blogger blogs as well.

Here is an example of a code snippet with this plugin.

CREATE TABLE TEST
   (ACCOUNT NUMBER(16,0),
    FIRST_NAME VARCHAR2(20)
   );

And there you go. I might need to tweak the formatting to highlight words a certain color but it shouldn’t be too hard. It also looks like orana.info picked up my post as I was testing this plugin, so the post was incomplete at the time.

I will talk about some of the Oracle Automatic Database Management enhancements in Oracle Database 10g. The main idea behind these is to make the DBA more productive. I have used some of these and some I have not. I guess you could say I have one foot in the future and the other in the past. As I share my studying with you, perhaps I will start using some of these myself.

The first enhancement is the ADDM (Automatic Diagnostic Monitor). Now in order to use ADDM, you must have an Oracle Diagnostic Pack license. Typically you use and access ADDM through your database control environment but you can also access it via packages and procedures. By default ADDM runs every hour. It is dependent upon the AWR (Automatic Workload Repository), which is like statspack on steroids and also runs every hour by default. You need an Oracle Diagnostic Pack license to look at AWR snapshots. If you want to look at an AWR report then just run awrrpt.sql which is located in your rdbms/admin directory. I’ll do a post about doing statspack in a previous post, but I only look at statspack for pre 10g databases. Also, there is a new background process, MMON, which attaches directly to the SGA and gathers statistics rather than querying the data dictionary. Back to ADDM. ADDM diagnoses many types of performance issues. Those include:

• Configuration issues
• Improper application usage
• Expensive SQL statements
• I/O performance issues
• Locking issues
• Excessive parsing
• CPU bottlenecks
• Undersized memory allocation
• Connection management issues

Read the rest of this entry »

The purpose of this blog entry is to show you how to create a physical standby database. I am going to do a few posts actually.

The first will be a basic data guard setup on 2 different machines. In this case, I used VMWARE. There are things I am not going to cover in order to keep things very basic and simple. If you want to do data guard on the same machine, you will need to do some renaming of things dynamically. It’s in the data guard documentation that I encourage you to read.

The second post will deal with enabling flashback database and how it works. We will enable it on our primary and secondary.

Next, we will integrate the two. We will be able to open our DB in read only mode for reporting purposes. We will also look at keeping a testing environment in sync with production. We will take it out of standby mode, do our testing, then flashback to an SCN before it came out of standby. It will then sync back up with the primary. Flashback also works in turning your primary into a new standby in certain situations.

On another post, I will look at the different production modes and discuss what we could have done differently in our setup to meet our needs and some of the pitfalls to watch out for.

Last, I will discuss how you can backup your standby DB with RMAN so you can minimize the impact to your production system. These backups can then be used to restore production since it is an exact block for block copy of your production database. If I get the itch, I will even add a final post on logical standby.

We also have a RAC environment that is new where we will be implementing data guard. As soon as I set that up, I will come up with a post.

First, you need a database. I will assume that you have already done this. I will also assume that you have installed the Oracle binaries on your standby host. Please make sure it is at the same version as your production system and has all the same patches applied. If all that is done, then proceed.

Your database needs to be in archivelog mode. While logged in as sysdba, issue “archive log list” command. If it is not enabled, then enable it. If you don’t have log_archive_dest_1 set to anything, then it will use your flash recovery area by default . If you are going to use this, then make sure you have ample room by setting the db_recovery_file_dest_size parameter. Also make sure you physically have the diskspace. You also need to enable forced logging. This will not allow anything to run without it being logged.

Read the rest of this entry »

Lewis Cunningham tagged me. I have to tell 8 things about me that most people don’t know!

1. I am a native Floridian and was born here! (We’re an endangered species).
2. I have a pile of books in my room that I intend to read but haven’t (trying hard not to look at them).
3. I used to be a DJ, still have lots of vinyl (mainly house, trance, and electronica).
4. I’ve been driving the same car for 12 years! (new cars look nice… not having a car payment looks even better).
5. My very first job out of High School was working in a warehouse. (what a coincidence, so was Lewis’s!, after 6 months, I decided it wasn’t my thing).
6. I used to be scared to fly, now it doesn’t bother me. (except the 9 hour flights to Europe for Disaster Recovery!.. but they do feed us well).
7. Heights usually do not scare me, but recently I went atop the Eiffel Tower back in November and wanted to immediately go back down! It was raining and very windy in the pitch darkness of night.
8. When we were kids (younger brother and I), we decided to skateboard off the roof of our 2 story house while using sheets as parachutes. I told him if he went first, so would I. After brother went , I decided it wasn’t a very good idea! hehe (Dad came home and punished us after the neighbor called him! It wasn’t pretty.)

I will add people after I tag them. In the meantime, I just wanted to get this up!

As a best practice, I don’t like logging in as SYS or SYSTEM unless I have to. I also don’t like remote OS authentication. If I connect remote, I want to use a password. If I am on the box however, I like to just connect using OS authentication (not providing a username and / or password). Now I could also give this internal user access to connect over the network, but well, I am not going to go there. The purpose of this post is to show you what I did to set this up on Windows. On Linux or Unix, it is quite easy, on Windows there is a gotcha.

First, to use OS Authentication for Oracle logins, you need to understand a parameter.

SQL> show parameter authen

NAME                    TYPE        VALUE
----------------------- ----------- -------------
os_authent_prefix       string      OPS$
remote_os_authent       boolean     FALSE

The os_authent_prefix could really be set to anything you want, but I like the default. The other parameter is set to FALSE because I don’t want to trust other operating systems authentication.

Next, you need to create the user. How do you find out who that user is? Simple, run this query after you login locally.

SQL> select UPPER(sys_context('userenv','os_user')) from dual;

UPPER(SYS_CONTEXT('USERENV','OS_USER'))
-------------------------------------------------------------------
MACHINENAME\TOM

Now that I have this information, I can easily create the user and grant him the privilege he needs.

SQL> create user "OPS$MACHINENAME\TOM" identified externally;

SQL>

As you can see, Oracle prefixed my login with OPS$ that I defined in os_authent_prefix. I also had to use the “\” because Oracle sees my login as “MACHINENAME\TOM” and not just Tom. The gotcha is that you cannot create a user with the “\” without using double quotes. If you were a Windows Domain user account then you would use “DOMAINNAME\USERNAME”.

I hope that someone finds this little tip useful in case they want to setup OS Authentication on Windows : ).

Backup and Recovery is one of the most critical tasks that a DBA has to perform. Sadly, it is also one of the most misunderstood. RMAN should be the tool of choice for backup and recovery for Oracle databases. There is so much that can be done with RMAN. What Robert and Matthew do is break it down piece by piece. There is a great intro and they build on that in subsequent chapters. If you want to link RMAN directly with your backup software (i.e. Veritas, EMC, Legato, etc..) then the authors show you how. I used the Veritas piece to link RMAN in and it worked flawlessly. They go over the latest features including block changing files (for faster incremental backups), merging level 1 and level 0 backups (for faster recoveries without the overhead of backing up the whole database), and even cover what the flash recovery area is and how to use it. There is so much great material packed in this book. Yeah, you can try and read Oracle’s docs, but rather than just show you how to do it, the authors also teach you why to do it. You can read this book chronologically or use it as a reference. The authors writing style make this book a great joy to read. They also include a lot of great examples in case you like to learn by seeing. I would highly recommend this book if you are an Oracle DBA, whether you use RMAN or not. Afterall, RMAN is what you should be using :).

Oracle Automatic Storage Management was introduced in Oracle 10g and improved upon in Oracle 11g. It’s the new storage filesystem from Oracle that runs on every platform. The idea behind ASM was to create a filesystem that was designed and integrated for the Oracle database. It gives high performance, easier administration, more flexibility, and clustering capabilities for Oracle RAC, and lower cost. It’s an exciting new technology that every Oracle DBA should look into. Instead of managing files you manage disk groups which are pools of storage. The book was written by a group of authors who have great insite into ASM including the Director of Development for ASM, Rich Long. They explain the basics of the storage stack. They then talk about configuring ASM and what the gotchas are for platforms like Solaris, AIX, Linux, Windows, and HP-UX. They authors even elaborate on multi-pathing software like HDLM and EMC Powerpath. They even talk in detail about the ASMLIB utility that is used for managing disks for ASM on Linux. Next, they discuss Diskgroups and Failure Groups in ASM. They then get into ASM administrations such as Files, Aliases, Templates, Rebalancing, Space Allocation, ASMCMD, Extents, Striping, Mirroring. They then get into deployment and Storage Array Configurations. The book finally ends with a Troubleshooting Guide with a clear explanation of the Alert Log files for ASM and some scripts that can be used to maintain an ASM environment.  I would highly recommend this book for any DBA that will be working with ASM and / or RAC. The high performance, ease of use,  and low cost make it a very attractive solution. This book will teach you everything you need to know about ASM and make you an ASM expert. After learning ASM, it also makes an excellent reference. I give it a perfect rating of 5 stars!

List all srvctl commands

srvctl –h

Shutdown service

srvctl stop service –d database_name

Shutdown the database on all nodes

srvctl stop database –d database_name

Shutdown a specific instance

srvctl stop instance –d database_name -i instance_name

Shutdown nodeapps (includes listener)

srvctl stop nodeapps –n node_name

! node_name is usually the hostname of the machine

Shutdown the listener

srvctl stop listener –n node_name

Shutdown ASM

srvctl stop asm –n node_name

To Startup, replace stop with start.

If you shutdown the database it will stop the service since the service is dependent upon the database. The reverse is also true. If you startup the service it will implicitly startup the database.

To check to see if your services are running.

$CRS_HOME/bin/crs_stat –t

If you want more detailed information, drop the –t.

$CRS_HOME/bin/crs_stat

You may include the $CRS_HOME/bin in your $PATH for ease of use.

I wanted to pass on a tip to anyone doing ASM or CRS or both on Sun Solaris. When you install CRS, ASM, or both, you have to use raw devices. CRS needs the raw devices for the OCR and voting disks. ASM needs the raw devices so that it can control them and offer them in storage pools known as disk groups. Solaris uses a Virtual Table of Contents (VTOC) file that it uses to share devices between servers. When the disk is formatted by your Solaris SA, you need to make sure they do not use the first cylinder. Instead of using cylinder 0 to whatever, they need to use from cylinder 1 to whatever, leaving cylinder 0 alone. That way, when you dd the disks it will not cause the disks to “disappear”. My Solaris admin and I went through it about 4 times till we found the catch. The documentation on metalink is a little fuzzy so hopefully this helps some people out.

Also, with raw devices, I would suggest using an mknod command to label the disks so that it is much easier to support in the future.