cold dead raw fish (CDRF)

Database GUIs, Free Toad Clone, Oracle DBA GUI, MySQL, SQL Console, etc

Java based Toad clone:
Oracle SQL Developer (ALSO works with OTHER DATABASES as well !!!)
http://www.orafaq.com/wiki/SQL_Developer
http://www.oracle.com/technology/products/database/sql_developer/index.html

MySQL GUI
http://dev.mysql.com/downloads/gui-tools/5.0.html

Recovering from Oracle ORA-01157 / ORA-01110 (somebody deleted an obsolete data file directly from the file system)

Note: This didn't go very smoothly, so these are RAW NOTES, but in the end I did fix it!!! (scenario 3 below).  The notes from the original thread were a bit vague / wrong details, so this is at least a bit more to go on.

In the course of trying to startup an Oracle database you might get an error like:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'D:\ORACLE_DATA\CUSTOMER\DB.ORA'

This has to do with Oracle looking for a data file associated with one of its databases that it can't get to any more.  There are 3 general cases:
1: The file is there, but the OS has it locked by some other process, or there's a permission issue, etc.
  Fix: operating system issue, permissions, or reboot to clear processes, etc.
2: The file is not there, it was deleted, but you want to recover it!
  Fix: See this thread and scroll down to Sept 15 2005 7:12 posting.  I did not try this.
3: The file is not there, it was deleted, and you don't care.  It's an old database that somebody knew was obsolete, so they just deleted it.  Oracle doesn't like that!
  Fix: See the same thread but scroll a bit up to the Sept 15 6:46 posting.

# 3 is the case I'm in.  The data was old and was blown away.  I just need to tell Oracle not to worry about it.

This is what I'm trying, from this thread (6:46 posting)

Before you start, you need to know where Oracle stores its control files.  And to find that out, I looked in the ora.ini file, so you'll need to find that.

Oracle ini I found:
D:\oracle\admin\(dbname)\pfile\init.ora.1111200217029
Look in there to find the control_file location, on line 34 I have:
control_files=("D:\oracle_data\stack\CONTROL01.CTL", "D:\oracle_data\stack\CONTR
OL02.CTL", "D:\oracle_data\stack\CONTROL03.CTL")

So the "control files" mentioned in the thread I mentioned above are in D:\oracle_data\stack.  We'll need to know this later.

(from Command Prompt, with sqlplus in path d:\oracle\ora92\bin)
sqlplus /nolog

(then inside SQL)
alter database backup controlfile to trace;

OR is it...

alter database backup controlfile to trace as 'some/path' REUSE;

I think the "reuse" parameter has them take out all the cruft, so that you can rerun it.

(in another command window)
Looked in file system, found D:\oracle\admin\stack\udump\stack_ora_700.trc   Use DIR /OD to see the most recent files, and compare against current system time.

(then in ANOTHER command window)

cd D:\oracle_data\stack
(remember, this was where we found the control files in an earlier step)
mkdir ctrl_bak
copy *.ctl ctrl_bak\
del *.ctl

In the second command window, with the trc file, make a copy of it.  CALL THE ORIGINAL stack_ora_700.trc.BAK.  We will be modifying stack_ora_700.trc

It needs to not have a bunch of cruft comments in it (I think that's what the reuse does).

And then on the first line you need to ADD this:  (see this page, search for controlfile)
connect / as sysdba

BTW If you wanted to do that on the command line it would be:

sqlplus "/ as sysdba"

Now from a command prompt we will restore the control file from this human readable backup:

sqlplus /nolog @stack_ora_700.trc

Not sure you need this next one ....

(now back in SQL)
startup nomount;

I eventually had to reboot.  But after that it worked!

You can also check the control files directory and notice that your 3 .CTL files are back.

JDBC Can't connect to Oracle: ORA-01033: ORACLE initialization or shutdown in progress

The advice on the web is to wait a while until Oracle has time to startup properly, which can take a while.  The admin tools won't connect either.  But if after waiting and waiting it still doesn't work, you may need to get out the crow bar.

I got a bit further with the info from this posting:
(from OS command prompt)
sqlplus /nolog
(then at SQL prompt)
shutdown
(get message saying database is not open)
startup

The last startup command will often get things going.  OR it will tell you why it's so darn unhappy.  In my case there's some old database it's trying to access that is no longer on the disk.

ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'D:\ORACLE_DATA\CUSTOMER\DB.ORA'

For me that's an obsolete database, so now I just need to find the command to tell Oracle to not worry about it.  More later...

Odd behavior when using multiple MySQL TIMESTAMP fields, fixed by changing to type DATETIME

Note 1: I know this sounds too weird to be true, but I saw it consistently.
Note 2: Yes, I'm aware that there are other ways to add an hour to a datetime interval, it's a long story, see prev post.

I had a table with two TIMESTAMP fields.  Adding an hour's worth of seconds to one field would reset the OTHER field to the current date and time.

So I'd do a select on start_time and end_time from my log table.  Both are fine.

Then I would do:
mysql> update log_table set end_time = from_unixtime( unix_timestamp( end_time
) + 3600 );

You'll notice I'm just changing the end_time field.

Then I'd do a select of both values and it would show that START time was now all set to the current date and time.  I recreated this a bunch of times.  I also dropped and recreated the table and indexes.  I also dropped and recreated with no index for either field, just in case it was something weird with indexing.

Changing the field from type TIMESTAMP to type DATETIME fixed the problem.  All of my problems vanished with making that one change.

I really think this is a bug.  I'm running 5.0.51a-community-nt MySQL Community Edition.  I suspect it has something to do with TIMESTAMP's defined behavior when it gets a null value, to return the current date and time.  But it doesn't explain the "cross talk" between fields - I'm guessing that stems from some type of query evaluator/executor and maybe cached/uncached garbage from somewhere... but who know!

Workaround: DATETIME = good, TIMESTAMP = bad (at least when using multiple date / time fields in the same talbe)

Weird Calendar page for September 1752 (the month they dropped 10 days from)

   September 1752
 S  M Tu  W Th  F  S
       1  2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

Pretty cool.  From PostgreSQL doc page.

Date math in MySQL vs. Oracle, PostgreSQL, Oracle, etc., adding floating point numbers, etc.

In many databases and computer languages dates and times can be treated like floating point numbers.  For example, 3.5 is understood to mean 3 and a half days, or 3 days and 12 hours.

This can be handy in a database, for example to roll all the dates in a table forward by some arbitrary amount of time.  In most databases you can just directly add floating point numbers to date / time values.  Although MySQL has a wealth of date and time arithmetic built in, it doesn't seem to allow for this.  It wants you to use minutes or seconds, etc.

The workaround is to temporarily use the UNIX_TIMESTAMP() and FROM_UNIXTIME() and deal with seconds instead of floating point numbers.  I little extra coding on the Java side as well.

So for example in Oracle I could just say:
UPDATE log_table SET end_time = end_time + 0.506851;

In PostgreSQL it's a bit more complicated, you can't just add a float:
UPDATE log_table SET end_time = end_time + (0.506851 * INTERVAL '1 day')

In MySQL you need different syntax AND you need to recalculate the value beforehand, so I would say:
UPDATE log_table SET end_time = FROM_UNIXTIME( UNIX_TIMESTAMP(end_time)+43792 );

Where 43792 seconds = 0.506851 * 12 * 60 * 60
That is 0.506 days times 12 hours times 60 minutes times 60 seconds
And it's assumed that end_time is of type DATETIME or TIMESTAMP.  This will NOT WORK with just DATE or TIME fields.

I guess you could probably do something like:
UPDATE log_table SET end_time = FROM_UNIXTIME( UNIX_TIMESTAMP(end_time)+ 0.506851 * 24 * 3600);
BUT there's probably some casting needed as well, I doubt that'll work as is.
And I'm not wild about doing the calculation in every line, though maybe it's no big deal, I haven't benchmarked it.

Note that the PostgreSQL trick of multiplying a floating point number  times the INTERVAL of 1 day  doesn't seem to work in MySQL.  I've tried 3 variants with parenthesis, etc.  So these do NOT work:
UPDATE log_table SET end_time = DATE_ADD(start_time,  0.506851 * INTERVAL '1' DAY );
UPDATE log_table SET end_time = DATE_ADD(start_time,  ( 0.506851 * INTERVAL '1' DAY) );
UPDATE log_table SET end_time = DATE_ADD(start_time,  0.506851 * (INTERVAL '1' DAY) );

The only other trick is that if you're building your SQL string in Java, you need to tell it to NOT use scientific / exponential notation.  So you do NOT want 4.3792E4.

To convert a float or double in Java to a String that you can then stuff into SQL you can use:

// Start with approx half of a day (an arbitrary floating point number)
double myDelta = 0.506851;
// Convert to Seconds
double newDelta = myDelta * 3600 * 24;
// Format to a rounded string, with no scientific notation
java.text.NumberFormat nf = java.text.NumberFormat.getInstance();
// Turn off fancy number formatting
nf.setGroupingUsed( false );
nf.setMinimumFractionDigits( 0 );
nf.setMaximumFractionDigits( 0 );
// And now the final String
String deltaStr = nf.format( newDelta );

If somebody comes up with a more direct route, please let me know.  The MySQL INTERVAL values for days seem to only accept integers.