Skip to content
August 26, 2012 / Alex Nedoboi

Free e-book giveaway: “Oracle Database XE 11gR2 Jump Start Guide” (Packt Publishing, 2012)

Last week I reviewed “Oracle Database XE 11gR2 Jump Start Guide” (Packt Publishing, 2012) in this post. Packt guys did like the review, and today I am pleased to announce that I will be giving away two free e-copies of the book.

All you need to do is just comment below the post and win a free copy of Oracle Database XE 11gR2 Jump Start Guide.

As was said in the review last week, the book covers a lot of things, but I would specifically highlight the following chapters that make the book a great guide for those starting with Oracle 11gR2 Express Edition -

  • Develop database applications using Oracle Application Express
  • Back up and restore
  • Performance tuning

(You can read more about this book and download a sample chapter here.)

How to Enter?

All you need to do is go to this page and look through the product description of the book and drop a line via the comments below to let people know what interests you the most about the book.

The contest will close in two weeks, on Sunday, 09/09/2012. Winners will be contacted by email, so be sure to use your real email address when you comment.

Same disclaimer as last week: I have no business or personal relationships with Packt, Oracle, or anyone who worked on the book. I am doing this giveaway thing purely in the interest of the Oracle community.

August 19, 2012 / Alex Nedoboi

Book review: “Oracle Database XE 11gR2 Jump Start Guide” by Asif Momen

About a year ago Packt Publishing approached me and offered to write a book on (one of the technologies in) Oracle 11g. After careful consideration I had to decline the offer. Which was the right decision as I have hardly had any spare time in the past 12 months.

However, Packt guys have kept in touch, and last month they asked me to review a just released book called “Oracle Database XE 11gR2 Jump Start Guide” by Asif Momen which I’m happily doing in this post.

Before we get into much detail, let me say that overall the book is good. The selection of topics is excellent and it is obvious since the very beginning of the book that the author is an experienced Oracle professional who has great understanding of what is needed to efficiently start (and continue) with Oracle database.

There are just a couple of things that I would have done differently. So let’s start with critique first and then move on to praising.

Cons

Considering the audience of the book, some of the chapters are either too technical or too brief. In other words, fail to explain the concepts in plain English and/or in enough detail.

For example, when introducing the listener concept, the book simply says that you need a listener to establish a connection to the database, and then the listener takes no part in your further dealings within the established connection. Which is absolutely correct. However, let’s imagine a reader who has never dealt with Oracle (or any other database) before and now this concept is just dumped on him without much explanation.

The way I would have wanted the book to explain such fundamental concepts, is to use plain language and abstraction from the database world. I believe that it is crucially important to get the fundamentals right inside the reader’s head from the start so that they don’t haunt the reader in the future.

So when introducing the listener concept, one could compare it to the real life situation of going to a restaurant. When you get there, at the door you are greeted by the host who walks you to your table and then you only deal with the waiter. Same with the listener, it greats you at the door, walks you to a database connections (or refuses to let you in if there are no connections available), and leaves you to deal with the database directly.

In the paragraph about synonyms, it is not very clear what synonyms are actually for. Imagine that there is a guy called Jack that lives on your street, and everyone knows him and everyone calls him Jack. So the paragraph about synonyms says – let’s call him Bob. What for?

“Do you know what Bob did?” – “Which Bob?” – “That guy.” – “But that’s Jack.” – “Well, I also call him Bob.”

Instead, if there is a funny looking guy that lives five streets away and no one knows him name, you and your neighbours can call him Bob for your convenience. So instead of saying “Do you know what that funny looking fella that lives five streets away don’t-know-his-name did?”, you could say “Do you know what Bob did?” (This is not the only thing you can use synonyms for, of course.)

You can see the author use similar approach to explain what an index is, making a classic comparison to index pages of a book. I believe the book would have benefited by having more of that.

Second thing. In the beginning of this review, I said that it’s obvious that the author is an experienced Oracle professional. That’s not a bad thing. However, with experience come old habits, and sometimes they are hard to get rid of, that’s true of many people, myself included. Not very often throughout the book, but sometimes this pre-11g mindset does surface.

One last thing, to finish this section. The wording of some statements. For example, “DML statements will become expensive if we have too many indexes, and if we have too few indexes, then queries become expensive.” While generally speaking the first part is almost always true, and the second part is less-almost but still-almost always true, I would change the wording to reflect the fact that sometimes practice doesn’t agree with theory. So instead of “will become expensive” I would say “DML statements may and often do become more expensive”.

Pros

Selection of topics is great. The book manages to cover the fundamentals, as well as provide insight on many things one is likely to be exposed to in a database environment.

Installation, configuration, queries, tables, indexes, view, functions, triggers. All these things you would expect to be discussed in all (or at least most) books for beginners, and they are in this book.

Storage, memory structures, background processes, moving data (import/export), edition upgrades. Some of these things you would expect in some books for beginners, and they all are in this book.

And on top of all those things this book gives you more.

The book surprised me by mentioning APEX, Oracle’s RAD. APEX, included in every version of Oracle, is free, and is an excellent tool. While there are books, three or four times size of this book, exclusively covering APEX development, this book does a big favour to those starting with Oracle database by introducing the reader to APEX, the tool that can solve much more tasks than an average person would think, much more efficiently than an average person would imagine.

Most importantly the book has two excellent chapters on backup and recovery and performance tuning. People starting with a database don’t normally pay much attention to these two topics. This could probably be explained by the fact that the first steps deal with tiny databases that require no performance tuning and no one cares if the whole database is lost.

When databases grow, start containing lots of data, and when the data becomes business critical, only then people start running in circles screaming why all of the sudden the database is slow, or what do you mean you don’t have a single backup anywhere.

Not only the book includes these two chapters, they are by far the best two chapters in the book. They are detailed, easy to grasp, and do have great examples including recovery from a complete database loss.

Conclusion

Overall, the book is good. It is fast paced and it does cover a lot. It does focus on important things.

There are some good references at the end of each chapter, sometimes they just refer to Oracle Database Documentation, but sometimes they are fantastic, especially in the performance tuning chapter.

The book could have been better balanced and more beginner-friendly. If you’re an absolute beginner, you may find yourself struggling to understand some concepts.

If you do have some basic understanding already and aren’t afraid of googling and doing further research outside the scope of this book, then the book is a solid guide and should get you started just fine.

 

This article is purely my own opinion not influenced by anyone. I have no business or personal relationships with Packt, Oracle, or any people who worked on the book.

November 12, 2011 / Alex Nedoboi

Tiny Tips: SSD and Linux. Enable TRIM and check if it works.

What is TRIM?

On hard disks, with spinning magnetic platters, you don’t care if a particular sector is being used by the operating system or not. If you want to use it again, just ask the controller to write into it, and the controller will just overwrite whatever was in that sector.

With SSD, if the sector already contains some information and you want to write into it, the old data needs to be deleted (zeroed out) first, and then the new data written. Zeroing out the old data takes considerable time, so it would make sense if we could tell the controller that the sector is no longer being used, allowing the controller to zero out the sector data in the background, and then when we need to use the sector again, it will be ready to be written into immediately, saving us a lot of time.

This is what the TRIM command does, it tells the controller that the sector is no longer used and can be zeroed out.

For the TRIM command to work, we need ext4 and the kernel to be 2.6.33 or higher. In this example we use Fedora 15.

Install Fedora 15 x86_64

If you don’t have it installed yet, or want to make sure that it’s installed correctly for the TRIM command to work, here is a very brief guide.

Installing Fedora is very much a matter of clicking the Next button.

The only important step where you would not click the Next button straight away, is the storage step.

Select the Create Custom Layout option. The reason we are not going with the default here is because we can’t use LVM with SSD.

Create two standard partitions, one swap and one ext4 (mounted as /). Don’t use ext2 or ext3 as they don’t support SSD either, particularly the TRIM command.

Enable TRIM

In default Fedora installation TRIM is not enabled by default. So let’s enable it.

As root, edit the /etc/fstab file and add the “discard” option to the root line.

UUID=... / ext4 discard 1 1

Restard the machine. Check if / has been mounted with “discard”.

[root@f15] mount
...
/dev/sda1 on / type ext4 (discard,rw)
...

Looks good. It is now time to test the TRIM functionality.

Check if TRIM works

We are going to create a file, check the sector data, then delete the file, and see if the sector data has been zeroed out.

We will be using the hdparm tool, so the following steps need to be done as root.

First, create a file.

[root@f15] for i in {10001..10200}; do echo "$i test line" >> testfile.txt; done

This will create a file with 200 lines, about 4k in size. Now make sure it is flushed out to the disk.

[root@f15] sync

Now let’s find out where the file is physically stored on the disk.

[root@f15] hdparm --fibmap testfile.txt

filesystem blocksize 4096, begins at LBA 0; assuming 512 byte sectors.
byte_offset  begin_LBA    end_LBA    sectors
          0   14927624   14927631          8

You can see the file starts at sector 14927624 and spans 8 sectors. Let’s check what’s in there.

[root@f15] hdparm --read-sector 14927624 /dev/sda

3031 3030 2031 6574 7473 6c20 6e69 0a65
3031 3030 2032 6574 7473 6c20 6e69 0a65
3031 3030 2033 6574 7473 6c20 6e69 0a65
...
3031 3330 2032 6574 7473 6c20 6e69 0a65

That’s our sector. “3031 3030 2031 6574 7473 6c20 6e69 0a65″ is a hex representation of “10000 test line”.

Now let’s delete the file. Make sure the change is flushed to the disk.

[root@f15] rm testfile.txt
[root@f15] sync

We are now expecting the sector to contain nothing but zeroes.

[root@f15] hdparm --read-sector 14927624 /dev/sda

0000 0000 0000 0000 0000 0000 0000 0000
0000 0000 0000 0000 0000 0000 0000 0000
0000 0000 0000 0000 0000 0000 0000 0000
...
0000 0000 0000 0000 0000 0000 0000 0000

Perfect, the TRIM command works in Fedora 15.

Recent releases of most popular distributions (Ubuntu, Mint, Sabayon, etc) should also be fine.

September 8, 2011 / Alex Nedoboi

Install Oracle Express XE 11gR2 on Oracle Linux 6.1 x64. (No bitmap index conversion on the fly!)

Some time ago, if you said you had installed Linux and, heavens forbid, Oracle on Linux, you’d be considered an utter geek.

Fast forward 15 year. These days, when people (who have experience with other databases and operating systems) ask me about Oracle/Linux, I feel somewhat embarrassed.

Let me illustrate what I mean.

Install Oracle Linux 6

After downloading it from edelivery.oracle.com/linux (version 6.1 x86_64 in this example), burn the .iso (or just use the .iso itself in case of virtual machine), and power on the box.

Wait a minute while it boot ups. Skip the image check. Once you see the Next button in the bottom right corner, click it.

Click Next again.

Click Next again.

Click Next again.

Click Next again.

Click Yes (to discard the data on the disk).

Name your machine. For example, o61.localdomain, then configure the network if you want to. Using DHCP might seem trendy, but if you prefer knowing the IP, set it here.

Click Next. (Select your location if you want to, or pretend you’re in New York.)

Click Next.

Click Next. (Partition the disk(s) if you want to. You can keep the default layout with LVM, or if you see no point, just two partitions should do – root/ext4 and swap.)

Click Next again.

Click Next again. (No need to tick “Database Server”, the Basic one will do just fine.)

Click Next, this is the last time. Watch Linux install or go put a kettle on.

Done. Yep, done, just 15 or so minutes.

So, the strategy. If you see Next, and are happy with the default setting, click it.

Install Oracle XE 11g R2

At this point you might think – installing Linux was way too easy, you can’t beat that.

Well, let’s see if installing Oracle is even easier.

Download the .zip from http://www.oracle.com, unzip it, and install it -

[root@o61 Disk1] rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

Then configure it -

[root@o61 Disk1] /etc/init.d/oracle-xe configure

Wow, that was tricky, clicking Enter five times (except the password step).

All done. In less than half hour.

Time to play

Allow connections from outside the machine, as SYSTEM run -

SQL> exec dbms_xdb.setListenerLocalAccess(false)

Also, make sure the firewall (iptables) allows connections on 1521 and 8080, or disable iptables altogether.

Let’s check the management page, go to http://server_address:8080/apex (log in as SYSTEM and password from the “oracle-xe configure” step).

Looks nice.

xe-1

Click the Storage tab. You can see SYSAUX/UNDO/TEMP are (kind of) unlimited, and USERS is limited to 11G (suppose Oracle intended the pun).

xe-2

What optimizer parameters do we get in 11gR2 XE?

xe-3

No bitmap indexes on the fly

Let’s have some fun.

SQL> create table people (id number(10), first_name varchar2(100), last_name varchar2(100));

Table created.

SQL> insert into people 
  2  select level, chr(ascii('A') + mod(level*11,13)), chr(ascii('A') + mod(level*19,23))
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> create index i_first on people(first_name);

Index created.

SQL> create index i_last on people(last_name);

Index created.

And then -

SQL> set autotrace on

SQL> set linesize 120

SQL> select count(1) from people where first_name = 'J' and last_name = 'S';

  COUNT(1)
----------
        33

Execution Plan
----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |     4 |      4(25) | 00:00:01 |
|   1 |  SORT AGGREGATE     |                  |     1 |     4 |            |          |
|*  2 |   VIEW              | index$_join$_001 |    33 |   132 |      4(25) | 00:00:01 |
|*  3 |    HASH JOIN        |                  |       |       |            |          |
|*  4 |     INDEX RANGE SCAN| I_LAST           |    33 |   132 |      1 (0) | 00:00:01 |
|*  5 |     INDEX RANGE SCAN| I_FIRST          |    33 |   132 |      2 (0) | 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LAST_NAME"='S' AND "FIRST_NAME"='J')
   3 - access(ROWID=ROWID)
   4 - access("LAST_NAME"='S')
   5 - access("FIRST_NAME"='J')

Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
 21  consistent gets
  0  physical reads
  0  redo size
526  bytes sent via SQL*Net to client
519  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

SQL>

What do we see here, on Express Edition?

Hash join (range scan, range scan), and 21 consistent gets.

What would we see on Enterprise Edition?

------------------------------------------------------
| Id  | Operation                         | Name     |
------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |
|   1 |  SORT AGGREGATE                   |          |
|   2 |   BITMAP CONVERSION COUNT         |          |
|   3 |    BITMAP AND                     |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS |          |
|*  5 |      INDEX RANGE SCAN             | I_LAST   |
|   6 |     BITMAP CONVERSION FROM ROWIDS |          |
|*  7 |      INDEX RANGE SCAN             | I_FIRST  |
------------------------------------------------------

Bitmap “and” (bitmap conversion(range scan), bitmap conversion(range scan)). And 5 gets.

Yes, this bit of functionality didn’t make it into XE.

But when you have free Oracle 11gR2 on free Linux, all installed and running in about 30 minutes, can you complain?

September 5, 2011 / Alex Nedoboi

Tiny tips: Use Gateways to connect Oracle database to Microsoft SQL Server

When you have two Oracle databases and want to exchange data between them, it is easily done via database links.

So for your local database you would go select * from local_table and for the remote one select * from remote_table@link_to_remote_db

But what if the remote database isn’t Oracle, what if it’s (for example) Microsoft SQL Server?

Well, almost no difference. You can create a database link to MSSQL just as easily.

For this, we will use Oracle Gateways – a piece of software that translates Oracle calls to MSSQL and vice versa.

Install Gateways

First we need to install it of course. You can download Oracle Gateways from Oracle website. As usual, unzip it and execute runInstaller.sh

The installation process is mostly a matter of clicking the Next button. At the last step you will be asked the details of your MSSQL Server.

In this scenario, our MSSQL Server is at machine called vb-m8r2, instance name INSTNAME, and database name DBNAME. Port 1433, default.

For testing purposes, I created a table called PEOPLE (id, first_name, last_name), and inserted two lines into it.

MSSQL 1

After Gateways installation is complete, check the config file – <gateways_home>/dg4msql/admin/initdg4msql.ora

It should have the line HS_FDS_CONNECT_INFO=vb-m8r2:1433//DBNAME, to avoid confusion, change it to …=vb-m8r2/INSTNAME/DBNAME

Make sure you remember the underlined bit – dg4msql – this is the SID we will be using for the listener.

Configure the gateway

Assuming your lister is called LISTENER, add the following entry to listener.ora

SID_LIST_LISTENER =
  (
    SID_LIST =
      (SID_DESC =
        (SID_NAME = dg4msql)
        (ORACLE_HOME = <gateways_home>)
        (ENVS = LD_LIBRARY_PATH=<gateways_home>/dg4msql/driver/lib:<gateways_home>/lib)
        (PROGRAM = <gateways_home>/bin/dg4msql)
      )
  )

Restart the listener.

If you specified the paths incorrectly, you will get an error – TNS-01201: Listener cannot find executable.

Otherwise, you should see the following lines -

Services Summary...
Service "dg4msql" has 1 instance(s).
  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Congratulations – you have configured the gateway.

Configure the database

First, add the following to tnsnames.ora

dg4msql =
(
  DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (CONNECT_DATA = (SID = dg4msql))
    (HS = OK)
)

Try to tnsping it.

Second, create the database link.

create public database link micro 
   connect to "ms_username" 
   identified by "ms_password" 
   using 'dg4msql'
;

Test it

Eventually, time for testing.

SQL> select * from people@micro;

id first_name last_name
-- ---------- ---------
 1 John       Smith
 2 Jean       Cook

The other way around -

SQL> insert into people@micro values (3,'A','B');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from people@micro;

id first_name last_name
-- ---------- ---------
 1 John       Smith
 2 Jean       Cook
 3 A          B

Now check on the MSSQL side -

MSSQL 2

Troubleshooting

If instead of this you’re getting an error, something like -

ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MICRO

It means you’ve misconfigured something, make sure there are no typos in initdg4msql.ora, listener.ora, tnsnames.ora.

Then make sure you can telnet from your Oracle box to vb-m8r2 on 1433, the listener starts, and you can tsnping dg4msql.

May 29, 2011 / Alex Nedoboi

1Z1-058 (1Z0-058 beta) 11g R2 RAC Expert. Answers.

Last week’s article on 1Z0-058 questions was quite popular, more popular than I expected.

So if you enjoyed the questions, now it’s time to get some answers!

(1)
Kevin is a database administrator at Fictional Real Estate Co. All he hears everyday is location, location, location. He is asked by one of the Sales team members about Oracle Real Application Clusters. What should he reply to his colleague’s “what is it all about”?

If a painter can paint a wall in an hour, two painters are likely to finish the job in 30 minutes, and it would probably take three painters about 20 minutes. So, in theory, the performance does increase. However, clusters are not about performance, the performance of every individual node doesn’t change just because it’s part of a cluster. As a matter of fact, performance can decrease. Imagine that the painters in the above example have to share their tools. They will have to wait for each other, and even incur some overheads handing the tools over.

Same with RAC, if there is a sequence or an index block contention, the whole cluster is no better (and sometimes even worse) than a single node. That’s why cluster-specific performance tuning is very important in a RAC environment.

Redundancy. Every single part in RAC should not be a single point of failure. The disks are mirrored, the storage and the machines have two (or more) interfaces between them, virtual IPs, SCANs, bonded network interfaces, etc.

Some time ago I witnessed a rather spectacular failure of an Oracle cluster. Due to some environmental factors (no, not global warming), one of the disks failed. Not a big deal, it was mirrored. The controller started to rebuild the failed disk, and also failed under the load (don’t forget the environmental factor). Not a big deal either, there were two controllers. Unfortunately, the failed controller managed to ship corrupted data to one of the nodes, so the node decided to shut down. Again, not a big deal, there was another node.

After all those events, the cluster still was fully functional, with just one disk, one controller and one node left. All thanks to redundancy.

But what if the entire cluster fails? Read on!

Availability. Real Application Clusters are one of the many High Availability (HA) solutions offered by Oracle. Together with replication (whether it’s Data Guard, or Streams, or Golden Gate) they form Maximum Availability Architecture (MAA). This means that even if the entire cluster fails, there is another cluster, with an exact copy of the data, replicated by Data Guard or a similar solution.

Scalability is another big thing about clusters. Let’s go back to our painters. If we want to paint more walls, we can just use more painters. Provided they don’t share tools, the job will be done faster. With just one painter (single instance database) all you could do is train the guy to work faster (upgrade server hardware) but there’s always a limit to that (not to mention that just marginally faster hardware can cost double).

Clusters waste no resources. All those redundant parts are being constantly used, they don’t wait for the other part to fail. If you have three painters, all of them will be doing the job, not sitting idle waiting for their fellow painters to get a cold.

By the way, the painters can share the paint, which in the RAC world is represented by shared data storage.

(2)
Jenson is a database developer at Fictional Cluster Masters Co. He wants to pull a practical joke on the company’s DBA, so the DBA would think someone is performing a DDoS attack on the cluster. What should Jenson do in his application?

As mentioned above, eliminating all possible contention points is a must in RAC environments. A shared sequence with no cache is a massive contention point. If you wonder how I came up with this question, well, I didn’t. It is based on my experience, as I saw a RAC brought down to its knees by hundreds of sessions queuing up for a sequence.

(3)
May is a junior DBA at Fictional Calendars Co. She wants to start some RAC instances and stop some other RAC instances. What tools should she use from the list below?

Some people think that you can’t use the old friend of yours, sqlplus, to start up or shutdown instances. Not true. The only difference is that you can only start up or shut down the instance you’re connected to, not the whole cluster. For the cluster wide operations, use srvctl. No cluster-specific parameters (mode=rac, -rac, foo=bar) needed.

Nothing wrong with graphical tools either, as long as you know how to do things when graphical tools are not available.

(4)
April is a junior DBA at Fictional Calendars Co. She asks the company’s senior DBA August what the number of UNDO and TEMP tablespaces and REDO log groups should be in a RAC environment. If August knows what he’s talking about, what will he answer?

There is no difference for TEMP in a RAC environment. You can have just one, or more than one for different users if you wish.

UNDO, on the other hand, is not shared, so every instance has its own UNDO.

REDO is also owned by every individual instance and it’s recommended that you have at least two groups per instance.

(5)
Mark is a database administrator at Fictional Storage Specialists Co. He has built a 10g R2 RAC on Linux 4 before, using raw devices for voting disk and cluster repository storage, and ASM for data files. He now wants to build an 11g R2 RAC on Linux 5. What are his options when it comes to storage?

In 10g you would need to set up raw devices or use OCFS (Oracle Clustered File System). In Linux 5 raw devices were phased out (though could still be used), but with 11g R1 you could use block devices to store voting disk and OCR, so no need for raw.

Until 11g R2 RAC, you couldn’t use ASM for voting disk or OCR file because of the catch 22. The clusterware needed voting/OCR to start up, but ASM only started after the clusterware.

In 11g R2 RAC however, the ASM bit and the clusterware are now parts of a bigger beast, called Grid Infrastructure, all-in-one so to speak. You can now place everything on ASM, just tick “Use ASM” during installation.

Alternatively, you can place everything on NFS, as long as all the nodes have access to the share and the mount is the same.

(6)
Matthew is a system administrator at Fictional Storage Specialists Co. He is going to install Linux OS as part of task to build an 11g R2 RAC. What packages and/or libraries he may need, depending on the storage options?

To use ASM, you would need three RPMs installed before starting GI installer. They are oracleasmlib, oracleasm-support, oracleasm. Some of them can be found on the installation media if you use Oracle Linux, and some are online. If you use RHEL or CentOS or any other non-Oracle flavour, just download all three for the respective kernel.

NFS doesn’t need any specific packages, just mount the shares on all nodes before GI installation. Make sure that the paths are the same on every node, and that the operating system user and group IDs for the “oracle” user and the “oinstall” groups are the same on all machines and storage devices.

Optionally you can use Oracle Direct NFS, this is recommended for performance reasons. The library is called libodm11.so and it’s only a matter of creating a symbolic link and restarting the instance. The performance is gained by bypassing the operating system cache which releases the memory and speeds up the I/O.

(7)
Dorothy has more than 20 year experience administering single instance Oracle databases. She has developed an extensive library of shell scripts over all those years. She now wants to modify the scripts to work in an Oracle RAC environment. What views/columns would she use to determine instance IDs?

This one is trivial. The V$ views are still there, but they only reflect the instance you’re connected to. For cluster wide data use GV$, for example GV$SGA_RESIZE_OPS, and the instance ID will be in the inst_id column.

By the way, the GV$ views are also available in single instance databases. So Dorothy wouldn’t need two sets of scripts, she should be able to use the modified versions on both single instance and RAC databases.

(8)
Tom is a network administrator and Dick is a system administrator at Fictional Links And Nets Co. They are working with the company’s DBA Harry building an 11g R2 RAC environment. They are reasonably confused about the number of network interfaces the DBA needs for the cluster, and ask for an explanation. What should Harry tell the pair of them?

If one of the painters needs a brush that is being used by another painter, they would exchange the brush directly, hand to hand, without the need of putting the brush back into the toolbox and picking it up from the toolbox. This is called toolbox cache fusion. If an instance needs some data block that is used by another instance, it will be shipped via the private interconnect.

(9)
Lily and Daisy are junior DBAs at Fictional Bad Blocks Co. They have heard that their senior DBA Ash had performed an RMAN recovery in a 11g R2 RAC environment. They are very keen to learn if there are any differences to single instance recovery. If Ash knows what he’s talking about, what will he tell them?

Using RMAN isn’t much different in a RAC environment. Recovery catalogs are optional. No need to copy the backups/logs to the local disks.

Only the node that you’re running RMAN on needs the access, the other node(s) can be down for all you know.

(10)
John is a database administrator at Fictional Just On Time Co. He needs to schedule various jobs against the company’s 11g R2 RAC database, and some of those jobs must run on the first node only to meet a legacy application’s requirements. What are the options?

This one is even more trivial. Perhaps it doesn’t need any explanation at all.

The reason for having this question was to actually show that in a RAC environment there is no guarantee which node will run the job.

If you want to make sure your job runs on a particular node, set its affinity, or connect specifically to that node. Just note that if the node is down, the job will not run.

(11)
Mario is a database administrator at Fictional Dash Nine Co. He needs to terminate a user’s session and issues an ALTER SYSTEM KILL SESSION ’123,456′ command, but receives an error that the session doesn’t exist. He realizes that he is connected to the second node and the session is running on the third node of the cluster. What is the easiest way of achieving the desired outcome?

Hey, session, where a-a-a-are you-u-u-u? I am at three-e-e-e-e.

Connecting to the third node would also work. If you can entertain yourself by jumping from node to node, why not. All you need is create another entry in your tnsnames.ora, adding the following line

...
(INSTANCE_NAME = foobar3)
...

right after

...
(SERVICE_NAME = foobar.domain.wherever)
...

(12)
Summer is a database administrator at Fictional Patience Co. The company has several 11g R2 RAC databases, some are one-node, some are two-node, some three-node, and some four-node. Summer is going to review cluster-specific waiting events for each database. Which of the following statements are true?

When painter X needs a brush, he says – hey, give me that brush. That’s the first step. Painter Y then gives him the brush. The interaction was two-step, wasn’t it. In Oracle RAC world, it is called two-way brush block request.

Now painter Z asks painter Y. Painter Y doesn’t have the brush any more, but tells painter X to give the brush to Z. And that was three way.

Depending on how often every painter uses the brush, they would carry the Brush Master title. If someone needs the brush, they would ask the Brush Master, and if the Brush Master doesn’t have the brush at the time of the request, he would ask to hand it over to whoever requested it on their behalf.

Painters? Really?

Let me know if you think you’ve had enough of those painters. Tell me, we all are IT professionals, “cache fusion” sounds so much better than “toolbox with paintbrushes”. Or not?

Please note that all these questions are in no way an indication of the actual exam questions. Names and company names are fictional. This line is italic. Some things may or may not be obvious.

May 23, 2011 / Alex Nedoboi

1Z1-058 (1Z0-058 beta) 11g R2 RAC Administration Expert

If you get emails from Oracle University, you might have received a notification that 1Z1-058 (1Z0-058 beta) “Oracle 11g R2 RAC Administration” exam is now available. It earns you an 11g R2 RAC Oracle Certified Expert (OCE), but you need to be an 11g DBA Oracle Certified Professional (OCP) first to get that.

If you looked at my Bio page, you probably saw all the certifications I hold. While being certified is nice, I must admit that I enjoy the process more than the end result. The best thing about Oracle exams is that you -

a. get to know what Oracle expects of you to know to be considered an expert in that particular area, and
b. get to learn things when you study for the exam. Yes, I believe that even if you have a solid experience, there is always something to learn.

I started using Oracle RAC from version 10g R2 (around 2006 IIRC) and have never looked back. However, most of the fellow DBAs I know didn’t use Oracle clusters, one reason or another.

A couple of years ago we (the company I’m consulting) started our move to 11g, and a few months ago the move was completed and we are now on 11g R2 RAC.

11g R2 RAC is brilliant!

And as for my fellow DBAs, I am now seeing a significant change in their attitudes towards Oracle Real Application Clusters. Some of them are starting to realize all the benefits of RAC, and at the same time 11g R2 (“R2” being key here) is a substantial move forward, making it very easy to switch to RAC and focus on what’s really important.

Therefore, I believe the new 1Z0-058 exam should be very popular when more and more people and companies switch from single instances to RAC environments.

The exam consists of two parts, similar to the 1Z0-048 exam on 10g R2 RAC, namely the clusterware administration part, and RAC database administration.

One of the main differences to the previous version is that the clusterware part is now Grid Infrastructure (GI), and since GI now includes ASM, the new exam pays significantly more attention to ASM. Ironically, an 11g R2 RAC can be built on NFS (very easy and cost efficient) without any need for ASM. Of course ASM should be used in production environment as it provides countless benefits (technically speaking they can be counted) over NFS.

Anyway, let’s get to the point. If you are interested in switching from single instance to RAC, or if you have already been working with Oracle RAC for some time but do share my beliefs that exams are a great reference for studying and filling the gaps in your knowledge (however small those gaps can be), below are ten questions that should get you going.

Questions

(1)
Kevin is a database administrator at Fictional Real Estate Co. All he hears everyday is location, location, location. He is asked by one of the Sales team members about Oracle Real Application Clusters. What should he reply to his colleague’s “what is it all about”?

A. Performance, performance, performance.
B. Availability, availability, availability.
C. Redundancy, redundancy, redundancy.
D. Scalability, scalability, scalability.
E. Efficient utilization of all available resources.

(2)
Jenson is a database developer at Fictional Cluster Masters Co. He wants to pull a practical joke on the company’s DBA, so the DBA would think someone is performing a DDoS attack on the cluster. What should Jenson do in his application?

A. Only use physical IP addresses to connect to the database.
B. Only use virtual IP addresses, but not SCAN, to connect to the database.
C. Use as many sequences as possible (ideally a dedicated sequence for every ID field), with large caches.
D. Use as few sequences as possible (ideally just one sequence shared by all ID fields), with no cache.

(3)
May is a junior DBA at Fictional Calendars Co. She wants to start some RAC instances and stop some other RAC instances. What tools should she use from the list below?

A. sqlplus
B. sqlplus –mode=rac
C. srvctl
D. srvctl -rac
E. Grid Control (if available) or Enterprise Manager
F. Grid Control (if available) but not Enterprise Manager

(4)
April is a junior DBA at Fictional Calendars Co. She asks the company’s senior DBA August what the number of UNDO and TEMP tablespaces and REDO log groups should be in a RAC environment. If August knows what he’s talking about, what will he answer?

A. 1 UNDO per instance, 1 TEMP per instance, 1 REDO per instance
B. 1 UNDO per cluster, 1 TEMP per cluster, 1 REDO per cluster
C. 2 or more UNDO per instance, 2 or more TEMP per cluster, 2 or more REDO per instance
D. 1 or more UNDO per instance, 1 or more TEMP per instance, 1 or more REDO per cluster
E. 1 UNDO per instance, 1 or more TEMP per cluster, 2 or more REDO per instance
F. 2 or more UNDO per cluster, 2 or more TEMP per cluster, 1 or more REDO per instance

(5)
Mark is a database administrator at Fictional Storage Specialists Co. He has built a 10g R2 RAC on Linux 4 before, using raw devices for voting disk and cluster repository storage, and ASM for data files. He now wants to build an 11g R2 RAC on Linux 5. What are his options when it comes to storage?

A. Raw devices for voting/OCR, ASM for data
B. Block devices for voting/OCR, NFS for data
C. OCFS2 for voting/OCR, NFS for data
D. NFS for voting/OCR, ASM for data
E. ASM for everything
F. NFS for everything

(6)
Matthew is a system administrator at Fictional Storage Specialists Co. He is going to install Linux OS as part of task to build an 11g R2 RAC. What packages and/or libraries he may need, depending on the storage options?

A. oracleasmlib, oracleasm-support, oracleasm for ASM
B. oraclenfslib, oraclenfs-support, oraclenfs for NFS
C. libasm11, libaio, sysstat for ASM
D. libnfs11, libaio, sysstat for NFS
E. libodm11 for ASM (optional)
F. libodm11 for NFS (optional)

(7)
Dorothy has more than 20 year experience administering single instance Oracle databases. She has developed an extensive library of shell scripts over all those years. She now wants to modify the scripts to work in an Oracle RAC environment. What views/columns would she use to determine instance IDs?

A. instance_id in V$ views
B. instance_id in V_$ views
C. inst_id in V$ views
D. instance_id in GV$ views
E. inst_id in V_$ views
F. inst_id in GV$ views

(8)
Tom is a network administrator and Dick is a system administrator at Fictional Links And Nets Co. They are working with the company’s DBA Harry building an 11g R2 RAC environment. They are reasonably confused about the number of network interfaces the DBA needs for the cluster, and ask for an explanation. What should Harry tell the pair of them?

A. Public interfaces are for client connections, and private interfaces are for administrator connections.
B. Public interfaces are for physical IPs, and private interfaces are for virtual IPs and SCAN.
C. Public interfaces are for client connections, and private interfaces are for cache fusion.
D. Public interfaces are for cluster-wide load balanced requests, and private interfaces are for instance-specific requests.

(9)
Lily and Daisy are junior DBAs at Fictional Bad Blocks Co. They have heard that their senior DBA Ash had performed an RMAN recovery in a 11g R2 RAC environment. They are very keen to learn if there are any differences to single instance recovery. If Ash knows what he’s talking about, what will he tell them?

A. Recovery catalogs must be used in RAC environments.
B. Recovery catalogs can but don’t have to be used in RAC environments.
C. Only the node that performs the recovery must have access to all necessary backups and archive logs.
D. All nodes must have access to all necessary backups and archive logs.
E. All the necessary backups and archive logs must be copied to the local disk(s) of the node that performs the recovery.
F. All the necessary backups and archive logs may reside either on the local disk(s) of the node that performs the recovery or in the Fast Recovery Area or in an NFS directory, as long as the node has access.

(10)
John is a database administrator at Fictional Just On Time Co. He needs to schedule various jobs against the company’s 11g R2 RAC database, and some of those jobs must run on the first node only to meet a legacy application’s requirements. What are the options?

A. Set job instance affinity with DBMS_SCHEDULER.
B. Set job node affinity with DBMS_JOB.
C. Use operating system’s commands to run legacy jobs on the first node only, connecting as “/ as sysdba”.
D. Use operating system’s commands to run legacy jobs on any node, connecting to the instance running on the first node.

Actually, let’s make it a dozen

(11)
Mario is a database administrator at Fictional Dash Nine Co. He needs to terminate a user’s session and issues an ALTER SYSTEM KILL SESSION ’123,456′ command, but receives an error that the session doesn’t exist. He realizes that he is connected to the second node and the session is running on the third node of the cluster. What is the easiest way of achieving the desired outcome?

A. Connect to the third node and re-issue the command.
B. Issue ALTER SYSTEM KILL SESSION ’123,456′ AUTO;
C. Stay on the second node and modify the parameters to ’123,456,3′
D. Stay on the second node and modify the parameters to ’123,456,@3′
E. Stay on the second node and modify the parameters to ’123,456,#3′

(12)
Summer is a database administrator at Fictional Patience Co. The company has several 11g R2 RAC databases, some are one-node, some are two-node, some three-node, and some four-node. Summer is going to review cluster-specific waiting events for each database. Which of the following statements are true?

A. One-way block request waits normally occur on one-node clusters.
B. One-way block request waits normally occur on one-or-more-node clusters.
C. Two-way block request waits normally occur on two-node clusters.
D. Two-way block request waits normally occur on two-or-more-node clusters.
E. Three-way block request waits normally occur on three-node clusters.
F. Three-way block request waits normally occur on three-or-more-node clusters.
G. Four-way block request waits normally occur on four-node clusters.
H. Four-way block request waits normally occur on four-or-more-node clusters.

Please note that all these questions are in no way an indication of the actual exam questions. Names and company names are fictional. This line is italic. Some things may or may not be obvious.

Follow

Get every new post delivered to your Inbox.