HA200 Col15 - Exercise Solutions

[Hay Bouten]

Copy&Paste Exercise Solutions for HA200 Col13

Exercise 1: Install a Single-Host SAP HANA System

• Nothing to copy/paste

Exercise 2: Installing SAP HANA Cockpit 2.0

• Nothing to copy/paste

Exercise 3: Configure and Open SAP HANA Cockpit 2.0

• 2.g) Connect to your SAP HANA database with the following command:
  For Group94: hdbsql -n wdflbmt7194 -i 00 -d SYSTEMDB -u SYSTEM -p Welcome1
  For Group94: hdbsql -n wdflbmt7194 -i 00 -d H94 -u SYSTEM -p Welcome1
  For Group95: hdbsql -n wdflbmt7195 -i 00 -d SYSTEMDB -u SYSTEM -p Welcome1
  For Group95: hdbsql -n wdflbmt7195 -i 00 -d H95 -u SYSTEM -p Welcome1
• 3.a) To create the COCKPIT_MONITOR database user, enter the following SQL statement:
  CREATE USER COCKPIT_MONITOR PASSWORD Welcome1 NO FORCE_FIRST_PASSWORD_CHANGE;
• 3.b) To grant the COCKPIT_MONITOR user the required authorization, enter the following SQL statements:
  • GRANT CATALOG READ to COCKPIT_MONITOR;
  • GRANT SELECT on SCHEMA _SYS_STATISTICS to COCKPIT_MONITOR;
  • ALTER USER COCKPIT_MONITOR DISABLE PASSWORD LIFETIME;
• 4.a) To connect to the SAP HANA cockpit manager, in Internet Explorer, enter the following URL: https://wdflbmt7285.wdf.sap.corp:51023
• 7.a) In a new tab, enter the following URL:
  https://wdflbmt7285.wdf.sap.corp:51021

Exercise 4: Update SAP HANA

• 4.c) In the Terminal, enter the following command:
  HDB version

Exercise 5: Install the SAP HANA Interactive Education (SHINE) Content
Unpack the SHINE Package

• 4.a) Start the SAP HANA cockpit 2.0 using the URL:
  https://wdflbmt7285:51021
• 4.h) In the Select Roles dialog box, search for the role:
  sap.hana.xs.lm.roles::Administrator.

Import the SHINE Package

• 1.a) In a Web browser, go to
  For Group94: https://wdflbmt7194:4300/sap/hana/xs/lm/index.html
  For Group95: https://wdflbmt7195:4300/sap/hana/xs/lm/index.html

Add the SHINE Administrator Role to your User Account

• 1.a) Start the SAP HANA cockpit 2.0 using the URL:
  https://wdflbmt7285:51021
• 1.h) In the Select Roles dialog box, search for the role:
  sap.hana.democontent.epm.roles::Admin

Generate Time Data and Create Synonyms

• 1) In a Web browser, go to
  For Group94: https://wdflbmt7194:4300/sap/hana/democontent/epm
  For Group95: https://wdflbmt7195:4300/sap/hana/democontent/epm

Exercise 6: Connect to the SAP HANA Database Using SAP HANA Studio

• 5.b) Enter the following SQL statement:
  select * from "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::PO.Item"
• 5.f) To check that the new setting is effective, enter the following SQL statement:
  select * from "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::PO.Item"

Exercise 7: Working with HDBSQL
Log On to Your SAP HANA Database Using HDBSQL

• 1.a) To navigate to the folder of the hdbclient, enter the following command:
  cd /hana/shared/<SID>/hdbclient
• 2.a) Enter the following command:
  ./hdbsql -n localhost -i 00 -u SYSTEM -p Welcome1
• 3.a) To test whether the connection works, execute the following command:
  SELECT * FROM dummy

Insert an Entry in the Hdbuserstore for User SYSTEM

• 1.a) Enter the following command:
  ./hdbuserstore LIST
• 2.a) Enter the following command:
  ./hdbuserstore SET MYSYSTEM localhost:30015 SYSTEM Welcome1
• 2.b) To show the content of the hdbuserstore, enter the following command:
  ./hdbuserstore LIST

Connect to Your SAP HANA Database

• 1.a) Enter the following command:
  ./hdbsql -U MYSYSTEM
• 2.a) To test whether the connection works, execute the following command:
  SELECT * FROM dummy

Exercise 8: Monitor SAP HANA with DBACockpit
Monitor SAP HANA Using the DBACockpit

• 1.f) To check that the ABAP system is running, enter the following command:
  ps —ef | grep dw
• 6.h) On the Input Query tab, review the following SQL statement:
  SELECT * FROM SAPT4H.USR02

Check the Content of the hdbuserstore

• 2.a) Enter the following command:
  hdbuserstore LIST

Configure the DBACockpit Connection for Your Standalone SAP HANA

• 5.d) From the list, select the table
  sap.hana.democontent.epm.data::MD.Products
• 5.h) On the Input Query tab, enter the following SQL statement:
  select "PRODUCTID", "CATEGORY", "WEIGHTMEASURE", "WEIGHTUNIT", "CURRENCY", "PRICE", "PRODUCTPICURL" from "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Products"

Exercise 9: Use Different Methods to Start and Stop the SAP HANA Database
Start and Stop SAP HANA Using SAP HANA Cockpit 2.0 in a Browser

• 2.a) Start a browser and open the following URL:
  https://wdflbmt7285.wdf.sap.corp:51021

Start and Stop SAP HANA Using HDB on the Command Line

• 3.b) To check the database status, enter the following command:
  HDB info
• 3.c) To stop the SAP HANA database, enter the following command:
  HDB stop
• 3.d) When the database stops, check the status of the processes using the following command:
  HDB info
• 4.a) In your PuTTY session, start the SAP HANA database using the following command:
  HDB start

Start and Stop SAP HANA Using the sapcontrol on the Command Line

• 3.a) As user ha200root, check the SAP HANA database status using the following command:
  /usr/sap/hostctrl/exe/sapcontrol -nr 00 -function GetProcessList
• 3.b) To stop the SAP HANA database, enter the following command:
  /usr/sap/hostctrl/exe/sapcontrol -nr 00 -function Stop
• 3.c) After the database stops, check the status of the processes using the following command:
  /usr/sap/hostctrl/exe/sapcontrol -nr 00 -function GetProcessList
• 4.a) As user ha200root, check the SAP HANA database status using the following command:
  /usr/sap/hostctrl/exe/sapcontrol -nr 00 -function GetProcessList
• 4.b) Start the SAP HANA database using the following command:
  /usr/sap/hostctrl/exe/sapcontrol -nr 00 -function Start
• 4.c) After the database starts, check the status of the processes using the following command: /usr/sap/hostctrl/exe/sapcontrol -nr 00 -function GetProcessList

Exercise 10: Change SAP HANA Configuration Parameters with SAP HANA Cockpit

• 2.a) Start a browser and open the URL:
  https://wdflbmt7285.wdf.sap.corp:51021
• 3.e) In the SQL editor in the right panel, enter the following SQL statement:
  select * from "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::PO.Item"

Exercise 11: Administer a Table
Create a Table and Insert Data

• 1.d) To create the table, enter the following command:
  CREATE TABLE "SAP_HANA_DEMO"."EPM.PO.Item_Part" like "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::PO.Item";
• 2.a) In the SQL Console, enter the following command:
  INSERT INTO "SAP_HANA_DEMO"."EPM.PO.Item_Part" SELECT * FROM "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::PO.Item";

Create and Merge Table Partitions

• 3.c) To check the partitioning consistency of the table, enter the following SQL command:
  CALL CHECK_TABLE_CONSISTENCY('CHECK_PARTITIONING_DATA', 'SAP_HANA_DEMO','"EPM.PO.Item_Part"');

Exercise 12: Monitor SAP HANA using Administration Console Tools

• 2.b) Start a browser and open the following URL:
  https://wdflbmt7285.wdf.sap.corp:51021

Exercise 13: Configure SAP HANA Traces

• 2.b) Start a browser and open the following URL:
  https://wdflbmt7285.wdf.sap.corp:51021
• 3.d) Search for the table:
  sap.hana.democontent.epm.data::PO.Item

Use the SAP HANA Web-Based Development Workbench to Activate the SQL Trace

• 1.a) In a browser, open the following URL:
  For Group94: https://wdflbmt7194:4300/sap/hana/ide/trace
  For Group95: https://wdflbmt7195:4300/sap/hana/ide/trace
• 2.c) Search for the table:
  sap.hana.democontent.epm.data::PO.Item

Exercise 14: Work with Diagnosis Files

• 2.a) Start a browser and open the following URL:
  https://wdflbmt7285.wdf.sap.corp:51021

Exercise 15: Analyze Queries

• Nothing to copy/past

Exercise 16: Transporting Changes
Create a Delivery Unit in the Source System

• 1.a) Start your browser and enter the following URL:
  https://wdflbmt7194:4300/sap/hana/xs/lm

Register the Source System in the Target System and Create a Transport

• 1.a) Start your browser and enter the following URL:
  https://wdflbmt7195:4300/sap/hana/xs/lm

Exercise 17: Backup and Recovery
Check the Location of the Directory

• 1.a) Start Internet Explorer and connect to the SAP HANA cockpit using the following URL:
  https://wdflbmt7285.wdf.sap.corp:51021
• 2.c) Search for the parameter:
  basepath_databackup
• 2.d) Search for the parameter:
  basepath_logbackup
• 3.c) To estimate the size of the next data backup, enter the following command:
  select * from M_BACKUP_SIZE_ESTIMATIONS

Check the Parameter Settings

• 1.c) Search for the parameter:
  log_mode
• 1.d) Search for the parameter:
  enable_auto_log_backup

Perform a Complete Data Backup of your Database

• 3.e) Navigate to the following backup directory:
  cd /usr/sap/H94/HDB00/backup/data

Simulate a File System Crash

• 2.a) To open the directory that contains the data volumes, navigate to the following directory:
  For Group94: cd /hana/data/H94/mnt00001
  For Group95: cd /hana/data/H95/mnt00001

Exercise 18: Configure Data Volume Encryption

• Nothing to copy/past

Exercise 19: Enable Audit Policy

• 2.j) For auditing, from schema SAP_HANA_DEMO select the table:
  EPM.PO.Item_Part

Analyze the Audit Log

• 1.c) Enter the following command:
  select * from "SAP_HANA_DEMO"."EPM.PO.Item_Part"
• 2.a) To check the resulting entry in the audit trail (database table), enter the following sql command:
  select "TIMESTAMP", "USER_NAME", "AUDIT_POLICY_NAME", "STATEMENT_STRING" from "PUBLIC". "AUDIT_LOG"

Exercise 20: Maintaining Users and Authorization
Create Role Management and Administration Users

• 1.d) Enter the user name:
  ROLE_ADMIN_USER
  2.d) Enter the user name:
  ADMIN_USER

Assign a Role to the SYSTEM User

• 1.h) Select the role:
  sap.hana.ide.roles::EditorDeveloper

Create a Role for Role Creators

• 1.a) To log on to the SAP HANA Web IDE with SYSTEM user, open Internet Explorer and enter the following URL: For Group94: http://wdflbmt7194.wdf.sap.corp:4300/sap/hana/xs/ide/
  Group95: http://wdflbmt7195.wdf.sap.corp:4300/sap/hana/xs/ide/
• 1.e) In the Package name field, enter:
  HA200_ROLE_PACKAGE
• 2.a) To log on to the SAP HANA Web IDE with SYSTEM user, open Internet Explorer and enter the following URL: For Group94: http://wdflbmt7194.wdf.sap.corp:4300/sap/hana/xs/ide/
  Group95: http://wdflbmt7195.wdf.sap.corp:4300/sap/hana/xs/ide/
• 2.d) Enter the following name for your role:
  role_builder

Assign Roles to the Role Management User

• 1.h) Select the role:
  sap.hana.ide.roles::EditorDeveloper
• 1.k) Select the role:
  HA200_ROLE_PACKAGE::role_builder

Create Roles for Database Administration

• 1.a) To log on to the SAP HANA Web IDE with SYSTEM user, open Internet Explorer and enter the following URL: For Group94: http://wdflbmt7194.wdf.sap.corp:4300/sap/hana/xs/ide/
  Group95: http://wdflbmt7195.wdf.sap.corp:4300/sap/hana/xs/ide/
• 1.d) Enter the following name for your role:
  basic_admin
• 2.a) To log on to the SAP HANA Web IDE with SYSTEM user, open Internet Explorer and enter the following URL: For Group94: http://wdflbmt7194.wdf.sap.corp:4300/sap/hana/xs/ide/
  Group95: http://wdflbmt7195.wdf.sap.corp:4300/sap/hana/xs/ide/
• 2.d) Enter the following name for your role:
  backup_admin

Exercise 21: Installing a SAP HANA Scale Out System

• 1.b) Execute the following command:
  df -h | grep /hanaDist

Create and Mount the Directory

• 1.a) On host wdflbmt7195, enter the following command:
  mount -a
• 1.b) To check that /hanaDist is now mounted correctly, execute the following command:
  df -h | grep /hanaDist

Create a Shared Directory

• 1.a) To create a directory, enter the following command:
  mkdir /hanaDist/shared

Install the SAP HANA Scale Out System

• 2.c) On the Specify the Data and Log Area screen, change the Data Volume and Log Volume location to:
  Data Volume location: /hanaDist/data/MHS
  Log Volume location: /hanaDist/log/MHS

Exercise 22: Convert a Single Tenant to a Multitenant System
Register SYSTEM Database in SAP HANA Cockpit

• 1.g) To connect to your system database, enter the following command:
  hdbsql -n <SAP HANA database server> -i 00 -u SYSTEM -p Welcome1 —d SYSTEMDB
• 2.a) To create the COCKPIT_MONITOR database user, enter the following SQL statement:
  CREATE USER COCKPIT_MONITOR PASSWORD Welcome1 NO FORCE_FIRST_PASSWORD_CHANGE;
• 2.b) To grant the COCKPIT_MONITOR user the required authorization, enter the following SQL statements:
  GRANT CATALOG READ to COCKPIT_MONITOR;
  GRANT SELECT on SCHEMA _SYS_STATISTICS to COCKPIT_MONITOR;
• 3.a) Start Internet Explorer and connect to the SAP HANA cockpit manager using the following URL:
  https://wdflbmt7285.wdf.sap.corp:51023
• 5.a) In a new tab, open the following URL:
  https://wdflbmt7285.wdf.sap.corp:51021