Oracle Restart

What is Oracle Restart:

Applies to: Database Management Software Oracle 11g R2 Grid Infrastructure

Oracle Restart is a high availability s/w designed for Single Instance (non Real Application Cluster) databases. For Real Application Cluster env. this facility is provided by Oracle Clusterware.
To implement this in database Oracle Grid s/w is mandatory to install. Oracle Restart will run from Grid S/W home Only.

 What it does:

Oracle Restart monitor health and automatically restart following components in case of failure:

a. Database Instance
b. Oracle net listener
c. Database Services
d. ASM Instance
e. ASM Disk group
f. Oracle Notification Services

How it works:

Oracle Restart installation modifies '/etc/inittab' file to make sure start up every time machine goes up. Below entries are made into 'inittab' file.

[oracle@database bin]$ cat /etc/inittab
h1:35:respawn:/etc/init.d/init.ohasd run >/dev/null 2>&1

init.ohsd is a wrapper of process which actually perform Restart feature. Direct execution of wrapper script is not supported.

Commands to operate:

crsctl is main command to work with Oracle Restart feature

1. Check Oracle Restart Configuration: Make sure command is executed from grid home

[oracle@database bin]$ export ORACLE_HOME=/etc/oracle/grid
[oracle@database bin]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@database bin]$ crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.

2. Enable/Disable Oracle Restart:

[oracle@database bin]$ crsctl enable  has
CRS-4622: Oracle High Availability Services autostart is enabled.

[oracle@database bin]$ crsctl disable  has
CRS-4621: Oracle High Availability Services autostart is disabled.

3. Start/Stop Oracle Restart:

[oracle@database bin]$ crsctl start  has
CRS-4123: Oracle High Availability Services has been started.

[oracle@database bin]$ crsctl stop  has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'database'
CRS-2673: Attempting to stop 'ora.asm' on 'database'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'database'
CRS-2673: Attempting to stop 'ora.evmd' on 'database'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'database' succeeded
CRS-2677: Stop of 'ora.asm' on 'database' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'database'
CRS-2677: Stop of 'ora.evmd' on 'database' succeeded
CRS-2677: Stop of 'ora.cssd' on 'database' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'database'
CRS-2677: Stop of 'ora.diskmon' on 'database' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'database' has completed
CRS-4133: Oracle High Availability Services has been stopped.

At time of start up command will show only one output, whereas at stoping this will show list of components.

Test Scenario: A database with Automatic Storage Management up and running. I am going to kill it's pmon and let's see if it comes up automatically.


[oracle@database ~]$ ps -ef | grep smon
oracle    8476     1  0 22:03 ?        00:00:00 asm_smon_+ASM
oracle    8657     1  0 22:05 ?        00:00:00 ora_smon_orcl
oracle    8827  8528  0 22:07 pts/1    00:00:00 grep smon

[oracle@database ~]$ sqlplus sys as sysdba

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> !

[oracle@database ~]$ kill -9 8657

I have killed pmon, this is reflected in alert log file of database in red color (see at bottom of post)

[oracle@database ~]$ ps -ef | grep smon

oracle    8476     1  0 22:03 ?        00:00:00 asm_smon_+ASM
oracle    8970     1  0 22:08 ?        00:00:00 ora_smon_orcl
oracle    9021  8838  0 22:08 pts/1    00:00:00 grep smon

[oracle@database ~]$ sqlplus sys as sysdba

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Now database is up and running without and manual intervention, which proves Oracle Restart feature.

Below is the screen shot of alert log file:

Thu Jan 24 22:06:24 2013
Starting background process SMCO
Thu Jan 24 22:06:24 2013
SMCO started with pid=39, OS id=8798
Thu Jan 24 22:06:35 2013
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Thu Jan 24 22:08:28 2013
PMON (ospid: 8628): terminating the instance due to error 474
Thu Jan 24 22:08:28 2013
ORA-1092 : opitsk aborting process
Thu Jan 24 22:08:28 2013
License high water mark = 12
Instance terminated by PMON, pid = 8628
USER (ospid: 8871): terminating the instance
Instance terminated by USER, pid = 8871
Thu Jan 24 22:08:29 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
CELL communication is configured to use the following interface(s) for this instance
    192.168.56.102
CELL interconnect IPC version: Oracle UDP/IP (generic)
IPC Vendor 1 Protocol 2
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /etc/oracle/oracle/dbs/spfileorcl.ora
System parameters with non-default values:
  processes                = 150
  memory_target            = 584M
  control_files            = "/etc/oracle/oradata/orcl/control01.ctl"
  control_files            = "/etc/oracle/fast_recovery_area/orcl/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_recovery_file_dest    = "/etc/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4002M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = "example.com"
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
  job_queue_processes      = 10
  audit_file_dest          = "/etc/oracle/admin/orcl/adump"
  audit_trail              = "DB"
  cell_offload_plan_display= "ALWAYS"
  db_name                  = "orcl"
  open_cursors             = 300
  diagnostic_dest          = "/etc/oracle"
Thu Jan 24 22:08:30 2013
-----------------------------------
-----------------------------------
-----------------------------------
-----------------------------------
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Jan 24 22:08:30 2013
MMNL started with pid=17, OS id=8976
starting up 1 shared server(s) ...
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /etc/oracle
Thu Jan 24 22:08:30 2013
ALTER DATABASE MOUNT /* db agent *//* {0:4:17} */
Successful mount of redo thread 1, with mount id 1333375326
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT /* db agent *//* {0:4:17} */
ALTER DATABASE OPEN /* db agent *//* {0:4:17} */
--------------------------------------------
--------------------------------------------
--------------------------------------------

Completed: ALTER DATABASE OPEN /* db agent *//* {0:4:17} */


Note: Oracle Restart works only in abnormal termination of components.

No comments:

Post a Comment