Protecting Oracle Instance with Local Clustering - Page 3

March 13, 2003

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.

oratab

artist001:/oracle/app/oracle/product/8.1.7:N

Listener.ora

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)))

Tnsnames.ora

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.

Host CNODEA

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

Host CNODEB

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.

Conclusion

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