In this blog post, we would like to discuss the basics of Oracle Restart when using it with Oracle Standard Edition in a standby database configuration.
Oracle Restart is integrated with Oracle Data Guard and the Data Guard broker, but this does not mean for those of you who use Standard Edition (which does not include Data Guard functionality) that you cannot get some value out of the use of Oracle Restart. My aim in this post is to show you a few basic steps to get you started with this utility.
Oracle Restart was introduced with Oracle 11gR2 and runs from the Grid Infrastructure (GI) home (software installation) when used in a non-clustered (single server) environment. You might have realized that when you install GI, configure ASM storage followed by the database software installation and database creation, that the database will automatically start at server startup. It is Oracle Restart that performs this little piece of magic around the auto starting of the database following a server restart, or in case of unexpected failure (crash).
There is a lot to Oracle Restart and there are a number of possibilities with it; but we'll only cover a few key points in this blog post, and so recommend you review the Oracle online documentation for more details, and as always TEST and TEST again before doing anything in production. The scripts and commands I use will also need to be adjusted to meet your environment particulars, but they should serve as a starting point for you to start playing with Oracle Restart. In my environment I am using the Oracle 11.2.0.3 software set (Grid Infrastructure and Database) running on Oracle Linux 5.7. ASM storage is configured and the database is called “prod” on both the primary and standby servers.
Oracle Restart takes dependencies into account and will attempt to start the components in the appropriate order. But be aware if you do not use the DBCA, for example, to create the database, and you use the manual “create database…” commands from the command line, the database will not be automatically added to Oracle Restart. You will have to manually add the database to Oracle Restart in this case. By using “DBCA” all required dependencies are put in place, such as ASM disk groups that need to be available before database start.
It is also key to understand that Oracle Restart makes use of the Oracle High Availability Services - also known as HAS - to start and stop the components, so if the HAS services are not running, Oracle Restart will not work. To check if the HAS service is running in your environment you can execute the “crsctl check has” command as the GI owner and software home. Below is a summary of the key commands for the HAS services.
Check HAS Status:
grid@dbvlin503[/home/grid]: which crsctl
/u01/app/11.2.0/grid/bin/crsctl
grid@dbvlin503[/home/grid]: crsctl check has
CRS-4638: Oracle High Availability Services is online
Review if HAS is set to auto start:
grid@dbvlin503[/home/grid]: crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
Other commands that are useful include:
crsctl enable has ← enable high availability service
crsctl disable has ← disable high availability service
crsctl start has ← start high availability service
crsctl stop has ← stop high availability service
For more details on the crsctl command see the Oracle online documentation. Also note that the Oracle Restart configuration is stored in the Oracle Local Registry (OLR). We are not going to go into detail about the OLR, but you can check the status of this by running the following command from the GI home as the root user:
root@dbvlin503[/u01/app/11.2.0/grid/bin]: ocrcheck -local
Status of Oracle Local Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2432
Available space (kbytes) : 259688
ID : 1547015995
Device/File Name : /u01/app/11.2.0/grid/cdata/localhost/dbvlin503.olr
Device/File integrity check succeeded
Local registry integrity check succeeded
Logical corruption check succeeded
Now let's have a quick look at one basic step - adding a database to Oracle Restart. The “srvctl” command line utility is used from the Oracle database software home, and for more details on the command you can use the “-h” option for help. Example:
oracle@dbvlin503[/home/oracle]: which srvctl
/u01/app/oracle/product/11.2.0/db_1/bin/srvctl
oracle@dbvlin503[/home/oracle]: srvctl add database -h
Adds a database configuration to be managed by Oracle Restart.
Usage: srvctl add database -d <db_unique_name> -o <oracle_home> [-m <domain_name>] [-p ] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s <start_options>] [-t <stop_options>] [-n <db_name>] [-i <inst_name>] [-y {AUTOMATIC | MANUAL | NORESTART}] [-a "<diskgroup_list>"]
-d <db_unique_name> Unique name for the database
-o <oracle_home> ORACLE_HOME path
-m Domain for database. Must be set if database has DB_DOMAIN set.
-p Server parameter file path
-r Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
-s <start_options> Startup options for the database. Examples of startup options are OPEN, MOUNT, or 'READ ONLY'.
-t <stop_options> Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.
-n <db_name> Database name (DB_NAME), if different from the unique name given by the -d option
-i <inst_name> Instance name
-y Management policy for the database (AUTOMATIC, MANUAL, or NORESTART)
-a "<diskgroup_list>" Comma separated list of disk groups
-h Print usage
In my environment, you will add the database “prod” to Oracle Restart using the “srvctl” command:
oracle@dbvlin503[/home/oracle]: srvctl add database -d prod -o /u01/app/oracle/product/11.2.0/db_1 -p +DATA/prod/spfileprod.ora -s open -t immediate -y AUTOMATIC -a "DATA,FRA"
In the command above we specify the Oracle Home and spfile location, as well as the start (open) and shutdown (immediate) options to be used when the database is to be started or stopped.
To check the status of the database you can use the status option in the srvctl command, as below. In our environment the database was down and this is reflected in the status.
oracle@dbvlin503[/home/oracle]: srvctl status database -d prod
Database is not running.
The next step we want to show is the configuration parameters, which can be displayed using the config option of the srvctl command.
oracle@dbvlin503[/home/oracle]: srvctl config database -d prod
Database unique name: prod
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/prod/spfileprod.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: prod
Disk Groups: DATA,FRA
Services:
Now that the database is registered we can start it with the srvctl command:
oracle@dbvlin503[/home/oracle]: srvctl start database -d prod
oracle@dbvlin503[/home/oracle]: srvctl status database -d prod
Database is running.
To show you the effect of using Oracle Restart, we are going to terminate the database in an “unexpected” way. Basically, we are going to kill the PMON background process. The effect is that Oracle Restart will automatically restart the database without us doing anything. So, an example:
oracle@dbvlin503[/home/oracle]: date; ps -ef|grep pmon
Tue Apr 30 22:35:24 EDT 2013
grid 2912 1 0 Apr22 ? 00:01:43 asm_pmon_+ASM
oracle 10690 1 0 22:32 ? 00:00:00 ora_pmon_prod
oracle 10938 9112 0 22:35 pts/0 00:00:00 grep pmon
oracle@dbvlin503[/home/oracle]: date; kill -9 10690
Tue Apr 30 22:35:47 EDT 2013
oracle@dbvlin503[/home/oracle]: ps -ef|grep pmon
grid 2912 1 0 Apr22 ? 00:01:43 asm_pmon_+ASM
oracle 10959 9112 0 22:35 pts/0 00:00:00 grep pmon
oracle@dbvlin503[/home/oracle]: date
Tue Apr 30 22:35:55 EDT 2013
oracle@dbvlin503[/home/oracle]: ps -ef|grep pmon
grid 2912 1 0 Apr22 ? 00:01:43 asm_pmon_+ASM
oracle 10964 1 0 22:35 ? 00:00:00 ora_pmon_prod
oracle 11026 9112 0 22:35 pts/0 00:00:00 grep pmon
oracle@dbvlin503[/home/oracle]: srvctl status database -d prod
Database is running.
The above is just a basic way to show how Oracle Restart will restart the database in case of unexpected behavior. Please note you can still use sqlplus to manually start/stop the database. Oracle Restart will not auto restart the database if you perform a shutdown from sqlplus. It will pick up that this is a controlled shutdown and will allow you to do so.
If your database listener is not already added to Oracle Restart, you can easily add it following similar steps using the “srvctl add listener” command. One of the advantages you have now is that during server restart the database will be started automatically.
In the next section what we would like to focus on is the standby database. Now in my test environment we are using the same setup on the standby server as the primary, which means the standby database also makes use of ASM storage, and Grid Infrastructure is already installed - so this means Oracle Restart is available to use. Now remember, we are using Standard Edition and so do not have the Data Guard option and the integration options it has with Oracle Restart.
There are a few ways of approaching this; first you can add the standby database to Oracle Restart, but the key is to make sure it starts it in a mounted state, not open. Otherwise what will happen is your standby database will be auto started to an open Read-Only state. In summary you want the physical standby to start automatically (in case of unexpected failure or server restart) into the correct state ready to perform recovery, which is a mounted state.
To perform this you can add the standby database as follows:
oracle@dbvlin504[/home/oracle]: srvctl add database -d prod -o /u01/app/oracle/product/11.2.0/db_1 -p +DATA/prod/spfileprod.ora -s mount -t immediate -y AUTOMATIC -a "DATA,FRA"
The Database is now added and configured to auto start but into a “mounted” state. To review the configuration you can use the “srvctl config” option:
oracle@dbvlin504[/home/oracle]: srvctl config database -d prod
Database unique name: prod
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/prod/spfileprod.ora
Domain:
Start options: mount
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: prod
Disk Groups: DATA,FRA
Services:
The next step is to start the standby database using the srvctl command. In our case the standby database was shutdown when we added it to Oracle Restart, so first thing to do is a status check followed by starting the database and then reviewing the database open mode and role.
oracle@dbvlin504[/home/oracle]: srvctl status database -d prod
Database is not running.
oracle@dbvlin504[/home/oracle]: srvctl start database -d prod
oracle@dbvlin504[/home/oracle]: srvctl status database -d prod
Database is running.
oracle@dbvlin504[/home/oracle]: sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 1 17:10:21 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> select name, database_role, open_mode, controlfile_type from v$database;
NAME DATABASE_ROLE OPEN_MODE CONTROL
---------- ----------------- ------------- -------
PROD PHYSICAL STANDBY MOUNTED STANDBY
We can now stop and start the standby database using the srvctl command and due to the configuration stating that this database should be started into a mount state, it will always auto start the database into a mount state.
Now remember we are using Standard Edition without Data Guard integration. When using Data Guard and the Data Guard broker, the Oracle Restart configuration will automatically be updated during Switchover or Failover. This means the database role and start mode can be specified (-r PHYSICAL_STANDBY|PRIMARY option and –s open|mount) when you add it to Oracle Restart, and if a switchover is executed Data Guard will update these values to reflect the switch. For example, when using Data Guard on the primary node you would specify:
srvctl add database –d prod …… -r PRIMARY –s open –t immediate
and on the standby
srvctl add database –d proddr …… -r PHYSICAL_STANDBY –s mount –t immediate
You then perform a switchover between the two nodes. Data Guard will update the Oracle Restart values so that the “prod” database will have its configuration changed to “-r PHYSICAL_STANDBY –s mount” and the “proddr” Oracle Restart configuration will be updated to reflect that it is now a primary – “-r PRIMARY –s open”.
But when using Standard Edition this option is not possible, so how do we achieve this? Well there are two ways we will go through:
Following a Graceful Switchover (role switch) or Activation (failover) you have to run a manual update command on each node to update its startup value from “mount” to “open” on the original standby – and on the original primary to reflect that it is now a standby you have to update the startup value from “open” to “mount”. For example:
oracle@dbvlin504[/home/oracle]: srvctl modify database -d prod -s open
oracle@dbvlin504[/home/oracle]: srvctl config database -d prod
Database unique name: prod
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/prod/spfileprod.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: prod
Disk Groups: DATA,FRA
Services:
This is a bit of a manual process, but it can easily be scripted, and then executed at the end of a role switch or activation.
The second option is a bit more complex, however, it is possible to automate the correct startup using a local custom resource, which can be added to Oracle Restart. Please note that this option is a lot more complex and it is recommended that you have a good understanding of adding resources, dependencies and action scripts. There is, as in most cases more than one way to do it and maybe the commands and scripts provided below can help you get started or you might just want to keep it simple and use the option 1 mentioned above. These scripts and commands are for reference only and you will need to TEST and adjust for your own environment.
The process can be summarized as follows:
Each database (primary and standby) is always started in a mounted state by Oracle Restart.
A resource - “action” script is executed that will interrogate the database to review the controlfile type and database open mode. If the database is using a primary controlfile, the script executes the command to open the database read/write. If the script finds that the database makes use of a standby controlfile, the script will exit without doing anything, as the database would be in a mounted state, which is what we want for the standby. This way we can control the correct database startup mode while still using Oracle Restart with a standard edition standby environment.
The first step is creating a resource action script. Now for more details on how this works, and examples, please review the Oracle Clusterware Administration and Deployment Guide with specific reference to these two sections:
• http://docs.oracle.com/cd/E11882_01/rac.112/e16794/crschp.htm • http://docs.oracle.com/cd/E11882_01/rac.112/e16794/resatt.htm (look at end for examples) Below is an example script:
#!/bin/bash
#
# Notes: For more examples on action scripts see Oracle Clusterware Resource Reference
# in the Oracle 11r2 Clusterware Administration and Deployment Guide
# This example script is for testing purpose only.
# set -x
###########################################
# Function to echo text to the scriptagaent log file
# - Everything that is echoed out by the action script to STDOUT
# will be automatically added to the this log file.
# - example log location:
# $GRID_HOME/log/hostname/agent/ohasd/scriptagent_grid/scriptagent_grid.log
###########################################
addLog()
{
echo "${1}"
}
###########################################
# Function to change database environments
# - Set the environment for the database
# - Make sure database is in /etc/oratab
# - oraenv is picked up from /usr/local/bin
###########################################
set_env ()
{
export ORAENV_ASK=NO
export ORACLE_SID=$1
. oraenv >> /dev/null
}
###########################################
# Function to setup parameter variables
# - Update db variable to the required database
# - Ensure db is specified in /etc/oratab
###########################################
setup_parameters ()
{
PATH=/usr/local/bin:$PATH
program=`basename $0`
db="prod"
set_env ${db}
v_database="v\$database"
debug=0 ## Possible values 0 or 1
## if 1 is set resource environment Attribute values will be displayed
}
###########################################
# Function to show resource attributes
# which is available as _CRS_ environment variables
# during action script execution
###########################################
show_env ()
{
for v_attr in `env |grep ^_CRS` ; do
echo "$v_attr"
done
}
###########################################
# Function check_db
###########################################
check_db ()
{
# Run basic check to see if pmon process is running
PROC=`ps -ef | grep ora_pmon_${1} | grep -v grep | cut -c48-`
if [ "X${PROC}" = "X" ]; then
addLog "Database not running... should be at least mounted"
exit 1
else
db_status=`sqlplus -s "/ as sysdba" << !EOF
set feedback off
set echo off
set heading off
set pages 0
select open_mode||':'||controlfile_type from ${v_database};
exit;
!EOF
`
fi
}
###########################################
# Function open_db
###########################################
open_db ()
{
addLog "Database ${db} will be opened"
v_open=`sqlplus -s "/ as sysdba" << !EOF
set feedback off
set echo off
set heading off
set pages 0
alter database open;
select open_mode||':'||controlfile_type from ${v_database};
exit;
!EOF
`
if [ "${v_open}" = "READ WRITE:CURRENT" ];
then
addLog "Database ${db} opened successfuly"
else
addLog "Database ${db} open failed ${v_open}"
exit 1
fi
}
##########################
##########################
####
#### MAIN PROGRAM SECTION
####
##########################
##########################
setup_parameters
if test $# -ne 1
then
echo "Usage: ${program} {start|stop|check|clean}"
exit 1
fi
if test ${debug} -eq 1
then
show_env
fi
case ${1} in
'start')
addLog "${program} start entry point called"
check_db
if [ "${db_status}" = 'MOUNTED:CURRENT' ];
then
open_db
else
addLog "Nothing to do"
fi
exit 0
;;
'stop')
addLog "${program} stop entry point called, nothing to do"
exit 0
;;
'check')
addLog "${program} check entry point called"
check_db
if [ "${db_status}" = 'MOUNTED:STANDBY' ] || [ "${db_status}" = 'READ WRITE:CURRENT' ];
then
addLog "OK - Database state ${db_status}"
exit 0
else
addLog "Error - Database in wrong state ${db_status}"
addLog "- If Primary it should be 'READ WRITE:CURRENT' "
addLog "- If Standby it should be 'MOUNTED:STANDBY' "
exit 1
fi
;;
'clean')
addLog "${program} clean entry point called, nothing to do"
exit 0
;;
*) echo "Usage: ${program} {start|stop|check|clean}"
exit 0
;;
esac
In my test case I saved the script in =/u01/app/grid/admin/action_script/startdb and made sure it has user (Grid Infrastructure owner) execute permissions (chmod u+x startdb).
The next step then is to create the resource. This is done as the Grid Infrastructure owner by executing the following command:
crsctl add resource custom.restartdb -type local_resource -attr "ACTION_SCRIPT=/u01/app/grid/admin/action_script/startdb,SCRIPT_TIMEOUT=30,CHECK_INTERVAL=60,RESTART_ATTEMPTS=3,START_DEPENDENCIES=hard(intermediate:ora.prod.db),DESCRIPTION=’Open Database if Primary, leave mounted if standby’"
Using the crsctl add resource command I am adding a custom resource called “custom.restardb” which will be executing the action script created. A dependency is set on the prod database (resource name “ora.prod.db”) so this resource (custom.restartdb) will only start if the database prod resource is either online or in an intermediate state (resource is partially online) - such as a database that is in a mounted state.
Example output in the log file on the primary node:
grid@dbvlin504[/home/grid]: tail -10 /u01/app/11.2.0/grid/log/dbvlin504/agent/ohasd/scriptagent_grid/scriptagent_grid.log
2013-05-02 21:54:09.932: [custom.restartdb][1106786624] {0:0:1015} [check] startdb check entry point called
2013-05-02 21:54:10.032: [custom.restartdb][1106786624] {0:0:1015} [check] OK - Database state READ WRITE:CURRENT
2013-05-02 21:54:30.698: [ AGFW][1083787584] {0:0:1015} Agent received the message: AGENT_HB[Engine] ID 12293:46978
2013-05-02 21:55:30.699: [ AGFW][1083787584] {0:0:1015} Agent received the message: AGENT_HB[Engine] ID 12293:46982
2013-05-02 21:56:00.710: [ AGFW][1083787584] {0:0:1015} Agent received the message: AGENT_HB[Engine] ID 12293:46986
2013-05-02 21:56:09.851: [ AGFW][1106786624] {0:0:1015} Entering script entry point...
2013-05-02 21:56:09.851: [custom.restartdb][1106786624] {0:0:1015} [check] Executing action script: /u01/app/grid/admin/action_script/startdb[check]
2013-05-02 21:56:09.956: [custom.restartdb][1106786624] {0:0:1015} [check] startdb check entry point called
2013-05-02 21:56:10.056: [custom.restartdb][1106786624] {0:0:1015} [check] OK - Database state READ WRITE:CURRENT
2013-05-02 21:56:30.711: [ AGFW][1083787584] {0:0:1015} Agent received the message: AGENT_HB[Engine] ID 12293:46988
Example output in the log file on the standby node:
grid@dbvlin503[/home/grid]: tail -10 /u01/app/11.2.0/grid/log/dbvlin503/agent/ohasd/scriptagent_grid/scriptagent_grid.log
2013-05-02 22:00:34.291: [custom.restartdb][1087809856] {0:0:966} [check] OK - Database state MOUNTED:STANDBY
2013-05-02 22:01:03.826: [ AGFW][1099151680] {0:0:966} Agent received the message: AGENT_HB[Engine] ID 12293:50369
2013-05-02 22:02:03.827: [ AGFW][1099151680] {0:0:966} Agent received the message: AGENT_HB[Engine] ID 12293:50375
2013-05-02 22:02:33.838: [ AGFW][1099151680] {0:0:966} Agent received the message: AGENT_HB[Engine] ID 12293:50379
2013-05-02 22:02:34.109: [ AGFW][1087809856] {0:0:966} Entering script entry point...
2013-05-02 22:02:34.109: [custom.restartdb][1087809856] {0:0:966} [check] Executing action script: /u01/app/grid/admin/action_script/startdb[check]
2013-05-02 22:02:34.214: [custom.restartdb][1087809856] {0:0:966} [check] startdb check entry point called
2013-05-02 22:02:34.314: [custom.restartdb][1087809856] {0:0:966} [check] OK - Database state MOUNTED:STANDBY
The action script will review the database status when executing, if the database is a primary database with a primary (current) controlfile, the action script will open the database. If it is a standby database (standby controlfile) it will leave the database in a mounted state.
Using the above example, if the server is unexpectedly restarted, the databases will be started in the correct state. Again please note that this is just an example to show some of the possibilities.
In summary, Oracle Restart is a powerful option you can use, and in most cases (99% of the time) our recommendation is to KEEP IT SIMPLE - and maybe just add an additional step at the end of your Graceful Switchover or Activation to manually update the startup parameter for the database (Option 1 above) in Oracle Restart (srvctl modify database –d <your_database> -s open|mount). But if you feel more adventurous and don't mind a little complexity, hopefully, the section above with the action script will give you an idea of where to start. For more examples please see the Oracle online documentation.