Its just a simple change right?

Putting a database in archivelog mode? Set the destination, the archive log format, shutdown immediate, startup mount, alter database archivelog, alter database open, alter system archive log current. Done.

Except, alter system archive log current failed:

ORA-19504: failed to create file "/u02/archive/ORCL/ORCL_1_1_686533687.dbf"
ORA-27040: file create error, unable to create file
Linux Error: 13: Permission denied

Huh, thats ok. This is an Oracle EBS environment and I created the archive destination directory with the applmgr user, not oracle. No problem, su - root, chown oracle:dba . , done.

alter system archive log current again, success!

Can't remember what query I issued next (its been a hazzy night) but I was greeted with the following error:

Errors in file /u01/oracle/ORCL/db/tech_st/10.2.0/admin/ORCL_myserver/bdump/orcl_smon_10547.trc:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/oracle/ORCL/db/apps_st/data/system05.dbf'

WTF? Just some background about this environment. I only cloned it the day before and I didn't have any backups. I was putting the database in archivelog mode so I could take backups.

This error typically means the datafile is offline, tho i'm not sure why. So I bring it online:

alter database datafile 5 online;
recover datafile 5;


ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

Hrmm.. ok, so I try to open the database:

SQL> alter database open;
alter database open
ERROR at line 1:
ORA-03113: end-of-file on communication channel

WTF? Check the alert log:

Errors in file /u01/oracle/ORCL/db/tech_st/10.2.0/admin/ORCL_rc1657/bdump/uat2_lgwr_11557.trc:
ORA-00600: internal error code, arguments: [3712], [1], [1], [1388],
[3796296244], [1388], [3796296241], []

Lovely. At this point I have 3 options:

1. Work with Oracle Support.
2. Rebuild the environment since I don't have any backups. Developers lose 1 day.
3. Back it up, research the error and see if I can find a solution.

I worked with Oracle support up until 6:30am, at which point I believe my analyst was finished for the day and went home. This is a UAT environment, so the SR is a P2. I wasn't expecting 24x7 support, so this was just bad timing.

I searched metalink and didn't find any solid hits. So I tried google. Not many hits, but I received one exact match, in chinese. Google graciously offered to translate the page for me and about the only useful text on the page was:

"Who came across the internal bug? 我用了alter database clear logfile group 后再重启动数据库就发生这个错误了。 I used the alter database clear logfile group restart the database after this error has occurred. "

At this point I had an hour left before testers started and only 2 options remaining. I figure I was going to have to rebuild the database anyways, so i'd try clearing the log files. I cleared the inactive groups without issue. The only group remaining was the current and if the clear logfile group command failed i'd have to proceed with the rebuild.

From the alert log:

alter database clear unarchived logfile group 1
Tue May 12 08:14:34 2009
Thread 1 advanced to log sequence 6 (preparation to clear logfile)
Clearing online log 1 of thread 1 sequence number 5
Tue May 12 08:17:34 2009
Completed: alter database clear unarchived logfile group 1


Alter database open; Success!!!

Thats my 6 hr simple change (most of that time was working with Oracle Support, taking a backup [only 5MB/s IO??!] of the environment before I played around with it, etc.) Now I need some sleep. You know your tired when you almost use dish washing liquid for cream in your coffee. Luckily an attentive coworker prevented disaster! Normally an all nighter doesn't leave me this drained but the previous night I only had 3 hrs sleep.


How to Change an Application Server User Password

The solution depends on if you are using Oracle Internet Directory (OID) or not. If you are using OID you can login to the OID Console or the oidadmin GUI to reset the password easily.

But what if your application server is stand alone? If your trying to reset the oc4jadmin superuser account then the only solution is manually modify the $ORACLE_HOME/j2ee/home/config/system-jazn-data.xml file.

Search the file for your the username and place the new password, prefixed with an exclamation point "!" between the <credentials> </credentials> delimiters. I am assuming that you have to prefix it with the ! so that Oracle knows how to handle the password. ie, that its not encrypted.

Restart the application server and verify that you can login.

How about regular user accounts tho? Surprisingly you have to follow the same process. I must have poked around in the Application Server Console for half an hour in disbelief that I couldn't reset a user password without knowing the old password.

If I missed something or if you know of another way to reset user accounts, please post a comment.


Oracle Waives Extended Support Fees

Here is some interesting news from Collaborate today. Oracle has waived some Extended Support fees for some product lines including E-Business Suite 11i10 and 10gR2. Good news for those that can't afford to upgrade at the moment due to the economy.

For Details Click Here.