topic Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks in Operating System - HP-UX

Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Stuart Abramson_2 — Mon, 08 Sep 2003 15:22:50 GMT

We are copying an Oracle 7.3.4 database from HP-UX 11.0 to a new HP-UX 11.11 server, using techniques that we regularly use to clone databases on hpux 11.0.

We did this with a small database last week, som 16 GB, with no problem.

When we do this with a bigger database, about 100 GB, Oracle just hangs with no error message on the the command ALTER DATABASE OPEN RESETLOGS; when atempting to open the database.

We can't figure out why.. Is there somehting about hpux 11i, which would cause this?

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Steven E. Protter — Mon, 08 Sep 2003 15:29:53 GMT

I don't think its 11.11.

It could be the database is trying to open a file bigger than 2 GB on a filesystem that isn't set up for large files.

Oracle can act like that when onf of the filesystems involved in the command is just too full.

Perhaps check /var/adm/syslog/syslog.log while running the command. There should be some clues in there.

tail -f /var/adm/syslog/syslog.log

The alert log should show something.

I'd go back and check the filesystems involved and make sure the appropriate ones are large files enabled.

You might also want to post up kmtune output because kernel settings could be triggering this.

SEP

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Massimo Bianchi — Mon, 08 Sep 2003 15:32:07 GMT

You say hanging... are you sure ?

tail -f alert_SID.log tells you anything ?

I think that you can see smon working.

Once it took 20 minutes to do an open resetlogs, so time must not be a real issue.

How long did you wait ?

Second think to check: id the db in archivelog mode and the archiver is started?

ps -ef | grep arc

if this is the case, oracle IS hang, and you must start manually the archiver:

svrmgrl> connect internal;
svrmgrl> archive log start;

Massimo

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Brian Crabtree — Mon, 08 Sep 2003 19:19:34 GMT

What process do you use to clone the database (offline or online). Are you changing the database name at the same time, or is the name staying the same?

Also, do you see any processes spinning and taking up CPU under the 'oracle' user (or owner of the binaries). User processes or database processes.

And why are you on 7.3.4 still? The bare minimum that you should be on is 8.0.6.3.0.

Thanks,

Brian

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Indira Aramandla — Tue, 09 Sep 2003 03:11:37 GMT

Hi Stuart,

What procedure are you using to clone the database. And when you say it hangs for how long and have you checked for any error logs.

Check in the BDUMP directory for the alert log and see if there are ORA errors reported. And for the UNIX promt grep for PMON process and see if it is runnning.

And the other thing is Oracle RDBMS 7.3.4 is certified on HP 11i, but it is as per EMS (Extended Maintenance Support). Do you have EMS with oracle.

Please provide more info.

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Felicity Nancarrow — Tue, 09 Sep 2003 05:15:47 GMT

I had a similar problem recently on an 8.1.7 database and it turned out to be a problem with the file permissions\ownership on the Oracle datafiles.

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Felicity Nancarrow — Tue, 09 Sep 2003 05:16:04 GMT

I had a similar problem recently on an 8.1.7 database and it turned out to be a problem with the file permissions\ownership on the Oracle datafiles.

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

T G Manikandan — Tue, 09 Sep 2003 05:50:44 GMT

The best option would be to check the alert logs for to proceed!

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Stuart Abramson_2 — Tue, 09 Sep 2003 16:38:49 GMT

Here is what the alert log says:

Tue Sep 9 11:37:02 2003
Successful mount of redo thread 1.
Tue Sep 9 11:37:02 2003
Completed: CREATE CONTROLFILE REUSE
DATABASE "R11C" RESETLOGS...
Tue Sep 9 11:37:02 2003
ARCH: Archival stopped
Tue Sep 9 11:37:02 2003
ALTER DATABASE OPEN RESETLOGS
Tue Sep 9 11:37:03 2003
RESETLOGS after incomplete recovery UNTIL CHANGE 1171211638
ORA-344 signalled during: ALTER DATABASE OPEN RESETLOGS...
Shutting down instance (abort)
License high water mark = 11

When you look that up, it says:

oracle@acshps02 [PROD] > oerr ora 344
00344, 00000, "unable to recreate online log '%s'"
// *Cause: An I/O failure occurred when attempting to recreate an online as
// part of either ALTER DATABASE OPEN RESETLOGS or ALTER DATABASE
// CLEAR LOGFILE command.
// *Action: Correct the file/device as indicated by accompanying errors.

What I/O failure would that be? I don't see it?

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Brian Crabtree — Tue, 09 Sep 2003 19:11:16 GMT

Could be almost anything. Check your logfiles from the v$logfile view, and verify that there is enough space and that the directories exist. If you are changing the database name or structure, make sure that you have changed these entries as well via the "alter database rename file ..." command.

Thanks,

Brian

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Indira Aramandla — Tue, 09 Sep 2003 23:49:25 GMT

Hi,

An I/O failure could be of any reason. Soem of the reasons coule be......

1. Check that the file is accessible. Correct the file/device as indicated by accompanying errors.

2.Possible lack of space on device.

3. May be you have placed your datafiles in a different directory path. You recreated your controlfile and did not list the new location of the redo log groups.

Can you check the script which has the file name and path and verify with the physical name and path of the file.

IA

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Massimo Bianchi — Wed, 10 Sep 2003 13:03:51 GMT

Hi,
i don't know if you have already solved your problem, but this looks like some files (online logs) are damaged, maybe wrong permission, maybe wrong ownership, maybe they are really messed up by some mars activity :)

Seriuosly: since you have already issued the create controlfile with the resetlogs, you should make sure that those file are fully accessible by oracle, also check directories.

If in need, restore the online redolog, they are the compromised part.

Massimo

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Mike Miller_8 — Wed, 10 Sep 2003 13:03:58 GMT

The next time you try the 'alter database open resetlogs' monitor your other volumes and see if one of them becomes pegged or grows quickly unexpectedly.

Failing this, try doing a fsck on the volume that the log should go on.

= Mike =

Re: Oracle hangs on ALTER DATABASE OPEN RESETLOGS; when atempting to open the database. Thanks

Volker Borowski — Wed, 10 Sep 2003 15:47:54 GMT

Hi,

by any chance ...
Did you increase the size/number/location of the logfiles in your sourcedb and did not adjust the filesystem size/layout on target ?
...and simply run out of space / existing directories ?

Volker