Read only tablespace requires no outstanding transactions...anywhere...

Nice little gotcha I received yesterday morning. Our intention is to rebuild two large tables from range partitioning to hash partitioning on another server. One of the datafiles for the tablespace that houses one of the tables in question is 1.4TB in size. When we tried to put it in read only mode
(we are using transport tablespace to segment the rebuild on another box), it hung on the enq - TX contention wait event. I found (and never knew before) that putting *any* tablespace in read only requires that no outstanding (uncommitted or rolled back) transactions exist, even in a tablespace that you are not putting into read only mode. Unfortunately, a long running transaction was about six hour from completing, so we had to wait it out.

It wasn't enormously inconvenient, but caused our rebuild to be pushed back a bit. I wonder why *no* transactions can exist? Does the kernel not "know" that there are no transactions that would affect the tablespace in question? Maybe it's just to complex to track, and its such a rare occurrence, the kernel designers just said "to be safe, let's ensure there is nothing happening when we go into read only mode"?

Do we need AWR?

A recent post on CDOS got me to thinking...do we need AWR? After all, AWR stands for "Automated Workload Repository". There are three words in that functionality title.

From dictionary.com:

Automated (automatic, actually): having the capability of starting, operating, moving, etc., independently
Workload: The amount of work that a machine produces or can produce in a specified time period.
Repository: A place where things may be put for safekeeping.

The normal reasons given for using AWR (or not) are as follows:

1. You can't duplicate the functionality
2. It is against your license terms to use AWR if you haven't paid for it.

If either is true, and depending on which one is, then you may or may not want to use it.

The first one indicates you can't do what it does. To answer this accurately, we have to document what it does. To be honest, I can't list every single piece of functionality, but I can say what I use it for (which is all that matters, anyway). I never use the Grid Control to view AWR information as a baseline. I have written my own queries to query the AWR (we *do* have it licensed). This is a piece of functionality I have always found lacking in AWR. If I want to see the historical change in db file sequential read, there is no Grid tool (of which I aam aware) that will do this for me. As a result, I wrote my own query to which I pass an event, and it will print out the historical time waited spent on this event. You can of course (and I do) apply this to OSSTAT, SYSSTAT, etc. In order to do this, of course, I must query the AWR views...or do I?

I could write my own SQL that would create tables as a copy of each v$view, and then periodically select everything from the current contents of these views into these repository tables. This is a simple solution. In fact, I wrote it this morning in 45 minutes.

But wait, is this legal? In other words, are the historical AWR views the ones that cannot be used, or is it the live v$view information that cannot be queried?

According to http://download.oracle.com/docs/cd/B19306_01/license.102/b14199.pdf, the only off limits view is v$active_session_history.

Command-Line APIs
Diagnostics Pack features can also be accessed by way of database server APIs and
command-line interfaces:

• The DBMS_WORKLOAD_REPOSITORY package is part of this pack.
• The DBMS_ADVISOR package is part of this pack if you specify ADDM as the value of the advisor_name parameter, or if you specify for the value of the task_name parameter any value starting with the ADDM prefix.
• The V$ACTIVE_SESSION_HISTORY dynamic performance view is part of this pack.
• All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.
• All data dictionary views with the prefix DBA_ADVISOR_ are part of this pack if queries to these views return rows with the value ADDM in the ADVISOR_NAME column or a value of ADDM* in the TASK_NAME column or the corresponding TASK_ID.
• The following reports found in the /rdbms/admin/ directory of the Oracle home directory are part of this pack: awrrpt.sql, awrrpti.sql, addmrtp.sql, addmrpti.sql, awrrpt.sql, awrrpti.sql, addmrpt.sql, addmrpti.sql, ashrpt.sql, ashrpti.sql, awrddrpt.sql, awrddrpi.sql, awrsqrpi.sql, awrsqrpt.sql.

Can you shutdown an instance from plsql?

SQL> begin
2 execute immediate 'alter database close';
3 execute immediate 'alter database dismount';
4 end;
5 /

PL/SQL procedure successfully completed.

SQL>

Shutting down archive processes
Archiving is disabled
Tue May 29 16:41:10 2007
ARCH shutting down
ARC1: Archival stopped
Tue May 29 16:41:15 2007
ARCH shutting down
ARC0: Archival stopped
Tue May 29 16:41:16 2007
Thread 1 closed at log sequence 24
Successful close of redo thread 1
Tue May 29 16:42:07 2007
Completed: alter database close
Tue May 29 16:42:07 2007
alter database dismount
Tue May 29 16:42:08 2007
SUCCESS: diskgroup ASM1 was dismounted
Tue May 29 16:42:09 2007
SUCCESS: diskgroup ASM2 was dismounted
SUCCESS: diskgroup ASM3 was dismounted
Tue May 29 16:42:09 2007
Completed: alter database dismount
(END)

It doesn't look like you can actually shutdown the processes, though, without external code.

Also, you can't re-mount a database shutdown in PLSQL, as you will get an

ORA-00750: database has been previously mounted and dismounted.

Do we need CDOS?

The usenet newsgroup comp.databases.oracle.server ("CDOS") can be a contentious place. Posters there tend to be very opinionated, and sometimes (actually, frequently in my estimation) very rude. It occurred to me that most countries in the world desire to be free. As a result, "big brother" type message board moderators don't tend to go over well with computer types, who are normally free thinkers. However, I have seen good contributor after good contributer driven away from CDOS by what I frequently call the "self appointed Oracle police". There are a few very prominent posters on the newsgroup who consistently become what I would call angry with "newbies" for asking questions they (the "police") don't feel should be asked.

This creates (at least) two problems in my mind:

1. Newbies are reluctant to ask questions for fear they may not be posting "correctly" (don't we all have enough to worry about in our lives without complaining about if someone "top posts"?), or are asking question for which they should already know the answer, but "they are too lazy to read the documentation", etc.
2. Good posters (good, technical contributors to questions posted for discussion) are driven away by the rudeness of these posters (I can't tell you how many times I have seen this over the years...)

It occurred to me that if we don't want to be policed by a "big brother" moderator, and if we don't want to spend time in the jungle of the self appointed Oracle police, there is an answer.

Why not have a forum where readers vote posters off the board in a democratic fashion? Although this may sound somewhat inhumane on the surface, it makes sense to me. Users who don't want to confront the Oracle playground bullies could anonymously cast a vote which would contribute to the deactivation of the bullies account(s). This could be done by restricting IP, username, etc., with a voter only being able to cast one vote against any given "problem child". This would allow the group to moderate itself, giving the "unheard" voice a say in what it looks like. Granted, I haven't completely thought this out (once a week purge?), but it's a start...

Of course, this idea relies on the ability (or lack thereof) of Oracle aficionados (or just newbies looking for an answer to a one time "simple" question) to act like human beings, and not "have it in for" a given poster.

Are there legal issues with this? In other words, what if a "scorned poster" posts things from a different IP, etc., which get the forum owner in legal hot water? Would this idea itself drive away good contributors (the stooges are normally very sound, technically)?

Having said all of that, it sounds like a cool app to write, anyhoo!!!

Standby created without a standby controlfile?!!

Today, a junior DBA created a standby database by copying the controlfile to another host, restoring the 4TB database it serves, and recovering all available logs via RMAN. Sounds good, right? Well, it was until we went into SQL*PLUS and found that a "RECOVER STANDBY DATABASE;" command returned an exception that we were not using a standby controlfile. At this point, we were thinking that we had lost an entire day, when we were already behind on the project.

My initial thought was to issue a "alter database convert to physical standby;". However, I then found that the DBA had created the controlfile for the standby as a "normal" backup controlfile. Was this a good thing?...yeah, not so much. Oracle will not allow you to convert a backup controlfile to a standby, since a backup controlfile implies you are doing incomplete recovery, which a standby database, by definition, should not be.

To complicate matters, we were putting the datafiles on the standby in ASM, where they were on raw devices on the primary host. As a result, RMAN set newname commands were required.
After copying a "real" standby controlfile to the standby host, I found the set newname commands did not work, as this controfile did not "know" about our previously restored and renamed files into ASM on the standby. Since we obviously did not want to re-restore 4TB of datafiles, we ended up cataloging the datafile copies on the standby host so the new standby controlfile "knew" about them, i.e.,

catalog datafilecopy '+ASM_TIER2/XWCBMW/DATAFILE/AEID_LB1_4K.285.621280543' level 0;

...

This worked, and gave us much joy, as out set newname commands in RMAN now worked, and allowed recovery to commence :)

Java Lock class is great for test casing

OK, so while trying to understand Oracle's redo latching mechanisms (we have been experiencing a nightmare with log file sync since switching to a new DMX subsystem), I found that java 1.5 has a new Lock class. How cool is that?!!!

import java.io.*;
import java.util.concurrent.locks.*;

class MyThread implements Runnable {
static Lock l = new ReentrantLock();
Thread thrd;

MyThread(String name){
thrd = new Thread(this, name);
thrd.start(); // start the thread
}

public void run(){
System.out.println(thrd.getName() + " starting.");
try {
boolean b = l.tryLock(10000,java.util.concurrent.TimeUnit.MILLISECONDS);
if (b)
System.out.println("Got the lock for " + thrd.getName());
else
System.out.println("Didn't get the lock for " + thrd.getName());
//l.lock();
Thread.sleep(2000);
l.unlock();
}
catch(Exception e) {}
System.out.println(thrd.getName() + " terminating.");
}
}

class myLock {
public static void main(String args[]) {
for (int j = 1; j <= Integer.parseInt(args[0]); j++) {
MyThread mt1 = new MyThread("Child #" + j);
}
}
}

We are all spinning our wheels, trying to use formulas to predict troublespots, when we should be asking basic questions that we do in everyday life. An example is a dad or mom with his/her child. Do we write scripts, or use software to tell us if our kids need help in a given area, or do we talk to them? I realize that a DBA cannot talk to 15,000 different user’s of a system on a daily basis, but it would benefit them (and their users) to ask key users questions about how they use the system. Don’t just look for latch free wait events. Ask the users what they are trying to accomplish. Problems are much easier to solve when we understand the environment in which they have been created...

Hello!

This is my first entry to the blog history of building appcrawler, an open source application that will allow you to examine your database applications from an artificial intelligence standpoint. It is an ambitious goal, but I have wanted to do it for a long time, and like they always say, "A journey of a thousand miles begins with one step..."