User login

Aggregator

Please use the contact form to include your blog here, suggest a feed, or give feedback.

If your blog is aggregated on OraFAQ, you may want to display this image on your blog:

Search

[ Advanced ] [ Help ]

Coskan Gundogar

What I learned about Oracle

URL: http://coskan.wordpress.com

Updated: 9 hours 29 min ago

ORA-02291: integrity constraint (RMAN.RLH_F1) violated

Thu, 2009-11-26 10:38

On one of our boxes, we start to get the error stack below when we want to do a crosscheck with catalog database in use.

RMAN>crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=357 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of crosscheck command on ORA_DISK_1 channel at 11/26/2009 15:08:05
ORA-19633: control file record 8403 is out of sync with recovery catalog

RMAN>resync catalog; 

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found

I unregistered the DB and register it but after it tries to resync the catalog during registration, error came up like below .

RMAN>unregister database;

database unregistered from the recovery catalog

RMAN>register database
database registered in recovery catalog
starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found

I could not find a metalink note, but a working solution from forums.oracle.com

Basically, you need to run unregister it by running undocumented internal !!!!! DBMS_RCVCAT.UNREGISTERDATABASE procedure on RMAN catalog DB. (ask Oracle support before you start doing something)

SQL> select 'EXEC DBMS_RCVCAT.UNREGISTERDATABASE('||DB_KEY||','||DBID||');'
3   command, resetlogs_time
4  from rc_database
5  where name = '&db_name';
Enter value for db_name: XXXXXX

COMMAND                                                      RESETLOGS_TIME
------------------------------------------------------------ -----------------
EXEC DBMS_RCVCAT.UNREGISTERDATABASE(5626305,201074392);      20071130 11:57:12

Run the command if the resetlogs time is right and it will unregister your database from catalog.

Re-register your DB ( RMAN>register database) , if you are lucky as me it will register and sync the catalog without a problem.

Reminder for Oracle Performance Firefighting by Craig Shallahamer

Sun, 2009-11-22 20:07

Just a reminder for the ones who are waiting for PDF version of Oracle Performance Firefighting by Craig Shallahamer, it is now available here so you can avoid shipping costs. Click here for my review of the book if you haven’t read before.

Crash recovery cannot be done automatically – [kcratr1_lastbwr]

Sun, 2009-11-22 08:30

I hit the error below during startup of my 10.2.0.1 RAC installation running on Solaris X86-64 on VMWare

ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],[], [], []

Basically none of the instances start-up with srvctl command and log this error to alert log.

Tried to run manual startup of instances but no chance same error.

Quick search on MOS (yes it was working ) take me to the note 393984.1 (no bug number)

It says this is a bug on Enterprise Edition – Version: 10.2.0.1 to 11.1.0.6.0

Solution is manual recovery.

SQL> startup;
        ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  2020864 bytes
Variable Size             138414592 bytes
Database Buffers          226492416 bytes
Redo Buffers                2170880 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],
[], [], []

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

Oracle 10g/11g Data and Database Management Utilities by Hector R Madrid

Tue, 2009-11-03 10:29

Two months ago I got mail from PACKT Publishing to review Oracle 10g/11g Data and Database Management Utilities by Hector R Madrid under their free blogger review program. Initially, I promised to finish it by beginning of October, but due to personal reasons, I can only finish it by today. Till I do my review, Hemant K Chitale and Anantha Narayan from Oracle blogosphere already mentioned about the book in their blogs so you can go and check their opinions as well (able to find different opinions, before buying something is definitely a good option)

After marketing and excuse section, now it is time to review the book,

This book promises us to learn (taken from the back cover);

to optimize time-consuming tasks efficiently by using Oracle DB utilities.
perform data loads on the fly and replace the functionality of the old exp/imp utilities using data pump or SQL*Loader
boost defences with Oracle Wallet
Improve Performance of RMAN backups
Perform more than just ETL process by taking advantage of the External Tables feature

Can it keep it’s promise ? I think Hector did very well about keeping his promise.

I liked the way he wrote the book, it is again very well organized with very simple easy to understand language. (If you have read my old reviews probably you already know that, I like the books I can finish, so in my opinion a good book must be a page turner as well as being technically satisfactory)

Who should have this book ?

There is already a who is this book written for on the back cover but I have to add some words on top of it.

If you don’t like to read Oracle Official Docs very much, if you can easily get bored or lost during your official docs journey and you want to have a reference on your desk about available Oracle Utilities, this book is definitely written for you.

If you are DBA at the same place more than 5 years (where things are stable enough) and started this job with version 7 or 8 and due to the nature of your company or yourself you don’t have to learn new things that much and can still pay your mortgage this book is a MUST for you. At least you can ease your life, save gazillions of time and look wiser to the new junior DBA when you can able to compare exp with data pump because if you can’t do it they wont trust your experience at least I don’t. Market is very tight and this book gives you enough knowledge to cope with market conditions.

If you are a junior DBA who doesn’t know where to start Oracle Utilities, I strongly recommend this book for your personal development.

Do I happy to have the book ? Sure I do. I did not learn new things that much but it worth reading for just couple of tips and tricks and learning some of the topics I have no experience on.

Now, is the time to go over the summary of topics. (What Hector did was he tried to cover every topic with average 30 pages and enough screenshots and code samples this make book easy to read and avoid boredom of long topics. )

1-Data Pump: This topic is well covered. There is enough information for started. I wish Hector covered the compression and encryption options of 11G, with examples. I liked tuning Data Pump performance section where he covers factors other than parallelism which affects datapump performance.

2-SQL*Loader: I have to confess that SQL*Loader is something very old in Oracle but I am kind a new for it because I never needed to use it at job so my knowledge was depending on simple tests. It was very nice to learn what it is capable of with loads of examples.

3-External Tables: I love and used external tables very much for mostly data and this section brought new areas of usage to my knowledge like reading listener and alert log via external tables.

4- Recovery Manager Advanced Techniques: I think this topic is named wrongly because when you see advanced you expect more but I did not get that much. It basically covers what RMAN can do instead of your old manual backup techniques. If you already an RMAN user and don’t know the new feature called faster backups through intra-file parallel backup restore operations in 11G it will be a good learning for you.

5- Recovery Manager Restore Recovery Techniques: This is the second part of RMAN managed backups which is RMAN managed restore recovery. Nothing new for me and I have to say TSPTR which is the most important feature is missed.

6- Session Management: Overall session monitoring for wait and lock analysis is covered with addition of resource manager. Old school boys will definitely find something new in this chapter.

7- Oracle Scheduler: This chapter can convince you to use this tool more. Job Chains should be covered with a little detail I think.

8- Oracle Wallet Manager: This chapter was new to me and probably new to most of you. I can say that is a good introduction to Oracle Wallet Manager.

9- Security Management: Again good introduction for encryption options available in Oracle. It is mostly practical usage of previous chapter.

10-Database Configuration Assistant: I use DBCA very much and this chapter was a bit fast reading but it is a must for guys who are not aware of what this tool can do other than creating a DB.

11- Oracle Universal Installer: Basically, Hector tried to mention that this tool is not just doing next next next. Cloning Oracle home using OUI was a new learning for me.

12- Enterprise Manager Configuration Assistant: There are nice tips and tricks about emca troubleshooting. You will like this topic if you have problems with this tool and don’t know where to look.

13-Opatch: This chapter is very well detailed and it adds Enterprise Manager usage for opatch utility. A must read for the ones who use opatch when applying patch.

Biggest problem of this book is lack of references. I really don’t like something without references. The author cannot know them by himself, he should have learned them from somewhere and it would be very nice if he shared them with us too so the book can point the users to the right directions to finalize their journey about utility they look for.

Well that’s about it. I want to thank PACKT Publishing for free review option and Hector for this nice work.

If I can find time to read, Next Book review probably will be Practical Oracle 8i :Building Efficient Databases , which I finally got my copy of it and willing to read it asap. Luckily Christmas coming and it will be quite at work

Simple blank matters

Mon, 2009-09-14 07:16

I saw this interesting bug on Metalink headlines and I thought it will be nice to post it here.

This is the heading of the bug ‘ADDING COLUMN WITH DEFAULT NULL LEADS TO UNNECESSARY FULL TABLE UPDATE ‘ numbered 8840491.

Normally, if you add a column with a default null value, there won’t be an update on the table itself (I think after 10G because trace for 9i shows update on table), running at the background, but if you put some spaces to make your alter statement look better, it is where the problem begins.

What I mean is these 4 statements are different during runtime due to the bug.

alter table test add (id number default null);

alter table test add ( id number default null);

alter table test add (id number default null );

alter table test add ( id number default null );

Lets see how do they differ. Actual bug is reported on 10.2.0.4 and I am able reproduce it on 11.1.0.6


SQL> set timing on
SQL> create table test (id number not null);

Table created.

Elapsed: 00:00:00.54
SQL> insert into test
  2  select rownum from dual connect by level<=1000000;

1000000 rows created.

Elapsed: 00:00:01.65

SQL> alter session set tracefile_identifier=null_bug_test;

Session altered.

Elapsed: 00:00:00.00
SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
SQL> --no space before after ()
SQL> alter table test add (id2 number default null)  ;

Table altered.

Elapsed: 00:00:00.09
SQL> --space after (
SQL> alter table test add ( id3 number default null)  ;

Table altered.

Elapsed: 00:00:00.01
SQL> --space before )
SQL> alter table test add (id4 number default null )  ;

Table altered.

Elapsed: 00:01:12.90
SQL> --space before and after ()
SQL> alter table test add ( id5 number default null ) ;

Table altered.

Elapsed: 00:01:34.27
SQL> -- spaces everywhere but not after before ()
SQL> alter table test add (id6  number   default   null)  ;

Table altered.

Elapsed: 00:00:00.04
SQL>

As you see from the timings some of them are longer than expected. Lets find them in tracefile

These are the update statements from the trace file.

=====================
PARSING IN CURSOR #1 len=29 dep=1 uid=82 oct=6 lid=82 tim=21689660840 hv=720540867 ad='30679e28' sqlid='7nb3cf4pg5563'
update "TEST" set "ID4"=null
END OF STMT
PARSE #1:c=15625,e=17207,p=0,cr=106,cu=1,mis=1,r=0,dep=1,og=1,tim=21689660835
=====================
PARSING IN CURSOR #5 len=29 dep=1 uid=82 oct=6 lid=82 tim=21762749484 hv=1088193227 ad='30679058' sqlid='f4g28hd0dt0qb'
update "TEST" set "ID5"=null
END OF STMT
PARSE #5:c=46875,e=146504,p=294,cr=100,cu=0,mis=1,r=0,dep=1,og=1,tim=21762749480

As you see space before ) and space before and after () causing full table update and guess the damage of this tiny hidden change on over 10 million row huge table.

Next time, if you wait your not null new column getting added longer than it needs on 10G>, check the syntax you might be hitting this bug.

Oracle Performance Firefighting by Craig Shallahamer

Thu, 2009-09-10 09:42

Today I am going to write about an amazing book Oracle Performance Firefighting by Craig Shallahamer, which was released in July 2009. After all the papers I read from Craig, my fingers were crossed for the release date and I ordered the book for our company immediately after release. The reason I was very excited about the book is that I always feel uncomfortable with overall system performance tuning and this is the area where Craig looks very good at.

During the review, I will try to cover what this book promises and gives us by going through all the chapters one by one. For overall, this book is not for sql tuning It is about how to find the problem and giving possible other solutions for common problems ( in addition to tuning your code as a first option.) by giving the information about how Oracle works in most problematic areas like buffer cache, shared pool, redo, undo and especially latches and mutexes.

Before starting to go through chapters, I want to say that the book is very well organized and the way Craig teaches avoids boredom about numbers and math. I could not leave the book even on holiday. The way he covers the very hard to understand topics brings curiosity about the next topic. One more thing is the number of usage of the word OraPub (his company) kept minimum. While reading Optimizing Oracle Performance by Cary Millsap and Jeffrey Holt, I really hated the word Hotsos because it was nearly on every page and that was very annoying. If you are curious about this problem it is not happening that much in this book.

Lets start with going through chapters (Chapters with * are the ones that really needs attention)

1- Methods and madness: This is the introduction to how to be a firefighter from both methodic and holistic perspective. Craig gives information What you need during Oracle firefighting and how you can build a methodology . He covers the basics of analysing the system as a whole under the name of OraPub 3 Circle Analysis. When I first read about the OraPub 3 Circle Analysis I did not expect much but after a few chapters I admit that it works quite fine. Most interesting topic to me was how to write the summary of your firefighting story. That is something I need to work on.

2- Listening to Oracle’s Pain: This chapter tries to explain how to understand what Oracle is crying for by using Oracle Wait Interface and ORTA (oracle response time analysis). It covers how Oracle kernel developers instrumented their code for creating Oracle Wait Interface. It wasnt something I did not know but still nice to remember most them.

3- Serialization Control (*): This chapter is where this book starts to rock about explaining what is going on in Oracle by telling how Latches Mutexes and Locks work and differ from each other. After finishing this chapter I believed that the book already worth the money I paid and there were 6 more chapters to go. Craig definitely knows how Oracle works and better than just knowing he knows how to explain it like latches for dummies. It is really hard not to remember or not understand what he is explaining.

4- Idenfying and Understanding Operating System Contention: This chapter tries to cover how to use unix/linux tool to gather information from operating system to do the operating system circle of OraPub 3 Circle Analysis. Apart from using tool it is very good about the way Craig teaches about how to talk and convince Network, OS and Storage guys in case of a problem without making their nerves. I personally liked very much the word Craig uses like “there is a memory pressure” instead of “paging”

5- Oracle Performance Diagnosis: This chapter is going deep into how to gather overall performance related data from Oracle and how to interpret the data you gathered without going to compulsive tuning disorder. He covers the wait event myths section which was very interesting and he tries explain why profiling is not “always” the best approach. Making the most out of DBMS_MONITOR tool is also covered well. Craig also gives internals about ASH (Active Session History). How it works why it is good and how to use it. Craig also gives the simple trick about how to learn Oracle internals which is reading Oracle patents.

6- Oracle Buffer Cache Internals(*): Till I read this book, I never dreamed about that, one day I will really fully understand and be able to explain to others, how Oracle buffer cache, shared pool and latches works, despite all official documentation and the books I read so far. This and upcoming 2 chapters gave me this opportunity and I want to thank Craig very much for explaining these topics this well. Diagrams and way he explains are totally amazing. Chapter is not just explaining buffer cache it is also how to tune it. Knowing the internals of Buffer Cache can be the only reason to have the book because it is nearly the main thing for optimal performance. I really like too much about latch and enqueue subtopics.

7- Oracle Shared Pool Internals(*): This is another reason that makes this book unique. I already wrote my own compilation about shared pool in a blog post but this chapter taught me many more things like lathes and mutexes in shared pool , In memory undo and how to tune them. This chapter is also a must to learn to do proper performance firefighting.

8- Oracle Redo Management Internals(*): In this chapter Craig gives how redo management works in Oracle and how to tune redo related issues which is again very important performance related topic. He goes over every redo related problem and gives possible solutions to them. Learning the dangerous commit_write parameter usage was completely new to me.

9- Oracle Performance Analysis(*): In this chapter every topic so far is integrated with each other in a possible problem scenario. Before going into the scenario Craig cover response service and queue time calculations and prepares us to scenario. In this chapter Craig starts an Overall system performance tuning in OraPub three circle analysis by analysing Oracle OS and Application together. He goes into the analysis three times and this gives reader the clues when and where to or not to stop tuning. This chapter totally depends on math and to be honest it is really easy to understands

Now it is time to talk about what I don’t like about the book. Craig introduces and explains too many internal parameters and he gives them as an option in case there is a problem. I loved to learn them because it is very helpful when you talk with Oracle support and also they are part of understanding Oracle but I prefer he mentioned more about not to use them before asking Oracle . He says not to use them couple of times but still not enough to me. Second thing is that we don’t have option to download the test case codes Craig mentions apart from OraPub monitoring kit. Third there is no index at the end of the book.Although I don’t use indexes that much but still nice to have. One last thing because it is only available from OraPub you might wait a bit if you are ordering outside of US like me, I wish there was an e-book option which I always find it easy but probably it is because of avoiding pricy.

After negative part lets come to the conclusion. This book is very informative very well written and in my humble opinion, a must on every DBA’s desk and I suggest it to every DBA. If you are experienced and read too many performance tuning books and feel comfortable with performance tuning but still struggling to understand latches mutexes buffer cache shared pool or overall performance tuning and if you want to fully understand them Oracle Performance Firefighting is totally for you. If you are a new for Oracle Performance Tuning, this book is a must to understand how Oracle works but please be careful about hidden/unsupported parameters.

My only regret is not to order 2 copies one for company and one for me. I hope you will like the book as much as I liked.

Many thank to Craig Shallahamer for bringing deep knowledge and experience about Oracle Performance tuning and sharing in this very special book.