Passed A&A 023

Hi

I passed A&A 023 today! I'll probably be working on B&R next.

thank you very much for all your help.

The Maverick

[Edited by D Maverick on 09-19-2001 at 09:41 AM]

Congratulations! Did you score high?

Congrats Maverick !

Julian,

I got 59/64. Thanks to you guys!!!

40% of the exam is situational 60% is basically objectives. Once you have a good understanding of the components and the command syntax of Oracle (including Initialization Parameters, Dictionary Views and Performance Views) i think your well of.

Julian, what exam are you currently working on?

Thank you very much again.

The Maverick

Thanks Raminder!

Way to go Maverick, you are well on your way.

Quote:

---

I got 59/64. Thanks to you guys!!!

---

Excellent score! Congrats again! It is hard to score over 55 in A&A. You are good.

Quote:

---

Julian, what exam are you currently working on?

---

9i upgrade and Oracle Reports. I am certifying now Oracle Develepment. I used to work with Forms, Reports, PL/SQL before I became a DBA.

How about you?

Julian,

I'm not really good, i just studied n practice hard. experience really counts. i'm administering Oracle database for more than a year now, so i guess i helps me to have a better understanding of the product.

I was a VB programmer and Novell adminstrator before i became an Oracle DBA and NT administrator. I would really like to learn Oracle Forms if i have the time.

Good luck on future exams!!!

Thank you very much again for your help.

The Maverick

Hi Maverick, congrats... which study guide did you use?? how's the questions compared to those in STS package?
What are the situational questions like?? can you us a couple of examples??

Thanks

Quote:

---

I would really like to learn Oracle Forms if i have the time.

---

Oh, it's not really that much fun :-)

NDT,

Thanks! I used the Oracle Student Guide, Sybex and STS.
although Sybex is a good book for A&A (for concepts and illustration) it's not as complete as the Student Guide. it's a good suplement though.

Real exam questions are a bit harder than the STS, but if you understand the concepts behind the questions of STS. you're gonna be Ok.

I'll try to post some questions that i can remenber.

Thanks again.

The Maverick

Here are some Q&A that i can remember. I'm not sure of the answer though. you guys can correct me if im wrong.

What are two reasons for changing user quotas on a tablespace?

A. Datafile becomes full.
B. User encounters slow response time from the application.
C. Tables owned by a user exhibit rapid and unanticipated growth.
D. Database objects are reorganized and placed in different tablespace.

My answer is A and D.

An Oracle user receives the following error:
ORA-01555 SNAPSHOP TOO OLD
What are two possible solutions?

A. Increase the extent size of the rollback segments.
B. Perform media recovery.
C. Increase the number of rollback segments.
D. Increase the size of the rollback segment tablespace.
E. Increase the value of the OPTIMAL storage parameter

My answer is A & E

Consider the following command to create the user Peter?
CREATE USER peter
IDENTIFIED by pan
TEMPORARY TABLESPACE temp
PASSWORD EXPIRE;
Since no default tablespace was specified, what will happen if this command is executed?

A. The user will not own a home directory.
B. User peter will have TEMP tablespace as the default.
C. User peter will have SYSTEM tablespace as the default.
D. The code will produce an error message, the user peter will not be created.

My answer is C

The Maverick

[Edited by D Maverick on 09-20-2001 at 08:59 AM]

THanks for your help Maverick, I'm also preparing for the A&A exam, probably try to write one next week.

One more thing:
=================
Here are some Q&A that i can remember. I'm not sure of the answer though. you guys can correct me if im wrong.

What are two reasons for changing user quotas on a tablespace?

A. Datafile becomes full.
B. User encounters slow response time from the application.
C. Tables owned by a user exhibit rapid and unanticipated growth.
D. Database objects are reorganized and placed in different tablespace.

My answer is A and D.
===================================

Should the answer for the above question be C,D ??
I'm not sure changing the user quota would reduce the size of the datafile at all but correct me if I am wrong. THanks

Hi guys! help us out here, NDT and I are not sure about our answers to this question.

What are two reasons for changing user quotas on a tablespace?

A. Datafile becomes full.
B. User encounters slow response time from the application.
C. Tables owned by a user exhibit rapid and unanticipated growth.
D. Database objects are reorganized and placed in different tablespace.

Thank you very much.

The Maverick

TS_quotas are for creating new objects in the table space not for inserting/updating records in existing table.

Thus (c) cannot be correct. I think its (a) & (d).

[Edited by Raminder on 09-22-2001 at 10:41 PM]

This is one of those tricky A&A questions.

After insert into a table you/user might get:
ORA-1536 "space quota exceeded for tablespace ..."
The error comes because a new extent needs to be created and the user has already reached his quota of space in the tablespace in question. Now, what do you do next: you grant additional quota on the tablespace to the user in question!

From the question it is a bit unclear: is there one or more than one datafile in the TS :-)

An Oracle user receives the following error:
ORA-01555 SNAPSHOP TOO OLD
What are two possible solutions?

A. Increase the extent size of the rollback segments.
B. Perform media recovery.
C. Increase the number of rollback segments.
D. Increase the size of the rollback segment tablespace.
E. Increase the value of the OPTIMAL storage parameter

Personally, I would say, A, C.

According to one of the Study Guides for Oracle 8i A&A Exam that I've read, the main reasons that we may be required to modify a user's qouta are:
1. The users tables start to grow alot
2. an application requires additional tables or indexes
3. if objects are reorganized and moved to different tablespaces

Based on the above circumstances I think C, D are the appropriate answers

Radminder:
I think your accessment is ALMOST accurate:
=============================
TS_quotas are for creating new objects in the table space not for inserting/updating records in existing table.

Thus (c) cannot be correct. I think its (a) & (d).
==============================

but from what I know, if you're the owner of the objects in a tablespace then the sum of all the objects current sizes (not the inital) in that tablespace must be less than or equal to the assigned qouta therefore you may get ORA-1536 error when the table grows beyond the usage quota specified. In my opinion, I think if you can create a small object which eventually grows bigger than the qouta then what's the point of specifying a quota. And I do agree with you that inserts, updates and deletes are controlled by privileges not qoutas. Plse correct me if Im wrong.

( plse don't take this personal, I think it's rude to make a correction since you're certified and I've never written any ocp exams before but this where I can learn from you all. )


[Edited by NDT on 09-24-2001 at 01:51 AM]

Quote:

---

Originally posted by NDT
According to one of the Study Guides for Oracle 8i A&A Exam that I've read, the main reasons that we may be required to modify a user's qouta are:
1. The users tables start to grow alot
2. an application requires additional tables or indexes
3. if objects are reorganized and moved to different tablespaces

Based on the above circumstances I think C, D are the appropriate answers

---

True!

Quote:

---

Originally posted by grjohnson
An Oracle user receives the following error:
ORA-01555 SNAPSHOP TOO OLD
What are two possible solutions?

A. Increase the extent size of the rollback segments.
B. Perform media recovery.
C. Increase the number of rollback segments.
D. Increase the size of the rollback segment tablespace.
E. Increase the value of the OPTIMAL storage parameter

Personally, I would say, A, C.

---

You have to recreate the RBSs in a way that A & E are fulfilled. Also, you could think of making the rBS with anough high minimum number of extents, for example 20. I always craete RBS with 20 initial extents (there is a reason for that :-))

As I said, personally, if I get the SANPSHOT TO OLD ERROR, I check concurrent connections and determine if I have enough rollback segments to handle the number of transactions. One of the main reason for SNAPSHOT TO OLD, is another transaction writting over the undo header in the segment's block thereby causing data inconsistency, especially if another transaction is referenicing this via CONSISTENT GETS.

I.e. This error occurs when a long-running query fails to get rollback needed for a consistent read, becuse it has been overwirtten by another transaction.

Now, if you had more rollback segments, it could help solve this issue. Just as, resizing the extents etc.

I'm sorry guys, but call me stubborn, I'm still sticking with A & C. :)

Cheers,



[Edited by grjohnson on 09-24-2001 at 02:20 AM]

Quote:

---

Originally posted by grjohnson
As I said, personally, if I get the SANPSHOT TO OLD ERROR, I check concurrent connections and determine if I have enough rollback segments to handle the number of transactions. One of the main reason for SNAPSHOT TO OLD, is another transaction writting over the undo header in the segment's block thereby causing data inconsistency, especially if another transaction is referenicing this via CONSISTENT GETS.

I.e. This error occurs when a long-running query fails to get rollback needed for a consistent read, becuse it has been overwirtten by another transaction.

Now, if you had more rollback segments, it could help solve this issue. Just as, resizing the extents etc.

I'm sorry guys, but call me stubborn, I'm still sticking with A & C. :)

Cheers,

[Edited by grjohnson on 09-24-2001 at 02:20 AM]

---

You are right. You should have a good number of RBSs.

The point now is that A, C and E are good answers. However, only 2 were required :-) Why also E: If you commit a change after the current transaction is started, then the rollback space where the information for that transaction is stored may possibly get overwritten by subsequent transactions. It could also be eliminated entirely by a shrink due to OPTIMAL. When Oracle tries to get the information for that row and the RB transaction is no longer existent, you will get ORA-1555.

Julian,

I agree with you that A. C. and E can all be correct. This is just another case of a stupid Oracle Certifiaction question. That's why it is so easy to get confused in an examination question if you really analyze the question, expecially if you can justify more than two answers.

Thanks mate,


Cheers,

It's been quite a discussion, i learn many things from you guys. Thanks a lot for all your feedback and comments. I hope we can have more discussion thread like this (rather than starting a thread to ask for cheet-sheets!)

Thank you very much!

The Maverick

Answer to the 'Snapshot too old' question
is: A & E

Reason:
A long running query in one session that starts before a change in that data is committed by another session
will see data as it existed precommit. If oracle cannot provide a read consistent view of the data, ORA-01555
(Snapshot too old) results.

The solution to the problem is to recreate the roll back segments with larger extents or higer optimal value of extents or higher minimum number of extents. It cannot be solved by increasingthe number of rollback segments.

Hi
I too got passed perfor and tuning. paper

It's very tough i just got passed by god's grace.

Most of the questions i got is from the dumps supplied by
[email protected] and TBT papers.

thanks to all
Bye
issac

Quote:

---

Originally posted by Raminder
Answer to the 'Snapshot too old' question
is: A & E

Reason:
A long running query in one session that starts before a change in that data is committed by another session
will see data as it existed precommit. If oracle cannot provide a read consistent view of the data, ORA-01555
(Snapshot too old) results.

The solution to the problem is to recreate the roll back segments with larger extents or higer optimal value of extents or higher minimum number of extents. It cannot be solved by increasingthe number of rollback segments.

---

True but still you should have enough number of extents if you want to avoid the 1555 error.

it is A & E, if you guys have the ILT (since 99% of questions is based on ILT) you will see that it states three solutions

• A higher MINEXTENTS value, this will avoid new transactions wrap the the extents freed by the large transaction therefore the read consistency wont be lost
• Larger extent sizes, this will avoid the large transaction to extend in several extents therefore the possibility of extents being wraped by other transaction is decreased
• A higher OPTIMAL value, avoid dynamic extent allocation, in other words keep large extents or high number of extents (both of above points)

Higher RBS may and may NOT help, in a busy system it wont help that is for sure. For example you have 10 RBS and they are all active, one with the large transaction (let's call it RBS01) and the others with smaller transactions but higher number of transactions
imagine this:

1. there is a long select going and it uses the before image in RBS01
2. the large transaction commits
3. a new transaction comes in
4. Oracle assigns RBS01 to this new transaction because Oracle 's RBS allocating algorithm is that it assigns the transaction to the RBS with less active transaction
5. the new transaction overwrites the before image of previous large transaction
6. the long select lost the trace of before image, 1555 occurs

In an active/busy system this will happen because of the small transactions (and many) so more RBS does not really help

Also the question is asking of how to avoid overwritting extents in RBS since A & E are related of tuning the number of extents they should be more correct

[Edited by pando on 09-24-2001 at 06:50 PM]

I'm with you one that Julian. Sorry Reminder, but If you have two rollback segments for 20 concurrent users, I don't care what sizing your rollback segments are, you'll get the snapshot to old error. A,C,E. Increasing the number of rollback segments can illiminate this problem. AS WELL as resizing them properly.

"since 99% of questions is based on ILT", still doesn't mean it's 100% correct.

And as you said yourself, "ADDING more may or may not help". I'm not saying its the be all and end all, it's tuning. But, of course the exam is black and white, and basically tests you from the TEXT BOOK, and not real life.

For all Oracle certification exam takers, me included, you are best answering the questions as the ILT and texts suggest and not from anything you've tried in real life situations.

Cheers

[Edited by grjohnson on 09-24-2001 at 06:54 PM]

so let's look this way

adding more RBS may or may not help

but increase OPTIMAL and extent size or higher number of extents DOES help for sure

one is may and the other is sure and we have to choose 2 ;)

Quote:

---

adding more RBS may or may not help

---

That's correct!

Quote:

---

but increase OPTIMAL and extent size or higher number of extents DOES help for sure

---

True. The question is made so, that you have to choose 2 of the 3 remaining options :-)

One has to choose the best answers.....

What a dooozzzey of a question. I hope the Backup and Recovery exams is a little more clearer in what it asks... PHEW!

Hi here are some more question from 023 exam. please correct me if im wrong.

1. Which two parameters might affect the concurrency of transactions on a data block? (choose 2)

A. PCTUSED
B. PCTFREE
C. INITRANS
D. MAXEXTENTS
E. NEXTEXTENT

My answer is B & C

2. What are two effects of setting REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE? (choose 2)

A. Password file can be stored remotely
B. Password file access is restricted to local users
C. Only one instance on the database can use the password file
D. Remote connection using the SYSDBA privileges are allowed
E. Remote connection using the SYSDBA privileges are prohibited

My answer is B & C


Thanks

The Maverick

Quote:

---

Originally posted by grjohnson
What a dooozzzey of a question. I hope the Backup and Recovery exams is a little more clearer in what it asks... PHEW!

---

It is in fact.

1. B & C If you run out of ITL slots in the data block, and processes queue for access to an ITL slot, you might get a dead lock. This is likely to happend if you have INITRANS 1 and PCTFREE 0.

2. D & C With Exclusive REMOTE_LOGIN_PASSWORDFILE any user/password combination in the password file can can log to Oracle remotely and adminster the instance.

Thanks Julian

Just failed the A&A, went in confident and finished the last question with the biggest smile on my face THEN, couldn't believe my eyes I had scored 40/64.. I am still recovering from shock! I had done the Sybex tests and the Sybex Virtual Test Center tests and found one of the Sybex VTs far more difficult but still passed them. Went through several questions on this thread which I've just discovered I ended up answering today and my answers concurr with what you guys choose and of course there was the usual dose of a couple of stupid Oracle Certifiaction questions where more than 1/2 options could be arguably right.

I feel absolutely decimated simply cos "I knew" I'd passed, if you can understand what I mean. If I was unsure of my chances I wouldn't be feeling so bad, the problem is I don't know where I lost the marks and I'm not sure I can do any better, sure I have the section analysis and I can sit here and argue with myself that I definitely picked up more correct answers than what I'm seeing and may have dropped some there but to get 24 wrong it just dosen't match up !! This I felt was an easier exam than the PL/SQL by far....

Well it's a double dose for me A&A and R&R in December, and I really don't know what to say right now but my confidence has just taken a kicking.

Rex

Rex,
I'm sure you will pass it next time you attend. A&A is the trickest of all 5 exams. It has probably the most complicated questions. Did you go for 8 or 8i A&A?

Rex,

Im sorry to hear that. Anyway, i hope you can put the past behind and move on. Its natural to feel angry and sad about it, but dont let failure hinder you from striving even harder.

I know you'll pass it next time. Dont worry! Good luck.