rc.oracle - General purpose maintenance utilities for Oracle database, System V (Solaris, HP, AIX, Linux) start/stop scripts, and VCS Oracle agents.
rc.oracle sid=SID action=start flag=Y|N|NO_CHANGE|NC (Default Y) state=NOMOUNT|MOUNT|OPEN ALTER_MOUNT|ALTER_OPEN ALTER_MOUNT_OPEN (Default OPEN) dbtype=PRIMARY|STANDBY (Default PRIMARY) managed=Y|N (Default N) archivelog=Y|N|NO_CHANGE|NC (Default NO_CHANGE) verbose=Y|N (Default N)
rc.oracle sid=SID action=stop flag=Y|N|NO_CHANGE|NC (Default N) method=IMMEDIATE|ABORT|ROLLOVER (Default ROLLOVER) rolltime=number in seconds (Default 300) verbose=Y|N (Default N)
rc.oracle sid=SID:psonly|nomonitor action=STAT|STATUS flag=Y|N (Default N) dtime=number (Default 999999) verbose=Y|N (Default N)
rc.oracle sid=SID action=RESTART verbose=Y|N (Default N)
rc.oracle sid=SID action=FLAG flag=Y|N|SHOW (Default SHOW)
S|Knumberoracle start|stop|restart|status|stat
S|Knumberoracle-SID start|stop|restart|status|stat
online SID[:psonly|nomonitor]
online . SID[:psonly|nomonitor]
offline SID[:psonly|nomonitor]
offline . SID[:psonly|nomonitor]
clean SID[:psonly|nomonitor]
clean . 0|1|2|3|4 SID[:psonly|nomonitor]
monitor SID[:psonly|nomonitor]
monitor . SID[:psonly|nomonitor]
* help=y
* version=y
rc.oracle is a single shell program to start, stop, test oracle database (primary and standby); check and update the flag; and send notifications, if configured, for abnormal database conditions, and when the N flag becomes too old.
rc.oracle is 3-in-1, multifunctional program serving different purposes when installed with different names. When named rc.oracle, its canonical name, it is a general purpose program; when linked as S|Knumberoracle or S|Knumberoracle-SID, it is used as System V start / stop script; when linked as online, offline, clean, and monitor, it is VCS (Veritas Cluster Server) Oracle agent.
( This is like Atlantic Coast Airlines. When flying for United it is United Express; when flying for Delta, it is Delta connection; when flying on its own, it is Independence Air. All shares the same management structure, same support channel, and payroll system. )
In order to describe each functionalities, a few concepts need to be cleared first.
Each database needs to be listed in oratab. This is not rc.oracle requirement, but part of standard installation procedure.
The complication comes that oratab is located in /var/opt/oracle on Solaris, in /etc on AIX and Linux. On systems that use /etc/oratab, it is better to make the following changes once:
mkdir -p /var/opt/oracle cp /etc/oratab /var/opt/oracle/oratab chown oracle:dba /var/opt/oracle /var/opt/oracle/oratab chmod 775 /var/opt/oracle chmod 664 /var/opt/oracle/oratab rm /etc/oratab ln -s ../var/opt/oracle/oratab /etc/oratab
and don't think about it. However, if you want to think about it please read MISCELLANEOUS INFORMATION section.
Each database has a flag in oratab. As documented in Oracle dbstart program, the Y flag means to start the database when server reboots. As a corollary, this flag is an indication the database is up, and can be monitored.
The VCS agent uses the flag to determine whether to monitor the database or not. If you shutdown the database for maintenance, or cold backup, you should change the flag to N so that the agent won't start it up. Change the flag to Y if you want to the agent to monitor it.
rc.oracle combines the two steps - database start/stop and flag change - for you.
When used as a general purpose utility for most common use, you specify two parameters, the Sid you are operate on, and the action which is either start, stop, restart, status, flag (show or change flag. The verbose=y option allows you to see what happens behind the scene.
For the START action, the default behavior is to startup an idle instance of a primary database, and change the flag to Y (Please see the FLAG section). However you can specify the flag to be either Y or N, or NO_CHANGE.
The default is to open the primary database, you can specify the state you want to alter database to, nomount, mount, open; or if the database was already in a nomount or mount state, you can specify the state change to alter_mount, alter_open, or alter_mount_open (first alter database mount, then alter database open).
If the database is an standby database, you need to tell rc.oracle either using the configuration file (see CONFIGURATION), or command line options (DBTYPE, MANAGED, LOGFILE) that:
DBTYPE=STANDBY : it is a standby database. MANAGED=Y : it is a managed standby. LOGFILE=Y : it is a managed standby using current logfile.
The command line options have higher precedence over configuration file.
The archivelog option allows you to change archivelog mode.
For the STOP action, the default behavior is to shutdown the instance and change the flag to N. Again you can specify the flag to be either Y or N, or NO_CHANGE.
The method option lets you specify how you want to shutdown. The default is ROLLOVER. rc.oracle first tries to shutdown IMMEDIATE. If it was not successful after ROLLTIME seconds (default 300), it executes shutdown ABORT and kill the ``shutdown immediate'' process. ``shutdown ABORT'' is followed by ``startup restrict open'' for immediate recovery, and then a clean ``shutdown''.
The FLAG action shows you the current flag, or change it to what you asked for.
The STATUS or STAT action checks the health of the database. If the FLAG option is Y, it checks the flag for the database in oratab and only test the database when the flag is Y. The DTIME only applies to STANDBY database. It checks if the elapsed time since the last log application exceeds the DTIME you specified.
The rc.oracle utility can used manually, or in other programs such as Oracle cold backup, database monitoring.
For example, in a Oracle rman cold backup, put in pre-script:
rc.oracle sid=<SID> action=stop rc.oracle sid=<SID> action=start state=mount
and in post script, put in post-script:
rc.oracle sid=<SID> action=start state=alter_open
This is better than putting
shutdown immediate startup mount ... alter database open
in rman script, because the default shutdown method for rc.oracle is ROLLOVER; and the post-script will startup the database even when the rman backup fails.
When the program is named as S|Knumberoracle-SID, or S|Knumberoracle, it can be used as system V start/stop scripts. The former is to start/stop the particular SID regardless the flag. The latter is to start/stop ALL Sids whoes flag is Y.
For example:
To start / stop Sids with Y flag on Solaris:
cp rc.oracle /etc/init.d/rc.oracle ln /etc/init.d/rc.oracle /etc/rc2.d/S90oracle ln /etc/init.d/rc.oracle /etc/rc0.d/K10oracle
The location and naming conventions are slightly different on other Sys V derived systems:
On AIX 5.2:
cp rc.oracle /etc/rc.d/init.d/rc.oracle ln /etc/rc.d/init.d/rc.oracle /etc/rc.d/rc2.d/S90oracle ln /etc/rc.d/init.d/rc.oracle /etc/rc.d/rc2.d/K10oracle
On SuSE Linux (2.4.7):
cp rc.oracle /etc/init.d/rc.oracle ln /etc/init.d/rc.oracle /etc/init.d/rc3.d/S90oracle ln /etc/init.d/rc.oracle /etc/init.d/rc3.d/K90oracle
rc.oracle supports chkconfig. On redhat based systems, you can use chkconfig to add and delete the service:
# chkconfig --add rc.oracle # chkconfig --list rc.oracle rc.oracle 0:off 1:off 2:on 3:on 4:on 5:on 6:off # chkconfig --del rc.oracle
When the program is named online, offline, clean, and monitor, it can be used as VCS (Veritas Cluster Server) Oracle agent.
This is done by installing the Solaris package MERotas provided in rc.oracle distribution. The following procedure is for illustration purpose only.
#* For illustration purpose only, please use package for your platform. *#
mkdir -p /opt/VRTSvcs/bin/Oracle
cp rc.oracle /opt/VRTSvcs/bin/Oracle/rc.oracle
cd /opt/VRTSvcs/bin/Oracle
for i in online offline clean monitor; do
ln rc.oracle $i
done
ln -s ../ScriptAgent OracleAgent # VCS provided
Please also see add_oracle_to_vcs.ksh in the distribution on how to add the service to VCS. Sid is the sole parameter that needs to register in VCS. Basically,
online SID = rc.oracle sid=<SID> action=start flag=Y offline SID = rc.oracle sid=<SID> action=stop method=immediate flag=Y clean SID = rc.oracle sid=<SID> action=stop method=abort flag=Y monitor SID = rc.oracle sid=<SID> action=stat flag=Y
We can run the commands on the left side manually before testing VCS.
The extended Sid can be used in the format SID:psonly|nomonitor. The qualifiers psonly and nomonitor, which are case inSENsiTIVE, are only applicable how the Sid is tested.
psonly, as the name suggests, is used to test only the shadow processes, pmon and smon. If the two processes exist, the instance is considered up even when it is in nomount state.
nomonitor, as the name suggests, is used not to test the the database, but considers it to be up.
These two qualifiers are used mainly for VCS, to prevent it from automatically restart the database. It does not affect the failover.
A monitor program should be in place when used these two qualifiers.
ZERO configuration is required for normal use of rc.oracle.
However, following the philosophy of ``simple things should be easy but complicated things should be possible'', the configuration file in $ORACLE_BASE/.ora_SID_env.ksh can be used for these parameters:
DBTYPE MANAGED MAILLIST PAGELIST FTIME UMASK
The format of specification is ``key = value''. The left side should contains the key as one word; the right side should contain the value and possibly spaces. The key is case INsenSItive.
So the following format all valid, and equivalent:
# MailList = dba_list@foo.com MAILlist=dba_list@foo.com
DBTYPE and MANAGED are introduced before.
MAILLIST and PAGELIST are used for VCS agent notification. The agent sends page to PAGELIST when (a) monitor failed; (b) monitor produced an unexpected status; (c) database start and stop. Unfortunately, monitor can not send page when the it times out. But hopefully, the notification will be sent by clean and online. The agent sends mail to MAILLIST when the N flag for the database becomes too old (older than FTIME if set, or 1 day).
The FTIME is a number in minutes, which provides a way for you to tell rc.oracle that you the flag is changed to N for x minutes. rc.oracle will send email to MAILLIST if this limit is exceeded, once per day between 08:00 and 08:15 hours.
UMASK is the umask value under which to start Oracle.
The VCS monitor spits the following informational messages which can be used for log monitor. The ``110'' and ``100'' are return code required by VCS. They are not part of the message.
110 YYYY-MM-DD HH24:MI:SS <SID>[:psonly|nomonitor] was up. 110 YYYY-MM-DD HH24:MI:SS <SID>[:psonly|nomonitor] needed attention. Details in <logdir>. 110 YYYY-MM-DD HH24:MI:SS <SID>[:psonly|nomonitor] was not tested with recent N flag. 110 YYYY-MM-DD HH24:MI:SS <SID>[:psonly|nomonitor] was not tested with old N flag. 110 YYYY-MM-DD HH24:MI:SS <SID>[:psonly|nomonitor] had unexpected status. Details in <logdir>. 100 YYYY-MM-DD HH24:MI:SS <SID>[:psonly|nomonitor] was down. Details in <logdir>.
The return codes for system V start / stop script are not used. The brief message informs the SA what is going on:
YYYY-MM-DD HH24:MI:SS Running S|K<number>oracle with start|stop option. This may take a few minutes. When in doubt, please check <logdir>. Working on <Sid> ... YYYY-MM-DD HH24:MI:SS <SID> was up|down.
The return codes for start/stop should not be trusted. Either check the specific messages produced by sqlplus, or follow up start/stop with stat. The return code and informational messages for rc.oracle status option are:
0 YYYY-MM-DD HH24:MI:SS <SID>[:psonly|nomonitor] was up. 1 YYYY-MM-DD HH24:MI:SS <SID>[:psonly|nomonitor] was down. !0 YYYY-MM-DD HH24:MI:SS <other messages.>
For example, you can say:
rc.oracle sid=<SID> action=stat || print "<SID> did not come up."
For flag change, it is better to check directly, for example:
[[ $(rc.oracle sid=<SID> action=flag) == "Y" ]] || exit 1
The overriding philosophy used in developing this utility is KISS (Keep It Simple Stupid).
This philosophy is reflected in one script zero configuration (for normal usage), and one parameter to pass to VCS (unfortunately, the one's can not be changed to zero's).
The simplicity helps in following areas:
Maintenance. It is easier to maintain one script than N scripts. It eliminates the confusion what script is used for what purposes.
Deployment. Just copy the file to the right place, link with right names. (pkgadd and chkconfig for VCS and on Linux makes things easier). Zero coding is needed. Only one parameter Sid is communicated to the SA's for VCS.
Operation. Due to a simple interface with VCS (one parameter), during Oracle upgrade, all we need to do is change the flag N, upgrade, and change the flag back to Y. And SA does not need get involved the database is shutdown for maintenance, or cold backup.
M1. ORATAB LOCATION
If rc.oracle only reads the file, it does not matter whether it is located in a Oracle owned directory /var/opt/oracle, or a system directory /etc. It can detect the location and and use it.
rc.oracle changes the the flag in certain operations, and in order to guarantee the read consistency, mv is used:
mv oratab.tmp oratab
If copy is used, the oratab is not usable during the operation, producing unpredictable errors. mv unlinks the old file, which is not possible if the executor does not own the directory. This also explains why link in reverse direction (/var/opt/oracle/oratab -> /etc/oratab) will not work.
The read consistency is guaranteed by the POSIX standard http://www.opengroup.org/onlinepubs/009695399/:
If the link named by the new argument exists and the file's link count becomes 0 when it is removed and no process has the file open, the space occupied by the file shall be freed and the file shall no longer be accessible. If one or more processes have the file open when the last link is removed, the link shall be removed before rename() returns, but the removal of the file contents shall be postponed until all references to the file are closed.
M2. NON-MEANINGFUL RETURN CODE
Return values for start/stop is not meaningful as shown below, therefore one should always follow start/stop with stat.
$ svrmgrl SVRMGR> connect internal Connected. SVRMGR> shutdown ORA-01149: cannot shutdown - file 1 has online backup set ORA-01110: data file 1: '/ora01/oradata/IBKSAD1/system01.dbf' SVRMGR> ^D Server Manager complete.
$ echo $? 0
This is where the Oracle supplied dbstart and dbshut erred. The following is excerpted from dbshut,
... connect internal shutdown EOF
if test $? -eq 0 ; then
echo "Database \"${ORACLE_SID}\" shut down."
else
echo "Database \"${ORACLE_SID}\" not shut down."
fi
Michael Wang <xw73@columbia.edu> with help from many people. Contributions are acknowledged in the version history within the programs.
version 10.7, 2008-09-16, Michael Wang <xw73@columbia.edu>.
* Change [[ ${i} = *=\?* ]] to [[ ${i} = *=[?]* ]]
+ The former does not work on ksh88 on HP although it should.
version 10.6, 2008-05-26, Michael Wang <xw73@columbia.edu>. * Port to ksh 88 requested and sponsored by + DataBase Intelligence Group (http://dbigusa.com). * Various fixes and clean up.
version 10.5, 2004-10-18, Michael Wang <xw73@columbia.edu>. * Added UMASK configuration parameter. * Enhanced get_var function. * Fixed test relic in restarting RAC ocmstart.
version 10.4, 2004-08-29, Michael Wang <xw73@columbia.edu>. * Tested on standalone AIX 5.2 with reboot.
version 10.3, 2004-08-23, Michael Wang <xw73@columbia.edu>. * revert to changing flag to Y after online|offline|clean + per discovery of VCS behavior by Jose Bacalla. * Added flat_opt function.
version 10.2, 2004-08-21, Michael Wang <xw73@columbia.edu>. * Tested Linux RAC (Oracle 9.2.0.4 on RHEL 3.0 compatible) with reboot.
version 10.1, 2004-08-20, Michael Wang <xw73@columbia.edu>. * Tested on AIX 5.2 VCS with Roy Morton.
version 10.0, 2004-08-10, Michael Wang <xw73@columbia.edu>. * Rewrite to address the following issues: + PSONLY and NOMONITOR options. + Optional notification for monitor failure and state change. + Self maintained logs: clean user experience and capability to debug. + ROLLOVER shutdown method as default: IMMEDIATE -> ABORT. + STARTUP state, eg: ALTER_MOUNT, ALTER_OPEN. + Linux (redhat) support: chkconfig and subsys lock. + RAC support: startup oracm. + STANDBY database support. + modularity attempt for futher development: rc_oracle function.
version 9.5, 2003-03-04, Michael Wang <xw73@columbia.edu>. * Changed "ksh $0" to "$0" so that what in #! line will be used. * Set UNIX95 for HP-UX to get SUSv2 bahavior for ps command (-o option).
version 9.4b, 2002-12-25, Michael Wang <xw73@columbia.edu>.
* Changed _grep "[o]ra_.*_$ORACLE_SID *$")_
to _grep "[0-9] ora_.*_$ORACLE_SID *$")_
Otherwise, it will grep "grep ora_pmon_SID".
version 9.4a, 2002-11-20, Michael Wang <xw73@columbia.edu>. * Default method=smart; "sleep 1" => "sleep 5".
version 9.4, 2002-06-17, Michael Wang <xw73@columbia.edu>.
* Changed "first_time = (SELECT MAX(first_time) FROM v\$log_history)"
to "sequence# = (SELECT MAX(sequence#) FROM v\$log_history)"
to ensure single row.
* Initialize SMARTIME.
version 9.3, 2002-04-25, Michael Wang <xw73@columbia.edu>. * Added method=smart. * Added version=y.
version 9.2, 2002-01-14, Michael Wang <xw73@columbia.edu>. * Fixed the problem that "help=y" leaves an empty dir in /tmp. * Changed "exit -- $?" to "exit $?": (exit -- 1); print $? => 0 in ksh88.
version 9.1, 2001-12-01, Michael Wang (xw73@columbia.edu).
* New way of getting SID_LINE:
(1) $ORACLE_BASE/.rc.oracle.cf: #_ORACLE_SID:*
(2) /var/opt/oracle/oratab: #_ORACLE_SID:*
(3) /var/opt/oracle/oratab: ORACLE_SID:*
* Relaxed GLOBAL_NAME test.
* Derive owner from $ORACLE_HOME/bin/oracle.
* Default UPASS is "/ as sysdba". Oracle 7 needs to specify different UPASS.
* Depress output for action=stat verbose=n.
* Fix FTIME issue:
j=; unset j; j=011; print "$j" => 9
typeset -Z6 j; j=; print "$j" => "<sp><sp><sp><sp><sp><sp>"
- Bourne shell detection simplification. "PATH= print" and (print) forks,
and only forked stderr can be redirected in Bourne shell.
- Spool to a tempfile in /tmp instead of /var/opt/oracle.
- Changed "exit -- -1" to "exit 127". (exit -- -1); print $? => 0 in ksh88.
version 9.0, 05/26/2001, Michael Wang (xw73@columbia.edu).
* Eliminate "svrmgrl" and "connect internal" for Oracle 9i+ where these
are no longer supported.
* Creating an Oracle user is optional (put "/ as sysdba" in UPASS field).
This is not new feature but never documented, never found and used.
* Added pager/email notification, and action=page|mail, message=... options
with help from Andrew Blatt, Janet Zhu on CVS. "A nasty solution for a
nasty problem in a nasty world"(TM).
* Check FLAG before lsnrctl to ease the upgrade per Raj Pande.
* "help=y" now uses "real man"(TM) page. Eliminated less helpful online help.
* More generic and safer removal of .aud and .trc files:
Solaris: ${lower_case_sid}_ora_$spid.trc, Linux: ora_spid.trc,
AIX: ora_$spid_${lower_case_sid}.trc. Oracle Enhancement #: 1665035.
* FLAG/FTIME update fix.
- used POSIX PATH.
- Use of POSIX "id -u" and "id -un" command.
- Added "print" in additon to "$RANDOM" for ksh test to exclude bash.
- Change "exit -1" to "exit -- -1" to make it work with PD KSH
(PD KSH v5.2.14 99/07/13.2 on Red Hat Linux 7.1, Kernel 2.4.4).
- Workaround different behaviors on "Ctrl-C" on trap between PD KSH and
AT&T KSH: PD KSH does not run exit trap upon "Ctrl-C", AT&T KSH does.
- uses cp -p $ORATAB $ORATAB.tmp to create a temp file with same permission.
- Eliminate fork (grep) in processing SID_LINE.
- More concise KSH construct for startup with and without $PFILE.
version 5.4, 02/21/2001, Michael Wang (xw73@columbia.edu).
* Tightened up the locking code.
* Added restart option for convenience only per Raj Pande. It stop and start
the database with default options.
* Added identifier "$ORACLE_SID: " for flag change message.
* exit 255 => exit -1
* Change [[ -z $ORATAB && -r $i ]] && ORATAB=$i to
[[ -r $i ]] && { ORATAB=$i; break; }
* Last line of start/stop options was: [[ $SHOW = Y ]] && exit -1.
[[ $SHOW = Y ]] would be the last command if it is false and hence its
status becomes the status of the script. The last line is changed to:
(( status == 0 && fstatus == 0 )).
version 5.3, 01/31/2001, Michael Wang (xw73@columbia.edu).
* Added date before "tail -1".
* Change [[ -z $Perl && -x $i ]] && Perl=$i to
[[ -x $i ]] && { Perl=$i; break; } for efficiency.
version 5.2, 01/26/2001, Michael Wang (xw73@columbia.edu). * Added ftime= option for start/stop.
version 5.11, 01/19/2001, Michael Wang (xw73@columbia.edu).
* Replace sed with ksh built-in:
THE_UDUMP_DEST=$ORACLE_HOME${THE_UDUMP_DEST#?}
* condition rm to eliminate fork when file does not exist:
[[ -f $i_aud ]] && rm -f $i_aud
version 5.1, 01/05/2000, Michael Wang (xw73@columbia.edu). * change "exit 0" to "exit $?" in oracle-SID case. This affects backup. * change "FOREVER" to "1 DAY".
version 5.0, 12/21/2000, Michael Wang (xw73@columbia.edu).
To incorporate the modifications made by Raj Pande and Pradeep Malhotra:
* Accepts sid=..., action=..., and other options when the program is
named rc.oracle or any other names except the following:
oracle-SID, [SK]<number>oracle, online, offline, clean, monitor.
* When the program is named oracle-SID, it will perform passed action
for this SID. Program with this name is ready to be used as Solaris
start/stop script for this SID without the need for a wrapper script.
This is the old behavior.
* When the program is named S<number>oracle, or K<number>oracle,
it will perform passed start or stop action for ALL oracle SIDs in
oratab with Y flag. This is to simulate dbstart/dbshut behavior.
* When the program is named online, offline, clean, monitor, it will
start, stop immediate, stop abort, and test oracle database for passwd
SID. Programs with these names are ready to be used as VCS entry points
without the need for wrapper scripts.
* Flag check. If the lifetime (in number of minutes) of N flag is specified
(9th field in oratab) and Perl is available (VCS has Perl), then it will
check if N flag is older than specified minutes, otherwise it will check
if N flag is older than 1 day.
* VERBOSE is defaulted to N instead of Y.
Other changes:
* FILTER=Y for action=stat to filter out oerrs, specified as the 8th field
in oratab in the format of oerr1|oerr2|... Example:
ora-12345|ora-23456|...
* Streamline the option processing.
* Added exit code 255 for "catch-all" errors.
* Some change in error message for consistency.
* "echo" is replaced by "print/print -r --" for robustness and compatibility.
version 4.0, 10/10/2000, Michael Wang (xw73@columbia.edu).
* version number advanced beyond pi. Tested on the following platforms:
- ksh Version M 1993-12-28 j
- ksh Version M-12/28/93d on Sun Solaris 2.6, 7 (dtksh).
- ksh Version M-11/16/88i on Sun Solaris 2.6, 7.
- ksh Version M-11/16/88f on IBM AIX 4.3.3.
- PD KSH v5.2.14 99/07/13.2 on Red Hat Linux 6.2.
* Exit code changed from 0 to 4 when Oracle executable can not be found.
* Added start option ARCHIVELOG=([YN0][YN0]|Y|N|NULL|R) per discussion
with Joe Ng.
* Array can not be reset on Version M-11/16/88f on AIX 4.3.3
i[0]=; typeset -u i; i=; unset i; i=b; echo $i => B
Changed to i to ix to avoid conflict.
* "==" in [[ string == pattern ]] is replaced by "=" for compatibility
with older Korn Shell (eg Version M-11/16/88f).
* Added " *" in i=$(ps -eaf | egrep "[0-9] ora_.*_$ORACLE_SID *$")
AIX 4.3 ps output has blank at end of line.
* Get rid of the_space_holder.
* Unset name for name=value pair. Example, TEST=.
* changed _echo "$ ..."_ to _echo "\$ ..."_. The former expression
exhibits different behaviour under differnet ksh versions.
version 3.14159, 08/18/2000, Michael Wang (xw73@columbia.edu).
* The test method for primary database is rolled back to version 2.0:
SELECT global_name from global_name, and compare the global_name
before the "." with ORACLE_SID instead of creating tables per discussion
with Rana Chander, Matthew Soloman, Susan Gruebel, and Sheck Cho.
Yuriy Leyzerovskiy of Oracle Corp provided grant statements on v$tables.
Prerequisite:
- /var/opt/oracle/oratab.SID:
SID:ORACLE_HOME:Y/N:PFILE:OWNER:DBTYPE:UPASS
- create user oracle_probe identified by oracle_probe
default tablespace temp temporary tablespace temp;
- grant create session to oracle_probe;
- grant select on SYS.V_$PARAMETER to oracle_probe;
- grant select on SYS.V_$SESSION to oracle_probe;
- grant select on SYS.V_$MYSTAT to oracle_probe;
- grant select on SYS.V_$PROCESS to oracle_probe;
IF global_name is not already set to ORACLE_SID,
- alter database rename global_name to <ORACLE_SID>;
On a box with multiple oracle users (oracle, oracle01, oracle02, ...):
- chown oracle:dba /var/opt/oracle # on all nodes of cluster
- chmod 775 /var/opt/oracle # on all nodes of cluster
* Added $ anchor in i=$(ps -eaf | egrep "[0-9] ora_.*_$ORACLE_SID$")
to cover the case that databases "foo" and "foobar" reside on same box.
* Rewrite "[[ -z $(eval echo \$$i) ]]" as "eval [[ -z \$$i ]]".
per suggestion from Dan A. Mercer on the internet. This avoids
unnecessary fork(), and reduces cpu time.
Tested on Sun E250 with 2x400MHz cpu, 2GB RAM, A1000 storage:
$ time sh oracle-mysid stat verbose=n
THE DATABASE mysid IS UP.
real 0m0.43s
user 0m0.28s
sys 0m0.21s
version 3.1415, 07/22/2000, Michael Wang (xw73@columbia.edu).
* Expanded oratab to include pfile, owner, and dbtype (PRIMARY, STANDBY,
STANDBY/MANAGED ) to support the new functionalities:
- Support running as different $OWNER.
- Support "startup pfile=<...>" option.
- Support standby database.
* Added archive option to archive log current.
* Enhanced SHOW=Y option to display SQL statement.
* FLAG=NULL option for start/stop to leave FLAG alone.
* Changed svrmgrl to sqlplus in testing PRIMARY (versus STANDBY) database.
* Eliminated TABLESPACE clause, instead using the test user's default one.
* Change on PATH and RUN_USER for Linux compatibility.
* unset LD_LIBRARY_PATH.
Properly linked oracle executables do not need LD_LIBRARY_PATH.
In Oracle 8i, set LD_LIBRARY_PATH causes listener problem, Ref:
TAR 12953862.600, Doc ID 1023333.1, Bug ID 1147434.
* "startup restrict open" and "shutdown" only runs after
"shutdown abort" but not "shutdown immediate".
* MASSIVE code clean up to support above functionalities and for
programming purity and efficiency.
version 3.141, 07/15/2000, Michael Wang (xw73@columbia.edu).
* Fixed the race condition when multiple processes test the database.
The bug was found by Janet Zhu and Susan Gruebel in an environment where
the script is used for backup as well as for QualixHA+.
The fix is to replace the fixed table_name "oracle_probe" with
variable name T<PID>, and slightly changed test sequence as shown
below. The old test sequence is indicated by (<number>) at end of line.
(1) drop table T<PID> without verification (N/A)
(2) create table T<PID> with verification (2)
(3) insert table T<PID> with verification (3)
(4) update table T<PID> with verification (4)
(5) drop table T<PID> with verification (1)
This fix covers the situation when table is inadvertently left behind.
version 3.14, 03/17/2000, Michael Wang (xw73@columbia.edu). * More reliable method of updating oratab.
version 3.1, 01/20/2000, Michael Wang (xw73@columbia.edu). * Minor midifications in Oracle env variable determination and verification.
version 3.0, 12/27/1999, Michael Wang (xw73@columbia.edu).
* Enhancement and cleanup. exp style user interface: exp help=Y
The line: [ "$RANDOM" = "$RANDOM" ] && exec ksh "$0" "$@"
is provided by David Korn.
version 2.0, 05/27/1999, Michael Wang (xw73@columbia.edu).
* Susan Gruebel provided fix for failover during hot backup.
* Susan Gruebel suggested test if database is open.
* ORATAB search order: oratab.$ORACLE_SID, oratab
* Fixed the order of FLAG and database state change.
start: START DATABASE, FLAG -> Y
stop: FLAG -> N, STOP DATABASE
version 1.0, 07/29/1998, Michael Wang (xw73@columbia.edu). * IPO. * Mikhail Rasol provided code for finding svrmgrl PID.