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.


amresh said...

Hi Thanks for blog,

Can you confirm if you have use reset log while creating control file


B said...

You just saved my arse with this!!!

It's frustrating that MetaLink has such long winded solutions, but none of them mention this basic fix.

Horray for the chinese page you found...

Sridevi Koduru said...

Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.