Oracle Notepad vi

How Can I?
Book Reviews
User Groups

Legal Notice & Terms of Use
Privacy Statement

Blog Archives

Thursday 26 December, 2013
Edward Snowden:
"Great Britain's George Orwell warned us of the danger of this kind of information. The types of collection in the book - microphones and video cameras, TVs that watch us - are nothing compared to what we have available today. We have sensors in our pockets that track us everywhere we go. Think about what this means for the privacy of the average person.

A child born today will grow up with no conception of privacy at all. They'll never know what it means to have a private moment to themselves, an unrecorded, unanalysed thought. And that's a problem because privacy matters, privacy is what allows us to determine who we are and who we want to be.

'The conversation occurring today will determine the amount of trust we can place both in the technology that surrounds us and the government that regulates it. Together we can find a better balance, end mass surveillance, and remind the government that if it really wants to know how we feel, asking is always cheaper than spying."
Daniel Morgan (with thanks to Kortchmar & Henley):
Turn this thing around
I will not go quietly
I will not lie down
I will not go quietly
Thursday 05 November, 2013

lala ngoxolo Madiba phumla elxolweni
Monday 04 November, 2013
Anyone that follows my work knows I enjoy solving a puzzle and uncovering a bit of unpublished Oracle functionality. Well I've seen a lot of interesting things but here's one I never expected to see and don't fully understand. Anyone want to hazard a guess what is going on here?

conn uwclass/uwclass@pdbdev


Sequence created.





SQL> exec dbms_cdc_impdpv.bump_sequence('UWCLASS', 'SEQ', 99);

PL/SQL procedure successfully completed.

ERROR at line 1:
ORA-00600: internal error code, arguments: [4882], [0x7FF0A7C1058], [0x7FF852E0DD8], [0x7FF8FF78B08], [], [], [], [], [], [], [], []

   2 FROM user_objects
   3 WHERE object_name = 'SEQ';


ERROR at line 1:
ORA-00955: name is already used by an existing object



I can guess part of it ... but why does the value end up at 100? One hundred is not 2+99.
Sunday 03 November, 2013
Last week one of my client's DBAs decided to "help." Help as it we could have fixed the problem in minutes ourselves, in a half-hour if we had to explain what we were doing, and it took two days because they helped.

By background the issue was a nertwork infrastructure failure that force a configuration change and a listener restart. The customer's DBA, probably feeling a bit dejected because those damned contractors were doing all the fun stuff, decided to restart the cluster himself: So we sat back on the conference bridge and listened to a shower of keyboard clicks. Might not have been too shaby if the listener actually functioned properly except it didn't.

An email asking the DBA what he had done, what commands, etc., brought back a terse response from his manager "He restarted the listener and that's all he did." Which obviously wasn't true as the listener on node 2 was non-functional.

The following listing shows the triage.

cd /var/log

vi messages

/* searched for the DBA's logons: Found for of them over the two days during which the issue was suppose to have been fixed */

Oct 30 22:48:35 oras1n2 adclient[19092]: INFO <fd:22 PAMVerifyPassword> audit User 'mhurd' authenticated based on Kerberos exchange to AD
Oct 30 22:48:35 oras1n2 adclient[19092]: INFO <fd:22 PAMIsUserAllowedAccess> audit User 'mhurd' is authorized

Oct 30 23:22:42 oras1n2 adclient[19092]: INFO <fd:20 PAMVerifyPassword> audit User 'mhurd' authenticated based on Kerberos exchange to AD
Oct 30 23:22:42 oras1n2 adclient[19092]: INFO <fd:20 PAMIsUserAllowedAccess> audit User 'mhurd' is authorized

Oct 30 23:24:37 oras1n2 adclient[19092]: INFO <fd:23 PAMVerifyPassword> audit User 'mhurd' authenticated based on Kerberos exchange to AD
Oct 30 23:24:37 oras1n2 adclient[19092]: INFO <fd:23 PAMIsUserAllowedAccess> audit User 'mhurd' is authorized

Nov 1 11:34:34 oras1n2 adclient[27196]: INFO <fd:24 PAMVerifyPassword> audit User 'mhurd' authenticated based on Kerberos exchange to AD
Nov 1 11:34:34 oras1n2 adclient[27196]: INFO <fd:24 PAMIsUserAllowedAccess> audit User 'mhurd' is authorized

Nov 1 18:13:05 oras1n2 adclient[27196]: INFO <fd:24 PAMVerifyPassword> audit User 'mhurd' authenticated based on Kerberos exchange to AD
Nov 1 18:13:05 oras1n2 adclient[27196]: INFO <fd:24 PAMIsUserAllowedAccess> audit User 'mhurd' is authorized

So now I knew there were multiple logins, more than just the one of which we had listeneed. So the next step was to review the bash_history file for what was done. bash_history unfortunately doesn't log date-time, making it nearly worthless, but nearly is not the same as totally and I quickly found what I was looking for shown in the next listing.

. .mark
tnsping ASHSFE4
ps -ef |grep LISTENER
locate listener_scan1
tail -f /app/11.2.0/grid/log/diag/tnslsnr/oras1n2/listener_scan1/trace/listener_scan1.log
lsnrctl status
lsnrctl services
. oraenv
lsnrctl services
lsnrctl restart
lsnrctl stop
lsnrctl start
ps -ef |grep pmon
ps -ef |grep LISTENER
lsnrctl stop LISTENER_SCAN1
. oraenv
lsnrctl stop LISTENER_SCAN1
lsnrctl start LISTENER_SCAN1
lsnrctl status
lsnrctl status
lsnrctl status LISTENER_SCAN1
ps -ef |grep LISTENER
locate listener_scan1
tail -f /app/11.2.0/grid/log/diag/tnslsnr/oras1n2/listener_scan1/trace/listener_scan1.log1

If you do not immediately see what is wrong ... then this blog post has been worth the keystrokes. You must not, ever, try to manage the scan listener using listener control (lsnrctl) but rather you must use server control (srvctl). The same goes for starting and stopping the database ... always use server control: Never SQL*Plus.

The processes in memory were killed and the listener started properly: Problem solved in a few minutes.
Wednesday 02 October, 2013
Sometimes I find myself in Oracle doing the same thing over and over again and expecting a different result. For example getting a new client and expecting that they hired me for a reason other than their internal dysfunction.

But just now I found what I have been looking for ... searching for ... yearning for ... in Oracle ... finally I have found the Redwood Shores version of 42. And I found it inside Oracle Database 12c version

So without further hyperbole ... here it is.

exec dbms_rcvcat.sanityCheck;
BEGIN dbms_rcvcat.sanityCheck; END;
ERROR at line 1:
RMAN-20021: Database not set
ORA-06512: at "RMANADMIN.DBMS_RCVCAT", line 10066
ORA-06512: at "RMANADMIN.DBMS_RCVCAT", line 10027
ORA-06512: at line 1

dbms_rcvcat.setDatabase has 15 parameters ... well ... better luck next time.
DataVault has a sanity check routine too: dvsys.dbms_macadm.dv_sanity_check.
And still we have so little of such a precious commodity.
Thursday 25 July, 2013
I've found something new and wonderful inside the Oracle database ... only two issues ... first it is undocumented and thus unsupported ... the second is that I have on idea what to do with it now that I've found it. That said ... here it is.

SQL> conn sys@pdborcl AS SYSDBA

SQL> SELECT dbms_isched.get_tns_nvpair('PDBORCL')
  2  FROM dual;

  (ADDRESS = (PROTCOL = TCP)(HOST = = 1521))

The server on which this was run has a CDB with two listeners ... one at port 1521 for public and another at port 1526 for DataGuard replication.

If anyone can help me figure out how to make use of this I would be happy to put your suggestion into the Library with attribution.
Thursday 30 May, 2013
All these years I've been faking it and no one has ever caught me. This short tutorial will teach how to fake it too.

An error I found this morning, while look at a DataPump import, was the following:

ORA-39083: Object type INDEX failed to create with error:
ORA-08114: can not alter a fake index
Failing sql is:

Most DBAs, even DBAs with 20+ years of experience have never heard of a fake index and wouldn't know one if they saw it. Here's the history lesson in how they came into being.

Oracle's ADDM, introduced in version 10.1, gives tuning advice. And one of the hidden techniques it uses to do this is to build fake indexes, run hidden explain plans, and see if the optimizer will use the index. NOSEGMENT indexes were the indexing trick and the DBMS_XPLAN package was how the plan was created behind the scenes. And if the index was used and the plan cost low ADDM recommends that you build the index: Simple.

A real index is a complex thing. It is an index object (IND$ and OBJ$), consists of a segment (SEG$), composed of extents (UET$), is owned by someone (USER$), the segment is in a tablespace (TS$), that consists of one or more datafiles (FILE$). And I'm just getting started. Once you issue the DDL command Oracle does lots of latching, recursive SQL, space allocation, a full table scan of the table, etc. etc. and has to build it which is I/O intensive. Now assume a table with millions of rows and it gets ugly. So what was Oracle to do? How could it try 10, 20, 50 different index possibilities and report which one was best on a running production database? The answer was NOSEGMENT indexes, also known as FAKE indexes. Fake in the sense that no index is really built. What happens is that two different entries are made into the data dictionary sufficient to fool the optimizer into thinking there is an index which is not actually built. So the index "build" takes 20 milliseconds or so. The optimizer only knows about two things ... objects and index columns ... so the two inserts are into OBJ$ and INDCOL$.

Now ADDM, after its analysis  is supposed to drop the NOSEGMENT indexes it creates. But occasionally, as happened at SF, it fails to do so for some reason. In the above case it appears the fake index was created by a Quest product so likely the user session crashed or they ended their session in some other way that aborted the session. Alternatively Quest may not use ADDM since they wouldn't know a client's licensing status and probably wrote their own engine to create, test, and drop them.

So the way you find these things, if you are looking for them is with the following SQL statement. What it does is look for index objects (OBJ$) that are not in dba_indexes (IND$) ... remember fake index creation only hit INDCOL$.

SELECT owner, object_name
FROM dba_objects
WHERE object_type = 'INDEX'
SELECT owner, index_name
FROM dba_indexes;

If you find one it can be dropped like any other index.
Here is my web page on indexes and it includes a NOSEGMENT index demo.
Scroll down to "Virtual/NoSegment".
Sunday 28 April, 2013
Thank you Big Brother for protecting me,
my family, and my friends
from the horror and savegry
of living in a free country
as intended by the founding fathers.

Would that the likes of Franklin,
Jefferson, and Madison had
been born with your wisdom.
Thursday 18 April, 2013
"What was God doing before the divine creation? Was he preparing hell for people who asked such questions?"
~ Dr. Stephen Hawking
Thursday 31 January, 2013
Just found this going through old pictures: Stanley meets one of his brothers at UKOUG.
Then went to Facebook and found Kyle Hailey had published this picture from the ClosedWorld 2012 conference. I hope I am never so miserable about being with good friends again.  ;-)
Wednesday 30 January, 2013
If you have been looking for something funny to start your day ... here's my contribution. I found this with a google search reviewing lots of crazy speculation about Database 12c which I have been Beta testing for many months.

Here's the line I just love: "It also wasn't clear Sunday whether Oracle will soon launch a beta program for 12c"

Which was published 30 September, 2012 by ComputerWorld.

Click here to read the original

"Soon launch?" ROFLMAO!
Tuesday 29 January, 2013
From time-to-time people try to define terms such as VLDB (Very Large DataBase) and those definitions change along with our use of technology. I am feeling the need to define older desupport-mode versions of the Oracle database in some manner so the following table is my key to these versions.

Keyword Version Number - Description
Currently Supported and Fully Patched Version or 12.1.
Currently Supported All versions equal to or greater than
Neolithic Versions and
Jurassic Versions to
Permian Anything 9i
Devonian Anything 8i
Pre-Cambrian Anything Pre 8i
Friday 25 January, 2013
Yes We Can
Friday 18 January, 2013
Tonight I am sitting here re-reading a book I haven't touched in more than 35 years: Isaac Asimov's "The Gods Themselves."
And I am struck by how appropriate the book's dedication which I repeat here for everyone that find's this page.

To Mankind

And the hope that the war against folly may someday be won, after all
Tuesday 8 January, 2013
Hundreds of thousands of websites are potentially at risk following the discovery of an extremely critical vulnerability in the Ruby on Rails framework that gives remote attackers the ability to execute malicious code on the underlying servers. Get the details at ARS Technica.
Tuesday 1 January, 2013

Happy New Year ... Best wishes for the new year and beyond.