How can we check where HANA system is single DB or multitenant database containers (MDC)?

As we know that HANA MDC feature introduced in HANA version 1.0 SPS 09 and Starting from HANA 2.0 SPS01, Multitenant Database Containers (MDC) became the standard. Please refer below snap 

When we are working HANA DB version 1.0 SPS 09 to 1.0 SPS 12, if some once ask the question that HANA DB which we are using is it MDC or Single DB?

We can check the same by running the simple command as shown below. 

select * from "PUBLIC"."M_DATABASES"

if the database is single DB then you will get below details

If the database is installed at MDC then you will get below details

Note:

We can see in an above snap how we can find single DB and MDC 

Sometimes it's possible that you may not receive any output from the above select query. That time you can find details by running below given SQL statements 

1: HANA_Configuration_Parameters -- SQL Query 

MDCSYS -- it means statement executed at SYSTEM database 

MDCTEN -- It means statement executed at Tenant database 

2: HANA_Configuration_Overview  --- SQL Query

We can see that its MDC ( tenant DB / multitenant system) with help both 1 and 2 queries.

Note: select query as per HANA DB version and you can down SQL statement from below link

Note 1969700 - SQL statement collection for SAP HANA

how can we reset the SYSTEM User Password of the System Database

In this blog we will see how can we reset the SYSTEM User Password of the System Database

Note: The system database and all tenant databases each have their own SYSTEM user. The system administrator can reset the password of any SYSTEM user if it has been irretrievably lost.

Prerequisites

1: You have the credentials of the operating system administrator (<sid>adm).

2: as we lost password we can't reset from HANA Studio with command. if you are able to login in HANA Studio then change password in graphical interface from security tab or execute the ALTER USER SQL statement directly in SYSTEM Database SQL

 ALTER USER SYSTEM PASSWORD <new_password>.

Procedure:

A: Log on to the server on which the name server of the system database is running as the operating system user (that is, <sid>adm user).

localhost:~ # su - hdbadm
hdbadm@localhost:/usr/sap/HDB/HDB00>

B: Shut down the instance by executing the following command:

/usr/sap/HDB/HDB<instance>/exe/sapcontrol -nr <instance> -function StopSystem HDB

or 

HDB stop

Example:  

hdbadm@localhost:/usr/sap/HDB/HDB00/exe> /usr/sap/HDB/HDB00/exe/sapcontrol -nr 00 -function StopSystem HDB

20.05.2020 07:21:15
StopSystem
OK

hdbadm@localhost:/usr/sap/HDB/HDB00/exe>

C: In a new session, start the name server of the system database by executing the following commands:

    a:   /usr/sap/<SID>/HDB<instance>/hdbenv.sh

hdbadm@localhost:/usr/sap/HDB/HDB00> ./hdbenv.sh     --> you will not get any output for this command

   b:  /usr/sap/<SID>/HDB<instance>/exe/hdbnameserver -resetUserSystem

hdbadm@localhost:/usr/sap/HDB/HDB00> /usr/sap/HDB/HDB00/exe/hdbnameserver -resetUserSystem

Starting interactive mode for resetting user SYSTEM...

service startup...

accepting requests at 127.0.0.1:30001

searching for master nameserver s4hana:30001 ...

assign as master nameserver. assign to volume 1 started

service startup...

Checking for recovery request ...

Loading topology ...

Opening persistence ...

run as transaction master

Loading licensing ...

Loading topology ...

setStarting(nameserver@s4hana:30001:1)

setActive(nameserver@s4hana:30001)

service assigned as master

service start as systemserver

setInactive(preprocessor@s4hana:30002)

setInactive(webdispatcher@s4hana:30006)

setInactive(compileserver@s4hana:30010)

setInactive(xsengine@s4hana:30007)

setInactive(indexserver@s4hana:30003)

resetting of user SYSTEM - new password:   

Welcome777      You must enter a password that complies with the password policy configured for the system.

new pw accepted.

(Re)Activating user SYSTEM...

done

prepare for shutting service down...

Stopping Linked Database Cleaner Job ...

setStopping(nameserver@s4hana:30001)

stop LicenseMeasurementTimerThread...

stop MinuteCron thread...

stop LoadDataWriter thread...

stop HighResolutionServiceLoadMonitor thread...

stop ClockMonitor thread...

stop LocalWatchDog thread...

stop MasterTokenLockWriter /usr/sap/HDB/SYS/global//hdb/nameserver.lck thread...

stop MasterTokenLockWriter /hana/shared/HDB/global/hdb/data//mnt00001/nameserver.lck thread...

stop FailoverManager thread...

stop Topology Merge thread...

setInactive(nameserver@s4hana:30001)

stop TopologyReplicatorManager thread...

stop HALogWriter thread...

saving topology...

Waiting for TopologyReplicatorManager thread...

Waiting for MinuteCron thread...

Waiting for HighResolutionServiceLoadMonitor thread...

Waiting for LoadDataWriter thread...

Waiting for LocalWatchdog thread...

Waiting for MasterTokenLockWriter /usr/sap/HDB/SYS/global//hdb/nameserver.lck thread...

Waiting for MasterTokenLockWriter /hana/shared/HDB/global/hdb/data//mnt00001/nameserver.lck thread...

Waiting for FailoverManager thread...

Waiting for ClockMonitor thread...

cleanup LicenseManager ...Waiting for Topology Merge thread...

Waiting for persistence ...

Disabling signal handler...

Stopping self watchdog...

Stopping request dispatcher...

Shutting service down...

setInactive(nameserver@s4hana:30001)

Stopping threads...

Stopping responder...

Stopping communication...

Deleting self watchdog...

Deleting request dispatcher...

Deleting responder...

Deleting service...

Deleting threads...

Deleting pools...

Deleting configuration...

Removing pidfile...

shutdown is completed.

hdbadm@localhost:/usr/sap/HDB/HDB00>

D: In a new session, start the instance by executing the following command

hdbadm@localhost:/usr/sap/HDB/HDB00/exe> /usr/sap/HDB/HDB00/exe/sapcontrol -nr 00 -function StartSystem HDB

20.05.XXXX 07:36:38
StartSystem
OK
hdbadm@localhost:/usr/sap/HDB/HDB00/exe>

Note: 

The password of the SYSTEM user of the system database is reset. You have to change the new password the next time you log on with this user. 

If you previously deactivated the SYSTEM user, it is now also reactivated. This means you will need to deactivate it again.

Note: Friends i am trying to share my knowledge and learning from experience. please help to correct if i am wrong. 

Please refer SAP HANA Administration Guide

Import of delivery units failed cannot check already imported delivery units

Some time we may get error like "Import of delivery units failed cannot check already imported delivery units" while doing HANA DB upgrade. as shown in below snap this error may come. 

ERROR: 

Cannot check already imported delivery units

 Connect failed: Connect failed (code= 10) : authentication failed

Note: before starting troubleshooting, i will request to read error care fully. because resolution is there in error. as we noted "authentication failed". Now we need to find which authentication failed

Resolution: 

we will discuss this issue step by step.

A: as error occurred, before delivery unit import what was step ? 

 --> before delivery unit import there was step of HANA DB upgrade and then start HANA database instances or instance. so we can see its showing in log all service/ server are started successfully as per above screen. 

   it mean that our upgrade is completed successfully. 

B: to check where upgrade done or not? open new putty session and login <SID>adm user then run 2 commands as shown below

1: check HANA DB version details. it will help you get current version details. 

hdbadm@localhost:/usr/sap/HDB/HDB00> HDB version

HDB version info:

  version:             2.00.020.00.1500920972

  branch:              fa/hana2sp02

  git hash:            7f63b0aa11dca2ea54d450aa302319302c2eeaca

  git merge time:      2017-07-24 20:29:32

  weekstone:           0000.00.0

  compile date:        2017-07-24 20:35:12

  compile host:        ld4551

  compile type:        rel

2: Check HANA database server process details by running SAPCONTROL commands as shown below.

hdbadm@s4hana:/usr/sap/HDB/HDB00> sapcontrol -nr 00 -function GetProcessList

20.05.2020 05:50:59

GetProcessList

OK

name, description, dispstatus, textstatus, starttime, elapsedtime, pid

hdbdaemon, HDB Daemon, GREEN, Running, 2020 05 20 05:32:37, 0:18:22, 2835

hdbcompileserver, HDB Compileserver, GREEN, Running, 2020 05 20 05:34:27, 0:16:32, 3001

hdbindexserver, HDB Indexserver-HDB, GREEN, Running, 2020 05 20 05:34:32, 0:16:27, 3044

hdbnameserver, HDB Nameserver, GREEN, Running, 2020 05 20 05:32:39, 0:18:20, 2851

hdbpreprocessor, HDB Preprocessor, GREEN, Running, 2020 05 20 05:34:27, 0:16:32, 3003

hdbwebdispatcher, HDB Web Dispatcher, GREEN, Running, 2020 05 20 05:36:19, 0:14:40, 3280

hdbxsengine, HDB XSEngine-HDB, GREEN, Running, 2020 05 20 05:34:32, 0:16:27, 3046

C: Now we know that from above points that HANA database upgrade. Now we need to focus on error 

and will refer below given SAP note.

Import of delivery units failed cannot check already imported delivery units

1795885 - HANA Upgrade: problems with "Importing delivery units"

hdbadm@localhost:/usr/sap/HDB/home> cd /usr/sap/HDB/SYS/global/hdb/install/bin

hdbadm@localhost:/usr/sap/HDB/SYS/global/hdb/install/bin> pwd

/usr/sap/HDB/SYS/global/hdb/install/bin

hdbadm@:/usr/sap/HDB/SYS/global/hdb/install/bin> ls -althr

total 228K

-r-xr-xr-x 1 root   root    15K Jul  7  2017 hdbremovehost

-r-xr-xr-x 1 root   root    15K Jul  7  2017 hdbreg

-r-xr-xr-x 1 root   root    15K Jul  7  2017 hdbinst

-r-xr-xr-x 1 root   root    15K Jul  7  2017 hdbcheck

-r-xr-xr-x 1 root   root    15K Jul  7  2017 hdbaddhost

-r-xr-xr- 1 root   root    15K Jul  7  2017 hdbupdrep

-r-xr-xr-x 1 root   root    15K Jul  7  2017 hdbupdconf

-r-xr-xr-x 1 root   root    15K Jul  7  2017 hdbuninst

-r-xr-xr-x 1 root   root    15K Jul  7  2017 hdbrename

-r-xr-xr-x 1 root   root    15K Jul  7  2017 hdbmodify

-r-xr-xr-x 1 root   root    15K Jul  7  2017 hdbhostctrl

-rw-r--r-- 1 root   root    940 Jul 24  2017 filelist.install

-rw-r--r-- 1 root   root    36K Jul 25  2017 SIGNATURE.SMF

drwxr-x--- 7 hdbadm sapsys 4.0K May  6 07:09 ..

drwxr-xr-x 7 root   root   4.0K May  6 07:09 instruntime

drwxr-xr-x 3 root   root   4.0K May  6 07:09 .

Now we can understand why authentication failed while checking already imported delivery units. because hdbupdrep  file owner and group by root user. and upgrade where running with <SID>adm user. in may case permission is 555. we will try delivery Unit import with SIDadm user. 

-r-xr-xr-x 1 root   root    15K Jul  7  2017 hdbupdrep  

D: In case you are installing or upgrading to a revision earlier than the above mentioned ones, run /usr/sap/<SID>/SYS/global/hdb/install/bin/hdbupdrep to update the delivery units.

If the repeated import also returns with errors, further steps must be taken, depending on the nature of the errors.

Please find process below to update Delivery Units.

hdbadm@localhost:/usr/sap/HDB/SYS/global/hdb/install/bin> ./hdbupdrep

SAP HANA Lifecycle Management - Update Repository 2.2.18

********************************************************

Enter System Database User Name [SYSTEM]: SYSTEM

Enter System Database User (SYSTEM) Password:

Importing delivery units...

No delivery units to import

Update Repository done

Log file written to '/var/tmp/hdb_HDB_UpdateRepository_2020-05-20_06.33.17/hdbupdrep.log' on host 's4hana'.

hdbadm@localhost:/usr/sap/HDB/SYS/global/hdb/install/bin>

Note: Friends i am trying to share my knowledge and learning from experience. please help to correct if i am wrong. 

How to create user in HANA DB with lifetime validity?

As HANA administrator, we need to work on HANA database users. In case we need set user password validity to unlimited. Its simple we can do same by just executing 2 queries.

1: We need to create user with password.

                             create user HANA_USER password "Welcome123";

2: Once user is created then set validity by running below query

                              alter user HANA_USER DISABLE PASSWORD LIFETIME;

Note:

We need to run these queries on respective TENANT or SYSTEM DB. 

To running queries we have sufficient privilege  

HANA DB Upgrade

1: SAP HANA Platform 1.0 (all supported SPS) to SAP HANA Platform 2.0 SPS 00 

2: SAP HANA Platform 2.0 to a higher release. 

PREREQUISITES

Before starting the update for the components in your SAP HANA landscape several prerequisites have to be fulfilled.

Make sure you fulfill the following prerequisites:

1: If you are performing an offline update, you have all the necessary installation files for the individual components.

2: You have a valid, permanent SAP license for the SAP HANA database. 

3: You have user names and passwords for the following users:
                 1.      sidadm user
                 2.      sapadm user
                 3.      S-user for the SAP Support Portal 
                 4:  SYSTEM

4: You have carefully read the update instructions for the individual components.

5: You have made the necessary preparations to perform backups for the SAP HANA database and the other components

6:If you are doing Update an SAP HANA System from 1.0 SPS 10 - 1.0 SPS 12 to SAP HANA Platform 2.0 SPS 00.  

              With SAP HANA Platform 2.0, the metadata persistency layout has been changed. Furthermore an older column store persistence format has been deprecated and is not supported anymore. Therefore you need to pay special attention before updating your existing SAP HANA 1.0 system to SAP HANA Platform 2.0.    --  Mandatory Preparation Steps for Upgrading a SAP HANA 1 System to SAP HANA 2 (2372809 )

7: IF you updating to SAP HANA Platform 2.0 SPS 00 or higher, it may be necessary to upgrade the operating system. For more information, 2235581 - SAP HANA: Supported Operating Systems

             Red Hat

                 7.2 (up to HANA 2.0 SPS03)

              SUSE

                12 SP2 (HANA 2.0 SPS01 and newer, up to HANA 2 SPS03)

               12 SP1 (HANA 2.0 SPS00 and newer, up to HANA 2 SPS03)

7: Download software 

Note

With an SAP HANA SPS you also perform updates for SAP HANA revisions and SAP HANA maintenance revisions. For more information, see SAP Note 2378962 - SAP HANA 2.0 Revision and Maintenance Strategy.

2378962 - SAP HANA 2.0 Revision and Maintenance Strategy

Please refer below link to understand when to upgrade HANA Database 

HANA DB upgrade


UPGRADE PROCESS

1: Change to the following directory on the installation medium:

cd /DATA_UNITS/ HDB_ <select as per OS)

2: Run the SAP HANA database lifecycle manager:

 ./hdblcmgui The SAP HANA database lifecycle manager graphical user interface appears.

3. Select a detected software component or add a software component location by selecting Add Component Location. Then select Next.

4. Select Update Existing System, and choose the SID from the drop-down menu. Then select Next.

 5. Select the components you would like to update, then select Next.

 6. Specify the SAP HANA authorization information.

 When asked for the database user, you have the opportunity to specify a lesser-privileged database user if you have previously created one. For more information about creating a database user for the update, see Related Information.

7. Define additional properties, depending on which components are selected.

8. After specifying all system properties, review the summary, and select Update.

please refer another blogs regarding errors doing upgrade