Category: 11g

Last Friday I went to Munich for THE upgrade exam. I have a few recommendations based on my preparation.

DISCLAIMER: no recommendation is based on the exam

Check you have a similar environment. You will get 11gR1 database and 10g entreprise manager. Personnaly I did my preparation on 11gR2 and 11g/12c em, I did not find useful to prepare on some oldish version. You should have some Linux / Unix around. It does not really matter if you use AIX, HPUX, Solaris or Linux.

I bought myself a keyboard with German Layout (the one with ß, Ä and Ö) to gain speed

Read each topic carefully and get proficiency:
– Database:
I know the meaning of the initialisation parameters. In need, I can quickly check one in the Reference
I am familiar with the sql syntax and have read the SQL Reference more than once

– Network configuration :
I know how to configure sqlnet.ora, tnsnames.ora and listener.ora without looking at the documentation.
I have carefully tested all possible parameters in Net Service Guide

– ADR :
I know how to set up the ADR location in the database and network.
I know how to use the command line utility, documented in utilities -> adrci

– RMAN :
I know how to configure RMAN with the configure statement. I am efficient in doing backups and restores
I have read the RMAN Reference carefully.

– Dataguard :
I am confident with the dgmgrl utility and with oem to configure and monitor dataguard.
I have read the Dataguard Broker Guide

– Secure Lob
I have played with lobs as basicfile and securefiles to check the differences and new features. I have read the secure files guide

– Streams
Streams is a very complex product. I have attended last year a five days course in Paris to only realise it is utterly tricky to maintain and debug, it case of errors.
I know where to look at if the capture, propagation or apply fail. Mostly in alert log, but also in the DBA views. I know how to restart the different processes. I am confident with both the OEM and PL/SQL Packages DBMS_STREAMS_*.
I have read the Streams Concept and Streams Replication Administration

– Advisors and baselines
A very valuable resource to read is the 2 Days Perf Guide. It may sound strange to prepare an OCM exam with a 2-Days guide, but it is an awesome lecture.
To get deeper and learn the API usage, read Performance Tuning -> Optimizing SQL Statements

– Replay a capture workload
I learnt both the PL/SQL and EM usage in the Real Application Testing User Guide

Almost all the other topics are self explanatory and almost trivial once you know the syntax by heart…

It is highly probable you already have MS AD in your company. Probably you use a local tnsnames.ora. Apart from setting a Oracle Internet Directory or Oracle Virtual Directory, there is one more option that you may want to consider : AD.

Ok, here is a bit of a road map :

– Schema Extension :
extending the schema is irreversible and you will have to test this properly and explain why you need this (remove the need of distributing a tnsnames, central administration) to your Microsoft Admin friends. To extend the schema, use Oracle Network Configuration Assistant. The step-by-step guide is there

– Anonymous or authenticated bind
prior to 11g, you needed to allow anonymous bind on the AD server. Your Security Admin friends will probably prefer the 11g approach of setting NAMES.LDAP_AUTHENTICATE_BIND to true. If you set NAMES.LDAP_AUTHENTICATE_BIND to true, the Oracle clients will use your windows credentials to do the tnsnames resolution.

For sql developer, use Connection Type=TNS, Connect Identifier=DB01. connection type=Ldap does not work with authenticated bind

– Import the tnsnames and / or create new entries
all done with Net Manager and pretty intuitively. Except that you will use “Directory –> Export Net Service Names” to import the tnsnames in AD

If you get and issue with ocijdbc11, you either do not have the *ocijdbc11* driver in your PATH / LD_LIBRARY_PATH / LIBPATH or the use the wrong driver. For instance if you compile with java 32bits, you cannot use the oci 64 bit.

If you use a jdbc thin ldap resolution and have no anonymous bind, it will return an error

After we upgrade a db to 11g someone complained about an ORA-24248: XML DB extensible security not installed

I thought, it should be easy to revert to 10g mechanism. Probably wrong after reading Marco :The default behavior for access control to network utility packages has been changed to disallow network operations to all nonprivileged users. This default behavior is different from, and is incompatible with, previous versions of Oracle Database.

I do not want to install XDB to send mail. Sounds like an overkill…

Ok, as an hard core dba I created a wrapper in the sys schema, something you probably should not do !

It is a common practice to always shutdown abort the database before restarting and shutting in down immediate. This is because sometimes SHUTDOWN IMMEDIATE takes ages. For instance due to a huge transaction to be rollback.

I do not like it. At all.

First, chance exists that you won’t be able to start the database anymore. I have not heard or meet anyone who had this issue since Oracle7, but I still believe it.

Second, shutdown abort is very useful if something goes seriously wrong. But if something goes wrong, you may want to find out what it is.

Third, you may hit more bugs than if you do close normal. And you may get less help from support if this is due to an abusive shutdown abort. YMMV

Ok, small demo to preach to the converteddisclaimer: this demo is not innocent, do not try this on your database

In 10g and before, the command was oradebug dump events 1 and the list was dumped in a trace file, 11g directly outputs to the console.

Note there is no backward compatibility with unsupported tools like oradebug.
In 11g you will get an ORA-76 with dump events

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump events 1
ORA-00076: dump EVENTS not found
$ oerr ora 76
00076, 00000, "dump %s not found"
// *Cause: An attempt was made to invoke a dump that does not exist.
// *Action: Type DUMPLIST to see the list of available dumps.

This is a bit confusing as if you are servicing more than one database per listener, all dns aliases will default to the same database. So I would not recommend a default service name if there is more than one service name.

I had a very serious issue last Friday with errors as weird as ORA-00322: log name of thread num is not current copy. After a clone from Prod to Test, the prod crashed. Both databases are located on the same server (I am not a virtualization fanatic) and clone from prod to test have been done by most of my dba readers.

What did change in 11g ?

Incredibly, in 11g, rman issues the following statement before restore

At this point, the complete restore was finished, we restored the missing archivelog, recover and open resetlog.
What happened then???
At this point, remember you still have the prod controlfile (and the prod db_name), so by doing an alter resetlogs, the production redologs will get overwritten without notice !

This is a fairly important change that could really hurt if you are cloning two databases on the same server.

In case you are trying to save a failed database clone, make sure you check db_name and also v$logfile before doing an alter database resetlogs!!!

If you have a datawarehouse and the data are getting to big for a full duplicate or tablespace transport, if you want to experience more about Streams or simply if you are in San Francisco and wants some distraction on Thursday after at 1pm, do not miss Chen session Oracle Streams – Live Demo

OK, something very bad about Toad formatter is (at least the version I am using) that it considers unquoted password to be case insensitive. Which is wrong. The password, in 11g, is case sensitive, even when not in double quotes. Therefore, be sure to put the right case when creating scripts and do not format your CREATE USER statements with Toad or use double quotes for disambiguation!

In session 1, the isolation level of the transaction is set to SERIALIZABLE. Session 2 update the salary of Scott to 5000 and commits. The session 1 therefore does not read committed data and any tentative to change the committed data will fail. Roll[back;] ends the transaction. The session 1 can then read committed data and update the salary to 3000.

Ok, let’s imagine you have to interview an OCM and you want to ask him a very difficult question 😉 :

– Is it possible in Oracle to read uncommitted data from another session?

When only the 11g oracle hash is used as a value, the password is case sensitive and if the setting of sec_case_sensitive_logon is on false, the login failed as there is no 10g string. This setting is probably the most secure setting as the 10g string is not saved in USER$.

In Oracle11g the alert log is an XML file. The old style alertSID.log is created out of the log.xml for backward compatibility only. However, some exciting enhancement are not noticable in the old one.

The 11g OCP certification should be available this year. You can register for the production exam 1Z0-050 New Features on Prometrics and pass the exam on Mon Feb 18th or later, the OCA exams 1Z1-051 SQL Fundamentals and 1Z1-052 Admin I are in beta and the OCP exam 1Z1-053 Admin II is planned for early 2008 (well, the OCA exams are planned for late 2007, so do not rely on this timeframe).
Check the official page :
Oracle Education 11g certification

The 11g OCM is planned for a later date (whatever that means). Well, I am no longer going to wait for the 10g OCM upgrade for 9i OCM… I am not going to do a certification for an older release since 11g exams are available. I will rather do the 11g ocp this year. As I wrote earlier, the 10g OCM was planned for late 2004…

OPatch did run the necessary script (catcpu for Dummy2007) on the various instances (LSC08). It needed only one step and 34 seconds on my notebook. This patch is a dummy patch, let’s wait for CPU January to see if it is online applicable 😉

Today I opened two SR about flashback archive in 11g. In one of them, I complained that user SCOTT was not allowed to create a flashback archive. In the doc that I downloaded a few weeks ago I read :Prerequisites
You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data archive. This privilege can be granted only by a user with DBA privileges. In addition, you must have the CREATE TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside.

So as I was getting an ORA-55611, I opened a SR. The support engineer pointed me to the online documentation where I was astonished to read :Prerequisites
You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data archive. In addition, you must have the CREATE TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside. To designate a flashback data archive as the system default flashback data archive, you must be logged in as SYSDBA.

Well, Read The Fine Online Manual !!!

The second tar is related to long retention (about the age of the earth)

He started his 11g series : Oracle Database 11g: The Top Features for DBAs and Developers

There are many partitioning enhancements. The most exciting feature for me is the INTERVAL partitioning. A huge cause of downtime and waste of storage is the range partitioning. In 10g and before, a partitioning by dates required that the partition are defined before values are inserted.

Ok, one more feature Arup introduced is the REF partitioning, where you have a schema with both the parent and child tables partitioned, and you want to partition on a column of the parent table that is not in the child table (as you had bitmap join indexes, you have now ref partitions). Check it on his site.

Finally Arup explained SYSTEM partitioning, which is not inconceivable, but will hardly be used.

Imagine you have a table containing just one single LOB column, and a LOB cannot be used as a partition key.

On the other hand, it is a huge change which is not documented (I immediately sent comments to the Security and Reference book authors) and it will make a lot of script failing (scripts that use to change the password to log in and change it back to the original value afterwards).