Using Expect to Manage Oracle Recovery

From: Dan Bikle <dbikle_at_rahul.net>
Date: 19 Dec 2001 01:32:36 GMT
Message-ID: <9voqnk$1nm$1@samba.rahul.net>

ExpectRollforward.html
12-18-2001 Bikle

A standby database may be in one of several states:

-Read Only Mode
-Manual Recovery Mode
-Managed Recovery Mode
-Activated Mode

A good description of the first three states may be found here: http://www.cs.rose-hulman.edu/docs/oracle-817/server.817/a76995/standbyc.htm#29765

I bumped into a situation recently when I had trouble moving a standby database from Manual Recovery Mode to Read Only mode. Actually, I had no trouble when I was 'at the helm' so to speak. I noticed that sometimes my standby database would complain that it needed more recovery when I tried to move it from Manual Recovery Mode to Read Only Mode. Any DBA who sees this situation knows what to do; he/she pushes the standby database through more recovery and then reattempts a move to Read Only Mode.

Scripting this reaction can be a bit tricky using an ordinary shell script.

The Expect script language, however, is well suited for emulating this DBA behavior.

We display some pseudo-code which helps explain the linked script below:

# define a useful function:
define proc attempt2open {} {
issue command: ALTER DATABASE OPEN READ ONLY; if db opens, return "READONLY"
if db indicates a need for recovery, return "NEEDSRECOVERY" else return nothing
}

main {} {
connect to db using sqlplus

issue command: SHUTDOWN ABORT
issue command: STARTUP NOMOUNT
issue command: ALTER DATABASE MOUNT STANDBY DATABASE;
# At this point the db may be in one of 2 states:

# NEEDSRECOVERY
# UNKNOWN set dbstate="UNKNOWN"
call attempt2open{} to attempt Read Only mode and also get dbstate while { $dbstate == "NEEDSRECOVERY" } {
issue command: RECOVER STANDBY DATABASE
wait for a log to get applied
issue command: CANCEL
call attempt2open{} to attempt Read Only mode and also get dbstate }

My observation of the above logic reveals that most calls to attempt2open{} result in the standby database moving to Read Only mode. Usually I do not enter the while {} loop. I have found with repeated experiments that every time that I have entered while loop, it did an excellent job of recovering the database to a state where it could be opened in Read Only mode. One experiment I need to run is to find out how the logic will work on a broken Standby Database.

An implementation of the above pseudo code in the Expect scripting language is linked below:

http://www.bikle.com/TThier/DBAtips/ExpectRollforward/mv2readOnlyMode-exp.txt

This is a trivial example. The Expect scripting language, however, has the ability to automate a wide variety of DBA behaviors for many different recovery scenarios. Received on Tue Dec 18 2001 - 19:32:36 CST