Protecting Oracle Instance with Local Clustering - Page 3
March 13, 2003Marin Komadina
Procedure for 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.
/LOGNODEH /oracle/app/oracle/admin/artist000 /oracle/app/oracle/admin/artist000/arch /oracle/dataspace/artist000 /LOGNODEI /oracle/app/oracle/admin/artist001 /oracle/app/oracle/admin/artist001/arch /oracle/dataspace/artist001
The new logical host name is LOGNODEI for instance artist001. The database is up and running with an activated listener.
Oracle configuration files update
Make new SID entries in the Oracle HA Agent configuration files for the new instance.
LISTENER_artist001 = ( ADDRESS_LIST = ( ADDRESS = ( PROTOCOL = TCP ) ( Host = LOGNODEI)(Port= 1534)) ( ADDRESS = ( PROTOCOL = IPC ) ( KEY = artist001 ) )) SID_LIST_LISTENER_artist001 = (SID_LIST=(SID_DESC= (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 monitoring
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; User created. SQL> GRANT CONNECT, REOURCE TO HA; Grant succeed. SQL> ALTER USER HA DEFAULT ROLE CONNECT,RESOURCE; User altered. SQL> GRANT CREATE SESSION TO HA; Grant succeed. SQL> GRANT CREATE TABLE TO HA; Grant succeed. SQL> GRANT UNLIMITED TABLESPACE TO HA; Grant succeed. SQL> GRANT SELECT ON SYS.V_$SYSSTAT TO HA; Grant succeed.
Oracle HA agent reconfiguration
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
The 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/ oracle/admin/artist001/pfile/initartist001.ora LISTENER_artist001
Parameters for haoracle command:
artist001 -instance name
LOGNODEI - Logical host for instance artist001
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
/oracle/app/oracle/admin/artist001/pfile/initartist001.ora 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.
#haoracle list off:artist001:LOGNODEI:60:10:120:300:ha/test:/oracle/app/oracle/ admin/artist001/pfile/initartist001.ora:LISTENER_artist001 on:artist000:LOGNODEH:60:10:120:300:ha/test:/oracle/app/oracle/ admin/artist000/pfile/initartist000t.ora:LISTENER_artist000t
Starting cluster monitoring for new instance:
#haoracle start artist001 #haoracle list on:artist001:LOGNODEI:60:10:120:300:ha/test:/oracle/app/ oracle/admin/artist001/pfile/initartist001.ora:LISTENER_artist001 on:artist000:LOGNODEH:60:10:120:300:ha/test:/oracle/app/ oracle/admin/artist000/pfile/initartist000.ora:LISTENER_artist000
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 deporting aartist_stl_dg Give up of logical host LOGNODEI succeeded
Taking over logical host LOGNODEI importing artist_stl_dg 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 configuration file.
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:
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.