How to force oracle to hang

[Duncan Edmonstone]

Hi,

I'm testing some MC/ServiceGuard monitoring scripts, and I'm looking for a reliable way to make Oracle 'hang' (as opposed to 'crash').

I have a script that connects to oracle, and updates a row in a table with the current date. The script is timed, and if it doesn't respond after X seconds then the monitor can cause a failover/restart of oracle. Now I want to test this out, but to do this I need to force oracle to 'hang'.

My first thought was that

alter system archive log stop;

followed by

alter system switch logfile;

several times until all redo log groups were used would work. This has indeed hung the session that issued the switch logfile command, but my monitor script is still happily chugging away updating the database every few seconds... I'm guessing that its still using the last redo log, and the whole database won't hang completely until this log is 'full' (which with the tiny updates I'm doing, and the size of the redo logs could take a while).

Does anyone know a quicker/easier way of making the database 'hang' in this realistic manner ( I know I could send SIGSTOP to all the server processes, but thats not really a 'realistic' test)

10 points for the perfect answer.

Cheers

Duncan

[Michael Steele_2]

Kill the lmon daemon.

The Advanced SG class taught by HP has an OPS lab which tests for oracle failovers. I can provide you with what I have since it uses the oracle1.sh monitoring script.

Verify cluster and both instances are running:

SVRMGR>connect internal
SVRMGR>select * from s$active_instanaces
SVRMGR>shutdown
SVRMGR>exit
$exit
#vgchange -a n vg_ops (* vg defined in oracle_package_cntl. *)

Add the packages and check that they continue running:

# cmrunpkg -n node1 ora_pkg
# cmviewcl
etc.

Test reformation:

Power off node1. Repeat for node2.

Test reformation after internal failure:

TOC on node1. Repeat node2.

Check instances:

kill lmon on node1. Repeat node2.

Check instances:

Run 'ins_rows_1' on node1 and kill lmon. Repeat node 2.

NOTE: This last test will be a little different for non OPS servers.

[Duncan Edmonstone]

yeah, I know that will hang an OPS/RAC database, but single node Oracle databases don't have an lmon process, so that isn't going to work...

thanks,

Duncan

[John Bolene]

If you have hot pluggable disk drives, unplug one that has the database on it.

[Michael Steele_2]

kill pmon.

[Duncan Edmonstone]

Good point John, but I'd expect my EMS HA Monitors to pick that up before my oracle monitor does... I'm really looking to test a software hang of the database rather than a hang caused by failing hardware... and besides on my test kit I'm using internal disks!

Thanks

Duncan

[Christian Gebhardt · Accepted solution]

Hi

to fill the redologs the best way is to inesrt and delete data.

create table redofill_test (col1 char(2000)) tablespace ;

begin
for i in 1 .. 10000
loop
insert into redofill_test values ('a');
commit;
delete from redofill_test;
commit;
end loop;
end;
/

drop table redofill_test;


char is a good choice for the datatype because oracle allocates 2000 bytes for each row.

Chris

[Duncan Edmonstone]

Killing pmon doesn't cause a hang, it causes the database to crash.

And I get this from my insert script:

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

So not a hang, but a crash.

Keep em coming!

Duncan

[Dietmar Konermann]

Maybe you should only suspend the pmon? Afterwards you could resume it again.

# kill -STOP
...
# kill -CONT

Best regards...
Dietmar.

[Duncan Edmonstone]

Chris, Dietmar,

I'll give these a try...


Thanks

Duncan

[Yogeeraj_1]

hi,

how about locking a table in one session or keeping a session active.

and issuing a "shutdown normal" (instead of shutdown immediate)

haven't tried it though..

hth
Yogeeraj

[Duncan Edmonstone]

Chris,

That did the trick - when the redo log filled everything started to hang... and my script did detect the problem.

Dietmar,

Sending a SIGSTOP to pmon didn't seem to have any effect - perhaps I needed to leave it for longer.


Yogeeraj,

I suspect that this will just produce a different error from my monitor script... it logs into the database as a seperate session every time it polls the database, so once a shutdown command has been issued, rather than a hang I'll just get a "ORA-01090: shutdown in progress - connection is not permitted" message from sqlplus.

Any other ideas will also score points. The more different methods I have to test this the better.

Cheers

Duncan

[Yogeeraj_1]

hi Duncan,

are you running 9i?

If yes, here's the good news!

In 9i, we have an alter database quiesce available with the resource manager.
This'll let you "pause" the system (put people on hold in effect).

Good luck
Yogeeraj

[Yogeeraj_1]

hi again,

Note that you can find more info in the Oracle9i Data Guard Concepts and Administration Release 2 Chapter 4.


You would do something like:

Ensure that Resource Manager is configured. (Resource Manager is a requirement when putting the database into a quiesced state.)

then

alter system quiesce restricted;

then Unquiesce the system and and switch logfiles:
alter system unquiesce;

alter system switch logfile;


regards
Yogeeraj

[Duncan Edmonstone]

Thanks Yogeeraj,

unfortunately I'm on 8i...

but i'll take note for when I have a 9i DB to cluster.

Cheers

Duncan

[R. Allan Hicks]

The way that I've had Oracle stop on me without crashing is to fill up the archive log destination. When log writer tries to write the archive log and there is no room, it does a "polite" wait. I've gotten caugth a full times with the destination full. It doesn't do any permanent damage and as soon as space becomes available it will start back without losing data.

If you stop archiving as you said on your initial submittal, Oracle may not care if you do a switchlog unless it feels that it has to have dbwriter write the changes before it can re-use the log, it won't hang.

Hope this helps.

[Indira Aramandla]

Hi Duncan,

Here is another test method to test your MC/ServiceGuard monitoring scripts.

I have picked this one form another client who accidently caused their database to hang, but seems to be a good test case.

Database will hang after a while (2 hours) if listener is started by root with "su -"

Eg:- "su - oracle -c "lsnrtl start LISTENER_name.world"

We know that the listener needs to be started by the oracle user, not root. It is started as root with the above command, so in fact it should be as if it was started by oracle. This will work fine at the command line. But if it is started in the serviceguard script, with the same command as above everything works fine for about 2 hours. Then every session will start to hang.

Another way to cause your database to hang will be to issue the suspend command.
Database will be suspended by an ALTER SYSTEM SUSPEND statement

The other ways is to fill your archivelog destination directory which others mentioned.