Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to force oracle to hang

SOLVED
Go to solution
Duncan Edmonstone
Honored Contributor

How to force oracle to hang

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

HTH

Duncan
16 REPLIES
Michael Steele_2
Honored Contributor

Re: How to force oracle to hang

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.
Support Fatherhood - Stop Family Law
Duncan Edmonstone
Honored Contributor

Re: How to force oracle to hang

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

HTH

Duncan
John Bolene
Honored Contributor

Re: How to force oracle to hang

If you have hot pluggable disk drives, unplug one that has the database on it.
It is always a good day when you are launching rockets! http://tripolioklahoma.org, Mostly Missiles http://mostlymissiles.com
Michael Steele_2
Honored Contributor

Re: How to force oracle to hang

kill pmon.
Support Fatherhood - Stop Family Law
Duncan Edmonstone
Honored Contributor

Re: How to force oracle to hang

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

HTH

Duncan
Christian Gebhardt
Honored Contributor
Solution

Re: How to force oracle to hang

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
Honored Contributor

Re: How to force oracle to hang

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

HTH

Duncan
Dietmar Konermann
Honored Contributor

Re: How to force oracle to hang

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

# kill -STOP
...
# kill -CONT

Best regards...
Dietmar.
"Logic is the beginning of wisdom; not the end." -- Spock (Star Trek VI: The Undiscovered Country)
Duncan Edmonstone
Honored Contributor

Re: How to force oracle to hang

Chris, Dietmar,

I'll give these a try...


Thanks

Duncan

HTH

Duncan
Yogeeraj_1
Honored Contributor

Re: How to force oracle to hang

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Duncan Edmonstone
Honored Contributor

Re: How to force oracle to hang

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

HTH

Duncan
Yogeeraj_1
Honored Contributor

Re: How to force oracle to hang

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: How to force oracle to hang

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Duncan Edmonstone
Honored Contributor

Re: How to force oracle to hang

Thanks Yogeeraj,

unfortunately I'm on 8i...

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

Cheers

Duncan

HTH

Duncan
R. Allan Hicks
Trusted Contributor

Re: How to force oracle to hang

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.




"Only he who attempts the absurd is capable of achieving the impossible
Indira Aramandla
Honored Contributor

Re: How to force oracle to hang

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.
Never give up, Keep Trying