Adding New Instance in Cluster
Since we have sufficient memory and CPU
resources free, we can add a new instance. The new project has a special
request for database version 8.1.7. Oracle software has been installed locally
on every physical host in the new ORACLE_HOME directory. New disk space has
been added to the system and new mounting points created.
The new logical host name is LOGNODEI for
instance artist001. The database is up and running with an activated listener.
new SID entries in the Oracle
HA Agent configuration files for the new instance.
( ADDRESS_LIST =
( ADDRESS = ( PROTOCOL = TCP ) ( Host = LOGNODEI)(Port= 1534))
( ADDRESS = ( PROTOCOL = IPC ) ( KEY = artist001 ) ))
(ORACLE_HOME = /oracle/app/oracle/product/8.1.6)(SID_NAME = artist001)))
artist001 =(DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = LOGNODEI)(PORT = 1534))
(CONNECT_DATA = (SID = artist001)))
Database user for fault
The Oracle HA Agent uses a special oracle
account to check database availability. The special user account in the Oracle
database has to be created before starting fault monitoring.
SQL> CREATE USER HA IDENTIFIED BY test DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE SYSTEM QUOTA 1M ON SYSTEM PROFILE "DEFAULT" ACCOUNT UNLOCK;
SQL> GRANT CONNECT, REOURCE TO HA;
SQL> ALTER USER HA DEFAULT ROLE CONNECT,RESOURCE;
SQL> GRANT CREATE SESSION TO HA;
SQL> GRANT CREATE TABLE TO HA;
SQL> GRANT UNLIMITED TABLESPACE TO HA;
SQL> GRANT SELECT ON SYS.V_$SYSSTAT TO HA;
Oracle HA agent
To protect the new instance with a cluster
we need to make an instance registration and activation against the cluster
software. Registration and activation must be taken from the mastered cluster
node, (the node from which we have started the cluster service).
As a Unix super account (root) user, run
the hareg command:
#hareg -s -r oracle -h LOGNODEI
#hareg -y oracle
new instance has to be added to the Oracle HA agent configuration data:
#haoracle insert artist001 LOGNODEI 60 10 120 300 ha/test /oracle/app/
for haoracle command:
artist001 -instance name
LOGNODEI - Logical host for instance
60 10 120 300 - connectivity probe paramaters (cycle time - 60 seconds,
cycle count - 10,probe time out - 120 seconds, restart delay - 300 seconds)
ha/test - fault monitoring database account
database initialization file
LISTENER_artist001 - oracle listener for new instance
Database instance artist001 is
ready for active monitoring with the Oracle HA agent. It is included in the configuration
files but not started.
Starting cluster monitoring for new
#haoracle start artist001
In moment of starting, cluster agent is
going to check the status of the database and listener process. If they are not
running cluster agent will start them.
Testing instance failover
cnodea@root#scadmin switch FCLUST00C2 CNODEB LOGNODEI
clustname dest-host logical-hosts ...
After we run a command "scadmin switch" we will start the
cluster failover process. The process is now transparent and fully automatic.
We can manually switch all, or only one database to another host. Additionally,
in the cluster log file we can monitor the database status and actions taken
from the Oracle HA Agent.
Giving up logical host LOGNODEI
fm_stop method of data service oracle completed successfully.
LOGNODEI:artist001: starting shutdown immediate for oracle instance artist001
LOGNODEI:artist001: Shutdown immediate for oracle instance artist001 completed
stop_net method of data service oracle completed successfully.
stop method of data service oracle completed successfully.
umount of /oracle/oradata1/artist001 succeeded
umount of /oracle/app/oracle/admin/artist001/arch succeeded
umount of /oracle/app/oracle/admin/artist001 succeeded
umount of /LOGNODEI succeeded
Give up of logical host LOGNODEI succeeded
Taking over logical host LOGNODEI
start method of data service oracle completed successfully.
LOGNODEI:artist001: starting up Oracle Listener
start_net method of data service oracle completed successfully.
LOGNODEI:artist001: Starting up instance artist001, PFILE=initartist001.ora
LOGNODEI:artist001: Startup for instance artist001 succeed.
fm_init method of data service oracle completed successfully.
Take over of logical host LOGNODEI succeeded
Please acknowledge that Oracle HA agent will make shutdown
immediate. This behavior is controlled by /etc/opt/SUNWscor/haoracle_config_V1
Second way to initiate instance failover is to use the haswitch command.
Checking Oracle connections in database artist001:
SQL> SELECT S.SID,S.STATUS,P.SPID,S.SERIAL#,S.USERNAME,S.OSUSER,S.TERMINAL
FROM V$SESSION S, V$PROCESS P WHERE S.paddr = P.addr and S.USERNAME='SYSHA' ORDER BY 1,2
SID STATUS SPID SERIAL# USERNAME OSUSER TERMINAL
----- -------- ----- -------- ---------- --------- ----------
63 INACTIVE 344 9148 HA root console
82 INACTIVE 29484 24809 daemon console
The database has two connections, local
daemon coming from HA Oracle Agent and remote connection coming from other
cluster node as user HA. These two connections exist all the time while the
Oracle HA Agent is running against the selected database.
Clustering, for a long time, has been a good solution for local
protection of our sensible applications. On the other hand it is very
expensive and not technically perfect:
any physical (block) corruption will crash our application
any logical (dropping of a database object) corruption
will crash our application
client-server connections will not survive cluster failover
uncontrolled instance failover caused by network timeouts or low
system resources during working time
unpleasant "shutdown abort"
People understand the problem and try to exploit existing
configurations to the maximum. Often, they run separate databases on both nodes
to obtain the maximum use from hardware or use a second node for offloading the
primary node from non-critical processing.
Trying to provide better and more reliable service for customers while
cutting hardware costs at the same time, may push companies in a new direction--Real
Application Cluster. Real Application cluster provides more availability and
performance for less money.
See All Articles by Columnist Marin Komadina