die Seilerwerks

Views, Datatypes and Nulls

2013-02-15T13:47:00.005-06:00

Had an interesting little case come up today when helping a client re-write a view. This legacy application stored a date value as January 1, 1970 when they didn't have a date (basically using it as a de facto NULL). I did my best to lecture the client on why this was a horrible idea, but there was nothing we could do in the short term.

Anyway, he had a view that would use a DECODE function to convert a date of 1970/01/01 to NULL, otherwise just pass the date through. However, we noticed that the view schema said the field was defined as varchar2(18) instead of date. Here is my example using a view on DBA_OBJECTS:

SQL> create or replace view test_objects1 (object_name, created) as
2 select object_name, decode (created
3 , to_date('1970/01/01','yyyy/mm/dd')
4 , null, created)
5 from dba_objects;

View created.

SQL>
SQL> desc test_objects1;
Name Null? Type
----------------------------------------- -------- -------------
OBJECT_NAME VARCHAR2(128)
CREATED VARCHAR2(18)

I suggested we wrap that NULL in a to_date function just to see what happens, and sure enough that gives us what we want:

SQL> create or replace view test_objects2 (object_name, created) as

2 select object_name, decode (created

3 , to_date('1970/01/01','yyyy/mm/dd')

4 , to_date(null), created)

5 from dba_objects;

View created.

SQL>

SQL> desc test_objects2;

Name Null? Type

----------------------------------------- -------- -------------

OBJECT_NAME VARCHAR2(128)

CREATED DATE

It seems silly to wrap NULL in a TO_DATE, or do any function on a NULL value. One would hope that Oracle sees the NULL and short-circuits the function to avoid doing the unnecessary work.

Update: Looks like this is documented functionality in the DECODE function:

Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.

Update #2: Thanks to Sayan in the comments for tipping me off about NULLIF, which seems like the best option, doing exactly what we want without having to convert the type:

SQL> create or replace view test_objects3 (object_name, created) as
2 select object_name
3 , nullif (created
4 , to_date('1970/01/01','yyyy/mm/dd'))
5 from dba_objects;

View created.

SQL>
SQL> desc test_objects3;
Name Null? Type
----------------------------------------- -------- -------------
OBJECT_NAME VARCHAR2(128)
CREATED DATE

Learning to Love the ADR - Part 4: Incident Packaging Service

2013-02-02T12:00:00.000-06:00

This post is the fourth (and final) in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.

IPS

Any DBA that has had to file a support request with Oracle Support will be violently nodding their head in agreement when I ask if they've ever had Oracle Support ask for log file after trace file after log file after trace file. It's hard to not be cynical, especially when they sometimes request a file that was already provided! In 11g, Oracle has made some of that work easier by providing the Incident Packaging Service, or IPS, with the ADR. IPS lets you quickly and easily gather up diagnostic files related to a given incident, problem, or timeframe. You can also create empty packages and add whatever files you see fit.

It all starts with the IPS CREATE PACKAGE command. This creates a logical package (no package files are created at this point) in the ADR. Various options include:

adrci> ips create package incident <incident_id>;
adrci> ips create package problem <problem_id>;
adrci> ips create package problemkey "problem_key_string";
adrci> ips create package seconds <seconds_before_now>;
adrci> ips create package time 'start_timestamp' to 'end_timestamp';
adrci> ips create package;

It is definitely worth your while to review the output of help ips create package to get details on these, as I won't be touching most of them any further. The last command listed creates an empty package, after which you can use the ips add incident or ips add file commands to add data to the package before generating it.

When you are ready to generate the package file to send to Oracle Support, run the IPS GENERATE PACKAGE command, e.g.

adrci> ips generate package 3 in /home/dts/files/;

This will create a zip file in the specified directory. The package number is given to you when you first create the package, as our later example will demonstrate.

Focusing on the first example, when you run "show incident" in adrci, it will provide you with the list of incidents and their incident numbers. If you feel the need to create an SR with Oracle Support for this incident, you can use that command to create a package based on that incident number, and then generate the package file for that package. However you can do both in one step using IPS PACK INCIDENT:

adrci> ips pack incident <incident_id> in <path>;

For eaxmple:

adrci> ips pack incident 13579 in /home/dts/files/;

This command creates a package and generates the zip file in one step.

Example Time!

Now that I've bored you with the details, lets go through an example incident that you can reproduce in your sandboxes at home, as I'm doing now as I write this.

Step 1: Cause an Incident!

Rather than really cause some harm to my sandbox, let's just set things up so something that normally wouldn't create an incident does so this once:

SQL> alter session set events '942 incident(table_missing)';

Session altered.

SQL> drop table doesnotexist;

drop table doesnotexist

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> alter session set events '942 trace name context off';

Session altered.

Viewing the trace (human-readable text) alert log:

Wed Jan 30 13:56:20 2013
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25751.trc (incident=10993):
ORA-00700: soft internal error, arguments: [EVENT_CREATED_INCIDENT], [942], [TABLE_MISSING], [], [], [], [], [], [], [], [], []
ORA-00942: table or view does not exist
Incident details in: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_10993/orcl_ora_25751_i10993.trc
Wed Jan 30 13:56:25 2013
Dumping diagnostic data in directory=[cdmp_20130130135624], requested by (instance=1, osid=25751), summary=[incident=10993].
Wed Jan 30 13:56:25 2013
Sweep [inc][10993]: completed
Sweep [inc2][10993]: completed

Step 2: Use ADRCI to view the incident

So our weary DBA is awaked by a page from the monitoring system and can now quickly see what the problem was without having to track down alert log locations and such.

[oracle@localhost ~]$ adrci

ADRCI: Release 11.2.0.2.0 - Production on Wed Jan 30 13:58:29 2013

ADR base = "/home/oracle/app/oracle"

adrci> show homes

ADR Homes:

diag/rdbms/orcl/orcl

diag/tnslsnr/localhost/listener

adrci> set home diag/rdbms/orcl/orcl

adrci> show incident

ADR Home = /home/oracle/app/oracle/diag/rdbms/orcl/orcl:

*************************************************************************

INCIDENT_ID PROBLEM_KEY CREATE_TIME

-------------------- ----------------------------------------------------------- ----------------------------------------

10993 ORA 700 [EVENT_CREATED_INCIDENT] [942] [TABLE_MISSING] 2013-01-30 13:56:20.294000 -06:00

1 rows fetched

Here we see the INCIDENT_ID is 10993, which we'll need to reference later. Now let's assume it's something more sinister and we feel the need to file an SR with Oracle Support and want to upload some files.

Step 3: Create a Package!

adrci> ips create package incident 10993;

Created package 1 based on incident id 10993, correlation level typical

adrci> ips generate package 1 in /home/oracle;

Generated package 1 in file /home/oracle/ORA700EVE_20130130140011_COM_1.zip, mode complete

... or ...

adrci> ips pack incident 10993;

Generated package 2 in file /home/oracle/ORA700EVE_20130130140052_COM_1.zip, mode complete

Both methods achieve the same result, with the exception of the resulting package file name, which as you can see is based on YYYYMMDDHH24MMSS date format. You can see that a new package is generated (1 vs 2). The zip file that is created is a standard zip file, you can use unzip -l <file_name> to look at the contents if you wish. You'll see a number of .dmp files, along with the XML and trace alert log files and other trace files that Oracle has deemed relevant to its needs. Alternatively, you can use IPS to query this information as well:

adrci> ips show files package <package_number>;

This will give you a nicely formatted listing of each file in the package, with some metadata for each file. All that's left now is to upload the package zip file to your SR and hope that Oracle Support can see what they need to see!

Well, that's it folks. Hopefully you found this somewhat informative and it leads you to do a little more digging into ADRCI on your own. At the very least you can now get rid of those ugly alert log text scraping scripts!

Learning to Love the ADR - Part 3: Purging (Out With The Old)

2013-01-30T19:00:00.000-06:00

This post is the third in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.

One of the benefits of the new ADR system is the automatic file maintenance (archiving and cleanup) that I mentioned earlier. Depending on the type of file it is, XML log and trace file maintenance is governed by either the long or short purge policy. The values of these policies can be observed from the ADRCI command "show control", e.g.:

adrci> show control

ADR Home = /home/oracle/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1335663986 720 8760 2012-03-28 09:46:07.813608 -05:00 2012-09-18 22:19:08.939553 -05:00 1 2 80 1 2012-03-28 09:46:07.813608 -05:00

I've highlighted the column names and values in red. It doesn't format very well in the terminal either.

Let's break it down.

Short Purge Policy - SHORTP_POLICY

The short purge policy governs files that usually need to be retained for only a short period of time. This includes trace files, core dumps and packaging information files (from IPS, which we'll cover later).

This policy is identified by the name "SHORTP_POLICY" and the value is represented in hours. The default SHORTP_POLICY is 720 hours, or 30 days. The maximum value is 35,791,394 hours, which is over 4,000 years. Hopefully you don't need any more time than this to retain your files. You can set the value to 0 to indicate that all files under this policy are immediately available for purging.

To set this value, you must use the "set control" command in ADRCI. For example, to set it to 168 hours (7 days), we would use this command:

adrci> set control (shortp_policy=168);

Long Purge Policy - LONGP_POLICY

Alert logs, incident info and incident dump files are all governed by the long purge policy, known by LONGP_POLICY in the ADR. The default retention time is 8,760 hours, or 365 days. It has the same maximum value as the short purge policy, and similar behavior when the value is set to zero. Similarly, to set the value for LONGP_POLICY to 1,440 hours (60 days):

adrci> set control (longp_policy=1440);

You can use show control again to view the new settings:

adrci> show control;

ADR Home = /home/oracle/app/oracle/diag/rdbms/orcl/orcl:

*************************************************************************

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME

-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------

1335663986 168 1440 2012-12-28 14:18:02.894677 -06:00 2012-09-18 22:19:08.939553 -05:00 1 2 80 1 2012-03-28 09:46:07.813608 -05:00

1 rows fetched

Purge Job

Now just because a file is eligible to be purged doesn't mean it will be purged soon. It means they will be purged when the instance's purge job runs next, and that could take up to a week. The purge job is an MMON slave task, and only runs once every 7 days, according to MOS Doc ID 975448.1. So don't play around with the long or short policy values thinking they will trigger an instant cleanup (like you would expect if you changed the FRA size below the current used space).

There is however a known bug (9500575) that affects Oracle 11.1.0.7 & 11.2.0.1 where alert log XML files for listeners are not automatically purged. This has the potential then to fill up the disk where these logs are written to.

PURGE Command

You can manually purge files from ADRCI with the PURGE command. One common use is to purge files of a certain type older than a certain age. For example, to overcome the listener log purge bug that was just mentioned, you could schedule this to run weekly (ADRCI can be called from the command-line or cron with commands to run):

adrci> purge -age 10080 -type alert;

This purges all alert logs older than 10,080 minutes. Note that the -age parameter here is in minutes, where as the policy values are in hours.

Similarly you could choose to purge all files for a specific incident ID (assuming you are certain you don't need them and can't wait for them to automatically be purged out) with the -i option:

adrci> purge -i 13579;

For more options, type "help purge" at the ADRCI prompt. I can't stress enough how good the ADRCI help facility is.

Learning to Love the ADR - Part 2: Diagnostic Files and ADRCI

2013-01-29T10:50:00.001-06:00

This post is the second in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.

Wherever the DIAGNOSTIC_DEST is (remember that the default is the $ORACLE_BASE value), Oracle will create a "diag" directory underneath it. Under this you'll find directories based on instance type, for example "rdbms" or "asm" or "tnslsnr". Dig deeper to find directories per database/instance and then the alert, trace, incident, etc. files. What is new with the ADR is the use of XML files for the alert log, as "log.xml" in the "alert" directory. The human-readable alert_XXX.log that you're used to is now in the "trace" directory. One important distinction is that the XML files are maintained by Oracle, archived once they reach 10Mb in size and purged based on policy settings that you can define. The trace files, just as with previous versions, require you to set up some sort of regular rotation/archiving/purging to prevent them from growing out of control. There are other advantages to the XML files, as we'll see in future posts.

SO. MANY. FILES.

Another not-so-fun surprise for those upgrading to Oracle 11g is that the new version generates a LOT more trace data than before. If you monitor those directories for size, your thresholds are likely to be blown away. Some of this was tamed in 11gR2, thankfully. One way to help cut back on some of trace (trc/trm suffixes) file generation is to set the hidden parameter _disable_health_check to TRUE. As with any hidden/underscore parameter, do NOT set this without approval from Oracle support (at least not in production). Another way which is easier, is to run regular PURGE jobs via adrci, which we will get to in another post.

Problems and Incidents

Oracle and the ADR nicely track problems and incidents. It is important to note that they are not one and the same. Problems are critical errors in the database (eg ORA-00600, 07445, 04031, etc). An incident is a single occurrence of a problem. When an incident occurs, Oracle creates incident trace files in addition to the normal alert log message and error trace file. You'll see this noted after the "Incident details in:" line in the alert log. For example:

Incident details in: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_12201/orcl_ora_18457_i12201.trc

However, there is a smarter way to look for incidents and alerts than scraping the human-readable alert log like some kind of animal. We'll use the ADRCI.

ADRCI

ADRCI is the command-line tool to navigate and manage the ADR. The name itself stands for ADR Command Interpreter. When you launch ADRCI, you have to point it to a specific home directory to work under. You first get the list of homes with the "show homes" command, and then set the home you wish to work on. E.g.:

[oracle@localhost ~]$ adrci

ADRCI: Release 11.2.0.2.0 - Production on Fri Dec 28 14:32:41 2012

ADR base = "/home/oracle/app/oracle"

adrci> show homes

ADR Homes:

diag/rdbms/orcl/orcl

diag/tnslsnr/localhost/listener

adrci> set home diag/rdbms/orcl/orcl

Now I can view incident and alert log information pertaining to my database home. If I wanted to troubleshoot problems with my listener, I would set the home to that directory instead.

ADRCI HELP

Everything you want to know about ADRCI commands can be found by using the help facility with in ADRCI, it is really very well documented. I'm not going to explain everything you can do with ADRCI here, but do want to cover a few of the more useful commands. One listing I would take the time to digest is:

adrci> help show alert

You'll be glad you did. And that takes us into ...

Viewing Alert Log with ADRCI

One of the most useful features of ADRCI is being able to view the alert log. You can even use it to view a running tail of the alert log (even on Windows!). Everything starts with the "show alert" command. By default this will open the entire alert log in an external text editor (like Notepad or vim, it is based on the editor option), unless the "-term" option is specified. To get our running tail, we would use this command:

adrci> show alert -tail -f

Note that the -tail option implies the -term.

Now this by itself isn't anything new. Where the real power comes in is in the ability for adrci to show alert log entries based on predicate searches. Again, the "help show alert" command will show you all of the options, but one quick example would be this:

adrci> show alert -p "message_text like '%ORA-%'" -term;

This will return any alert log entries that have a message body containing an ORA- error. This is much smarter than running grep on the text alert log since it includes message timestamps and other contextual information than just getting a line of text. One thing to note is that the search is case-sensitive, and so far I haven't seen any way around that (no upper or lower function in adrci). You can also do patter-matching on other fields, such as host_id, user_id, originating_timestamp, etc.

For example, to get a list of all alert log messages in the last hour:

adrci> show alert -p "originating_timestamp >= systimestamp-1/24" -term;

Or, to get all ORA-600 messages in the last 30 days:

adrci> show alert -p "originating_timestamp >= systimestamp-30 and message_text like '%ORA-600%'" -term;

It should be obvious by now that this is perfect for alert log monitoring scripts. ADRCI can execute scripts and spool output to log files as well, just as SQL*Plus does. This leads us to ...

What about those incidents?

Ah yes. This all started because we wanted a quick way to find incidents reported in our database. Well, here it is:

adrci> show incident

That's it. You'll get a list of the incidents recorded in the ADRCI (based on the available XML data), including the incident ID, problem key and time of the incident. Later in this series, I'll set up an incident to demonstrate how a typical on-call DBA can use ADRCI for quick troubleshooting and information gathering using the incident ID.

ADRCI Scripts

I'm the master of the segue. Anyway, as I said, ADRCI can call scripts just like SQL*Plus. And, like SQL*Plus, it uses the "@" sign to execute them interactively:

adrci> @/home/dts/scripts/test.adrci

You can also tell adrci to execute a script from the command line, like this:

$ adrci script=/home/dts/scripts/test.adrci

Here's an example script that you might use to monitor the alert log for ORA errors in the past 5 minutes and write them to a log file:

# ADRCI script to find alert log errors

SPOOL /home/dts/logs/alert_log_errors.log

ECHO "ALERT LOG ERRORS:";

SET HOMEPATH diag/rdbms/orcl/orcl;

SHOW ALERT -TERM -P "MESSAGE_TEXT LIKE '%ORA-%' AND ORIGINATING_TIMESTAMP >= SYSTIMESTAMP-5/1440";

SPOOL OFF

The semi-colon line terminator is optional, the script runs the same with or without them. As you can see, the pound character "#" is used to start comments, and the "echo" command just outputs text. Output from one example run on my sandbox is:

ALERT LOG ERRORS:

ADR Home = /home/oracle/app/oracle/diag/rdbms/orcl/orcl:

*************************************************************************

2012-07-12 11:40:06.309000 -05:00

ORA-1543 signalled during: create temporary tablespace migtemp

tempfile size 1G...

For my next article, however, we'll do a quick look at how the ADR purges old log files, and when it might not do so.

30 January 2013 ADDENDUM: X$DBGALERTEXT

I was working on the rest of this series when I realized I forgot to mention X$DBALERTEXT. You (or your monitoring scripts) can query X$DBALERTEXT to get the same information as ADRCI does. This is an external table that references all of the log*.xml files, and so is dependent on those files being where they should be (just like ADRCI). According to MOS Doc ID 961682.1, this data is "effectively indexed by date," so date-based queries are "efficient and performant."

There is an inst_id column in this table, but from my tests it is NOT RAC-aware. The only data I could see was from the local node.

As of Oracle 11.2, there is a related view named V$DIAG_ALERT_EXT, which is for all intents and purposes, the same as X$DBGALERTEXT. However the new view includes alert information from ALL homes under the ADR (ASM, listeners, clients, etc.).

Learning to Love the ADR - Part 1: Location, Location, Location!

2012-10-26T15:07:00.001-05:00

This post is the first in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.

The Automatic Diagnostic Repository, or "ADR", is a location on disk where an Oracle instance keeps many files of diagnostic data that tools like Enterprise Manager or ADR Command Interpreter (adrci) use for gathering and presenting information to the user. When we refer to diagnostic files, we are talking about:

Alert logs (RDBMS instance, ASM instance, Listener)
Trace files (e.g. 10046 or 10053 as well as system-generated)
Core dumps

Now in prior versions of Oracle, you would expect to find these files under $ORACLE_BASE/admin/$ORACLE_SID in bdump, cdump or udump, by default. You could always specify different locations with the %_DUMP_DEST initialization parameters, of course. The log files would grow ad infinitum if not properly rotated, compressed, archived and/or truncated, possibly causing some headaches with disk space management.

As of Oracle 11g, however, much (but not all) of this is managed by the instance now. As an added bonus, Oracle moved the locations of these files, as I'm sure many of you with alert log monitoring software have found out by now. The thing that catches most DBAs off-guard when upgrading to 11g is the change in location for these files, especially the alert log.

Well, Oracle Corporation has seen fit to unify all these various alert, trace and dump files under the ADR location, which is specified in the DIAGNOSTIC_DEST initialization parameter. This one parameter replaces all of the %_DUMP_DEST parameters, and defaults to the value of the $ORACLE_BASE environment variable at the time of instance startup. If the $ORACLE_BASE environment variable is unset, then DIAGNOSTIC_DEST defaults to $ORACLE_HOME/log.

Beware the ORACLE_BASE

Note that I emphasized "at the time of instance startup" earlier. This is important because you may come across servers where the ORACLE_BASE variable might not be defined when the instance is first started. In this case the ADR will be under $ORACLE_HOME/log. Then a DBA consultant might come along and standardize the login profile with an ORACLE_BASE setting, and upon next startup the DIAGNOSTIC_DEST will move. This can make things confusing for DBAs trying to find older diagnostic files in the new ADR location if they don't know (and rarely expect) this kind of situation. Yet it is one that I have personally witnessed and was one of the initial motivations for me to put this presentation together and learn more about the ADR for my own benefit.

Let's demonstrate this quickly. I have my working 11.2.0.1 sandbox on Oracle Linux. I set the full slate of environment vars via oraenv:

[oracle@localhost ~]$ . oraenv

ORACLE_SID = [oracle] ? ora11201

The Oracle base for ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1 is /oracle/app/oracle

Then I launch sqlplus and start the instance, then check the diagnostic_dest parameter value:

SQL> show parameter diagnost

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

diagnostic_dest string /oracle/app/oracle

That is as expected. I can verify that my ADR files exist under there

[oracle@localhost ~]$ ls /oracle/app/oracle/diag/rdbms/ora11201/ora11201/

alert cdump hm incident incpkg ir lck metadata stage sweep trace

Now, shutdown the database and create a pfile from the spfile (if you use an spfile). Then be sure rename the spfile so it isn't used during startup. Next we unset the necessary initialization parameters and environment variable.

[oracle@localhost dbs]$ echo $ORACLE_BASE

/oracle/app/oracle

[oracle@localhost dbs]$ unset ORACLE_BASE

[oracle@localhost dbs]$ echo $ORACLE_BASE

[oracle@localhost dbs]$ vi initora11201.ora

[oracle@localhost dbs]$ grep "#" initora11201.ora

#ora11201.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment

#*.diagnostic_dest='/oracle/app/oracle'

Note I also had to comment out the __oracle_base instance parameter value in the pfile. At this point our instance will have no knowledge or ties to the old ORACLE_BASE. Now, log back into sqlplus and startup (or even just startup nomount) and check again:

SQL> show parameter diagnostic_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

diagnostic_dest string /oracle/app/oracle/product/11.

2.0/dbhome_1/log

You can see we're now using the value of $ORACLE_HOME/log. Look under that directory and you'll indeed find the full set of ADR files you would expect:

[oracle@localhost dbs]$ ls /oracle/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/ora11201/ora11201/

alert cdump hm incident incpkg ir lck metadata stage sweep trace

The only problem is that it doesn't have any of the history contained in the files in the old ADR.

The bottom line? Be careful and make sure your $ORACLE_BASE is properly set and I strongly advise you to explicitly set your diagnostic_dest initialization parameter so it doesn't rely on environment variables.

V$DIAG_INFO

Other than checking the diagnostic_dest value, you can also query the view V$DIAG_INFO to see where everything is going:

SQL> select name, value from v$diag_info;

NAME VALUE

--------------------- ---------------------------------------------------------

Diag Enabled TRUE

ADR Base /oracle/app/oracle

ADR Home /oracle/app/oracle/diag/rdbms/ora11201/ora11201

Diag Trace /oracle/app/oracle/diag/rdbms/ora11201/ora11201/trace

Diag Alert /oracle/app/oracle/diag/rdbms/ora11201/ora11201/alert

Diag Incident /oracle/app/oracle/diag/rdbms/ora11201/ora11201/incident

Diag Cdump /oracle/app/oracle/diag/rdbms/ora11201/ora11201/cdump

Health Monitor /oracle/app/oracle/diag/rdbms/ora11201/ora11201/hm

Default Trace File /oracle/app/oracle/diag/rdbms/ora11201/ora11201/trace/

ora11201_ora_16064.trc

NAME VALUE

--------------------- ---------------------------------------------------------

Active Problem Count 0

Active Incident Count 0

11 rows selected.

Deprecated Variables

Now the old %_DUMP_DEST initialization parameters are deprecated and will default to values based on the DIAGNOSTIC_DEST value. But, while I'm here, let's test another question that's rattled around my brain: What happens if we set both DIAGNOSTIC_DEST and BACKGROUND_DUMP_DEST to different paths?

To test, I quickly made a bdump directory in my user home and set the old background_dump_dest value in the pfile:

[oracle@localhost dbs]$ grep background_dump_dest initora11201.ora

*.background_dump_dest='/home/oracle/test_bdump';

I'll note that I've also uncommented the diagnosted_dest value from before. Now, we startup and we immediately get a note that we're using a parameter that some might call "old and busted", but the instance does start as normal:

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Now when we check, we see that Oracle just completely ignored the setting we specified for the old parameter:

SQL> show parameter diagnostic_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

diagnostic_dest string /oracle/app/oracle

SQL> show parameter background_dump_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

background_dump_dest string /oracle/app/oracle/diag/rdbms/

ora11201/ora11201/trace

Disabling ADR

Now, it is possible to disable the ADR and go back to the way things were prior to Oracle 11g. First you'll need to set the hidden parameter _diag_adr_enabled to FALSE, for example if using spfile:

SQL> alter system set "_diag_adr_enabled"=false scope=spfile;

System altered.

Then you MUST set the old %_DUMP_DEST parameters to point to the desired locations. These directories must exist and be writable by oracle, obviously.

SQL> alter system set background_dump_dest='/oracle/app/oracle/admin/ora11201/bdump' scope=spfile;

System altered.

SQL> alter system set core_dump_dest='/oracle/app/oracle/admin/ora11201/cdump' scope=spfile;

System altered.

SQL> alter system set user_dump_dest='/oracle/app/oracle/admin/ora11201/udump' scope=spfile;

System altered.

Then we restart the database instance for the settings to take effect. I've tested this on my 11.2.0.1 sandbox (these are the commands from that very session) and it works. Note that you will again see the ORA-32004 warning about having a deprecated parameter set, that is to be expected. However, this time the settings take effect:

SQL> show parameter dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/app/oracle/admin/ora11
201/bdump
core_dump_dest string /oracle/app/oracle/admin/ora11
201/cdump
user_dump_dest string /oracle/app/oracle/admin/ora11
201/udump
SQL> show parameter "_diag_adr_enabled"

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_diag_adr_enabled boolean FALSE

HOWEVER, I do not advise doing this in production at all, and certainly not without the blessing of Oracle Support for any reason. I simply don't think there is anything to be gained by reverting to this method, and you lose the flexibility and power of having the alert data in XML format, which we'll see how to harness in a future post!

This ends Part 1 of my ADR intro series, which focused ADR location(s). Part 2 will focus on diagnostic files and how Oracle uses and maintains them.

Me at RMOUG

2012-02-16T13:11:00.004-06:00

Here's a quick picture of me during my RMOUG 2012 presentation, giving a tour of the Oracle 11g ADR:

Thanks to Kellyn Pot'Vin for the picture!

A NoCOUG to Remember

2012-02-15T14:57:00.000-06:00

This post is long overdue, as I was supposed to blog about my appearance at NoCOUG before I left (sorry, Vanessa!). However in my efforts to rehearse and adjust my presentation, blogging about it just fell to the wayside. However now that NoCOUG 2011 Summer Conference is in the books, I'd like to take a few minutes to share my experience not only as an attendee, but also as a first-time speaker.

When I found out that NoCOUG had accepted my abstract, "Oracle 11g: Learning to Love the ADR", I was both ecstatic and terrified. This meant that I actually had to prepare the presentation and speak in front of peers. Surely they would throw me into San Francisco Bay if I didn't bring my A-game, so I set out to do just that.

Step 1: Learning to Love the ADR

The fact is that I chose to speak on the ADR because I knew very little about it myself. This would not only get me into the glorious world of Oracle conference speakers, complete with a lifestyle rivalled only by Caligula, but it would also ensure that I knew as much as I could about the ADR. Until this project, I had only known where to find the human-readable alert log so I could continue on with my Oracle 10g style of monitoring! I had heard tales of creating "packages" for Oracle support, but surely this was a complex, Herculean feat that would take many months of meditation to even begin to understand.

So I scoured Oracle Support notes and the public documentation to get notes on any aspect I could about the ADR. Once I felt I had enough source material, it was time to build an outline for my slides to follow. After that, it was time to eat my own dog food, so I got busy poking around the ADR and playing with adrci on my Oracle 11.2 sandbox, while concurrently applying some new-found knowledge on client installations. The latter helped me find a bug involving listener alert log purging!

Step 2: The Slides

The next step was actually creating a deck of slides. Using the Pythian template and Libre Office 3.4.2, I slowly figured out how this done in the most simple format possible. This means it was all text, baby. No cool stock photos or cute puppy clip-art. That's something I'll work on for next time and/or another topic.

Once I had a first draft of my slides and did a self-rehearsal, I enlisted the help of my Pythian colleagues in our Sydney office to be my test audience to not only judge my presenting pace & speech, but also criticize the content of my presentation. And they did the latter wonderfully, each asking great questions that I hadn't previously considered and making some great suggestions on where to provide a little more info and offering anecdotes from their own ADR experiences. I can definitely say that they made my presentation twice as good as it was before! After implementing those changes, I did another dress-rehearsal for some more colleagues around the world, got a few more notes, and I felt like it was ready.

Step 3: The Conference

When I got to the conference in San Ramon, I especially keen to meet Jonathan Lewis for the first time. Jonathan is one of the people in the Oracle world that I respect the most, and it was a joy not only to be in his presence but to also later share a lunch table with him. I was also delighted to finally meet my colleague Chen Shapira, one of the most brilliant DBAs we have at Pythian, which is saying quite a lot!

Jonathan was, naturally, the keynote speaker. He gave fantastic talk on two-table joins (after all, all joins eventually become two-table joins). You can see a video of a similar two-table join optimization presentation that he gave to the Turkish OUG earlier this year. Fantastic improvements are possible when you break things down to their simplest forms. The general idea reminded me also of this great video by Stephane Faroult, but Jonathan does go into much more terrific detail.

After the keynote, I stayed for Jonathan's second session, which was titled "A Beginner's Guide to Becoming an Expert", with emphasis on becoming, rather than being. This was another great session that emphasized on having easily reproducable tests. Jonathan noted that the script he was using could build a set of tables with over 6.5 million rows on his laptop in under a minute, showing that it doesn't take a lot of effort to have such scripts written and readily available whenever you need to test something. His scripts directory contained over 2600 SQL scripts for all manner of scenarios. One needs to be in the mindset to test repeatedly, making slight changes between tests and understanding the difference in results.

He included a demonstration of how Oracle actually fails the ACID test, which he blogged about afterward, definitely give it a read.

After an outdoor lunch, I decided to pass on the next session to go over my slides again and ensure that everything was working on my laptop as expected. One other session I was really interested seeing was Kyle Hailey's NFS Tuning session, but unfortunately it was at the same time as my own session.

Step 4: Spreading the Love

When the time came for my session, I felt very prepared. I did catch myself rushing through words once in a while, but otherwise felt my pace was good. There were a few questions from the audience that I couldn't immediately answer, but was able to find the answer for during the post-session ad-hoc demo that I went through for those that stuck around and had extra questions. Having a live instance to work on was incredibly valuable to the presentation and helped not only to reinforce the information that I was sharing, but also to test scenarios that audience members came up with that I hadn't previously illustrated.

There was some great Q & A at the end of the session, as well as after the session. As I said earlier, I stayed around answering questions and going through new scenarios for some time afterward. Even after packing up and moving out of the room I was speaking with people who had more questions about the ADR, even some that hadn't attended my session! I spent so much time talking, in fact, that I was late for the last sessions and so decided to just catch up on email rather than burst in late and distrub the presenters. As you can see, I ended up only attending Jonathan Lewis' two sessions in addition to my own. Not a bad day altogether, except for losing my jacket (black Outdoor Research spring jacket, if anyone finds one!).

Unfortunately I was taking the red-eye back to the midwest that night so I didn't get to do any sightseeing aside from what I got to see from the BART train. But I did enjoy this change from home:

I could get used to the San Francisco consistency.

It's like a year-round Wisconsin springtime. For those interested, I did provide some twitter updates between sessions. I didn't want to live-tweet because it would have been harder for my simple mind to follow along.

You can download the slides from my presentation, as well as a few others, from the NoCOUG website. I do plan to also record a webinar of this presentation, after I incorporate a few more changes, later this year.

In Conclusion ...

Overall, I think it was a very worthwhile experience. I accomplished my goal of learning about the ADR well enough to speak confidently and competently on it. I also accomplished my year's goal of submitting to 3 conferences and my career goal of becoming an Oracle speaker. I definitely look forward to speaking more on this and other topics at future conferences as well as presenting internally to the rest of my Pythian colleagues.

Upgrading Standalone ASM to Oracle Grid Infrastructure 11.2.0.2? Beware Bug 10283819!

2012-02-15T14:53:00.000-06:00

No, this isn't a re-post of my earlier blog about bug 1233183.1. We've found a fun new bug that seems to be specific to our poor standalone ASM instances when upgrading from Oracle Grid Infrastructure 11.2.0.1 to 11.2.0.2.

The bug was first brought to my attention about four days after completing the Grid Infrastructure upgrade. The client system administrator (SA) noticed that the disk holding the Oracle home directories was slowly filling, at the rate of about 1Gb per day. We identifed that core dump files being created under the new GRID_HOME/log/diskmon/ directory, at the rate of about 1 every 10 minutes, each one about 8M in size. That adds up to 1152M (or just over 1Gb) per 24-hour day. Add that to the 8Gb that was being held in GRID_HOME/.patch_storage (we had to rollback the 11.2.0.1 April 2010 PSU and apply the 11.2.0.1 July 2010 PSU just to upgrade to 11.2.0.2), and that put a bit of a squeeze on the free disk.

The good ol' OTN forums led me to bug 10283819. The original poster there shared also that removing the old (11.2.0.1) grid home directory and restarting diskmon services stopped the core dump creation. The poster then went to question a second issue with increased diskmon.log writing. After a solution was found for that, Oracle Support closed the bug for some reason, without ever addressing the core dump creation.

I can verify that removing the old 11.2.0.1 grid home (I did a tar+bz2 first) and restarting the services did stop the core dump creation, and am pushing back to Oracle support to get the bug re-opened or a new bug filed to specifically address this. In the meantime, if you are unable or unsure about removing the old grid infrastructure home, it should be safe to have a regularly scheduled script remove the diskmon core dump directories and save you a full disk surprise late some night.

Using a Custom Timezone? Beware Oracle 11.2.0.2 Grid Infrastructure!

2012-02-15T14:52:00.000-06:00

We have a client that runs an application that, for whatever reasons, does NOT like daylight saving time. For that reason, the Oracle server is kept in Eastern Standard Time and does not change with the rest of the eastern United States when DST begins and ends every year. They accomplish this with a custom /etc/localtime file. However, they left /etc/sysconfig/clock set to "TZ=America/New_York," which would prove fateful as I shall point out. So, with the custom localtime file, the "date" command as well as selecting sysdate or systimestamp would always return the current time in Eastern Standard Time. When it is Daylight Saving Time, as it is right now, this would be one hour behind "real" time as we consider it.

Now, we recently upgraded this client from Oracle Grid Infrastructure (for single-instance ASM) from 11.2.0.1 to 11.2.0.2. The next business day, the client alerted us that their date fields were coming back in Eastern Daylight Time. While this time was still technically right, they needed the time in the EST timezone.

I first set about trying to duplicate the problem. I was able to see the same incorrect results when I connected to the database remotely (e.g. via sqlplus or Oracle SQL Developer over TNS), but not locally (i.e. "sqlplus / as sysdba"). Then I duplicated the problem when connecting locally via TNS, meaning I was going through the listener. So we had narrowed it down to only connections going through the listener. I hadn't considered the listener to be aware of timezones, so this was rather mind-boggling for me.

Here is an example of the incorrect results we saw:
SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 14-JUN-11 07.38.31.711902 PM -04:00 SQL> !date Tue Jun 14 18:38:33 EST 2011
The system date always returned the value we wanted, but now SYSTIMESTAMP (and SYSDATE, and any other date values stored in the database) were coming back in EDT.

My Pythian colleague Marc Fielding found My Oracle Support document ID 1209444.1, which had these two eye-opening lines:

1. For 11.2.0.1, shell environment variable TZ is set correctly for grid user and root.
2. For 11.2.0.2 and above, TZ entry in $GRID_HOME/crs/install/s_crsconfig__env.txt sets to correct time zone.

Looking in the s_crsconfig_*_env.txt file on these hosts, I saw this:
TZ=America/New_York
The workaround was to change the TZ value in this file to "EST5" and perform a quick restart the HAS daemon:
# /etc/init.d/ohasd stop # /etc/init.d/ohasd start
After doing this, I liked what I was seeing:
SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 14-JUN-11 06.42.16.384596 PM -05:00 SQL> !date Tue Jun 14 18:42:19 EST 2011

Marc and I believe that the Grid Infrastructure installer grabs the value in /etc/sysconfig/clock when setting up the env file in question. We've asked the client to ensure that /etc/sysconfig/clock is always properly set in the future.

We imagine that most places don't try to fight Daylight Saving Time this way, but the bug also applies if you are doing any kind of timezone slight-of-hand, like telling your database it is in US Central time when the server might be in US Pacific time. So if your organization is doing this, be sure to double check the crsconfig file after 11.2.0.2 installation!

Vote for my session for Oracle OpenWorld 2011!

2011-06-03T14:26:00.000-05:00

Well I didn't make the judges cut, but through Oracle Mix's Suggest-A-Session, you can still send me to OOW by voting for my session, which is unofficially titled, "Making Love to the ADR ... the Don Seiler Way." The official title is much less romantic.

Cheers!

Upgrading Standalone ASM to Oracle Grid Infrastructure 11.2.0.2? Beware Bug 1233183.1!

2011-01-03T11:28:00.000-06:00

NOTE: This post originally appeared on the Pythian blog.

The past four days have found me very frustrated and at wits' end while testing upgrades of standalone Oracle Grid Infrastructure (ASM) 11.2.0.1 to 11.2.0.2 on RHEL/OEL 5 VMs. The upgrade would seem to go fine, but after rebooting, I would see ASM and LISTENER running under the old (11.2.0.1) grid home directories again.

Looking at /etc/oratab, I saw this:

$ grep -i asm /etc/oratab
+ASM:/u01/app/grid/product/11.2.0/grid_1:N # line added by Agent

grid_1 is the old grid home, I expect to see grid_2. The comment about being added by Agent led me to a path where I eventually took a look at /etc/init.d/ohasd, which is basically the master script that starts everything up. I noticed that this file hadn't been updated as part of the patching, and contained this:

$ grep -i crs_home /etc/init.d/ohasd
ORA_CRS_HOME=/u01/app/grid/product/11.2.0/grid_1
export ORA_CRS_HOME

I then ran some web searches for "oracle upgrade 11.2.0.1 ohasd" and found a blog post that had the same problem. Searching My Oracle Support then turned up DocID 1233183.1, titled "Standalone GI: init.ohasd/ohasd not updated after 11201 to 11202 upgrade".

The bug is basically what it says, those files are not being updated during upgrades of standalone grid infrastructure. This is due to a logic bug in roothas.pl. I suggest reading the document for details.

The workaround is to manually copy those two files after the upgrade finishes. First backup the old files:

$ mkdir ~/ohasd_init_backup
$ cd ~/ohasd_init_backup
$ ls /etc/init.d/*ohasd*
/etc/init.d/init.ohasd /etc/init.d/ohasd
$ cp /etc/init.d/*ohasd* .
$ ls
init.ohasd ohasd

Then copy the 11.2.0.2 files into place (as root):

# cd /etc/init.d/
# cp /u01/app/grid/product/11.2.0/grid_2/crs/init/init.ohasd .
cp: overwrite `./init.ohasd'? y
# cp /u01/app/grid/product/11.2.0/grid_2/crs/init/ohasd .
cp: overwrite `./ohasd'? y

Obviously, substitute "/u01/app/grid/product/11.2.0/grid_2" with the path your 11.2.0.2 installation directory. NOTE: SLES users need to copy ohasd.sles, rather than ohasd. See the MOS document for details

Now, a quick check to make sure the proper home is used:

$ grep -i crs_home /etc/init.d/ohasd
ORA_CRS_HOME=/u01/app/grid/product/11.2.0/grid_2
export ORA_CRS_HOME

Now ensure that the two new scripts have the same ownership and permissions as the old ones. Then reboot to ensure that everything takes effect. After the server comes back up, ensure that all services and oratab are still pointing to the new grid home. Be sure to check "srvctl config" for the asm and listener services, and check the paths in /etc/oratab.

This is the second bug with the 11.2.0.2 upgrade process that I've encountered. The first one requires patching the 11.2.0.1 Grid Infrastructure with the July 2010 PSU just to be able to upgrade to 11.2.0.2. Let's hope for some stronger QA in the future.

I Do Not Think It Means What You Think It Means

2010-12-27T17:25:00.002-06:00

Arup Nanda posted a great blog article about one of the most fundamental, yet misunderstood, behaviors of the Oracle database: the relationship of the COMMIT statement and buffer caches being flushed to disk. I plan to re-read this every day for month. This clip seems apropos:

The article is titled "100 Things You Probably Didn't Know About Oracle Database," and I'm definitely looking forward to the other 99 articles.

Back to Blogger!

2010-11-19T22:45:00.001-06:00

With the recent announcement of Google Apps for Domains integration with the rest of the Google tools suite, I've decided to further simplify my life and run this blog on Google's blogger utility. I've been considering coming back for a while, given that Wordpress doesn't let me do everything I want (e.g. Amazon associate links) and now Blogger does what I wanted before (e.g. Pages).

The big task now is to find a way to import from Wordpress to Blogger. I can import everything from my previous Blogger blog, but might have to manually copy posts since then (3 years ago). I don't know that there are that many since then, so it might not be too bad. Plus I'm on vacation this week.

Beware the /var/tmp/.oracle Hidden Directory!

2010-11-12T14:29:00.005-06:00

NOTE: This post originally appeared on the Pythian blog.

A few months ago, we had a test instance complaining that it couldn't write to ASM. This was an 11.1.0.7 single (non-RAC) instance on Oracle Enterprise Linux 5, using ASM for the storage. We first saw these errors in the alert log:

ORA-15032: not all alterations performed

ORA-29702: error occurred in Cluster Group Service operation

ERROR: error ORA-15032 caught in ASM I/O path

Uh-oh, that doesn't look good. So I log into the ASM instance and try to see if the disks are OK:

SQL> select path, mount_status from v$asm_disk;

select path, mount_status from v$asm_disk

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-29702: error occurred in Cluster Group Service operation

ORA-29702: error occurred in Cluster Group Service operation

I can't even query that. As Ted would say, "strange things are afoot at the Circle K."
To be safe, I thought I'd try to shutdown the DBMS instance, which also failed without having to abort:

SQL> shutdown immediate

ORA-00204: error in reading (block 1, # blocks 1) of control file

ORA-00202: control file: '+FOOTEST_DATA/footest1_footest_db/control01.ctl'

ORA-15081: failed to submit an I/O operation to a disk

SQL> shutdown abort ORACLE instance shut down.

We decided to restart the whole DBMS/ASM/CSS stack, but CSS wouldn't stop either:

-bash-3.2# /etc/init.d/init.cssd stop

Stopping Cluster Synchronization Services.

Unable to communicate with the Cluster Synchronization Services daemon.

Shutdown has begun. The daemons should exit soon.

We ended up booting the server altogether, after which everything came up nicely. We filed an SR with Oracle Support, who directed us to Note 391790.1 (Unable To Connect To Cluster Manager Ora-29701). This note lists the cause, quite simply, as:

The hidden directory '/var/tmp/.oracle' was removed while instances & the CRS stack were up and running. Typically this directory contains a number of "special" socket files that are used by local clients to connect via the IPC protocol (sqlnet) to various Oracle processes including the TNS listener, the CSS, CRS & EVM daemons or even database or ASM instances. These files are created when the "listening" process starts.

The solution is to restart CRS or reboot the machine. Our /var/tmp/.oracle directory looked like this:

[oracle@footest ~]$ ls -la /var/tmp/.oracle

total 12

drwxrwxrwt 2 root root 4096 May 8 15:03 .

drwxrwxrwt 3 root root 4096 May 10 07:02 ..

srwxrwxrwx 1 oracle dba 0 May 8 15:03 s#18854.1

srwxrwxrwx 1 oracle dba 0 May 8 15:03 s#18854.2

srwxrwxrwx 1 oracle dba 0 May 8 15:03 sEXTPROC

srwxrwxrwx 1 oracle dba 0 May 8 14:44 sfootestDBG_CSSD

srwxrwxrwx 1 oracle dba 0 May 8 14:44 sOCSSD_LL_footest_

srwxrwxrwx 1 oracle dba 0 May 8 14:44 sOCSSD_LL_footest_localhost

srwxrwxrwx 1 oracle dba 0 May 8 14:44 sOracle_CSS_LclLstnr_localhost_0

srwxrwxrwx 1 oracle dba 0 May 8 15:03 sPNPKEY

I did some sandbox testing, and found that only the Oracle and root OS users could delete that directory, and was able to duplicate the error every time when doing so.

However, I really was dumbstruck that Oracle would have so critical a directory in /var/tmp! I politely note this to Oracle Support, who justified this location with a few solid reasons:

It has always been in this location (and still is in 11gR2).
/var/tmp/.oracle is a hidden directory, so it probably won't be noticed by any miscreants looking to cause trouble.

OK, I was being sarcastic, these reasons are awful. The only safeguard they gave was "make sure no one deletes it." We scoured the server for cron jobs that would automatically clean out /var/tmp but didn't find any, nor any bash history suggesting malice. The only thing that we could think of was that this test server was in a VM (Citrix Xen), although one would hope that it doesn't happen at all, regardless. We certainly could not find an explanation, but now we're aware to not delete /var/tmp/.oracle while the instances are running (even though we never did before).

Surachart Opun has also blogged on this topic.

Installing Oracle 11gR2 Enterprise Edition on Ubuntu 10.04 (Lucid Lynx)

2010-06-30T15:37:00.007-05:00

Note: This post originally appeared on The Pythian Group blog.

I recently reformatted my laptop with the latest Ubuntu LTS release, 10.04, aka Lucid Lynx. Since I like to have a native client installation as well as a portable sandbox server, I decided to install the latest version of Oracle EE, 11.2.0.1.

Rather than re-invent the wheel, I'm going to direct you to the previous Oracle-on-Ubuntu post by my colleague Augusto Bott. Many of the directions there hold true here (even with 32-bit vs 64-bit), with a few exceptions.

Download the Software

First and foremost, download the 32-or-64-bit installer files (there are 2 of them) from OTN.

Installing Pre-requisite Packages

Install these software packages on your Ubuntu 10.04 system:

$ sudo apt-get install unzip build-essential x11-utils rpm ksh lsb-rpm libaio1

Ubuntu 10.04 comes with libstdc++6 installed. However, Oracle 11gR2 requires libstdc++5. If you do not install libstdc++5, you will see errors as described in this OTN thread. The fix, as described in that thread, is to download and manually shoehorn the libstdc++5 library files onto Ubuntu 10.04:

$ wget http://mirrors.kernel.org/ubuntu/pool/universe/g/gcc-3.3/libstdc++5_3.3.6-17ubuntu1_amd64.deb

$ dpkg-deb -x libstdc++5_3.3.6-17ubuntu1_amd64.deb ia64-libs

$ sudo cp ia64-libs/usr/lib/libstdc++.so.5.0.7 /usr/lib64/ $ cd /usr/lib64/

$ sudo ln -s libstdc++.so.5.0.7 libstdc++.so.5

$ wget http://security.ubuntu.com/ubuntu/pool/universe/i/ia32-libs/ia32-libs_2.7ubuntu6.1_amd64.deb

$ dpkg-deb -x ia32-libs_2.7ubuntu6.1_amd64.deb ia32-libs

$ sudo cp ia32-libs/usr/lib32/libstdc++.so.5.0.7 /usr/lib32/

$ cd /usr/lib32 $ sudo ln -s libstdc++.so.5.0.7 libstdc++.so.5

Now we're ready to move on.

Create User and Groups

First create the oinstall and dba groups:

$ sudo su - # addgroup oinstall # addgroup dba

Then create the oracle user and assign it to those groups:

# useradd -g oinstall -G dba -d /home/oracle -s /bin/bash oracle
# passwd oracle

Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
# mkdir /home/oracle

# chown -R oracle:dba /home/oracle

Now we create some symbolic links to give the Ubuntu system a more "Red Hat-ish layout," as Augusto put it:

# ln -s /usr/bin/awk /bin/awk

# ln -s /usr/bin/rpm /bin/rpm

# ln -s /usr/bin/basename /bin/basename

# mkdir /etc/rc.d

# for i in 0 1 2 3 4 5 6 S ; do ln -s /etc/rc$i.d /etc/rc.d/rc$i.d ; done

# mkdir -p /u01/app/oracle

# chown -R oracle:dba /u01

Then we update some sysctl parameters by editing /etc/sysctl.conf. It is wisest to backup this file first and then add these lines to the end of /etc/sysctl.conf:

fs.file-max = 6815744

fs.aio-max-nr = 1048576

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.core.rmem_default = 4194304

net.core.rmem_max = 4194304

net.core.wmem_default = 1048576

net.core.wmem_max = 1048576

net.ipv4.ip_local_port_range = 9000 65535

Note that these values are different from what was needed in 11gR1.

Now we update some limits for the oracle software owner by adding these lines to the end of /etc/security/limits.conf:

oracle soft nproc 2047

oracle hard nproc 16383

oracle soft nofile 1023

oracle hard nofile 65535

Again, it is best to backup the file first.

Now we activate the new settings from /etc/sysctl.conf with this command:

# sysctl -p

You should see the new settings in the output of that command.

Unpack and Install the Software

From here we go into the directory containing our two 11gR2 database zip files. I'm using the 64-bit edition, so my files are named linux.x64_11gR2_databaseXof2.zip, where X is 1 or 2. I unzip both of these files, which creates a "database" subdirectory:

$ unzip linux.x64_11gR2_database_1of2.zip

$ unzip linux.x64_11gR2_database_2of2.zip

Each of these commands will produce a large volume of output to the terminal as it lists each file being unpacked. Once this is done, your directory should look something like this:

$ ls -lh total 2.2G

drwxr-xr-x 8 seiler seiler 4.0K 2009-08-20 14:34 database

-rw-r--r-- 1 seiler seiler 1.2G 2010-03-09 11:33 linux.x64_11gR2_database_1of2.zip

-rw-r--r-- 1 seiler seiler 1.1G 2010-03-09 12:38 linux.x64_11gR2_database_2of2.zip

Now you simply cd into the database directory and run the runInstaller program:

$ cd database

$ ./runInstaller

This will launch the Oracle Universal Installer, or OUI, program. The rest is pretty straight forward. Since this is just a sandbox, I chose not to provide any email info and declined to receive updates. I chose to first install the software only, which went perfectly well. OUI will complain about missing packages, since it is checking for RPMs. We can safely ignore these and proceed with installation.

Once installation is done I set these variables in my bash environment:

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export ORACLE_SID=orcl

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

export EDITOR=/usr/bin/vi

Then some quick tests to verify installation:

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 10 23:46:42 2010

SQL> quit

$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory

Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.1.0.6.6

OUI version : 11.2.0.1.0

OUI location : /u01/app/oracle/product/11.2.0/dbhome_1/oui

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2010-06-10_23-46-53PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2010-06-10_23-46-53PM.txt

--------------------------------------------------------------------------------

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.1.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------

OPatch succeeded.

Everything looks hunky-dory. Let's create an instance.

I launched dbca to create an Oracle 11gR2 instance. The one problem I encountered here was that, at the end of dbca prompts, the "Confirmation" dialog window was blank. Other blog posts on the internet suggested blindly clicking around until you magically hit the "OK" button, but more often than not I would hit the "Cancel" button. Turns out this is a not-so-uncommon problem with Java Swing and Gnome's visual effects. The solution for Gnome users is to disable Visual Effects in the Appearance preferences before launching dbca. If you are using a lightweight desktop environment such as LXDE, you shouldn't have this problem.

And that's that. The rest is nothing new to those who have done installations before. You can optionally configure TNS names or Listener with the netca tool, or log into your new instance and enjoy.

Special thanks to Augusto Bott for not only authoring the previous Oracle-on-Ubuntu articles but also for suggesting the Gnome Visual Effects conflict!

Applying Oracle 11.2 April 2010 PSU for Single-Instance ASM and DBMS

2010-06-30T15:36:00.003-05:00

Note: This post originally appeared at The Pythian Group blog.

NOTE NOTE: Grid Infrastructure PSU 9343627 has since been revoked by Oracle, and replaced with a newer PSU. Do not apply PSU 9343627. I'm leaving this post up for educational purposes only.

When news of the April 2010 PSU for Oracle 11.2 came out, I was excited to see it, since it marked the first non-one-off patch release for the 11.2 database software. I happened to have an 11gR2 test system running on 11gR2 ASM via standalone Grid Infrastructure. I applied PSU 9352237 to the DBMS home and fired it up, only to see the folly of my ways when any ASM file operations like disk resizing (or auto-extending) failed with ORA-1653. This was due to the DBMS component now having a higher version number than the ASM component, which ASM does not allow. The Grid Infrastucture PSU would need to be applied to bring the ASM component up to snuff, but that patch (9343627) was, at that time, only "announced" with no ETA. Alas, the patch was rolled back and we continued testing without it.

Then this week I check again and saw that PSU 9343627 was released and gave it a whirl. I was a little confused when the README seemed to contain a lot of instructions that always assumed it to be on a clustered, RAC install. My setup was a single-instance Grid Infrastructure installation just to provide ASM. I soon met problem upon problem when going through first this setup step:

# GRID_HOME/crs/install/rootcrs.pl -unlock
2010-05-03 11:40:42: Parsing the host name
2010-05-03 11:40:42: Checking for super user privileges
2010-05-03 11:40:42: User has super user privileges
Using configuration parameter file: GRID_HOME/crs/install/crsconfig_params
CRS-4013: This command is not supported in a single-node configuration.
CRS-4000: Command Stop failed, or completed with errors.
You must kill crs processes or reboot the system to properly
cleanup the processes started by Oracle clusterware
The Oracle Clusterware stack failed to stop.
You should stop the stack with 'crsctl stop crs' and rerun the command

I decided to try the PSU anyway, thinking Oracle would certainly have noted in the README if this was a problem for single-node installations. Turns out I was wrong:

$ GRID_HOME/OPatch/opatch napply -local -oh GRID_HOME -id 9343627,9352237
Invoking OPatch 11.2.0.1.2

... snip ...

Running prerequisite checks...
Prerequisite check "CheckApplicable" failed.
The details are:

Patch 9343627:
Copy Action: Desctination File "GRID_HOME/bin/oradnssd" is not writeable.
'oracle.crs, 11.2.0.1.0': Cannot copy file from 'oradnssd' to 'GRID_HOME/bin/oradnssd'

UtilSession failed: Prerequisite check "CheckApplicable" failed.

OPatch failed with error code 73

I created an SR with Oracle support, and after reviewing my case agreed that the instructions were not right for single-instance Grid Infrastructure installations. They will be updating the README, but in the meantime I'd like to share the revised instructions that got me through two successful GI/DBMS PSU applications today.

Naming Conventions
First a few naming conventions to avoid confusion:

DBMS_HOME - The Oracle DBMS installation directory.
GRID_HOME - The Oracle Grid Infrastructure installation directory.

I've replaced all references to the directories in my examples with DBMS_HOME and GRID_HOME. Whenever you see those strings, substitute the actual path to your DBMS install or GRID install, whichever the case may be.

Download the PSU and OPatch 11.2
First, you'll obviously need to download patch 9343627 that contains the PSU for your platform. In my case that's Linux x86_64. As of this writing, I believe it's only available for Linux x86 and x86_64. Note that patch 9343627 also includes patch 9352237. You do not have to, and should not, download that PSU separately. Everything you need is in the 9343627 download.

Second, choose a working directory to unzip that file. In my case, this is /home/oracle/software/11gR2/psu/gi, which looked like this after unzipping the PSU zip file:

$ ls
9343627 9352237 p9343627_112010_Linux-x86-64.zip

Unlike normal one-off patches, where you would descend into the individual patch-number-named directories, you'll stay at this level.

Note also that you'll need OPatch 11.2 or higher. See MOS Doc ID 6880880 and get the latest version of OPatch for your platform. Then just unzip the file in the DBMS_HOME and GRID_HOME directories, overwriting the old OPatch directory.

Pre-Patch Steps
First I'm assuming that you have shut down all DBMS instances on this server. We will shut down ASM later in this process, so be sure to shutdown the DBMS instances now.

Now we'll start the pre-patch steps. First we stop the database on this server, saving the state of the database configuration in /tmp/oracle_home.stat:

$ DBMS_HOME/bin/srvctl stop home -o DBMS_HOME -s /tmp/oracle_home.stat

Repeat this for any additional Oracle database home installs on the server.

Next we save the database home configuration. This only needs to be done if we're also going to patch the DBMS home in addition to the GI home. This is done from the directory where we unzipped the PSU:

$ ./9343627/custom/server/9343627/custom/scripts/prepatch.sh -dbhome DBMS_HOME
./9343627/custom/server/9343627/custom/scripts/prepatch.sh completed successfully.

After that, we shut down ASM as one would normally do it:

$ . oraenv
+ASM
$ sqlplus / as sysasm
SQL> shutdown immediate;
SQL> quit

Then, as root, we unlock and shutdown HAS (not CRS, as the original README would have you do):

# GRID_HOME/crs/install/roothas.pl -unlock
2010-05-04 17:14:08: Checking for super user privileges
2010-05-04 17:14:08: User has super user privileges
2010-05-04 17:14:08: Parsing the host name
Using configuration parameter file: GRID_HOME/crs/install/crsconfig_params
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'localhost'
CRS-2673: Attempting to stop 'ora.cssd' on 'localhost'
CRS-2677: Stop of 'ora.cssd' on 'localhost' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'localhost'
CRS-2677: Stop of 'ora.diskmon' on 'localhost' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'localhost' has completed
CRS-4133: Oracle High Availability Services has been stopped.
Successfully unlock GRID_HOME

Now we're ready to do some patching.

Applying PSU 9343627 and 9352237 to Oracle 11.2 Grid Infrastructure
As I mentioned earlier, the PSU contains both 9343627 and 9352237, and it is advised that you apply both. I did so successfully with these commands:

$ GRID_HOME/OPatch/opatch napply -oh GRID_HOME -id 9343627
$ GRID_HOME/OPatch/opatch napply -oh GRID_HOME -id 9352237

For each one, you'll go through the normal opatch [y|n] confirmation prompts, and also prompted for an email address. I entered in my MOS login email, and left the password blank (it is optional). The rest was automatic and finished without error for both cases.

Applying PSU 9343627 and 9352237 to Oracle 11.2 DBMS
Here is something slightly different when applying the 9343627 patch to the DBMS_HOME, we specify a subdirectory after the "napply" parameter:

$ DBMS_HOME/OPatch/opatch napply 9343627/custom/server/ -oh DBMS_HOME -id 9343627

For patch 9352237, we use the regular syntax:

$ DBMS_HOME/OPatch/opatch napply -oh DBMS_HOME -id 9352237

Note that I use OPatch from DBMS_HOME when patching DBMS_HOME, and from GRID_HOME when patching GRID_HOME. I can't say if it is actually necessary, but I like to keep things tidy. This is why I advised installing the new OPatch into both homes earlier.

As of this moment, both homes are patched, but we aren't done yet!

Post-Patch Steps
First we run the postpatch.sh script included in patch 9343627 to reset some file permissions on the DBMS_HOME:

$ ./9343627/custom/server/9343627/custom/scripts/postpatch.sh -dbhome DBMS_HOME

Now we re-lock the GRID_HOME and restart the GI stack. As root:

# GRID_HOME/crs/install/roothas.pl -patch
2010-05-04 17:33:41: Checking for super user privileges
2010-05-04 17:33:41: User has super user privileges
2010-05-04 17:33:41: Parsing the host name
Using configuration parameter file: GRID_HOME/crs/install/crsconfig_params
CRS-4123: Oracle High Availability Services has been started.

Verify that the services we need are started (again as root):

# crsctl check has
CRS-4638: Oracle High Availability Services is online
# crsctl check css
CRS-4529: Cluster Synchronization Services is online

If you don't see CSS started, then you aren't alone. On both of my servers, CSS was not set to auto-start from OHASD. This was fixed by running these commands as the GRID_HOME owner (oracle):

$ crsctl modify resource "ora.cssd" -attr "AUTO_START=1"
$ crsctl modify resource "ora.diskmon" -attr "AUTO_START=1"

Then, as root:

# crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'localhost'
CRS-2673: Attempting to stop 'ora.cssd' on 'localhost'
CRS-2677: Stop of 'ora.cssd' on 'localhost' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'localhost'
CRS-2677: Stop of 'ora.diskmon' on 'localhost' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'localhost' has completed
CRS-4133: Oracle High Availability Services has been stopped.

# crsctl start has
CRS-4123: Oracle High Availability Services has been started.

# crsctl check has
CRS-4638: Oracle High Availability Services is online

# crsctl check css
CRS-4529: Cluster Synchronization Services is online

Moving on in the post-patch steps, we start the DBMS_HOME from the state file we created earlier:

$ DBMS_HOME/bin/srvctl start home -o DBMS_HOME -s /tmp/oracle_home.stat

At this point, we are done. You can start ASM and the DBMS instances. For some affirmation, run this for both GRID_HOME and DBMS_HOME (just using GRID_HOME in this example)

$ GRID_HOME/OPatch/opatch lsinventory
Invoking OPatch 11.2.0.1.2

Oracle Interim Patch Installer version 11.2.0.1.2
Copyright (c) 2010, Oracle Corporation. All rights reserved.

Oracle Home : GRID_HOME
Central Inventory : /opt/oracle/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.2
OUI version : 11.2.0.1.0
OUI location : GRID_HOME/oui
Log file location : GRID_HOME/cfgtoollogs/opatch/opatch2010-05-04_18-36-23PM.log

Patch history file: GRID_HOME/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : GRID_HOME/cfgtoollogs/opatch/lsinv/lsinventory2010-05-04_18-36-23PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Grid Infrastructure 11.2.0.1.0
There are 1 products installed in this Oracle Home.

Interim patches (2) :

Patch 9352237 : applied on Tue May 04 18:22:41 EDT 2010
Unique Patch ID: 12381846
Created on 25 Mar 2010, 00:05:17 hrs PST8PDT
Bugs fixed:
8661168, 8769239, 8898852, 8801119, 9054253, 8706590, 8725286, 8974548
8778277, 8780372, 8769569, 9027691, 9454036, 9454037, 9454038, 8761974
7705591, 8496830, 8702892, 8639114, 8723477, 8729793, 8919682, 8818983
9001453, 8475069, 9328668, 8891929, 8798317, 8820324, 8733749, 8702535
8565708, 9036013, 8735201, 8684517, 8870559, 8773383, 8933870, 8812705
8405205, 8822365, 8813366, 8761260, 8790767, 8795418, 8913269, 8897784
8760714, 8717461, 8671349, 8775569, 8898589, 8861700, 8607693, 8642202
8780281, 9369797, 8780711, 8784929, 8834636, 9015983, 8891037, 8828328
8570322, 8832205, 8665189, 8717031, 8685253, 8718952, 8799099, 8633358
9032717, 9321701, 8588519, 8783738, 8796511, 8782971, 8756598, 9454385
8856497, 8703064, 9066116, 9007102, 8721315, 8818175, 8674263, 9352237
8753903, 8720447, 9057443, 8790561, 8733225, 9197917, 8928276, 8991997, 8837736

Patch 9343627 : applied on Tue May 04 18:18:31 EDT 2010
Unique Patch ID: 12381846
Created on 15 Apr 2010, 11:28:38 hrs PST8PDT
Bugs fixed:
9343627, 9262748, 9262722

--------------------------------------------------------------------------------

OPatch succeeded.

As I said, Oracle support will be updating the README for PSU 9343627, but in the meantime I hope this guide helps you as much as it helped me. I'd like to thank my Pythian colleague Alex Gorbachev for his help in diagnosing some of the GI problems after the first broken patching, and also Esteban B. at Oracle Support for working closely with us to get a new single-node action plan.

A Drupal Backup Script

2009-11-22T10:28:00.000-06:00

I maintain a drupal codebase that hosts multiple sites. I’ve been shamefully lax in getting regular backups of those files and databases, until today. Here is a pretty basic bash script that will create a bzip2 archive of the drupal codebase (including site-specific dirs), and then create mysqldump exports of each site’s database and gzip those files. This particular script requires that the drupal install directory be named “drupal”, but you can change this easily enough to suit your needs.

This does require a ~/.drupalsites file that contains the needed database login info (suggest chmod 600). Like I said, nothing too clever.This script runs fine from cron, I have it scheduled for a weekly run. I then plan to rsync this to my home server for an offsite copy, even though the hosting service provides backups as well.

Moving Oracle Datafiles to a ZFS Filesystem with the Correct Recordsize

2009-10-27T12:51:00.000-05:00

Originally posted on The Pythian Group blog.

Full credit for this tale should go to my colleague Christo Kutrovsky for the inspiration and basic plan involved.

We recently migrated a large database from Solaris SPARC to Solaris x86-64. All seemed to go well with the migration, but in the next few weeks, we noticed some I/O issues cropping up. Some research led us to find that the ZFS filesystem used to hold the datafiles was killing us on I/O. The default “recordsize” setting for ZFS was 128k.

$ /usr/sbin/zfs get recordsize zfs-data
NAME PROPERTY VALUE SOURCE
zfs-data recordsize 128K default

An Oracle database typically uses 8k for the block size, but in this case it was 16k. We saw basically the same thing that Neelakanth Nadgir described in his blog post, Databases and ZFS:

With ZFS, not only was the throughput much lower, but we used more [than] twice the amount of CPU per transaction, and we are doing 2x times the IO. The disks are also more heavily utilized. We noticed that we were not only reading in more data, but we were also doing more IO operations [than] what is needed.

The fix is to set the ZFS recordsize for a datafile filesystem to match the Oracle instance’s db_block_size. We also read in the ZFS Best Practices Guide that redo logs should be in a separate filesystem with the default ZFS recordsize of 128k. We already had them separate, so we just needed to get our datafiles on a ZFS filesystem with a 16k recordsize.

The first step is to change the ZFS recordsize for the current filesystem. Easy enough, but the change in recordsize will only apply to new files written there. It doesn’t do anything for our existing terabyte-plus of datafiles. This means we need to move the files to a new ZFS filesystem with the correct recordsize already set. That’s where the magic happens.

My first thought was that we’d need whole other slab of disk. Then I learned that we can simply create a new ZFS filesystem on the same pool of disks as the old filesystem. Obviously we won’t be able to hold two complete copies of the datafiles, but this would let us migrate groups of files at a time (for example, a tablespace). When the move is complete, disk space is released from the old filesystem, so the sum of used disk is essentially unchanged.

The rest is just a matter of moving datafiles. Now, we all know you can’t just move or copy Oracle datafiles from a running instance. We also know that clients want as little downtime as possible, so doing a complete shutdown/move/mount/rename/open is out of the question. We could offline a tablespace, move its datafiles, rename them in the database, then online the tablespace again. Here’s a sample scenario, starting with tablespace and datafile creation:

SQL> create tablespace zfstest
2 datafile '/u01/app/oracle/oradata/orcl/zfs128k/zfstest01.dbf'
3 size 128m;

Tablespace created.

SQL> alter tablespace zfstest
2 add datafile '/u01/app/oracle/oradata/orcl/zfs128k/zfstest02.dbf'
3 size 128m;

Tablespace altered.

SQL> create table zfsobjects tablespace zfstest
2 as select * from dba_objects;

Table created.

Accept the following:

/u01/app/oracle/oradata/orcl/zfs128k/ is a ZFS filesystem with default 128k recordsize
/u01/app/oracle/oradata/orcl/zfs16k/ is a ZFS filesystem with a 16k recordsize

So here we have datafiles created on a ZFS filesystem with the default 128k recordsize, and a small table just to test data access. To move the entire tablespace in one go, we do this:

SQL> alter tablespace zfstest offline;

Tablespace altered.

SQL> !mv /u01/app/oracle/oradata/orcl/zfs128k/zfstest01.dbf
/u01/app/oracle/oradata/orcl/zfs16k/zfstest01.dbf

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/zfs128k/zfstest01.dbf' to
'/u01/app/oracle/oradata/orcl/zfs16k/zfstest01.dbf';

Database altered.

SQL> !mv /u01/app/oracle/oradata/orcl/zfs128k/zfstest02.dbf
/u01/app/oracle/oradata/orcl/zfs16k/zfstest02.dbf

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/zfs128k/zfstest02.dbf' to
'/u01/app/oracle/oradata/orcl/zfs16k/zfstest02.dbf';
Database altered.

SQL> alter tablespace zfstest online;

Tablespace altered.

SQL> select count(*) from zfsobjects;

COUNT(*)
----------
68844

Pretty simple. We do the one escape to the OS mv command to move the datafile. If you want to be safe, you could use cp instead of mv to copy the file. Just know that disk space from the old filesystem won’t be released until you delete the old files.

Note that this operation could take a very long time in the case of large tablespaces. We could try a somwhat similar approach of taking one datafile offline at a time and moving it. We’d just need to perform a quick recovery before bringing it online. That would look like this:

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/zfs128k/zfstest01.dbf' offline;

Database altered.

SQL> !mv /u01/app/oracle/oradata/orcl/zfs128k/zfstest01.dbf
/u01/app/oracle/oradata/orcl/zfs16k/zfstest01.dbf

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/zfs128k/zfstest01.dbf' to
'/u01/app/oracle/oradata/orcl/zfs16k/zfstest01.dbf';

Database altered.

SQL> recover datafile '/u01/app/oracle/oradata/orcl/zfs16k/zfstest01.dbf';
Media recovery complete.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/zfs16k/zfstest01.dbf' online;

Database altered.

SQL> alter database datafile
'/u01/app/oracle/oradata/orcl/zfs128k/zfstest02.dbf' offline;

Database altered.

SQL> !mv /u01/app/oracle/oradata/orcl/zfs128k/zfstest02.dbf
/u01/app/oracle/oradata/orcl/zfs16k/zfstest02.dbf

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/zfs128k/zfstest02.dbf'
to '/u01/app/oracle/oradata/orcl/zfs16k/zfstest02.dbf';

Database altered.

SQL> recover datafile '/u01/app/oracle/oradata/orcl/zfs16k/zfstest02.dbf';
Media recovery complete.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/zfs16k
/zfstest02.dbf' online;

Database altered.

SQL> select count(*) from zfsobjects;

COUNT(*)
----------
68844

Seems fine. Note that your instance needs to be in ARCHIVELOG mode to be able to take datafiles offline. Obviously, if someone tried to access data on an offline datafile they’d get this error:

SQL> select count(*) from zfsobjects;
select count(*) from zfsobjects
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/zfs16k/zfstest01.dbf'

So if a datafile is particularly large, this can mean still a few minutes of some data being offline. We have one more tool at our disposal that provides for a quicker turnaround: RMAN’s BACKUP AS COPY command. As it says, this creates a copy of a datafile as a backup. The benefit is that it does this while the current datafile (and data) remains online. We then just need to offline, switch, recover, and online each datafile, which takes very little time compared to the time spent moving a large datafile. That process would be something like this.

First, we want to know the file numbers of our datafiles. This is because the RMAN procedure doesn’t provide for an easy way to preserve datafile names when we backup an entire tablespace (which we’re going to do):

SQL> select file_id, file_name
2 from dba_data_files
3 where tablespace_name='ZFSTEST';

FILE_ID FILE_NAME
---------- -------------------------------------------------------
6 /u01/app/oracle/oradata/orcl/zfs128k/zfstest01.dbf
7 /u01/app/oracle/oradata/orcl/zfs128k/zfstest02.dbf

So we have file numbers 6 and 7. Now we create our RMAN command file and execute it:

RMAN> backup as copy
2> tablespace zfstest format '/u01/app/oracle/oradata/orcl/zfs16k/%U';
3>
4> sql 'alter database datafile 6 offline';
5> switch datafile 6 to copy;
6> recover datafile 6;
7> sql 'alter database datafile 6 online';
8>
9> sql 'alter database datafile 7 offline';
10> switch datafile 7 to copy;
11> recover datafile 7;
12> sql 'alter database datafile 7 online';
13>
Starting backup at 18-AUG-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/zfs128k/zfstest01.dbf
output file name=/u01/app/oracle/oradata/orcl/zfs16k/data_D-ORCL_I-1203425866_TS-ZFSTEST_FNO-6_01kn0irh tag=TAG20090818T140640 RECID=2 STAMP=695225208
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/zfs128k/zfstest02.dbf
output file name=/u01/app/oracle/oradata/orcl/zfs16k/data_D-ORCL_I-1203425866_TS-ZFSTEST_FNO-7_02kn0is0 tag=TAG20090818T140640 RECID=3 STAMP=695225222
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 18-AUG-09

sql statement: alter database datafile 6 offline

datafile 6 switched to datafile copy
"/u01/app/oracle/oradata/orcl/zfs16k/data_D-ORCL_I-1203425866_TS-ZFSTEST_FNO-6_01kn0irh"

Starting recover at 18-AUG-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 18-AUG-09

sql statement: alter database datafile 6 online

sql statement: alter database datafile 7 offline

datafile 7 switched to datafile copy
"/u01/app/oracle/oradata/orcl/zfs16k/data_D-ORCL_I-1203425866_TS-ZFSTEST_FNO-7_02kn0is0"

Starting recover at 18-AUG-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 18-AUG-09

sql statement: alter database datafile 7 online

Recovery Manager complete.

Voila! The tablespace is moved with minimal downtime:

SQL> select file_id, file_name
2 from dba_data_files
3 where tablespace_name='ZFSTEST';

FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
6
/u01/app/oracle/oradata/orcl/zfs16k/data_D-ORCL_I-1203425866_TS-ZFSTEST_FNO-6_01kn0irh

7
/u01/app/oracle/oradata/orcl/zfs16k/data_D-ORCL_I-1203425866_TS-ZFSTEST_FNO-7_02kn0is0

SQL> select count(*) from zfsobjects;

COUNT(*)
----------
68844

Obviously, you can generate all your statements from SQL scripts, and you’ll definitely want to do this if your tablespace has a large amount of datafiles. If you are scripting, you could opt to backup one datafile at a time if you need to preserve file names. In this case, I wasn’t too concerned.

UPDATE: Teammate Marc Billette pointed out that the old datafiles were not removed when doing a datafile copy and switch. I clearly left them on disk, continuing to use space! So, once we are done with all the datafile switches, then our original datafiles become the copies, and we can list and delete those in RMAN like this:

RMAN> list datafilecopy all;

List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
4 6 A 18-AUG-09 629982 18-AUG-09
Name: /u01/app/oracle/oradata/orcl/zfs128k/zfstest01.dbf

5 7 A 18-AUG-09 630001 18-AUG-09
Name: /u01/app/oracle/oradata/orcl/zfs128k/zfstest02.dbf

RMAN> delete datafilecopy all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
4 6 A 18-AUG-09 629982 18-AUG-09
Name: /u01/app/oracle/oradata/orcl/zfs128k/zfstest01.dbf

5 7 A 18-AUG-09 630001 18-AUG-09
Name: /u01/app/oracle/oradata/orcl/zfs128k/zfstest02.dbf

Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/zfs128k/zfstest01.dbf RECID=4 STAMP=695225227
deleted datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/zfs128k/zfstest02.dbf RECID=5 STAMP=695225228
Deleted 2 objects

Thanks to Marc for catching this early in the published life of the article!

I hope this post can help other people that run into this problem with Oracle on ZFS (I’ve heard from one on twitter already), or people just wanting to move datafiles. Again, thanks to Pythian DBA Christo Kutrovsky for laying this process out for us!

Note: this process was tested on Oracle 10gR2 (10.2.0.2) and 11gR1 (11.1.0.6).

HOWTO: Oracle Cross-Platform Migration with Minimal Downtime

2009-10-27T12:49:00.001-05:00

Originally posted at The Pythian Group blog.

I recently performed a migration from Oracle 10gR2 on Solaris to the same version on Linux, immediately followed by an upgrade to 11g. Both platforms were x86-64. Migrating to Linux also included migrating to ASM, whereas we had been using ZFS to hold the datafiles on Solaris. Restoring files into ASM meant we would have to use RMAN (which we would probably choose to use anyway).

As with many databases, the client wanted minimal downtime. It was obvious to us that the most time-consuming operation would be the restore and recovery into the new instance. We were basically doing a restore and recovery from production backups and archived redo logs. It quickly dawned on me that we could start this operation well before the scheduled cutover time and downtime window, chopping at least six hours from the downtime window. The client would only need to keep the new instance in mount mode after the initial restore/recovery finished, periodically re-catalog the source instance's FRA (which was mounted via NFS), and then re-run the recover database command in RMAN. Once the time comes to cutover, simply archivelog current the original instance and shutdown immediate. Then open the new instance with the RESETLOGS option, and voila! Migration complete!

I'll try to recreate a simple example here.

On the new instance, assume that you have ASM set up and configured with two disk groups, DATA and REDO. Let's also assume that you have the FRA directory (or the directory where you hold backups and archivelogs) mounted from the Solaris host to the Linux host under /solfra/prod/. Here's the command we used:

mount -o hard,bg,tcp,nfsvers=3,suid,rsize=32768,wsize=32768,nointr,noac,ro solhost:/fra /solfra/

We then need to get a pfile and edit it for ASM-ification. You can either restore the spfile from backup or just export a pfile from the current running production instance. To do the former, you'd need to go into RMAN and run an operation similar to this (best to ensure that the ORACLE_HOME, ORACLE_SID, and other Oracle-related environment variables are set properly):

RMAN> startup force

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/10.2.0/db_1/dbs/initprod.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 06/29/2009 13:34:50
ORA-00205: error in identifying control file, check alert log for more info
RMAN>
RMAN> restore spfile to pfile 'initprod.ora' from autobackup recovery area = '/solfra/' db_unique_name = 'PROD';

Starting restore at 29-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

recovery area destination: /solfra/
database name (or database unique name) used for search: PROD
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /solfra/PROD/autobackup/2009_06_29/o1_mf_s_690813149_54kypcxz_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 29-JUN-09

From here you can go into sqlplus and CREATE PFILE FROM SPFILE;.

Now we have our text pfile in $ORACLE_HOME/dbs/initprod.ora, and we want to make these changes specific to the transition to ASM:

*.control_files='+DATA','+REDO'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.db_recovery_file_dest='/path/to/fra'
*.db_recovery_file_dest_size='2000G'

Obviously, the last two parameters about location and size of the FRA should be tailored to your setup.

We then create a new SPFILE with CREATE SPFILE FROM PFILE;.

Now we're ready to restore the controlfile(s) from backup:

RMAN> startup force;

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/13/2009 20:20:56
ORA-00205: error in identifying control file, check alert log for more info

RMAN> restore controlfile from autobackup recovery area = '/solfra/' db_unique_name = 'PROD';

Starting restore at 13-AUG-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=650 devtype=DISK

recovery area destination: /solfra/
database name (or database unique name) used for search: PROD
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /solfra/PROD/autobackup/2009_08_13/o1_mf_s_694801261_5890xyoj_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=+DATA/prod/controlfile/current.256.694815695
output filename=+REDO/prod/controlfile/current.256.694815697
Finished restore at 13-AUG-00

Note that if you restored the spfile from backup, you don't need to specify the recovery area in the restore controlfile from autobackup command. In this case, because I chose to create a pfile from the source instance, I needed to supply the recovery area location. Note that this is the recovery area that holds the source backups, not the recovery area that your new instance will use once it is opened.

With the control files restored, we can mount the new instance:

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

The next step is to catalog the backup files and archivelogs from the remote FRA.

RMAN> CATALOG START WITH '/solfra/PROD' NOPROMPT;

Starting implicit crosscheck backup at 13-AUG-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=646 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=650 devtype=DISK
Crosschecked 565 objects
Finished implicit crosscheck backup at 13-AUG-09

Starting implicit crosscheck copy at 13-AUG-09
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 13-AUG-09

searching for all files in the recovery area
cataloging files...

This will continue listing all the files in the catalog, and some files that were not cataloged (e.g. online redo logs, non-RMAN files, etc.).

Now we're ready to begin our RESTORE phase. You'll need an rman command file that looks something like this:

run {
set newname for datafile X to new;
# ... (repeat for other datafiles)
set newname for tempfile Y to new;
# ... (repeat for other tempfiles)
restore database;
switch datafile all;
switch tempfile all;
}
recover database;

Note that X and Y are the file# of the individual datafile and tempfile, respectively. You will probably want to generate this file if you have a large number of datafiles.

It is important that you read MetaLink Doc 415884.1, if you are performing a migration to a different platform with the same endian. Oracle suggests that you should perform a CONVERT DATABASE, but later admits that you only need to perform a CONVERT on tablespaces containing UNDO segments:

The convert database command is a no operation command for all files that do not contain undo but they are scanned regardless during the conversion. Using the convert tablespace/datafile for those containing undo will suffice and all other source files not containing undo can be moved to the target (same endian) platform without the Recovery Manager conversion. This will greatly reduce the time required to move and database with thousands of files from source to target platform also decreasing the amount of time the database is unavailable.

We had an internal debate over whether or not it is truly necessary even to do this. However, I would advise you to read this document and discuss it with Oracle Support Services. They would most likely try to convince you to do it or threaten to not support your database.

Once the script finishes with the recover database portion, this query should indicate that datafile headers and controlfiles are in sync:

SQL> alter session set nls_date_format='YYYY/MON/DD hh24:mi:ss';
Session altered.

SQL> select checkpoint_time,fuzzy,count(*),status
from ( select checkpoint_time,fuzzy,status
from v$datafile_header
union all
select controlfile_time,'CTL',null from v$database)
group by checkpoint_time,fuzzy,status;

CHECKPOINT_TIME FUZ COUNT(*) STATUS
-------------------- --- ---------- -------
2009/AUG/14 02:22:11 CTL 1
2009/AUG/14 02:22:11 YES 1266 ONLINE

The database is still in mount mode. If we were ready to cut over now, we could open the database with the RESETLOGS option. However, we are still a few hours or a day away from the cutover. What we want to do is apply more logs from the current production instance as they get archived. To minimize the babysitting, we decided to run this in a script once every few hours:

CATALOG START WITH '/solfra/PROD' NOPROMPT;
RECOVER DATABASE;

As you can guess, this re-catalogs the NFS-mounted FRA, ignoring files that were already cataloged. So now the controlfile knows about the new archivelogs. The RECOVER DATABASE command then beautifully applies logs from where it left off until it runs out of logs again. We re-run this periodically to keep our new instance as close to current as possible until it's time to cut over.

When the cutover time comes, we run this on the current production instance:

SQL> alter system archive log current;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Then, on the new instance, we do one last catalog and recover. This should bring us completely up to date with the original prod instance. Now we just need to open with resetlogs:

SQL> alter database open resetlogs;
Database altered.

In my case, I manually renamed the online and standby redo logs to +REDO (they still had the old filesystem names in v$logfile), but I believe that OPEN RESETLOGS does this automatically anyway.

And so, here we are in Linux on on the same version (10.2.0.2) as Solaris with appropriate one-off patches, using ASM. At this point, I was just under 30 minutes into my downtime window since the original prod instance was shutdown. I don't think that's too bad for moving a 1.3T database!

After this, I immediately went into upgrading to 11gR1 (11.1.0.7) and then building a new standby instance, but that is outside the scope of the initial migration that I wanted to share with you today. Cheers!

Turn Off db_cache_advice To Avoid Latch Contention Bugs

2009-08-26T16:06:00.000-05:00

Originally posted on The Pythian Group blog.

A couple of weeks ago, we noticed some timeouts in some of our standard Oracle RDBMS health check scripts on a new instance. I had just migrated this instance to bigger, better, badder hardware and so it had been given more SGA to use, namely a bigger buffer cache. The software version was still Oracle 10.2.0.2, as we wanted to introduce as few variables as possible (we were already moving to a new platform with an endian change).

At first the timeouts were infrequent, but over the course of a week started to grow in frequencey until the point where none of the checks were finishing in the allowed timeframe. We ran an AWR report, and tucked far down in the “Latch Activity” section, a colleague noticed this:

Pct    Avg   Wait                 Pct
                                    Get    Get   Slps   Time       NoWait NoWait
Latch Name                     Requests   Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
...
simulator lru latch          10,032,617    3.3    0.7  44950      336,837    0.3
...
 Latch Activity                             DB/Inst: FOO/foo  Snaps: 156-157
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
   willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0

                                           Pct    Avg   Wait                 Pct
                                    Get    Get   Slps   Time       NoWait NoWait
Latch Name                     Requests   Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
transaction branch alloc        112,412    0.0    0.0      0            0    N/A
undo global data                466,321    0.0    0.0      0            0    N/A
user lock                         7,440    0.8    0.4      1            0    N/A
          -------------------------------------------------------------

The “simulator lru latch” event brought us to MetaLink note 5918642.8 and bug 5918642. Affecting 10g and 11g prior to 10.2.0.4 and 11.1.0.7, respectively. The bug is with the database buffer cache advisor, controlled by the parameter db_cache_advice, which defaults to ON (depending on statistics_level). The note simply states:

High simulator lru latch contention can occur when db_cache_advice is set to ON if there is a large buffer cache.

We simply set db_cache_advice to OFF (thankfully it is a dynamic parameter), and pretty quickly our checks were running just fine.

My suggestion is to simply turn this off unless you are actively using the cache advisor to tune an instance. Once you are done tuning, and are no longer using the advisor, turn it off.

NOTE: As Mladen Gogola pointed out in the comments, turning this off will cause problems if you are using automatic memory management (i.e. sga_target > 0). Re-pasting his post here:

The problem with that advice is that it will prevent automatic memory management from resizing the buffer cache and the instance will end up with a huge, mostly empty, shared pool and default buffer cache. Automatic memory management is biased toward shared pool even with the cache dvice turned on, without it, buffer cache will be reduced to the minimum size, usually only 64MB. If you disable cache advice, I would also recommend disabling the automatic memory management and configuring SGA manually.

Setting up Network ACLs in Oracle 11g… For Dummies

2009-08-19T12:37:00.000-05:00

Originally posted on The Pythian Group blog.

Having recently performed a test upgrade for a client from Oracle RDBMS 10g to 11g, I can tell you that one of the big changes that will likely require action on your part as DBA is the new fine-grained access control for the packages UTL_SMTP, UTL_TCP, UTL_MAIL, UTL_HTTP and UTL_INADDR. Part of the Oracle 11g pre-upgrade tool will notify you of users that will require new privileges.

Of course, Oracle’s post-upgrade network ACL setup documentation is much more confusing than it needs to be, at least for simple minds like me. A colleague stepped forward with a simple set of commands for a basic setup that even the tired and stressed can understand.

I’ll share that here, with some basic explanation:

BEGIN
-- Create the new ACL, naming it "netacl.xml", with a description.
-- Also, provide one starter privilege, granting user FOO
-- the privilege to connect.
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('netacl.xml',
'Allow usage to the UTL network packages', 'FOO', TRUE, 'connect');

-- Now grant privilege to resolve DNS names for FOO,
-- and then grant connect and resolve to user BAR
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl.xml' ,'FOO', TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl.xml' ,'BAR', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl.xml' ,'BAR', TRUE, 'resolve');

-- Specify which hosts this ACL applies to,
-- for simplicity, we're saying all (*)
-- You might want to specify certain hosts to lock this down.
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('netacl.xml','*');
END;
/

As you can see, this example will let the FOO and BAR database users connect and resolve to any host. The ASSIGN_ACL section in the full package documentation (see link below) details how this can be used to lock down a user’s ability to make outside connections.

Of course, nothing beats reading the Oracle 11g DBMS_NETWORK_ACL_ADMIN documentation, where you can see some examples of stricter ACL setups.

Sending Timezone-Aware Email with UTL_SMTP

2009-08-19T12:32:00.000-05:00

Originally posted at The Pythian Group blog.

I’m back again with another in what I hope will be a long line of “Quick Tips for Newbies” series.

At The Pythian Group, we have employees all over the globe, from our headquarters in Ottawa to regional offices in Boston, Prague, India and Sydney, and a few scattered remote workers in Seattle, Paris, Kiev, Brazil, South Africa and Wisconsin, among other places. In other words, we are spread across multiple timezones, and since it wasn’t too long ago that everyone was in Ottawa, this is something that still presents little quirks.

One such quirk involved email generated by one of our internal Oracle instances—via a stored procedure that used UTL_SMTP to send the messages—did not have timezone information in the “Date” email header. As a result, they would be stamped with the hour in Eastern timezone (Ottawa time), but the mail clients would think that hour was local. Depending on where you are relative to Ottawa, this could be many hours in the past or future. Of course, this wouldn’t be noticed if you were in Ottawa or even Boston, both in Eastern. For the rest, it was at the very least, an annoyance—but one that is easily fixed.

Looking at the PL/SQL stored procedure that we used to generate email messages, I saw that the “Date” header was being built with this code:

date_hdr := 'Date: '||to_char(sysdate,'dd Mon yy hh24:mi:ss');

The fix is almost trivial—just use SYSTIMESTAMP instead of SYSDATE, and include the timezone in the TO_CHAR function:

date_hdr := 'Date: '||to_char(systimestamp,'dd Mon yy hh24:mi:ss tzhtzm');

Voila! Emails now had a full Date header. And there was much rejoicing from around the world.

Here’s a quick query to highlight the difference:

SQL> select to_char(sysdate,'dd Mon yy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'D
------------------
01 Dec 08 18:50:02

SQL> select to_char(systimestamp,'dd Mon yy hh24:mi:ss tzhtzm') from dual;

TO_CHAR(SYSTIMESTAMP,'DD
------------------------
01 Dec 08 18:50:10 -0500

Even if you aren’t sending email to all the ends of the Earth, it won’t hurt to make your messages timezone-aware. I’m sure it will save some confusion and frustration down the line.

Note: I discovered the fix via this blog post, which seems to be invite-only at the time of this writing.

ORA-16069? You May Need A New Standby Controlfile

2009-08-19T12:20:00.000-05:00

Originally posted on The Pythian Group blog.

On a recent Monday, I had to perform an emergency Oracle standby switchover for a client whose primary instance host had mysteriously rebooted itself over the previous day. Confidence in that host was, understandably, shaken.

The Oracle Data Guard configuration is a 3-instance setup using Data Guard Broker: one primary, we’ll call it OraA, feeding two standby instances, OraB and OraC. In this particular configuration, we perform switchovers between OraA and OraC. Caught in the middle is OraB, which is on a 60-minute standby delay.

After this particular switchover, OraB started complaining with this message in the alert log:

ORA-16069: Archive Log standby database activation identifier mismatch

We had seen this occasionally in prior switchovers, and the problem would fix itself once the standby delay passed and the OraB standby would process the log notifying it of the switchover. This time, however, recovery was stopped and more than enough time had elapsed. OraA and OraC were performing perfectly fine.

Much of the reference searching I saw suggested that the standby instance would have to be completely rebuilt. Not an appetizing option. A search of metalink turned up Bug 4048687, which seemed to demonstrate a similar problem, although on a different OS/Platform. That solution was to recreate the standby controlfile. Trust me, it sounds more drastic than it is!

Here’s how to do it in just 6 easy steps!

Shutdown the misbehaving standby.
Copy one of the current standby controlfiles for safekeeping (just in case).
On the primary instance, create a new standby controlfile: alter database create standby controlfile as '/tmp/stdby.ctl';
Transfer that new standby controlfile to the standby host.
Copy the new controlfile to the controlfile location(s) used by the instance (you have more than one, right?).
STARTUP MOUNT the standby instance. If you use the Data Guard Broker, it should automatically begin recovery for you; otherwise restart managed recovery with alter database recover managed standby database disconnect;

Voila. Standby recovery should resume nicely, assuming logs are there to apply.

GNU basename in PL/SQL

2008-10-01T10:20:00.000-05:00

Reposted from The Pythian Group blog.

In the process of scripting a database migration, I was in need of something akin to the GNU basename utility that I know and love on Linux. basename is most famous for taking a full file path string and stripping away the leading path component, returning just the name of the file. This can be emulated in PL/SQL with calls to SUBSTR and INSTR, like this:

substr(dirname,instr(dirname,'/',-1)+1)

(Thanks to Ian Cary, who shared this logic on oracle-l)

As you can see, this simply finds the last occurence of /, which is our directory separator on *nix and Solaris operating systems. On Windows, it would be \. It then returns a substring beginning one character after that last separator until the end of the string. Voila, a basic basename routine!

Upon reading the basename man page again, I found that basename also takes an optional parameter, a suffix string. If this suffix string is provided, basename will also truncate that string from the end. For example:

$ basename /home/seiler/bookmarks.html
bookmarks.html
$ basename /home/seiler/bookmarks.html .html
bookmarks

I decided that this would be handy to have, and set out to create a compatible basename function in PL/SQL. Here is what I came up with:

CREATE OR REPLACE FUNCTION basename (v_full_path IN VARCHAR2,
v_suffix IN VARCHAR2 DEFAULT NULL,
v_separator IN CHAR DEFAULT '/')
RETURN VARCHAR2
IS
v_basename VARCHAR2(256);
BEGIN
v_basename := SUBSTR(v_full_path, INSTR(v_full_path,v_separator,-1)+1);
IF v_suffix IS NOT NULL THEN
v_basename := SUBSTR(v_basename, 1, INSTR(v_basename, v_suffix, -1)-1);
END IF;

RETURN v_basename;
END;
/

I’ve also added an optional third parameter to specify a directory separator other than the default. It would probably be rarely useful, but not hard to remove if you don’t like it. As you can see, I’ve used similar SUBSTR/INSTR logic to identify the suffix index and prune it out.

Here it is in action:

SQL> COLUMN file_name FORMAT a45;
SQL> COLUMN basename FORMAT a15;
SQL> COLUMN no_suffix FORMAT a12;
SQL> SELECT file_name
2 , basename(file_name) as basename
3 , basename(file_name, '.dbf') as no_suffix
4 FROM dba_data_files;

FILE_NAME BASENAME NO_SUFFIX
--------------------------------------------- --------------- ------------
/u01/app/oracle/oradata/orcl/users01.dbf users01.dbf users01
/u01/app/oracle/oradata/orcl/sysaux01.dbf sysaux01.dbf sysaux01
/u01/app/oracle/oradata/orcl/undotbs01.dbf undotbs01.dbf undotbs01
/u01/app/oracle/oradata/orcl/system01.dbf system01.dbf system01
/u01/app/oracle/oradata/orcl/example01.dbf example01.dbf example01

I hope this makes your work just a little bit easier, as it has mine.

Does Oracle’s Block Change Tracking File Shrink?

2008-10-01T10:10:00.000-05:00

Reposted from The Pythian Group blog.

Just a quick post to get myself back into blogging mode. Recently in IRC (#oracle on freenode, to be precise), a fresh face asked if the Block Change Tracking file ever shrinks. She had been worrying about the file in her instance continuing to grow. A number of us speculated (non-BAAG!) that perhaps taking an RMAN backup would somehow purge the file of what it was keeping track of, and then the magical Oracle fairies would promptly resize it for us. Needless to say, I was hesitant to take this theory forward with Alex Gorbachev aware of my home address.

After setting up Oracle 10.2.0.1 on a nice VirtualBox image (more on that in another post) running CentOS 5, I began to do some reading. For some reason, actually reading the official tahiti docs was last on my list. A search of the 10gR2 docs quickly yielded this (from RMAN Incremental Backups):

4.4.4.4 Estimating Size of the Change Tracking File on Disk

The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database. Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. Note, however, the following two factors that may cause the file to be larger than this estimate suggests:

To avoid overhead of allocating space as your database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB incremenents [sic]. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.

For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.

So (if the docs are to be trusted), it would seem that whether or not a backup is taken has no effect on the size of the file, or at least wouldn’t cause it to be shrunk. The size is tied to the amount of data in the database itself, not necessarily the changes in the database waiting to be included in the next incremental RMAN backup.

The documentation does suggest, however, that file size might be affected if (for example) a tablespace and its datafiles were dropped from the database. I’ll save this test for another day!