Unwanted Service in HANA Studio for SYSTEMDB
Have got a situation where the index server is shown in HANA studio under HANA SYSTEMDB . Though upon checking the services from OS level, you dint see any such services.
But your database in HANA studio, always shown as stopped database. It is good to get rid of such services which can cause the false alerts, or mislead the technical team. It might happen for tenant DB also.
This blog will help to understand why such incident happen and how to rectify them.
Context: When we reset credentials in SAP HANA Database without following recommended procedure it will add another index server and created rogue service. Below are the steps need to follow to reset credentials:
Usually we do not follow step 1 to 3 and directly reset credentials which results in creation of rogue service. Below are the commands to identify creation of rogue service in HANA System Database.
Execute “select * from m_volumes” to check available volumes
Under SystemDB, execute “select * from M_TOPOLOGY_TREE where path like ‘/host/<Host name>/indexserver’ ” to check the available Indexservers including rogue ones which you can see under SystemDB.
Every index service volume is assigned and can be identified with command “Select * from M_TOPOLOGY_TREE where Path LIKE ‘/volumes”
Under Name attribute above, numbering like 4:2, 1, 5:2 and more should be matched with available volumes. No sign of -1:2 availability means rogue volume.
So in such situation if more than one service of a particular type is running on a host, you can remove the superfluous service(s)/rogue service to streamline the use of database resources.
- You are logged on to the system database and having system privilege DATABASE ADMIN.
- The database is online.
- The service to be removed is running.
- Automatic log backup must be enabled.
Rogue Service Removal
With the help of steps mentioned above you are able to identify the rogue service running on HANA Database. e.g In our case its with volume -1:2. Us below query to remove rogue service from System Database.
ALTER SYSTEM ALTER CONFIGURATION (‘topology.ini’, ‘system’) UNSET (‘/host/<Host Name>/indexserver’,'<PORT>’) WITH RECONFIGURE
After executing above query refresh page and check rogue service is deleted.
Execute query to confirm rogue service removal.
Remove volume id with
ALTER SYSTEM ALTER CONFIGURATION (‘topology.ini’, ‘system’) UNSET (‘/volumes’, ‘<Volume Name>’) WITH RECONFIGURE;
Execute “”Select * from M_TOPOLOGY_TREE where Path LIKE ‘/volumes”” again to ensure removal is done.
After removal of rogue service from System Database make sure database backup is taken successfully.